/* 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)
*/