MySQL Week 5

For this weeks exercises we consider an example of a database used by an online CD shop. Start by setting up the database using the file week05.setup.sql available on the blog. This will also fill the database with example data to be used in queries.

The database keeps track of CDs and their prices, customers and orders. The table cd contains information about CDs: artist name, title and price (in DKR), table customer contains information about customers. Table purchase contains information about orders (order is a keyword in SQL and so can not be used as a name for a table), which includes identity of customer and date of purchase, table purch cd(purch id, cd id, quantity) links CDs to orders, e.g. the tuple (3,21,2) represents the fact that two copies of cd number 21 are part of purchase number 3.

Tables customer and cd have surrogate keys, i.e., the primary keys of these are numbers that have no meaning in the outside world. We could have also chosen identifiers such as email addresses for customers or combinations of artist and title for cd, although the latter in practice might not be unique due e.g. to reissues of CDs.

Exercise 1: Aggregate functions

Write queries that

• List all orders along with name of customer, date, items bought, quantities
• List all orders along with name of customer, date and total price of order
• List all orders along with name of customer, date and total price of order, but only those orders with a total price above 100 kr.

Exercise 2: Hitlists

In this exercise you should construct queries that produce hitlists.

• First construct an all time hitlist: List artist names and titles of cd’s in descending order of total sold quantities.
• Now construct a hitlist for the month of February 2011. Do as you did above, but count only orders from February 2011.
• Restrict your hitlist from February to mention cd’s sold in at least two copies.

Exercise 3: Customer recommendations

In this exercise we construct queries that find information on who bought what, and queries that help us make customer recommendations, based on which cd’s are often bought together

• Find all orders containing Bob Dylan’s ’Blonde on Blonde’
• Find names of all customers who bought ’Blonde on Blonde’
• Find all cd’s that have been bought in the same order as ’Blonde on Blonde’. Sort them by the number of orders they have in common. Hint. First write a query that finds all cd’s along with all the purchase id’s in which they were bought, then combine with your solution to question 3.1
• We have noticed that Otto Mann really likes Judas Priest, and would like to sell him some more. Write a query that finds all Judas Priest albums that Otto has not already bought from us. Hint. First write a query that finds all cd’s Otto has bought.

For the last of these exercises the SQL construction except could have been useful, but note that this is not supported by MySQL.