SQL-The relational database Standard

Chapter4: SQL-The relational database Standard T2: Page 245-284

 Data Definition, Constraints and Schema Changes in SQL2
 Basic Queries in SQL
 More Complex SQL Queries
 Insert, Delete and Update Statements in SQL
 Views in SQL
 Specifying General Constraints as Assertion
 Additional Features of SQL

Overview
 IBM SEQUEL (Structured English QUEry Language) language developed as part of System R project at the
IBM San Jose Research Laboratory
 Renamed SQL (Structured Query Language)
 ANSI and ISO standard SQL:
 SQL-86 or SQL1
 SQL-89
 SQL-92 or SQL2
 SQL:1999 (language name became Y2K compliant) or SQL3
 SQL:2003
 SQL is a comprehensive database language, it has statements for the data definition, update and query.
 It has facilities for defining views on the database, for specifying security and authorization, for defining
integrity constraints and for specifying transaction controls.
 Commercial systems offer most, if not all, SQL2 features, plus varying feature sets from later standards and
special proprietary features.

Data Definition, Constraints, and Schema changes in SQL2

 ‘table’ for relation
 ‘row’ for tuple
 ‘column’ for attribute

Schema and Catalog concepts in SQL2

 The concepts of relational database schema were incorporated into SQL2 in order to group together tables and
other constructs that belong to the same database application.
 An schema is identified by a schema name and includes an authorization identifier.
 Authorization identifier indicates the user or account who owns the schema
 It also includes the descriptors for each element in the schema.
 Schema elements include tables, constraints, views, domains and other constructs (like authorization grants
that describe the schema.
 Elements of schema can be defined at the time of creation of schema or later.

CREATE SCHEMA
 Specifies a new database schema by giving it a name, e.g.
create schema company authorization KUMAR;
 All users are not authorized to create schemas and its elements
 The privileges to create the schema, tables and other construct can explicitly be granted to the relevant users
by DBA.
 SQL2 uses the concept of catalog.
 Catalog is a named collection of schemas in an SQL environment.
 A catalog contains a special schema called INFORMATION_SCHEMA, which provides information on all
the schemas in the catalog and all the element descriptors of all the schema in the catalog to authorized users.
 Integrity constraints can be defined for the relations and between the relations of same schema.
 Schemas within the same catalog can also share certain elements such as domain definitions.

CREATE TABLE Command in SQL

 The CREATE TABLE command is used to specify a new base relation by giving its name, and specifying
each of its attributes and constraints.
 The attributes are specified first by giving their name, their data types and constraints for the attributes like
NOT NULL, CHECKS etc. specified on each attribute.
 The key, entity integrity and referential integrity constraints can be specified within the CREATE TABLE
command after the attributes declaration, or can be added later using the ALTER command.
 An SQL relation is defined using the create table command:

CREATE TABLE R(A1 D1, A2 D2, ..., An Dn,

(integrity-constraint1),
...,
(integrity-constraintk))
 R is the name of the relation
 each Ai is an attribute name in the schema of relation R
 Di is the data type of values in the domain of attribute A
 Relations in Company Schema
CREATE TABLE EMPLOYEE
( FNAME VARCHAR(15) NOT NULL, MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9), BDATE DATE,
ADDRESS VARCHAR(30), SEX CHAR,
SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (DNO) REFERENCES DEPT (DNUMBER) );

CREATE TABLE DEPTARTMENT

( DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE (SSN));

CREATE TABLE LOCATIONS

(DNUMBER INT NOT NULL,
DLOCATION VARCHAR(15) NOT NULL,
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPTARTMENT);
CREATE TABLE PROJECT
( PNAME VARCHAR(15) NOT NULL,
PNUMBER INT NOT NULL,
PLOCATION VARCHAR(15) NOT NULL,
DNUM INT NOT NULL,
PRIMARY KEY (PNUMBER),
UNIQUE (PNAME),
FOREIGN KEY (DNUM) REFERENCES DEPTARTMENT (DNUMBER));

CREATE TABLE WORKS_ON

(ESSN CHAR(9) NOT NULL,
PNO INT NOT NULL,
HOURS DECIMAL(3,1) NOT NULL,
DNO INT NOT NULL,
PRIMARY KEY (ESSN, PNO),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER));

CREATE TABLE DEPENDANT

( ESSN CHAR(9) NOT NULL,
DEPANDANT_NAME VARCHAR(15) NOT NULL, SEX CHAR, BDATE DATE,
ADDRESS VARCHAR(30),
RELATIONSHIP VARCHAR(8),
PRIMARY KEY (ESSN, DEPANDANT_NAME),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN));

 Schema name can be explicitly attached with the relation name separated by period.
