Intermediate

SQL
Intermediate SQL
• Join Expressions
• Views (SDL)
• Transactions (SDL)
• Integrity Constraints
• SQL Data Types and Schemas
• Authorization (SDL)

Jul 18, 2025 Dept. of I&CT 2

Joined Relations
• Join operations take two relations and return as a result another relation.
• A join operation is a Cartesian product which requires that tuples in the two
relations match (under some condition).
• It also specifies the attributes that are present in the result of the join
• The join operations are typically used as subquery expressions in the from
clause

Select name, course_id

from instructor natural join teaches;

Jul 18, 2025 Dept. of I&CT 3

Join Expressions – Join Conditions
• The join…using clause: Form of natural join that only requires values to
match on specified attributes
• SQL supports another form of join: Specifying arbitrary join condition
• The on condition allows a general predicate over the relations being joined
• This predicate is written like a where clause predicate except for the use
of the keyword on rather than where

select *
from student join takes on [Link] =
[Link];
Jul 18, 2025 Dept. of I&CT 4
Join Expressions – Join Conditions
List the details of the students who have taken a course
select *
from student, takes
where [Link] = [Link];
OR
select *
from student natural join takes;
OR
select *
from student join takes on [Link] = [Link];
Jul 18, 2025 Dept. of I&CT 5
Outer Join
• In natural join some tuples in either or both of the relations being joined may be
“lost”.

• An extension of the join operation that avoids loss of information

• The outer join preserves the tuples that would be lost in a join, by creating tuples
in the result containing null values

• Computes the join and then adds tuples from one relation that does not match
tuples in the other relation to the result of the join using null values.

Jul 18, 2025 Dept. of I&CT 6

Outer Join
1. Computes the join ( Adds all the matching tuples)

2. Adds tuples form one relation that does not match tuples in the other relation to
the result of the join.

3. Uses null values for unmatched tuples.

• Three forms of outer join:

• left outer join
• right outer join
• full outer join
SQL> select * from emp; SQL> select * from dept;

EID NAME DID DEPT_NAME BUILD

DID ---------- ---------- -----
---------- ----- ---------- 1 Nursing B1
101 ABC 2 Medicine B2
1 3 Ortho B3
102 Deepa 4 Neurology B2
1
103 Hari
2 SQL> select * from emp natural join dept;
104 John
NULL DID EID NAME DEPT_NAME BUILD
105 Ravi ---------- ---------- ----- ---------- -----
NULL 1 101 ABC Nursing B1
1 102 Deepa Nursing B1
2 103 Hari Medicine B2

Jul 18, 2025 Dept. of I&CT 8

SQL> select * from emp; SQL> select * from dept;

EID NAME DID DEPT_NAME BUILD

DID ---------- ---------- -----
---------- ----- ---------- 1 Nursing B1
101 ABC 2 Medicine B2
1 3 Ortho B3
102 Deepa 4 Neurology B2
1
103 Hari
2
104 SQL>John select * from emp natural left outer join dept;
NULL
105 Ravi
DID EID NAME DEPT_NAME BUILD
NULL ---------- ---------- ----- ---------- -----
1 101 ABC Nursing B1
1 102 deepa Nursing B1
2 103 Hari Medicine B2
_ 104 John _ _
_ 105 Ravi _ _
Jul 18, 2025 Dept. of I&CT 9
SQL> select * from emp; SQL> select * from dept;

EID NAME DID DEPT_NAME BUILD

DID ---------- ---------- -----
---------- ----- ---------- 1 Nursing B1
101 ABC 2 Medicine B2
1 3 Ortho B3
102 Deepa 4 Neurology B2
1
103 Hari
2
104 SQL>John select * from emp natural right outer join dept;
NULL
105 DID
Ravi EID NAME DEPT_NAME BUILD
NULL ---------- ---------- ----- ---------- -----
1 101 ABC Nursing B1
1 102 Deepa Nursing B1
2 103 Hari Medicine B2
3 _ _ Ortho B3
4 _ _ Neurology B2
Jul 18, 2025 Dept. of I&CT 10
SQL> select * from emp; SQL> select * from dept;

