FAME Outer JOIN Exercise SOLUTION(1)
docx
keyboard_arrow_up
School
Carnegie Mellon University *
*We aren’t endorsed by this school
Course
MISC
Subject
English
Date
Jul 1, 2024
Type
docx
Pages
2
Uploaded by JudgeCrocodile4335
xFAME Outer JOIN Exercise SOLUTION
Look at the structure of two tables: Artist_T and ContractedArtist_T
JOIN the tables and use * to see everything. Visually check the output to see how many artists have a contract. SELECT * FROM Artist_T
JOIN ContractedArtist_T ON Artist_T.ArtistID = ContractedArtist_T.ArtistID
Now change your JOIN so that you can also see artists in the output who don’t have a contract. Some artists should now have null values (-)
SELECT * FROM Artist_T
LEFT JOIN ContractedArtist_T ON Artist_T.ArtistID = ContractedArtist_T.ArtistID
Next, go back to the first statement, but now add in the ArtistManager_T table so that as part of all the output (use *) you can see the first and last name of the artist’s manager. SELECT * FROM Artist_T
JOIN ContractedArtist_T ON Artist_T.ArtistID = ContractedArtist_T.ArtistID
JOIN ArtistManager_T ON ContractedArtist_T.AManagerID=ArtistManager_T.AManagerID
Now include the artists who do not have a contract. Notice that with the first JOIN you created a new table. Now that you are joining a third table, you will have to do a LEFT JOIN again. After you have reviewed the result from using *, change the output so that it only shows the artistID, the first and last name for the artist, and the first and last name for the manager. Organize the output by ArtistID
SELECT Artist_T.ArtistID, Artist_T.FirstName, Artist_T.Lastname, ArtistManager_T.FirstName, ArtistManager_T.LastName FROM Artist_T
LEFT JOIN ContractedArtist_T ON Artist_T.ArtistID = ContractedArtist_T.ArtistID
LEFT JOIN ArtistManager_T ON ContractedArtist_T.AManagerID=ArtistManager_T.AManagerID
ORDER BY Artist_T.ArtistID
Sigh and feel a sense of accomplishment. How could you make the output distinguish between the names of the Artist and the Manager? SELECT Artist_T.ArtistID, Artist_T.FirstName, Artist_T.Lastname, ArtistManager_T.FirstName AS Manager FirstName, ArtistManager_T.LastName AS ManagerLastName FROM Artist_T
LEFT JOIN ContractedArtist_T ON Artist_T.ArtistID = ContractedArtist_T.ArtistID
LEFT JOIN ArtistManager_T ON ContractedArtist_T.AManagerID=ArtistManager_T.AManagerID
ORDER BY Artist_T.ArtistID
Use Double Quotes to leave spaces in the output labels. SELECT Artist_T.ArtistID, Artist_T.FirstName, Artist_T.Lastname, ArtistManager_T.FirstName AS "Manager First name", ArtistManager_T.LastName AS "Manager Last name" FROM Artist_T
LEFT JOIN ContractedArtist_T ON Artist_T.ArtistID = ContractedArtist_T.ArtistID
LEFT JOIN ArtistManager_T ON ContractedArtist_T.AManagerID=ArtistManager_T.AManagerID
ORDER BY Artist_T.ArtistID
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help