/*
Purpose: DBS201 Lab 5
*/
SET AUTOCOMMIT ON;
--
--------------------------------------------------------------------------------
---------------------------------------
-- Q1 Create table as shown on lab handout
...
/*
Purpose: DBS201 Lab 5
*/
SET AUTOCOMMIT ON;
--
--------------------------------------------------------------------------------
---------------------------------------
-- Q1 Create table as shown on lab handout
DROP TABLE L5_movies;
CREATE TABLE L5_movies (
mID int Primary Key,
title varchar(35) not null,
releaseYear int not null,
director int not null,
score decimal(3,2) check(score between 0 AND 5)
);
DROP TABLE L5_actors;
CREATE TABLE L5_actors (
aID int Primary Key,
firstName varchar(20) not null,
lastName varchar(30) not null
);
DROP TABLE L5_castings;
CREATE TABLE L5_castings (
movieID int,
actorID int,
Primary Key (movieID, actorID)
);
DROP TABLE L5_directors;
CREATE TABLE L5_directors (
directorID int,
firstName varchar(20) not null,
lastName varchar(30) not null,
Primary Key (directorID)
);
--
--------------------------------------------------------------------------------
---------------------------------------
-- Q2
ALTER TABLE L5_movies
ADD CONSTRAINT movie_directors_fk FOREIGN KEY (director) REFERENCES
L5_directors(directorID);
--
--------------------------------------------------------------------------------
---------------------------------------
-- Q3
ALTER TABLE L5_movies
ADD CONSTRAINT moviename_unq UNIQUE (title);
--
--------------------------------------------------------------------------------
---------------------------------------
-- Q4
INSERT ALL
INTO L5_directors VALUES (1010, 'Rob', 'Minkoff')
INTO L5_directors VALUES (1020, 'Bill', 'Condon')
INTO L5_directors VALUES (1050, 'Josh', 'Cooley')
INTO L5_directors VALUES (2010, 'Brad', 'Bird')
INTO L5_directors VALUES (3020, 'Lake', 'Bell')
SELECT * FROM dual;
INSERT ALL
[Show More]
.png)
