This exercise requires you to have access to Oracle Express (or a similar SQL da
ID: 3819852 • 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
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
CREATE TABLE movies(
-- Specifying primary key with auto increment id
Id INT NOT NULL auto_increment,
Title varchar(50) NOT NULL, -- title of the movie
Genre varchar(20), -- Genre field
Director varchar(50) NOT NULL unique, -- Director name with unique constraint
Writer varchar(50), -- writer name
Date_Of_Release date, -- date of release
PRIMARY KEY (Id) -- setting Id as primary field
);
-- Inserting data into movies table and before insert converting date format to the default date format
insert into movies (Title, Genre, Director, Writer, Date_Of_Release) values ("White House Down", "Action", "Roland Emmerich","James Vanderbilt", convert(datetime, 'July 4, 2013', 107));
insert into movies (Title, Genre, Director, Writer, Date_Of_Release) values ("Olympus Has Fallen", "Action", "Antoine Fuqua","Katrin Benedikt", convert(datetime, 'Sep 1, 2013', 107));
insert into movies (Title, Genre, Director, Writer, Date_Of_Release) values ("Phantom", "Action", "Todd Robinson","Todd Robinson", convert(datetime, 'Mar 1, 2013', 107));
insert into movies (Title, Genre, Director, Writer, Date_Of_Release) values ("Driving Miss Daisy", "Comedy", "Bruce Beresford","Alfred Uhry", convert(datetime, 'Dec 15, 1989', 107));
insert into movies (Title, Genre, Director, Writer, Date_Of_Release) values ("Mission Impossible 3", "Action", "J.J. Abrams","Alex Kurtzman", convert(datetime, 'May 5, 2006', 107));
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.