-> producer_id SMALLINT(4) NOT NULL DEFAULT 0

Query OK, 0 rows affected (1.03 sec)
mysql> INSERT INTO producer VALUES
-> (1, "Phil Spector", 36),
-> (2, "George Martin, 40),
-> (3, "Tina Weymouth", 20),
-> (4, "Chris Frantz", 20),
-> (5, "Ed Kuepper", 15);
Query OK, 5 rows affected (0.50 sec)
Records: 5 Duplicates 0 Warnings You can download these instructions from the book’s website in the file pro-
ducer.sql, and run them in the same way you ran the music.sql file.
You can see it’s a fairly simple table an identifier column, a textual name, and an integer value of the number of years they’ve been producing. The second table is almost identical, but stores information about engineers—that is, the people who work the mixing desks and other equipment that’s used in the music recording process. Here’s the table and its data:
mysql> CREATE TABLE engineer (
-> engineer_id SMALLINT(4) NOT NULL DEFAULT 0,
-> engineer_name CHAR) DEFAULT NULL,
-> years SMALLINT(3) DEFAULT 0,
-> PRIMARY KEY (engineer_id));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO engineer VALUES
-> (1, "George Martin, 40),
-> (2, "Eddie Kramer, 38),
-> (3, "Jeff Jarratt", 40),
-> (4, "Ed Stasium", 25);
Query OK, 4 rows affected (0.14 sec)
Records: 4 Duplicates 0 Warnings You can download these instructions from the book’s website in the file engineer.sql.
Using ANY and IN
Now that you’ve created the sample tables, you can try an example using ANY. Suppose you’re looking to find engineers who’ve been working longer than the least experienced producer. You can express this information need as follows:
mysql> SELECT engineer_name, years

Download 4.24 Mb.

Share with your friends: