Basic SQL

Introduction: Parts of SQL

1. Data-definition language (DDL)

2. Data-manipulation language (DML)

3. Integrity

4. View definition

4. Transaction control

5. Embedded and dynamic SQL

6. Authorization

Aug 31, 2025 Dept of ICT

Introduction: Parts of SQL
1. Data-definition language (DDL) provides commands for defining relation schemas, deleting relations, and
modifying relation schemas

2. Data-manipulation language (DML) provides the ability to query information from the database and to insert tuples
into, delete tuples from, and modify tuples in the database.

3. Integrity includes commands for specifying integrity constraints that the data stored in the database must satisfy.

-Updates that violate integrity constraints are disallowed.

4. View definition includes commands for defining views

5. Transaction control includes commands for specifying the beginning and ending of transactions.

6. Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming
languages

7. Authorization includes commands for specifying access rights to relations and views.

Aug 31, 2025 Dept of ICT

Basic Types in SQL
• char(n): A fixed-length character string with user-specified length n. The full form, character, can be used

instead.

• varchar(n): A variable-length character string with user-specified maximum length n. The full form,

character varying, is equivalent

• int: An integer. The full form, integer, is equivalent

• smallint: A small integer

• numeric(p, d) (number(p, d)): A fixed-point number with user-specified precision

- number consists of p digits (plus a sign), and d of the p digits are to the right of the decimal point.

• float(n): A floating-point number, with precision of at least n digits

Aug 31, 2025 Dept of ICT

 SQL Commands

Aug 31, 2025 Dept. of I&CT 6

Basic Schema Definition - CREATE TABLE construct

create table R(
A1 D1,

A2 D2,
…….
An Dn,
<integrity constraint1>
<integrity constraint n> );

Aug 31, 2025 Dept of ICT

Aug 31, 2025 Dept. of I&CT 8
Basic Schema Definition - CREATE TABLE construct
create table R(
A1 Datatype,
A2 Datatype,
…….
An Datatype,
<integrity constraint1>
<integrity constraint n> );
create table instructor (
ID varchar( 5 ) ,
name varchar( 20 ) not null,
deptname varchar(20 ) ,
primary key(ID ),
foreign key (deptname) references
department ) ;
Aug 31, 2025 Dept of ICT
 For department budget has precision of 12 and 2
decimal places allowed and budget must be greater
than 0

For classroom primary key

is ( building, room
number)
 Basic Schema Definition - CREATE TABLE construct

create table classroom ( building varchar (15) ,

roomnumber char (5),
capacity numeric (4, 0) ,
primary key ( building, roomnumber ) ) ;

create table department (

deptname varchar( 20 ) primary key,
building varchar(15),
budget numeric(12, 2) check (budget > 0 ) ) ;
For classroom primary key is building,
roomnumber
department budget has precision of 12 and 2 decimal places allowed and budget must be greater than 0
Aug 31, 2025 Dept of ICT
Basic Schema Definition - CREATE TABLE construct
create table course(
courseid varchar(8),
title varchar(50),
deptname varchar (20),
credits numeric (2,0) check (credits > 0),
primary key (courseid),
constraint fkey1 foreign key (deptname) references
department on delete set null;
)
Aug 31, 2025 Dept of ICT
"Write an SQL statement to create a table named section for a university database.
The table should include the following:
•A composite primary key combining courseid, secid, semester, and year.
•Foreign keys:
•courseid referencing a course table with ON DELETE CASCADE.
•building and roomnumber referencing a classroom table with ON DELETE SET NULL.
•Columns:
•courseid as a VARCHAR(8).
•secid as a VARCHAR(8).
•semester as a VARCHAR(6) restricted to the values 'Fall', 'Winter', 'Spring', 'Summer'.
•year as a NUMERIC(4, 0), allowed only between 1702 and 2099.
•building as a VARCHAR(15).
•roomnumber as a VARCHAR(7).
•timeslotid as a VARCHAR(4).

Aug 31, 2025 Dept. of I&CT 14

Basic Schema Definition - CREATE TABLE construct
create table section(
courseid varchar (8),
secid varchar (8),
semester varchar (6) check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)),
year numeric (4,0) check (year > 1701 and year < 2100),
building varchar (15),
roomnumber varchar (7),
timeslotid varchar (4),
primary key (courseid, secid, semester, year),
foreign key (courseid) references course on delete cascade,
foreign key (building, roomnumber) references classroom on delete set null);

Aug 31, 2025 Dept of ICT

 Circular dependency

Department table Employee table

CREATE TABLE Department (
CREATE TABLE Employee (
Dept_ID INT PRIMARY KEY,
Employee_ID INT PRIMARY KEY,
Dept_Name VARCHAR(100) NOT NULL UNIQUE,
Name VARCHAR(100) NOT NULL,
Manager_ID INT,
Dept_Name VARCHAR(100),
FOREIGN KEY (Manager_ID) REFERENCES Employee(Employee_ID)
FOREIGN KEY (Dept_Name) REFERENCES
); Department(Dept_Name)

);

Aug 31, 2025 Dept. of I&CT 16

Circular dependency

• Create one table without its foreign key first.

• After creating the second table, add the foreign key constraint to the
first table using ALTER TABLE.

08/31/2025 17
 • Create one table without its foreign key first.

Circular dependency • Create the second table with foreign key.

• Add the foreign key constraint to the first table using ALTER TABLE.

Department table Employee table

CREATE TABLE Department (
CREATE TABLE Employee (
Dept_ID INT PRIMARY KEY,
Employee_ID INT PRIMARY KEY,
Dept_Name VARCHAR(100) NOT NULL UNIQUE,
Name VARCHAR(100) NOT NULL,
Manager_ID INT,
Dept_Name VARCHAR(100),
FOREIGN KEY (Manager_ID) REFERENCES Employee(Employee_ID)
FOREIGN KEY (Dept_Name) REFERENCES
); Department(Dept_Name)

);

ALTER TABLE Department ADD CONSTRAINT FK_ManagerID FOREIGN KEY (Manager_ID)

REFERENCES Employee(Employee_ID);

08/31/2025 18
 Modification to the Database

Aug 31, 2025 Dept. of I&CT 19

 Modification of DB - Insertion
• Add a new tuple to course table

• insert into course values ('CS−437' , 'DBS ' , 'Comp. Sci .' ,4 ) ;

• or equivalently

• insert into course ( courseid, title, deptname, credits ) values ( 'CS−437

', ' DBSystems ', ‘Comp. Sci .', 4 ) ;

• Add a new tuple to student with totcreds set to null

