7. SQL

Initially, SEQUEL language (System R) project at IBM

Three relational languages : QUEL, QBE, SQL

(Later renamed) Structured Query Language (SQL)

Currently, Standard Relational database Language
- Users need to be less concerned to migrate to other
types of relational DBMS

Very-High-level language
- Declarative (= Non-procedural) language
- Users do not need to specify how (= what order of) to
execute query operations.
- Say “what to do” rather than “how to do”
- Relational algebra is a procedural language.

Table (= relation), row (= tuple), column (= attribute)

A table consists of :
- Base Table
- View (= Virtual table)

Basic commands;
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)

DDL is used for defining schemas, tables, and views.
- CREATE, DROP, ALTER

DML is used for retrieving and modifying tuples in a table.
- SELECT (FROM WHERE)
- INSERT, DELETE, UPDATE

A table is defined using CREATE TABLE command:
CREATE TABLE R
( A1 : D1
A2 : D2
. . . . .
An : Dn)
Constraints )
- R : relation name
- Ai : attribute name
- Di : domain (=data type) of Ai
- Constraints : Integrity Constraints

A relation defined by CREATE TABLE is called a “base table”.
- The relation (and its tuples) are “physically stored”.
- All created tables are initially “empty”.
- Attributes are ordered as they are specified in CREATE TABLE

System Defined

• Numeric : INT, SMALLINT, FLOAT(or REAL), . . .
• Character String : CHAR(n), VARCHAR(n), CLOB, . .
• Bit String : BIT(n), BIT VARYING(n), BLOB, . .
• Boolean : TRUE, FALSE, UNKOWN
• DATE : YEAR, MONTH, DAY, . . .
• . . . . . . . .

User Defined

• It is possible for users to specify domain of attribute directly;
• For example, we can create domain “SSN-TYPE”;
  CREATE DOMAIN SSN-TYPE AS CHAR(9)
• We can use this SSN-TYPE for defining attributes SSN, Super_SSN,
  Mger-SSN, . . .;
• What if we change later SSN data type to another one?

Key Integrity
- Primary key and keys can be specified by PRIMARY KEY
and UNIQUE, respectively

Entity Integrity
- This must be specified by NOT NULL on PRIMARY KEY
- NOT NULL also optionally can be specified on other attributes.
: For example, employee names, phone may be specified by
non null; What if new employee’s name is unknown?

Referential Integrity
- This is specified by FOREIGN KEY REFERENCES
- SQL’s default action is to reject the operation that violates
referential integrity violation
- SQL also provides user specified trigger action

DEFAULT
- Default value is included in new tuple if explicit value is not provided
- For example, default manager for new department is;
CREATE TABLE DEPT
Mgr_SSN CHAR(9) DEFAULT 999999999
- If no default value is specified, the default DEFAULT value is NULL.

CHECK
- We can restrict domain to specific values;
- For example, department numbers are between 1 and 20;
DNO INT . . .CHECK (DNO > 0) AND (DNO < 21)
- CHECK can be also used with CREATE DOMAIN
CREATE DOMAIN DNO AS INT
DNO INT . . .CHECK (DNO > 0) AND (DNO < 21)

                Total 6 trigger actions possible; For example: 

(1) ON UPDATE CASCADE
: Change the value of referencing FK to the updated (new) PK
value for all the referencing tuples;

(2) ON DELETE CASCADE
: Delete all the referencing tuples;

(3) ON UPDATE SET DEFAULT

(4) ON DELETE SET DEFAULT
: The value of FK in referencing tuples is changed to default value;