Movies and Cars DB

Problem 1:

Write the SQL statement to create a table to store information on movies. The table must have attributes for the following:

• Title of movie
• Genre
• Director
• Writer
• Date of release

Be sure to define an appropriate primary key for the table. Assume each movie has only one director and one producer.

Problem 2:

Write the SQL statements to insert the following movie information into the table created in Problem 1

White House Down Action Roland Emmerich James Vanderbilt July 4, 2013
Olympus Has Fallen Action Antoine Fuqua Katrin Benedikt Sep 1, 2013
Phantom Action Todd Robinson Todd Robinson Mar 1, 2013
Driving Miss Daisy Comedy Bruce Beresford
Alfred Uhry Dec 15, 1989
Mission Impossible 3 Action J.J. Abrams Alex Kurtzman May 5, 2006

Problem 3:

Write the SQL queries (select statements) to obtain the following information

• List of all movies
• List of all Action movies
• List of all Comedy movies
• List of all movies released after Jan 1, 2001
• List of all directors
• List of all movies directed and written by the same person
• Number of Action movies
• Number of movies released before Jan 1, 2001
• List of all writers who have written comedy movies released before Jan 1, 2005
• List of all directors who have directed action movies released after Jan 1, 2005

Problem 4:

Write the SQL statements (update statements) to change the data per the following:

• Change the title of the movie White House Down to white house down.
• Change the release date of the movie Driving Miss Daisy to Nov 1, 2013
• Change the director for all movies directed by Todd Robinson to Tom Robinson
• Change genre of all Comedy movies to Action.

Problem 5:

The Acme limo service company needs a simple database to track their cars and drivers. They also want to track driver assignments. Write the SQL statements to create tables to store driver, car and assignment information. Assume each driver is assigned only one car. Each car can be assigned to multiple drivers. The tables must capture the following information for each car and driver:

• Driver: Name, SSN, DOB
• Car: VIN, Make, Model, Color

Be sure to include the appropriate primary and foreign keys

Problem 6:

Write the SQL statement to insert data for the following cars, drivers and their assignments:

John Doe 123121234 11-11-2000
Jill Doe 321434532 07-03-1999
Jane Smith 432121234 03-08-1983
Mike Smith 321125634 04-06-1995

VIN1234 Porsche Cayene Black
VIN2134 Porsche Panamera Red
VIN2134 Hummer H2 Black
VIN5432 Mercedes Benz S500 Red
VIN6543 Mercedes Benz E350 Blue
VIN7654 Audi A8 White

Driver assignments

• John is assigned the Hummer
• Jill is assigned the Audi
• Mike is assigned the Panamera

Problem 7:

Write the SQL statements (update statements) to change the driver assignments as follows

• John is assigned to drive the Audi
• Jill is assigned to drive the Hummer