Lab #4: SQL queries involving subqueries
Purpose: In this
lab, you should establish solid understanding and skills to write advanced SQL
queries by
·
composing logic conditions involving subqueries
in the WHERE clause together with comparison operators (such as >, <, >=, <=, < >, …) and subquery quantifiers such as EXISTS, NOT EXISTS,
ANY, ALL, and
·
applying the skills you learned from the
previous two SQL labs to apply cross
product, selection operation, and
projection operation in the FROM
clause, the WHERE clause, and SELECT clause of the SQL language respectively.
Step 1: Login
into MySQL local server in Metzger 100 lab with the password mysql.
Step 2: Use the buildUpDB.txt script to build up the records of the sample database
containing the salespeople, customers, and orders tables as described in
Homework#2. Follow the example queries in lab4.txt to
interact with MySQL database server to establish solid understanding and skills
as described in the purpose section above. The example queries in lab4.txt show you how to
- Find
the sname and comm of every salespeople whose comm (commission rate) is lower
than that of some (i.e. ANY) salesperson who got at least one order on
‘2000-10-06’.
- Find
the sname and comm of every salespeople whose comm (commission rate) is lower
than that of every salesperson (i.e. ALL) who got at least one order on
‘2000-10-06’
- Find
the every date when there was at
least one order from the customers but none of the orders that day was
from the customer with the cnum
2006.
- Find
the cnames of the customers who
DID NOT have order(s) everyday.
- Find
the cnames of the customers who
DID have order(s) everyday.
Step 3 For the sample database, write
SQL SELECT statements to
- Find
the cname of every customer who
placed at least one order on ‘2000-10-03’.
- Find
the cname of every customer
whose rating is better than that of some (i.e. ANY) customer who did place
an order on ‘2000-10-03’.
- Find
the cname of every customer
whose rating is better than that of every customer (i.e. ALL ) who did
place an order on ‘2000-10-03’.
- Find
the dates when either the
customer with the cnum 2007 or
the customer with cnum 2008 (or
both) had at least one order.
- Find
the dates when there were orders
but none of the orders was from either the customer with the cnum 2007 or the customer with cnum 2008.
- Find
the dates when there was one or
more order from the customers but none of the orders was with the
salesperson with the snum 1002.
- Find
the snums of the salespeople who
DID NOT get order(s) everyday.
- Find
the snums of the salespeople who
DID get order(s) everyday.
Step 4: Email me the SQL
queries you have for the questions in STEP 3 above and inform me of your
progress or difficulties encountered in this week’s progress report.
Useful
references for doing this homework:
· The
SQL overview handout, the relational algebra handout, and the SQL subquery
handout.
· The
section on
subqueries from the online MySQL reference manual