######################################################################
# SQL EXAMPLES                                                       #
# Introduction to Databases, IT University of Copenhagen, Fall 2003  #
# Taught by Rasmus Pagh                                              #
# October 21, 2003                                                   #
######################################################################

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

CREATE TABLE Students (
  cpr CHAR(10) PRIMARY KEY,
  name CHAR(20),
  address CHAR(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 CHAR(10) PRIMARY KEY,
  name CHAR(30) NOT NULL,
  address CHAR(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 CHAR(10) PRIMARY KEY,
  name CHAR(30) NOT NULL,
  address CHAR(20)
);

CREATE TABLE Students (
  cpr CHAR(10) PRIMARY KEY
               CONSTRAINT ValidCPR REFERENCES ITUpeople(cpr),
  enrolled CHAR(10),
  graduated CHAR(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.
# Not currently working at ITU because triggers are not properly enabled.

# / 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".

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
    UPDATE MovieExec
    SET netWorth = OldTuple.netWorth
    WHERE cert = NewTuple.cert;
END;
/

UPDATE MovieExec
SET netWorth = 0;


# 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;
DROP TRIGGER MGMInsert;