SQL> ------------------------------------------------------------------------ SQL> -- SQL EXAMPLES -- SQL> -- Introduction to Databases, IT University of Copenhagen, Fall 2004 -- SQL> -- Taught by Rasmus Pagh -- SQL> -- September 10, 2004 -- SQL> ------------------------------------------------------------------------ SQL> SQL> -- Projection examples SQL> SQL> SELECT * 2 FROM Movie 3 WHERE studioName = 'Disney' AND year = 1990; TITLE YEAR LENGTH INCOLOR STUDIONAME PRO ------------------------- ---------- ---------- ---------- --------------- --- Pretty Woman 1990 119 1 Disney 999 SQL> SQL> SELECT title, length 2 FROM Movie 3 WHERE studioName = 'Disney' AND year = 1990; TITLE LENGTH ------------------------- ---------- Pretty Woman 119 SQL> SQL> SELECT title AS name, length AS duration 2 FROM Movie 3 WHERE studioName = 'Disney' AND year = 1990; NAME DURATION ------------------------- ---------- Pretty Woman 119 SQL> SQL> SELECT title AS name, length/60 AS lengthInHours 2 FROM Movie 3 WHERE studioName = 'Disney' AND year = 1990; NAME LENGTHINHOURS ------------------------- ------------- Pretty Woman 1.98333333 SQL> SQL> SELECT title AS name, length/60 AS length, 'hours' AS inHours 2 FROM Movie 3 WHERE studioName = 'Disney' AND year = 1990; NAME LENGTH INHOU ------------------------- ---------- ----- Pretty Woman 1.98333333 hours SQL> SQL> SELECT title, length/60 length, 'hours' 2 FROM Movie 3 WHERE studioName = 'Disney' AND year = 1990; TITLE LENGTH 'HOUR ------------------------- ---------- ----- Pretty Woman 1.98333333 hours SQL> SQL> SQL> -- Selection examples SQL> SQL> SELECT title 2 FROM Movie 3 WHERE year > 1970 AND NOT (inColor=1); TITLE ------------------------- The Man Who Wasn't There SQL> SQL> SELECT title 2 FROM Movie 3 WHERE (year > 1970 OR length < 90) AND studioName = 'MGM'; TITLE ------------------------- Terms of Endearment The Usual Suspects SQL> SQL> SELECT title 2 FROM Movie 3 WHERE year > 1970 OR length < 90 AND studioName = 'MGM'; TITLE ------------------------- Pretty Woman The Man Who Wasn't There Logan's run Star Wars Empire Strikes Back Star Trek Star Trek: Nemesis Terms of Endearment The Usual Suspects 9 rows selected. SQL> SQL> SELECT title 2 FROM Movie 3 WHERE title LIKE 'Star ____'; TITLE ------------------------- Star Wars Star Trek SQL> SQL> SELECT title 2 FROM Movie 3 WHERE title LIKE 'Star %'; TITLE ------------------------- Star Wars Star Trek Star Trek: Nemesis SQL> SQL> SELECT title 2 FROM Movie 3 WHERE title LIKE '%''s%'; TITLE ------------------------- Logan's run SQL> SQL> SQL> -- Join examples SQL> SQL> SELECT name 2 FROM Movie, MovieExec 3 WHERE title = 'Star Wars' AND producerC = cert; NAME ------------------------------ George Lucas SQL> SQL> SELECT name 2 FROM Movie, MovieExec 3 WHERE title = 'Star Wars' AND producerC > cert; NAME ------------------------------ Ted Turner Stephen Spielberg Merv Griffin Calvin Coolidge SQL> SQL> SELECT MovieStar.name AS MovieStar, MovieExec.name AS MovieExec 2 FROM MovieStar, MovieExec 3 WHERE MovieStar.address = MovieExec.address; MOVIESTAR MOVIEEXEC ------------------------------ ------------------------------ Jane Fonda Ted Turner SQL> SQL> SELECT Ships.name, displacement, numGuns 2 FROM Ships, Classes, Outcomes 3 WHERE (Outcomes.ship = Ships.name) AND (Classes.class = Ships.class); NAME DISPLACEMENT NUMGUNS -------------------- ------------ ---------- Kirishima 32000 8 Washington 37000 9 California 32000 12 Tennessee 32000 12 SQL> SQL> SELECT Star1.name AS Star1, Star2.name AS Star2 2 FROM MovieStar Star1, MovieStar Star2 3 WHERE Star1.address = Star2.address 4 AND Star1.name < Star2.name; STAR1 STAR2 ------------------------------ ------------------------------ Alec Baldwin Kim Basinger SQL> SQL> SQL> -- Examples for database modification SQL> SQL> INSERT INTO StarsIn(movieTitle, starName, movieYear) 2 VALUES ('The Maltese Falcon', 'Sydney Greenstreet', 1942); 1 row created. SQL> SQL> DELETE FROM StarsIn 2 WHERE starName = 'Sydney Greenstreet'; 1 row deleted. SQL> SQL> INSERT INTO StarsIn 2 VALUES ('The Maltese Falcon', 1942, 'Sydney Greenstreet'); 1 row created. SQL> SQL> UPDATE MovieExec 2 SET netWorth = netWorth * 1000000 3 WHERE netWorth < 1000000; 0 rows updated. SQL> SQL> CREATE TABLE DinnerTable( 2 host VARCHAR(30), 3 food VARCHAR(30), 4 seats INT, 5 occupiedSeats INT 6 ); Table created. SQL> SQL> DROP TABLE DinnerTable; Table dropped. SQL> SQL> SQL> -- Examples for NULL SQL> SQL> SELECT a 2 FROM NullExample 3 WHERE a = 10; A ---------- 10 10 10 10 SQL> SQL> SELECT a 2 FROM NullExample 3 WHERE NOT (a = 10); A ---------- 0 0 0 0 20 20 20 20 8 rows selected. SQL> SQL> SELECT * 2 FROM Movie 3 WHERE length <= 120 OR length >120; TITLE YEAR LENGTH INCOLOR STUDIONAME PRO ------------------------- ---------- ---------- ---------- --------------- --- Pretty Woman 1990 119 1 Disney 999 The Man Who Wasn't There 2001 116 0 USA Entertainm. 777 Star Wars 1977 124 1 Fox 555 Empire Strikes Back 1980 111 1 Fox 555 Star Trek 1979 132 1 Paramount 444 Star Trek: Nemesis 2002 116 1 Paramount 321 Terms of Endearment 1983 132 1 MGM 123 The Usual Suspects 1995 106 1 MGM 999 Gone With the Wind 1938 238 1 MGM 123 9 rows selected. SQL>