CREATE TABLE COMPANY. EMPLOYEE…….
 EMPLOYEE table becomes part of Schema COMPANY

Attribute Data types and Domains in SQL

1. Numeric
2. Character
3. Bit-string
4. Boolean
5. Date
6. Time
7. Timestamp
8. Interval
 Numeric: integer number of various sizes and floating numbers of various precision
  INTEGER or INT: Integer (a finite subset of the integers that is machine-dependent) without any decimal
part.
 SMALLINT: Small integer (a machine-dependent subset of the integer domain type) without any
decimal part.
 REAL, DOUBLE precision: Floating point and double-precision floating point numbers, with machine-
dependent precision.
 FLOAT(n): Floating point number, with user-specified precision of at least n digits.
 NUMERIC(i,j) or DECIMAL(I,j) or DEC(I,j): Fixed point number, with user-specified precision of i as total
no. of digits, with j digits to the right of decimal point.
 Character: consists of sequence of character either of fixed length or varying length, default length of
character string is 1
 CHAR(n) or CHARACTER(n): Fixed length character string, with user-specified length n.
 VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(N): Variable length character
strings, with user-specified maximum length n.
 For fixed length strings (CHARACTER), a shorter string is padded with blank spaces which are ignored at
time of comparison (lexicographic order)
 A concatenation operator || can also be used to concatenate two strings.
 CHARACTER LARGE OBJECT (CLOB) for large text values (documents)
 Bit-string: consists of sequence of bits, either fixed length or varying length, default length of bit string is 1
 Boolean: it has three values TRUE, FALSE or UNKNOWN (for null)
 Date: it has ten positions, in the form YYYY-MM-DD, components are YEAR, MONTH, DAY
 Time: it has at least eight positions, in the form HH:MM:SS, components are HOUR, MINUTE, SECOND
 Only valid date & time is allowed, comparison operators can be used.
 INTERVAL: Interval data type specifies an interval – a relative value that can be used to increment or
decrement an absolute value of a date, time or timestamp.
 Intervals are qualified to be either YEAR/MONTH or DAY/TIME intervals.
 It can be positive or negative when added to or subtracted from an absolute value, the result is an absolute
value.
 TIME WITH TIME ZONE: this data type includes an additional six positions for specifying the displacement
from the standard universal time zone, which is in the range +13:00 to 12:59 in the units of
HOURS:MINUTES
 If WITH TIME ZONE is not included, the default is the local time zone for the SQL session.
 TIME(i): Made up of hour: minute: second plus i additional digits specifying fractions of a second format is
hh:mm:ss ii...i
 TIMESTAMP: A timestamp includes both the DATE and TIME fields, plus a minimum of six positions for
decimal fractions of seconds and an optional WITH TIME ZONE qualifier.

Domains in SQL
 A domain can be declared and the domain can be used with several attributes.
 CREATE DOMAIN ENO_TYPE AS CHAR(9)
 ENO_TYPE can be used in place of CHAR(9) with SSN, ESSN, MGRSSN.
 Data type of domain can be changed that will be reflected for the numerous attributes in the schema and
improves the schema readability.

Specifying Basic constraints in SQL

 Attribute constraints
 Attributes defaults
 Key constraints
 Referential integrity constraints

Specifying Attribute constraints & Attribute defaults

 A constraint NOT NULL may be specified on an attribute if null can not be permitted for that attribute.
 A DEFAULT clause is used to declare a default value for an attribute in absence of actual value.
 Whenever an explicit value is not provided for the attribute, default value will be appended.
 A clause CHECK is used to restrict the domain values for an attribute.

CREATE TABLE EMPLOYEE

(
ENO VARCHAR2(6) CHECK (ENO LIKE 'E%'),
ENAME VARCHAR2(10) CHECK (ENAME = UPPER (ENAME)),
ADDRESS VARCHAR2(10) NOT NULL,
STATE VARCHAR2(10) CHECK (STATE IN ('KARNATAKA', 'KERALA')),
EMP_TYPE CHAR (1) DEFAULT ‘F’
);

Specifying Key constraints

 Table constraints can be specified on a table, including keys and referential integrity.
 The PRIMARY KEY clause specifies the single (or composite) primary key constraint.
….DNUM INT PRIMARY KEY….
….PRIMARY KEY(ESSN, PNUM)….
 A UNIQUE clause is used to specify alternate keys.
….DNAME CHAR(9) NOT NULL UNIQUE….

CREATE TABLE EMPLOYEE

(
SSN CHAR(9),
FNAME VARCHAR(15) NOT NULL,
LNAME VARCHAR(15) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
 PRIMARY KEY (SSN)
);

Specifying Referential integrity constraints

 A clause FOREIGN KEY can be specified for the foreign key constraint to implement the relationship