• insert into student values ( '3003 ’ ,’ Green ’ ,’ Finance ’ , null ) ;

Aug 31, 2025 Dept of ICT
Modification of DB – Delete Construct/Drop Table
• Deleting all the contents of the table
• delete from student;
• Deleting a specific content from the table
• delete from student where P;
• delete from student where deptname = ' ICT ' ;
• Deleting table
• drop table student;

Aug 31, 2025 Dept of ICT 21 / 63

Modification of DB - Updation
• Annual salary increases are being made, and salaries of all instructors are to be
increased by 5 percent
• update instructor set salary = salary * 1.05;
• If a salary increase is to be paid only to instructors with salary of less than ₹
70000
• update instructor
set salary = salary * 1.05
where salary < 70000;
• Increase salaries of instructors whose salary is over ₹100,000 by 3%, and all
others receive a 5% raise

Aug 31, 2025 Dept of ICT 22 / 63

Modification of DB - Updation

update instructor update instructor

set salary = salary * 1.03 set salary = salary * 1.05
where salary >= 100000; where salary < 100000;

update instructor update instructor

set salary = salary * 1.05 set salary = salary * 1.03
where salary < 100000; where salary >= 100000;
Test Case 1 Test Case 2

Aug 31, 2025 Dept of ICT 23 / 63

Modification of DB - Updation

• Same query as before but with case statement

update instructor
set salary = case
when salary >= 100000 then salary * 1.03
else salary * 1.05
end;

Aug 31, 2025 Dept of ICT 24 / 63

ALTER Table Construct

• Used to add or drop an attribute to/from a table

alter table instructor add age int;
• Delete the attribute
alter table instructor drop age ;
alter table instructor drop column age ;

Aug 31, 2025 Dept of ICT 25 / 63

ALTER Table Construct
• Adding a foreign key
alter table B
add foreign key ( name ) references A;

alter table B
add constraint fkname
foreign key ( name ) references A( name ) ;

alter table B
drop constraint fkname ;

create table B(
id number primary key,
name varchar(10),
constraint fkname foreign key ( name ) . . . ) ;
Aug 31, 2025 Dept of ICT 26 / 63
Basic Query Structure

• A typical SQL query has the following form:

select A1, A2, … An select ID, Name

from r1, r2, … rm from instructor
where P; where dept_name=‘Comp. Sci.;

• The result of an SQL query is a relation

Aug 31, 2025 Dept of ICT 27 / 63

 SQL Queries

Aug 31, 2025 Dept. of I&CT 29

Basic Query Structure

• A typical SQL query has the following form:

select A1, A2, … An select ID, Name

from r1, r2, … rm from instructor
where P; where dept_name=‘Comp. Sci.;

• The result of an SQL query is a relation

Aug 31, 2025 Dept of ICT 30 / 63

SELECT Clause
• Select clause lists the attributes desired in the result of a query.
 Find the names of all instructors:
select name
from instructor;
• SQL names are case insensitive
 Find the department names of all instructors,
select deptname
from instructor;
• SQL allows duplicates in relations as well as in query results

Aug 31, 2025 Dept of ICT 31 / 63

SELECT Clause
• To force the elimination of duplicates, insert the keyword distinct after select.
• Find the names of all departments with instructor, and remove duplicates

• The keyword all species that duplicates not be removed.

• An asterisk in the select clause denotes "all attributes"

• The select clause can contain arithmetic expressions involving the operation, +, -, *, and /,
and operating on constants or attributes of tuples.
• Return a relation that is same as the instructor relation, except that the value of the attribute salary is
multiplied by 1.1

Aug 31, 2025 Dept of ICT 32 / 63

SQL> select dept_name from SQL> select distinct dept_name from instructor;
instructor;
DEPT_NAME
DEPT_NAME --------------------
-------------------- Comp. Sci.
Comp. Sci. Finance
Finance Music
Music Physics
Physics History
History Biology
Physics Elec. Eng.
Comp. Sci.
History 7 rows selected.
Finance
Biology
Comp. Sci.

DEPT_NAME
--------------------
Elec. Eng.

12 rows selected.
Aug 31, 2025 Dept. of I&CT 33
 • select ID, name, dept name, salary * 1.1 from
instructor;
SQL> select * from instructor;

ID NAME DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci.
65000
12121 Wu Finance
90000
15151 Mozart Music
40000
22222 Einstein Physics
95000
32343 El Said History
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583Aug Califieri
31, 2025 History 62000
Dept. of I&CT 34
 • select ID, name, dept name, salary * 1.1 from
instructor;
SQL> select * from instructor; SQL> select ID, name, dept_name, salary * 1.1 from
instructor;
ID NAME DEPT_NAME
SALARY ID NAME DEPT_NAME SALARY*1.1
----- -------------------- -------------------- ---------- ----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci. 10101 Srinivasan Comp. Sci. 71500
65000 12121 Wu Finance 99000
12121 Wu Finance 15151 Mozart Music 44000
90000 22222 Einstein Physics 104500
15151 Mozart Music 32343 El Said History 66000
40000 33456 Gold Physics 95700
22222 Einstein Physics 45565 Katz Comp. Sci. 82500
95000 58583 Califieri History 68200
32343 El Said History 76543 Singh Finance 88000
60000 76766 Crick Biology 79200
33456 Gold Physics 83821 Brandt Comp. Sci. 101200
87000
45565 Katz Comp. Sci. ID NAME DEPT_NAME SALARY*1.1
75000 ----- -------------------- -------------------- ----------
58583 Califieri History 98345 Kim Elec. Eng. 88000
62000
76543Aug Singh31, 2025 Finance 12 rows
Dept. selected.
of I&CT 35
80000
SELECT Clause
• distinct

• all

•*

• Arithmetic Operation :, +, -, *, and /, and operating on constants or attributes of tuples.

Return a relation that is same as the instructor relation, except that the value of the attribute
salary is multiplied by 1.1

Aug 31, 2025 Dept of ICT 36 / 63

 Find all instructors in Comp. Sci. dept with salary greater than ₹80000
SQL> select * from instructor;

ID NAME DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci. select name
65000
12121 Wu Finance from instructor
90000
15151 Mozart Music
40000 where dept_name = 'Comp. Sci.'
22222 Einstein Physics
95000
32343 El Said History and salary>80000;
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543Aug Singh31, 2025 Finance Dept. of I&CT 37
80000
 Find all instructors in Comp. Sci. dept with salary greater than ₹80000
SQL> select * from instructor; select name

ID NAME DEPT_NAME from instructor

SALARY where dept_name = 'Comp. Sci.' and salary>80000;
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci.
65000
12121 Wu Finance SQL> select name from instructor where
90000
dept_name = 'Comp. Sci.' and salary>80000;
15151 Mozart Music
40000
22222 Einstein Physics NAME
95000 --------------------
32343 El Said History Brandt
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543Aug Singh31, 2025 Finance Dept. of I&CT 38
80000
 Find all instructors in Comp. Sci. dept with salary greater than ₹80000
SQL> select * from instructor;
select name
ID NAME DEPT_NAME from instructor
SALARY
----- -------------------- -------------------- ---------- where dept_name != 'Comp. Sci.' and salary>80000;
10101 Srinivasan Comp. Sci.
65000
12121 Wu Finance
90000
15151 Mozart Music
40000
22222 Einstein Physics
95000
32343 El Said History
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543Aug Singh31, 2025 Finance Dept. of I&CT 39
80000
 Find all instructors in Comp. Sci. dept with salary greater than ₹80000
SQL> select * from instructor;
select name
ID NAME DEPT_NAME from instructor
SALARY
----- -------------------- -------------------- ---------- where dept_name != 'Comp. Sci.' and salary>80000;
10101 Srinivasan Comp. Sci.
65000
12121 Wu Finance
90000 SQL> select name
15151 Mozart Music 2 from instructor
40000 3 where dept_name != 'Comp. Sci.' and
22222 Einstein Physics salary>80000;
95000
32343 El Said History NAME
60000
--------------------
33456 Gold Physics
87000 Wu
45565 Katz Comp. Sci. Einstein
75000 Gold
58583 Califieri History
62000
76543Aug Singh
31, 2025 Finance Dept. of I&CT 40
80000
Arithmetic operators in where clause
SQL> SELECT ID, Name, Salary
2 FROM Instructor
3 WHERE mod(Salary,5000)=0;

ID NAME SALARY
----- -------------------- ----------
10101 Srinivasan 65000
12121 Wu 90000
Which instructors are fetched from
15151 Mozart 40000
the table?
22222 Einstein 95000
32343 El Said 60000
45565 Katz 75000
76543 Singh 80000
98345 Kim 80000

8 rows selected.

Aug 31, 2025 Dept. of I&CT 41

Arithmetic operators in where clause
• Find instructors whose salary is exactly divisible by 5000:
SQL> SELECT ID, Name, Salary
2 FROM Instructor
3 WHERE mod(Salary,5000)=0;

ID NAME SALARY
----- -------------------- ----------
10101 Srinivasan 65000
12121 Wu 90000
15151 Mozart 40000
22222 Einstein 95000
32343 El Said 60000
45565 Katz 75000
76543 Singh 80000
98345 Kim 80000

Aug 31, 2025 8 rows selected.Dept. of I&CT 42

SQL> select id, name, salary from instructor where
salary*1.05>90000;

ID NAME SALARY
----- -------------------- ----------
12121 Wu 90000
22222 Einstein 95000
33456 Gold 87000
83821 Brandt 92000

Aug 31, 2025 Dept. of I&CT 43

WHERE Clause

• The where clause specifies conditions that the result must satisfy
• Comparison results can be combined using the logical connectives and, or, and
not.

• Comparisons can be applied to results of arithmetic expressions in

where clause

Aug 31, 2025 Dept of ICT 44 / 63

Queries on Multiple Relations
 Retrieve the names of all instructors, along with their department names and department
building name

Aug 31, 2025 Dept of ICT 45 / 63

Queries on Multiple Relations Retrieve the names of all
SQL> select * from instructor; instructors, along with their
department names and
ID NAME DEPT_NAME
SALARY
department building name
SQL> select * from department;
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci. DEPT_NAME BUILDING
65000 BUDGET
12121 Wu Finance -------------------- --------------- ----------
90000 Elec. Eng. Taylor
15151 Mozart Music 85000
40000 Finance Painter
22222 Einstein Physics 120000
95000 Biology Watson
32343 El Said History 90000
60000 Comp. Sci. Taylor
33456 Gold Physics 100000
87000 History Painter
45565 Katz Comp. Sci. 50000
75000 How
Music many rows must Packardbe there
58583 Califieri History in 80000
62000 Physics
resultant relation Watson
of the query?
76543 AugSingh
31, 2025 Finance Dept of ICT 70000 46 / 63
SQL> select name, department.dept_name, building from
instructor, department;

NAME DEPT_NAME NAME DEPT_NAME BUILDING

BUILDING -------------------- -------------------- ---------------
-------------------- -------------------- --------------- Kim Elec. Eng. Taylor
Srinivasan Elec. Eng. Taylor Srinivasan Finance Painter
Wu Elec. Eng. Taylor Wu Finance Painter
Mozart Elec. Eng. Taylor Mozart Finance Painter
Einstein Elec. Eng. Taylor Einstein Finance Painter
El Said Elec. Eng. Taylor El Said Finance Painter
Gold Elec. Eng. Taylor Gold Finance Painter
Katz Elec. Eng. Taylor Katz Finance Painter
Califieri Elec. Eng. Taylor Califieri Finance Painter
Singh Elec. Eng. Taylor Singh Finance Painter
Crick Elec. Eng. Taylor Crick Finance Painter
Brandt Elec. Eng. Taylor
….
Aug 31, 2025 Dept. of I&CT 47
NAME DEPT_NAME BUILDING NAME DEPT_NAME BUILDING
-------------------- -------------------- --------------- -------------------- -------------------- ---------------
Brandt Finance Painter Crick Biology Watson
Kim Finance Painter Brandt Biology Watson
Srinivasan Biology Watson Kim Biology Watson
Wu Biology Watson Srinivasan Comp. Sci. Taylor
Mozart Biology Watson Wu Comp. Sci. Taylor
Einstein Biology Watson Mozart Comp. Sci. Taylor
El Said Biology Watson Einstein Comp. Sci. Taylor
Gold Biology Watson El Said Comp. Sci. Taylor
Katz Biology Watson Gold Comp. Sci. Taylor
Califieri Biology Watson Katz Comp. Sci. Taylor
Singh Biology Watson Califieri Comp. Sci. Taylor

NAME DEPT_NAME BUILDING

-------------------- -------------------- ---------------
Gold Physics Watson
Katz Physics Watson
Califieri Physics Watson
Singh Physics Watson
Crick Physics Watson
Brandt Physics Watson
Kim Physics Watson

84 rows selected.
Aug 31, 2025 Dept. of I&CT 48
SQL> select name, department.dept_name, building from
instructor, department where instructor.dept_name
=department.dept_name;
NAME DEPT_NAME BUILDING
-------------------- -------------------- ---------------
Srinivasan Comp. Sci. Taylor
Wu Finance Painter
Mozart Music Packard
Einstein Physics Watson
El Said History Painter
Gold Physics Watson
Katz Comp. Sci. Taylor
Califieri History Painter
Singh Finance Painter
Crick Biology Watson
Brandt Comp. Sci. Taylor

NAME DEPT_NAME BUILDING

-------------------- -------------------- ---------------
Kim Elec. Eng. Taylor

12 rows selected.
Aug 31, 2025 Dept. of I&CT 49
SQL> select name, dept_name, building from instructor
natural join department;
NAME DEPT_NAME BUILDING
-------------------- -------------------- ---------------
Srinivasan Comp. Sci. Taylor
Wu Finance Painter
Mozart Music Packard
Einstein Physics Watson
El Said History Painter
Gold Physics Watson
Katz Comp. Sci. Taylor
Califieri History Painter
Singh Finance Painter
Crick Biology Watson
Brandt Comp. Sci. Taylor

NAME DEPT_NAME BUILDING

-------------------- -------------------- ---------------
Kim Elec. Eng. Taylor

12 rows selected.

Aug 31, 2025 Dept. of I&CT 50

Queries on Multiple Relations
 Retrieve the names of all instructors, along with their department names and department
building name
• The role of each clause is as follows:
 The select clause is used to list the attributes desired in the result of a query.
 The from clause is a list of the relations to be accessed in the evaluation of the query.
 The where clause is a predicate involving attributes of the relation in the from clause

• Operational order: first from, then where, and then select

• The from clause by itself defines a Cartesian product of the relations listed in the
clause.

Aug 31, 2025 Dept of ICT 51 / 63

Queries on Multiple Relations

select * from instructor, department;

Displays the Cartesian product of every tuple in the relations

select *

from instructor, department

where instructor.deptname = department.deptname;

• This displays the valid details of the instructors only once.

Aug 31, 2025 Dept of ICT 52 / 63

Natural Join
• Natural join matches tuples with the same values for all common attributes, and retains only one
copy of each common column

Aug 31, 2025 Dept of ICT 53 / 63

Natural Join
select A1, A2, . . . , An
from r1 natural join r2 natural join . . . natural join rm
where P;

List the names of instructors along with the course ID of the courses that they taught.

select name, courseid

from instructor, teaches
where instructor.id = teaches.id;

select name, courseid

from instructor natural join teaches;
Aug 31, 2025 Dept of ICT 54 / 63
Queries on Multiple Relations
department(deptname, building, budget)

course(courseid, title, deptname, credits)

instructor(ID, name, deptname, salary)

section(courseid, sectionid, sem, year, building, roomnumber, timeslotid)

teaches(ID, courseid, secid, semester, year)

 For all instructors in the university who have taught some course, find their names and the course ID of
all courses they taught

 Find instructor names and course identifiers for instructors in the Computer Science department

 Find the course ID, semester, year and title of each course offered by the Comp. Sci. department
Aug 31, 2025 Dept of ICT 55 / 63
Natural Join department(deptname, building, budget)
course(courseid, title, deptname, credits)
instructor(ID, name, deptname, salary)
section(courseid, sectionid, sem, year, building,
roomnumber, timeslotid)
teaches(ID, courseid, secid, semester, year)

 List the names of instructors along with the titles of courses that they
teach

Aug 31, 2025 Dept of ICT 56 / 63

Natural Join department(deptname, building, budget)
course(courseid, title, deptname, credits)
instructor(ID, name, deptname, salary)
section(courseid, sectionid, sem, year, building,
roomnumber, timeslotid)
teaches(ID, courseid, secid, semester, year)

 List the names of instructors along with the titles of courses that they teach
select name, title
from instructor natural join teaches natural join course;

• Is the query correct?

• Will this work if there is an instructor who teaches a course that belongs to another
department?

Aug 31, 2025 Dept of ICT 57 / 63

SQL> select * from teaches; SQL> select course_id, title from course;

ID COURSE_I SEC_ID SEMEST COURSE_I TITLE

YEAR -------- --------------------------------------------------
----- -------- -------- ------ ---------- BIO-101 Intro. to Biology
10101 CS-101 1 Fall 2009 BIO-301 Genetics
10101 CS-315 1 Spring 2010 BIO-399 Computational Biology
10101 CS-347 1 Fall 2009 CS-101 Intro. to Computer Science
12121 FIN-201 1 Spring 2010 CS-190 Game Design
15151 MU-199 1 Spring 2010 CS-315 Robotics
22222 PHY-101 1 Fall 2009 CS-319 Image Processing
32343 HIS-351 1 Spring 2010 CS-347 Database System Concepts
45565 CS-101 1 Spring 2010 EE-181 Intro. to Digital Systems
45565 CS-319 1 Spring 2010 FIN-201 Investment Banking
76766 BIO-101 1 Summer 2009 HIS-351 World History
76766 BIO-301 1 Summer 2010
COURSE_I TITLE
ID COURSE_I SEC_ID SEMEST -------- --------------------------------------------------
YEAR MU-199 Music Video Production
----- -------- -------- ------ ---------- PHY-101 Physical Principles
83821 CS-190 1 Spring 2009
83821 CS-190 2 Spring 2009 13 rows selected.
98345 EE-181 1 Spring 2009
76766 Aug CS-190
31, 2025 1 Spring 2009 Dept. of I&CT 58
 SQL> select * from instructor;

ID NAME DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci.
65000
12121 Wu Finance
90000
15151 Mozart Music
40000
22222 Einstein Physics
95000
32343 El Said History
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543 Singh Finance
Aug 31, 2025 Dept. of I&CT 59
80000
SQL>select name, title from instructor natural join teaches
natural join course;
NAME TITLE
-------------------- --------------------------------------------------
Crick Intro. to Biology
Crick Genetics
Srinivasan Intro. to Computer Science
Katz Intro. to Computer Science
Brandt Game Design
Brandt Game Design
Srinivasan Robotics
Katz Image Processing
Srinivasan Database System Concepts
Kim Intro. to Digital Systems
Wu Investment Banking

NAME TITLE
-------------------- --------------------------------------------------
El Said World History
Mozart Music Video Production
Einstein Physical Principles

14 rows
Augselected.
31, 2025 Dept. of I&CT 60
Natural Join

 List the names of instructors along with the titles of courses that they teach
select name, title
from (instructor natural join teaches) join course using (courseid);

Aug 31, 2025 Dept of ICT 61 / 63

SQL>select name, title from (instructor natural join teaches)
join course using (course_id);
NAME TITLE
-------------------- --------------------------------------------------
Crick Intro. to Biology
Crick Genetics
Srinivasan Intro. to Computer Science
Katz Intro. to Computer Science
Brandt Game Design
Brandt Game Design
Crick Game Design
Srinivasan Robotics
Katz Image Processing
Srinivasan Database System Concepts
Kim Intro. to Digital Systems

NAME TITLE
-------------------- --------------------------------------------------
Wu Investment Banking
El Said World History
Mozart Music Video Production
Einstein Physical Principles
15 rows selected.
Aug 31, 2025 Dept. of I&CT 62
SQL>select name, title from instructor, teaches,course
where instructor.ID=teaches.ID and
teaches.course_id=course.course_id;

NAME TITLE
-------------------- --------------------------------------------------
Crick Intro. to Biology
Crick Genetics
Srinivasan Intro. to Computer Science
Katz Intro. to Computer Science
Brandt Game Design
NAME TITLE
Brandt Game Design
-------------------- --------------------------------------------------
Crick Game Design
Wu Investment Banking
Srinivasan Robotics
El Said World History
Katz Image Processing
Mozart Music Video Production
Srinivasan Database System Concepts
Einstein Physical Principles
Kim Intro. to Digital Systems
15 rows selected.

Aug 31, 2025 Dept. of I&CT 63

SQL>select name, title from instructor i, teaches t,
course c where i.ID=t.ID and t.course_id=c.course_id;
NAME TITLE
-------------------- --------------------------------------------------
Crick Intro. to Biology
Crick Genetics
Srinivasan
Katz
Intro. to Computer Science
Intro. to Computer Science Renaming tables
Brandt Game Design
Brandt
Crick
Game Design
Game Design
and attributes
Srinivasan Robotics
Katz Image Processing
Srinivasan Database System Concepts
Kim Intro. to Digital Systems

NAME TITLE
-------------------- --------------------------------------------------
Wu Investment Banking
El Said World History
Mozart Music Video Production
Einstein Physical Principles

15 rows selected.
Aug 31, 2025 Dept. of I&CT 64
Rename Operation – as clause
• The SQL allows renaming relations and attributes using the as clause
 For all instructors in the university who have taught some course, find their names and the
course ID of all courses they taught
select name, courseid
from instructor i, teaches t
where i.id = t.id;
• Another usage of rename operation is a case where we wish to compare tuples in the same
relation.
 Find the names of all instructors who have a higher salary than at least one instructor in
'Comp. Sci'.
• Also known as Correlation name, correlation variable, tuple variable, table alias
Aug 31, 2025 Dept of ICT 65 / 63
SQL> select i.name as instructor_name from instructor i
where i.dept_name = 'Finance';

NAME
--------------------
Wu
Singh

Aug 31, 2025 Dept. of I&CT 66

SQL> SELECT DISTINCT i1.name FROM instructor i1 WHERE
i1.salary > ANY (SELECT i2.salary FROM instructor i2 WHERE
i2.dept_name = 'Biology');
NAME
--------------------
Wu
Einstein
Gold
Katz
Singh
Brandt
Kim

7 rows selected.
 String Operations

Aug 31, 2025 Dept. of I&CT 68

SQL> select name from SQL> select name from instructor
instructor; where name like '%ri%';
NAME
--------------------
Srinivasan
Wu
Mozart
Einstein
El Said
Gold
Katz
Califieri
Singh
Crick
Brandt

NAME
--------------------
Kim

12 rows selected.
Aug 31, 2025 Dept. of I&CT 69
SQL> select name from SQL> select name from instructor
instructor; where name like '%ri%';
NAME NAME
-------------------- --------------------
Srinivasan Srinivasan
Wu Califieri
Mozart Crick
Einstein
El Said
Gold
Katz
Califieri
Singh
Crick percent (%): The %
Brandt
character matches any
NAME
--------------------
substring
Kim

12 rows selected.
Aug 31, 2025 Dept. of I&CT 70
SQL> select name from SQL> select name from instructor
instructor; where name like ‘_ric_';
NAME
--------------------
Srinivasan
Wu
Mozart
Einstein
El Said
Gold
Katz
Califieri
Singh
Crick
Brandt

NAME
--------------------
Kim

12 rows selected.
Aug 31, 2025 Dept. of I&CT 71
SQL> select name from SQL> select name from instructor
instructor; where name like ‘_ric_';
NAME NAME
-------------------- --------------------
Srinivasan Crick
Wu
Mozart
Einstein
El Said
Gold
Katz underscore ( _ ): The _ character
Califieri
Singh matches any character
Crick
Brandt

NAME
--------------------
Kim

12 rows selected.
Aug 31, 2025 Dept. of I&CT 72
String operation

• Strings are specified in single quotes. Ex: 'Computer'

• SQL includes a string-matching operator for comparisons on character strings. The
operator "like" uses patterns that are described using two special characters
 percent (%): The % character matches any substring
 underscore ( _ ): The _ character matches any character
 Find the names of all instructors whose name includes the substring "dar"

select name
from instructor
where name like ‘%dar%’;

Aug 31, 2025 Dept of ICT 73 / 63

String Operations

• Match the string "100 %"

like ‘100\% ' escape ‘ \ ' )

