MODULE 3

SQL
Basic SQL
SQL was called SEQUEL (Structured English Query
Language) and was designed and implemented at
IBM Research as the interface for an experimental
relational database system called SYSTEM R.
SQL is now the standard language for commercial
relational DBMSs. The standardization of SQL is a
joint effort by the American National Standards
Institute (ANSI) and the International Standards
Organization (ISO), and the first SQL standard is
called SQL-86 or SQL1.
 1. 1970s: SQL was first developed by a team at IBM's
San Jose Research Laboratory, led by Donald
Chamberlin and Raymond Boyce.
 2. 1974: Chamberlin and Boyce published a paper on
"SEQUEL" (Structured English Query Language), a
precursor to SQL.
 3. 1979: Relational Software (later Oracle Corporation)
released the first commercial SQL database.
 4. 1986: SQL became a standard language for
relational databases, with the publication of the ANSI
SQL standard.
 5. 1990s: SQL became widely adopted, with various
database vendors implementing their own versions.
1. SQL-86: The first official SQL standard.
2. SQL-92: A major revision that added new
features.
3. SQL:1999: Introduced support for object-
relational databases.
4. SQL:2003: Added XML support.
5. SQL:2011: Introduced support for temporal
databases.
6. SQL Server 2022: Enhanced batch mode
performance utilizing new hardware
capabilities like CPU's AVX-512 instruction
set.
SQL Data Definition and Data
Types
The main SQL command for data definition
is the CREATE statement, which can be
used to create schemas, tables (relations),
types, and domains, views, assertions, and
triggers.
SQL uses the terms table, row, and column
for the formal relational model terms
relation, tuple, and attribute, respectively
Schema and Catalog Concepts
in SQL
An SQL schema is identified by a
schema name and includes an
authorization identifier to indicate the
user or account who owns the schema,
as well as descriptors for each element
in the schema.
Schema elements include tables, types,
constraints, views, domains, and other
constructs (such as authorization grants)
that describe the schema
A schema is created via the CREATE
SCHEMA statement,
the schema can be assigned a name and
creates a schema called COMPANY owned by the
user with authorization identifier ‘Jsmith’
CREATE SCHEMA COMPANY AUTHORIZATION
‘Jsmith’;
a catalog—a named collection of schemas
A catalog always contains a special schema called
INFORMATION_SCHEMA, which provides
information on all the schemas in the catalog and
all the element descriptors in these schemas.
The CREATE TABLE Command
in SQL
 The CREATE TABLE command is used to specify a
new relation by giving it a name and specifying its
attributes and initial constraints.
 The attributes are specified first, and each attribute is
given a name, a data type to specify its domain of values,
and possibly attribute constraints, such as NOT NULL.
 integrity constraints can be specified within the CREATE
TABLE statement or can be added later using the ALTER
TABLE command.
 we can explicitly attach the schema name to the relation
name, separated by a period.
 CREATE TABLE COMPANY.EMPLOYEE
rather than
 CREATE TABLE EMPLOYEE
The relations declared through CREATE
TABLE statements are called base tables
and stored as a file by the DBMS.
the CREATE VIEW statement are used to
distinguish base relations from virtual
relations.
Attribute Data Types and
Domains in SQL
 Numeric data types include
o integer numbers of various sizes (INTEGER or INT, and
SMALLINT) and floating-point (real) numbers of various precision
(FLOAT or REAL, and DOUBLE PRECISION).
o DECIMAL(i, j)—or DEC(i, j) or NUMERIC(i, j)—where i, the precision,
is the total number of decimal digits and j, the scale, is the
number of digits after the decimal point
 Character-string data types are either fixed length—