between the relations i.e. referential integrity.
 A referential integrity can be violated when tuples are inserted, deleted or foreign key value is updated.
 We can specify CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys).
 An option must be qualified with either ON DELETE or ON UPDATE.
 Possible options for referential triggered actions:
 ON DELETE SET DEFAULT
 ON DELETE SET NULL
 ON DELETE CASCADE
 ON UPDATE CASCADE
 ON UPDATE SET DEFAULT
 ON UPDATE SET NULL

CREATE TABLE EMPLOYEE

( FNAME VARCHAR(15) NOT NULL,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9), ENO CHAR(5) ,
BDATE DATE, ADDRESS VARCHAR(30),
SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN) ON DELETE SET DEFAULT ON
UPDATE CASCADE,
FOREIGN KEY (DNO) REFERENCES DEPT (DNUMBER) ON DELETE CASCADE ON UPDATE
CASCADE );

Naming the constraints

 A constraint name is used to identify a particular constraint in case the constraint must be dropped or
modified later.
 Giving names to constraints is optional.
 Name of the constraint should be unique within a schema.

create table employee

(eno varchar2(10) constraint pk_employee Primary key
ename varchar2(30),
address varchar2(100) not null,
state varchar2(10), dt_of_joining date,
constraint chk_eno check(eno like 'e%'),
constraint chk_ename check (ename=upper(ename)),
constraint chk_state check(state in('karnataka','kerala'))
constraint fk_dno_dept foreign key (dno) references dept);

Schema Change Statements in SQL

Drop commands
 DROP command is used to remove an element & its definition
 DROP SCHEMA: To drop schema
 DROP TABLE: To drop table
 The relation (or schema) can no longer be used in queries, updates, or any other commands since its
description no longer exists
 There are two DROP behaviour options CASCADE and RESTRICT
 A CASCADE option is used, To remove schema and its all tables, views & all other elements
Examples:
 DROP SCHEMA COMPANY CASCADE;
 Schema and its all element are dropped.
 There are two DROP behaviour options CASCADE and RESTRICT
 A CASCADE option is used, To remove schema and its all tables, views & all other elements
Examples:
 DROP SCHEMA COMPANY CASCADE;
 Schema and its all element are dropped.
 DROP TABLE DEPENDENT CACADE;
 Table and its all element are dropped.
 If RESTRICT option is used instead of CASCADE
 A schema is dropped only if it has no elements, otherwise error will be shown.
 A table is dropped only if it is not referenced in any constraint by any other table.

ALTER command
 The definition of a base table can be changed by using ALTER TABLE command.
 The various possible options include
 adding a column
 dropping a column
 changing the definition of column
 adding and dropping the table constraints
 ALTER TABLE command is used to add an attribute to one of the base relations
 The new attribute will have NULLs in all the tuples of the relation right after the command is executed;
hence, the NOT NULL constraint is not allowed for such an attribute
Example:
 ALTER TABLE EMPLOYEE ADD JOB CHAR(12);
 The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple.
 This can be done using the UPDATE command or by DEFAULT clause.
 ALTER TABLE command is also used to drop an attribute from one of the base relations.
 To drop a column, an option CASCADE or RESTRICT should be chosen for drop behaviour .
Example:
 ALTER TABLE EMPLOYEE DROP JOB;
 If CASCADE, all relations and views that reference the column are dropped automatically from the schema
along with the column.
 ALTER TABLE command is also used to modify an attribute of one of the base relations.
Example:
 ALTER TABLE EMPLOYEE ALTER MGRSSN DROP DEFAULT;
 ALTER TABLE EMPLOYEE ALTER MGRSSN SET DEFAULT;
Adding or dropping constraints
 ALTER TABLE EMPLOYEE DROP CONSTRAINT FK_SUPERSSN CASCADE;

Basic Queries in SQL

The SELECT_FROM_WHERE Structure of Basic Queries in SQL

 Basic form of the SQL SELECT statement is called a SELECT-FROM-WHERE block
SELECT <attribute list>
FROM <table list>
WHERE <condition>
 <attribute list> is a list of attribute names whose values are to be retrieved by the query
 <table list> is a list of the relation names required to process the query
 <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query
 Query 0: Retrieve the birthdate and address of the employee whose name is ‘John B. Smith’.
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=’John’ AND MINIT=’B.’
AND LNAME=’Smith’;

 Π BDATE, ADDRESS (σFNAME=’John’ AND MINIT=’B’ AND LNAME=’Smith’(Employee))

 Similar to a SELECT-PROJECT pair of relational algebra operations
 The SELECT-clause specifies the projection attributes
 The WHERE- clause specifies the selection condition
 However, the result of the query may contain duplicate tuples
