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.

Submit your SQL .sql file on Blackboard.

Requirements

  1. Create a view mpeg_tracks that displays all tracks with MPEG audio file format. Display track name (track_name) artist name (artist_name), composer, album title (album_title), and media type (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 20 Flight Rock by BackBeat from the database.
  5. Display how many tracks there are for each album. Show album ID, album title (album_title), and track count (track_count).
    1. Note: some of your counts might be different due to added or deleted records during in-class exercises.