This exercise requires you to have access to Oracle Express (or a similar SQL da
ID: 3823109 • Letter: T
Question
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
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
Explanation / Answer
problem 1 :-
CREATE TABLE MOVIES_DETAILS (
TITLE VARCHAR (50) NOT NULL PRIMARY KEY ,
GENRE VARCHAR (20) NOT NULL ,
DIRECTOR VARCHAR (50) NOT NULL ,
PRODUCER VARCHAR (50) NOT NULL ,
WRITER VARCHAR(50) NOT NULL,
DATE_OF_RELEASE DATE NOT NULL ,
UNIQUE (PRODUCER , DIRECTOR )
);
PROBLEM 2 :-
(a)
INSERT INTO MOVIE_DETAILS ( TITLE , GENRE , DIRECTOR , PRODUCER , DATE_OF_RELEASE )
VALUES ( White House Down , Action , Roland Emmerich , James Vanderbilt , 2013-07-04 );
(b)
INSERT INTO MOVIE_DETAILS ( TITLE , GENRE , DIRECTOR , PRODUCER , DATE_OF_RELEASE )
VALUES (Olympus Has Fallen , Action , Antonie Fuqua , Katrin Benedikt , 2013-09-01 );
(c)
INSERT INTO MOVIE_DETAILS ( TITLE , GENRE , DIRECTOR , PRODUCER , DATE_OF_RELEASE )
VALUES (Phantom , Action ,Todd Robinson ,Todd Robinson Todd Robinson , 2013-04-01 );
(d)
INSERT INTO MOVIE_DETAILS ( TITLE , GENRE , DIRECTOR , PRODUCER , DATE_OF_RELEASE )
VALUES (Driving Miss Diasy , Comedy , Bruce Beresford , Alferd Uhry , 1989-12-15 );
NOTE : REST WILL BE ALSO INSERTED SIMILARLY.
PROBLEM 3 :-
SQL QUERIES TO OBTAIN THE INFORMATION :-
(A) LIST OF ALL MOVIES
SELECT * FROM MOVIE_DETAILS ;
(B) LIST OF ALL ACTION MOVIES
SELECT * FROM MOVIE_DETAILS WHERE GENRE = 'ACTION' ;
(C) LIST OF ALL COMEDY MOVIES
SELECT * FROM MOVIE_DETAILS WHERE GENRE ='COMEDY ' ;
(D) LISTOF ALL MOVIES RELEASED AFTER JAN 1 , 2001
SELECT * FROM MOVIE_DETAILS WHERE DATE_OF_RELEASE > TO DATE ( 2001-01-01 ) ;
(E) LIST OF ALL DIRECTORS
SELECT DISTINCT DIRECTOR FROM MOVIE_DETAILS ;
(F) LIST OF ALL MOVIES DIRECTED AND PRODUCED BY THE SAME PERSON
SELECT * FROM MOVIE_DETAILS WHERE "DIRECTOR = PRODUCER' ;
PROBLEM 4 :-
SQL QUERY TO CHANDE DATA FROM TABLE .
(A) Change the title of the movie “White House Down” to “white house down”.
UPDATE MOVIE_DETAILS SET TITLE = 'white house down ' WHERE TITLE = ' White House Title ' ;
(b) Change the release date of the movie “Driving Miss Daisy” to Nov 1, 2013
UPDATE MOVIE_DETAILS SET DATE_OF_RELEASE = ' 2013-11-01 ' WHERE TITLE = ' DRIVING MISS DAISY '
(C) Change the director for all movies directed by Todd Robinson to Tom Robinson
UPDATE MOVIE_DETAILS SET DIRECTOR = ' TOM ROBINSON '
WHERE DIRECTOR = ' TODD ROBINSON ' ;
(D) Change genre of all Comedy movies to Action.
UPDATE MOVIE_DETAILS SET GENRE ='ACTION' WHERE GENRE = ' COMEDY' ;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.