Movie(title, year, length, inColor, studioName, producerC#)
StarsIn(movieTitle, movieYear, starName)
MovieStar(name, address, gender, birthdate)
MovieExec(name, address, cert#, netWorth)
Studio(name, address, presC#)

DDL Statements
==============
create table movie (title char(100), year integer, length integer, 
inColor smallint, studioName char(20), producerC# integer);

insert into StarsIn('King Kong', 2005, 'Naomi Watts'); 


DML Statements
==============
1. Movies produced by disney in 1990

select *
From movie
Where studioname = 'disney' and year = 1990


1a. 'as' variations / rename operation

1b. Play with the predicates

	year between 1990 and 1995
    year not between 1990 and 1995
    title like 'star wars%'
	title like 'star wars _'
    Escape: using '\%', '\\' etc.

1c. "distinct" variation

1d. Orderby variation


2. Interesting query - We haven't discussed NULLS yet.
Select * 
From movie
Where length <= 120 or length > 120

Does not select everything. Nulls behave funnily.

3. Find producer name for 'star wars'
Select movieexec.name
From movie, movieexec
Where movie.title = 'star wars' and movie.producerC# = Movieexec.cert#

4. Find stars that live together

Select star1.name, star2.name
From moviestar star1, moviestar star2
Where star1.address = star2.address
And star1.name < star2.name

5. Find female movie stars that are also movie executives with worth over $10M

(select name, address
from moviestar
where gender = 'F'
)
intersect
(
select name, address
from movieexec
where networth > 1000000
)

5a. Similarly union, and union all, and intersect all
	

AGGREGATES
==========
1. Average length of a movie
   select name, avg(length) 
   from movie

2. How has the average length of a movie changed per year.
   select year, avg(length)
   from movie
   group by movie

3. Compute the average length of film for early producers

select cert#, name, avg(length)
from movieexec, movie
where producerC# = cert#
group by cert#
having min(year) < 1930

4. Find the movie with the maximum length.

select *
from movie
where movie.length = (select max(length) from movie);

5. Select the producer with the maximum average length movies.

create table temp as
select producerC#, avg(length) as aveL
from movie
group by producerC#;

select producerC#, name
from movieExec, temp
where movieExec.cert# = temp.producerC# and 
temp.aveL = (select max(aveL) from temp);

Other option:

with temp as 
	(select producerC#, avg(length) as aveL
	from movie
	group by producerC#)
select producerC#, name
from movieExec, temp
where movieExec.cert# = temp.producerC# and 
temp.aveL = (select max(aveL) from temp);

Not too much difference between the two. You just don't have to deal
with creating/deleting tables. On the other hand, it is possible
that option 1 is faster.

Third option:

select cert#,  name
from movieExec, movie
where movieExec.cert# = temp.producerC#
group by movieExec.cert#
having avg(length) >= all (
       select avg(length) 
       from movie
       group by producerC#
);

SET PREDICATES
==============
6. find producer of 'star wars'
select name
from movieexec
where cert# = (select producerC#
			   from movie
			   where title = 'star wars'
)

This query will be folded back to use a join instead of a subquery.

7. all producers of movies in which harrison ford stars.

select name
from movieexec
where cert# IN (
	select producerC#
	from movie
	where (title, year) in 
		(select movietitle, movieyear
		 from starsin
		 where starName = 'Harrison Ford'
))

Can be rewritten as (some database systems will do this automatically) :
select name
from movieexec, movie, starsin
where cert# = producer# and title = movieTitle and year = movieYear and starName = 'Harrison Ford'

8. Find movie titles that appear more than once
select title 
from Movie old
where year < any (select  year 
				 from movie
				 where title = old.title)


9. Find number of actors 3 hops away from Kevin Bacon (an actor is one hop away from another actor
if they co-star in a movie).

Do this in steps.
(1) Create a table that puts co-stars next to each other.

create table co-stars as 
select s1.starName actor1, s2.starName actor2
from starsin s1, starsin s2
where s1.movieTitle = s2.movieTitle and s1.movieYear = s2.movieYear;

All information required to answer the query is in this new table co-stars.
Note that this table contains self-links (tuples of the form (A, A)). This is 
important if you want to find the actors who are less than 3 hops away.

(2) Hint: Lay out the tables and draw connections.

select c1.actor1, c3.actor2
from co-stars c1, co-stars c2, co-stars c3
where c1.actor2 = c2.actor1 and c2.actor2 = c3.actor1;

(3) What if you were required to find actors exactly 3 hops away ?