SQL> ------------------------------------------------------------------------ SQL> -- SQL EXAMPLES -- SQL> -- Introduction to Databases, IT University of Copenhagen, Fall 2005 -- SQL> -- Taught by Rasmus Pagh -- SQL> -- October 24, 2005 -- SQL> ------------------------------------------------------------------------ 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> SQL> -- Outerjoin: SQL> SQL> SELECT PC.model, Laptop.model, PC.price 2 FROM (PC FULL OUTER JOIN Laptop 3 ON PC.price = Laptop.price); MODE MODE PRICE ---- ---- ---------- 1008 2004 999 1004 2004 999 1005 2010 1499 1009 1699 1007 2299 1006 2119 1012 799 1001 799 1011 1299 1013 2499 1002 2499 MODE MODE PRICE ---- ---- ---------- 1003 1999 1010 699 2009 2002 2006 2005 2007 2003 2008 2001 21 rows selected. SQL> SQL> -- Aggregation: SQL> SQL> SELECT sum(price) FROM PC; SUM(PRICE) ---------- 20207 SQL> SQL> SELECT studioName, SUM(length) 2 FROM Movie 3 GROUP BY studioName; STUDIONAME SUM(LENGTH) --------------- ----------- Disney 119 Fox 235 MGM 476 Paramount 248 USA Entertainm. 116 6 rows selected. SQL> SQL> SELECT studioName, SUM(length) 2 FROM Movie 3 GROUP BY studioName, year; STUDIONAME SUM(LENGTH) --------------- ----------- Fox 124 Fox 111 MGM 238 MGM 132 MGM 106 Disney 119 Paramount 132 Paramount 116 USA Entertainm. 116 10 rows selected. SQL> SQL> SELECT name, SUM(length) 2 FROM MovieExec, Movie 3 WHERE producerC = cert 4 GROUP BY name 5 HAVING MAX(year) < 1990; NAME SUM(LENGTH) ------------------------------ ----------- Calvin Coolidge 370 George Lucas 235 SQL>