SQL

Components of SQL

• Data definition language (DDL)– To create database structures– To enforce constraints etc.

• Data manipulation language (DML)– To access data

• Authorization language – To grant privileges to users

• SQL supports dynamic database definition– Structures can be modified easily

About SQL

• Its fairly standard across the all relational database management systems but…

• There are some differences in– Syntax– Data types– Functionality

• As a rule, Oracle is a better environment in which to learn SQL than Access… – But we will look at both, with an emphasis on

Oracle

DDL Commands

• CREATE TABLE

• CREATE INDEX

• ALTER TABLE

• RENAME TABLE

• DROP TABLE

• DROP INDEX

• CREATE VIEW

CREATE TABLECREATE TABLE base-table-name (colname datatype

[column constraints NULL/NOT NULL, DEFAULT…, UNIQUE, CHECK…, PRIMARY KEY],

[,colname datetype [column constraints …]]...

[table constraints PRIMARY KEY…, FOREIGN KEY…, UNIQUE…, CHECK…]

[storage specifications]);

Student Table

CREATE TABLE Student(stuId CHAR(6),lastName CHAR(20) NOT NULL,firstName CHAR(20) NOT NULL,major CHAR(10),credits SMALLINT DEFAULT 0,CONSTRAINT Student_stuId_pk PRIMARY KEY

(stuId),CONSTRAINT Student_credits_cc CHECK

(credits>=0 AND credits < 150));

Create Tables for University DBCREATE TABLE Student ( previous slide )CREATE TABLE Faculty (

facId CHAR(6),name CHAR(20) NOT NULL,department CHAR(20) NOT NULL,rank CHAR(10),CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));

CREATE TABLE Class(classNumber CHAR(8),facId CHAR(6) NOT NULL,schedule CHAR(8),room CHAR(6),CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId)) ;

CREATE TABLE Enroll (stuId CHAR(6),classNumber CHAR(8),grade CHAR(2),CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId),CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES

Class (classNumber),CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student(stuId));

Rules for Identifiers

• No SQL keywords may be used

• Table name must be unique within the database

• Column names user must be unique within the table

Data Types• Each column must have a data type specified

– Numeric, fixed and varying-length character strings, bit strings, and user-defined types

• Data types vary by rdbms– See text and mysql documentation

User-defined Data Types

• We can declare our own domains and set constraints at the domain level:

CREATE DOMAIN creditValues INTEGER DEFAULT 0 CHECK (VALUE >=0 AND VALUE <150);

• This domain (data) type can then be used in defining columns:– In Student, can write credits creditValues – And we can use creditValues to define other data

•

Constraints

• These can be defined at:– column level– table level

• Column-level constraints– NULL/NOT NULL – UNIQUE– PRIMARY KEY– CHECK – DEFAULT – Written immediately after column name or

datatype

Table-level constraints• PRIMARY KEY

– For composite keys (simple ones at column level)

• FOREIGN KEY– Requires that the referenced table has already been

created

• UNIQUE– Specifies values in a combination of columns that must

be unique – Used to specify candidate keys

• ON DELETE / ON UPDATE for foreign keys– CASCADE / SET NULL/ SET DEFAULT / NO ACTION;

• Constraints can be given a name– This can be useful for disabling them at times

Indexes

• We can create any number of indexes for a given table– They facilitate fast retrieval of records with specific

values in a column– They don’t always return unique values

• Overhead is involved– The rdbms must maintain the index

• Tree data structures used to order values on index

CREATE INDEX

CREATE [UNIQUE] INDEX indexname ON basetablename (colname [order] [,colname [order]]...) [CLUSTER] ;

CREATE INDEX Student_lastName_firstName_ndx ON Student (lastName, firstName);

• UNIQUE enforces unique values for indexed column or combination of columns– The column need not be unique– Order is ASC (default) or DESC

• CLUSTER specification keeps records with same value for indexed field together (only one per table)

•

ALTER TABLE

• To add a new columnALTER TABLE basetablename ADD columnname datatype;ALTER TABLE Student ADD COLUMN birthdate DATETYPE;

• To drop a columnALTER TABLE basetablename DROP COLUMN columnname;ALTER TABLE Student DROP COLUMN major;

• To add a constraintALTER TABLE basetablename ADD CONSTRAINT constraint_defn;

• To drop a constraintALTER TABLE basetablename DROP CONSTRAINT constraint_name;

Other Changes to Tables

• Renaming a table:RENAME TABLE old-table-name TO new-table-name;

RENAME TABLE FACULTY TO TEACHERS;

• Dropping a table:DROP TABLE basetablename;

DROP TABLE CLASS;

• Dropping an index:DROP INDEX indexname;

DROP INDEX Student_lastName_fristName_ndx;

SQL DML

• Non-procedural, declarative language

