Workshop
The Workshop is designed to help you anticipate possible questions, review what you've learned, and begin learning how to put your knowledge into practice.
Quiz
What would be the LIMIT clauses for selecting the first 25 records of a table? Then the next 25?
How would you formulate a string comparison using LIKE to match first names of "John" or "Joseph"?
What would be the query to select all records in the master_name table that were added before October 30, 2001. Also, order the records from newest to oldest and exclude any with the default date.
Answers
LIMIT 0, 25 and LIMIT 26, 25
LIKE 'Jo%'
SELECT * FROM master_name WHERE ((name_dateadded < '2001-10-30') and (name_dateadded != '0000-00-00 00:00:00')) ORDER BY name_dateadded DESC;
Activity
Select all the records in the master_name and company tables. Using your knowledge of the INSERT statement, populate the name_company_map table with the correct values.
For example, if John Doe works at NASA, select from the master_name table and the company table to find the IDs you need:
mysql> SELECT name_id, firstname, lastname FROM master_name; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 1 | John | Smith | | 2 | Jane | Smith | | 3 | Jimbo | Jones | | 4 | Andy | Smith | | 7 | Chris | Jones | | 46 | Patrick | O'Brien | | 45 | Anna | Bell | | 44 | Jimmy | Carr | | 43 | Albert | Smith | | 42 | John | Doe | +---------+-----------+----------+10 rows in set (0.00 sec)
John Doe's name_id is 42.
mysql> select company_id, companyname from company; +------------+------------------+ | company_id | companyname | +------------+------------------+ | 1 | Sun Microsystems | | 2 | Joe's Steakhouse | | 3 | Sams Publishing | | 4 | NASA | +------------+------------------+4 rows in set (0.00 sec)
Joe works at NASA, and that company_id is 4.
To create the relationship, add the following into the name_company_map table:
INSERT INTO name_company_map (name_id, company_id) VALUES (42,4);