Lab 8: More SQL Statements

Posted on | by nayeon

Overview

This lab helps you practice more SQL statements that manipulate the database, such as insert, update, and deleting records. You will write five SQL statements against the Song database.

Requirements

Create one .sql file for all five SQL statements. Use comments to write the prompt above each SQL statement.

Part 1 – Song Database

  1. Create a view mpeg_tracks that displays all tracks with MPEG audio file format. Display track name (alias: track_name) artist name (alias: artist_name), composer, album title (alias: album_title), and media type (alias: media_type). Sort results in alphabetical order by track name.

  2. Add a track below to the database:
    1. Track Title: The Ocean
    2. Album: The Song Remains The Same (Disc 1)
    3. Artist: Led Zeppelin
    4. Media Type: MPEG audio file
    5. Genre: Rock
    6. Composer: John Bonham/John Paul Jones/Robert Plant
    7. Milliseconds: 248000
    8. Bytes: 7990000
    9. Unit Price: 0.99
  3. Make the following changes to the track added above:
    New Bytes: 8998765
    New Unit Price: 1.99
  4. Delete track named “20 Flight Rock” composed by Ned Fairchild from the database.
  5. Display how many tracks there are for each album. Show album ID, album title (alias: album_title), and track count (alias: track_count).
    1. Note: some of your counts might be different due to added or deleted records during in-class exercises.

Submission

When complete, you must upload the completed .sql file to Blackboard. Go to Assignments/Labs -> Lab 8: More SQL Statements and upload the file.