Database and Information Systems

06/11/2023 Indian Institute of Technology Indore Computer Science & Engineering

 Course Roadmap

. Chapter 1 Introduction to Databases

Chapter 2 Integrity Constraints and ER Model

Chapter 3 Relational Databases and Schema Refinement

Chapter 4 Query Language

Chapter 5 Transaction and Concurrency Control

Chapter 6 Indexing
 Introduction to Structure Query Language

n Structure Query Language (SQL): Domain specific and declarative

language
l Allow access and manipulate databases
l Execute queries against a database
l Retrieve data from a database User

l Insert records in a database

l Update records in a database
Language
l Delete records from a database
l Create new databases
l Create new tables in a database Structure
Database Data
l Create views in a database
l Set permissions on tables and views

Database and Information Systems 4.3

 History
n In 1970, E.F. Codd develops relational database concepts: published
a research paper while working at IBM San Jose Research Laboratory
n During 1974-1979, Sequel (Structured English Query Language) was
created at IBM, renamed to SQL Later
n In 1979, Oracle markets first DB with SQL
n In 1986, ANSI SQL standards were released and updated later wards
(1989, 1992, 1999, 2003, …)
n Most DBMS are SQL-99 compliant, with partial SQL-2003 compliant
n Now Database major players: Oracle, IBM, Microsoft, MySQL

Database and Information Systems 4.4

 Sub Language
n Data Definition Language
l Used to define structure of a table
n Data Manipulation Language
l Used to manipulate database records
n Data Query Language
l Used to access required data from database tables
n Data Control Language
l Used for transaction based operations and security

Database and Information Systems 4.5

 Data Definition Language

The SQL data-definition language (DDL) allows the specification of

information about relations, including:
n The schema for each relation (or table)
n The type of values associated with each attribute
n The Integrity constraints
n Examples ID Roll No Department
l Create Table
l Drop Table
 Delete table
l Alter Table
 Add and remove columns in a table Table: Student
l Truncate
 Delete all the data inside a table
l Integrity constraints: primary key, foreign key, alternate key
l Rename

Database and Information Systems 4.6

 Create Table Construct

 An SQL relation is defined using the create table command:

create table r
(A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
 r is the name of the relation
 each Ai is an attribute name in the schema of relation r
 Di is the data type of values in the domain of attribute Ai
 Example:
create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2)
primary key (ID)
foreign key (dept_name) references dept (dept_name));

Database and Information Systems 4.7

 Domain Types in SQL

 char(n). Fixed length character string, with user-specified length n.

 varchar(n). Variable length character strings, with user-specified
maximum length n.
 int. Integer (a finite subset of the integers that is machine-dependent).
 numeric(p,d). Fixed point number, with user-specified precision of p
digits, with d digits to the right of decimal point. (ex., numeric(3,1), allows
44.5 to be stores exactly, but not 444.5 or 0.32)

Database and Information Systems 4.8

 Integrity Constraints in Create Table

 Types of integrity constraints

 primary key (A1, ..., An )
 foreign key (Am, ..., An ) references r
 not null
 SQL prevents any update to the database that violates an integrity
constraint.
 Example:
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
primary key (ID),
foreign key (dept_name) references dept (dept_name));

Database and Information Systems 4.9

 More Constraints in SQL
 Unique
 No duplicate values in a column
 Default
 salary int default 10000
 Check
 Fix the domain
 Example: check (age >50)
 Not Null
 Mandatory value
 Primary Key
 Unique + Not Null
 Example: Student Roll No in Institute Database
 Foreign Key
 For referential integrity

Database and Information Systems 4.10

 And a Few More Relation Definitions

 create table student (

ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),
primary key (ID),
foreign key (dept_name) references dept (dept_name));

 create table takes (

ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
primary key (ID, course_id, sec_id, semester, year) ,
foreign key (ID) references student (ID));

Database and Information Systems 4.11

 Data Manipulation Language
 Data Manipulation Language (DML) is used to manipulate the data records
 Insert
