######################################################################
# SQL EXAMPLES                                                       #
# Introduction to Databases, IT University of Copenhagen, Fall 2003  #
# Taught by Rasmus Pagh                                              #
# September 30, 2003                                                 #
######################################################################

# SQL environment examples
# ------------------------

# Create tables in schema corresponding to user pagh:

CREATE SCHEMA AUTHORIZATION pagh
CREATE TABLE Movie (
  title char(25),
  year int,
  length int,
  inColor int,
  studioName char(15),
  producerC char(4)
);
CREATE TABLE MovieExec (
  name char(30),
  address char(30),
  cert int,
  netWorth int
);
CREATE TABLE MovieStar (
  name char(30),
  address char(30),
  gender char(1),
  birthdate char(10)
);
CREATE TABLE StarsIn (
  movieTitle char(30),
  movieYear int,
  starName char(30)
);

################################################################################
# To run the below examples, perform INSERT statements for the above relations #
# available at www.itu.dk/users/pagh/IDB03/data/                               #
################################################################################

# Security and user authorization examples
# ----------------------------------------
# The user executing the SQL command is given before the >.

pagh2> SELECT title FROM pagh.Movie;
pagh>  GRANT SELECT ON Movie TO pagh2;
pagh2> SELECT title FROM pagh.Movie;

pagh2> INSERT INTO pagh.Movie VALUES ('Star Wars', 1977, 124, 1, 'Fox', '555');
pagh>  GRANT INSERT ON Movie TO pagh2;
pagh2> INSERT INTO pagh.Movie VALUES ('Star Wars', 1977, 124, 1, 'Fox', '555');

pagh2> GRANT SELECT ON pagh.Movie TO pagh3;
pagh>  GRANT SELECT ON Movie TO pagh2 WITH GRANT OPTION;
pagh2> GRANT SELECT ON pagh.Movie TO pagh3;
pagh3> SELECT title FROM pagh.Movie WHERE year >= 1990;

pagh>  REVOKE SELECT ON Movie FROM pagh2 CASCADE CONSTRAINTS;
pagh3> SELECT title FROM pagh.Movie WHERE year >= 1990;


# Subquery examples
# -----------------

# Subqueries in the FROM clause:

SELECT *
FROM (SELECT *
      FROM Movie
      WHERE studioName = 'Disney')
WHERE year = 1990;

SELECT DisneyMovie.title, MGMmovie.title
FROM (SELECT *
      FROM Movie
      WHERE studioName = 'Disney') DisneyMovie,
     (SELECT *
      FROM Movie
      WHERE studioName = 'MGM') MGMmovie
WHERE DisneyMovie.producerC = MGMmovie.producerC;

SELECT name
FROM MovieExec,
     (SELECT producerC
      FROM Movie, StarsIn
      WHERE title = movieTitle AND
            year = movieYear AND
            starName = 'Harrison Ford') FordProducers
WHERE cert = FordProducers.producerC;

# Subqueries returning atomic (or scalar) values:

SELECT name
FROM MovieExec
WHERE cert = (SELECT producerC
              FROM Movie
              WHERE title = 'Star Wars');

SELECT name
FROM MovieExec
WHERE cert = (SELECT producerC
              FROM Movie
              WHERE title LIKE 'Star%');

# Subqueries in conditions:

SELECT name
FROM MovieExec
WHERE 2* netWorth > ALL (SELECT netWorth
                         FROM MovieExec);

SELECT title
FROM Movie
WHERE producerC <> ALL (SELECT cert
                        FROM MovieExec);

SELECT name
FROM MovieExec
WHERE cert IN (SELECT producerC
               FROM Movie
               WHERE (title, year) IN (SELECT MovieTitle, MovieYear
                                       FROM StarsIn
                                       WHERE StarName = 'Harrison Ford'));

# Correlated subqueries:

SELECT StarName
FROM StarsIn Star1
WHERE 2 < (SELECT count(StarName)
           FROM StarsIn Star2
           WHERE Star2.MovieTitle = Star1.MovieTitle
                 AND Star2.MovieYear = Star1.MovieYear);

SELECT title, year
FROM Movie
WHERE NOT EXISTS (SELECT *
                  FROM Movie M
                  WHERE year > Movie.year);

SELECT title, year
FROM Movie
WHERE NOT EXISTS (SELECT *
                  FROM Movie
                  WHERE year > Movie.year);


# View examples
# -------------

# View declaration and use

CREATE VIEW FordProducers
AS (SELECT producerC
      FROM Movie, StarsIn
      WHERE title = movieTitle AND
            year = movieYear AND
            starName = 'Harrison Ford');

SELECT * FROM FordProducers;

INSERT INTO Movie VALUES ('Return of the Jedi', 1983, 112, 1, 'Fox', '599');
INSERT INTO StarsIn VALUES ('Return of the Jedi', 1983, 'Harrison Ford');
SELECT * FROM FordProducers;

SELECT name
FROM MovieExec, FordProducers
WHERE cert = FordProducers.producerC;

# Modifying views

CREATE VIEW MGMmovies
AS SELECT *
   FROM Movie
   WHERE studioName = 'MGM';

INSERT INTO MGMmovies
VALUES ('The Fugitive', 1993, 130, 1, 'MGM', '556');

UPDATE MGMmovies
SET producerC = '567'
WHERE title='The Fugitive';

# Using views in connection with authorization

GRANT INSERT, UPDATE ON MGMmovies TO MGMsecretary;