EID NAME DID DEPT_NAME BUILD

DID ---------- ---------- -----
---------- ----- ---------- 1 Nursing B1
101 ABC 2 Medicine B2
1 3 Ortho B3
102 Deepa 4 Neurology B2
1
103 Hari
SQL> select * from emp natural full outer join dept;
2
104 John
NULL DID EID NAME DEPT_NAME BUILD
105 ---------- Ravi ---------- ----- ---------- -----
NULL 1 101 ABC Nursing B1
1 102 deepa Nursing B1
2 103 Hari Medicine B2
_ 104 John
_ 105 Ravi
3 _ _ Ortho B3
4 _ _ Neurology B2
Jul 18, 2025 Dept. of I&CT 11
Outer Join
• There are three forms of outer join:
 The left outer join preserves tuples only in the relation named before
(to the left of) the left outer join operation
select * from course natural left outer join prereq;

Jul 18, 2025 Dept. of I&CT 12

Outer Join
 Right outer Join preserves tuples only in the relation named after (to the
right of) the right outer join operation
select *
from course natural right outer join prereq;

 Full outer join preserves tuples in both relations

Jul 18, 2025 Dept. of I&CT 13

Outer Join
• The right outer join is symmetric to the left outer join

A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A.

• The full outer join is a combination of the left and right outer join types

Jul 18, 2025 Dept. of I&CT 14

Outer Join
• Find all students who have not taken a course
• Display a list of all students in the Comp. Sci. department, along with the course
sections, if any, that they have taken in Spring 2021, all course sections from
Spring 2021 must be displayed, even if no student from the Comp. Sci.
department has taken the course section

Jul 18, 2025 Dept. of I&CT 15

 SQL> select *
from instructor i natural join department d;
DEPT_NAME ID NAME SALARY BUILDING BUDGET
------------ ----- ------------ ---------- - --------- - ---------
Comp. Sci. 10101 Srinivasan 65000 Taylor 100000
Finance 12121 Wu 90000 Painter 120000
Music 15151 Mozart 40000 Packard 80000
Physics 22222 Einstein 95000 Watson 70000
History 32343 El Said 60000 Painter 50000
Physics 33456 Gold 87000 Watson 70000
Comp. Sci. 45565 Katz 75000 Taylor 100000
History 58583 Califieri 62000 Painter 50000
Finance 76543 Singh 80000 Painter 120000
Biology 76766 Crick 72000 Watson 90000
Comp. Sci. 83821 Brandt 92000 Taylor 100000
Elec. Eng. 98345 Kim 80000 Taylor 85000

12 rows selected.

Jul 18, 2025 Dept. of I&CT 16

SQL> select *
from instructor i inner join department d on i.dept_name=d.dept_name

ID NAME DEPT_NAME SALARY DEPT_NAME BUILDING

BUDGET
----- ------------ ------------ ---------- ------------ ---------- ----------
10101 Srinivasan [Link]. 65000 [Link]. Taylor 100000
12121 Wu Finance 90000 Finance Painter
120000
15151 Mozart Music 40000 Music Packard 80000
22222 Einstein Physics 95000 Physics Watson 70000
32343 El Said History 60000 History Painter 50000
33456 Gold Physics 87000 Physics Watson 70000
45565 Katz [Link]. 75000 [Link]. Taylor 100000
58583 Califieri History 62000 History Painter 50000
76543 Singh Finance 80000 Finance Painter
120000
76766 Crick Biology 72000 Biology Watson 90000
83821 Brandt [Link]. 92000 [Link]. Taylor 100000
98345 Kim [Link]. 80000 [Link]. Taylor 85000
12 rows selected.
Jul 18, 2025 Dept. of I&CT 17
Outer Join
• The on clause can be used with outer joins
select *
from student left outer join takes on [Link] = [Link];
• on and where behave differently for outer join
The on condition is part of the outer join specification, but a where clause is
not

Jul 18, 2025 Dept. of I&CT 18