Query 1: Retrieve the name and address of all employees who work for the ‘Research’ department.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’Research’ AND
DNUMBER=DNO

 Similar to a SELECT-PROJECT-JOIN sequence of relational algebra operations

 (DNAME=’Research’) is a selection condition (corresponds to a SELECT operation in relational algebra)
 Retrieve FNAME, LNAME, ADDRESS is a project operation
 (DNUMBER=DNO) is a join condition (corresponds to a JOIN operation in relational algebra)

Query 2: For every project located in ‘Stafford’, list the project number, the controlling department
number, and the department manager’s last name, address, and birthdate.
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN
AND PLOCATION=’ Stafford’;

 There are two join conditions

 The join condition DNUM=DNUMBER relates a project to its controlling department
 The join condition MGRSSN=SSN relates the controlling department to the employee who manages that
department
 Retrieve PNUMBER, DNUM, LNAME, BDATE, ADDRESS is a project operation
 Project location is ‘Stafford’

Ambiguous Attribute names, Aliasing, and Tuple Variables

 In SQL, we can use the same name for two (or more) attributes as long as the attributes are in different
relations
 A query that refers to two or more attributes with the same name must qualify the attribute name with the
relation name by prefixing the relation name to the attribute name
Example:
 EMPLOYEE.LNAME
 DEPARTMENT.DNAME

Query 1A: Retrieve the name and address of all employees who work for the ‘Research’ department.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’Research’ AND
EMPLOYEE.DNUMBER = DEPARTMENT.DNUMBER
Q1B
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE E, DEPARTMENT D
WHERE DNAME=’Research’ AND
E.DNUMBER = D.DNUMBER;

 Some queries need to refer to the same relation twice

 In this case, aliases are given to the relation name

Query 8: For each employee, retrieve the employee’s name, and the name of his or her immediate
supervisor.
SELECT E.FNAME, E.LNAME, S.FNAME,
S. LNAME
FROM EMPLOYEE E S
WHERE E.SUPERSSN=S.SSN

 The alternate relation names E and S are called aliases.

 E and S can be thought as two different copies of EMPLOYEE; E represents employees in role of supervisees
and S represents employees in role of supervisors

Basic Queries in SQL

 Aliasing can also be used in any SQL query for convenience and AS keyword can also be used to specify
aliases
SELECT E.FNAME, E.LNAME, S.FNAME,
S. LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN

Unspecified WHERE-clause
 A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-clause are
selected

Query 9: Retrieve the SSN values for all employees.

SELECT SSN
FROM EMPLOYEE

 If more than one relation is specified in the FROM-clause and there is no join condition, then the
CARTESIAN PRODUCT of tuples is selected
Q1O: SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT
 It is extremely important not to overlook specifying any selection and join conditions in the WHERE-clause;
otherwise, incorrect and very large relations may result

Use of * (Asterisk)
 To retrieve all the attribute values of the selected tuples, a * is used, which stands for all the attributes

Q1C: SELECT *
FROM EMPLOYEE
WHERE DNO=5;

Q1D: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’Research’ AND
DNO=DNUMBER;

 SQL usually treats a table not as a set but rather as a multiset, duplicate tuples can appear more than once in a
table, and in the result of a query.
 SQL does not automatically eliminate duplicate tuples in the result of queries because:
 Duplicate elimination is expensive.
 The user may want to see the duplicates in the result of query.
 For an aggregate function, elimination of tuples is not desired.

Tables as Sets in SQL

 An SQL table with a Primary key is restricted to being a set, since the key value must be distinct in each
tuple.
 Keyword DISTINCT can be used in SELECT clause if duplicates have to be eliminated in the query result.
 SELECT SALARY FROM EMPLOYEE
or SELECT ALL SALARY FROM EMPLOYEE
 SELECT DISTINCT SALARY FROM EMPLOYEE

Set operations in SQL

 SQL has directly incorporated some set operations
 union operation (UNION)
 set difference (MINUS or EXCEPT)
 intersection (INTERSECT)
 The resulting relations of these set operations are sets of tuples; duplicate tuples are eliminated from the result
 The set operations apply only to union compatible relations ; the two relations must have the same attributes
and the attributes must appear in the same order
 If duplicates have to be retained
  union operation (UNION ALL)
 set difference (MINUS ALL or EXCEPT ALL)
 intersection (INTERSECT ALL)

Query 4: Make a list of all project numbers for projects that involve an employee whose last name is
‘Smith’ as a worker or as a manager of the department that controls the project.
(SELECT DISTINCT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN
AND LNAME=’Smith’)
UNION
(SELECT DISTINCT PNUMBER
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME=’ Smith’)

