SQL> ---------------------------------------------------------------------- SQL> -- SQL EKSEMPLER SQL> -- Databasesystemer, IT University of Copenhagen, Spring 2005 SQL> -- Taught by Rasmus Pagh SQL> -- April 7, 2005 SQL> ---------------------------------------------------------------------- SQL> SQL> -- To run the below examples, perform INSERT statements for the SQL> -- relations available at www.itu.dk/users/pagh/DBS05/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> -- Projection may yield duplicate rows: SQL> SQL> SELECT price FROM PC; PRICE ---------- 799 2499 1999 999 1499 2119 2299 999 1699 699 1299 PRICE ---------- 799 2499 13 rows selected. SQL> SQL> SQL> -- Selection does not introduce, nor remove duplicates: SQL> SQL> SELECT * FROM PC WHERE price < 1000; MODE SPEED RAM HD RD PRICE ---- ---------- ---------- ---------- ---------- ---------- 1001 700 64 10 48xCD 799 1004 866 64 10 12xDVD 999 1008 700 64 30 24xCD 999 1010 750 64 30 40xCD 699 1012 350 64 7 48xCD 799 SQL> SQL> SELECT * 2 FROM (SELECT price FROM PC) 3 WHERE price < 1000; PRICE ---------- 799 999 999 699 799 SQL> SQL> SQL> -- Joins and duplicates: SQL> SQL> SELECT PC.price 2 FROM (SELECT price FROM PC) PC, 3 (SELECT price FROM Laptop) Laptop 4 WHERE PC.price = Laptop.price; PRICE ---------- 999 1499 999 SQL> SQL> SELECT * 2 FROM (SELECT price FROM PC) PC, 3 (SELECT price FROM Laptop) Laptop 4 WHERE PC.price = Laptop.price; PRICE PRICE ---------- ---------- 999 999 1499 1499 999 999 SQL> SQL> SQL> -- Set operations and duplicates: SQL> SQL> (SELECT price FROM PC) INTERSECT (SELECT price FROM Laptop); PRICE ---------- 999 1499 SQL> SQL> (SELECT price FROM PC) UNION ALL (SELECT price FROM Laptop); PRICE ---------- 799 2499 1999 999 1499 2119 2299 999 1699 699 1299 PRICE ---------- 799 2499 1448 2584 2738 999 2399 2999 3099 1249 2599 PRICE ---------- 1499 23 rows selected. SQL> SQL> SQL> -- Duplicate elimination: SQL> SQL> SELECT DISTINCT price FROM PC; PRICE ---------- 699 799 999 1299 1499 1699 1999 2119 2299 2499 10 rows selected. SQL> SQL> SELECT DISTINCT * 2 FROM (SELECT price FROM PC) PC, 3 (SELECT price FROM Laptop) Laptop 4 WHERE PC.price = Laptop.price; PRICE PRICE ---------- ---------- 999 999 1499 1499 SQL> SQL> SQL> -- Operators with built-in duplicate elimination: SQL> SQL> (SELECT price FROM PC) UNION ALL (SELECT price FROM Laptop); PRICE ---------- 799 2499 1999 999 1499 2119 2299 999 1699 699 1299 PRICE ---------- 799 2499 1448 2584 2738 999 2399 2999 3099 1249 2599 PRICE ---------- 1499 23 rows selected. SQL> (SELECT price FROM PC) UNION (SELECT price FROM Laptop); PRICE ---------- 699 799 999 1249 1299 1448 1499 1699 1999 2119 2299 PRICE ---------- 2399 2499 2584 2599 2738 2999 3099 18 rows selected. SQL> SQL>