ITEC 3335
Database Development
Fall 2017
Homework #7

More SQL

Use the 'Big' PVFC database of the textbook in MySQL (see HW #3), provide the MySQL commands for the following queries.

(1) Employee names with the skill '10in Table Saw'.

+----------------+
| EmployeeName   |
+----------------+
| Phil Morris    |
| Lawrence Haley |
| Robert Lewis   |
+----------------+
3 rows in set (0.00 sec)

(2) Orders and their details (products and quantities) done by the saleperson with id #2. Only orders with order lines are listed.

+----------+---------------------+-------------------------+----------+
| order id | date                | product                 | quantity |
+----------+---------------------+-------------------------+----------+
|        3 | 2014-07-19 00:00:00 | Birch Coffee Tables     |        2 |
|       24 | 2015-03-10 00:00:00 | Cherry End Table        |        0 |
|       48 | 2015-03-11 00:00:00 | High Back Leather Chair |        5 |
|       56 | 2015-03-11 00:00:00 | Entertainment Center    |        1 |
|       69 | 2015-03-11 00:00:00 | 48" Bookcase            |        4 |
+----------+---------------------+-------------------------+----------+
5 rows in set (0.00 sec)
 

(3) List the order id that contains the ordering of the product 'Birch Coffee Tables' and the quantity of
'Birch Coffee Tables' ordered.

+----------+----------+
| order id | quantity |
+----------+----------+
|        1 |       18 |
|        3 |        2 |
|       25 |        5 |
|       39 |        3 |
|       54 |        2 |
+----------+----------+
5 rows in set (0.00 sec)
 

(4) List the states and the number of customers they have. Display the result in the descending order of the number of customers.

+-------+-------+
| state | count |
+-------+-------+
| NY    |     4 |
| FL    |     2 |
| NM    |     2 |
| TX    |     1 |
| NJ    |     1 |
| CA    |     1 |
| CO    |     1 |
| Il    |     1 |
| VA    |     1 |
+-------+-------+
9 rows in set (0.00 sec) 

(5) List the states and the number of customers they have. Display the result in the descending order of the number of customers. List only those states with two or more customers.

+-------+-------+
| state | count |
+-------+-------+
| NY    |     4 |
| FL    |     2 |
| NM    |     2 |
+-------+-------+
3 rows in set (0.00 sec)

(6) List the names of the products that use the material with id FINGLS, but not the material with id NF1.

+----------------------+
| ProductDescription   |
+----------------------+
| Entertainment Center |
+----------------------+
1 row in set (0.00 sec)
 

The solutions should be put into a single text file, with proper description. Name the file: H6_<<Your name>>_<<Your Student ID>>.sql. Your TA should be able to execute your sql file directly for grading.

As usual, submit the homework through Blackboard.