/* Sample answer to SQL Tutorial, 
   MSC Conversion / ISE 3 / JMC 3 
   Databases course taught by Dr. Fariba Sadri 

   Inherited material from Ian Moor and Maria Lin
   Updated by Olav Beckmann, 2001/02/13 
*/


-- Query 1.  Show the directors and titles
--           of all the films, sorted by director in ascending order.

SELECT Director,Title
FROM films
ORDER BY Director ASC;

/* 
director          |title              
------------------+-------------------
Alfred Hitchcock  |The Birds          
Alfred Hitchcock  |Psycho             
Brian Gibson      |The Juror          
David Fincher     |Alien 3            
Fred Niblo        |Ben Hur            
Gus Van Sant      |Psycho             
James Cameron     |Aliens             
James Cameron     |The Terminator     
Jean-Pierre Jeunet|Alien Resurrection 
J Lee-Thompson    |Cape Fear          
John Carpenter    |Dark Star          
Luc Besson        |The Fifth Element  
Martin Scorsese   |Cape Fear          
Michael Minner    |The Big Sleep      
Mike Nichols      |Catch 22           
Phillip Noyce     |Dead Calm          
Ridley Scott      |Alien              
Sergei Eisenstien |Battleship Potemkin
Terry Gilliam     |Twelve Monkeys     
Thomas H. Ince    |The Alien          
Tony Scott        |Enemy of the State 
Tony Scott        |Crimson Tide       
William Wyler     |Ben Hur            
(23 rows)
*/


-- Query 2.  Show the titles and running times
--          of all the British films, without duplications.

SELECT DISTINCT Title,Length
FROM films
WHERE origin ='UK';

/*
title        |length 
-------------+----------------
Alien        |@ 1 hour 57 mins
The Big Sleep|@ 1 hour 39 mins
*/


-- Query 3.  List the cast of Ben Hur.

SELECT name
FROM casting
WHERE title = 'Ben Hur';

/* 
name
------------------
Ramon Novarro
Francis X. Bushman
Carmel Myers
(3 rows)
*/


-- Query 4.  Who has been directed in films by
--           both Ridley Scott and Alfred Hitchcock?

select name from casting
where director='Ridley Scott'
INTERSECT
select name from casting
where director='Alfred Hitchcock';

/* 
name
-------------------
Veronica Cartwright
(1 row)
*/


-- Query 5.  Which actors appeared in any or all of the Alien films?
--           Show the actors' name and the films' titles.

SELECT name,title
FROM casting
WHERE title LIKE 'Alien%';

/* 
        name         |       title        
---------------------+--------------------
 Dominique Pinon     | Alien Resurrection
 Michael Wincott     | Alien Resurrection
 Lance Henriksen     | Aliens
 Bill Paxton         | Aliens
 William Hope        | Aliens
 Jenette Goldstein   | Aliens
 Al Matthews         | Aliens
 Sigourney Weaver    | Alien
 Tom Skerrit         | Alien
 John Hurt           | Alien
 Veronica Cartwright | Alien
 Yaphet Kotto        | Alien
 Ian Holm            | Alien
 Harry Dean Stanton  | Alien
 Sigourney Weaver    | Aliens
 Carrie Henn         | Aliens
 Michael Biehn       | Aliens
 Paul Reiser         | Aliens
 Sigourney Weaver    | Alien 3
 Charles Dutton      | Alien 3
 Charles Dance       | Alien 3
 Paul McGann         | Alien 3
 Brian Glover        | Alien 3
 Ralph Brown         | Alien 3
 Sigourney Weaver    | Alien Resurrection
 Winona Ryder        | Alien Resurrection
 Ron Perlman         | Alien Resurrection
 Gary Dourdan        | Alien Resurrection
(28 rows)
*/


-- Query 6. Show all the directors who have either made a British  
--          film or who have cast a film titled `The Terminator'.

select films.director from films
where films.origin='UK'
union
select casting.director
from casting
where casting.title='The Terminator';

/* 
director      
--------------
James Cameron 
Michael Minner
Ridley Scott  
(3 rows)
*/


-- Query 7. Which films have been made since 1980 
--          and last 1 hour and 40 mins or more.
--          Show the titles, directors, dates, and running times.

SELECT Title,Director, made, length
from films
where made >= '01-01-1980'
and length >= interval '1 hour 40 mins';

/* 
       title        |      director      |    made    | length 
--------------------+--------------------+------------+--------
 The Fifth Element  | Luc Besson         | 1997-01-01 | 02:07
 Aliens             | James Cameron      | 1986-01-01 | 02:17
 The Terminator     | James Cameron      | 1984-01-01 | 01:48
 Alien 3            | David Fincher      | 1992-01-01 | 01:55
 Enemy of the State | Tony Scott         | 1998-01-01 | 02:11
 Psycho             | Gus Van Sant       | 1998-01-01 | 01:49
 The Juror          | Brian Gibson       | 1996-01-01 | 01:58
 Crimson Tide       | Tony Scott         | 1997-01-01 | 01:52
 Twelve Monkeys     | Terry Gilliam      | 1995-01-01 | 02:10
 Alien Resurrection | Jean-Pierre Jeunet | 1997-01-01 | 01:49
 Cape Fear          | Martin Scorsese    | 1991-01-01 | 02:08
(11 rows)
*/

--  Query 8. Which other actors appeared in the same films
--           as Sigourney Weaver?
--           Show the actors' name and the films' titles.
--           Do not show Sigourney Weaver.

SELECT name,title
FROM casting
WHERE title IN
 (SELECT title
  FROM casting
  WHERE name = 'Sigourney Weaver')
AND name != 'Sigourney Weaver';

/* 
name               |title
-------------------+------------------
Dominique Pinon    |Alien Resurrection
Michael Wincott    |Alien Resurrection
Lance Henriksen    |Aliens
Bill Paxton        |Aliens
William Hope       |Aliens
Jenette Goldstein  |Aliens
Al Matthews        |Aliens
Tom Skerrit        |Alien
John Hurt          |Alien
Veronica Cartwright|Alien
Yaphet Kotto       |Alien
Ian Holm           |Alien
Harry Dean Stanton |Alien
Carrie Henn        |Aliens
Michael Biehn      |Aliens
Paul Reiser        |Aliens
Charles Dutton     |Alien 3
Charles Dance      |Alien 3
Paul McGann        |Alien 3
Brian Glover       |Alien 3
Ralph Brown        |Alien 3
Winona Ryder       |Alien Resurrection
Ron Perlman        |Alien Resurrection
Gary Dourdan       |Alien Resurrection
(24 rows)
*/

-- Query 9. Show the titles of the films which are longer than 
--          at least one of the films made 
--          by the director 'Mike Nichols'

select f1.title 
from films f1, films f2
where f2.director ='Mike Nichols'
and f1.length >f2.length;

/* 
title             
------------------
The Fifth Element 
Aliens            
Ben Hur           
Ben Hur           
Enemy of the State
Twelve Monkeys    
Cape Fear         
(7 rows)
*/