Assignment 3: SQL (8% of the course mark)
Your task in this assignment is to write 10 SQL queries in the context of the provided database. We recommend that you attempt to draw the ERD to better understand the data model. However, you are not required to submit your ERD.
Late submissions are not allowed.
SQL
Your task is to formulate 10 SQL queries to retrieve the following information from the given database implementation. We do not want you to provide us with the query results, just give us your queries and we will run your queries against the given database.
1. List all the books that are priced $20.00 or less. Display the result in decreasing order of price.
2. List the books that have word Harry in their names (book titles).
3. List all the customers who live in Los Angeles or Wichita.
4. List all the books for which any orders have been placed. Display the result in increasing order of the book id and do not repeat the book details.
5. List all the customers along with their book orders for whom book orders have not yet been shipped.
6. List all the customers whose first names or last names begin with letter A.
7. List all the customers who ordered who placed book orders for between 15-Aug-2018 and 18-Aug-2018. Display your results in the increasing order of the customer number and the date order is placed.
8. Get the average book price and average quantity on hand.
9. List all the employees who have served the customers with the same zip code. The list should also include customer name, zip.
10. List all the employees who were born after the year 1996.
First, download the database (called BookMailOrderDatbase, link above) from the stream and open the database using DB Browser for SQLite. Spend some time understanding the data model (e.g. understand what the tables are, what do attributes represent, what are the primary keys, what are the foreign keys and draw an ERD) and then develop your queries using DB Browser for SQLite. Make sure you submit your SQL queries via STREAM – simply paste your queries into the assignment submission interface (please do not create a separate document).
Important: For marking, we will run your queries against our database. You DO NOT need to submit your database or ERD. Use the given criteria only and in your output display what is asked but do include all the relevant columns to make the information meaningful and query results verifiable. You might have to do some research on SQL to finish the assignment (slightly more challenging than the lecture and/or lab work). Do not collaborate and/or share your answers with others.
How this assignment is going to be marked?
This is the provisional marking scheme:
Total 8 marks. Questions 1-4 are worth .5 mark each and questions 5-8 are worth 1 mark each.
