Question

11. For each song in the songs table, update the value of artist_id to the id of the corresponding artist in the artists table. 11.1. You must update all songs using only one UPDATE statement and a subquery. Hardcoded ids or names are not allowed in your query. 12. For each song in the songs table, update the value of genre_id to the id of the corresponding genre in the genres table. 12.1. You must update all songs using only one UPDATE statement and a subquery. Hardcoded ids or genres are not allowed in your query.

Answer 1

Below is the Update query for the question 11.1:

UPDATE songs
SET artist_id = (
SELECT artist.id
FROM artist
WHERE artist.name = songs.artist
);

Below is the Update query for the question 12.1;

UPDATE songs
SET genre_id = (
SELECT genre.id
FROM genre
WHERE genre.name = songs.genre
);

Explanation :

In the above queries the columns artist_id and genre_id of table songs is updated by using Update statement and SUBQUERY in which select statement is used to get the genre id and artist id from the genre and artist tables by using the condition where genre names and artist names are equal to respective names in the songs table.

Please refer to the snapshot below for the above update queries:

UPDATE songs SET artist_id =( SELECT artist.id FROM artist WHERE artist.name = songs.artist ); UPDATE songs SET genre_id = ( SELECT genre.id FROM genre WHERE genre.name = songs.genre ; SELECT * FROM songs;

$sqlite3 database. sdb <main.sql 1 Love Taylor_swift|3|41|0.Pop|1|10 3 Maps Maroon5|35|0. Pop|3|10 4 Under Pressure Queen |3|46|0.Rock|430 5 Blue_Danube Strauss 3.58 0.0 Pop 5 10

Explanation :

Three tables are created with the below description:

1) Table1 : artist

Column names: id , name

Query for creating and inserting the sample data in table artist :

CREATE TABLE artist(id integer PRIMARY KEY, name varchar(50));

INSERT INTO artist VALUES(1,'Taylor_swift');
INSERT INTO artist VALUES(3,'Maroon5');
INSERT INTO artist VALUES(4,'Queen');
INSERT INTO artist VALUES(5,'Strauss');

2) Table1 : genre

Column names: id , name

Query for creating and inserting the sample data in table genre:

CREATE TABLE genre(id integer PRIMARY KEY, name varchar(50));

INSERT INTO genre VALUES(10,'Pop');
INSERT INTO genre VALUES(30,'Rock');
INSERT INTO genre VALUES(40,'Classical');
INSERT INTO genre VALUES(50,'Funk');

2) Table1 : songs

Column names: id , title, artist, minutes, seconds, sales, genre, artist_id, genre_id

Query for creating and inserting the sample data in table songs:

CREATE TABLE songs(id integer PRIMARY KEY, title varchar(50), artist varchar(50), minutes integer, seconds integer, sales float, genre varchar(50), artist_id integer, genre_id integer);

INSERT INTO songs VALUES(1,'Love','Taylor_swift',3,41,0.00,'Pop',0,0);
INSERT INTO songs VALUES(3,'Maps','Maroon5',3,5,0.00,'Pop',0,0);
INSERT INTO songs VALUES(4,'Under_Pressure','Queen',3,46,0.00,'Rock',0,0);
INSERT INTO songs VALUES(5,'Blue_Danube','Strauss',3,58,0.00,'Pop',0,0);

Please refer to the snapshot of the SQL code and the execution result:

CREATE TABLE artist(id integer PRIMARY KEY, name varchar(50)); INSERT INTO artist VALUES (1, 'Taylor swift' INSERT INTO artist VALUES , 'Maroon5'); INSERT INTO artist VALUES 4, 'Queen INSERT INTO artist VALUES (5, 'Strauss) $sqlite3 database. sdb < main.sql 11 Love Taylor_swift|3|41|0.Pop|1|10 31 Maps | Maroon5|31510.0 Pop|3|10 4 Under Pressure Queen |3|46|0. Rock|4|30 5 Blue_Danube Strauss 31580. Pop|5|10 CREATE TABLE genre(id integer PRIMARY KEY, name varchar(50)); INSERT INTO genre VALUES (10, 'Pop'); INSERT INTO genre VALUES 30, 'Rock" INSERT INTO genre VALUES 40, 'Classical INSERT INTO genre VALUES (50, 'Funk'); CREATE TABLE songs (id integer PRIMARY_KEY, title varchar(50), artist varchar, (50), minutes integer, seconds integer, sales float, genre varchar(50), artist_id integer, genre_id integer); INSERT INTO songs VALUES(1, 'Love', 'Taylor_swift',3,41,0.00, Pop',0,0); INSERT INTO songs VALUES(3, 'Maps', 'Maroon5',3,5,0.00, 'Pop',0,0); INSERT INTO songs VALUES(4, 'Under Pressure', 'Queen',3,46,0.00, Rock',0,0); INSERT INTO songs VALUES(5, 'Blue Danube', 'Strauss',3,58,0.00, 'Pop',0,0); UPDATE songs SET artist id = ( SELECT artist.id FROM artist WHERE artist.name = songs.artist UPDATE songs SET genre_id = ( SELECT genre.id FROM genre WHERE genre.name = songs.genre ); SELECT * FROM songs;