CHAR(n) or CHARACTER(n), where n is the number of characters
—or varying length— VARCHAR(n) or CHAR VARYING(n) or
CHARACTER VARYING(n), where n is the maximum number of
characters.
o Apostrophes, case sensitive
o Concatenation operator denoted by || (double vertical bar) that
can concatenate two strings
o CHARACTER LARGE OBJECT CLOB maximum length can be
specified in kilobytes (K), megabytes (M), or gigabytes (G). For
example, CLOB(20M) specifies a maximum length of 20
megabytes.2222222222222222222
 Bit-string data types are either of fixed length n—
BIT(n)—or varying length BIT VARYING(n), where n is
the maximum number of bits., for example, B‘10101’.
 BINARY LARGE OBJECT or BLOB is also available to specify
columns that have large binary values, such as images.
 A Boolean data type has the traditional values of
TRUE or FALSE.
 NULL values  a Boolean data type is UNKNOWN.
 The DATE data type has ten positions, and its
components are YEAR, MONTH, and DAY in the form
YYYY-MM-DD.
 The TIME data type has at least eight positions, with the
components HOUR, MINUTE, and SECOND in the form
HH:MM:SS.
 Literal values are represented by single-quoted strings
preceded by the keyword DATE or TIME; for example, DATE
‘2014-09-27’ or TIME ‘09:12:47’.
additional data types
 A timestamp data type (TIMESTAMP) includes the
DATE and TIME fields, plus a minimum of six positions
for decimal fractions of seconds and an optional WITH
TIME ZONE qualifier.
 for example, TIMESTAMP ‘2014-09-27 09:12:47.648302’.
 INTERVAL data type —a relative value that can be
used to increment or decrement an absolute value of a
date, time, or timestamp.
 It is possible to specify the data type of each attribute
directly or domain can be declared, and the domain name
can be used with the attribute specification
CREATE DOMAIN SSN_TYPE AS CHAR(9);
 CREATE TYPE command, which can be used to
create user defined types or UDTs.
Specifying Constraints in
SQL
 Specifying Attribute Constraints and Attribute
Defaults
 define a default value for an attribute by appending
the clause DEFAULT <value> to an attribute
definition.
 Another type of constraint can restrict attribute or
domain values using the CHECK clause
 suppose that department numbers are restricted to
integer numbers between 1 and 20
 Dnumber INT NOT NULL CHECK (Dnumber > 0
AND Dnumber < 21);
 CREATE DOMAIN D_NUM AS INTEGER
 CHECK (D_NUM > 0 AND D_NUM < 21);
Specifying Key and Referential
Integrity Constraints
Dnumber INT PRIMARY KEY,
Dname VARCHAR(15) UNIQUE,

The default action that SQL takes for an integrity

violation is to reject the update operation that
will cause a violation, which is known as the
RESTRICT option
the schema designer can specify an alternative
action to be taken by attaching a referential
triggered action clause to any foreign key
constraint
SET NULL, CASCADE, and SET DEFAULT qualified
with either ON DELETE or ON UPDATE
The action for CASCADE ON DELETE is to
delete all the referencing tuples, whereas
the action for CASCADE ON UPDATE is to
change the value of the referencing foreign
key attribute(s) to the updated (new)
primary key value for all the referencing
tuples.
Giving Names to Constraints
the keyword CONSTRAINT
Specifying Constraints on
Tuples Using CHECK
CHECK (Dept_create_date <=
Mgr_start_date);
called row-based constraints because
they apply to each row individually and are
checked whenever a row is inserted or
modified.
 Basic Retrieval Queries in
SQL
SQL allows a table (relation) 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, because a set does
not allow two identical members; rather, it is a
multiset (sometimes called a bag) of tuples.
SELECT statement  basic statement for
retrieving information from a database.
The SELECT-FROM-WHERE Structure of
Basic SQL Queries
 The basic form of the SELECT statement,
sometimes called a mapping or a select-from-
where block, is formed of the three clauses
SELECT, FROM, and WHERE and has the following
form:
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 birth date and
address of the employee(s) whose
name is ‘John B. Smith’.
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = ‘John’ AND Minit =
‘B’ AND Lname = ‘Smith’;
 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 Dname = ‘Research’ AND Dnumber = Dno;
