Get documents about "The SQL Relational Database Standard
Document Sample
The SQL Relational Database
Standard
Lecture 6
Why a Standard Query Language?
APP1 APP2 APP3
Standard for DBMS query language
My-
Ora DB2
SQL
About SQL
• Stands for Structured Query Language
• Developed at IBM Research for System R
• Includes features of relational algebra and
tuple relational calculus
• The standard for relational data access
• DBMS independent
• Both a Data definition language (DDL) and
a Data manipulation language (DML)
Terminology
Relational Algebra SQL
Relation Table
Tuple Row
Attribute Column
Schema and Catalogs
Support for creating and maintaining schemas are provided
from SQL2. A schema is created by a CREATE SCHEMA
command. For example:
CREATE SCHEMA UNIV AUTHORIZATION dean;
Creates a schema “UNIV” owned by the user with userid “dean”.
A catalog is a named collection of schemas. Referential integrity
constraints can be defined across schemas only if they belong to
the same catalog.
Table Creation
A new relation can be created using the CREATE TABLE
command. The general syntax is as follows:
CREATE TABLE [schema.]table_name [{column descriptions}];
Example:
CREATE TABLE UNIV.DEPARTMENT;
Data Types and Domains (SQL2)
Primitive data types in SQL2 include:
Numeric (int, smallint, float, real, double precision)
Formatted numbers (decimal(i,j), numeric(i,j))
Character string of either fixed length (char(n)) or
of varying length (varchar(n))
Bit strings of either fixed length (bit(n)) or varying length
(bit varying(n))
Date, time and timestamp
Text
…
Data Types and Domains (SQL2)
Non-standard domains may be defined using the CREATE
DOMAIN construct. For example:
CREATE DOMAIN RollNoType AS INT(6);
The domain RollNoType will be substituted by INT(6)
wherever used.
Constraints and Default Values
• The NOT NULL constraint on an attribute disallows NULL
as a valid value
• The DEFAULT construct specifies the default value of an
attribute
• The PRIMARY KEY construct specifies one or more
attributes as the primary key
• The UNIQUE construct specifies alternate (secondary) keys
• The FOREIGN KEY construct ensures referential integrity
Table Creation Example
CREATE TABLE EMPLOYEE (
PANNO VARCHAR(16) NOT NULL, UNIQUE,
EMPNO INT(6) NOT NULL,
NAME VARCHAR (40),
GENDER CHAR,
DOB DATE,
ADDRESS VARCHAR(100),
SALARY DECIMAL(8,2),
REPORTSTO INT(6),
PRIMARY KEY (EMPNO),
FOREIGN KEY (REPORTSTO) REFERENCES
EMPLOYEE (EMPNO));
Table Deletion
Tables can dropped (deleted) using the DROP TABLE command:
DROP TABLE NAME [DEPENDENT {CASCADE | RESTRICT}];
The above command drops the table specified by NAME. If the
CASCADE option is provided, then any foreign-key constraints
and views that reference the table will also be dropped.
If RESTRICT option is specified, then the table is dropped only if
it does not have any incoming references.
Schema Deletion
Schemata can dropped (deleted) using the DROP SCHEMA
command:
DROP SCHEMA NAME {CASCADE | RESTRICT};
The above command drops the schema specified by NAME. If the
CASCADE option is provided, then all tables in the schema will be
automatically dropped.
If RESTRICT option is specified, then the schema is dropped only if
it does not have any elements.
Modifying Tables
Table definitions can be modified using the ALTER TABLE
command. Example:
ALTER TABLE COMPANY.EMPLOYEE
ADD JOB VARCHAR(20);
..adds a new column to the EMPLOYEE table with the name
job and domain VARCHAR of maximum size 20 characters.
Modifying Tables
Consider the following example:
ALTER TABLE COMPANY.EMPLOYEE
ADD JOB VARCHAR(20);
Unless a DEFAULT clause is specified, the value of the JOB
attribute for all the tuples will be NULL.
If no DEFAULT clause is specified, then the NOT NULL
constraint cannot be used.
Modifying Tables
To drop columns, either the CASCADE or RESTRICT options
should be used:
ALTER TABLE COMPANY.EMPLOYEE
DROP PANNO CASCADE;
If CASCADE option is used, then all constraints and views that
refer to the column (PANNO in this case) are also dropped
automatically.
If RESTRICT is used, then the column is dropped only if it does
not have any referencing constraints or views.
Modifying Tables
It is possible to alter a column definition by adding or dropping
DEFAULT clauses:
ALTER TABLE COMPANY.EMPLOYEE
ALTER REPORTSTO SET DEFAULT “007”;
or
ALTER TABLE COMPANY.EMPLOYEE
ALTER REPORTSTO DROP DEFAULT;
All NULL values for a column are updated whenever a new
DEFAULT is set.
The SELECT Operation
• The basic retrieval operation in SQL
• Has no relationship with the SELECT
operation in Relational Algebra
• SQL SELECT allows multiple occurrences
of the same tuple. (Views the relation as a
multi-set rather than a set)
The SELECT Operation
The SELECT-FROM-WHERE form:
SELECT <attribute list>
FROM <table list>
WHERE <condition>;
Example:
SELECT EMPNO, NAME FROM EMPLOYEE
WHERE REPORTSTO = „007‟;
The SELECT Operation
The SELECT-FROM-WHERE form can act on multiple tables
as well. Consider the table DEPARTMENT shown below:
CREATE TABLE DEPARTMENT (
DNO INT(6) NOT NULL,
NAME VARCHAR(20),
ADDRESS VARCHAR(60),
HEAD INT(6),
PRIMARY KEY (DNO),
FOREIGN KEY HEAD
REFERENCES EMPLOYEE(EMPNO));
The SELECT Operation
Consider the following query: What is the name of the person
who heads the “SUPPLIES” department?
SELECT EMPLOYEE.NAME
FROM EMPLOYEE, DEPARTMENT
WHERE EMPNO = HEAD
AND
DEPARTMENT.NAME = „SUPPLIES‟;
Note the use of the table name in order to disambiguate attributes
having the same name. Table names need not be used where there
is no ambiguity.
The SELECT Operation
What happens when the same table has to be used multiple times?
Consider the query: What is the name of the person to whom
Arvind Kulkarni reports to?
SELECT EMPLOYEE.NAME
FROM EMPLOYEE, EMPLOYEE
WHERE EMPLOYEE.NAME = „Arvind Kulkarni‟
AND
EMPLOYEE.REPORTSTO = EMPLOYEE.EMPNO;
Ambiguous!
The SELECT Operation
The solution is to use “aliasing” of table names:
SELECT BOSS.NAME
FROM EMPLOYEE, EMPLOYEE AS BOSS
WHERE EMPLOYEE.NAME = „Arvind Kulkarni‟
AND
EMPLOYEE.REPORTSTO = BOSS.EMPNO;
The SELECT Operation
When the WHERE clause is omitted, SELECT acts as the project
operator of relational algebra:
SELECT NAME, PANNO
FROM EMPLOYEE;
Returns all rows in EMPLOYEE, but only the NAME and PANNO
columns.
The SELECT Operation
When more than one table is specified and WHERE is omitted, the
result is equivalent to a project over the cross product of tables
SELECT EMPLOYEE.NAME, DEPARTMENT.NAME
FROM EMPLOYEE, DEPARTMENT;
Returns rows associating all EMPLOYEE names with all
DEPARTMENT names.
The SELECT Operation
All columns of a table can be retrieved using the „*‟ construct:
SELECT *
FROM EMPLOYEE
WHERE NAME = „Bhadriah‟;
Returns the entire row for all EMPLOYEE relations where
Name is “Bhadriah”.
SELECT * FROM EMPLOYEE, DEPARTMENT;
Returns the cross product of EMPLOYEE and DEPARTMENT.
Tables as Sets
By default SQL treats relations (tables) as bags or multi-sets
rather than sets.
A tuple may appear multiple times in a table. The DISTINCT
clause can be used to make the output into a set.
SELECT DISTINCT NAME
FROM EMPLOYEE;
Tables as Sets
Set theoretic operators UNION, INTERSECTION and EXCEPT
denote operations union, intersection and set difference.
They assume relations to be sets and not multi-sets. Note the use
of DISTINCT in the following example:
SELECT DISTINCT NAME
FROM EMPLOYEE
WHERE SALARY > 300000.00
UNION
SELECT DISTINCT NAME
FROM EMPLOYEE
WHERE SALARY < 24000.00;
Tables as Sets
When DISTINCT is not used and relations are multi-sets, the
following set-theoretic operators can be used:
UNION ALL (for disjoint union)
INTERSECTION ALL (for bag intersection)
EXCEPT ALL (for bag difference)
Substring Comparison
Partial strings can be specified by embedding them around
% symbols and compared using the LIKE comparator:
SELECT *
FROM EMPLOYEE
WHERE NAME LIKE „%Arun%‟;
% symbol matches any number of characters. A single character
can be matched by the underscore (_) symbol.
… WHERE NAME LIKE „_arun__‟;
Arithmetic Operators
Standard numeric operators like addition (+), subtraction (-),
multiplication (*) and division (/) can be applied to attributes
having numeric domains.
The operators may also appear as part of the output table:
SELECT 1.1 * SALARY
FROM EMPLOYEE
WHERE SALARY > 300000;
Summary
• Introduction to the SQL2 standard
• Tables, attributes and values
• Schema creation, table creation
• Entity and foreign key constraints
• SELECT operation and its different
forms…
Introduction to the SQL Standard
Lecture 7
Summary
• Introduction to the SQL2 standard
• Tables, attributes and values
• Schema creation, table creation
• Entity and foreign key constraints
• SELECT operation and its different
forms…
The SELECT Operation
• The SELECT-FROM-WHERE generic form
• Disambiguating attributes
• Aliasing
• Selecting from multiple tables
• Tables as multi-sets and set operations on tables
• Substring comparison
• Arithmetic operators
The EMPLOYEE Table
CREATE TABLE EMPLOYEE (
PANNO VARCHAR(16) NOT NULL, UNIQUE,
EMPNO INT(6) NOT NULL,
NAME VARCHAR (40),
GENDER CHAR,
DOB DATE,
ADDRESS VARCHAR(100),
SALARY DECIMAL(8,2),
REPORTSTO INT(6),
DNO INT(6),
PRIMARY KEY (EMPNO),
FOREIGN KEY (REPORTSTO) REFERENCES
EMPLOYEE (EMPNO));
The DEPARTMENT Table
CREATE TABLE DEPARTMENT (
DNUMBER INT(6) NOT NULL,
NAME VARCHAR(20),
ADDRESS VARCHAR(60),
HEAD INT(6),
PRIMARY KEY (DNO),
FOREIGN KEY HEAD
REFERENCES EMPLOYEE(EMPNO));
Arithmetic Operators
The BETWEEN operator can be used to check for numeric values
within a given range:
SELECT *
FROM EMPLOYEE
WHERE SALARY BETWEEN 300000 AND 400000;
Sorting Results
Output of a SELECT statement can be ordered (sorted) based
on one or more attributes by using the ORDER BY clause:
SELECT *
FROM EMPLOYEE
WHERE SALARY > 300000
ORDER BY NAME, EMPNO;
Nested Queries
Dynamically created tables from SELECT queries can be used
within other SELECT queries:
SELECT NAME
FROM DEPARTMENT
WHERE DNUMBER IN
(SELECT DNO
FROM EMPLOYEE
WHERE SALARY > 300000);
Highlighted query called the “nested query” and the non-highlighted
query called “outer query”.
Nested Queries
Comparisons with an entire table of values can be performed using
the ALL clause:
SELECT NAME
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE SUPERVISOR=„007‟);
Returns the names of employees whose salaries are more than the
salaries of all those who report to „007‟
Nested Queries
Attribute names and scope:
Any unqualified attribute name in a nested query applies to the
innermost block of the query:
SELECT E.NAME
FROM EMPLOYEE AS E
WHERE E.EMPNO IN
(SELECT REPORTSTO
FROM EMPLOYEE
WHERE E.NAME = NAME);
Return all employee names who have the same name as their boss.
Nested Queries
Correlated Nested Queries:
Whenever the WHERE clause of an innermost query refers to an
attribute of the outer query, the query is said to be a correlated
nested query. (Example in the previous slide)
Understanding Correlated Queries:
In order to understand correlated queries, it should be noted that the
query is performed once for each tuple of the outer-most block.
Nested Queries
The (NOT) EXISTS clause is used to check whether the result of a
SELECT operation is empty or not:
SELECT D.HEAD
FROM DEPARTMENT AS D
WHERE NOT EXISTS
(SELECT EMPNO
FROM EMPLOYEE
WHERE REPORTSTO=D.EMPNO);
Return the set of all heads of departments who do not have anyone
directly reporting to them.
Explicit Sets
Explicit sets of values for set theoretic operations can be specified
using parentheses:
SELECT NAME
FROM EMPLOYEE
WHERE REPORTSTO IN („007‟, „008‟, „009‟);
Returns the names of all employees who report to „007‟, „008‟ or
„009‟.
Checking for NULL values
NULL values of attributes can be checked by using the
IS NULL or IS NOT NULL constructs.
SELECT NAME, SALARY
FROM EMPLOYEE AS E
WHERE EXISTS
(SELECT EMPNO
FROM EMPLOYEE
WHERE E.REPORTSTO = EMPNO
AND
REPORTSTO IS NULL);
Return the names and salaries of people who report directly to the
head of the company.
Renaming Attributes
The AS clause can be used to rename attributes in the same way
as it can rename relations.
SELECT NAME AS EMPLOYEE_NAME
FROM EMPLOYEE;
The renamed attribute will be displayed in the output result of the
SELECT query.
Joins
Equijoins may be specified by equating attribute names from
two or more relations:
SELECT NAME, ADDRESS
FROM EMPLOYEE JOIN DEPARTMENT
ON DNO = DNUMBER
WHERE DEPARTMENT.NAME=„Research‟;
The DNO attribute from EMPLOYEE is equated to DNUMBER
attribute of DEPARTMENT for performing the join.
Joins
Natural joins may be specified using the NATURAL JOIN
construct. It automatically finds attributes having the same
names for performing the join.
Relations may be renamed to accommodate natural join using
the AS construct.
SELECT EMPLOYEE.NAME, DEPARTMENT.NAME
FROM EMPLOYEE NATURAL JOIN DEPARTMENT;
Joins
Outer joins may be specified using the following
constructs:
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Aggregate Functions
Aggregate functions like COUNT, SUM, AVG, MAX and MIN
operate on all tuples from the result and return a value:
SELECT COUNT(SALARY), MIN(SALARY),
MAX(SALARY), SUM(SALARY),
AVG(SALARY)
FROM EMPLOYEE;
COUNT can be used on non-numeric attributes as well.
MAX and MIN can also be used on non-numeric attributes if they
have a total ordering defined (Ex: date, char, etc.)
Aggregate Functions
Since aggregate functions return a single value, they can be used
as part of a condition in a WHERE clause:
SELECT E.NAME
FROM EMPLOYEE AS E
WHERE (SELECT COUNT(*)
FROM DEPARTMENT AS D
WHERE D.HEAD=E.EMPNO) >= 2;
Return the names of all employees who head two or more
departments.
Aggregate Functions
In many cases, aggregate functions have to be applied to
subgroups of tuples rather than all tuples. For this, the
GROUP BY clause is useful:
SELECT DNO, COUNT(*), AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO;
For each department, count the number of employees and their
average salaries.
Aggregate Functions
GROUP BY is applied after computing results of a query.
Sometimes it is required to retrieve tuples based on some aggregate
property. For this, the HAVING clause is useful:
SELECT DNO, COUNT(*), AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO
HAVING COUNT(*) > 20;
Return all departments having more than twenty employees, and
show the number of employees and their average salary.
Aggregate Functions
HAVING versus WHERE:
WHERE conditions apply to individual tuples independently.
HAVING conditions apply to groups of tuples.
SELECT Overall Form
SELECT <attribute and function list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>];
Constructs within box braces are optional.
The INSERT Command
Data tuples can be added to tables using the INSERT
command. Example:
INSERT INTO EMPLOYEE
VALUES („1002‟, 002, „Bharath Kumar‟, „M‟,
„9-5-1973‟, „Rajajinagar Bangalore 10‟,
300000, 007, 5);
Inserts an entire EMPLOYEE record with corresponding values.
The INSERT Command
Specific attributes can be populated by explicitly specifying
them by name:
INSERT INTO EMPLOYEE(NAME,ADDR,DNO)
VALUES („Arun K‟, „Yeshwanthpur‟, 5);
The other attributes will get either NULL values or their DEFAULT
values.
All attributes that are left out should hence not have NOT NULL
constraint or must have the DEFAULT constraint.
The INSERT Command
The result of a SELECT query can be provided as data for
an INSERT command:
Consider the following table:
CREATE TABLE EMD (
EMPLOYEE INT(6),
MANAGER INT(6),
DNO INTEGER);
The INSERT Command
The following INSERT dynamically creates data from a SELECT
query for insertion:
INSERT INTO EMD
SELECT E.EMPNO AS EMPLOYEE,
E.REPORTSTO AS MANAGER,
D.DNUMBER AS DNO
FROM EMPLOYEE AS E JOIN
DEPARTMENT AS D
ON E.DNO = D.DNUMBER;
The INSERT Command
• Inserts multiple instances of the same tuple if
called multiple times as long as there is no
violation of UNIQUE constraints
• Insertion fails if referential integrity is violated
• Multiple tuples can be inserted within a single
INSERT command by enclosing each tuple within
parentheses and separating tuples by a comma
Deletion of Tuples
Tuples can be deleted from a table using the
DELETE-FROM-WHERE command:
DELETE FROM EMPLOYEE WHERE EMPNO=007;
(deletes one tuple)
DELETE FROM EMPLOYEE WHERE DNO IN
(SELECT DNUMBER FROM DEPARTMENT
WHERE HEAD=007);
(deletes possibly more than one tuples)
DELETE FROM EMPLOYEE;
(deletes all tuples)
Updation of Tuples
Tuples can be modified using the
UPDATE-SET-WHERE command:
UPDATE EMPLOYEE
SET SALARY = SALARY*1.1
WHERE REPORTSTO=007;
Give a 10% raise in salary for all employees working under 007.
Views (Virtual Tables)
• A table that physically exists in a database is
called a base table
• A view or a virtual table is a table that is derived
from other tables
• The other tables could either be base tables or
other views
• Views need not be stored in the database as tables
(but as queries)
• Update operations to a view are limited; but
querying is not
Specification of Views
Views are specified using the CREATE VIEW command:
CREATE VIEW EMD(EMPL, MGR, DEPT)
AS
SELECT E.NAME, M.NAME, D.NAME
FROM EMPLOYEE AS E,
EMPLOYEE AS M,
DEPARTMENT AS D
WHERE E.REPORTSTO=M.EMPNO
AND
E.DNO=D.DNUMBER;
Views
• A view is always up to date
• Hence characterization of a view is not done
during view definition time, but during
query
• Views can be deleted using the DROP
VIEW command
• Efficient implementation of views a tricky
problem.
Summary
• Introduction to the SQL2 standard
• Tables, attributes and values
• Schema creation, table creation
• Entity and foreign key constraints
• SELECT operation and its different
forms…
The SELECT Operation
• The SELECT-FROM-WHERE generic form
• Disambiguating attributes
• Aliasing
• Selecting from multiple tables
• Tables as multi-sets and set operations on tables
• Substring comparison
• Arithmetic operators
The SELECT Operation
• EXISTS and NULL checks
• Aggregation functions
• GROUP BY and HAVING constructs
• Nested queries: aliasing and scope in nested
queries
• INSERT, DELETE and UPDATE
operations
• Views and Virtual tables
Related docs
