Using the database: Write SQL queries for each of the questions below. A) Find t
ID: 3872568 • Letter: U
Question
Using the database: Write SQL queries for each of the questions below.
A) Find the playlists where the composer is not specified for 10 or more tracks. Print the Id, and the name of such playlists. Number of rows returned in the result = 6
B) Find the playlists where all the tracks are by the same composer. Print the Id, and the name of such playlists. Number of rows returned in the result = 1 (4 is acceptable too)
TEE Track Trackld T Album Artist Artistld Name MediaType MediaTypeld Name Tite Artistld Albumld MediaTypeld Genreld Composer Milliseconds Genre Genreld Name Playlist Playlistd Name PlaylistTrack Playlistic Trackld Bytes UnitPrice Employee Employeeld LastName FirstName InvoiceLine InvoiceLineld Invoiceld Trackld UnitPrice Quantity Customer Customerld FirstName LastName Company Address ReportsTo BirthDate HireDate Address City State Country PostalCode Phone Fax Email Invoice Invoiceld Customerld InvoiceDate illingAddress BillingCity BillingState BillingCountry State Country PostalCode Fax SupportRepld TotalExplanation / Answer
1.)
SELECT pl.playlistid, pl.name
FROM playlist pl
WHERE pl.playlistid in
(
SELECT plt.playlistid
FROM playlisttrack plt
WHERE pl.playlistid = plt.playlistid and plt.trackid not in
(SELECT distinct(trackid) FROM invoiceline il));
2.)
SELECT pl.playlistid, pl.name
FROM playlist pl
WHERE pl.playlistid in
(SELECT pt.playlistid FROM
playlisttrack pt
WHERE pt.trackid in
(SELECT t.trackid
FROM track t
WHERE t.composer is null)
GROUP BY playlistid HAVING count(*)>10);
3.)
SELECT pl.playlistid, pl.name
FROM playlist pl
WHERE pl.playlistid in
(
SELECT plt.playlistid
FROM playlisttrack plt
WHERE pl.playlistid = plt.playlistid and plt.trackid not in
(SELECT distinct(trackid) FROM invoiceline il));
2.)
SELECT pl.playlistid, pl.name
FROM playlist pl
WHERE pl.playlistid in
(SELECT pt.playlistid FROM
playlisttrack pt
WHERE pt.trackid in
(SELECT t.trackid
FROM track t
WHERE t.composer is null)
GROUP BY playlistid HAVING count(*)>10);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.