SQL> ------------------------------------------------------------------------ SQL> -- SQL eksempler -- SQL> -- Databasesystemer, IT Universitetet i K¿benhavn, forŒr 2005 -- SQL> -- Rasmus Pagh -- SQL> -- 9. februar, 2005 -- 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> 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 * 2 FROM Movie, MovieExec 3 WHERE producerC = cert; TITLE YEAR LENGTH INCOLOR STUDIONAME PRO NAME ADDRESS CERT NETWORTH ------------------------- ---------- ---------- ---------- --------------- --- ------------------------------ ------------------------------ ---------- ---------- Star Wars 1977 124 1 Fox 555 George Lucas Oak Rd. 555 25000000 Empire Strikes Back 1980 111 1 Fox 555 George Lucas Oak Rd. 555 25000000 Terms of Endearment 1983 132 1 MGM 123 Calvin Coolidge Fast Lane 123 20000000 Gone With the Wind 1938 238 1 MGM 123 Calvin Coolidge Fast Lane 123 20000000 SQL> SQL> SELECT name 2 FROM Movie, MovieExec 3 WHERE title = 'Star Wars' AND producerC = cert; NAME ------------------------------ George Lucas 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> -- 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> -- 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> 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> -- 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.