Question

I need help in SQL management studio please. Question: Create the same query as query 5, but only include artists if they have 'Pop' Genre.

Previous question:

Create a list of all composers and artists in your database. Include two columns in your record set (the names of the returned columns are in brackets): 'Composer/Artist Name' and 'Type' (which will indicate whether your results are from the composer or artist table). Order by 'Composer/Artist Name'.

This is the answer for query 5:

SELECT ArtistName AS Composer_Artist_Name,'Artist' AS TYPE FROM tblArtist
UNION (SELECT ComposerName AS Composer_Artist_Name,
'Composer' AS TYPE FROM tblComposer)
ORDER BY Composer_Artist_Name;

This is the create script for the database:

CREATE TABLE tblArtist (
   ArtistID INT NOT NULL PRIMARY KEY,
   ArtistName VARCHAR(100),
   Genre VARCHAR(50));

CREATE TABLE tblComposer (
   ComposerID INT NOT NULL PRIMARY KEY,
   ComposerName VARCHAR(100));

CREATE TABLE tblSong (
   SongID INT NOT NULL PRIMARY KEY,
   SongName VARCHAR(255),
   DateReleased DATE,
   ArtistID INT REFERENCES tblArtist(ArtistID),
   ComposerID INT REFERENCES tblComposer(ComposerID),
   Cost NUMERIC(6,2));

CREATE TABLE tblPlaylist (
   PlaylistID INT NOT NULL PRIMARY KEY,
   PlayListName VARCHAR(255),
   DateCreated DATE);

CREATE TABLE tblPlaylistSongs (
   PlaylistSongID INT NOT NULL PRIMARY KEY,
   PlayListID INT REFERENCES tblPlaylist(PlaylistID),
   SongID INT REFERENCES tblSong(SongID));

--Insert records into tables
INSERT INTO tblArtist VALUES (1, 'Elvis Presley', 'Rock and Roll');
INSERT INTO tblArtist VALUES (2, 'Michael Jackson', 'Pop');
INSERT INTO tblArtist VALUES (3, 'Led Zepplin', 'Rock and Roll');
INSERT INTO tblArtist VALUES (4, 'Bruce Springsteen', 'Rock and Roll');
INSERT INTO tblArtist VALUES (5, 'Steppenwolf', 'Rock and Roll');
INSERT INTO tblArtist VALUES (6, 'The Beatles', 'Pop');
INSERT INTO tblArtist VALUES (7, 'Madonna', 'Pop');
INSERT INTO tblArtist VALUES (8, 'Johnny Cash', 'Rock and Roll');
INSERT INTO tblArtist VALUES (9, 'Prince', 'Pop');

INSERT INTO tblComposer VALUES (1, 'Jeremy Leiber');
INSERT INTO tblComposer VALUES (2, 'Mike Stoller');
INSERT INTO tblComposer VALUES (3, 'Mark James');
INSERT INTO tblComposer VALUES (4, 'June Carter Cash');
INSERT INTO tblComposer VALUES (5, 'Johnny Cash');
INSERT INTO tblComposer VALUES (6, 'Rod Temperton');
INSERT INTO tblComposer VALUES (7, 'Michael Jackson');
INSERT INTO tblComposer VALUES (8, 'Siedah Garrett');
INSERT INTO tblComposer VALUES (9, 'Madonna');
INSERT INTO tblComposer VALUES (10, 'Peter Brown');

INSERT INTO tblSong VALUES (2, 'Hound Dog', '1969-01-01', 1, 1, 1.49);
INSERT INTO tblSong VALUES (3, 'Jailhouse Rocker', '1957-01-01', 1, 2, 1.29);
INSERT INTO tblSong VALUES (4, 'Always on My Mind', '1973-01-01', 1, 3, 0.99);
INSERT INTO tblSong VALUES (5, 'Ring of Fire', '1963-01-01', 8, 4, 1.49);
INSERT INTO tblSong VALUES (6, 'Folsom Prison Blues', '1957-01-01', 8, 5, 1.29);
INSERT INTO tblSong VALUES (7, 'Man in Black', '1971-01-01', 8, 5, 0.99);
INSERT INTO tblSong VALUES (8, 'Billie Jean', '1982-01-01', 2, 7, 1.29);
INSERT INTO tblSong VALUES (9, 'Thriller', '1982-01-01', 2, 6, 1.29);
INSERT INTO tblSong VALUES (10, 'Man in the Mirror', '1987-01-01', 2, 8, 0.99);
INSERT INTO tblSong VALUES (11, 'Like a Prayer', '1989-01-01', 7, 9, 1.29);
INSERT INTO tblSong VALUES (12, 'Material Girl', '1984-01-01', 7, 10, 1.29);
INSERT INTO tblSong VALUES (13, 'Express Yourself', '1989-01-01', 7, 9, 0.99);

INSERT INTO tblPlaylist VALUES (1, 'Rock and Roll Classics', '2018-04-05');
INSERT INTO tblPlaylist VALUES (2, 'Pop Classics', '2018-04-01');
INSERT INTO tblPlaylist VALUES (3, 'Mixed', '2018-04-15');

INSERT INTO tblPlaylistSongs VALUES (1, 1, 2);
INSERT INTO tblPlaylistSongs VALUES (2, 1, 4);
INSERT INTO tblPlaylistSongs VALUES (3, 1, 6);
INSERT INTO tblPlaylistSongs VALUES (4, 1, 3);
INSERT INTO tblPlaylistSongs VALUES (5, 2, 8);
INSERT INTO tblPlaylistSongs VALUES (6, 2, 9);
INSERT INTO tblPlaylistSongs VALUES (7, 2, 10);
INSERT INTO tblPlaylistSongs VALUES (8, 2, 12);
INSERT INTO tblPlaylistSongs VALUES (9, 2, 13);
INSERT INTO tblPlaylistSongs VALUES (10, 3, 2);
INSERT INTO tblPlaylistSongs VALUES (11, 3, 12);
INSERT INTO tblPlaylistSongs VALUES (12, 3, 3);
INSERT INTO tblPlaylistSongs VALUES (13, 3, 8);
INSERT INTO tblPlaylistSongs VALUES (14, 3, 11);
INSERT INTO tblPlaylistSongs VALUES (15, 3, 9);

Answer 1

/* Here is your edited query to include only result of pop genre*/

SELECT ArtistName AS Composer_Artist_Name,'Artist' AS TYPE FROM tblArtist WHERE tblArtist.Genre="Pop" UNION (SELECT ComposerName AS Composer_Artist_Name, 'Composer' AS TYPE FROM tblComposer) ORDER BY Composer_Artist_Name;