Assignment 2
The following is the database schema used in the questions.
Note: when writing queries, please express dates in the format of YYYY-MM-DD.
1. Express each of the following queries in relational algebra AND relational calculus AND SQL (2% each):
Query 1: List names and cities of all the customers.
Query 2: List names and prices of those parts produced by Apple or Samsung in 2020. Query 3: List supply dates, quantities and amounts of all the Apple parts produced in 2020.
Query 4: For each supply with a quantity over 1000 and after 2019-01-01, list the part name, customer name and customer cities.
Query 5: List names and cities of the customers who bought one or more Apple products. Query 6: List names and cities of the customers who did not buy any Apple product. Query 7: List names and cities of the customers who bought Apple products only.
Query 8: List names and cities of the customers who bought all the Apple products.
2. Express each of the following queries in SQL (1% each): Query 9: Find the average price of Apple products.
Query 10: For each Apple product, list product number, product name and total supply quantity since Jan 1, 2019. 1
Query 11: For each Apple product supplied to Guelph in 2020, list product number, product name, total supply quantity, and total supply amount.
Query 12: For each Apple product supplied to more than 10 different customers in Guelph in 2020, list product number, product name, total supply quantity, and total supply amount. Sort the result by product name.