The condition Dnumber = Dno is called a join condition, because it combines
two tuples: one from DEPARTMENT and one from EMPLOYEE, whenever the
value of Dnumber in DEPARTMENT is equal to the value of Dno in EMPLOYEE.

 A query that involves only selection and join conditions plus projection
attributes is known as a select-project-join query
 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 birth date

SELECT Pnumber, Dnum, Lname, Address, Bdate

FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND
Plocation = ‘Stafford’ ;
The join condition Dnum = Dnumber relates a project tuple to its controlling
department tuple, whereas the join condition Mgr_ssn = Ssn relates the
controlling department tuple to the employee tuple who manages that
department
Ambiguous Attribute Names, Aliasing,
Renaming, and Tuple Variables
Q1A: SELECT Fname, EMPLOYEE.Name,
Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Name = ‘Research’
AND
DEPARTMENT.Dnumber =
EMPLOYEE.Dnumber;
Q1 can be rewritten as Q1′ below with fully
qualified
attribute names.
Q1′: SELECT EMPLOYEE.Fname,
EMPLOYEE.LName,
EMPLOYEE.Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.DName =
‘Research’ AND
DEPARTMENT.Dnumber = EMPLOYEE.Dno;
The ambiguity of attribute names also arises in the
case of queries that refer to the
same relation twice
Query 8. For each employee, retrieve
the employee’s 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.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;

EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd,

Addr, Sex, Sal, Sssn, Dno)
Q1B: SELECT E.Fname, E.LName,
E.Address
FROM EMPLOYEE AS E, DEPARTMENT
AS D
WHERE D.DName = ‘Research’ AND
D.Dnumber = E.Dno;
Unspecified WHERE Clause and
Use of the Asterisk
A missing WHERE clause indicates no condition
on tuple selection; hence, all tuples of the
relation specified in the FROM clause qualify and
are selected for the query result.
Queries 9 and 10. Select all EMPLOYEE
Ssns (Q9) and all combinations of
EMPLOYEE Ssn and DEPARTMENT Dname (Q10)
in the database.
Q9: SELECT Ssn
FROM EMPLOYEE;
Q10: SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;
Q1C: SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

Q1D: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dno =
Dnumber;

Q10A: SELECT *
FROM EMPLOYEE, DEPARTMENT;
Tables as Sets in SQL
SQL does not automatically eliminate
duplicate tuples in the results of queries,
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;
 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.
Q4A: ( SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = 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 and
Arithmetic Operators
LIKE comparison operator :The first feature
allows comparison conditions on only parts
of a character string, used for string pattern
matching.
Partial strings are specified using two
reserved characters:
% replaces an arbitrary number of zero or
more characters,
(_) underscore replaces a single character
 Query 12. Retrieve 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 were born

during the 1970s.
Q12: SELECT Fname, Lname
FROM EMPLOYEE
WHERE Bdate LIKE ‘197 _ _ _ _ _ _ _’;
 If an underscore or % is needed as a literal character in the
string, the character should be preceded by an escape
character, which is specified after the string using the
keyword ESCAPE
 ‘AB\_CD\%EF’ ESCAPE ‘\’ represents the literal string ‘AB_CD
%EF’ because \ is specified as the escape character.
 Any character not used in the string can be chosen as the
escape character.
 If an apostrophe (’) is needed, it is represented as two
consecutive apostrophes (”)

 Query 13. Show the resulting salaries if every employee

working on the ‘ProductX’ project is given a 10% raise.

 Q13: SELECT E.Fname, E.Lname, 1.1 * E.Salary AS

Increased_sal
 FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
 WHERE E.Ssn = W.Essn AND W.Pno = P.Pnumber AND P.Pname
= ‘ProductX’;
 An interval value is the result of the difference between two date, time, or
timestamp values.
For string data types, the concatenate operator ||
can be used in a query to append two string
values.
For date, time, timestamp, and interval data
types, operators include incrementing (+) or
decrementing (−) a date, time, or timestamp by
an interval.
Another comparison operator is 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
Dno =5;
Ordering of Query Results
 To order the tuples in the result of a query by the values
of one or more of the attributes that appear in the query
result, by 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,
then first name.
Q15: SELECT D.Dname, E.Lname, E.Fname, P.Pname
FROM DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS
W, PROJECT AS P
WHERE D.Dnumber = E.Dno AND E.Ssn = W.Essn AND
W.Pno = P.Pnumber ORDER BY D.Dname, E.Lname,
E.Fname;
 the keyword DESC, ASC
Discussion and Summary of Basic
SQL Retrieval Queries
SELECT <attribute list>
FROM <table list>
[ WHERE ] <condition>
[ ORDER BY ];
INSERT, DELETE, and UPDATE
Statements in SQL
The INSERT Command
U1: INSERT INTO EMPLOYEE
VALUES ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-
12-30’, ’98 Oak Forest, Katy, TX’, ‘M’, 37000,
‘653298653’, 4 );

