Assignment 7: Full SQL Statements

Posted on | by nayeon

Overview

This assignment helps you practice more SQL statements that manipulate the database, such as insert, update, and deleting records. You will write eleven SQL statements: five for Song database and six for DVD database.

Requirements

Create one .sql file for all eleven 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 (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.

Part 2 – DVD Database

  1. Create a view dramas that displays all drama DVDs with release date not set to NULL. Show DVD ID, DVD title, release date, award, format, genre, label, rating, and sound.
  2. Add a new DVD with the below information:
    1. Title: The Godfather
    2. Release Date: March 24, 1972
    3. Award: 45th Academy Award for Best Picture
    4. Format: Fullscreen, Widescreen
    5. Genre: Drama
    6. Label: Paramount
    7. Rating: R
    8. Sound: DTS
  3. Make the following changes to the DVD titled “Zero Effect.” This DVD already exists in the database.
    1. New Label: Columbia TriStar
    2. New Genre: Comedy
    3. New Format: Fullscreen
  4. Delete the DVD titled “Major League 3:Back to the Minors” from the database. This DVD already exists in the database.
  5. Display the number of characters for the longest and shortest title in the database. Name columns longest_title and shortest_title respectively. Use aggregate functions.
  6. Display all genres and number of DVDs belonging to each genre as dvd_count column. Show genre ID, genre name, and DVD count. Use an aggregate function.

Submission

When complete, you must upload the completed .sql file to Blackboard. Go to Assignments -> Assignment 7: Full SQL Statements and upload the file.