Purdue University Fort Wayne SQL Queries Worksheet

Your task in this assignment is to write a set of SQL queries (I will supply the tables). The script file has been added to the question and run it in SQLDeveloper to build the tables you will be querying:

CD(CID, Title, Artist, YearReleased, Price)

CDSTORE(SID, Name, City, State)

CDORDER(OrderID, StoreID, CDID, Quantity, OrderDate)

In addition to the primary keys underlined above, CDID in CDORDER is a foreign key referencing CID in CD, and StoreID in CDORDER is a foreign key referencing SID in CDSTORE. Look at the Columns, Data, and Constraints for each of the three tables before continuing, to be sure that they have been constructed correctly. You might also want to draw the foreign keys and reference arrows into the set of relation schemas given above to be sure that you understand the links among the tables. For each of the following query problems, follow the steps discussed in class: interpret the problem, predict the output by solving it by hand on the needed table(s), write a query to solve the problem, and test the query. (Many of these query problems can be solved with information from just one of the given tables, but some will require inner joins.) In a separate .sql file (do not modify CDs2022.sql), write a script that contains just eight SQL queries to solve the following problems (in this order):

1. Give the title, price, and year of all CDs released by Alice Stein before 2017, ordered from the newest CD to the oldest CD.

2. Give an alphabetical list of the names of all stores whose names begin with ‘CD’.

3. Display the number of CDs released in each year, ordered from the earliest year to the latest. (Include the value of the year itself in each line of your output as well.)

4. For each artist, give the artist’s name and the average price of the CDs released by that artist, ordered from the lowest average price to the highest average price.

5. For each store that has ordered CDs, give the ID of the store and the total quantity of CDs that the store has ordered. Order the output by the ID of the store.

6. Give the IDs and names of all CD stores that placed an order during April of 2021.

7. List, in alphabetical order, the titles of all CDs ordered by stores in New York (‘NY’). (Hint: You will need to join all three tables together to solve this problem.)

8. For each CD, give the title of the CD and how many orders have been placed for that CD – not the total quantity, just how many orders have been placed for any quantity of that CD. Order the output from the CD with the most orders placed for it to the CD with the fewest. (If no one has ordered the CD yet, you do not have to include it in the output.)

You may write and test your queries individually in SQLDeveloper, but once you have them working, you should put them all together into a single script file and be sure that the queries can be run all together in this form. Run the script file containing your queries to verify that your results are correct.