Substring Pattern Matching

 The LIKE comparison operator is used to compare partial strings
 Two reserved characters are used: ‘%‘ (or ‘*‘ in some implementations) replaces an arbitrary number of
characters, and ‘_’ replaces a single arbitrary character

Query 12: Retrieve all employees whose address is in Houston, Texas.

(Here, the value of the ADDRESS attribute must contain the substring ‘Houston,Texas’.)
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE
‘%Houston,Texas%

Query 12A: Retrieve all employees who were born during the 195Os.
(Here, ‘5’ must be the 8th character of the string according to our format for date, so the BDATE value is 5’, with
each underscore as a place holder for a single arbitrary character.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE LIKE ‘_ _ _ _ _ 195_’;
(or ‘%195_’)

Arithmetic Operators
 The standard arithmetic operators ‘+’, ‘-’, ‘*’, ‘/’ (for addition, subtraction, multiplication, and division,
respectively) can be applied to numeric values in an SQL query result.
 Query 13: Show the resulting salaries if every employee working on the ‘ProductX’ project is given a
10% raise.
 SELECT FNAME, LNAME, 1.1*SALARY AS INCREASED_SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME=’ProductX’

Comparison Operators
Query 14: Retrieve all the employees in department 5 whose salary is between Rs. 30,000 and Rs. 40,000.
 SELECT *
FROM EMPLOYEE
WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5;
 SELECT *
FROM EMPLOYEE
WHERE (SALARY >= 30000) AND (SALARY <= 40000) AND DNO = 5;

Ordering of Query results

 The ORDER BY clause is used to sort the tuples in a query result based on the values of some attribute(s)
 The default order is in ascending order of values.
 The keyword DESC can be used if descending order is required; the keyword ASC can be used to explicitly
specify ascending order, even though it is the default

Query 15: Retrieve a list of employees and the projects each works in, ordered by the employee’s
department, and within each department ordered alphabetically by employee last name, first name.
 SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE,
WORKS_ON, PROJECT
WHERE DNUMBER=DNO
AND SSN=ESSN
AND PNO=PNUMBER
ORDER BY DNAME, LNAME, FNAME

 ORDER BY DNAME, LNAME, FNAME DESC

 ORDER BY DNAME, LNAME DESC, FNAME DESC
 ORDER BY DNAME DESC, LNAME, FNAME DESC

More Complex Queries

Nested Queries
 Some queries require that existing values in database be fetched and then used in a comparison condition.
 A complete SELECT query, called a nested query, can be specified within the WHERE-clause of another
query, called the outer query

Query 1: Retrieve the name and address of all employees who work for the ‘Research’ department.
 SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN
(SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=’Research’)

 The nested query selects the number of the ‘Research’ department

 The outer query select an EMPLOYEE tuple if its DNO value is in the result of nested query
 The comparison operator IN compares a value v with a set (or multi-set) of values V, and evaluates to TRUE
if v is one of the elements in V
 In general, we can have several levels of nested queries

Query 4A: Make a list of all project numbers for projects that involve an employee whose last name is
‘Smith’ as a worker or as a manager of the department that controls the project.
SELECT DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN
(SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN
AND LNAME=’Smith’)
OR
PNUMBER IN
(SELECT PNUMBER
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME=’ Smith’)

 In addition of IN operator, a number of other comparison operator like:

 = (if nested query returns only single value)
 = ANY or =SOME
 >, <, <=, >=, <>
 ALL keyword can be used with all above operators.

SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE DNO=5)
Correlated Nested Queries
 If a condition in the WHERE-clause of a nested query references an attribute of a relation declared in the
outer query, the two queries are said to be correlated.
 A reference to an unqualified attribute refers to the relation declared in the innermost nested query

Query 16: Retrieve the name of each employee who has a dependent with the same first name as the
employee.
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN
(SELECT ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN AND
E.FNAME=FNAME)

Single Block Query

 A query written with nested SELECT... FROM... WHERE... blocks and using the = or IN comparison
operators can always be expressed as a single block query.

Q16A
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN=D.ESSN AND
E.FNAME=D.DEPENDENT_NAME

The Exists Function

 EXISTS is used to check whether the result of a correlated nested query is empty (contains no tuples) or not
 EXISTS and NOT EXISTS are usually used in conjunction with a correlated nested query
 EXISTS returns TRUE if there is at least one tuple in the result of the query, otherwise it returns false.
 NOT EXISTS returns TRUE if there is no tuples in the result of the query, otherwise it returns false.

