Home > Articles > Data > SQL

  • Print
  • + Share This
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;
  • + Share This
  • 🔖 Save To Your Account