insert into Student values (‘4', ‘Ram', ‘CS');

 Delete
 Remove all tuples from the student relation
 delete from Student

 Remove student with ID 2

 delete from Student where ID = 2;

 Update
 Update a tuple from the student relation
 update Student set Department = ‘CS' where ID = 3;
ID Roll No Department
1 Rahul CS
2 Suresh EE
3 Kesav ME
Table: Student
Database and Information Systems 4.12
 Data Query Language
 Data Query Language (DQL) is used to access required data from
database tables
 A typical SQL query has the form:

select A1, A2, ..., An

from r1, r2, ..., rm
where P

 Ai represents an attribute
 Ri represents a relation
 P is a predicate.
 The result of an SQL query is a relation.
 Other DQL commands
 Group By
 Having

Database and Information Systems 4.13

 Data Control Language
 Data Control Language (DCL) is used for transaction based
operations and security
 Operations
 Grant
 Give privileges to a user over table
 Revoke
 Remove privileges from user over table
 Rollback
 If transaction is failed, rollback it
 Commit
 Transactions completed successfully, save in database
 Save Point
 Save some part of execution in DB

Database and Information Systems 4.14

 Alter Command in SQL
 Alter is use to change the schema or structure or relation or table
 Function of Alter command
 Add columns
 alter table student add address varchar (10);
 Remove columns
 alter table student drop column address;
 Modify data type
 alter table student modify ID varchar (10);
 Add constraints
 alter table student add primary key (name);
 Remove constraints
 alter table student drop primary key;
 Rename column/table
 alter table student rename column id to roll_no;
 alter table student rename to stu;
Database and Information Systems 4.15
 Difference between Alter and Update

Alter Update
DDL DML
Make changes in relation or table structure Make changes in
data
alter table employee add address varchar (10); update employee set
salary = salary*2
where ID =1;

ID name salary
1 rahul 1000000
2 rohan 2000000
3 rakesh 3000000

Table: employee

Database and Information Systems 4.16

 Difference between Delete, Drop, and Truncate

Delete Drop Truncate

DML Command DDL Command DDL Command

Delete all rows. Delete table structure. Delete all rows.
delete from Student; drop table Student; truncate table Student;
Can give condition. No condition No condition
delete from Student
where ID = 1;

Can rollback before No Rollback No rollback

commit. Use logs
Slower Faster Faster

ID Roll No Department
1 Rahul CS
2 Suresh EE
Database and Information Systems 4.17
Table: Student
 Modification of the Database

 Deletion of tuples from a given relation

 Insertion of new tuples into a given relation
 Updating of values in some tuples in a given relation

Database and Information Systems 4.18

 Deletion

 Delete all instructors

delete from instructor;

 Delete all instructors from the Finance department

delete from instructor
where dept_name= 'Finance’;

 Delete all tuples in the instructor relation for those instructors associated
with a department located in the Watson building.
delete from instructor
where dept name in (select dept name
from department
where building = 'Watson');

Database and Information Systems 4.19

 Insertion

 Add a new tuple to course

insert into course
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

 or equivalently

insert into course (course_id, title, dept_name, credits)

values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

 Add a new tuple to student with tot_creds set to null

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

Database and Information Systems 4.20

 Insertion (Cont.)

 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
18,000 INR.
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and total_cred > 144;

 The select from where statement is evaluated fully before any of its
results are inserted into the relation.

Database and Information Systems 4.21

 Updates

 Give a 5% salary raise to all instructors

update instructor
set salary = salary * 1.05
 Give a 5% salary raise to those instructors who earn less than 70000
update instructor
set salary = salary * 1.05
where salary < 70000;
 Give a 5% salary raise to instructors whose salary is less than average
update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
from instructor);

Database and Information Systems 4.22

 Aggregate Functions
 Aggregate Functions
 Max, Min, Count, Avg, Sum
E_id E_name Dept Salary
1 Ram HR 10000
2 Amrit MRKT 20000
3 Ravi HR 30000
4 Nitin MRKT 30000
5 Varun IT 50000
6 Sandy TESTING NULL
 Max Table: Emp

 Find maximum salary

 Select Max(Salary) from Emp;
 Find employee name who is getting maximum salary?

