Skip to content
Advertisement

SQL QUERY : Find for each year copies sold > 10000

I am practicing a bit with SQL and I came across this exercise:

Consider the following database relating to albums, singers and sales:

Album (Code, Singer, Title) Sales (Album, Year, CopiesSold) with a constraint of referential integrity between the Sales Album attribute and the key of the Album report. Formulate the following query in SQL :

  • Find the code and title of the albums that have sold 10,000 copies every year since they came out.

I had thought of solving it like this:

Can you help me with this? Thanks.

Advertisement

Answer

You can do this with an INNER JOIN, GROUP BY, and HAVING.

The HAVING clause will filter out albums whose minimum Copies Sold are < 10000.

EDIT

There was also a question about gaps in the Sales data, there are a number of ways to modify the above query to solve for this as well. One solution would be to use an embedded query to identify the correct number of years.

This solution assumes that at least one album by some artist was sold each year (a fairly safe bet). If you had a Years table there are simpler solutions. If the data is current there are also solutions that utilize DATEDIFF.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement