SQLThe Relational Database Standard

1
SQL-The Relational Database Standard

• Data Definition Statements
• Insert, Delete, and Update Statements
• Basic Queries
• Views (Virtual Tables)

2
1 Data Definition Statements The CREATE TABLE
Command The CREATE TABLE command is used to
specify a new relation by giving it a name and
specifying its attributes and constraints. The
key, entity integrity, and referential integrity
constraints can be specified after the attributes
are declared, or they can be added later using
the ALTER TABLE COMMAND. Example (p70) CREATE
TABLE Student ( Id INTEGER, Name CHAR(20), Ad
dress CHAR(50), Status CHAR(10) )
3
Data types and Domains Data types numeric
integer numbers of various size
INTEGER or INT SMALLINT
real numbers of various precision
FLOAT REAL
DOUBLE PRECISION (Formatted numbers can be
declared by using DECIMAL(i, j), DEC(i, j), or
NUMBER(i, j).
4
character-string (default for n is one)
fixed-length CHAR(n) or CHARACTER(n)
varying-length VARCHAR(n), CHAR VARYING(n), or
CHARACTER VARYING(n) bit-string (default for n
is one) fixed-length BIT(n)
varying-length BIT VARYING(n) date ten
positions YYYY-MM-DD,
5
time eight positions HHMMSS time(i)
where i is the time fractional seconds
precision, specifies i1 additional positions for
TIME. In SQL2, it is possible to specify the
data type of each attribute directly
alternatively, a domain can be declared, and the
domain name used. Example CREATE DOMAIN
SSN_TYPE AS CHAR(9)
6
Specifying Constraints and Default Values in
SQL2. NOT NULL Example CREATE TABLE Student (
Id INTEGER NOT NULL, Name CHAR(20) NOT
NULL, Address CHAR(50), Status CHAR(10) )
7
DEFAULT ltvaluegt Example CTEATE TABLE Student
( DID INTEGER NOT NULL DEFAULT 1,
Id INTEGER NOT NULL, Name CHAR(20) NOT
NULL, Address CHAR(50), Status CHAR(10) )
8
Following the attribute specifications,
additional table constraints can be specified on
a table, including keys and referential
integrity. Example CREATE TABLE EMPLOYEE (
FNAME VARCHAR(15) NOT NULL, MINT CHAR, LNAME V
ARCHAR(15) NOT NULL, SSN CHAR(9) NOT
NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHA
R, SALARY DECIMAL(10,2), SUPERSSN CHAR(9), DNO
INT NOT NULL DEFAULT 1, PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER))