• like ‘ab\%cd%' escape ‘\’

matches all strings beginning with "ab%cd".

• like ‘ab\\cd%' escape ‘\’

matches all strings beginning with "ab\cd".

• like ‘%ab\%cd' escape ‘\’

Aug 31, 2025 Dept of ICT 74 / 63

 Functions on character strings

• select first_name || ' ' || last_name as full_namefrom

instructor;
• Concatenating (using “||”)

• select substr(name, 1, 3) as first_three_letters from

instructor;
• Extracting substrings

• select length(name) as name_length from instructor;

• select upper(name) as name_uppercase

Aug 31, 2025 Dept. of I&CT from instructor; 75
Functions on character strings
• Concatenating (using “||”),
• Extracting substrings,
• Finding the length of strings,
• Converting strings to uppercase (using the function
upper(s) where s is a string) and lowercase
• (using the function lower(s)),
• Removing spaces at the end of the string (using
trim(s)) ….

Aug 31, 2025 Dept. of I&CT 76

 Ordering the Display of
Results

Aug 31, 2025 Dept. of I&CT 77

 SQL> select * from instructor order by name
desc;

Aug 31, 2025 Dept. of I&CT 78

SQL> select * from instructor order by name
desc;

ID NAME DEPT_NAME
SALARY
----- -------------------- -------------------- ---------- The order by clause causes
12121 Wu Finance 90000
10101 Srinivasan Comp. Sci. the tuples in the result of a
65000
76543 Singh Finance 80000 query to appear in sorted
15151 Mozart Music 40000
98345 Kim Elec. Eng. 80000 order.
45565 Katz Comp. Sci. 75000 Specify desc for descending
33456 Gold Physics 87000
32343 El Said History 60000 order or asc for ascending
22222 Einstein Physics 95000
76766 Crick Biology 72000 order, for each attribute.
58583 Califieri History 62000

