SQL> ------------------------------------------------------------------------ SQL> -- SQL EXAMPLES -- SQL> -- Introduction to Databases, IT University of Copenhagen, Fall 2005 -- SQL> -- Taught by Rasmus Pagh -- SQL> -- October 31, 2005 -- SQL> ------------------------------------------------------------------------ SQL> SQL> -- Primary key example: SQL> -- -------------------- SQL> SQL> CREATE TABLE Students ( 2 cpr VARCHAR(10) PRIMARY KEY, 3 name VARCHAR(20), 4 address VARCHAR(20) 5 ); Table created. SQL> SQL> INSERT INTO Students VALUES ('0602751127','Ethan Longwinder','My Way 2'); 1 row created. SQL> INSERT INTO Students VALUES ('0602751127','Ethan Longwinder II','My Way 2'); INSERT INTO Students VALUES ('0602751127','Ethan Longwinder II','My Way 2') * ERROR at line 1: ORA-00001: unique constraint (PAGH.SYS_C005079) violated SQL> INSERT INTO Students VALUES (NULL,'Mysterio Student','No Way 8'); INSERT INTO Students VALUES (NULL,'Mysterio Student','No Way 8') * ERROR at line 1: ORA-01400: cannot insert NULL into ("PAGH"."STUDENTS"."CPR") SQL> SELECT * FROM Students; CPR NAME ADDRESS ---------- -------------------- -------------------- 0602751127 Ethan Longwinder My Way 2 SQL> SQL> DROP TABLE Students; Table dropped. SQL> SQL> SQL> -- UNIQUE key examples: SQL> -- -------------------- SQL> SQL> CREATE TABLE Students ( 2 cpr VARCHAR(10) PRIMARY KEY, 3 name VARCHAR(30) NOT NULL, 4 address VARCHAR(20), 5 CONSTRAINT my_constraint UNIQUE (name,address) 6 ); Table created. SQL> SQL> INSERT INTO Students VALUES ('0602751127','Ethan Longwinder','My Way 2'); 1 row created. SQL> INSERT INTO Students VALUES ('0602751129','Ethan Longwinder','My Way 2'); INSERT INTO Students VALUES ('0602751129','Ethan Longwinder','My Way 2') * ERROR at line 1: ORA-00001: unique constraint (PAGH.MY_CONSTRAINT) violated SQL> INSERT INTO Students VALUES ('2103780002','H. Omeless',NULL); 1 row created. SQL> INSERT INTO Students VALUES ('2103780004',NULL,NULL); INSERT INTO Students VALUES ('2103780004',NULL,NULL) * ERROR at line 1: ORA-01400: cannot insert NULL into ("PAGH"."STUDENTS"."NAME") SQL> SELECT * FROM Students; CPR NAME ADDRESS ---------- ------------------------------ -------------------- 0602751127 Ethan Longwinder My Way 2 2103780002 H. Omeless SQL> SQL> INSERT INTO Students VALUES ('0602751129','Bullie Bank','Goa Way 10'); 1 row created. SQL> INSERT INTO Students VALUES ('0602751131','Ethan Longwinder','My Way 4'); 1 row created. SQL> UPDATE Students SET address = 'Urban Collective'; UPDATE Students SET address = 'Urban Collective' * ERROR at line 1: ORA-00001: unique constraint (PAGH.MY_CONSTRAINT) violated SQL> SELECT * FROM Students; CPR NAME ADDRESS ---------- ------------------------------ -------------------- 0602751127 Ethan Longwinder My Way 2 2103780002 H. Omeless 0602751129 Bullie Bank Goa Way 10 0602751131 Ethan Longwinder My Way 4 SQL> SQL> DROP TABLE Students; Table dropped. SQL> SQL> SQL> -- Assertion / referential integrity examples SQL> -- ------------------------------------------ SQL> SQL> CREATE TABLE ITUpeople ( 2 cpr VARCHAR(10) PRIMARY KEY, 3 name VARCHAR(30) NOT NULL, 4 address VARCHAR(20) 5 ); Table created. SQL> SQL> CREATE TABLE Students ( 2 cpr VARCHAR(10) PRIMARY KEY 3 CONSTRAINT ValidCPR REFERENCES ITUpeople(cpr), 4 enrolled VARCHAR(10), 5 graduated VARCHAR(10), 6 gpa REAL CHECK (gpa>=6 AND gpa<=13), 7 CONSTRAINT PositiveStudyTime CHECK (enrolled < graduated) 8 ); Table created. SQL> SQL> INSERT INTO ITUpeople VALUES ('0602751129','Bullie Bank','Goa Way 10'); 1 row created. SQL> INSERT INTO Students VALUES ('0602751129','2003-08-01',NULL,NULL); 1 row created. SQL> UPDATE Students SET graduated = '2001-02-28' WHERE cpr='0602751129'; UPDATE Students SET graduated = '2001-02-28' WHERE cpr='0602751129' * ERROR at line 1: ORA-02290: check constraint (PAGH.POSITIVESTUDYTIME) violated SQL> SQL> DELETE FROM ITUpeople WHERE cpr='0602751129'; DELETE FROM ITUpeople WHERE cpr='0602751129' * ERROR at line 1: ORA-02292: integrity constraint (PAGH.VALIDCPR) violated - child record found SQL> SELECT * FROM ITUpeople; CPR NAME ADDRESS ---------- ------------------------------ -------------------- 0602751129 Bullie Bank Goa Way 10 SQL> SQL> ALTER TABLE Students DROP CONSTRAINT ValidCPR; Table altered. SQL> ALTER TABLE Students ADD CONSTRAINT ValidCPR 2 FOREIGN KEY (cpr) REFERENCES ITUpeople(cpr) ON DELETE CASCADE; Table altered. SQL> DELETE FROM ITUpeople WHERE cpr='0602751129'; 1 row deleted. SQL> SELECT * FROM ITUpeople; no rows selected SQL> SELECT * FROM Students; no rows selected SQL> SQL> DROP TABLE Students; Table dropped. SQL> DROP TABLE ITUpeople; Table dropped. SQL> SQL> SQL> -- Trigger examples SQL> -- ---------------- SQL> -- Using Oracle syntax, not SQL standard syntax. SQL> SQL> -- / at the end is necessary only for SQL*Plus, not part of SQL. SQL> -- Triggers may have compilation errors - these can be displayed SQL> -- in SQL*Plus by writing "show errors". SQL> -- It should be noted that Oracle places some restrictions on triggers, SQL> -- and in particular cannot execute the triggers in GUW. SQL> SQL> CREATE TABLE NetworthHistory ( 2 name VARCHAR(25), 3 oldnetworth INT, 4 newnetworth INT 5 ); Table created. SQL> SQL> CREATE TRIGGER NetWorthTrigger 2 AFTER UPDATE OF netWorth ON MovieExec 3 REFERENCING 4 OLD AS Oldtuple 5 NEW AS Newtuple 6 FOR EACH ROW 7 WHEN (Oldtuple.networth <> NewTuple.networth) 8 BEGIN 9 INSERT INTO NetworthHistory 10 VALUES (:Oldtuple.name,:Oldtuple.networth,:Newtuple.networth); 11 END; 12 / Trigger created. SQL> SQL> UPDATE MovieExec 2 SET netWorth = 29000000 3 WHERE name='George Lucas'; 1 row updated. SQL> SQL> SELECT * FROM NetworthHistory; NAME OLDNETWORTH NEWNETWORTH ------------------------- ------------ ------------ George Lucas 200000000 29000000 SQL> SQL> UPDATE MovieExec 2 SET netWorth = 25000000 3 WHERE name='George Lucas'; 1 row updated. SQL> SQL> SELECT * FROM NetworthHistory; NAME OLDNETWORTH NEWNETWORTH ------------------------- ------------ ------------ George Lucas 200000000 29000000 George Lucas 29000000 25000000 SQL> SQL> DROP TRIGGER NetWorthTrigger; Trigger dropped. SQL> DROP TABLE NetworthHistory; Table dropped. SQL> SQL> -- Instead-of trigger example: SQL> SQL> CREATE VIEW MGMMovie AS 2 SELECT title, year 3 FROM Movie 4 WHERE studioName = 'Paramount'; View created. SQL> SQL> CREATE TRIGGER MGMInsert 2 INSTEAD OF INSERT ON MGMMovie 3 REFERENCING NEW AS NewRow 4 FOR EACH ROW 5 BEGIN 6 INSERT INTO Movie(title, year, studioName) 7 VALUES(:NewRow.title, :NewRow.year, 'MGM'); 8 END; 9 / Trigger created. SQL> SQL> INSERT INTO MGMMovie VALUES ('Robin Hood: Men in Tights', 1993); 1 row created. SQL> SELECT * FROM Movie WHERE year = 1993; TITLE YEAR LENGTH INCOLOR STUDIONAME PRO ------------------------- ---------- ---------- ---------- --------------- --- Robin Hood: Men in Tights 1993 MGM SQL> SQL> DROP VIEW MGMMovie; View dropped. SQL>