Database and Information Systems 4.23

 Aggregate Functions
 Aggregate Functions
 Max, Min, Count, Avg, Sum
E_id E_name Dept Salary
1 Ram HR 10000
2 Amrit MRKT 20000
3 Ravi HR 30000
4 Nitin MRKT 30000
5 Varun IT 50000
 Max 6 Sandy TESTING NULL
 Find maximum salary Table: Emp
 Select Max(Salary) from Emp;
 Find employee name who is getting maximum salary: Use of
Nested or SubQuery
 Select E_name from Emp where Salary = (Select Max(Salary)
from Emp); Here, inner query execute before outer query
Database and Information Systems 4.24
 Aggregate Functions
 Max
 Select E_name from Emp where Salary = (Select Max(Salary) from Emp);
10000 = 50000 False
20000 = 50000 False
30000 = 50000 False
40000 = 50000 False
50000 = 50000 True
 Count: count total records or rows
 Select Count(*) from Emp;
 Sum: sum on columns containing numerical values
 Select Sum(Salary) from Emp;
 Avg: average on columns containing numerical values Table: Emp

 Select Avg(Salary) from Emp;

 It discords NULL value

Database and Information Systems 4.25

 Aggregate Functions

Table: Emp

 Select Min(Salary) from Emp; ??

 Select Avg(Distinct(Salary)) from emp; ??
 Select Count(Salary) from Emp; ??
 Select Count(Distinct(Salary)) from Emp; ??

Database and Information Systems 4.26

 In and Not In
 In and Not In are used when one value is compared with multiple values
 Examples
 Find detail of employee whose address is either Delhi or Indore, or
Pune
 Select * from Emp where Address In (‘Delhi’, ‘Indore’, ‘Pune’);
 Similarly Not In is also used
 Select * from Emp where Address Not In (‘Delhi’, ‘Indore’, ‘Pune’);

Eid Ename Address

1 Ravi Indore
2 Varun Delhi
3 Nitin Pune
4 Robin Bangalore
5 Ammy Indore
Table: Emp

Database and Information Systems 4.27

 SQL Queries and SubQueries
 Use of IN in sub-queries or nested queries
 Find the name of employees who are working on a project
 Select Ename from Emp where Eid in (Select Distinct Eid from
Project);

Eid Ename Address Eid Pid Pname Location

1 Ravi Indore 1 P1 IOT Bangalore
2 Varun Delhi 5 P2 Big Data Delhi
3 Nitin Pune 3 P3 Retail Mumbai
4 Robin Bangalore 4 P4 Android Hyderabad
5 Ammy Indore Table: Project
Table: Emp

 Select Ename from Emp where Eid not in (Select Distinct Eid
from Project); ??
 Note: SQL executes innermost subquery first, then next level

Database and Information Systems 4.28

 SQL Queries and SubQueries
 Write a SQL query to find second highest salary from Emp Table
 Select Max(Salary) from Emp where Salary <> (Select
Max(Salary) from Emp);

E_id E_name Dept Salary

1 Ram HR 10000
2 Amrit MRKT 20000
3 Ravi HR 30000
4 Nitin MRKT 30000
5 Varun IT 50000
Table: Emp

 Write a SQL query to find employee name who is taking second

highest salary
 Select E_name from Emp where Salary = (Select Max(Salary)
from Emp where Salary <> (Select Max(Salary) from Emp));

Database and Information Systems 4.29

 SQL Queries and SubQueries: Group By Clause

 Write a query to display all the department names along with number of
employees working in that department Aggregate
 Select Dept, count(Dept) from Emp Group By(Dept); Function

E_id E_name Dept Salary

1 Ram HR 10000 HR HR 2
2 Amrit MRKT 20000 HR MRKT 2
3 Ravi HR 30000 MRKT IT 1
4 Nitin MRKT 30000 MRKT Group By with
count
5 Varun IT 50000 IT
Group By
Table: Emp Intermediate
Result
 Group by groups rows that have the same values
 Can use aggregate functions with group by
 Example: Find branch-wise student names
