SQL [part 5/5]

• SQL queries (cont.)

• Nested queries
• Data modification: insert, delete, update
• Other definitions of data
• Use of SQL in programming languages
Nested queries

The where clause can contain complex predicates in which an attribute or

attribute expression is compared with the result of the execution of a SQL
query. The SQL query contained within the where clause of the other query is
called a nested query.
● Syntax
ComparisonOperator < any | all > SelectSQL

○ ComparisonOperator: one of the normal operators (i.e., =, <>, <, >, <=, >=)
○ any: the predicate is true if at least one row returned by SelectSQL satisfies the
comparison. The short form of =any is in.
○ all: the predicate is true if all the rows returned by SelectSQL satisfy the
comparison. The short form of <>all is not in.
○ The domain of the elements returned by the nested query must be compatible
with the attribute with which the comparison is made
Nested queries - examples

● Example

Q1: Find the first names and surnames of employees who work in the
departments based in London select FirstName, Surname
from EMPLOYEE
where Dept = any (select DeptName
from DEPARTMENT
where City=‘London’)

Alternative solution (without using a nested query)

select FirstName, Surname
from EMPLOYEE E, DEPARTMENT D
where E.Dept=D.DeptName and D.City=‘London’
Nested queries - examples

● Example

Q2: Find the first names and surnames of employees of the Planning
department who share an office with an employee of the Research department
select FirstName, Surname
from EMPLOYEE
where Dept=‘Planning’ and
Office = any (select Office
from EMPLOYEE
NOTE: =any can be replaced by in where Dept=‘Research’)
Alternative solution (without using a nested query)
select P.FirstName, P.Surname
from EMPLOYEE P, EMPLOYEE R
where P.Dept=‘Planning’ and
R.Dept=‘Research’ and
P.Office=R.Office
Nested queries - examples

● Example

Q3: Find the names of the departments in which there is no one sits in office 20
select DeptName
from DEPARTMENT
where DeptName <> all (select Dept
NOTE: <>all can be replaced from EMPLOYEE
by not in where Office=20)

Alternative solution (without using a nested query)

select DeptName
from DEPARTMENT
except
select Dept
from EMPLOYEE
where Office=20
Nested queries – aggregate operators with a nested query

Aggregate operators (e.g., max, min) can be used in nested queries

Q4: Find the first names and surnames of the employees who earn the highest
salary
select FirstName, Surname
from EMPLOYEE
where Salary = any (select max(Salary)
from EMPLOYEE)

Alternative solution
select FirstName, Surname
from EMPLOYEE
where Salary >= all (select Salary
from EMPLOYEE
where Salary is not null)
Nested queries – logical operator exists

SQL offers a logical operator exists for use with a nested query.
● The operator returns true only if the nested query does not produce an
empty result.
● not exists is the opposite of exists
Q5: Find the names and locations (cities) of the departments in which no
employees earn a salary greater than 40
select DeptName
from DEPARTMENT D
where not exists (select *
from EMPLOYEE E
where D.DeptName=E.Dept and
E.Salary>40)
Data modification in SQL

The SQL statements that allow data modification are

○ insert: insert rows into a table
○ delete: delete rows from a table
○ update: change attribute values of the rows of a table
● All of the three statements can operate on a set of rows (i.e., set oriented)
● The statements can contain conditions or SQL queries that access other
tables
Data modification in SQL – insert statement

● Syntax insert into TableName [ (AttributeList) ]

< values (ListOfValues) | SelectSQL >

● Example

S1: Insert a row into table DEPARTMENT by specifying a list of values

insert into DEPARTMENT
values ('Payroll', 'Merrion Sq North', 'Dublin')

S2: Insert a row into table DEPARTMENT by specifying a list of values

insert into DEPARTMENT(City, Address, DeptName)
values ('Dublin', 'Beech Hill Office Campus', 'H&R')
Data modification in SQL – insert statement

Example

S3: Insert a row into table DEPARTMENT by specifying a list of values

insert into DEPARTMENT (DeptName, City)
values ('Finance', 'Dublin')

S4: Extract information about all departments based in London (using a

subquery) and insert the information into table LONDON_DEPARTMENT
insert into LONDON_DEPARTMENT
(select DeptName, Address
from DEPARTMENT
where City='London')
Data modification in SQL – insert statement

● The order of the attributes in AttributeList (if present) dictates the

correspondence between the values to be inserted and the attributes of the
table
● If AttributeList is omitted, all the attributes of the table are considered, and
the attribute order defined in the table definition is assumed
● If AttributeList does not contain all attributes of the table, the default value
(if defined) or the null value is assigned to the missing attributes
Data modification in SQL – delete statement

● Syntax delete from TableName

[ where Condition ]

● Example
S5: Delete information of all employees shoes salary is null
delete from EMPLOYEE
where Salary is null

S6: Delete information of all employees who work in a department based in