9
PRIMARY KEY UNIQUE clause specifies alternate (or
secondary) keys CTEATE TABLE DEPARTMENT (
DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT
NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATE DAT
E, PRIMARY KEY (DNUMBER), UNIQUE(DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN))
10
FOREIGN KEY. A referential triggered action
clause can be attached to a foreign key to
specify the action to be taken if a referential
integrity constraint is violated upon deletion or
modification. The options include SET NULL,
CASCADE, and SET DEFAULT. An option must be
qualified with ON DELETE or ON UPDATE. A
constraint can be given a name using the keyword
CONSTRAINT.
11
CTEATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT
NULL, MINI CHAR, LNAME VARCHAR(15) NOT
NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRE
SS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2),
SUPERSSN CHAR(9), DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT
EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES
EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE
CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY
(DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE
SET DEFAULT ON UPDATE CASCADE)
12
The relations declared through CREATE TABLE
statements are called base tables this means
that the relation and its tuples are actually
created and stored as a file by the DBMS. In SQL
the attributes in a base table are considered to
be ordered in the sequence in which they are
specified in the CREATE TABLE statement.
However, rows are not considered to be ordered
within a relation.
13
2 Insert, Delete, and Update Statements The
INSERT Command U1 INSERT INTO EMPLOYEE VALUES
('Richard', 'K', 'Marini', '653298653',
'1962-12-30' '98 Oak Forest, Katy, TX' 'M',
37000, '987654321' 4) A second form of the
INSERT statement allows the user to specify
explicit attribute names that correspond to the
values provided in the INSERT command. These
attributes must include all attributes with NOT
NULL specification and no default value. U1A
INSERT INTO EMPLOYEE (FNAME, LNAME, DNO, SSN)
VALUES ('Richard', 'Marini', 4, '653298653')
14
Integrity Constraints U2 INSERT INTO EMPLOYEE
(FNAME, LNAME, SSN, DNO) VALUES ('Robert',
'Hatcher', '980760540', 2) U2 is rejected if
referential integrity checking is provided by the
DBMS, because no DEPARTMENT tuple exists in the
database with department number 2. U2A INSERT
INTO EMPLOYEE (FNAME, LNAME, SSN, DNO) VALUES
('Robert', Hatcher', '980760540', 5)
15
The DELETE Command U4A DELETE FROM EMPLOYEE
WHERE LNAME'Brown' U4B DELETE FROM EMPLOYEE
WHERE SSN'123456789' U4C DELETE FROM
EMPLOYEE WHERE DNO IN (SELECT DNUMBER
FROM
DEPARTMENT WHERE DNAME'Research') U4D
DELETE FROM EMPLOYEE
16
The UPDATE Command U5 UPDATE PROJECT SET
PLOCATION'Bellaire', DNUM5 WHERE PNUMBER10
U6 UPDATE EMPLOYEE SET SALARY SALARY1.1
WHERE DNO IN (SELECT DNUMBER FROM
DEPARTMENT WHERE
DNAME'Research')
17
3 Basic Queries SQL allows a table to have two or
more tuples that are identical in all their
attribute values. Hence, in general, an SQL
table is not a set of tuples rather it is
multiset. The SELECT-FROM-WHERE Structure of SQL
Queries The basic form of the SELECT statement
is SELECT ltattribute listgt FROM lttable listgt
WHERE ltconditiongt
18
Query 0 Retrieve the birth date and address of
the employee(s) whose name is John B. Smith
Q0 SELECT BDATE, ADDRESS FROM EMPLOEE WHERE
FNAME 'John' AND MNITI'B' AND LNAME 'Smith'
19
Query 1 Retrieve the name and address of all
employees who work for the Research department.
Q1 SELECT FNAME, LNAME, ADDRESS FROM EMPLOEE,
DEPARTMENT WHERE DNAME 'Research' AND
DNUMBERDNO In general, any number of select
and join conditions may be specified in a single
SQL query.
20
Query 2 For every project located in Stafford,
list the project number, the controlling
department number, and the department managers
last name, address, and birth date. Q2 SELECT
PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM
PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUMDNUMBER
AND MGRSSNSSN AND
PLOCATIONStafford
21
Dealing with Ambiguous Attribute Names and
Renaming (Aliasing) In SQL , the same name can be
used for more than one attributes as long as the
attributes are in different relations. When
referring to these attributes, we must qualify
them. This is done by prefixing the relation name
to the attribute name and separating the two by a
period.
22
Query 8 For each employee, retrieve the
employees first and last name and the first and
last name of his or her immediate supervisor.
Q8 SELECT E.FNAME, E.LNAME, S.FNAME,
S.LNAMEFROM EMPLOYEE AS E, EMPLOYEE AS S WHERE
E.SUPPERSSNS.SSN In this case, we are allowed
to declare alternative relation names E and S,
called aliases or tuple variables, for the
EMPLOYEE relation. An alias can follow the
keyword AS, or it can directly follow the
relation name. It is also possible to rename the
relation attributes within the query in SQL2 by
giving them aliases for example EMPLOYEE AS
E(FN,MI,LN,SSN,BD,ADDR,SEX,SAL,SSSN,DNO)
23
Unspecified WHERE-Clause and Use of Asterisk
() If WHERE-clause is missing, all tuples of the
relation specified in the FROM-clause will be
selected. If more than one relation is specified
in the FROM-clause and there is no WHERE-clause,
then the CROSS PRODUCT of these relations is
selected. To retrieve all the attribute values of
the selected tuples, we just use an asterisk.
24

• Tables as Sets in SQL
• SQL does not automatically eliminate duplicate
  tuples in the results of queries for the
  following reasons
• Duplicate elimination is an expensive operative
  operation.
• The user may want to see duplicate tuples in the
  result of a query.
• When an aggregate function is applied to tuples,
  in most cases we do not want to eliminate
  duplicates.

25
If we do want to eliminate duplicate tuples, we
use the keyword DISTINCT in the SELECT-clause
Query 11 Retrieve the salary of every employee
(Q11) and all distinct salary values (Q11A) Q11
SELECT ALL SALARY FROM EMPLOYEE Q11A SELECT
DISTINCT SALARY FROM EMPLOYEE
26
When use set operations, we must make sure that
the two relations are compatible. Query 4 Make
a list of all project numbers for projects that
involve an employee whose last name is Smith,
either as a worker or as a manager of the
department that controls the project.
Q4(SELECT DISTINCT PNUMBER FROM PROJECT,
DEPARTMENT, EMPLOYEE WHERE DNUMDNUMBER AND
MGRSSNSSN AND
LNAMESmith) UNION (SELECT DISTINCT
PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBERPNO AND ESSNSSN AND
LNAMESmith)
27
Substring Comparisons, Arithmetic operators, and
Ordering Substring Comparisons Substring
Comparison is done by using the LIKE comparison
operator. Partial strings are specified by using
two reserved characters and _. replaces
an arbitrary number of characters. _ replaces a
single characters
28
Query 12 Find all employees whose address is in
Houston, Texas Q12 SELECT FNAME, LNAME FROM
EMPLOYEE WHERE ADDRESS LIKE Houston, TX
Query 12A Find all employees who born during
1950s. Q12A SELECT FNAME, LNAME FROM EMPLOYEE
WHERE BDATE LIKE __5______
29
Arithmetic operators Query 13 Show the
resulting salaries if every employee working on
the ProductX project is given a 10 percent
raise. Q13 SELECT FNAME, LNAME, 1.1SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSNESSN
AND PNOPNUMBER AND PNAMEProductX can
be used to concatenate two strings. , - can be
used for incrementing or decrementing date, time,
timestamp, and interval data types.
30
BETWEEN Query 14 Retrieve all employees in
department 5 whose salary is between 30,000 and
40,000 Q14 SELECT FROM EMPLOYEE WHERE
(SALARY BETWEEN 30000 AND 40000) AND DNO5
31
Ordering SQL allows the user to order the tuples
in the result of a query by the values of one or
more attributes, using the ORDER BY-clause.
Query 15 Retrieve a list of employees and the
projects they are working on, ordered by
department and, within each department, ordered
alphabetically by last name, first name. Q15
SELECT DNAME, LNAME, FNAME, PNAME FROM
DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE
DNUMBERDNO AND SSNESSN AND PNOPNUMBER ORDERED
BY DNAME, LNAME, FNAME ASC ascending order
DESC descending order Ex ORDER BY DNAME DESC,
LNAME ASC, FNAME ASC
32
4 Views (Virtual Tables) in SQL A view is a
single table that is derived from base tables or
previously defined views. Specification of Views
in SQL V1 CREATE VIEW WORKS_ON1 AS SELECT
FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE,
PROJECT, WORKS_ON WHERE SSNESSN AND
PNOPNUMBER
33
V2 CREATE VIEW DEPT_INFO (DEPT_NAME,
NO_OF_EMPS, TOTAL_SAL) AS SELECT DNAME,
COUNT(), SUM(SALARY) FROM DEPARTMENT,
EMPLOYEE WHERE DNUMBERDNO GROUP BY
DNAME
34
We can specify SQL queries on a view in the same
way as we do on base tables. QV1 SELECT FNAME,
LNAME FROM WORKS_ON1 WHERE PNAME'ProjectX'