• Can be – interactive– embedded in host language– stand-alone (SQL/PSMs)

• Basic commandsSELECT

UPDATE

INSERT

DELETE

SELECT Statement

SELECT [DISTINCT] col-name [AS newname], [,col-name..]…

FROM table-name [alias] [,table-name]…

[WHERE predicate]

[GROUP BY col-name [,col-name]…[HAVING predicate]

or …

[ORDER BY col-name [,col-name]…];

UniversityDB

stuID

lastName

firstName

major

credits

classNo

schedule

room

facID

facID

name

dept

rank

stuID

classNo

grade

Student Enroll Class Faculty

Get names & ids of all Math majors

SELECT lastname, firstname, stuID

FROM Student

WHERE major = ‘Math’;

Get all info about CSC faculty

SELECT *

FROM Faculty

WHERE dept = ‘CSC’;

Get the course numbers for all courses with enrollment

SELECT DISTINCT classNum

FROM Enroll;

Display the Student table

SELECT *

FROM Student;

Get names & ID’s of all faculty, listed alphabetically

SELECT name, facID

FROM Faculty

ORDER BY name;

SELECT name, facIDFROM FacultyORDER BY name, facID;

Get names of all math majors with > 30 credits

SELECT lastName, firstName

FROM Student

WHERE major = ‘MATH’

AND credits > 30;

Find ID’s and names of all students in ART 103A

SELECT Student.stuID, lastName, firstName

FROM Student, Enroll

WHERE Student.stuID = Enroll.stuID;

(Joining two tables)

Find ID’s (in ascending order) and grades of all students taking a

course with faculty ‘F110’

SELECT stuID, gradeFROM Class, EnrollWHERE Class.classNumber =

Enroll.classNumberAND facID = ‘F110’

ORDER BY stuID;

Find class #’s, names & majors of all students taking a course with

faculty ‘F110’

SELECT Enroll.classNumber, lastName, firstName, major

FROM Class, Enroll, StudentWHERE Class.classNumber = Enroll.classNumber

AND Enroll.stuID = Student.stuIDAND facID = ‘F110’

ORDER BY stuID;

Find class #’s of all of classes that meet in the same room

SELECT C1.classNumber, C2.classNumber

FROM Class C1, Class C2

WHERE C1.room = C2.room

AND C1.classNumber <> C2.classNumber

Must compare the Class table with itself – requires aliases.

This example works on the assumption that a maximum of two classes will meet in the same room

Subqueries

• These are queries within queries

SELECT columnName(s)

FROM tableName(s)

WHERE someColumnName = or IN(SELECT columnName(s)

FROM tableName(s)

WHERE someCondition);

The subquery executes FIRST

Find all class #’s for Byrne of the Math Dept

SELECT classNumber

FROM Class

WHERE facID =

(SELECT facID

FROM Faculty

WHERE name = ‘Byrne’

AND dept = ‘Math’);

Aggregate functions

• COUNT

• SUM

• MAX

• MIN

• AVG

How many students are in ART 103A?

SELECT COUNT (DISTINCT stuID)

FROM ENROLL

WHERE classNumber = ‘ART103A’;

Find the ID of the student with the most credits

SELECT stuID

FROM Student

WHERE credits =

(SELECT MAX(credits)

FROM Student);

Must use subquery

List each class# along with its enrollment

SELECT classNumber, COUNT(stuID)

FROM Enroll

GROUP BY classNumber;

Recap SQL commands so far

• SELECT is basic– * is the ‘wildcard’ or list column names

• WHERE for all conditions– Including joins

• ORDER BY for sorting• AND, OR, NOT logical operations• AS for aliases

– good for using same table twice• Subqueries• Aggregate functions (Example 18 is excellent)

– COUNT, SUM, MAX, MIN, AVG– GROUP BY

Some new SQL DML

• Nested subqueries• EXISTS / NOT EXISTS• UNION• Formatting query results• HAVING with GROUP BY• LIKE • NULL• Updating operators

Nested queries

• Get an alphabetical list of names & id’s of all students in any class taught by F110

SELECT lastName, firstName, stuIDFROM StudentWHERE stuID IN (SELECT stuID FROM Enroll WHERE classNumber IN (SELECT classNumber FROM Class WHERE facID = 'F110'))ORDER BY lastName, firstName;

1

2

3

EXISTS / NOT EXISTSCorrelated subqueries

• Names of all students enrolled in CSC201A

• Also, NOT EXISTS

SELECT lastName, firstName FROM StudentWHERE EXISTS (SELECT * FROM Enroll WHERE Enroll.stuID = Student.stuID

AND classNumber = ‘CSC201A’;

UNION

• Can be used instead of OR in a condition if the condition

SELECT facIDFROM FacultyWHERE department = 'History'

UNION SELECT facIDFROM ClassWHERE room = 'H221';

One query

Formatting query results

• List the student id and # courses each student has taken.– Uses an expression, a string constant and an

alias for a column name.

SELECT stuID, 'Number of courses =', credits/3 AS CoursesFROM Student;

HAVING

• HAVING : GROUP BY as WHERE : SELECT

• Find all courses with fewer than 3 students

SELECT className FROM EnrollGROUP BY classNameHAVING COUNT(*) < 3;

LIKE

• For pattern matching

• List the names of all Math classes

SELECT *FROM ClassWHERE classNumber LIKE 'MTH*';

In Access, use *

NULL

• Find the stuID & classes of all students with missing grades.

SELECT stuID, classNumberFROM EnrollWHERE grade IS NULL;

Can’t use = here

Updating operators

• UPDATE

• INSERT

• DELETE

UPDATE Operator

UPDATE tablename

SET columnname = expression

[columnname = expression]... [WHERE predicate];

• Used for changing values in existing records• Can update one or many records in a table

– All that meet the WHERE condition will be changed

• For null value, use SET columnname = NULL• Can use a sub-query to identify records to be updated

UPDATE examples

• See hand-out, pp. 323-325– Examples 1-6

INSERT Operator

INSERTINTO tablename [(colname [,colname]...)]VALUES (constant [,constant]...);

• Used for inserting new records into database, one at a time

• Not necessary to name columns if values are supplied for all columns, in proper order

• To insert null value for a column, specify only the other columns or write null as the value

• Can specify values for some columns, in any order, as long as values match order

INSERT examples

• See hand-out, 326-327– Examples 1-4

DELETE Operator

DELETEFROM tablenameWHERE predicate;

• Used to remove records from a table• Can delete one or many records at a time• Operation may not work if it would violate

referential integrity• Can use a sub-query to target records to be

deleted• If you delete all records from a table, its structure

still remains, and you can insert into it later

DELETE examples

• See hand-out, pp. 328-329– Examples 1-4

Exercises

• Use the ASSIGNS DDL commands to create an Access database– Code the SQL commands for

• Hand-out, pp. 355-356, problems 6.3-6.9; 6.11-6.21

– Code the SQL commands for• Hand-out, pp. 357-358, Lab Exercise 6.2c

Active Databases

• DBMS monitors database to prevent illegal states– Constraints – Triggers

Constraints

• Constraints– Specified when table is created, or later– IMMEDIATE MODE

• constraint checked when each INSERT, DELETE, UPDATE is performed

– DEFERRED MODE – see pp. 329-330• postpones constraint checking to end of transaction

SET CONSTRAINT name DEFERRED

– Can use DISABLE CONSTRAINT name, ENABLE CONSTRAINT name

Triggers

• ECA model:– Event

• some change made to the database– Condition

• a logical predicate – Action

• a procedure done when the event occurs and the condition is true, also called firing the trigger

Triggers

• Can be fired before or after – Insert– Update– Delete

• It accesses values it needs as :OLD. and :NEW.– :OLD refers to values being deleted or replaced – :NEW refers to the values just inserted or those

replacing old values• Can specify how many times a trigger fires

– once for each triggering statement, or – for each row that is changed by the statement

Trigger Syntax

CREATE TRIGGER trigger_name[BEFORE/AFTER] [INSERT/UPDATE/DELETE]

ON table_name[FOR EACH ROW] [WHEN condition]BEGIN

trigger bodyEND;

• Can ALTER TRIGGER name DISABLE;• ALTER TRIGGER name ENABLE;• DROP TRIGGER name;• See examples in Figure 6.5

Relational Views

• Can be subsets of base tables, subsets of joins, or contain calculated data

• Many reasons for views – Allow different users to see the data in

different forms– Provide a simple authorization control device– Free users from complicated DML operations– If database is restructured, view can keep the

user's model constant

Create ViewCREATE VIEW viewname [(viewcolname(s)…]

AS SELECT colname(s)...

FROM basetablename(s)…

WHERE condition(s);

• Value-independent view– A vertical subset of a table, with no WHERE,

• Value-dependent view– Choose only certain rows, using WHERE

• Join view• Can use functions in SELECT

Using Views• Views can be queried

– SELECT colname(s) FROM viewname– Views can be used to create other views

• Views can be updated – The primary key must be in the view– An INSTEAD OF trigger is used to update base

table(s), and the change is reflected in the view

CREATE TRIGGER InsertStuVw2INSTEAD OF INSERT ON StudentVw2FOR EACH ROWBEGIN

INSERT INTO StudentVALUES (:NEW.stuId, :NEW.lastName, :NEW.firstName,

:NEW. Credits);

END;

Ending Transactions

• COMMIT makes permanent changes in the current transaction

• ROLLBACK undoes changes made by the current transaction