Database and Information Systems 4.30
 SQL Queries and SubQueries: Having Clause

 Write a query to display all the department names where number of

employees are less than two
 Select Dept from Emp Group By(Dept) having count(*) <2;

E_id E_name Dept Salary

1 Ram HR 10000
2 Amrit MRKT 20000
3 Ravi HR 30000
4 Nitin MRKT 30000
5 Varun IT 50000
Table: Emp
 Find the name of employee(s) who is/are working in the
department where number of employees are less than two?

Database and Information Systems 4.31

 SQL Queries and SubQueries: Having Clause

 Write a query to display all the department names where number of

employees are less than two
 Select Dept from Emp Group By(Dept) having count(*) <2;

E_id E_name Dept Salary

1 Ram HR 10000
2 Amrit MRKT 20000
3 Ravi HR 30000
4 Nitin MRKT 30000
5 Varun IT 50000
Table: Emp
 Now employee name also can be found using nested query
 Select E_name from Emp where Dept in (Select Dept from
Emp Group By(Dept) having count(*) <2);

Database and Information Systems 4.32

 Correlated SubQuery
 Subquery that uses value from outer query
 Follows top to bottom approach
 First row of outer query compares with all the rows of inner query
 Called Synchronized Query
 Example Returns true
 Find all employees detail who work in a department or false
 Select * from Emp where exists (Select * from Dept where [Link]= [Link])
Eid Name Address

1 A Delhi Did Dname Eid

D1 HR 1
2 B Pune
D2 IT 2
3 A Chd
D3 MRKT 3
4 B Delhi
D4 Testing 4
5 C Pune
Table: Dept
6 D Mumbai

7 E Hyd

Table: Emp
Can write query using in ?
Database and Information Systems 4.33
 Exist and Not Exist SubQueries
 Find the detail of employee who is working on at least one project
 Select * from Emp where exists (Select Eid from Project where
[Link] = [Link])
 Find the detail of employee who is not working on any project
 Select * from Emp where not exists (Select Eid from Project where
[Link] = [Link])

Eid Ename Address Eid Pid Pname Location

1 Ravi Indore 1 P1 IOT Bangalore
2 Varun Delhi 5 P2 Big Data Delhi
3 Nitin Pune 3 P3 Retail Mumbai
4 Robin Bangalore 4 P4 Android Hyderabad
5 Ammy Indore Table: Project
Table: Emp

Database and Information Systems 4.34

 Correlated SubQuery – Nth Highest Salary

 Find N-th highest salary

 Will use correlated nested query, it processes top to bottom
 Select ID, Salary from Emp e1 where N-1 = (Select count (distinct
Salary) from Emp e2 where [Link] > [Link])
 Where e1 and e2 are alias of Emp Table

ID Salary ID Salary
1 10000 1 10000
2 20000 2 20000
3 20000 3 20000
4 30000 4 30000
5 40000 5 40000
6 50000 6 50000
Table: Emp e1 Table: Emp e2

Database and Information Systems 4.35

 Difference between Joins, Nested
SubQuery and Correlated SubQuery
 Example: Find the detail of employee who is working on any department

Nested SubQuery Correlated SubQuery Joins

(or Correlated SubQuery)
Bottom Up Approach Top Down Approach Cross Product +
Condition
Select * from Emp Select * from Emp where Select [Link],
where eid in (Select exits (Select eid from Dept [Link] from Emp ,
eid from Dept); where [Link] = [Link]); Dept where [Link] =
[Link];
eid name
1 A dept no name eid
2 B D1 IT 1
3 C D2 HR 2
4 D D3 MRKT 3
5 E Table: Dept
Table: Emp
Database and Information Systems 4.36
 References
 Silberschatz, Abraham, Henry F. Korth, and Shashank
Sudarshan. Database system concepts. Vol. 6. New York: McGraw-
Hill, 1997.
 Ramez Elmasri, Shamkant B. Navathe. Fundamentals of Database
Systems. Edition 6. Pearson, 2010.

Database and Information Systems 4.37