For example, to enter a tuple for a new EMPLOYEE

for whom know only the Fname, Lname, Dno, and
Ssn attributes, we can use
U1A: U1A: INSERT INTO EMPLOYEE (Fname, Lname,
Dno, Ssn)
VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);
U2: INSERT INTO EMPLOYEE (Fname,
Lname, Ssn, Dno) VALUES (‘Robert’,
‘Hatcher’, ‘980760540’, 2);
(U2 is rejected if referential integrity
checking is provided by DBMS.)
U2A: INSERT INTO EMPLOYEE (Fname,
Lname, Dno) VALUES (‘Robert’, ‘Hatcher’,
5);
(U2A is rejected if NOT NULL checking is
provided by DBMS.)
U3A: CREATE TABLE WORKS_ON_INFO
( Emp_name VARCHAR(15),
Proj_name VARCHAR(15),
Hours_per_week DECIMAL(3,1) );

U3B: INSERT INTO WORKS_ON_INFO

( Emp_name, Proj_name, Hours_per_week )
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;
To create a table D5EMPS with a similar structure to the
EMPLOYEE table and load it with the rows of employees
who work in department 5,
CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE AS E
WHERE E.Dno = 5) WITH DATA;
WITH DATA specifies that the table will be
created and loaded with the data specified
in the query.
The DELETE Command
The DELETE command removes tuples from a relation.
Tuples are explicitly deleted from only one table at a
time.
DROP TABLE command to remove the table
definition .
U4A: DELETE FROM EMPLOYEE
WHERE Lname = ‘Brown’;
U4B: DELETE FROM EMPLOYEE
WHERE Ssn = ‘123456789’;
U4C: DELETE FROM EMPLOYEE
WHERE Dno = 5;
U4D: DELETE FROM EMPLOYEE;
The UPDATE Command
 The UPDATE command is used to modify attribute values
of one or more selected tuples.
 SET clause in the UPDATE command specifies the
attributes to be modified and their new values.
 For example, to change the location and controlling
department number of project number 10 to ‘Bellaire’ and
5,
U5: UPDATE PROJECT
SET Plocation = ‘Bellaire’, Dnum = 5
WHERE Pnumber = 10;

U6: UPDATE EMPLOYEE

SET Salary = Salary * 1.1
WHERE Dno = 5;
Additional Features of SQL
 SQL features: various techniques for specifying complex
retrieval queries, including nested queries, aggregate
functions, grouping, joined tables, outer joins, case
statements, and recursive queries;
 SQL views, triggers, and assertions; and commands for
schema modification.
 These include embedded (and dynamic) SQL, SQL/CLI
