Lab 2 : Advanced SQL Queries

You have worked with simple SQL SELECT queries in Lab. 1. The goal of Lab. 2 is to expose you to intemediate complexity of SQL SELECT statements using joins, nested queries and relational division.

Consider the following database schema with three tables about movies, actors and casting. 

CREATE TABLE actor ( id INTEGER NOT NULL, name VARCHAR(35), PRIMARY KEY (id));

CREATE TABLE movie ( id INTEGER NOT NULL, title VARCHAR(70), 
	yr DECIMAL(4), score FLOAT, votes INTEGER, director INTEGER, 
	PRIMARY KEY (id), FOREIGN KEY (director) REFERENCES actor(id));

CREATE TABLE casting (
	movieid INTEGER NOT NULL, actorid INTEGER NOT NULL, 
	ord INTEGER, PRIMARY KEY (movieid, actorid), 
	FOREIGN KEY (movieid) REFERENCES movie(id), 
	FOREIGN KEY (actorid) REFERENCES actor(id));

GRANT SELECT ON actor TO PUBLIC; GRANT SELECT ON movie TO PUBLIC;
GRANT SELECT ON casting TO PUBLIC;
TA account (i.e. F02G40) has these three tables with data and has made those available to all the student accounts. Use the tables from the TA account for testing SQL expressions for the queries in Laboratory 2. Since these tables are quite large, you should NOT copy those in your own accounts.

You may refer to these table either via prefixing owning account name or via synonyms. Following example illustrates use of dot notation via prefixing owning account: 

  SQL> select * from F02G40.MOVIE;
Following example illustrates use of synonyms: 
  SQL> create synonym MOVIE for F02G40.MOVIE;
  SQL> create synonym CASTING for F02G40.CASTING;
  SQL> create synonym ACTOR for F02G40.ACTOR;
  SQL> select count(*) from MOVIE group by yr;
Write SQL statements for the queries listed in following subsections. Submit these SQL expressions and query results.
0) Setup
Create synonyms for three tables from the TA accounts. Test the synonyms by writing SQL expression for the following queries:
  1. How many movies are there ?
  2. How many movies have titles starting with 'Star Trek' ?
  3. How many actors are there ?
  4. Which movie has smallest cast ?
  5. Which movie has largest cast ?
  6. Which actor has been featured in the highest number of movies ?
1) Inner join
Write SQL expressions for the following queries.
  1. Obtain the name of actors in the cast list for the film 'Die Hard'.
  2. List the title of films in which 'Kevin Costner' has appeared
  3. List the title of films where 'Kevin Costner' has appeared - but not in the lead star role.(Lead star role is defined by casting.order = 1.)
  4. List the title of films together with their stars for all 1967 films.
  5. List the title of 1970 films by order of cast list size.
  6. List the name of actors featured in at least 13 films released after 1990.
2) Outer join
Write SQL expressions for the following queries.
  1. List the film title and the lead actor name for all 1960 films. Be sure that movie 'Psycho' is in the list.
  2. List the name of actors whose name starts 'U' and the number of films that the actor played. Include actors with no film.
  3. For every actor, list the number of movies starring him/her as the first person on the casting. Include all actor with names starting with 'Z' in the result.
3) Self join
Write SQL expressions for the following queries.
  1. List movie pairs with common titles. Provide movie ids and years to explain the pairs.
  2. List the name pair of actors who have worked together in more than 2 films. (The name of actors starts with 'A'.)
  3. List the title pair of movies which have the same lead star actor. (Restrict result to movies with names starting with 'V'.)
4) Nested queries(I)
Write SQL expressions for the following queries.
  1. Which were the busiest years for 'Al Pacino'.
  2. List title and lead actor name for of films starring 'Jesse Ventura' .
  3. Obtain the name of actors in who have had at least 15 lead star roles.
  4. List the name of all actors who have worked with 'James Dean'.
5) Nested queries(II) using exists, not exists
Write SQL expressions for the following queries.
  1. List actors who have worked with 'Al Pacino'.
  2. List the name of actors who don't play in any movie 'Mel Gibson' has appeared in.( Restrict result to the actors whose name begin with 'Z'.)
  3. List names of actors with lead star role in a 1940s movie.
6) Queries using set operators, union, intersect and minus
Write SQL expressions for the following queries.
  1. List titles of movies both 'Tom Hanks' and 'Meg Ryan' have appeared in.
  2. List the title of movies featuring 'Harrison Ford' but not 'Anthony Daniels' not.
  3. List titles of movies starting with 'Die Hard' or starring 'Bruce Willis'. Ensure there the list has no duplicates.
7) Relational Division
Write SQL expressions for the following queries.
  1. List names of actors who acted in every movie with titles starting with 'Die Hard'.
  2. List the name of actors who acted in all of films featuring 'Jesse Ventura'.
  3. List the title of movies which feature all of actors featured in 'Wallace & Gromit: A Grand Day Out' and 'Wallace & Gromit: The Wrong Trousers' movies.