sql test

A small local take-out shop sells pizza, calzone, wings, and such. They have a simple database to track their customers and orders. The owner is not much of a SQL expert, so he asks you to answer some questions.

Sample of the data You should assume that the tables are much larger than this when formulating the SQL. Table: Customer Customer_ID 1 2 3 4 5 6 ‌

Name Andy David Betty Charlie David Francine

Table: Purchase Customer_ID 1 6 3 2 5 5 3 6 2 ‌

Purchase_Date 1/11 1/12 1/12 1/13 1/13 1/15 1/18 1/24 2/12

Phone 415-555-1717 415-572-1414 415-655-1313 650-245-1515 650-892-1818 415-655-1212

Item Pizza Calzone Wings Calzone Calzone Wings Wings Pizza Wings

For each question please provide appropriate SQL to help the shop owner. Answer as many as you can.

1) The owner asks you for a list of all customers:

2) Now, all customers and their orders:

3) The owner says he is really looking for a list of all wing orders. You sigh and provide the following SQL:

4) Now he says the list is too long. Instead of a line for every order, he asks for a list of customers and how many times they have ordered wings:

5) Hmmm. The owner looks at the results and realizes that people who didn’t order wings are not in the list. Can you make them appear as well?

6) The owner finally tells you what he really wants: a list only of the customers that have not ordered wings. The shop is going to send coupons to just those customers.