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 produce 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 name, ave(length)
from movieexec, movie
where producerC# = cert#
group by name
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 producer#, avg(length) as aveL
from movie
group by producer#;

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

Other option:

with temp as 
	(select producer#, avg(length) as aveL
	from movie
	group by producer#)
select name
from movieExec, temp
where movieExec.cert# = temp.producer# 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.

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

This will be folded back.

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'
))

Rewrite:
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)