ID NAME DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
83821
Aug 31, Brandt
2025 Comp. Sci. Dept.92000
of I&CT 79
• List all the instructors who work in Physics department in ascending and
descending order.

Aug 31, 2025 Dept of ICT 80 / 63

WHERE Clause Predicate: Between -
And

SQL> select name from instructor where salary

between 90000 and 100000;

NAME
--------------------
Wu
Einstein
Brandt

Aug 31, 2025 Dept. of I&CT 81

WHERE Clause Predicate: Between -
And
Find the names of instructors with salary amounts between ₹90,000 and
₹100,000
SQL> select name from instructor where salary
between 90000 and 100000;

NAME
--------------------
Wu
Einstein
Brandt

Aug 31, 2025 Dept. of I&CT 82

Aug 31, 2025 Dept. of I&CT 83
Aug 31, 2025 Dept. of I&CT 84
 Cust_ID Cust_Name Order_ID Amount Cust_ID
Naveen 1 200 5
1
2 500 3
2 Ramya 3 300 1
3 Akash
4 Aashay
5 Geetha

Aug 31, 2025 Dept. of I&CT 85

 Cust_ID Cust_Name Order_ID Amount Cust_ID
Naveen 1 200 5
1
2 500 3
2 Ramya 3 300 1
3 Akash
4 Aashay
5 Geetha

1 Naveen 300
3 Akash 500
5 Geetha 200

Aug 31, 2025 Dept. of I&CT 86

 Aggregate Functions

Aug 31, 2025 Dept. of I&CT 87

Aggregate Functions

• Aggregate functions are functions that take a collection (a set or multiset) of values as input
and return a single value
• SQL offers five built-in aggregate functions
 Average: avg
 Minimum: min
 Maximum: max
 Total: sum
 Count: count
• Input to sum and avg must be a collection of numbers, but the other operators can operate on
collections of nonnumeric data types, such as strings, as well.
Aug 31, 2025 Dept of ICT 88 / 63
SQL> select * from instructor; SQL> select max(salary) from instructor;

ID NAME DEPT_NAME MAX(SALARY)

SALARY -----------
----- -------------------- -------------------- ---------- 95000
10101 Srinivasan Comp. Sci.
65000
SQL> select name, max(salary) from
12121 Wu Finance
90000
instructor;
15151 Mozart Music select name, max(salary) from instructor
40000 *
22222 Einstein Physics ERROR at line 1:
95000 ORA-00937: not a single-group group function
32343 El Said History
60000
33456 Gold Physics SQL> select name, salary from instructor i1
87000
where salary = (select max(i2.salary) from
45565 Katz Comp. Sci.
75000
instructor i2);
58583 Califieri History
62000 NAME SALARY
76543 Singh Finance -------------------- ----------
80000 Aug 31, 2025 Einstein
Dept. of I&CT 95000 89
SQL> select * from instructor; SQL> select min(salary) from instructor;

ID NAME DEPT_NAME MIN(SALARY)

SALARY -----------
----- -------------------- -------------------- ---------- 40000
10101 Srinivasan Comp. Sci.
65000 SQL> select count(Salary) from instructor;
12121 Wu Finance
90000 COUNT(SALARY)
15151 Mozart Music -------------
40000 12
22222 Einstein Physics
95000 SQL> select avg(Salary) from instructor;
32343 El Said History
60000 AVG(SALARY)
33456 Gold Physics -----------
87000 74833.3333
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543 Singh Finance
80000Aug 31, 2025 Dept. of I&CT 90
76766 Crick Biology
SQL> select * from instructor;
SQL> select avg(Salary) from instructor;
ID NAME DEPT_NAME
SALARY AVG(SALARY)
----- -------------------- -------------------- ---------- -----------
10101 Srinivasan Comp. Sci. 74833.3333
65000
12121 Wu Finance
SQL> select i1.name from instructor i1
90000
15151 Mozart Music where i1.salary>(select avg(i2.Salary) from
40000 instructor i2)
22222 Einstein Physics
95000
32343 El Said History
60000 select i1.name
33456 Gold Physics
87000 from instructor i1
45565 Katz Comp. Sci.
75000 where i1.salary
58583 Califieri History
62000 >(select avg(i2.Salary) from instructor
76543 Singh Finance
80000Aug 31, 2025 i2)
Dept. of I&CT 91
76766 Crick Biology
SQL> select * from instructor;
SQL> select avg(Salary) from instructor;
ID NAME DEPT_NAME
SALARY
AVG(SALARY)
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci.
-----------
65000 74833.3333
12121 Wu Finance
90000 SQL> select i1.name from instructor i1
15151 Mozart Music where i1.salary>(select avg(i2.Salary) from
40000 instructor i2)
22222 Einstein Physics
95000 NAME
32343 El Said History
--------------------
60000
33456 Gold Physics
Wu
87000 Einstein
45565 Katz Comp. Sci. Gold
75000 Katz
58583 Califieri History Singh
62000 Brandt
76543 Singh Finance Kim
80000
76766Aug 31, 2025
Crick Biology Dept. of I&CT 92
7 rows selected.
Aggregate Functions

 Find the average salary of instructors in the Computer Science department.

 Find the total number of instructors who teach a course in the Spring 2020
semester
 Find the number of tuples in the course relation

Aug 31, 2025 Dept of ICT 93 / 63

 Aggregate Functions

There are circumstances where we would like to apply the aggregate function
not only to a single set of tuples, but also to a group of sets of tuples - group
by clause

group by
clause

Aug 31, 2025 Dept of ICT 94 / 63

Aggregate Functions • Find the average salary of instructors in each department
SQL> select * from instructor order by
dept_name asc;

ID NAME DEPT_NAME SALARY

----- -------------------- -------------------- ----------
76766 Crick Biology 72000
83821 Brandt Comp. Sci.
92000
10101 Srinivasan Comp. Sci.
65000
45565 Katz Comp. Sci.
75000
98345 Kim Elec. Eng.
80000
12121 Wu Finance
90000
76543 Singh Finance
80000
58583 Califieri History 62000
32343 El Said History 60000
15151 Mozart
Aug 31, 2025
Music Dept of ICT 95 / 63
40000
Aggregate Functions • Find the average salary of instructors in each department
SQL> select * from instructor order by
dept_name asc;
SQL> select dept_name,avg(Salary) from
ID NAME DEPT_NAME SALARY
instructor group by dept_name;
----- -------------------- -------------------- ----------
76766 Crick Biology 72000 DEPT_NAME AVG(SALARY)
83821 Brandt Comp. Sci. -------------------- -----------
92000 Comp. Sci. 77333.3333
10101 Srinivasan Comp. Sci. Finance 85000
65000 Music 40000
45565 Katz Comp. Sci. Physics 91000
75000 History 61000
98345 Kim Elec. Eng. Biology 72000
80000 Elec. Eng. 80000
12121 Wu Finance
90000
76543 Singh Finance
80000
58583 Califieri History 62000
32343 El Said History 60000
15151 Mozart
Aug 31, 2025
Music Dept of ICT 96 / 63
40000
Aggregate Functions
• Find the number of instructors in each department who teach a course in the Fall 2009 semester

