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:

SELECT CODE, TITLE, COUNT (*)
FROM ALBUM JOIN SALES ON ALBUM.Code = SALES.Album
WHERE CopiesSold > 10000
HAVING COUNT(*) = /* Select difference from current year and came out year.*/

Can you help me with this? Thanks.

Answer

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

SELECT A.Code, A.Title
FROM ALBUM A
INNER JOIN SALES S ON S.Album = A.Code
GROUP BY A.Code, A.Title
HAVING MIN(S.CopiesSold) >= 10000

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.

SELECT A.Code, A.Title
FROM ALBUM A
INNER JOIN SALES S ON S.Album = A.Code
GROUP BY A.Code, A.Title
HAVING MIN(S.CopiesSold) >= 10000 AND
       COUNT(*) = (SELECT COUNT(DISTINCT Year) FROM SALES WHERE Year >= MIN(s.Year))

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.