Databases - Worksheet 5

This worksheet uses the Classic Models database available from the MySQL Tutorial site. This covers the operations of a wholesale business selling scale models of cars, planes, trains, ships etc. to retailers.

The SQL queries here use a combination of the techniques covered over the first half of the semester, so some are straightforward while some require more thought. Try to use the more complex constructs (such as nested or correlated queries) only when they are required.

• How many different products does Classic Models stock?
• What SQL command will list all the categories? (category is the same as product line)

+------------------+
| Classic Cars     |
| Motorcycles      |
...  

• What SQL command produces a table listing the categories along with the number of products in each category?

+------------------+----------+
| Classic Cars     |       38 |
| Motorcycles      |       13 |
...

• What SQL command lists the names of the customers who have employee number 1323 as their sales representative?

+-----------------------------------+
| Land of Toys Inc.                 |
| Gift Depot Inc.                   |
...

• What SQL command lists the full names of the employees who are not sales reps for any customers (Warning: NULL causes subtle but major problems with NOT IN so you have to ensure that any set you create contains no NULL values.)

+--------------------------------+
| Diane Murphy                   |
| Mary Patterson                 |

• What SQL command makes a list of the number of people each supervisor supervises? (If an employee does not supervise anyone, then they are not a supervisor, and so should not appear in this list).

+----------------+----------+
|           1002 |        2 |
|           1056 |        4 |
...

• What is the total value of the orders for which employee 1370 is responsible (this number is just a bit larger than 1.25 million).
• What SQL command lists the total value of the orders for each employee, still listed by employee number?

| 1165 | 1081530.54 |
| 1166 |  347533.03 |
...

• What SQL command lists the order numbers that include ships, in ascending order of order number?

+-------------+
|       10105 |
|       10106 |
|       10117 |

• What SQL command lists the order numbers that include either ships or trains?

+-------------+
|       10104 |
|       10105 |
|       10106 |

• What SQL command lists the order numbers that include both ships and trains?

+-------------+
|       10105 |
|       10117 |
|       10128 |

Written with StackEdit.