Aug 31, 2025 Dept of ICT 97 / 63

Aggregate Functions

• Find the number of instructors in each department who teach a course in the Fall 2009 semester

SQL> select i.dept_name, count(t.id) from teaches t, instructor i where

t.ID=i.ID and semester = 'Fall' and year = '2009' group by i.dept_name;
DEPT_NAME COUNT(T.ID)
-------------------- -----------
Comp. Sci. 2
Physics 1

Aug 31, 2025 Dept of ICT 98 / 63

Aggregate Functions

• Any attribute that is not present in the group by clause must appear only inside an
aggregate function if it appears in the select clause, otherwise the query is treated as
erroneous
/* erroneous query */
select deptname, name, count (distinct id)
from instructor natural join teaches
where semester = ' Spring ' and year = 2020
group by deptname;

Aug 31, 2025 Dept of ICT 99 / 63

Aggregate Functions
• At times, it is useful to state a condition that applies to groups rather than to tuples -
having clause

Having clause
 Find departments where the average salary of the instructors is more than ₹42,000

Aug 31, 2025 Dept of ICT 100 / 63

Aggregate Functions
• At times, it is useful to state a condition that applies to groups rather than to tuples -
having clause
 Find departments where the average salary of the instructors is more than ₹42,000
SQL> select dept_name, avg(salary) from instructor group by
dept_name having avg(salary)>42000;

DEPT_NAME AVG(SALARY)
-------------------- -----------
Comp. Sci. 77333.3333
Finance 85000
Physics 91000
History 61000
Biology 72000
Elec. Eng. 80000

6 rows selected.
Aug 31, 2025 Dept of ICT 101 / 63
Aggregate Functions

• Find departments where the average salary of the instructors is more than ₹42,000

SQL> select dept_name, avg(salary) from instructor group

by dept_name having avg(salary)>42000 order by
dept_name;
DEPT_NAME AVG(SALARY)
-------------------- -----------
Biology 72000
Comp. Sci. 77333.3333
Elec. Eng. 80000
Finance 85000
History 61000
Physics 91000
Aug 31, 2025 Dept of ICT 102 / 63
6 rows selected.
Aggregate Functions

• Any attribute that is present in the having clause without being aggregated
must appear in the group by clause, otherwise the query is treated as
erroneous.
• Order of execution: from, where, group by, having, order by, select

Aug 31, 2025 Dept of ICT 103 / 63

Aggregate Functions
For each course-section offered in 2023, find the average total credits
(totcred) of all students enrolled in the section, if the section had at least
2 students.

Aug 31, 2025 Dept of ICT 104 / 63

Aggregate Functions
For each course section offered in 2023, find the average total credits
(totcred) of all students enrolled in the section, if the section had at least 2
students.

select courseid, semester, year, secid, avg (totcred)

from takes natural join student
where year = 2023
group by courseid, semester, year, secid
having count (ID) >= 2 ;

Aug 31, 2025 Dept of ICT 105 / 63

Aggregation with NULL and Boolean values
• Null values, when they exist, complicate the processing of aggregate
operators
select sum (salary)
from instructor;
• All aggregate functions except count (*) ignore null values in their input
collection

Aug 31, 2025 Dept of ICT 106 / 63

 SET Operations

Aug 31, 2025 Dept. of I&CT 107

Aug 31, 2025 Dept. of I&CT 108
Set Operations: Operate on relations and correspond to the mathematical set-theory operations

• Union
• Intersect
• Except (Minus)
 Find the set of all courses offered in the Fall 2009 semester

select courseid from section where semester = ' Fall ' and year = 2009;
 Find the set of all courses offered in the Spring 2009 semester

select courseid from section where semester = ' Spring ' and year = 2009;

Aug 31, 2025 Dept of ICT 109 / 63

SQL> select course_id from section where semester = 'Fall' and year = 2009;

COURSE_I
--------
CS-101
CS-347
PHY-101
SQL> select course_id from section where semester = 'Spring' and year = 2009;
COURSE_I
--------
CS-190
CS-190
EE-181
SQL> select course_id from section where semester = 'Spring' and year = 2009 union
select course_id from section where semester = 'Fall' and year = 2009;

Aug 31, 2025 Dept. of I&CT 110

SQL> select course_id from section where semester = 'Fall' and year = 2009;

COURSE_I
--------
CS-101
CS-347
PHY-101
SQL> select course_id from section where semester = 'Spring' and year = 2009;
COURSE_I
--------
CS-190
CS-190
EE-181
SQL> select course_id from section where semester = 'Spring' and year = 2009 union
select course_id from section where semester = 'Fall' and year = 2009;
COURSE_I
--------
CS-190
EE-181
CS-101
CS-347
Aug 31, 2025 Dept. of I&CT 111
PHY-101
Set Operations
 Find the set of all courses offered either in Fall 2023 or in Spring 2022 or both
select courseid
from section
where semester = 'Fall' and year = 2023
union
select courseid
from section
where semester = 'Spring' and year = 2022;
• The union operation automatically eliminates duplicates, unlike the select clause.
• If the duplicates are needed, then union all is to be used.

Aug 31, 2025 Dept of ICT 112 / 63

Set Operations
 Find courses that were offered in Spring 2010 as well as in Fall 2009 - (Intersect)
 Find courses that ran in Spring 2010 but not in Fall 2009 - (except or minus)

Aug 31, 2025 Dept of ICT 113 / 63

SQL> select course_id from section where SQL> select course_id from section where
semester = 'Spring' and year = 2010; semester = 'Fall' and year = 2009;

COURSE_I COURSE_I
-------- --------
CS-101 CS-101
CS-315 CS-347
CS-319 PHY-101
CS-319
FIN-201
HIS-351
MU-199
SQL> select course_id from section where semester = 'Spring' and year = 2010
intersect
select course_id from section where semester = 'Fall' and year = 2009;

COURSE_I
--------
CS-101

Aug 31, 2025 Dept. of I&CT 114

SQL> select course_id from section where SQL> select course_id from section where
semester = 'Spring' and year = 2010; semester = 'Fall' and year = 2009;

COURSE_I COURSE_I
-------- --------
CS-101 CS-101
CS-315 CS-347
CS-319 PHY-101
CS-319
FIN-201
HIS-351
MU-199
SQL> select course_id from section where semester = 'Spring' and year = 2010
minus select course_id from section where semester = 'Fall' and year = 2009;

COURSE_I
--------
CS-315
CS-319
FIN-201
HIS-351
MU-199
Aug 31, 2025 Dept. of I&CT 115
 NULL VALUES

Aug 31, 2025 Dept. of I&CT 116

• Null signifies an unknown value or that a value does not exist

• and: The result of • true and unknown is unknown

• false and unknown is false,
while
• unknown and unknown is
unknown
• or: The result of • true or unknown is true
• false or unknown is unknown, while

• unknown or unknown is unknown.

Find all instructors who appear in the instructor relation with null values for salary.
• is null, is not null

Aug 31, 2025 Dept of ICT 117 / 63

 id name department salary bonus
1 Alice HR 50000 5000
2 Bob IT 60000 NULL
3 Carol Finance NULL 7000
4 Dave IT NULL NULL

SQL> SELECT name,

CASE
WHEN salary >= 50000 AND bonus >= 5000 THEN 'Eligible’
ELSE 'Not Eligible’
END AS eligibility_status
FROM employees;

Aug 31, 2025 Dept. of I&CT 118

 id name department salary bonus
1 Alice HR 50000 5000
2 Bob IT 60000 NULL
3 Carol Finance NULL 7000
4 Dave IT NULL NULL

SQL> SELECT
2 name,
3 CASE
4 WHEN salary >= 50000 AND bonus >= 5000 THEN 'Eligible’
5 ELSE 'Not Eligible’
6 END AS eligibility_status
7 FROM employees;

NAME ELIGIBILITY_
-------------------------------------------------- ------------
Alice Eligible
Bob Not Eligible
Carol Not Eligible
Dave Not Eligible

Aug 31, 2025 Dept. of I&CT 119

 Nested Subquery

Aug 31, 2025 Dept. of I&CT 120

Nested Subquery

• A subquery is a select-from-where expression that is nested within another

query

• To perform tests for set membership, set comparisons, and set cardinality

• Set membership is checked using in and not in constructs

Aug 31, 2025 Dept of ICT 121 / 63

Set Membership – IN, NOT IN

• The in connective tests for set membership, where the set is

a collection of values produced by a select clause.
• The not in connective tests for the absence of set
membership.

Aug 31, 2025 Dept of ICT 122 / 63

 department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
SET Membership instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

• Find students who have not taken any course yet using set
membership function

Aug 31, 2025 Dept. of I&CT 123

 department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
SET Membership instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

• Find students who have not taken any course yet using
set membership function
select name from student where id not in (select id
from takes);

Aug 31, 2025 Dept. of I&CT 124

SQL> SELECT course_id FROM teaches SQL> SELECT course_id FROM teaches WHERE
WHERE semester = 'Fall' AND year = semester = 'Spring' AND year = 2010
2009 ;
COURSE_I
COURSE_I --------
-------- CS-315
CS-101 FIN-201
CS-347 MU-199
PHY-101 HIS-351
CS-101
CS-319

SQL> SELECT course_id FROM teaches WHERE semester = 'Fall' AND year =
2009 AND course_id IN (SELECT course_id FROM teaches WHERE semester =
'Spring' AND year = 2010 );

