Database Programming :: Projects :: SQL Select
Problem
Video4Ever is a store that sells VHS tapes and DVDs. Somehow they are still in business and have asked you to write some SQL queries for them to get data about their inventory. Download this SQL file and import it in phpMyAdmin to get started. You can get to phpMyAdmin by going to db.yhscs.us. Your username is your first name followed by your graduation year and your password is "yhs" followed by your ID number.
Answer the questions below by running a SQL query on the Video4Ever database. You need to turn in a Google Doc with the query and results for each question.
Question One
List all information about the studios.
Question Two
List the MovieCode, Title, and Price for every movie between $20 and $30 (inclusive).
Question Three
List the name of each studio with at least the start of the word "Production" in their name. The word should at least contain "Prod" and should be preceded by a space.
Question Four
For each type of movie, list the type and average price in ascending order of price.
Question Five
How many total employees does Video4Ever have?
Question Six
For each movie produced by Fox, list the MovieCode, Title, and Price.
Question Seven
List the MovieCode, Title, and Price for each movie produced by Universal with a price greater than $10.
Question Eight
Find the Title and Studio Name for every movie directed by Steven Spielberg.
Question Nine
What is the total value (number of copies of each movie times the price of the movie and add them all up) of the movies at the Oswego branch? Use aliases for this query.
Question Ten
What is the total number of movies on hand at the Yorkville branch?
Question Eleven
List all the movie titles in descending order at the downtown Naperville branch. Use aliases for this query.
Question Twelve
What is the average price for science fiction movies (SF)?
Question Thirteen
List the directors with movies produced by Warner Brothers. Each director should be listed exactly once and they should be listed in alphabetical order. Use aliases for this query.
Question Fourteen
List the directors with each movie they directed sorted alphabetically first by director and then by movie title. Use aliases for this query.
Question Fifteen
How many directors are in the database?