Query 16B: Retrieve the name of each employee who has a dependent with the same first name as the
employee.
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE EXISTS
(SELECT *
FROM DEPENDENT
WHERE SSN=ESSN AND
FNAME=DEPENDENT_NAME)
Query 6: Retrieve the names of employees who have no dependents.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE
NOT EXISTS
(SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)

 The correlated nested query retrieves all DEPENDENT tuples related to an EMPLOYEE tuple. If none exist,
the EMPLOYEE tuple is selected
 EXISTS is necessary for the expressive power of SQL

Q7. List the names of managers who have at least one dependent.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
AND
EXISTS (SELECT *
FROM DEPARTMENT
WHERE SSN=MGRSSN);

Correlated Nested Queries

 The original SQL as specified for SYSTEM R also had a CONTAINS comparison operator, which is used in
conjunction with nested correlated queries
 This operator was dropped from the language, possibly because of the difficulty in implementing it efficiently
 Most implementations of SQL do not have operator CONTAINS
 The CONTAINS operator compares two sets of values, and returns TRUE if one set contains all values in the
other set (reminiscent of the division operation of algebra).

Query 3: Retrieve the name of each employee who works on all the projects controlled by department
number 5.
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE ((SELECT PNO, ESSN
FROM WORKS_ON
WHERE SSN=ESSN)
CONTAINS
(SELECT PNUMBER
 FROM PROJECT
WHERE DNUM=5))

 The second nested query, which is not correlated with the outer query, retrieves the project numbers of all
projects controlled by department 5
 The first nested query, which is correlated, retrieves the project numbers on which the employee works,
which is different for each employee tuple because of the correlation

The UNIQUE Function

 UNIQUE returns true if there are no duplicate tuples in the result of the query, otherwise it returns false.
 This can be used to test whether the result of a nested query is a set or multiset.

The Explicit Sets in SQL

 It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than a nested
query

Query 13: Retrieve the social security numbers of all employees who work on project number 1, 2, or 3.
SELECT DISTINCT ESSN
FROM WORKS ON
WHERE PNO IN (1,2,3)

NULLS in SQL
 SQL allows queries that check if a value is NULL (missing or undefined or not applicable)
 SQL uses IS or IS NOT to compare NULLs because it considers each NULL value distinct from other NULL
values, so equality comparison is not appropriate.

Query 14: Retrieve the names of all employees who do not have supervisors.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL

 Note: If a join condition is specified, tuples with NULL values for the join attributes are not included in the
result

Renaming Attributes
 Any attribute which appears in the result can be renamed by adding the qualifier AS followed by the desired
new name.
 AS construct can be used for both attribute names and relation names and can be used in both SELECT and
FROM clauses.
Query 8A: For each employee, retrieve the employee’s name, and the name of his or her immediate
supervisor.
SELECT E.NAME AS Supervisee_name,
S.NAME AS Superviser_name,
FROM EMPLOYEE AS E,
EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN

Joining Tables
 We can specify a “joined relation” in the FROM-clause and it looks like any other relation but is the result of
a join
 It allows the user to specify different types of joins (regular THETA JOIN, NATURAL JOIN, LEFT OUTER
JOIN, RIGHT OUTER JOIN, CROSS JOIN, etc)

Query 1: Retrieve the name and address of all employees who work for the ‘Research’ department.
 Q1: SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNUMBER=DNO AND DNAME=’Research’;

could be written as:

Q1: SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE JOIN DEPARTMENT
ON DNUMBER=DNO)
WHERE DNAME=’Research’

Could also be written as:

SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN (DEPARTMENT
AS DEPT(DNAME, DNO, MSSN, MSDATE)))
WHERE DNAME=’Research’

Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE E S
WHERE E.SSN=S.SUPERSSN

can be written as for outer join:

Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S
ON E.SSN=S.SUPERSSN)
Q2: For every project located in ‘Stafford’, list the project number, the controlling department number,
and the department manager’s last name, address, and birthdate.

could be written as follows; this illustrates multiple joins in the joined tables
Q2: SELECT PNUMBER, DNUM,
LNAME, BDATE, ADDRESS
FROM ((PROJECT JOIN DEPARTMENT ON
DNUM=DNUMBER)
JOIN EMPLOYEE ON MGRSSN=SSN)
WHERE PLOCATION=’ Stafford’;

Aggregate Functions
 Include COUNT, SUM, MAX, MIN, and AVG
 Some SQL implementations may not allow more than one function in the SELECT-clause

Query 19: Find the sum of the salary of all employees, the maximum salary, the minimum salary, and the
average salary among employees.
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE;

Query 20: Find the sum of the salary of all employees, the maximum salary, the minimum salary, and the
average salary among employees who work for the ‘Research’ department.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND
DNAME= ‘Research’

Query 20: Find the sum of the salary of all employees, the maximum salary, the minimum salary, and the
average salary among employees who work for the ‘Research’ department.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER)
WHERE DNAME= ‘Research’;

