Write a query that JOINS two tables to list all of the albums and their respective artists. The results…

Write a query that JOINS two tables to list all of the albums and their respective artists. The results…

Question:

     

Transcribed Image Text:

Write a query that JOINS two tables to list all of the albums and their respective artists. The
results should include the album id, the album name, and the artist. The query should show all rows in the album table including albums without an artist listed (Unmatched Rows). Ensure
that your column names are formatted according to the output below.
Your output should look like this:
Album No# Album Name
Now 27
Dangerously in Love Beyonce
1
2
3
4
Artist
Rumours
Thriller
Fleetwood Mac
Michael Jackson 5) Write a query that JOINS all tables created in the previous lab to display the data in a similar
format to music_basic. When you are joining your tables ensure that you are joining records with
valid albums, artists, and genres. (Only matching rows)
NOTE: You are not using the music_basic table, but rather JOINing the artists, genre, album,
and songs tables to output a table in the same format. Modify your column headers to produce a
readable table that looks like this:
(Hint: SELECT * FROM music_basic to get an idea for how your output should look. There are
multiple ways to do this!)
Id
1
2
4
5
Title
Where Does
My Heart
Beat Now
The Last to
Know
Call Me
Maybe
Have a
Heart
Artist
Genre
Celine Dion Pop
Celine Dion Pop
Katy Perry Pop
Celine Dion Pop
Length
4.33
4.36
3.13
4.16
Release Year
1990
1990
2011
1991
Album
Unison
Unison
Now 27
Unison 6) Some songs were not released as part of an album and thus are noted with NULL for their
album_id. INSERT a new album called ‘Single’ and UPDATE songs with a NULL album_id to
belong to this new album. (Include screenshots of the relevant queries used and their output) 7) Rerun your query from Question 5 but sort by song id in descending order.
Artist Genre Length Release Year Album
Andrea
Classical 4.55
2007
Bocelli
The
Beatles
Id Title
41
La Voce Del
Silenzio
I want to Hold
Your Hand
All I want for
39 Christmas Is
40
38
You
Candle in the
Wind 1997
Mariah
Carey
Elton
John
Rock
2.24
Christmas 4.01
Pop
3.59
1963
1994
1997
I
The Best of Andrea Becelli:
Vivere
Meet the
Beatles!
Single
Single Write a query that JOINS two tables to list all of the albums and their respective artists. The
results should include the album id, the album name, and the artist. The query should show all rows in the album table including albums without an artist listed (Unmatched Rows). Ensure
that your column names are formatted according to the output below.
Your output should look like this:
Album No# Album Name
Now 27
Dangerously in Love Beyonce
1
2
3
4
Artist
Rumours
Thriller
Fleetwood Mac
Michael Jackson 5) Write a query that JOINS all tables created in the previous lab to display the data in a similar
format to music_basic. When you are joining your tables ensure that you are joining records with
valid albums, artists, and genres. (Only matching rows)
NOTE: You are not using the music_basic table, but rather JOINing the artists, genre, album,
and songs tables to output a table in the same format. Modify your column headers to produce a
readable table that looks like this:
(Hint: SELECT * FROM music_basic to get an idea for how your output should look. There are
multiple ways to do this!)
Id
1
2
4
5
Title
Where Does
My Heart
Beat Now
The Last to
Know
Call Me
Maybe
Have a
Heart
Artist
Genre
Celine Dion Pop
Celine Dion Pop
Katy Perry Pop
Celine Dion Pop
Length
4.33
4.36
3.13
4.16
Release Year
1990
1990
2011
1991
Album
Unison
Unison
Now 27
Unison 6) Some songs were not released as part of an album and thus are noted with NULL for their
album_id. INSERT a new album called ‘Single’ and UPDATE songs with a NULL album_id to
belong to this new album. (Include screenshots of the relevant queries used and their output) 7) Rerun your query from Question 5 but sort by song id in descending order.
Artist Genre Length Release Year Album
Andrea
Classical 4.55
2007
Bocelli
The
Beatles
Id Title
41
La Voce Del
Silenzio
I want to Hold
Your Hand
All I want for
39 Christmas Is
40
38
You
Candle in the
Wind 1997
Mariah
Carey
Elton
John
Rock
2.24
Christmas 4.01
Pop
3.59
1963
1994
1997
I
The Best of Andrea Becelli:
Vivere
Meet the
Beatles!
Single
Single

Expert Answer:

Answer rating: 100% (QA)

1 To join the two tables and list all of the albums and their respective artists we can use the following SQL query SELECT album id title artist name FROM albums INNER JOIN artists ON albums artist id
View the full answer