(Call Level Interface) and its predecessor ODBC (Open
Data Base Connectivity), and SQL/PSM (Persistent
Stored Modules)
 Each commercial RDBMS will have, in addition to the
SQL commands, a set of commands for specifying
physical database design parameters, file structures for
relations, and access paths such as indexes
 SQL has transaction control commands. These are
used to specify units of database processing for
concurrency control and recovery purposes.
 SQL has language constructs for specifying the
granting and revoking of privileges to users
 The DBA staff can grant the privileges to create
schemas, tables, or views to certain users. These
SQL commands—called GRANT and REVOKE.
 SQL has language constructs for creating triggers.
 SQL has incorporated many features from object-
oriented models to have more powerful capabilities,
leading to enhanced relational systems known as
object-relational.
 SQL and relational databases can interact with new
technologies such as XML and OLAP/data
warehouses.
More Complex SQL Retrieval
Queries
Comparisons Involving NULL and Three-
Valued Logic
1. Unknown value
2. Unavailable or withheld value
3. Not applicable attribute
 SQL uses a three-valued logic with values TRUE,
FALSE, and UNKNOWN instead of the standard two
valued (Boolean) logic with values TRUE or FALSE.
SQL uses the comparison operators IS or IS
NOT rather than using = or <> to compare
an attribute value to NULL.
This is because SQL considers each NULL
value as being distinct from every other
NULL value, so equality comparison is not
appropriate

Query 18. Retrieve the names of all

employees who do not have supervisors.

Q18: SELECT Fname, Lname

FROM EMPLOYEE WHERE
Super_ssn IS NULL;
Nested Queries, Tuples, and
Set/Multiset Comparisons
Some queries require that existing values
in the database be fetched and then used
in a comparison condition.
nested queries which are complete select-
from-where blocks within another SQL
query, That other query is called the outer
query.
the comparison operator IN, which
compares a value v with a set (or multiset)
of values V and evaluates to TRUE if v is
one of the elements in V
Q4A : SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN ( SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn =
Ssn AND Lname = ‘Smith’ )
OR
Pnumber IN
( SELECT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn = Ssn AND Lname =
‘Smith’ );
If a nested query returns a single attribute
and a single tuple, the query result will be a
single (scalar) value.
In such cases, it is permissible to use =
instead of IN for the comparison operator.
In general, the nested query will return a
table (relation), which is a set or multiset of
tuples.
SQL allows the use of tuples of values in
comparisons by placing them within parentheses.
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN ( SELECT Pno, Hours FROM
WORKS_ON WHERE Essn = ‘123456789’ );
 In addition to the IN operator, a number of other
comparison operators can be used to compare a
single value v (typically an attribute name) to a set
or multiset v (typically a nested query).
 The = ANY (or = SOME) operator returns TRUE if the
value v is equal to some value in the set V and is
hence equivalent to IN.
 Other operators that can be combined with ANY (or
SOME) include >, >=, <=, and <>.
the names of employees whose salary is
greater than the salary of all the employees
in department 5
 SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ALL ( SELECT Salary FROM
EMPLOYEE WHERE Dno = 5 );

The rule is that 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 and is the same sex as the
employee.
Q16: SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN ( SELECT D.Essn FROM
DEPENDENT AS D WHERE E.Fname =
D.Dependent_name AND E.Sex = D.Sex );
Correlated Nested Queries
Whenever a condition in the WHERE clause of a
nested query references some attribute of a relation
declared in the outer query, the two queries are said
to be correlated.
In general, 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 AS E, DEPENDENT AS D
WHERE E.Ssn = D.Essn AND E.Sex = D.Sex AND
E.Fname = D.Dependent_name;
The EXISTS and UNIQUE Functions
in SQL
EXISTS and UNIQUE are Boolean functions
that return TRUE or FALSE; hence, they can
be used in a WHERE clause condition.
The EXISTS function in SQL is used to check
whether the result of a nested query is
empty (contains no tuples) or not.
Q16B: SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE EXISTS ( SELECT * FROM
DEPENDENT AS D WHERE E.Ssn = D.Essn
AND E.Sex = D.Sex AND E.Fname =
D.Dependent_name);
Query 6. Retrieve the names of employees
who have no dependents.
Q6: SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT * FROM
DEPENDENT WHERE Ssn = Essn );
Query 7. List the names of managers who
have at least one dependent.
Q7: SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT * FROM
DEPENDENT WHERE Ssn = Essn ) AND
EXISTS ( SELECT * FROM DEPARTMENT
WHERE Ssn = Mgr_ssn );
Retrieve the name of each employee who works on all the projects controlled by
department number 5

