Question

Given the following relational schema, write queries in SQL to answer the English questions. There is a shipment database on the MySQL server. You can also use the DDL for MySQL. You must only submit the SQL for your answers but you can include the query output as well to help the TA with marking.

Customer(cid: integer, cname: string, address: string, city: string, state: string)
Product(pid: integer, pname: string, price: currency, inventory: integer)
Shipment(sid: integer, cid: integer, shipdate: Date/Time)
ShippedProduct(sid: integer, pid: integer, amount: integer)

1. Return the products (name) whose name contains 'Ch' with a price more than the average price.

   Output:

   +--------------+
   | pname        |
   +--------------+
   | Wooden Chair |
   +--------------+
   

2. Return all customers and their states that share a state with another customer.

   Output: (Note: Order of rows does not matter.)

   +-----------------+-------+
   | cname           | state |
   +-----------------+-------+
   | Fred Smith      | IL    |
   | Joe Smithsonian | IA    |
   | Steve Stevenson | IL    |
   | Russell Johnson | CA    |
   | John Doe        | MI    |
   | Scott Charles   | CA    |
   | Shannon Rose    | MI    |
   | Beth Rosebud    | IA    |
   | Suzanne May     | IA    |
   +-----------------+-------+
   

3. Return the shipment id and total value of the entire shipment (price*amount) ordered by the shipment values ascending.

   Output:

   +-----+----------------+
   | sid | Total_Shipment |
   +-----+----------------+
   |  12 |          45.49 |
   |   8 |          98.75 |
   |   7 |          98.97 |
   |  10 |         104.00 |
   |   4 |         164.95 |
   |   6 |         183.96 |
   |  11 |         260.00 |
   |   3 |         659.80 |
   |   5 |         676.00 |
   |   9 |        1664.00 |
   +-----+----------------+

Answer 1

1) SELECT pname FROM product WHERE pname LIKE "%Ch%" AND price > AVG(price);

Note:

• Here, we can see one aggregate function AVG() is used.
• We have to select pnames contains Ch, so use a LIKE operator associated with pname. %Ch% specifies zero or more characters on the beginning or end of pname.
• Two conditions, pname with 'ch' and also the price is equal to average of all the price are satisfied, then that rows will be selected.

2)SELECT cname, state FROM Customer A, Customer B WHERE A.cid != B.cid AND A.state= B.state;

• We can use same table with different aliase names in a same query. A and B are aliases of same table Customer.
• Here, we have to check a customer name with same state, so use the condition A.state = B.state also both will not representing same customer so use A.cid not equal to B.cid.

3)SELECT sid, price*amount AS "Total_Shipment" FROM Product, ShippedProduct WHERE Product.pid= ShippedProduct.pid ORDER BY price*amount asc;

• Two tables Product and ShippedProduct, we can calculate price* amount. Use an aliase name Total_Shipment for price*amount.
• If the Pid's both tables are same, then price from Product and amount from ShippedProduct will be multiplied.
• Also the result should be in the ascending order of price*amount.
• Use asc keyword for ascending, desc for descending order sorting.