I'm supposed to write a query for this statement:
List the names of customers, and album titles, for cases where the customer has bought the entire album (i.e. all tracks in the album)
FROM (Customer C, Invoice I, InvoiceLine IL, Track T, Album Al) AS R1
AND NOT EXISTS (
FROM (Album Al, Track T) AS R2
AND R2.Title NOT IN (
FROM (Album Al, Track T) AS R3
misuse of aggregate function COUNT()
You cannot alias a table list such as
(Album Al, Track T) which is an out-dated syntax for
(Album Al CROSS JOIN Track T). You can either alias a table, e.g.
Album Al or a subquery, e.g.
(SELECT * FROM Album CROSS JOIN Track) AS R2.
So first of all you should get your joins straight. I don't assume that you are being taught those old comma-separated joins, but got them from some old book or Website? Use proper explicit joins instead.
Then you cannot use
WHERE COUNT(R1.TrackId) = COUNT(R3.TrackId).
COUNT is an aggregate function and aggregation is done after
As to the query: It's a good idea to compare track counts. So let's do that step by step.
Query to get the track count per album:
select albumid, count(*) from track group by albumid;
Query to get the track count per customer and album:
select i.customerid, t.albumid, count(distinct t.trackid) from track t join invoiceline il on il.trackid = t.trackid join invoice i on i.invoiceid = il.invoiceid group by i.customerid, t.albumid;
select c.firstname, c.lastname, a.title from ( select i.customerid, t.albumid, count(distinct t.trackid) as cnt from track t join invoiceline il on il.trackid = t.trackid join invoice i on i.invoiceid = il.invoiceid group by i.customerid, t.albumid ) bought join ( select albumid, count(*) as cnt from track group by albumid ) complete on complete.albumid = bought.albumid and complete.cnt = bought.cnt join customer c on c.customerid = bought.customerid join album a on a.albumid = bought.albumid;