Aug 31, 2025 Dept. of I&CT 125

SQL> SELECT course_id FROM teaches SQL> SELECT course_id FROM teaches WHERE
WHERE semester = 'Fall' AND year = semester = 'Spring' AND year = 2010
2009 ;
COURSE_I
COURSE_I --------
-------- CS-315
CS-101 FIN-201
CS-347 MU-199
PHY-101 HIS-351
CS-101
CS-319

SQL> SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2009 AND
course_id
IN (SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2010 );

COURSE_I
--------
CS-101

Aug 31, 2025 Dept. of I&CT 126

SQL> SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2009 AND
course_id
NOT IN ( SELECT course_id FROM teaches WHERE semester = 'Spring' AND year =
2010 );

COURSE_I
--------
CS-347
PHY-101 SQL> SELECT course_id FROM teaches WHERE
SQL> SELECT course_id FROM teaches
WHERE semester = 'Fall' AND year = semester = 'Spring' AND year = 2010
2009 ;
COURSE_I
COURSE_I --------
-------- CS-315
CS-101 FIN-201
CS-347 MU-199
PHY-101 HIS-351
CS-101
CS-319

Aug 31, 2025 Dept. of I&CT 127

SQL> SELECT course_id FROM teaches SQL> SELECT course_id FROM teaches WHERE
WHERE semester = 'Fall' AND year = semester = 'Spring' AND year = 2010
2009 ;
COURSE_I
COURSE_I --------
-------- CS-315
CS-101 FIN-201
CS-347 MU-199
PHY-101 HIS-351
CS-101
CS-319

SQL> SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2009
AND course_id NOT IN ( SELECT course_id FROM teaches WHERE semester =
'Spring' AND year = 2010 );

Aug 31, 2025 Dept. of I&CT 128

 department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

Find the names of instructors whose names are neither "Mozart“ nor
"Einstein".
 Find the total number of (distinct) students who have taken course sections
taught by the instructor with ID 110011

Aug 31, 2025 Dept. of I&CT 129

 department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

Find the total number of (distinct) students who have taken course sections
taught by the instructor with ID 10101

SQL> select count(distinct id) as total_students from takes

where (course_id, sec_id, semester, year) IN

(select course_id, sec_id, semester, year from teaches where id =

Aug 31, 2025 Dept. of I&CT 130
10101 );
SQL> select count(distinct id) as total_students from takes where (course_id, sec_id,
semester, year) IN (select course_id, sec_id, semester, year from teaches where id =
10101 );
SQL> select * from takes;
SQL> select course_id, sec_id, semester, year
ID COURSE_I SEC_ID SEMEST from teaches where id = 10101
YEAR GR 2 ;
----- -------- -------- ------ ---------- --
128 CS-101 1 Fall 2009 COURSE_I SEC_ID SEMEST YEAR
A -------- -------- ------ ----------
128 CS-347 1 Fall 2009 CS-101 1 Fall 2009
A- CS-315 1 Spring 2010
12345 CS-101 1 Fall 2009 CS-347 1 Fall 2009
C
12345 CS-190 2 Spring ID COURSE_I SEC_ID SEMEST YEAR GR
2009 A ----- -------- -------- ------ ---------- --
12345 CS-315 1 Spring 2010 76543 CS-101 1 Fall 2009 A
A 76543 CS-319 2 Spring 2010 A
12345 CS-347 1 Fall 2009 76653 EE-181 1 Spring 2009 C
A 98765 CS-101 1 Fall 2009 C-
19991 HIS-351 1 Spring 2010 98765 CS-315 1 Spring 2010 B
B 98988 BIO-101 1 Summer 2009 A
23121 FIN-201 1 Spring 2010 98988 BIO-301 1 Summer 2010 C
Aug 31,C+2025 Dept. of I&CT 131
SQL> SQL> select count(distinct id) as total_students from takes where (course_id,
sec_id, semester, year) IN (select course_id, sec_id, semester, year from teaches where
id = 10101 );

TOTAL_STUDENTS
--------------
6

Aug 31, 2025 Dept. of I&CT 132

Nested Subquery – Set Comparison: some, all
 Find the names of all instructors whose salary is greater than at least one instructor in
