INNER JOIN

Subqueries: The Juicy Bits

Mon, 12/17/2007 - 16:35 — richard.selby

People love to know where all the juicy stuff is in the bible. So how about we create a table which points in the right direction? Note the use of the SET data type, on one of its rare outings.

DROP TABLE IF EXISTS juicybits;
CREATE TABLE juicybits(

  book        VARCHAR(32),

  cap         TINYINT UNSIGNED,

  verse_start TINYINT UNSIGNED,

  verse_end   TINYINT UNSIGNED,

  title       VARCHAR(255) NOT NULL,

  class       SET('prostitution','virgin',  

                  'insults','rape','slavery',

                  'willy','bear-maul','stoning')

);

Procedures: One Chapter at a Time

Mon, 12/17/2007 - 11:47 — richard.selby

Stored Procedures and Functions are a biggy on the MySQL Developer exam. They account for 20% of the marks of the second paper. And no wonder, there's a lot of new functionality (and syntax) to keep in mind. So with that in mind, let's put a procedure together.

Suppose you want to see the all of Genesis Cap 1 together. We can use group_concat() to squidge all the verses in Genesis 1 together on one line.

SELECT GROUP_CONCAT(line  ORDER BY verse SEPARATOR ' ')  as genesis

  FROM bible

 WHERE book = 'Ge'

   AND cap = 1 \G