SQL> ---------------------------------------------------------------------- SQL> -- SQL EXAMPLES -- SQL> -- Introduction to Databases, IT University of Copenhagen, Fall 2004 -- SQL> -- Taught by Rasmus Pagh -- SQL> -- October 8, 2004 -- SQL> ---------------------------------------------------------------------- SQL> SQL> -- To run the below examples, perform INSERT statements for the above SQL> -- relations available at www.itu.dk/users/pagh/IDB04/data/ SQL> SQL> -- Subquery examples SQL> -- ----------------- SQL> SQL> -- Subqueries in the FROM clause: SQL> SQL> SELECT * 2 FROM (SELECT * 3 FROM Movie 4 WHERE studioName = 'Disney') 5 WHERE year = 1990; TITLE YEAR LENGTH INCOLOR STUDIONAME PRO ------------------------- ---------- ---------- ---------- --------------- --- Pretty Woman 1990 119 1 Disney 999 SQL> SQL> SELECT DisneyMovie.title, MGMmovie.title 2 FROM (SELECT * 3 FROM Movie 4 WHERE studioName = 'Disney') DisneyMovie, 5 (SELECT * 6 FROM Movie 7 WHERE studioName = 'MGM') MGMmovie 8 WHERE DisneyMovie.producerC = MGMmovie.producerC; TITLE TITLE ------------------------- ------------------------- Pretty Woman The Usual Suspects SQL> SQL> SELECT name 2 FROM MovieExec, 3 (SELECT producerC 4 FROM Movie, StarsIn 5 WHERE title = movieTitle AND 6 year = movieYear AND 7 starName = 'Harrison Ford') FordProducers 8 WHERE cert = FordProducers.producerC; NAME ------------------------------ George Lucas George Lucas SQL> SQL> -- Subqueries returning atomic (or scalar) values: SQL> SQL> SELECT name 2 FROM MovieExec 3 WHERE cert = (SELECT producerC 4 FROM Movie 5 WHERE title = 'Star Wars'); NAME ------------------------------ George Lucas SQL> SQL> SELECT name 2 FROM MovieExec 3 WHERE cert = (SELECT producerC 4 FROM Movie 5 WHERE title LIKE 'Star%'); WHERE cert = (SELECT producerC * ERROR at line 3: ORA-01427: single-row subquery returns more than one row SQL> SQL> -- Subqueries in conditions: SQL> SQL> SELECT name 2 FROM MovieExec 3 WHERE 2* netWorth > ALL (SELECT netWorth 4 FROM MovieExec); NAME ------------------------------ George Lucas Ted Turner Merv Griffin SQL> SQL> SELECT title 2 FROM Movie 3 WHERE producerC <> ALL (SELECT cert 4 FROM MovieExec); TITLE ------------------------- Pretty Woman The Man Who Wasn't There Logan's run Star Trek Star Trek: Nemesis The Usual Suspects 6 rows selected. SQL> SQL> SELECT name 2 FROM MovieExec 3 WHERE cert IN (SELECT producerC 4 FROM Movie 5 WHERE (title, year) IN (SELECT MovieTitle, MovieYear 6 FROM StarsIn 7 WHERE StarName = 'Harrison Ford')); NAME ------------------------------ George Lucas SQL> SQL> -- Correlated subqueries: SQL> SQL> SELECT StarName 2 FROM StarsIn Star1 3 WHERE 2 < (SELECT count(StarName) 4 FROM StarsIn Star2 5 WHERE Star2.MovieTitle = Star1.MovieTitle 6 AND Star2.MovieYear = Star1.MovieYear); STARNAME ------------------------------ Carrie Fisher Mark Hamill Harrison Ford SQL> SQL> SELECT title, year 2 FROM Movie 3 WHERE NOT EXISTS (SELECT * 4 FROM Movie M 5 WHERE year > Movie.year); TITLE YEAR ------------------------- ---------- Star Trek: Nemesis 2002 SQL> SQL> SELECT title, year 2 FROM Movie 3 WHERE NOT EXISTS (SELECT * 4 FROM Movie 5 WHERE year > Movie.year); TITLE YEAR ------------------------- ---------- Pretty Woman 1990 The Man Who Wasn't There 2001 Logan's run 1976 Star Wars 1977 Empire Strikes Back 1980 Star Trek 1979 Star Trek: Nemesis 2002 Terms of Endearment 1983 The Usual Suspects 1995 Gone With the Wind 1938 10 rows selected. SQL> SQL> SQL> -- View examples SQL> -- ------------- SQL> SQL> -- View declaration and use SQL> SQL> CREATE VIEW FordProducers 2 AS (SELECT producerC 3 FROM Movie, StarsIn 4 WHERE title = movieTitle AND 5 year = movieYear AND 6 starName = 'Harrison Ford'); View created. SQL> SQL> SELECT * FROM FordProducers; PRO --- 555 599 555 SQL> SQL> INSERT INTO Movie VALUES ('Return of the Jedi', 1983, 112, 1, 'Fox', '599'); 1 row created. SQL> INSERT INTO StarsIn VALUES ('Return of the Jedi', 1983, 'Harrison Ford'); 1 row created. SQL> SELECT * FROM FordProducers; PRO --- 555 599 599 599 599 555 6 rows selected. SQL> SQL> SELECT name 2 FROM MovieExec, FordProducers 3 WHERE cert = FordProducers.producerC; NAME ------------------------------ George Lucas George Lucas SQL> SQL> -- Modifying views SQL> SQL> CREATE VIEW MGMmovies 2 AS SELECT * 3 FROM Movie 4 WHERE studioName = 'MGM'; View created. SQL> SQL> INSERT INTO MGMmovies 2 VALUES ('The Fugitive', 1993, 130, 1, 'MGM', '556'); 1 row created. SQL> SQL> UPDATE MGMmovies 2 SET producerC = '567' 3 WHERE title='The Fugitive'; 2 rows updated. SQL> SQL>