Hello! I have attempted to answer the two subquery challenges in BOLD at the end of the database created below, but I am unsure of my answers. I don't have SQL available to execute the subqueries to make sure they will work, so can somebody test their functionality and advise if they are coded correctly? If not, can you please provide correction?

Database coding in REGULAR text (NOT bold or italic)

Questions in BOLD text

Attempted answers in ITALIC text

/***************************************************************************/

/* SELECT AND EXECUTE THIS BLOCK TO CREATE THE ONLINEMUSIC DATABASE */

/***************************************************************************/

-- Set Context to Master

USE MASTER;

GO

-- If the OnlineMusic database exists, delete it.

IF EXISTS (SELECT * FROM Master.dbo.sysdatabases WHERE NAME = 'OnlineMusic')

DROP DATABASE OnlineMusic;

--Create the OnlineMusic Database

CREATE DATABASE OnlineMusic;

GO

-- CREATE TABLES IN THE ONLINE MUSIC DATABASE

USE OnlineMusic;

-- Create the ARTISTS table

-- schema: ARTISTS (ArtistID, ArtistName)

CREATE TABLE tblArtists -- Create the Artists table

(

ArtistID Int PRIMARY KEY,

ArtistName Varchar(129)

);

-- Insert a data element into the ARTISTS table

INSERT INTO tblArtists VALUES

(1 , 'Lady Gaga' ),

(2 , 'Phil Collins' ),

(3 , 'Maroon 5' ),

(4 , 'Elvis Presley' ),

(5 , 'The Beatles' ),

(6 , 'Paul McCartney' ),

(7 , 'John Lennon' ),

(8 , 'Frank Sinatra' ),

(9 , 'James Brown' ),

(10, 'Taylor Swift' ),

(11, 'Beyance' ),

(12, 'Lorde' ),

(14, 'Bruno Mars' ),

(15, 'Adele' ),

(16, 'Mariah Cary' ),

(17, 'Phil Collins' ),

(18, 'Charlie XCS' ),

(19, 'Sam Smith' ),

(20, 'Magic!' ),

(21, 'Aretha Franklin');

-- Create the SONGS table

-- schema: SONGS (SongID, SongTitle)

CREATE TABLE tblSongs

(

SongID int PRIMARY KEY,

SongTitle varchar (128) NOT NULL

);

-- Insert data into the SONGS table

-- schema: SONGS (SongID, SongTitle)

INSERT INTO tblSongs VALUES

(101, 'I Wanna Hold Your Hand' ),

(102, 'Please Please Me' ),

(103, 'Band on the Run' ),

(104, 'Starting Over' ),

(105, 'Hard Day''s Night' ),

(106, 'Hound Dog' ),

(107, 'Poker Face' ),

(108, 'All You Need is Love' ),

(109, 'One More Night' ),

(110, 'That''s All Right, Mama' ),

(111, 'One More Night' ),

(112, 'Born This Way' ),

(113, 'My Way' ),

(114, 'Another Day in Paradise' ),

(115, 'You''ll Be in My Heart' ),

(116, 'I Don''t Care Anymore' ),

(117, 'Begin the Beguine' ),

(118, 'White Christmas' );

-- Create the RECORD LABELS table

-- schema: RECORDLABELS (LabelID, LabelName)

CREATE TABLE tblRecordLabels -- Create the RecordLabels Table

(

LabelID INT PRIMARY KEY,

LabelName VARCHAR(128) NOT NULL

);

-- Insert data into the RECORD LABELS table

-- schema: RECORDLABELS (LabelID, LabelName)

INSERT INTO tblRecordLabels VALUES

(1001, 'Capital Records' ),

(1002, 'Apple Records' ),

(1003, 'Sun Records' ),

(1004, 'RCA Records' ),

(1006, 'Virgin Records' ),

(1007, 'Walt Disney Records'),

(1008, 'Atlantic Records' ),

(1009, 'Rhino Records' ),

(1010, 'EMI' ),

(1011, 'PolyGram' );

-- Create the TRACKS table

-- Schema TRACKS (PK:TrackID identity(10000, 1), AK(ArtistID, SongID, LabelID), TrackSales default 0)

-- FK SongID references SONGS

-- FK ArtistID references ARTISTS

-- FK LabelID references RECORDLABELS

