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

# All of the below is for use in Oracle.

# Preliminaries:
# --------------

# Before autotrace can be used, the following table must be created:

create table PLAN_TABLE (
	statement_id 	varchar2(30),
	timestamp    	date,
	remarks      	varchar2(80),
	operation    	varchar2(30),
	options       	varchar2(30),
	object_node  	varchar2(128),
	object_owner 	varchar2(30),
	object_name  	varchar2(30),
	object_instance numeric,
	object_type     varchar2(30),
	optimizer       varchar2(255),
	search_columns  number,
	id		numeric,
	parent_id	numeric,
	position	numeric,
	cost		numeric,
	cardinality	numeric,
	bytes		numeric,
	other_tag       varchar2(255),
	partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
	other		long);

# To set autotrace and timing on:

set autotrace on;
set timing on;

# To get sample data (works at ITU until February 2004, may take a while):

CREATE TABLE projects (
  first1 CHAR(10), last1 CHAR(10), grade1 INT,
  first2 CHAR(10), last2 CHAR(10), grade2 INT,
  first3 CHAR(10), last3 CHAR(10), grade3 INT
);

INSERT INTO projects (SELECT * FROM pagh.projects);

###############################################################
# Please remove the table (by writing "DROP TABLE projects;") #
# when you are done, to free space on the database server.    #
###############################################################

# Full table scan versus index scan:
# (notice the execution plan and the number of db block gets)

SELECT count(*) FROM projects WHERE last2='Gearloose' and last3='Rabbit';

INSERT INTO projects values ('Rasmus','Pagh',11,'Toger','Norgaard',10,'Lars','Bengtsson',10);

CREATE INDEX groupmembers23 on projects (last2,last3,first2,first3);

INSERT INTO projects values ('Rasmus','Pagh',13,'Toger','Norgaard',11,'Lars','Bengtsson',11);

SELECT count(*) FROM projects WHERE last2='Gearloose' and last3='Rabbit';

SELECT count(*) FROM projects WHERE last2='Gearloose';

SELECT count(*) FROM projects WHERE last3='Rabbit';


# Buffering
# (notice the number of db block gets and physical reads)

SELECT count(*) FROM projects WHERE last1='Gearloose' and last2='Rabbit';

SELECT max(grade2) FROM projects WHERE last1='Gearloose' and last2='Rabbit';

DROP TABLE projects;