Integrity Constraints
• Guard against accidental damage to the database by ensuring that authorized changes to
the database do not result in a loss of data consistency

• Identified as part of the database schema design process, and declared as part of the create
table command used to create relations

• Can also be added to an existing relation by using the command alter table table-name
add constraint, where constraint can be any constraint on the relation.
• When alter command is executed, the system first ensures that the relation satisfies the specified
constraint.

• If it does, the constraint is added to the relation; if not, the command is rejected.

Jul 18, 2025 Dept. of I&CT 19

Integrity Constraints on single table
• Primary key,
• not null,
• Unique
• check(P), where P is a predicate

Not null: The not null specification prohibits the insertion of a null value for the
attribute
name varchar(20) not null
budget numeric(12, 2) not null

Jul 18, 2025 Dept. of I&CT 20

Integrity Constraints
Unique (A1, A2,…, Am )

• This constraint when associated with the column(s) of a table, it will not allow us to store
a repetition of data/values in the column, but Unique constraint allows ONE NULL value.

• More than one NULL value is also considered as repetition, hence it does not store a
repetition of NULL values also

• The unique specification states that the attributes A1, A2,…, Am form a candidate key

• Candidate keys are permitted to be null

Jul 18, 2025 Dept. of I&CT 21

Integrity Constraints
Unique (A1, A2,…, Am )

• More than one NULL value is also considered as repetition, hence it does not store a
repetition of NULL values also

• The unique specification states that the attributes A1, A2,…, Am form a candidate key

• Candidate keys are permitted to be null

CREATE TABLE Employees ( emp_id INT PRIMARY KEY,

email VARCHAR(100) UNIQUE,
phone_number VARCHAR(15) UNIQUE );

Jul 18, 2025 Dept. of I&CT 22

Integrity Constraints
Check(P):
• When applied to a relation declaration, the clause check(P) specifies a
predicate P that must be satisfied by every tuple in a relation

create table department(

deptname varchar (20),
building varchar (15),
budget numeric (12, 2) check (budget > 0),
primary key (deptname)) ;

Jul 18, 2025 Dept. of I&CT 23

Referential Integrity
• Ensures that a value that appears in one relation for a given set of attributes also appears
for a certain set of attributes in another relation.
• Example: If “Biology” is a department name appearing in one of the tuples in the
instructor relation, then there exists a tuple in the department relation for “Biology”.

• Let A be a set of attributes.

• Let R and S be two relations that contain attributes A and where A is the primary key of S.

• A is said to be a foreign key of R if for any values of A appearing in R these values

must appear in S.

Jul 18, 2025 Dept. of I&CT 24

Referential Integrity
• Constrint violation
• Normally reject the action that caused the violation (that is, the transaction
performing the update action is rolled back).
• A foreign key clause can specify that
• if a delete or update action on the referenced relation violates the constraint,
• then, instead of rejecting the action, the system must take steps to change the
tuple in the referencing relation to restore the constraint

Jul 18, 2025 Dept. of I&CT 25

Referential Integrity
• CASCADE:
• If a referenced row is deleted or updated, the change propagates to the referencing table.

• SET NULL:
• If a referenced row is deleted or updated, the foreign key in the referencing table is set to
NULL.

• SET DEFAULT:
• Similar to SET NULL, but instead of NULL, a default value is assigned to the foreign key.

• RESTRICT:
• Prevents the deletion or update of a referenced row if any referencing rows exist.
• This is the default behavior in some databases.

Jul 18, 2025 Dept. of I&CT 26

 create table parent ( id int primary key );

create table child ( id int primary key,

parent_id int,
foreign key (parent_id) references parent(id)
on delete cascade
on update set null );

Jul 18, 2025 Dept. of I&CT 27

Complex check clause

• The predicate in the check clause can be an arbitrary predicate, which can
include a subquery.
• check (time_slot_id in (select time_slot_id from time_slot))

• The check condition verifies that the time slot id in each tuple in the section relation is
actually the identifier of a time slot in the time slot relation.

• The condition has to be checked when

• a tuple is inserted or modified in section