Q3A: SELECT Fname, Lname

FROM EMPLOYEE
WHERE NOT EXISTS ( ( SELECT Pnumber
FROM PROJECT WHERE Dnum = 5) EXCEPT
( SELECT Pno FROM WORKS_ON WHERE Ssn
= Essn) );
Q3B: SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS
( SELECT * FROM WORKS_ON B
WHERE ( B.Pno IN ( SELECT Pnumber
FROM PROJECT
WHERE Dnum = 5 ) AND
NOT EXISTS ( SELECT * FROM WORKS_ON C
WHERE C.Essn = Ssn AND C.Pno = B.Pno )));
Explicit Sets and Renaming in
SQL
Query 17. Retrieve the Social Security numbers
of all employees who work on project numbers
1, 2, or 3.
Q17: SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);

Q8A: SELECT E.Lname AS Employee_name,

S.Lname AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;
Joined Tables in SQL and Outer
Joins
Q1, which retrieves the name and address
of every employee who works for the
‘Research’ department
Q1A: SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON
Dno = Dnumber)
WHERE Dname = ‘Research’;
In a NATURAL JOIN on two relations R and
S, no join condition is specified; an implicit
EQUIJOIN condition for each pair of
attributes with the same name from R and
S is created
Q1B: SELECT Fname, Lname, Address
FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno,
Mssn, Msdate)))
WHERE Dname = ‘Research’;
The default type of join in a joined table is
called an inner join, where a tuple is
included in the result only if a matching
tuple exists in the other relation.
Q8B: SELECT E.Lname AS Employee_name, S.Lname AS
Supervisor_name
FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE
AS S ON E.Super_ssn = S.Ssn);

 INNER JOIN only pairs of tuples that match the join

condition are retrieved, same as JOIN
 LEFT OUTER JOIN every tuple in the left table must
appear in the result; if it does not have a matching
tuple, it is padded with NULL values for the attributes
of the right table,
 RIGHT OUTER JOIN every tuple in the right table must
appear in the result; if it does not have a matching
tuple, it is padded with NULL values for the attributes
of the left table
 FULL OUTER JOIN
It is also possible to nest join specifications; that
is, one of the tables in a join may itself be a
joined table
the join of three or more tables as a single
joined table, is called a multiway join
Q2A: SELECT Pnumber, Dnum, Lname, Address,
Bdate
FROM ((PROJECT JOIN DEPARTMENT ON Dnum =
Dnumber) JOIN EMPLOYEE ON Mgr_ssn = Ssn)
WHERE Plocation = ‘Stafford’;
the comparison operators + =, = +, and + = +
for left, right, and full outer join, respectively
Q8C: SELECT E.Lname, S.Lname
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.Super_ssn + = S.Ssn;
Aggregate Functions in SQL
Aggregate functions are used to summarize
information from multiple tuples into a
single-tuple summary.
Grouping is used to create subgroups of
tuples before summarization.
A number of built-in aggregate functions
exist: COUNT, SUM, MAX, MIN, and AVG.
The COUNT function returns the number of
tuples or values as specified in a query.
Query 19. Find the sum of the salaries of all
employees, the maximum salary, the
minimum salary, and the average salary.
Q19: SELECT SUM (Salary), MAX (Salary),
MIN (Salary), AVG (Salary)
FROM EMPLOYEE;
Q19A: SELECT SUM (Salary) AS Total_Sal,
MAX (Salary) AS Highest_Sal, MIN (Salary)
AS Lowest_Sal, AVG (Salary) AS
Average_Sal
FROM EMPLOYEE;
Query 20. Find the sum of the salaries of all
employees of the ‘Research’ department,
as well as the maximum salary, the
minimum salary, and the average salary in
this department.
Q20: SELECT SUM (Salary), MAX (Salary), MIN
(Salary), AVG (Salary)
FROM (EMPLOYEE JOIN DEPARTMENT ON
Dno = Dnumber)
WHERE Dname = ‘Research’;
Queries 21 and 22. Retrieve the total
number of employees in the company
(Q21) and the number of employees in the
‘Research’ department (Q22).
Q21: SELECT COUNT (*) FROM EMPLOYEE;
Q22: SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME =
‘Research’;

