###################################################################### # SQL EXAMPLES # # Introduction to Databases, IT University of Copenhagen, Fall 2003 # # Taught by Rasmus Pagh # # October 7, 2003 # ###################################################################### ################################################################### # To run the below examples, perform INSERT statements for # # the relations Product, PC, Laptop, Printer, Movie and MovieExec # # available at www.itu.dk/users/pagh/IDB03/data/ # ################################################################### # Projection may yield duplicate rows: SELECT price FROM PC; # Selection does not introduce, nor remove duplicates: SELECT * FROM PC WHERE price < 1000; SELECT * FROM (SELECT price FROM PC) WHERE price < 1000; # Joins and duplicates: SELECT PC.price FROM (SELECT price FROM PC) PC, (SELECT price FROM Laptop) Laptop WHERE PC.price = Laptop.price; SELECT * FROM (SELECT price FROM PC) PC, (SELECT price FROM Laptop) Laptop WHERE PC.price = Laptop.price; # Set operations and duplicates: (SELECT price FROM PC) INTERSECT (SELECT price FROM Laptop); (SELECT price FROM PC) UNION ALL (SELECT price FROM Laptop); # Duplicate elimination: SELECT DISTINCT price FROM PC; SELECT DISTINCT * FROM (SELECT price FROM PC) PC, (SELECT price FROM Laptop) Laptop WHERE PC.price = Laptop.price; # Operators with built-in duplicate elimination: (SELECT price FROM PC) UNION ALL (SELECT price FROM Laptop); (SELECT price FROM PC) UNION (SELECT price FROM Laptop); # Outerjoin: SELECT PC.model, Laptop.model, PC.price FROM PC, Laptop WHERE PC.price = Laptop.price (+); # Oracle 8i has limited outerjoin capability. # Oracle 9i has the FULL OUTER JOIN operator. # Aggregation: SELECT sum(price) FROM PC; SELECT studioName, SUM(length) FROM Movie GROUP BY studioName; SELECT studioName, SUM(length) FROM Movie GROUP BY studioName, year; SELECT name, SUM(length) FROM MovieExec, Movie WHERE producerC = cert GROUP BY name HAVING MAX(year) < 1990;