
----------------------------------------------------
-- Page 8-9 --

CREATE TABLE Movies (
	mID int PRIMARY KEY,
	title char(20) NOT NULL,
	director char(10) NOT NULL,
	year int default 0,
	length real,
	UNIQUE (title, director));

ALTER TABLE Movies ADD COLUMN budget real default 0;

ALTER TABLE Movies DROP COLUMN budget;

CREATE TABLE Artists (
	aID int PRIMARY KEY,
	aName char(20),
	nat char(10));

CREATE TABLE Roles (
	mID int,
	aID int,
	character char(20),
	PRIMARY KEY (mID,aID,character),
	FOREIGN KEY (mID) references Movies (mID) ON UPDATE CASCADE,
	FOREIGN KEY (aID) references Artists (aID) ON DELETE NO ACTION);

----------------------------------------------------
-- Page 10-11 --

INSERT INTO Movies VALUES (1,'Shining', 'Kubrick', 1980, 146);
INSERT INTO Movies VALUES (2, 'Player', 'Altman', 1992, 146);
INSERT INTO Movies VALUES (3, 'Chinatown', 'Polanski', 1974, 131);
INSERT INTO Movies VALUES (4, 'Repulsion', 'Polanski', 1965, 143);
INSERT INTO Movies VALUES (5, 'Star Wars IV', 'Lucas', 1977, 126);
INSERT INTO Movies VALUES (6, 'American Graffiti', 'Lucas', 1973, 110);
INSERT INTO Movies VALUES (7, 'Full Metal Jacket', 'Kubrick', 1987, 156);

INSERT INTO Artists VALUES (1, 'Nicholson', 'American');
INSERT INTO Artists VALUES (2, 'Ford', 'American');
INSERT INTO Artists VALUES (3, 'Stone', 'British');
INSERT INTO Artists VALUES (4, 'Fisher', 'American');

INSERT INTO Roles VALUES(1, 1, 'Jack Torrance');
INSERT INTO Roles VALUES(3, 1, 'Jake J.J. Gittes');
INSERT INTO Roles VALUES(1, 3, 'Delbert Grady');
INSERT INTO Roles VALUES(5, 2, 'Han Solo');
INSERT INTO Roles VALUES(6, 2, 'Bob Falfa');
INSERT INTO Roles VALUES(5, 4, 'Princess Leia Organa');

CREATE TABLE Characters (
	cName char(20) PRIMARY KEY, 
	address char(40));

INSERT INTO Characters(cName) 
	(SELECT character FROM Roles);

----------------------------------------------------
-- Page 12 --

DELETE FROM Movies
WHERE mID NOT IN 
	(SELECT mID
	 FROM Roles);

----------------------------------------------------
-- Page 13 --

UPDATE Movies
SET Length = 134, Year = 1975
WHERE title='Chinatown';

----------------------------------------------------
-- Page 15 --

--INSERT INTO Movies VALUES (123, 'Chinatown', 'Polanski', 1974, 131);
--INSERT INTO Movies VALUES (123, 'Shining', 'Kubrick', 1980, 131);

----------------------------------------------------
-- Page 20 --

UPDATE Movies
SET mid = 101
WHERE title='Chinatown';

-- DELETE FROM Artists
-- WHERE aID = 2;

----------------------------------------------------
-- Page 21 --

CREATE TABLE R 
	(A int,
	 B int CHECK (B > 0),
	 C int CHECK (C > 0),
	 CHECK (B > C));

INSERT INTO R VALUES(4, 3, 2);
-- INSERT INTO R VALUES(4, 2, 3);
INSERT INTO R VALUES(0, 3, 2);
-- INSERT INTO R VALUES(0, 3, 0);

----------------------------------------------------
-- Page 24-28 --

SELECT M.title
FROM Movies M
WHERE M.director = 'Lucas';

SELECT *
FROM Movies M1, Movies M2;

SELECT M1.mID
FROM Movies M1, Movies M2;

SELECT M.mID
FROM Movies M , Roles R
WHERE M.mID = R.mID;

-- SELECT mID
-- FROM Movies M , Roles R
-- WHERE M.mID = R.mID;

