This exercise requires you to have access to Oracle Express (or a similar SQL database).

Instructions

All documents and SQL code must be zipped into a single file and uploaded to Blackboard

All SQL code must run error-free for full credit

All SQL code must be commented appropriately

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