• the relation time slot changes (in this case, when a tuple is deleted or modified in
relation time slot)
Jul 18, 2025 Dept. of I&CT 28
Complex Check Clauses
• An assertion is a predicate that expresses a condition the database must always
satisfy.
• Domain constraints and referential-integrity constraints are special forms of
assertions
• syntax
create assertion <assertion-name> check <predicate>;

Jul 18, 2025 Dept. of I&CT 29

Complex Check Clauses

create assertion A1 as CHECK

( (select count(*)
from EMP e
where [Link] = 'President') <= 1
)

Jul 18, 2025 Dept. of I&CT 30

Complex Check Clauses

create assertion At_most_one_president as CHECK

( ( select count(*)
from EMP e
where [Link] = 'President') <= 1
)

Jul 18, 2025 Dept. of I&CT 31

Complex Check Clauses
• This SQL statement creates an assertion to demand that there's no more than
a single president among the employees:
create assertion At_most_one_president as CHECK
( (select count(*)
from EMP e
where [Link] = 'President') <= 1
)

Jul 18, 2025 Dept. of I&CT 32

Complex Check Clauses
• create assertion A1 as CHECK
(not exists
( select 'trainer in Boston'
from EMP e, DEPT d
where [Link] = [Link]
and [Link] = 'TRAINER'
and [Link] = 'BOSTON’
)
)

Jul 18, 2025 Dept. of I&CT 33

Complex Check Clauses
• create assertion NO_TRAINERS_IN_BOSTON as CHECK
(not exists
( select 'trainer in Boston'
from EMP e, DEPT d
where [Link] = [Link]
and [Link] = 'TRAINER'
and [Link] = 'BOSTON’
)
)

Jul 18, 2025 Dept. of I&CT 34

Complex Check Clauses
• This SQL statement creates an assertion to demand that Boston based departments do not employ
trainers:
create assertion NO_TRAINERS_IN_BOSTON as CHECK
(not exists
( select 'trainer in Boston'
from EMP e, DEPT d
where [Link] = [Link]
and [Link] = 'TRAINER'
and [Link] = 'BOSTON’
)
)

Jul 18, 2025 Dept. of I&CT 35

Complex Check Clauses
• Make sure that supervisee’s salary is less than his Supervisor’s salary

Jul 18, 2025 Dept. of I&CT 37

Complex Check Clauses
• Make sure that supervisee’s salary is less than his Supervisor’s salary

Create assertion Emp_sal_chek

check(not exists (
select *
from Employee E1, Employee E2
where E1.Mgr_Id=E2.Emp_Id and
[Link]>[Link]) );

Jul 18, 2025 Dept. of I&CT 38

Complex Check Clauses
• For each tuple in the student relation, the value of the attribute tot cred must equal the sum of credits of
courses that the student has completed successfully.

create assertion credits earned constraint check

