------------------------------------------------------------------------
-- SQL EXAMPLES                                                       --
-- Introduction to Databases, IT University of Copenhagen, Fall 2004  --
-- Taught by Rasmus Pagh                                              --
-- October 22, 2004                                                   --
------------------------------------------------------------------------

-- Primary key example:
-- --------------------

CREATE TABLE Students (
  cpr VARCHAR(10) PRIMARY KEY,
  name VARCHAR(20),
  address VARCHAR(20)
);

INSERT INTO Students VALUES ('0602751127','Ethan Longwinder','My Way 2');
INSERT INTO Students VALUES ('0602751127','Ethan Longwinder II','My Way 2');
INSERT INTO Students VALUES (NULL,'Mysterio Student','No Way 8');
SELECT * FROM Students;

DROP TABLE Students;


-- UNIQUE key examples:
-- --------------------

CREATE TABLE Students (
  cpr VARCHAR(10) PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  address VARCHAR(20),
  CONSTRAINT my_constraint UNIQUE (name,address)
);

INSERT INTO Students VALUES ('0602751127','Ethan Longwinder','My Way 2');
INSERT INTO Students VALUES ('0602751129','Ethan Longwinder','My Way 2');
INSERT INTO Students VALUES ('2103780002','H. Omeless',NULL);
INSERT INTO Students VALUES ('2103780004',NULL,NULL);
SELECT * FROM Students;

INSERT INTO Students VALUES ('0602751129','Bullie Bank','Goa Way 10');
INSERT INTO Students VALUES ('0602751131','Ethan Longwinder','My Way 4');
UPDATE Students SET address = 'Urban Collective';
SELECT * FROM Students;

DROP TABLE Students;


-- Assertion / referential integrity examples
-- ------------------------------------------

CREATE TABLE ITUpeople (
  cpr VARCHAR(10) PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  address VARCHAR(20)
);

CREATE TABLE Students (
  cpr VARCHAR(10) PRIMARY KEY
               CONSTRAINT ValidCPR REFERENCES ITUpeople(cpr),
  enrolled VARCHAR(10),
  graduated VARCHAR(10),
  gpa REAL CHECK (gpa>=6 AND gpa<=13),
  CONSTRAINT PositiveStudyTime CHECK (enrolled < graduated)
);

INSERT INTO ITUpeople VALUES ('0602751129','Bullie Bank','Goa Way 10');
INSERT INTO Students VALUES ('0602751129','2003-08-01',NULL,NULL);
UPDATE Students SET graduated = '2001-02-28' WHERE cpr='0602751129';

DELETE FROM ITUpeople WHERE cpr='0602751129';
SELECT * FROM ITUpeople;

ALTER TABLE Students DROP CONSTRAINT ValidCPR;
ALTER TABLE Students ADD CONSTRAINT ValidCPR 
                         FOREIGN KEY (cpr) REFERENCES ITUpeople(cpr) ON DELETE CASCADE;
DELETE FROM ITUpeople WHERE cpr='0602751129';
SELECT * FROM ITUpeople;
SELECT * FROM Students;

DROP TABLE Students;
DROP TABLE ITUpeople;


-- Trigger examples
-- ----------------
-- Using Oracle syntax, not SQL standard syntax.

-- / at the end is necessary only for SQL*Plus, not part of SQL.
-- Triggers may have compilation errors - these can be displayed
-- in SQL*Plus by writing "show errors".
-- It should be noted that Oracle places some restrictions on triggers,
-- and in particular cannot execute the triggers in GUW.

CREATE TABLE NetworthHistory  (
  name VARCHAR(25),
  oldnetworth INT,
  newnetworth INT
);

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
    OLD AS Oldtuple
    NEW AS Newtuple
FOR EACH ROW
WHEN (Oldtuple.networth <> NewTuple.networth)
BEGIN
    INSERT INTO NetworthHistory
    VALUES (:Oldtuple.name,:Oldtuple.networth,:Newtuple.networth);
END;
/

UPDATE MovieExec
SET netWorth = 29000000
WHERE name='George Lucas';

SELECT * FROM NetworthHistory;

UPDATE MovieExec
SET netWorth = 25000000
WHERE name='George Lucas';

SELECT * FROM NetworthHistory;

DROP TRIGGER NetWorthTrigger;
DROP TABLE NetworthHistory;

-- Instead-of trigger example:

CREATE VIEW MGMMovie AS
SELECT title, year
FROM Movie
WHERE studioName = 'Paramount';

CREATE TRIGGER MGMInsert
INSTEAD OF INSERT ON MGMMovie
REFERENCING NEW AS NewRow
FOR EACH ROW
BEGIN
INSERT INTO Movie(title, year, studioName)
  VALUES(:NewRow.title, :NewRow.year, 'MGM');
END;
/

INSERT INTO MGMMovie VALUES ('Robin Hood: Men in Tights', 1993);
SELECT * FROM Movie WHERE year = 1993;

DROP VIEW MGMMovie;