-- Lab 2A - Single Table Select Statements -- 1. Select all rows and all columns

Question

-- Lab 2A - Single Table Select Statements -- 1. Select all rows and all columns in the Album table. -- 2. Select the TrackId, Name and Composer columns for all rows in the Track table. -- 3. Select all columns for all artists in the Artist table sorted by artist Name. -- 4. Select all columns for all albums in the Album table sorted first by artistId and then Title. -- 5. From the Track table, display the Name and Milliseconds for all tracks sorted by milliseconds highest to lowest. -- 6. Modify the query for number 5 so that only the 20 longest tracks are displayed. -- 7. Select the TrackId, Name and Composer for all tracks with in the Hip Hop/Rap genre (GenreId 17); -- 8. Select the TrackId, Name and Composer for all tracks that cost more than 99 cents -- 9. Select the TrackId, Name and Composer for all tracks that cost more than 99 cents in GenreId 21. -- 10. Select the TrackId, Name and GenreId for all tracks that cost more than 99 cents and are not in GenreId 21 -- 11. Select the TrackId, Name and GenreId for all tracks that have a GenreId of 12 or a GenreId of 17. -- 12. Select the TrackId, Name, and UnitPrice for all tracks that cost more than 99 cents and are either in GenreId 5 or GenreId 18. -- 13. Add a sort to the query from number 12 to sort by UnitPrice -- 14. List the title for all albums with U2 as the artist -- 15. List all of the tracks on the album titled 'Zooropa'.

Explanation / Answer

I am answering queries as per the SQL SERVER. Since no exact schema defintion is available, the queries are answered as per the specifications in the questions.

1. SELECT * FROM Album --> * Selects all columns
2. SELECT TrackId,Name, Composer FROM Track --> Selects the specified columns only
3. SELECT * FROM Artist order by ArtistName
4. SELECT * FROM Album order by ArtistId, Title
5. SELECT Name, Milliseconds FROM Track order by Milliseconds desc
6. SELECT TOP 20 Name, Milliseconds FROM Track order by Milliseconds desc --> Selects top 20 records
7. SELECT TrackId, Name, Composer WHERE GenreId = 17
8. SELECT TrackId, Name, Composer WHERE cost > 99
9. SELECT TrackId, Name, Composer WHERE cost > 99 AND GenreId = 21 -->Where clause with two conditions to satisfy simultaneously
10. SELECT TrackId, Name, Composer WHERE cost > 99 AND GenreId <> 21 --> <> is the not equal to operator
OR
SELECT TrackId, Name, Composer WHERE cost > 99 AND GenreId NOT IN 21

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.