( not exists
(select ID from student
where tot cred <> (select sum(credits)
from takes natural join course
where [Link]= [Link]
and grade is not null and grade<> ’F’ )

Jul 18, 2025 Dept. of I&CT 39

Built-in Data Types in SQL

• date: Dates, containing a (4 digit) year, month and date

• Example: date ‘2005-7-27’
• time: Time of day, in hours, minutes and seconds.
• Example: time ‘09:00:30’ time ‘09:00:30.75’
• timestamp: date plus time of day
• Example: timestamp ‘2005-7-27 09:00:30.75’
• interval: period of time
• Example: interval ‘1’ day
• Subtracting a date/time/timestamp value from another gives an interval value
• Interval values can be added to date/time/timestamp values

Jul 18, 2025 Dept. of I&CT 40

User-Defined Types
• create type construct in SQL creates user-defined type
create type Dollars as numeric (12,2)

• create table department

(dept_name varchar (20),
building varchar (15),
budget Dollars);

Jul 18, 2025 Dept. of I&CT 41

Domains

• create domain construct in SQL-92 creates user-defined domain types

create domain person_name char(20) not null

• Types and domains are similar.

• Domains can have constraints, such as not null, specified on them.

create domain degree_level varchar(10)
constraint degree_level_test
check (value in (’Bachelors’, ’Masters’,
’Doctorate’));

Jul 18, 2025 Dept. of I&CT 42

Large-Object Types
• Large objects (photos, videos, CAD files, etc.) are stored as a large object:
• blob: binary large object -- object is a large collection of uninterpreted
binary data (whose interpretation is left to an application outside of the
database system)
• clob: character large object -- object is a large collection of character data

When a query returns a large object, a pointer is returned rather than the
large object itself.

Jul 18, 2025 Dept. of I&CT 43

Views
In some cases, it is not desirable for all users to see the entire logical model (that is, all the actual relations
stored in the database)

A view provides a mechanism to hide certain data from the view of certain users

SQL allows a “virtual relation” to be defined by a query, and the relation conceptually contains the result
of the query

The virtual relation is not precomputed and stored, but instead is computed by executing the query
whenever the virtual relation is used

Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called
a view
Jul 18, 2025 Dept. of I&CT 44
View Definition
• A view is defined using the create view statement which has the form
create view v as <query expression>
• Once a view is defined, the view name can be used to refer to the virtual
relation that the view generates
• View definition is not the same as creating a new relation by evaluating the
query expression
 Rather, a view definition causes the saving of an expression; the expression
is substituted into queries using the view
• View definitions are stored but not the result of the query in view!

Jul 18, 2025 Dept. of I&CT 45

View Definition
• A view of instructors without salary field
create view faculty_details as
select id, name, deptname
from instructor;
 Find all instructors in the Biology department
• View names may appear in a query any place where a relation name may appear
• Create a view which gets the department salary total
• One view may be used in the expression defining another view
• A view relation v1 is said to depend directly on a view relation v2 if v2 is used in
the expression defining v1
Jul 18, 2025 Dept. of I&CT 46
View Definition
• A view relation v is said to be recursive if it depends on itself
 Define a view that lists the course ID and room number of all Computer
Science courses offered in the Fall 2015 semester in the Watson building
create view cse_fall_2015 as
select [Link], secid, building, roomnumber
from course, section
where [Link] = [Link]
and [Link] = ‘Comp. Sci.’
and [Link] = ‘Fall’
and [Link] = 2015;
Jul 18, 2025 Dept. of I&CT 47
View Definition

create view cseWatson as

select coursed, roomnumber
from cse_fall_2015
where building = ‘Watson’;

Jul 18, 2025 Dept. of I&CT 48

Updating a view
• The difficulty is that a modification to the database expressed in terms of a view must be
translated to a modification to the actual relations in the logical model of the database.
create view faculty as
select ID, name, dept_name
from instructor

• If a tuple is added as follows:

• insert into faculty values (’30765’, ’Green’, ’Music’);
• This insertion must be represented by an insertion into the relation instructor, since
instructor is the actual relation from which the database system constructs the view
faculty. However, to insert a tuple into instructor, we must have some value for salary.
Jul 18, 2025 Dept. of I&CT 49
Updating a view

• There are two reasonable approaches to dealing with this insertion:

• • Reject the insertion and return an error message to the user.
• • Insert a tuple (’30765’, ’Green’, ’Music’, null) into the instructor relation.

Jul 18, 2025 Dept. of I&CT 50

Update of a View
• Add a new tuple to faculty view which we defined earlier
insert into faculty values (’30765’, ’Green’, ’Music’);
• This insertion must be represented by the insertion of the tuple
insert into instructor values (’30765’, ’Green’, ’Music’, null)
into the instructor relation

Jul 18, 2025 Dept. of I&CT 51

Some Updates cannot be Translated Uniquely
• create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name= department.dept_name;

• insert into instructor_info values (’69987’, ’White’, ’AB5’);

• If id does not exist
• what if no department is in AB5?

• Suppose we Insert into instructor the (69987’, ’White’, null, null)

• Insert in department(null, AB5, null)

• In the absence of common dept name this view update will fail
Jul 18, 2025 Dept. of I&CT 52
Updating main table through views

Jul 18, 2025 Dept. of I&CT 53

Updating main table through views

Jul 18, 2025 Dept. of I&CT 54

Updating main table through views

Jul 18, 2025 Dept. of I&CT 55

Updating main table through views
• An SQL view is said to be updatable (that is, inserts, updates or deletes can
be applied on the view) if the following conditions are all satisfied by the
query defining the view:
• The from clause has only one database relation.
• The select clause contains only attribute names of the relation, and does not have any
expressions, aggregates, or distinct specification.
• Any attribute not listed in the select clause can be set to null
• The query does not have a group by or having clause.

Jul 18, 2025 Dept. of I&CT 56

Materialized Views
• Certain database systems allow view relations to be stored, but they make sure
that, if the actual relations used in the view definition change, the view is kept
up-to-date. Such views are called materialized views
• The process of keeping the materialized view up-to-date is called
materialized view maintenance
• Applications that use a view frequently may benefit if the view is materialized
• Benefits to queries from the materialization of a view must be weighted
against the storage costs and the added overhead for updates

Jul 18, 2025 Dept. of I&CT 57

Materialized Views
create materialized view mview

Jul 18, 2025 Dept. of I&CT 58

Materialized Views
create materialized view mview
build immediate/deferred

Jul 18, 2025 Dept. of I&CT 59

 I
Materialized Views
create materialized view mview Build Immediate: Means materialized views
(mv) created immediately
build immediate/deferred

Jul 18, 2025 Dept. of I&CT 60

 I
Materialized Views
create materialized view mview Build Immediate: Means materialized views
(mv) created immediately
build immediate/deferred

Build Deferred: Means materialized view

created after one refresh

Jul 18, 2025 Dept. of I&CT 61

 I
Materialized Views
create materialized view mview
build immediate/deferred
refresh complete/fast/force

Jul 18, 2025 Dept. of I&CT 62

 I
Materialized Views
Complete Refresh: Oracle database
create materialized view mview must re-execute the materialized view
build immediate/deferred query to refresh the view

refresh complete/fast/force

Jul 18, 2025 Dept. of I&CT 63

 I
Materialized Views
Complete Refresh: Oracle database
create materialized view mview must re-execute the materialized view
build immediate/deferred query to refresh the view

refresh complete/fast/force

Fast refresh: Update (or insert/delete)

only the rows which have been changed
on master tables

Jul 18, 2025 Dept. of I&CT 64

 I
Materialized Views
create materialized view mview
build immediate/deferred
refresh complete/fast/force
on commit/demand

Jul 18, 2025 Dept. of I&CT 65

Materialized Views
Refresh on Commit: Commits the data in
create materialized view mview materialized views immediately after data
build immediate/deferred inserted and committed in table – Also known
as Incremental refresh
refresh complete/fast/force
on commit/demand

Jul 18, 2025 Dept. of I&CT 66

Materialized Views
Refresh on Commit: Commits the data in
create materialized view mview materialized views immediately after data
build immediate/deferred inserted and committed in table – Also known
as Incremental refresh
refresh complete/fast/force
on commit/demand

Refresh on Demand: Using this option you can

add the condition for refreshing data in
materialized view

Jul 18, 2025 Dept. of I&CT 67

Materialized Views
create materialized view mview
build immediate/deferred
refresh complete/fast/force
on commit/demand
as
select * from instructor
where deptname = ‘ICT’;

Jul 18, 2025 Dept. of I&CT 68

Jul 18, 2025 Dept. of I&CT 69
Basics of Transactions
• A transaction consists of a sequence of query and/or update statements. The SQL standard
specifies that a transaction begins implicitly when an SQL statement is executed.
• Unit of work
• Atomic transaction
• either fully executed or rolled back as if it never occurred
• Transactions begin implicitly
• Ended by commit work: commits the current transaction; that is, it makes the updates
performed by the transaction become permanent in the database
• rollback work: causes the current transaction to be rolled back; that is, it undoes all the
updates performed by the SQL statements in the transaction.
• (note: detail discussion on transaction will revisit later)

Jul 18, 2025 Dept. of I&CT 70