-- unique (ArtistID, SongID, LabelID)

-- not null ArtistID, SongID, LabelID

CREATE TABLE tblTracks

( TrackID int IDENTITY (10001, 1) PRIMARY KEY ,

ArtistID int NOT NULL REFERENCES tblArtists ,

SongID int NOT NULL REFERENCES tblSongs ON DELETE CASCADE,

LabelID int NOT NULL REFERENCES tblRecordLabels ,

TrackSales int DEFAULT 0 ,

UNIQUE (ArtistID, SongID, LabelID)

);

-- Insert Data in to the TRACKS table

INSERT INTO tblTracks VALUES

( 1, 105, 1001, 1),

( 2, 105, 1001, 3),

( 3, 106, 1001, 2),

( 4, 107, 1002, 9),

( 5, 105, 1003, 5),

( 6, 104, 1004, 7),

( 7, 101, 1003, 6),

( 8, 105, 1004, 6),

( 9, 103, 1004, 0),

(10, 104, 1003, 8),

(11, 102, 1002, 9),

(12, 102, 1003, 9),

( 5, 101, 1003, 6),

( 5, 102, 1003, 7),

( 5, 108, 1003, 0),

( 4, 106, 1002, 8),

( 4, 113, 1002, 4),

(11, 108, 1003, 3),

(19, 107, 1002, 4),

(19, 114, 1009, 11),

(21, 117, 1003, 17),

( 4, 104, 1003, 2),

( 7, 101, 1002, 3);

-- Create the TRANSACTIONS Table

-- Schema: TRANSACTIONS (TransactionID, TrackID, TrackSales)

-- FK TrackID reference TRACKS

CREATE TABLE tblTransactions

(TransactionID INT IDENTITY(3001,1) ,

TrackID INT REFERENCES tblTracks,

TransactionDate DATE ,

UnitsSold INT

);

-- Insert data into the TRANSACTIONS table

INSERT INTO tblTransactions VALUES

(10005, '2018-02-14', 02),

(10001, '2019-07-04', 05),

(10012, '2017-12-25', 01),

(10008, '2019-03-17', 04),

(10003, '2020-01-01', 03),

(10006, '2024-04-07' ,09),

(10017, '2015-02-22' ,04),

(10001, '2020-10-05' ,15),

(10009, '2024-06-09' ,19),

(10017, '2023-11-06' ,03),

(10010, '2019-01-28' ,04),

(10014, '2020-05-07' ,12),

(10003, '2015-08-10' ,14),

(10006, '2016-08-22' ,13),

(10004, '2014-04-19' ,16),

(10008, '2024-02-10' ,02),

(10001, '2024-07-05' ,09),

(10016, '2018-11-01' ,10),

(10002, '2016-09-04' ,12),

(10008, '2023-07-28' ,05),

(10008, '2019-10-16' ,05),

(10007, '2025-12-22' ,09),

(10003, '2014-07-29' ,17),

(10006, '2016-02-05' ,11),

(10010, '2019-12-21' , 6),

(10004, '2024-12-15' , 5),

(10016, '2014-05-29' ,16),

(10005, '2024-05-01' , 4),

(10003, '2014-05-25' , 7),

(10012, '2017-07-29' , 6),

(10003, '2017-12-27' , 5),

(10009, '2022-06-07' , 9),

(10014, '2022-09-24' , 2),

(10006, '2017-01-11' ,17),

(10001, '2020-04-13' , 5),

(10004, '2025-02-03' ,17),

(10011, '2016-05-18' ,13),

(10009, '2017-05-02' , 7),

(10015, '2024-09-14' ,12),

(10016, '2014-05-05' , 1),

(10005, '2025-05-12' ,2),

(10013, '2021-05-15' , 5),

(10009, '2020-11-19' ,16),

(10008, '2015-02-13' , 9),

(10014, '2019-05-13' ,20),

(10017, '2018-08-24' , 6),

(10001, '2022-11-06' ,16),

(10010, '2017-02-22' , 6),

(10012, '2018-01-15' , 7),

(10012, '2021-10-08' ,12),

(10007, '2025-04-06' , 4),

(10001, '2017-06-01' ,15),

(10013, '2014-12-12' ,16),

(10012, '2025-09-23' ,12),

(10017, '2025-07-22' , 3),

(10016, '2025-08-14' , 8),