the Finance department.
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept name = ‘Finance’;
• Same query using > some clause
select name from instructor
where salary > some (select salary
from instructor
where dept name = ‘Finance');

Aug 31, 2025 Dept of ICT 133 / 63

Nested Subquery – Set Comparison: some, all
 Find the names of all instructors whose salary is greater than at least one instructor in
the Finance department.
• "greater than at least one" is represented in SQL by >some

• > some comparison in the where clause of the outer select is true if the salary value of
the tuple is greater than at least one member of the set of all salary values for instructors
in Finance department.
• SQL also allows < some, <= some, >= some, = some, and <> some comparisons.

Aug 31, 2025 Dept of ICT 134 / 63

Nested Subquery – Set Comparison: some, all
 V <comp> some r t r such that (V <comp> t )
Where <comp> can be:     
0
(5 < some 5 ) = true
(read: 5 < some tuple in the relation)
6
0
(5 < some 5 ) = false

0
(5 = some 5 ) = true

0
(5  some 5 ) = true (since 0  5)
(= some)  in
However, ( some)  not in
Aug 31, 2025 Dept of ICT 135 / 63
SQL> select * from instructor;
SQL> select * from instructor where dept_name
ID NAME DEPT_NAME
='Finance’;
SALARY
----- -------------------- -------------------- ----------
ID NAME DEPT_NAME
10101 Srinivasan Comp. Sci.
SALARY
65000
----- -------------------- -------------------- ----------
12121 Wu Finance
12121 Wu Finance 90000
90000
76543 Singh Finance 80000
15151 Mozart Music
40000 SQL> SELECT name FROM instructor WHERE salary
22222 Einstein Physics > SOME ( SELECT salary FROM instructor
95000 WHERE dept_name = ‘Finance’ );
32343 El Said History
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543 Singh Finance
80000
Aug 31, 2025 Dept. of I&CT 136
76766 Crick Biology
SQL> select * from instructor; SQL> select * from instructor where dept_name
='Finance’;
ID NAME DEPT_NAME
SALARY ID NAME DEPT_NAME
----- -------------------- -------------------- ---------- SALARY
10101 Srinivasan Comp. Sci. ----- -------------------- -------------------- ----------
65000 12121 Wu Finance 90000
12121 Wu Finance 76543 Singh Finance 80000
90000
SQL> SELECT name FROM instructor WHERE salary
15151 Mozart Music
> SOME ( SELECT salary FROM instructor
40000
WHERE dept_name = ‘Finance’ );
22222 Einstein Physics
95000
NAME
32343 El Said History
--------------------
60000
Einstein
33456 Gold Physics
Brandt
87000
Wu
45565 Katz Comp. Sci.
Gold
75000
58583 Califieri History
62000
76543 Singh Finance
80000
Aug 31, 2025 Dept. of I&CT 137
76766 Crick Biology
SQL> select * from instructor;
SQL> select * from instructor where dept_name
ID NAME DEPT_NAME
='Finance’;
SALARY
----- -------------------- -------------------- ----------
ID NAME DEPT_NAME
10101 Srinivasan Comp. Sci.
SALARY
65000
----- -------------------- -------------------- ----------
12121 Wu Finance
12121 Wu Finance 90000
90000
76543 Singh Finance 80000
15151 Mozart Music
40000 SQL> SELECT name FROM instructor WHERE salary
22222 Einstein Physics > SOME ( SELECT salary FROM instructor
95000 WHERE dept_name = ‘Finance’ );
32343 El Said History
60000 NAME
33456 Gold Physics --------------------
87000 Einstein
45565 Katz Comp. Sci. Brandt
75000 Wu
58583 Califieri History Gold
62000
76543 Singh Finance
80000
Aug 31, 2025 Dept. of I&CT 138
76766 Crick Biology
SQL> select * from instructor; SQL> select * from instructor where dept_name
='Finance’;
ID NAME DEPT_NAME
SALARY ID NAME DEPT_NAME
----- -------------------- -------------------- ---------- SALARY
10101 Srinivasan Comp. Sci. ----- -------------------- -------------------- ----------
65000 12121 Wu Finance 90000
12121 Wu Finance 76543 Singh name FROM
SQL> SELECT Finance instructor WHERE80000
90000 salary < SOME ( SELECT salary FROM instructor
15151 Mozart Music WHERE dept_name = 'Finance' );
40000
22222 Einstein Physics NAME
95000 --------------------
32343 El Said History Mozart
60000 El Said
33456 Gold Physics Califieri
87000 Srinivasan
45565 Katz Comp. Sci. Crick
75000 Katz
58583 Califieri History Singh
62000 Kim
76543 Singh Finance Gold
80000
Aug 31, 2025 9 rows
Dept. selected.
of I&CT 139
76766 Crick Biology
SQL> select * from instructor; SQL> select * from instructor where dept_name
='Finance’;
ID NAME DEPT_NAME
SALARY ID NAME DEPT_NAME
----- -------------------- -------------------- ---------- SALARY
10101 Srinivasan Comp. Sci. ----- -------------------- -------------------- ----------
65000 12121 Wu Finance 90000
12121 Wu Finance 76543 Singh name FROM
SQL> SELECT Finance instructor WHERE80000
90000 salary <> SOME ( SELECT salary FROM instructor
15151 Mozart Music WHERE dept_name = 'Finance' );
40000
22222 Einstein Physics
95000
32343 El Said History
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543 Singh Finance
80000
Aug 31, 2025 Dept. of I&CT 140
76766 Crick Biology
SQL> select * from instructor; SQL> select * from instructor where dept_name
='Finance’;
ID NAME DEPT_NAME
SALARY ID NAME DEPT_NAME
----- -------------------- -------------------- ---------- SALARY
10101 Srinivasan Comp. Sci. ----- -------------------- -------------------- ----------
65000 12121 Wu Finance 90000
12121 Wu Finance 76543 Singh name FROM
SQL> SELECT Finance instructor WHERE80000
90000 salary <> SOME ( SELECT salary FROM instructor
15151 Mozart Music WHERE dept_name = 'Finance' );
40000
NAME
22222 Einstein Physics --------------------
95000 Srinivasan
32343 El Said History Wu
Mozart
60000 Einstein
33456 Gold Physics El Said
87000 Gold
45565 Katz Comp. Sci. Katz
Califieri
75000 Singh
58583 Califieri History Crick
62000 Brandt
Kim
76543 Singh Finance
80000 12 rows selected.
Aug 31, 2025 Dept. of I&CT 141
76766 Crick Biology
SQL> select * from instructor;
SQL> SELECT name FROM instructor WHERE salary
ID NAME DEPT_NAME <> SOME ( SELECT salary FROM instructor WHERE
SALARY dept_name = 'Biology' );
----- -------------------- -------------------- ----------
10101 Srinivasan Comp. Sci. NAME
65000 --------------------
12121 Wu Finance Srinivasan
90000 Wu
15151 Mozart Music Mozart
40000 Einstein
22222 Einstein Physics El Said
95000 Gold
32343 El Said History Katz
60000 Califieri
33456 Gold Physics Singh
87000 Brandt
45565 Katz Comp. Sci. Kim
75000
58583 Califieri History 11 rows selected.
62000
76543 Singh Finance
80000
Aug 31, 2025 Dept. of I&CT 142
76766 Crick Biology
Nested Subquery – Set Comparison
Find the names of all instructors who have a salary value greater than that of each
0
instructor in the Finance department. (5 < all 5 ) = false
6
6
V <comp> all r t r (V <comp> t) (5 < all 10 ) = true

4
(5 = all 5 ) = false

4
(5  all 6 ) = true (since 5  4 and 5  6)
( all)  not in
However, (= all)  in

Aug 31, 2025 Dept of ICT 143 / 63

Nested Subquery – Set Comparison
Find the names of all instructors who have a salary value greater than that of each
instructor in the Biology department.

• Construct > all corresponds to the phrase "greater than all“

• SQL also allows < all, <= all, >= all, = all, and <> all comparisons

• <>all is identical to not in, whereas = all is not the same as in.

 Find the departments that have the highest average salary

Aug 31, 2025 Dept of ICT 144 / 63

SQL> select * from instructor; SQL> select * from instructor where dept_name
='Finance’;
ID NAME DEPT_NAME
SALARY ID NAME DEPT_NAME
----- -------------------- -------------------- ---------- SALARY
10101 Srinivasan Comp. Sci. ----- -------------------- -------------------- ----------
65000 12121 Wu Finance 90000
12121 Wu Finance 76543 Singh name FROM
SQL> SELECT Finance instructor WHERE80000
90000 salary <> ALL ( SELECT salary FROM instructor
15151 Mozart Music WHERE dept_name = 'Finance' );
40000
22222 Einstein Physics NAME
95000 --------------------
32343 El Said History Srinivasan
60000 Katz
33456 Gold Physics Gold
87000 Brandt
45565 Katz Comp. Sci. El Said
75000 Califieri
58583 Califieri History Crick
62000 Mozart
76543 Singh Finance Einstein
80000
76766 CrickAug 31, 2025
Biology 9 rows
Dept. selected.
of I&CT 145
SQL> select * from instructor;

ID NAME DEPT_NAME
SQL> SELECT name FROM instructor WHERE salary
SALARY
>ALL ( SELECT salary FROM instructor WHERE
----- -------------------- -------------------- ----------
dept_name = 'Comp. Sci.' );
10101 Srinivasan Comp. Sci.
65000
NAME
12121 Wu Finance
--------------------
90000
Einstein
15151 Mozart Music
40000
22222 Einstein Physics
95000
32343 El Said History
60000
33456 Gold Physics
87000
45565 Katz Comp. Sci.
75000
58583 Califieri History
62000
76543 Singh Finance
80000
Aug 31, 2025 Dept. of I&CT 146
76766 Crick Biology
Test for Empty Relation

• The exists construct returns the value true if the argument subquery is nonempty

• exists r returns true if r ≠ Φ and

• not exists r returns true if r = Φ

• Correlated Subquery: A subquery that uses a correlation name from an outer

query

• Result of exists is a Boolean value TRUE or FALSE

Aug 31, 2025 Dept of ICT 147 / 63

Test for Empty Relation

• The exists construct returns the

value true if the argument subquery
is nonempty
• exists r returns true if r ≠ Φ and

• not exists r returns true if r = Φ

Find all courses offered in both the Fall 2009 and in

the Spring 2010 using exists keyword

Aug 31, 2025 Dept of ICT 148 / 63

 • The exists construct returns the
Test for Empty
Relation value true if the argument subquery
is nonempty
• exists r returns true if r ≠ Φ and

• not exists r returns true if r = Φ

Find all courses offered in both the Fall 2009 and in the Spring 2010
select course_id
from section S
where semester = 'Fall' and year = 2009
and exists ( select course_id
from section T
where semester = 'Spring' and year = 2010 and S.course_id =
T.course_id) ;

Aug 31, 2025 Dept of ICT 149 / 63

COURSE_I SEC_ID SEMEST COURSE_I SEC_ID SEMEST
…… ……
-------- -------- ------ ---------- --------------- -------- -------- ------ ---------- ---------------
BIO-101 1 Summer 2009 ….. BIO-101 1 Summer 2009 …..
BIO-301 1 Summer 2010 .. BIO-301 1 Summer 2010 ..
CS-101 1 Fall CS-101 1 Fall
2009 .. 2009 ..
CS-101 1 Spring CS-101 1 Spring
2010 .. 2010 ..
CS-190 1 Spring CS-190 1 Spring
2009 .. 2009 ..
CS-190 2 Spring CS-190 2 Spring
2009 .. 2009 ..
CS-315 1 Spring CS-315 1 Spring
2010 .. 2010 ..
CS-319 1 Spring CS-319 1 Spring
2010 .. 2010 ..
CS-319 2 Spring CS-319 2 Spring
2010 .. select course_id from section S where 2010semester
.. = 'Fall' and year = 2009
CS-347 1 Fall CS-347 1 Fall
and exists ( select * from section T
2009 .. 2009 ..
EE-181 1 where semester = 'Spring' and EE-181
Spring year = 2010 1 and S.course_id = T.course_id) ;
Spring
2009 Aug.. 31, 2025 2009
Dept. ..
of I&CT 150
 SQL> select course_id from section S
where semester = 'Fall' and year = 2009
and exists ( select * from section T
where semester = 'Spring' and year = 2010 and
S.course_id = T.course_id) ;

COURSE_I
--------
CS-101

Aug 31, 2025 Dept. of I&CT 151

Test for Empty Relation
• The exists operator terminates the processing of the subquery once the subquery returns the
first row
select *
from customers
where exists (select *
from orderdetails
where customers.customerid = orderdetails.customerid );

Aug 31, 2025 Dept. Of I&CT 152

Test for Empty Relation
• Correlated Subquery: A subquery that uses a correlation name from an outer query
• Result of exists is a Boolean value TRUE or FALSE
• The exists operator terminates the processing of the subquery once the subquery returns the first row
select *
from customers
where exists (select *
from orderdetails
where customers.customerid = orderdetails.customerid );
• Will return all records from the customers table where there is at least one record in the order details
table with the matching customer id

Aug 31, 2025 Dept. Of I&CT 153

 Test for Empty Relation
select *
from customers
where not exists ( select *
from orderdetails
where customers.customerid = orderdetails.customerid) ;
• Will return all records from the customers table where there are no records in the order
details table for the given customerid.
• not exists construct simulates the set containment

Aug 31, 2025 Dept. Of I&CT 154

Test for Empty Relation

IN Exists

Scans all rows returned by the sub Terminates the processing of the
query to conclude the result subquery once the sub query returns the
first row

Return all rows where the attribute Returns true of the subquery returns any
value is present in the subquery rows, otherwise, it return false
select *
Exists or Not exists solely check the
from tablename
where id in (subquery); existences of rows in the sub query

select *
from tablename
where id = 1 OR id = 2 OR id = 3 OR
id = null;
Aug 31, 2025 Dept. Of I&CT 155
Test for Empty Relation

 Find those instructors who do not teach any course

 Find all students who have taken all courses offered in Finance department
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

Aug 31, 2025 Dept. Of I&CT 156

Test for Empty Relation

 Find those instructors who do not teach any course

 Find all students who have taken all courses offered in Finance department

select id, name from student s where not exists (( select course_id from course where
dept_name = ‘Finance’ ) minus (select course_id from takes t where s.id = t.id));

Aug 31, 2025 Dept. Of I&CT 157

SQL> select * from takes;

ID COURSE_I SEC_ID SEMEST ID COURSE_I SEC_ID SEMEST YEAR GR

YEAR GR ----- -------- -------- ------ ---------- --
----- -------- -------- ------ ---------- -- 45678 CS-319 1 Spring 2010 B
128 CS-101 1 Fall 2009 54321 CS-101 1 Fall 2009 A-
A 54321 CS-190 2 Spring 2009 B+
128 CS-347 1 Fall 2009 55739 MU-199 1 Spring 2010 A-
A- 76543 CS-101 1 Fall 2009 A
12345 CS-101 1 Fall 2009 76543 CS-319 2 Spring 2010 A
C 76653 EE-181 1 Spring 2009 C
12345 CS-190 2 Spring 2009 A 98765 CS-101 1 Fall 2009 C-
12345 CS-315 1 Spring 2010 A 98765 CS-315 1 Spring 2010 B
12345 CS-347 1 Fall 2009 98988 BIO-101 1 Summer 2009 A
A 98988 BIO-301 1 Summer 2010
19991 HIS-351 1 Spring 2010
B 22 rows selected.
23121 FIN-201 1 Spring 2010
SQL> select C+ course_id from course where dept_name = 'Finance';
44553 PHY-101 1 Fall 2009
COURSE_I B-
--------
45678 CS-101 1 Fall 2009
FIN-201F
Aug 31, 2025
45678 CS-101 1 Spring 2010 B+ Dept. of I&CT 158
Test for Empty Relation
 Find those instructors who do not teach any course

 Find all students who have taken all courses offered in Finance department

SQL> select id, name from student s where not exists (( select
course_id from course where dept_name = 'Finance' ) minus (select
course_id from takes t where s.id = t.id));

ID NAME
----- --------------------
23121 Chavez

Aug 31, 2025 Dept. Of I&CT 159

 Sub Queries in FROM
clause

Aug 31, 2025 Dept. of I&CT 160

Sub Queries in FROM clause

Any select-from-where expression returns a relation as a result can be inserted

into another select-from-where anywhere that a relation can appear

Aug 31, 2025 Dept. Of I&CT 161

Sub Queries in FROM clause

department(dept_name, building, budget)

course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

Find the average instructor’s salaries of those departments where the average
salary is greater than ₹42000
Aug 31, 2025 Dept. Of I&CT 162
Sub Queries in FROM clause
Find the average instructor’s salaries of those departments where
the average salary is greater than ₹42000

SELECT dept_name, avg_salary

FROM (
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
) dept_avg
WHERE avg_salary > 42000;

Aug 31, 2025 Dept. Of I&CT 163

Sub Queries in FROM clause
• Find the average instructor’s salaries of those departments where the average salary is
SQL> SELECT dept_name, avg_salary
greater than ₹42000 2 FROM (
3 SELECT dept_name, AVG(salary) AS
avg_salary
4 FROM instructor
5 GROUP BY dept_name
6 ) dept_avg
7 WHERE avg_salary > 42000;

DEPT_NAME AVG_SALARY
-------------------- ----------
Comp. Sci. 77333.3333
Finance 85000
Physics 91000
History 61000
Biology 72000
Elec. Eng. 80000
Aug 31, 2025 Dept. Of I&CT 164
Sub Queries in FROM clause
• Find the average instructor’s salaries of those departments where the average salary is greater than ₹42000

SQL> SELECT dept_name, avg_salary SQL> select dept_name, avg(Salary) as

2 FROM ( avg_salary from instructor group by
3 SELECT dept_name, AVG(salary) AS dept_name having avg(salary)>42000;
avg_salary
4 FROM instructor DEPT_NAME AVG_SALARY
5 GROUP BY dept_name -------------------- ----------
6 ) dept_avg Comp. Sci. 77333.3333
7 WHERE avg_salary > 42000; Finance 85000
Physics 91000
DEPT_NAME AVG_SALARY History 61000
-------------------- ---------- Biology 72000
Comp. Sci. 77333.3333 Elec. Eng. 80000
Finance 85000
Physics 91000 6 rows selected.
History 61000
Biology 72000
Elec.AugEng.31, 2025 80000 Dept. Of I&CT 165
 department(dept_name, building, budget)
course(course_id, title, dept_name, credits)

Sub Queries in FROM instructor(ID, name, dept_name, salary)

clause section(course_id, sec_id, semester, year, building, room_number,
time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

• Find the average instructor’s salaries of those departments where the average salary is
greater than ₹42000
• Find the maximum salary across all departments of the total salary at each department
• Print the names of each instructor, along with their salary and the average salary in the
department
Aug 31, 2025 Dept. Of I&CT 166
Sub Queries in FROM clause

• Nested subqueries in the from clause cannot use correlation variables

from other relations in the from clause.

Aug 31, 2025 Dept. Of I&CT 167

 WITH clause

Aug 31, 2025 Dept. of I&CT 168

• Find the average instructor’s salaries of those departments where the
average salary is greater than ₹42000
department(dept_name, building, budget) , course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary) ,
section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year), student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)
with dept_avg(dept_name, val) as(
SELECT dept_name, AVG(salary) AS
avg_salary
FROM instructor
GROUP BY dept_name
)
SELECT dept_name, val
FROM dept_avg
Aug 31, 2025 Dept. ofWHERE
I&CT val> 42000; 169
WITH clause
• The with clause defines the temporary relation, which is used in the immediately following
query

