----------------------------------------------------------------------
-- SQL EXAMPLES                                                       --
-- Introduction to Databases, IT University of Copenhagen, Fall 2004  --
-- Taught by Rasmus Pagh                                              --
-- October 8, 2004                                                    --
----------------------------------------------------------------------

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

-- 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';


-- 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;


-- Using views in connection with authorization

GRANT INSERT, UPDATE ON MGMmovies TO MGMsecretary;