Query 23. Count the number of distinct

salary values in the database.
Q23: SELECT COUNT (DISTINCT Salary)
FROM EMPLOYEE;
to retrieve the names of all employees who
have two or more dependents (Query 5),
Q5: SELECT Lname, Fname
FROM EMPLOYEE
WHERE ( SELECT COUNT (*) FROM
DEPENDENT WHERE Ssn = Essn ) > = 2;
Grouping: The GROUP BY and
HAVING Clauses
Query 24. For each department, retrieve
the department number, the number of
employees in the department, and their
average salary.
Q24: SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
Query 25. For each project, retrieve the
project number, the project name, and the
number of employees who work on that
project.
Q25: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname;
Query 26. For each project on which more
than two employees work, retrieve the
project number, the project name, and the
number of employees who work on the
project.
Q26: 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, the project name, and the
number of employees from department 5
who work on the project.
Q27: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno AND Ssn = Essn AND
Dno = 5
GROUP BY Pnumber, Pname;
SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000
GROUP BY Dno
HAVING COUNT (*) > 5;
Query 28. For each department that has more
than five employees, retrieve the department
number and the number of its employees who
are making more than $40,000
Q28: SELECT Dno, COUNT (*)
FROM EMPLOYEE WHERE Salary>40000 AND Dno
IN ( SELECT Dno FROM EMPLOYEE
GROUP BY Dno HAVING COUNT (*) > 5)
GROUP BY Dno;
Other SQL Constructs: WITH and
CASE
Q28′: WITH BIGDEPTS (Dno) AS ( SELECT
Dno FROM EMPLOYEE GROUP BY Dno
HAVING COUNT (*) > 5) SELECT Dno,
COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN
BIGDEPTS GROUP BY Dno;
U6′: UPDATE EMPLOYEE
SET Salary = CASE
WHEN Dno = 5 THEN
Salary + 2000
WHEN Dno = 4 THEN
Salary + 1500
WHEN Dno = 1 THEN
Salary + 3000 ELSE Salary + 0 ;
Recursive Queries in SQL
Q29: WITH RECURSIVE SUP_EMP (SupSsn,
EmpSsn) AS
( SELECT SupervisorSsn, Ssn
FROM EMPLOYEE UNION
SELECT E.Ssn, S.SupSsn
FROM EMPLOYEE AS E, SUP_EMP AS S
WHERE E.SupervisorSsn = S.EmpSsn)
SELECT*
FROM SUP_EMP;
Views (Virtual Tables) in SQL
A view in SQL terminology is a single table
that is derived from other tables
A view does not necessarily exist in
physical form; it is considered to be a
virtual table, in contrast to base tables,
whose tuples are always physically stored
in the database

 EMPLOYEE, WORKS_ON, and PROJECT

tables the defining tables of the view.
Specification of Views in SQL
 In SQL, the command to specify a view is CREATE VIEW.
 The view is given a (virtual) table name (or view name), a list
