Yorkville High School Computer Science Department
Yorkville High School Computer Science Department on Facebook  Yorkville High School Computer Science Department Twitter Feed  Yorkville High School Computer Science Department on Instagram

Yorkville High School Computer Science

ASSIGNMENTS: Network App - December 14, 2018 :: Web App - December 14, 2018 :: Operating System - December 17, 2018 >>

Database Programming :: Projects :: SQL Select

Rubric
Lesson
Due: Sep 11

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?

Yorkville High School Computer Science Department on Facebook Yorkville High School Computer Science Department Twitter Feed Yorkville High School Computer Science Department on Instagram