Question

5. Write the SQL to list the Total Tracks and Music Label for all CDs with less than 10 tracks.
6. Write the SQL to add the following CD information to this database.

label O cd track bltitle VARCHAR(15) cdid INT(11) trkid INT(11) lblstreet VARCHAR(25) cdlblid VARCHAR(10) trknum INT(11) Iblci ty VARCHAR(20) cdtitle VARCHAR (25) K0trktitle VARCHAR(25) lblstate VARCHAR(20) cdyear YEAR(4) trklength DECIMAL (4,2) lblpostcode VARCHAR(10) lbltitle VARCHAR (15) cdid INT(11) Iblnation VARCHAR(20) Indexes Indexes Indexes O

a. CD Title is Marvin Gaye’s Hits

b. From the year is 1988

c. The Music Label is Motown

d. The CD serialized code is WD72645

e. The Tracks and Track Lengths are: i

. Grapevine 3.12

ii. Let’s Get It On 3.55

iii. Too Busy Thinking 2.55

iv. How Sweet It Is 2.48

f. Now write the query to display these new tracks only

Answer 1

5.

SELECT cd.lbltitle, COUNT(track.trkid) AS Total_Tracks
FROM cd INNER JOIN track
ON cd.cdid = track.cdid
GROUP BY track.cdid
HAVING COUNT(track.cdid) < 10;

6.
i)
INSERT INTO label (lbtitle)
VALUES ('Motown');

INSERT INTO cd (cdlblid,cdtitle,cdyear,lbltitle)
VALUES ('WD72645', 'Marvin Gaye’s Hits', '1988', 'Motown');

INSERT INTO track (trktitle,trklength,cdid)
VALUES ('Grapevine',3.12,(SELECT cdid FROM cd WHERE cdtitle = 'Marvin Gaye’s Hits'));

INSERT INTO track (trktitle,trklength,cdid)
VALUES ('Let’s Get It On',3.55,(SELECT cdid FROM cd WHERE cdtitle = 'Marvin Gaye’s Hits'));

INSERT INTO track (trktitle,trklength,cdid)
VALUES ('Too Busy Thinking',2.55,(SELECT cdid FROM cd WHERE cdtitle = 'Marvin Gaye’s Hits'));

INSERT INTO track (trktitle,trklength,cdid)
VALUES ('How Sweet It Is',2.48,(SELECT cdid FROM cd WHERE cdtitle = 'Marvin Gaye’s Hits'));