Assignment 7: SELECT SQL Statements

Posted on | by nayeon

Overview

This assignment consists of two parts: importing DVD database (needed for future assignments) and writing SELECT SQL statements against the DVD and song databases.

Requirements

Part 1 – DVD Database Import

  1. Download dvd.sql file.
  2. Create a new database in cPanel → MySQL Databases.
    1. Name it username_dvd_db (cPanel will prefix your username for you automatically).
    2. For example: ttrojan_dvd_db.
  3. Import dvd.sqlfile to the newly created database.
    1. cPanel → PHPMyAdmin → [Click on username_dvd_db Database] → Import.
  4. Verify all tables exist and are populated with data.
  5. All tables and relationships should match the figure below.

Part 2 – SELECT SQL Statements

Open up MySQL Workbench and create one .sql file. In this file, you will write six SQL statements – four statements against the song database and two statements against the DVD database you just imported in Part 1. Above each statement, write a comment with the below prompt.

Important: Use JOIN statements when querying from multiple tables as taught in lecture. Do NOT select multiple tables on FROM statement. See this stackoverflow link for more info.

Song Database SELECT statements (4)

  1. Display albums that have the letters “on” somewhere in the album title. Sort results in alphabetical order by album title.
    Sample results:

  2. Same as #1, but only show album title and artist name (no artist_id) columns.
    Sample results:

  3. Display tracks that have AAC audio file format. Only show track name (alias: track_name), composer, media type name (alias: media_type), and unit price columns.
    1. Use primary key like media_type_id column for comparison instead of strings like media type name.
      Sample results:

  4. Display R&B/Soul and Jazz tracks that have a composer (not NULL). Sort results in reverse-alphabetical order by track name. Only show track ID, track name (alias: track_name), composer, milliseconds, and genre name (alias: genre_name) columns.
    1. Use primary key like genre_id column for comparison instead of strings like genre name.
      Sample results:

DVD Database SELECT statements (2)

  1. Display DVDs that have the genre Drama that won awards. Sort results by year of when the DVD won an award. Show dvd title, award, genre, label, and rating.
    1. Use primary keys for comparison instead of the string names.
      Sample results:
  2. Display R-rated Sci-Fi DVDs that have a release date (not NULL). Order results from newest to oldest released DVD. Show dvd title, release date, rating, genre, sound, and label.
    1. Use primary keys for comparison instead of the string names.
      Sample results:

Submission

When complete, you must upload the completed .sql file on Blackboard. Go to Assignments -> Assignment 7: SELECT Statements and upload the file. There is no need to upload this file to the server.