Query 21: Retrieve the total number of employees.

SELECT COUNT(*)
FROM EMPLOYEE;

Query 22: Retrieve the total number of employees in the Research Department.
SELECT COUNT(*)
FROM EMPLOYEE, DEPARTMENT
 WHERE DNO=DNUMBER AND DNAME=‘Research’;

Query 23: Count the distinct salary values in the database.

SELECT COUNT(DISTINCT SALARY)
FROM EMPLOYEE;

Query 5: List the names of all employees with two or more dependents.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE (SELECT COUNT(*)
FROM DEPENDENT
WHERE SSN = ESSN) >=2;

Grouping
 SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-
clause
 In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation
 Each subgroup of tuples consists of the set of tuples that have the same value for the grouping attribute(s)
 Then aggregate function is applied to each subgroup independently

Query 20: For each department, retrieve the department number, the number of employees in the
department, and their average salary.
SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO

 The EMPLOYEE tuples are divided into groups--each group having the same value for the grouping attribute
DNO
 The COUNT and AVG functions are applied to each such group of tuples separately
 The SELECT-clause includes only the grouping attribute and the functions to be applied on each group of
tuples
 A join condition can be used in conjunction with grouping

Query 25: For each project, retrieve the project number, project name, and the number of employees who
work on that project.
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME;
 In this case, the grouping and functions are applied after the joining of the two relations

The Having-clause
 Sometimes we want to retrieve the values of these functions for only those groups that satisfy certain
conditions
 The HAVING-clause is used for specifying a selection condition on groups (rather than on individual tuples)

Query 26: For each project on which more than two employees work, retrieve the project number, project
name, and the number of employees who work on that project.
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER , PNAME
HAVING COUNT (*) >2;

Query 27: For each project, retrieve the project number, project name, and the number of employees from
department 5 who work on that project.
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER = PNO
AND SSN = ESSN AND DNO = 5
GROUP BY PNUMBER, PNAME;

Query 28: For each department that has more than five employees, retrieve the department number, and
the number of its employees who are earning more than 40000.
SELECT DNUMBER, COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNUMBER = DNO AND SALARY>40000 AND DNO IN (SELECT DNO
FROM EMPLOYEE
GROUP BY DNO
HAVING COUNT (*)>5)
GROUP BY DNUMBER;

Summary of SQL Queries

 A query in SQL can consist of up to six clauses, but only the first two, SELECT and FROM, are mandatory.
The clauses are specified in the following order:
 SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
 [HAVING <group condition>]
[ORDER BY <attribute list>]
 The SELECT-clause lists the attributes or functions to be retrieved
 The FROM-clause specifies all relations (or aliases) needed in the query but not those needed in nested
queries
 The WHERE-clause specifies the conditions for selection and join of tuples from the relations specified in the
FROM-clause
 GROUP BY specifies grouping attributes
 HAVING specifies a condition for selection of groups
 ORDER BY specifies an order for displaying the result of a query
 A query is evaluated by first applying the WHERE-clause, then GROUP BY and HAVING, and finally the
SELECT-clause

INSERT, DELETE and UPDATE Statements in SQL

The INSERT Command
 There are three SQL commands to modify the database; INSERT, DELETE, and UPDATE
 INSERT is used to add one tuple to a relation by specifying relation name and a list of values.
 Attribute values should be listed in the same order as the attributes were specified in the CREATE TABLE
command
Example: U1:
 INSERT INTO EMPLOYEE
VALUES (‘Richard’,’K’,’Marini’, ‘653298653’, ‘30-DEC-52’, ‘98 Oak Forest,Katy,TX’, ‘M’, 37000,
‘987654321’, 4)

 An alternate form of INSERT specifies explicitly the attribute names that correspond to the values in the new
tuple
 Attributes with NULL values can be left out

Example: U1A: Insert a tuple for a new EMPLOYEE for whom we only know the FNAME, LNAME, and
SSN attributes.
 INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES (‘Richard’, ‘Marini’, ‘653298653’)
 Only the constraints specified in the DDL commands are automatically enforced by the DBMS when updates
are applied to the database
 Another variation of INSERT allows insertion of multiple tuples resulting from a query into a relation

Example: Create a temporary table that has the Department name, number of employees, and total salaries
for each department.
U3A:
 CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(1O), NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);
