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

# Commit and rollback examples
# ----------------------------

# The user executing the SQL command is given before the >.
# (The two users could also be two different connections
# to the database by the same user, if you want to try it out alone.)

# Isolation level SERIALIZABLE:

pagh>  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
pagh2> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

pagh>  SELECT * FROM Primes;
pagh2> SELECT * FROM pagh.Primes;

pagh>  INSERT INTO Primes VALUES (41);
pagh2> INSERT INTO pagh.Primes VALUES (43);

pagh>  SELECT * FROM Primes;
pagh2> SELECT * FROM pagh.Primes;

pagh>  COMMIT;
pagh2> SELECT * FROM pagh.Primes;

pagh2> COMMIT;
pagh2> SELECT * FROM pagh.Primes;


# Isolation level READ COMMITTED (default in Oracle):

pagh>  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
pagh2> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

pagh>  CREATE TABLE Primes (p INT);
pagh>  GRANT SELECT ON Primes TO pagh2;
pagh>  INSERT INTO Primes VALUES (2);

pagh2> SELECT * FROM pagh.Primes;
pagh2> INSERT INTO pagh.Primes VALUES (2003);
pagh2> SELECT * FROM pagh.Primes;

pagh>  COMMIT;
pagh2> SELECT * FROM pagh.Primes;
pagh>  SELECT * FROM Primes;

pagh2> COMMIT;
pagh>  SELECT * FROM Primes;

pagh>  INSERT INTO Primes VALUES (3);
pagh>  ROLLBACK;
pagh>  SELECT * FROM Primes;


# Process killed example
# ----------------------

COMMIT;
INSERT INTO Primes VALUES (5);
SELECT * FROM Primes;

# Now use the operating system feature to kill SQL*Plus, and log in again

SELECT * FROM Primes;
DROP TABLE Primes;