This chapter is from the book
This chapter is from the book
This chapter is from the book
Workshop
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix A, “Answers.”
Here are the CREATE TABLE statements and INSERT statements for the FRIENDS and PRICE tables. Type the following code into MySQL if you have not already done so.
create table friends
(lastname varchar(15) not null,
firstname varchar(15) not null,
areacode numeric(3) null,
phone varchar(9) null,
st char(2) not null,
zip varchar(5) not null);
insert into friends values
('BUNDY', 'AL', '100', '555-1111', 'IL', '22333');
insert into friends values
('MEZA', 'AL', '200', '555-2222', 'UK', NULL);
insert into friends values
('MERRICK', 'BUD', '300', '555-6666', 'CO', '80212');
insert into friends values
('MAST', 'JD', '381', '555-6767', 'LA', '23456');
insert into friends values
('BULHER', 'FERRIS', '345', '555-3223', 'IL', '23332');
insert into friends values
('PERKINS', 'ALTON', '911', '555-3116', 'CA', '95633');
insert into friends values
('BOSS', 'SIR', '204', '555-2345', 'CT', '95633');
create table price
(item varchar(15) not null,
wholesale decimal(4,2) not null);
insert into price values
('TOMATOES', '.34');
insert into price values
('POTATOES', '.51');
insert into price values
('BANANAS', '.67');
insert into price values
('TURNIPS', '.45');
insert into price values
('CHEESE', '.89');
insert into price values
('APPLES', '.23');
Quiz
Use the FRIENDS table to answer the following questions.
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------- --------- -------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332 PERKINS ALTON 911 555-3116 CA 95633 BOSS SIR 204 555-2345 CT 95633
| 1. |
Write a query that returns everyone in the database whose last name begins with M. |
| 2. |
Write a query that returns everyone who lives in Illinois with a first name of AL. |
| 3. |
Given two tables (PART1 and PART2) containing columns named PARTNO, how would you find out which part numbers are in both tables? Write the query. |
| 4. |
What shorthand could you use instead of WHERE a >= 10 AND a <=30? |
| 5. |
What will this query return? SELECT FIRSTNAME FROM FRIENDS WHERE FIRSTNAME = 'AL' AND LASTNAME = 'BULHER'; |
| 6. |
What is the main difference in the result set when using UNION versus UNION ALL? |
| 7. |
What is the primary difference between using INTERSECT and MINUS? |
Exercises
| 1. |
Using the FRIENDS table, write a query that returns the following: NAME ST ----- ------- AL FROM IL |
| 2. |
Using the FRIENDS table, write a query that returns the following: NAME PHONE -------------- ------------ MERRICK, BUD 300-555-6666 MAST, JD 381-555-6767 BULHER, FERRIS 345-555-3223 |
| 3. |
Select all columns from the PRICE table where the column WHOLESALE is greater than .50. |
| 4. |
What results do you get from the following query? mysql> select *
-> from price
-> where item like '%ATO%';
|
| 5. |
Does MySQL support set operators such as UNION, UNION ALL, INTERSECT, and MINUS? |
| 6. |
What is wrong with the following query? SELECT FIRSTNAME,LASTNAME FROM FRIENDS_1 UNION SELECT FIRSTNAME FROM FRIENDS_2; |