-- SELECT *
-- FROM Movies, Movies;

----------------------------------------------------
-- Page 29-31 --

SELECT M.mID, M.title, M.director, 2009 - M.year
FROM Movies M;

SELECT M.mID, M.title, M.director, 2009 - M.year AS numYears
FROM Movies M;

----------------------------------------------------
-- Page 32-33 --

SELECT director
FROM Movies;

SELECT DISTINCT director
FROM Movies;

----------------------------------------------------
-- Page 34 --

SELECT DISTINCT A.aName
FROM Artists A, Roles R, Movies M
WHERE M.director = 'Lucas' 
      AND R.mID = M.mID 
      AND A.aID = R.aID;

----------------------------------------------------
-- Page 36-37 --

SELECT A.aName
FROM Artists A
WHERE A.aID IN (SELECT DISTINCT R.aID
		FROM Roles R, Movies M
		WHERE M.director = 'Lucas' AND
		R.mID = M.mID);

----------------------------------------------------
-- Page 41-42 --

SELECT DISTINCT M.director
FROM Movies M
WHERE 1980 > ALL (SELECT M1.year
		  FROM Movies M1
		  WHERE M1.director = M.director);

SELECT DISTINCT M.director
FROM Movies M
WHERE 1980 > ANY (SELECT M1.year
		  FROM Movies M1
		  WHERE M1.director = M.director);

----------------------------------------------------
-- Page 44-45 --

SELECT A.aName
FROM Artists A
WHERE NOT EXISTS ((SELECT M.mID
		   FROM Movies M
		   WHERE M.director = 'Lucas')
		  EXCEPT
		  (SELECT R.mID
		   FROM Roles R
		   WHERE R.aID = A.aID));

----------------------------------------------------
-- Page 59-60 --

CREATE TABLE S 
	(A int);

INSERT INTO S VALUES (1);
INSERT INTO S VALUES (2);
INSERT INTO S VALUES (1);
INSERT INTO S VALUES (2);
INSERT INTO S VALUES (NULL);
INSERT INTO S VALUES (NULL);

SELECT A FROM S;

SELECT A FROM S WHERE A <> 1
UNION ALL
SELECT A FROM S WHERE A = 1;


SELECT A FROM S WHERE A <> 1
UNION ALL
SELECT A FROM S WHERE A = 1
UNION ALL
SELECT A FROM S WHERE A IS NULL;

----------------------------------------------------
-- Page 71 --

SELECT A.aName
FROM (Movies M JOIN Roles R ON ((M.director = 'Lucas') 
				 AND (R.mID = M.mID)))
	       JOIN Artists A ON A.aID = R.aID;


----------------------------------------------------
-- Page 72-76 --

CREATE TABLE Studio (
	sName char(10),
	mID int);

CREATE TABLE Finance (
	mID int,
	gross real);

INSERT INTO Studio VALUES ('United', 1);
INSERT INTO Studio VALUES ('United', 2);
INSERT INTO Studio VALUES ('Dreamworks', 3);

INSERT INTO Finance VALUES (1, 156);
INSERT INTO Finance VALUES (2, 412);
INSERT INTO Finance VALUES (4, 25);

SELECT S.sName, SUM(F.gross) AS Total
FROM Studio S JOIN Finance F ON S.mID = F.mID
GROUP BY S.sName;

SELECT S.sName, SUM(F.gross) AS Total
FROM Studio S LEFT OUTER JOIN Finance F ON S.mID = F.mID
GROUP BY S.sName;


----------------------------------------------------
-- Page 77 --

SELECT director
FROM Movies
GROUP BY director
HAVING COUNT(mID) >= 2;

SELECT director
FROM (SELECT director, COUNT(mID) AS CTN
      FROM Movies
      GROUP BY director) AS Temp
WHERE CTN >= 2;

----------------------------------------------------
-- Page 84-92 --
SELECT COUNT(*) AS noTuples
FROM Movies;

SELECT COUNT(DISTINCT director) AS noDirectors
FROM Movies;

SELECT AVG(length) AS AVGL
FROM Movies
WHERE director = 'Lucas';