SQL Assignment C(10 marks)

Unless otherwise stated, use one SQL statement instead of multiple SQL statements to answer the following questions

When a table is shown under the question, you should generate a report the same as the table.

Questions:

C1. What is the most popular item in terms of the number of times borrowed? (2 marks)

C2. What is the average cost of Taylor Swift’s albums, including CDs and DVDs? Round a number to 2 decimal places. (2 marks)

C3.For each book title with more than 2 copies, list the names of library members who have borrowed them and have not returned. Sort the results in ascending order by Title and then in ascending order by Last Name. (2 marks)

C4. The librarian manager is interested to know yearly borrowing volume by Major. Present a report displaying the Major, Year, and the number of loans. Sort the results in ascending order by Major and then in ascending order by Year. (2 marks)

C5. Produce a report showing all books that been borrowed in the past by “Yi-Te Chiu”.Include Title in your query results. (2 marks).