comment Query 1:
comment Who played Tracy Lord? When? In what films?
comment 	(Mentally record response time)

	join films, parts into temp ;
	print name, title, year, studio
		from temp
		where role = "Tracy Lord" ;
comment cleanup
	! "rm temp*"
comment Alternate solution, performing selection on two relations
comment        (Compare relative times, should be similar)
	print name, films.title, films.year, studio
		from  films, parts
		where role = "Tracy Lord"
		    & films.title = parts.title
		    & films.year  = parts.year ;
comment cleanup
	! "rm temp*"
comment Alternate solution, performing selection before join.
comment        (Compare relative times, should be quicker)

	select name, title, year
		from parts
		where role = "Tracy Lord"
		into temp1
	join films, temp1 into temp2 ;
	print name, title, year, studio from temp2 ;
	print name, title, year, studio from temp2
		into answer1 ;
comment cleanup
	! "rm temp*"

COMMENT Query 2:
COMMENT What movies did Fred Astaire and Ginger Rogers make together?

	project  title, year
		from   parts
		where  name = "Fred Astaire"
		into   temp2_1 ;
	project  title, year
		from   parts
		where  name = "Ginger Rogers"
		into   temp2_2 ;
	join temp2_1, temp2_2  into temp2_3 ;
	join temp2_3, films  into temp2_4 ;
	print title, year, studio, type from temp2_4 ;
	print title, year, studio, type from temp2_4
		into   answer2 ;
comment cleanup
	! "rm temp*"

comment Query 3:
comment What films did the same person both write and direct?
comment How many such films are there?

	join writers, directors into temp3_1 ;
	join temp3_1, films into temp3_2 ;
	print name, title, year from temp3_2 ;
	print name, title, year from temp3_2 into answer3
	count * from temp3_2 ;
comment cleanup
	! "rm temp*"

comment Query 4:
comment What films did Billy Wilder both write and direct?
comment Did any of these win an Oscar?  In what category?

	select	title, year
		from	directors
		where	name = "Billy Wilder"
		into	temp4_1 ;
	select	title, year
		from	writers
		where	name = "Billy Wilder"
		into	temp4_2 ;

comment			intersect temp4_1 with temp4_2
	diff temp4_1, temp4_2 into temp4_3;
	diff temp4_1, temp4_3 into both;

	join both, films into temp4_4;

	print	title, year, studio, type from temp4_4 ;
	print	title, year, studio, type from temp4_4
		into answer4a;

	select	*
		from	oscars
		where	name = "Billy Wilder"
		into	wilder_oscars;
	
	join both, wilder_oscars into temp4_5;

	print	category, year, name from temp4_5 ;
	print	category, year, name from temp4_5
		into answer4b

comment cleanup
	! "rm temp* both.sdb wilder*"

comment Query 5:
comment What roles did Bogart play between 1937 and 1943 ?

	select year, title, role
		from  parts
		where name = "Humphrey Bogart"
		    & year >= 1937
		    & year <= 1943
		into temp5_1 ;
	sort temp5_1 by year, title ;
	print * from temp5_1 ;
	print * from temp5_1 into answer5 ;
comment cleanup
	! "rm temp*"

comment Query 6:
comment Did Katharine Hepburn ever work under Alfred Hitchcock ?

	select	title, year
		from	parts
		where	name = "Katharine Hepburn"
		into	temp6_1 ;

	select	title, year
		from	directors
		where	name = "Alfred Hitchcock"
		into	temp6_2 ;

comment			intersect temp6_1 with temp6_2
	diff	temp6_1, temp6_2 into temp6_3 ;
	diff	temp6_1, temp6_3 into both;

	print * from both ;
	print * from both into answer6 ;

	count * from both ;
comment cleanup
	! "rm temp* both.sdb"

comment Query 7:
comment How man Donald Ogden Stewart screenplays did Cukor direct?
comment What were they?

	select title, year
		from  writers 
		where name = "Donald Ogden Stewart"
		into  stewart ;
	select title, year
		from  directors
		where name = "George Cukor"
		into  cukor ;
	diff stewart, cukor into temp7_1
	diff stewart, temp7_1 into both

	count * from both ;

	print * from both ;
	print * from both into answer7 ;

comment cleanup
	! "rm stewart.sdb cukor.sdb both.sdb temp*"

comment Query 8:
comment Which directors made NO films between 1943 and 1948?

	select name
		from  directors
		where year >= 1943 & year <= 1948
		into  working ;
	project name
		from  directors
		into  all_dir ;

	diff  all_dir, working into idle ;

	print * from idle ;
	print * from idle into answer8 ;

comment cleanup
	! "rm working.sdb all_dir.sdb idle.sdb"
comment Query 9:
comment What films have been remade?

	project title, year
		from  films
		into  temp9_1
	rename  temp9_1.year  into year2 ;

	join  films, temp9_1  into temp9_2 ;

	select title, year, year2, studio, color
		from  temp9_2
		where year <> year2
		into  remakes
	print title, year, studio, color from  remakes ;
	print title, year, studio, color from  remakes into answer9a ;

comment Has a film with an Oscar-winning screenplay ever been remade?

	select title, year, category
		from  oscars
		where category = "screenplay"
		into  temp9_3
	rename temp9_3.year into year3 ;

	join   temp9_3, remakes into winners ;

	print title, year, year3, category from winners ;
	print title, year, year3, category from winners into answer9b 

comment Has a sequel ever won an Oscar?

	select *
		from  remakes
		where year > year2
		into  sequels
	join  sequels, oscars  into temp9_4 ;

	print title, year, category from temp9_4 ;
	print title, year, category from temp9_4 into answer9c

comment cleanup
	! "rm temp* remakes* sequels* winners*"