Write a query that JOINS two tables to list all of the albums and their respective...
Fantastic news! We've Found the answer you've been seeking!
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 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 albumid title artistname FROM albums INNER JOIN artists ON albumsartistid art... View the full answer
Related Book For
Vector Mechanics for Engineers Statics and Dynamics
ISBN: 978-0073398242
11th edition
Authors: Ferdinand Beer, E. Russell Johnston Jr., David Mazurek, Phillip Cornwell, Brian Self
Posted Date:
Students also viewed these databases questions
-
Explain three (3) benefits of entrepreneurs , who create business plans before launching their companies reap?
-
In this assignment, you will be asked to write SQL queries to get relevant information from the database you built from Assignment 3 and therefore to answer some questions pertaining to the S record...
-
Examine a company where a leader effectively managed a crisis. What leadership traits and strategies did they employ to navigate the crisis and maintain team morale? Explain
-
Match the items below by entering the appropriate code letter in the space provided. An entry that involves three or more accounts Transferring journal entries to ledger accounts The side which...
-
Determine the tension in cables BD and CD and the x, y, z components of reaction at the ball-and-socket joint at A. Given: F = 300 N a = 3 m b = 1 m c = 0.5 m d = 1.5 m D
-
Some fusion reactors use an induction process in which thermal neutrons collide with deuterium particles that are essentially twice as massive ( consisting of one proton and one neutron ) . Consider...
-
Identify treatments and experimental units in the following scenarios. a. City A would like to evaluate whether a new employment training program for the unemployed is more effective compared to the...
-
In a pure exchange economy with two goods, G and H, the two traders have Cobb-Douglas utility functions. Suppose that Tony's utility function is Ut = GtHt and Margaret's utility function is Um =...
-
SportsWorld purchased equipment costing $10,000. The equipment has a residual value of $1,000, and an estimated useful life of 5 years or 36,000 shoes. Actual units produced during the year were...
-
For the simple pendulum shown in Figure CP2.7, the nonlinear equation of motion is given by where L = 0.5 m, m = 1 kg, and g = 9.8 m/s2. When the nonlinear equation is linearized about the...
-
You've hired a contractor, Hamid s Hammers, to outfit the interior of your new business. After seeing the very poor quality of his work, you now want to cancel the contract on the grounds that there...
-
A machine was bought on credit for 15,000 from the XY Manufacturing Co Ltd, on 1 October 2015. The estimated useful economic life of the machine was seven years and the estimated scrap value 1,000....
-
Yuan Ltd has an accounting year ended 28 February 2015. Due to staff shortages, the stocktaking had not been undertaken until 9 March 2015 and the inventory valued at this date is 100,600. This value...
-
From the following figures calculate the closing inventory-in-trade that would be shown using (1) FIFO, (2) LIFO, (3) AVCO methods. Bought Sold March 400 at 30 each December 420 for 40 each September...
-
Mary Smith commenced trading on 1 September 2016 as a distributor of the Straight Cut garden lawn mower, a relatively new product which is now becoming increasingly popular. Upon commencing trading,...
-
The trial balance for a small business at 31 August 2015 is as follows: At 31 August 2015 there was: Inventory valued at cost prices 9,100 Accrued rent of 400 Prepaid business rates of 300 The...
-
Think about the internal controls you have experienced at the theater when responding to this discussion scenario. The Big Screen is a movie theater. On several occasions Chuck Todd, the owner,...
-
Consider the function f and its graph. a. Estimate the zeros of the area function b. Estimate the points (if any) at which A has a local maximum or minimum. c. Sketch a graph of A, for 0 x 10,...
-
Determine the components of the reactions at A and E, (a) If the 800-N load is applied as shown, (b) If the 800-N load is moved along its line of action and is applied at point D. S00 N 200 mm 300...
-
A 900-lb space vehicle traveling with a velocity v0 = (1500 ft/s)k passes through the origin O. Explosive charges then separate the vehicle into three parts A, B, and C, with masses of 150 lb, 300...
-
Locate the centroid of the plane area shown. 60 mm 60 mm
-
A football is thrown on a long pass. Compared to the balls initial horizontal velocity component, the velocity at the highest point is (a) greater, (b) less, (c) the same.
-
What process do you follow to make decisions for these different circumstances, and are you satisfied with the results?
-
A football is thrown on a long pass. Compared to the balls initial vertical velocity, the vertical component of its velocity at the highest point is (a) greater, (b) less, (c) the same.
Study smarter with the SolutionInn App