Consider the following database schema for a BOOKSTORE database: ? Books (bookid
ID: 3641336 • Letter: C
Question
Consider the following database schema for a BOOKSTORE database:? Books (bookid, title, author, year)
? Customers (customerid, name, email)
? Purchases (customerid, bookid, year)
? Reviews (customerid, bookid, rating)
? Pricing (bookid, format, price)
Write SQL queries to answer the following:
(1) Find books (show their titles) written by 'Mike Wu' since year 2005.
(2) Find books (show their titles, authors and prices) that are on 'Database' (i.e., the title field contains 'Database'), available in 'VIDEO' format.
(3) For each year, 'Nelish Patel' purchased at least one book, find the number of books purchased. That is, the output should be a set of tuples, each indicating a year and the number of books purchased by 'Nelish Patel' in that year.
(4) Find customers (show their names and email addresses) who purchased more than one book in year 2011.
(5) Find the ratings information (show titles, authors and average ratings) for books on 'Database' (i.e., title contains 'Database').
Explanation / Answer
1. SELECT title FROM Books WHERE author = "Mike Wu" and year > 2005; 2. SELECT title, author, price FROM Books NATURAL JOIN Pricing WHERE format = "VIDEO" AND title LIKE "%Database%"; 3. SELECT year, COUNT(bookid) AS number FROM Customers JOIN Purchases USING (customerid) WHERE name = 'Nelish Patel' GROUP BY year; 4. SELECT name, email FROM ( SELECT customerid, name, email, COUNT(bookid) AS number FROM Customer JOIN Purchases USING (customerid) GROUP BY customerid ) AS counted WHERE number > 1; 5. SELECT title, author, AVERAGE(rating) AS average_rating FROM Reviews JOIN (SELECT * FROM Books WHERE title LIKE "%Database%") AS filtered USING (bookid) GROUP BY bookid;