with dept_avg(dept_name, val) as(

SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
)
SELECT dept_name, val
FROM dept_avg
WHERE val> 42000;

Aug 31, 2025 Dept. Of I&CT 170

WITH clause examples
with GoodBudget(dept_name, val) as

(select dept_name, budget from department where budget >

50000)

select name from instructor natural join GoodBudget;

Aug 31, 2025 Dept. of I&CT 171

WITH clause
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

 Find those departments with the maximum budget using with clause
 Find all departments where the total salary is greater than the average of the total
salary at all departments using with clause

Aug 31, 2025 Dept. Of I&CT 172

 SQL> with maxbudget(maxb) as(
SELECT max(budget) AS maxb
FROM department
)
SELECT dept_name
FROM department, maxbudget
WHERE budget>maxbudget.maxb/2;
Find those
departments with the DEPT_NAME
maximum budget --------------------
using with clause Elec. Eng.
Finance
Biology
Comp. Sci.
Music
Physics

Aug 31, 2025 6 rows selected.

Dept. of I&CT 173
Find all departments where the total salary is greater than the average of the total
salary at all departments using with clause

with depttotal (deptname, val) as

( select dept_name, sum(salary)
from instructor
group by dept_name ) ,
depttotavg (val) as
( select avg(val)
from depttotal )
select deptname, depttotal.val
from depttotal, depttotavg
where depttotal.val >= depttotavg.val;

Aug 31, 2025 Dept. Of I&CT 174

 SQL> with depttotal (deptname, val) as
2 (select dept_name, sum(salary)
3 from instructor
4 group by dept_name),
5 depttotavg (val) as
6 (select avg(val)
7 from depttotal)
8 select deptname, depttotal.val
9 from depttotal, depttotavg
10 where depttotal.val >= depttotavg.val;

DEPTNAME VAL
-------------------- ----------
Physics 182000
Comp. Sci. 232000
Finance
Aug 31, 2025 170000 Dept. of I&CT 175
 Scalar Sub-Queries

Aug 31, 2025 Dept. of I&CT 176

Scalar Sub-Queries
• The subquery returns only one tuple containing a single attribute; such
subqueries are called scalar subqueries

• Scalar subquery is one which is used where a single value is expected

 List all departments along with the number of instructors in each department

select dept_name,
(select count(*) from instructor where department.dept_name =
instructor.dept_name) as numinstructor
from department;
Aug 31, 2025 Dept. Of I&CT 177
Scalar Sub-Queries

• Scalar subqueries can occur in select, where and having clauses

• Scalar subqueries may also be defined without aggregates

• If the result has more than one tuple when the subquery is executed, a run-
time error

• Technically the type of a scalar subquery result is still a relation, even if it

contains a single tuple error occurs

Aug 31, 2025 Dept. Of I&CT 178

Modification of database - Deletion
 Delete all tuples in the instructor relation pertaining to instructors in the Finance
department

 Delete all instructors with a salary between ₹13000 and ₹15000

 Delete all tuples in the instructor relation for those instructors associated with a
department located in the Watson building

 Delete the records of all instructors with salary below the average at the university

• Performing all the tests before performing any deletion is important

Aug 31, 2025 Dept. Of I&CT 179

SQL> insert into department values('CSE','AB5',50000);

1 row created.

SQL> insert into instructor values('12345','Ambika','CSE',300000);

1 row created.

SQL> delete from instructor where dept_name = (select

dept_name from department where building='AB5');

1 row deleted.

Aug 31, 2025 Dept. of I&CT 180

Modification of database – Insertion
 Make each student in the Music department who has earned more than 144
credit hours, an instructor in the Music department, with a salary of ₹18000.

insert into instructor (id, name, dept_name, salary)

select id, name, 'Physics', 18000

from student

where dept_name = 'Physics' and tot_cred>50;

• It is possible for tuples to be given values on only some attributes of the

schema
Aug 31, 2025 Dept. Of I&CT 181
Modification of database – Updation
 Update totcred attribute of each student tuple to the sum of the credits of courses

successfully completed by the student. Assume that a course is successfully

completed if the student has a grade that is not ‘F’ or null

department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building,
room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)

Aug 31, 2025 Dept. Of I&CT 182

Modification of database – Updation
 Update totcred attribute of each student tuple to the sum of the credits of courses successfully

completed by the student. Assume that a course is successfully completed if the student has a

grade that is not ‘F’ or null

update student S

set totcred = (select sum(credits)

from takes natural join course

where S.ID = takes.ID and takes.grade <> 'F ' and

takes.grade is not null ) ;

Aug 31, 2025 Dept. Of I&CT 183

Questions
1. Find the titles of courses in Comp. Sci. department that have 3 credits
2. Find the IDs of all students who were taught by an instructor named Einstein;
make sure there are no duplicates in the result
3. Find the highest salary of any instructor
4. Find all instructor earning the highest salary (there may be more than one
with the same salary)
5. Find the enrollment of each section that was offered in Autumn 2021
6. Find the maximum enrollment across all sections in Autumn 2021
7. Find the sections that had the maximum enrollment in Autumn 2021
8. Increase the salary of each instructor in the Comp. Sci. department by 10%
Aug 31, 2025 Dept. Of I&CT 184
Questions
9. Delete all courses that have never been offered (that is, do not occur in the section relation)
10. Insert every student whose total credit attribute is greater than 100 as an instructor in the
same department, with a salary of ₹10000
11. Find the names of all students who have taken atleast one Comp. Sci. course; make sure
there are no duplicate names in the result
12. Find the IDs and names of all students who have not taken any course offering before
Soring 2021
13. For each department, find the maximum salary of the instructors in that department. You
may assume that every department has atleast one instructor
14. Find the lowest, across all departments of the per-department maximum salary computed
by the preceding query
Aug 31, 2025 Dept. Of I&CT 185