of attribute names, and a query to specify the contents of the
view.

 V1: CREATE VIEW WORKS_ON1 AS SELECT Fname, Lname,

Pname, Hours
 FROM EMPLOYEE, PROJECT, WORKS_ON
 WHERE Ssn = Essn AND Pno = Pnumber;

 V2: CREATE VIEW DEPT_INFO(Dept_name, No_of_emps,

Total_sal) AS SELECT Dname, COUNT (*), SUM (Salary)
 FROM DEPARTMENT, EMPLOYEE
 WHERE Dnumber = Dno
 GROUP BY Dname;
To retrieve the last name and first name of
all employees who work on the ‘ProductX’
project, can utilize theWORKS_ON1 view
and specify the query as in
QV1: QV1: SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname = ‘ProductX’.

V1A: DROP VIEW WORKS_ON1;

View Implementation, View
Update, and Inline Views
query modification, involves modifying or
transforming the view query into a query
on the underlying base tables.
SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = Pnumber
AND Pname = ‘ProductX’;
view materialization, involves physically
creating a temporary or permanent view
table when the view is first queried or
created and keeping that table on the
assumption that other queries on the view
 UV1: UPDATE WORKS_ON1
 SET Pname = ‘ProductY’
 WHERE Lname = ‘Smith’ AND Fname = ‘John’ AND
Pname = ‘ProductX’;

 (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
= ( SELECT Pnumber
 FROM PROJECT
 WHERE Pname = ‘ProductX’ );

 (b): UPDATE PROJECT SET Pname = ‘ProductY’ WHERE

Pname = ‘ProductX’;
A view with a single defining table is
updatable if the view attributes contain the
primary key of the base relation, as well as
all attributes with the NOT NULL constraint
that do not have default values specified.
Views defined on multiple tables using joins
are generally not updatable.
Views defined using grouping and
aggregate functions are not updatable.
Views as Authorization
Mechanisms
retrieve employee information for
employee tuples whose Dno = 5, and will
not be able to see other employee tuples
when the view is queried.
CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;
CREATE VIEW BASIC_EMP_DATA AS
SELECT Fname, Lname, Address
FROM EMPLOYEE;
Schema Change Statements in
SQL
The DROP Command
to remove the COMPANY database schema
and all its tables, domains, and other
elements, the CASCADE option is used as
follows:
DROP SCHEMA COMPANY CASCADE;
RESTRICT the schema is dropped only if it
has no elements in it.
DROP TABLE DEPENDENT CASCADE;
The ALTER Command
 to add an attribute for keeping track of jobs of
employees to the EMPLOYEE base relation in the
COMPANY schema
 ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job
VARCHAR(12);

 If CASCADE is chosen, all constraints and views that

reference the column are dropped automatically from
the schema, along with the column.
 If RESTRICT is chosen, the command is successful only
if no views or constraints (or other schema elements)
reference the column.
 ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN
Address CASCADE;
 to alter a column definition by dropping an existing default
clause or by defining a new default clause.
ALTER TABLE COMPANY.DEPARTMENT ALTER
COLUMN Mgr_ssn DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER
COLUMN Mgr_ssn SET DEFAULT
‘333445555’;

to drop the constraint named EMPSUPERFK

in Figure 6.2 from the EMPLOYEE relation,
ALTER TABLE COMPANY.EMPLOYEE
DROP CONSTRAINT EMPSUPERFK CASCADE;
Specifying Constraints as
Assertions and Actions as Triggers
 two additional features of SQL: the CREATE
ASSERTION statement and the CREATE
TRIGGER statement
CREATE ASSERTION, which can be used to
specify additional types of constraints that
are outside the scope of the built-in
relational model constraints (primary and
unique keys, entity integrity, and referential
integrity)
CREATE TRIGGER, which can be used to
specify automatic actions that the database
system will perform when certain events
and conditions occur.