U3B:
 INSERT INTO DEPTSINFO (DEPT_NAME,
NO_OF_EMPS, TOTAL_SAL)
SELECT DNAME, COUNT (*), SUM (SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME;

 Note: The DEPTS_INFO table may not be up-to-date if we change the tuples in either the DEPARTMENT or
the EMPLOYEE relations after issuing U3B.
 (We have to create a view (see later) to keep such a table up to date.)

The DELETE Command

 Removes tuples from a relation
 Includes a WHERE-clause to select the tuples to be deleted
 Tuples are deleted from only one table at a time (unless CASCADE is specified on a referential integrity
constraint)
 A missing WHERE-clause specifies that all tuples in the relation are to be deleted; the table then becomes an
empty table
 Referential integrity should be enforced
 The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHERE-clause
Examples:
U4A:
 DELETE FROM EMPLOYEE
WHERE LNAME=’Brown’
U4B:
 DELETE FROM EMPLOYEE
WHERE SSN=’ 123456789’
 The DELETE Command
U4C:
 DELETE FROM EMPLOYEE
WHERE DNO IN
(SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=’Research’)
U4D:
 DELETE FROM EMPLOYEE

The UPDATE Command

 UPDATE is used to modify attribute values of one or more selected tuples
 A WHERE-clause selects the tuples to be modified
 An additional SET-clause specifies the attributes to be modified and their new values
 Each command modifies tuples in the same relation
 Referential integrity should be enforced

Example: Change the location and controlling department number of project number 10 to ‘Bellaire’ and
5, respectively.
U5:
UPDATE PROJECT
SET PLOCATION = ‘Bellaire’, DNUM = 5 WHERE PNUMBER=1O;

Example: Give all employees in the ‘Research’ department a 10% raise in salary.
U6:
UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=’Research’);

 In this request, the modified SALARY value depends on the original SALARY value in each tuple
 The reference to the SALARY attribute on the right of = refers to the old SALARY value before modification
 The reference to the SALARY attribute on the left of= refers to the new SALARY value after modification
Views in SQL
 A view is a “virtual” table that is derived from other tables (base tables or other views)
 A view does not exist physically.
 View allows for limited update operations.
 View allows full query operations
 It is a convenient way for expressing certain operations e.g. instead of writing a complicated query again and
again, a view can be created for the query, and simple SELECT command can be used to view the query
result.
SQL command:
 CREATE VIEW <view> <a possible list of attribute names > < a query to specify the contents of the view>
 If attribute names are not specified, these will be same as in the base relations)
 Select first name, last name of employee along with the project name and number of hours on which they are
working on.

V1: Specify a different WORKS_ON table

CREATE VIEW WORKS_ON_NEW
AS
SELECT FNAME, LNAME, PNAME, HOURS
 FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO = PNUMBER;

 We can specify SQL queries on a newly created view which simplify the query
 SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME=‘titan’;
 When no longer needed, a view can be dropped
DROP VIEW WORKS_ON_NEW;
 A view is always supposed to be up to date; if we modify base tables.
 Hence view is not realized at the time of view definition but rather at the time we specify a query on the view.

View Update
 Update on a single view without aggregate operations
 Update may map directly to an update on the underlying base table
 Views involving joins:
 an update may map to an update on the underlying base relations but not always possible
 Views defined using groups and aggregate functions are not updateable

CREATE VIEW EMP_DEPT

AS
SELECT ENAME, DNO
FROM EMPLOYEE;

 UPDATE EMP_DEPT
SET DNO = 4
WHERE ENAME = ‘SMITH’;

 CREATE VIEW WORKS_ON_NEW

AS
SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO = PNUMBER;

UVI: update the PNAME attribute of ‘John Smith’ from ‘ProductX’ to ‘ProductY’

 UPDATE WORKS_ON_NEW
SET PNAME = ‘ProductY’
WHERE LNAME = ‘Smith’
AND FNAME = ‘John’
 AND PNAME = ‘ProductX’;

b) UPDATE PROJECT
SET PNAME = ‘ProductY’
WHERE PNAME = ‘ProductX’);
-------------not feasible

a) UPDATE WORKS_ON
SET PNO = (SELECT PNUMBER FROM PROJECT
WHERE PNAME = ‘ProductY’)
WHERE ESSN IN (SELECT SSN
FROM EMPLOYEE
WHERE LNAME = ‘Smith’ AND FNAME = ‘John’)
AND PNO IN (SELECT PNUMBER FROM PROJECT WHERE PNAME = ‘ProductX’);

V2:
 CREATE VIEW DEPT_INFO(DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)
AS
SELECT DNAME, COUNT(*), SUM(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER
GROUP BY DNAME;

 CREATE VIEW DEPT_INFO(DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)

AS
SELECT DNAME, COUNT(*), SUM(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO= DNUMBER
GROUP BY DNAME;

UV2: UPDATE DEPT_INFO

SET TOTAL_SAL = 1000000
WHERE DNAME = ‘Research’;