London
delete from EMPLOYEE
where Dept = any(select DeptName
from DEPARTMENT
where City='London')
Data modification in SQL – delete statement

● The delete statement removes from the table all rows that satisfy the
specified condition
● The removal may produce deletions of rows in other tables if a referential
integrity constraint with cascade reaction policy has been defined
● If the where clause is omitted, delete removes all rows in the table while
keeping the table schema
S7: Delete all rows in table LONDON_DEPARTMENT
delete from LONDON_DEPARTMENT

● To remove the schema of a table, use drop statement

S8: Remove the schema of table LONDON_DEPARTMENT
drop table LONDON_DEPARTMENT
Data modification in SQL – update statement

● Syntax update TableName

set Attribute = < Expression | SelectSQL | null | default >
{, Attribute = < Expression | SelectSQL | null | default > }
[ where Condition ]
● Example
S9: Increase the salary of all employees in the Production department by 10%
update EMPLOYEE
set Salary = Salary * 1.1
where Dept= 'Production'
Data modification in SQL – update statement

● As the language is set oriented, the order of the statements is important.

● In the following example, some employees may get a double raise.

update EMPLOYEE
set Salary = Salary * 1.1
where Salary < 50

update EMPLOYEE
set Salary = Salary * 1.1
where Salary >= 50
Other definitions of data – generic integrity constraints

A constraint is a property that a set of data must satisfy.

● Constraints can be classified as
○ Immediate constraints: verified immediately after each modification of the DB
○ Deferred constraints: verified only at the end of a series of operations (transaction)
● Constraints can also be classified depending on the objects they access
○ On a single relation
o On a single tuple
▪ Attribute constraints
▪ Multiple-attribute constraints
o On multiple rows of the same relation
▪ Functional dependencies
▪ Cardinality constraints
o Aggregation constraints
○ On multiple relations: referential integrity constraints (foreign keys)
Other definitions of data – generic integrity constraints

Examples:
● On a single attribute
Salary of an employee must be between 20 and 150 (thousands)
● On multiple attributes (assuming table EMPLOYEE has two attributes Salary
and Bonus)
The bonus an employee earns must always be less than their salary
● Cardinality constraints
There must be at least three employees in the Administration department
● Aggregation constraints
The average salary of employees in department Planning must be greater than 30
● Constraints on multiple relations:
The sum of salaries of employees working in London must not be greater than 900
Other definitions of data – generic integrity constraints – check clause

● The check clause can be used to express arbitrary constraints during

schema definition
● Syntax check ( Condition )

● Condition is what can appear in a where clause (including nested queries)

● Example
S10: Create a table MODULES that contains to check clauses
create table MODULES(
Code varchar(10),
Name varchar(20),
Credits smallint, (1) Module codes must start with “COMP”
check (Code like "COMP%"),
check (Credits >=5 and Credits <=15)) (2) Numbers of credits are between 5 and 15
Other definitions of data – generic integrity constraints - assertions

● Assertions allow the definition of constraints outside of table definitions

● Useful in many situations (e.g., to express generic inter-relational
constraints)
● Syntax create assertion AssertionName check (Condition)

● Condition is what can appear in a where clause (including nested queries)

● Example
S11: Create an assertion to ensure that table EMPLOYEE must always have at
least one row
create assertion AtLeastOneEmp
check (1 <= (select count(*) from EMPLOYEE))

Note: As of version 8.0.41, MySQL does not support assertions. Triggers is a possible alternative.
Other definitions of data – Views

Views are ‘virtual’ tables whose contents depend on contents of other tables in
the DB
● Syntax create view ViewName [ (AttributeList) ] as SelectSQL
[ with [ local | cascaded ] check option ]

● The statement associates a name and a list of attributes with the result of
the execution of a query
● A view can be defined based on other views
● Views cannot be mutually dependent
Other definitions of data – Views

● Examples

S12: Create a view that contains information about all employees in the
Production department
create view PROD_EMPLOYEE as
select * from EMPLOYEE
where Dept= 'Production'

S13: Create a view which is a subset of PROD_EMPLOYEE that contains only

employees who earn a salary less than 50. Keep only the FirstName, Surname
and Salary attributes.
create view JUNIOR_PROD_EMPLOYEE (FirstName, Surname, Salary) as
select FirstName, Surname, Salary from PROD_EMPLOYEE
where Salary<50
Use of SQL in programming languages

SQL statements can be embedded inside the instructions written in a host

programming language (e.g., C, Java) to build application programs
● Programs with embedded SQL use a precompiler to manage SQL statements
● Embedded SQL statements are preceded by a special character (e.g., ‘$’ or
‘EXEC SQL’
● Program variables may be used a parameters in the SQL statements
(preceded by character `:`)
● Cursor allows a program to access the rows of a table one at a time
● SQL environment offers a predefined variable sqlcode to describe to status
of the SQL execution
Use of SQL in programming languages

Example of a C program that contains embedded SQL