(10011, '2023-08-01' , 6),

(10003, '2022-12-17' , 7),

(10017, '2015-12-08' ,12),

(10011, '2020-09-16' , 3),

(10016, '2015-08-20' , 8),

(10003, '2019-08-22' , 9),

(10018, '2022-03-19' , 5),

(10014, '2025-02-03' ,17),

(10016, '2018-09-02' ,16),

(10014, '2015-07-27' ,11),

(10006, '2023-01-27' ,16),

(10012, '2015-11-22' ,14),

(10015, '2021-01-04' ,19),

(10009, '2017-02-10' ,18),

(10009, '2019-03-23' , 8),

(10016, '2022-03-05' , 2),

(10015, '2025-03-17' , 2),

(10002, '2022-11-08' , 4),

(10012, '2016-02-22' , 7),

(10012, '2015-02-15' ,18),

(10014, '2024-01-01' , 5),

(10009, '2021-02-23' , 17),

(10002, '2025-10-09' , 8),

(10014, '2022-07-29' ,14),

(10005, '2024-11-23' , 6),

(10002, '2024-09-26' ,16),

(10002, '2022-03-05' , 9),

(10006, '2021-08-03' , 8),

(10006, '2022-05-12' ,19),

(10006, '2017-09-07' ,13),

(10017, '2023-11-05' ,11),

(10004, '2017-09-20' ,19),

(10001, '2020-12-15' ,14),

(10017, '2025-10-25' ,12),

(10004, '2022-10-04' , 2),

(10013, '2016-10-09' ,16),

(10012, '2016-11-19' , 9),

(10007, '2022-02-15' , 5),

(10010, '2021-10-02' ,10),

(10018, '2023-04-13' , 1),

(10005, '2021-07-17' ,11),

(10015, '2014-10-15' ,18),

(10010, '2015-12-27' ,19),

(10009, '2020-05-22' , 4),

(10003, '2024-08-08' ,15),

(10003, '2019-11-16' ,20),

(10007, '2024-04-13' , 4),

(10015, '2014-05-21' ,11),

(10009, '2019-08-03' , 5),

(10009, '2022-04-17' ,15),

(10017, '2017-02-06' , 1),

(10015, '2022-08-08' ,16),

(10005, '2018-02-20' , 6),

(10015, '2025-05-10' , 6),

(10004, '2016-08-05' ,20);

-- Display the contents of the four tables

SELECT * FROM tblArtists;

SELECT * FROM tblSongs;

SELECT * FROM tblRecordLabels;

SELECT * FROM tblTracks;

SELECT * FROM tblTransactions;

GO

/**************************************************************/

/* END OF BLOCK TO CREATE THE ONLINEMUSIC DATABASE */

/**************************************************************/

--------------------------------------------------------------------------------

-- CHALLENGE 1. WRITE A CORRELATED SUBQUERY TO ANSWER THE BUSINESS QUESTION --

--------------------------------------------------------------------------------

-- A "Best-of" track for a given artist is a track that sells more copies than the average track sales for that artist.

-- BUSINESS QUESTION: What are the "Best-of" tracks for each Artist?

-- SHOW: Artist ID,

-- Artist Name

-- Song Title

-- Track Sales

SELECT ArtistID, ArtistName, SongTitle, TrackSales

FROM tblTracks trkouter

JOIN tblSongs s ON s.SongID = trkouter.SongID

JOIN tblArtists l ON l.ArtistID = trkouter.ArtistID

WHERE TrackSales >

( SELECT AVG(trkinner.TrackSales)

FROM tblTracks trkinner

WHERE trkouter.ArtistID = trkinner.ArtistID

)

ORDER BY ArtistName;

--------------------------------------------------------------------------------

-- CHALLENGE 2. WRITE A CORRELATED SUBQUERY TO ANSWER THE BUSINESS QUESTION --

--------------------------------------------------------------------------------

-- BUSINESS QUESTION: What are the total track sales for each artist?

-- SHOW: ArtistID,

-- ArtistName,

-- Total Track Sales for the artist AS TotalTrackSales

SELECT ArtistID, ArtistName, TotalTrackSales

FROM tblTracks t

JOIN tblSongs s ON s.SongID = t.SongID

JOIN tblArtists l ON l.ArtistID = t.ArtistID

/**************************************************************/