CSE3001

Database Management Systems

Prof. Bhupendra Panchal

Assistant Professor
Modules Syllabus
Module- Relational Database design: features, atomic domain.
3
SQL: data definition, aggregate function, Null Values, nested sub queries,
Joined relations. Triggers. Data Manipulation Language (DML) and
Transaction Control Language (TCL) , Basic SELECT statements , Table
Joins, Restricting and Sorting Data - Order By / Group By. Retrieving Data
Using the SQL SELECT Statement, Single-Row Functions, Conversion
Functions and Conditional Expressions, Reporting Aggregated Data Using
the Group Functions, Displaying Data from Multiple Tables. Joins, Set
Operators, DML Statements, Data Definition Language - Data Dictionary

Views - Creating Sequences, Synonyms, and Indexes, Creating Views -

Introduction to views, data independence, security, updates on views,
comparison between tables and views.

Prepared and compiled by 2

Bhupendra Panchal, Asst. Professor, CSE
SQL Joins:
• SQL Join is used to fetch data from two or more tables, which is joined
to appear as single set of data.
• It is used for combining column from two or more tables by using
values common to both tables.
• Join Keyword is used in SQL queries for joining two or more tables.
• Minimum required condition for joining table, is (n-1) where n, is
number of tables.
• A table can also join to itself, which is known as, Self Join.

Prepared and compiled by 3

Bhupendra Panchal, Asst. Professor, CSE
Types of JOIN:
JOIN

INNER JOIN OUTER JOIN

(Outcome contains only (Outcome contains all tuples

matching tuples) from all tables)

• Theta Join • Left Join

• Equi Join • Right Join
• Natural Join • Full Join
• Self Join

Prepared and compiled by 4

Bhupendra Panchal, Asst. Professor, CSE
Types of JOIN:

Inner Join

Left Full
Prepared and compiled by
Right
Bhupendra Panchal, Asst. Professor, CSE
INNER Join or EQUI Join
• This is a simple JOIN in which the result is based on matched data as per
the equality condition specified in the SQL query.

•The syntax for Inner Join is,

SELECT column-name-list FROM

table-name1 INNER JOIN table-name2 on
[Link]-name = [Link]-name;

Prepared and compiled by 6

Bhupendra Panchal, Asst. Professor, CSE
INNER Join or EQUI Join Example
Table1: class Table2: class_info
ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
4 Anu

SELECT * from class INNER JOIN class_info on [Link] =

class_info.id;

ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai

Prepared and compiled by 7

Bhupendra Panchal, Asst. Professor, CSE
 Natural JOIN
• Natural Join is a type of Inner join which is based on
column having same name and same datatype present in both
the tables to be joined.

•The syntax for Natural Join is,

SELECT * FROM table-name1 NATURAL JOIN table-name2;

Prepared and compiled by 8

Bhupendra Panchal, Asst. Professor, CSE
Natural JOIN Example
Table1: class Table2: class_info
ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
4 Anu

SELECT * from class NATURAL JOIN class_info

ID NAME ADDRESS
1 Abhi Delhi
2 Adam Mumbai
3 Alex Chennai

Prepared and compiled by 9

Bhupendra Panchal, Asst. Professor, CSE
 Inner Join – Example
loan borrower
loan-number branch-name amount customer-name loan-number
L-170 Downtown 3000 Jones L-170
L-230 Redwood 4000 Smith L-230
L-260 Perryridge 1700 Hayes L-155

loan Borrower
loan-number branch-name amount customer-name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith

Prepared and compiled by

Bhupendra Panchal, Asst. Professor, CSE
Outer JOIN
• Outer Join is based on both matched and unmatched data.
• Outer Joins subdivide further into,

Left Outer Join

Right Outer Join

Full Outer Join

Prepared and compiled by 11

Bhupendra Panchal, Asst. Professor, CSE
LEFT Outer Join
• The left outer join returns a result set table with the matched
data from the two tables and then the remaining rows of the
left table and null from the right table's columns.

•Syntax for Left Outer Join is,

SELECT column-name-list FROM
table-name1 LEFT OUTER JOIN table-name2
ON [Link]-name = [Link]-name;

Prepared and compiled by 12

Bhupendra Panchal, Asst. Professor, CSE
 LEFT Outer Join Example-1
Table1: class Table2: class_info
ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
4 Anu 7 Noida
5 Ashish 8 Panipat

SELECT * FROM class LEFT OUTER JOIN class_info ON ([Link] = class_info.id);

ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
4 Anu Null Null
5 Ashish Null
Prepared and compiled by Null 13
Bhupendra Panchal, Asst. Professor, CSE
 Left Outer Join – Example-2
loan borrower
loan-number branch-name amount customer-name loan-number
L-170 Downtown 3000 Jones L-170
L-230 Redwood 4000 Smith L-230
L-260 Perryridge 1700 Hayes L-155

Left Outer Join

loan Borrower
loan-number branch-name amount customer-name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-260 Perryridge 1700 null

Prepared and compiled by

Bhupendra Panchal, Asst. Professor, CSE
RIGHT Outer Join
• The right outer join returns a resultset table with the
matched data from the two tables being joined, then the
remaining rows of the right table and null for the remaining
left table's columns.

•Syntax for Right Outer Join is,

SELECT column-name-list FROM

table-name1 RIGHT OUTER JOIN table-name2
ON [Link]-name = [Link]-name;

Prepared and compiled by 15

Bhupendra Panchal, Asst. Professor, CSE
 RIGHT Outer Join Example-1
Table1: class Table2: class_info
ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
4 Anu 7 Noida
5 Ashish 8 Panipat

SELECT * FROM class RIGHT OUTER JOIN class_info ON ([Link] = class_info.id);

ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
Null Null 7 Noida
Null Null 8 and compiled by Panipat
Prepared 16
Bhupendra Panchal, Asst. Professor, CSE
 Right Outer Join – Example-2
loan borrower
loan-number branch-name amount customer-name loan-number
L-170 Downtown 3000 Jones L-170
L-230 Redwood 4000 Smith L-230
L-260 Perryridge 1700 Hayes L-155

• Right Outer Join

loan borrower

loan-number branch-name amount customer-name

L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-155 null null Hayes

Prepared and compiled by

Bhupendra Panchal, Asst. Professor, CSE
FULL Outer Join
• The full outer join returns a result set table with the matched
data of two table then remaining rows of both left table and
then the right table.

•Syntax of Full Outer Join is,

SELECT column-name-list FROM
table-name1 FULL OUTER JOIN table-name2
ON [Link]-name = [Link]-name;

Prepared and compiled by 18

Bhupendra Panchal, Asst. Professor, CSE
 FULL Outer Join Example-1
Table1: class Table2: class_info
ID NAME ID ADDRESS
1 Abhi 1 Delhi
2 Adam 2 Mumbai
3 Alex 3 Chennai
4 Anu 7 Noida
5 Ashish 8 Panipat

ID NAME ID ADDRESS
1 Abhi 1 Delhi
SELECT * FROM class 2 Adam 2 Mumbai
FULL OUTER JOIN
class_info ON 3 Alex 3 Chennai
[Link] = class_info.id); 4 Anu Null Null
5 Ashish Null Null
Null Null 7 Noida
Prepared and
Null compiled by
Null 8 Panipat 19
Bhupendra Panchal, Asst. Professor, CSE
 Full Outer Join – Example-2
loan borrower
loan-number branch-name amount customer-name loan-number
L-170 Downtown 3000 Jones L-170
L-230 Redwood 4000 Smith L-230
L-260 Perryridge 1700 Hayes L-155

Full Outer Join

loan borrower
loan-number branch-name amount customer-name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-260 Perryridge 1700 null
L-155 null null Hayes

Prepared and compiled by

Bhupendra Panchal, Asst. Professor, CSE
Aggregate Functions:

List of Aggregate Functions in SQL:

SUM -to calculate sum of numbers

MAX -to find maximum value

MIN - to find minimum value

AVG -to calculate average of numbers

COUNT -to count not null values in an attribute

21
Aggregate Functions:
Let`s consider a relation RESULT

SQL> select *from result;

RNO CLGNAME SEM MARKS NAME

1 OIST I 71 Ajay
2 OIST II 73 Aman
3 OCT I 75 Bhanu
4 OIST I 72 Ajit
5 OCM I 76 Kartik
6 OCM II 82 Karan
7 OCT II 79 Ravi
8 OCT I 80 Rakesh

22
Aggregate Functions:
SQL> select max(marks) from result;
MAX(MARKS)
----------
82

SQL> select min(marks) from result;

MIN(MARKS)
----------
71

SQL> select sum(marks) from result;

SUM(MARKS)
----------
608
23
Aggregate Functions:
SQL> select avg(marks) from result;
AVG(MARKS)
----------
76

SQL> select count(marks) from result;

COUNT(MARKS)
------------
8

SQL> select rno,name from result where marks in(select max(marks) from result);
RNO NAME
---------- --------------------
6 Karan
Group by Clause:
Q. Find the maximum marks of OIST college.

SQL> select max(marks) from result where clgname='OIST';

MAX(MARKS)
----------
73

Q. Find the maximum marks of each college.

SQL> select max(marks) from result group by clgname;
MAX(MARKS)
----------
73
82
80
25
Group by Clause:
Q. Display the name of all the colleges with their maximum marks.
SQL> select max(marks),clgname from result group by clgname;
MAX(MARKS) CLGNAME
---------- ----------
73 OIST
82 OCM
80 OCT

Q. Display the name of all the colleges with their maximum marks in each
semester.
SQL> select max(marks),clgname,sem from result group by clgname,sem;
MAX(MARKS) CLGNAME SEM
---------- ---------- ---
79 OCT II
73 OIST II
82 OCM II
80 OCT I
76 OCM I
26
72 OIST I
Group by Clause:
Q. Find the college name with their maximum marks but only those colleges,
having average marks greater than 74.
SQL> select max(marks),clgname from result group by clgname having
avg(marks)>74;
MAX(MARKS) CLGNAME
---------- ----------
82 OCM
80 OCT

Q. Find maximum marks of all colleges those are having at least 3 records.
SQL> select max(marks),clgname from result group by clgname having
count(clgname)>2;

MAX(MARKS) CLGNAME
---------- ----------
73 OIST
80 OCT
27
Order by Clause:
Q. Display the maximum marks of each college in ascending order.
SQL> select max(marks),clgname from result group by clgname order by
clgname;
MAX(MARKS) CLGNAME
---------- ---------- ---
82 OCM
80 OCT
73 OIST
Q. Find the maximum marks of all colleges with their name & semester in
ordered list.
SQL> select max(marks),clgname,sem from result group by clgname,sem order by
clgname;
MAX(MARKS) CLGNAME SEM
---------- ---------- ---
76 OCM I
82 OCM II
80 OCT I
79 OCT II
72 OIST I 28
73 OIST II
Order by Clause:
Q. Find the maximum marks of all colleges with their name in ascending but
semester in descending order.

SQL> select max(marks),clgname,sem from result group by clgname,sem order by

clgname asc, sem desc;

MAX(MARKS) CLGNAME SEM

---------- ---------- ---
82 OCM II
76 OCM I
79 OCT II
80 OCT I
73 OIST II
72 OIST I

29
Complex Queries:
Consider relations:
Emp (eid, ename, dno)
Dept (dno, dname)
Sal (eid, salary)

SQL> create table emp (eid number(10) primary key, ename char(20), dno
varchar2(10) references dept);

SQL> create table dept (dno varchar2(10) primary key, dname char(20));

SQL> create table sal(eid number(10) references emp, salary number(10),

primary key(eid));

30
Complex Queries:
SQL> select *from emp;

EID ENAME DNO

---------- -------------------- ----------
1 Ajay D01
2 Ajit D02
3 Amit D02
4 Aman D03
5 Aman D03
6 Anay D01
7 Anant D02

31
Complex Queries:
SQL> select *from dept; SQL> select *from sal;

DNO DNAME EID SALARY

---------- ---------- ---------- ----------
D01 Admin 1 40000
D02 Finance 2 35000
D03 HR 3 35000
4 32000
5 47000
6 43000
7 52000

32
Complex Queries:
Q. Find those employee who works in HR Department.

Q. Display name of employees starts with ‘A’.

Q. Display those name that contains ‘n’ somewhere.

Q. Find details of those employees whose salary is more than 40000.

Q. Find the name of employee who earns minimum salary.

Q. Find department name that has highest salary.

Q. Find the average salary of each department.

Q. Find those department name that have at least 3 employees.

Q. Display the employee name in order but department in descending

order.
33
Complex Queries:
Emp (empname, street, city)
Works (empname , compname, salary)
Company (compname, street, city)

Q. Find all employees who earn more salary than every employee of
‘ABC Cooperation’.

Q. Find name and street address of those employee who works in HCL.

Q. Find all company names located in every city in which ‘ABC

Cooperation’ is located

34
Complex Queries:
emp1 (ssn, name)
sal1 (ssn, salary) // ssn referencing to emp
works1 (project#, ssn) // project# referencing to project & ssn referencing to emp
proj1 (project#, project_name, location)

Q. Display the projects name at Delhi.

SQL> select pname from proj1 where location='Delhi’;

PNAME

----------

HR
Complex Queries:
Q. Find the project name of employees whose salary is greater than 50000.

SQL> select pname from proj1 where [Link]# in(select [Link]# from
works1 where [Link] in(select [Link] from sal1 where salry>50000));
PNAME
----------
Finance
Account

SQL> select pname from sal1,works1,proj1 where salry>50000 and [Link]#

=[Link]# and [Link]=[Link];
PNAME
----------
Finance
Account
Complex Queries:
Q. Retrieve the name and ssn of those employees working on project no ‘P101’.

SQL> select [Link], [Link] from emp1 where [Link] in(select [Link]
from works1 where project#='P01');
SSN NAME
---------- --------------------
1 Amit
4 Sumit

SQL> select [Link], [Link] from emp1,works1 where [Link]=[Link]

and project#='P01';

SSN NAME
---------- --------------------
1 Amit
4 Sumit
Complex Queries:
Q. Find the employees name working on project no P201 and salary>45000.

SQL> select [Link] from emp1 where [Link] in(select [Link] from works1
where project#=‘P201’ and [Link] in(select [Link] from sal1 where
salary>45000));

SQL> select [Link] from emp1,works1,sal1 where [Link]=[Link]

[Link]=[Link] and project#=‘P201’ and salary>45000;

Q. Find the employees name with maximum salary.

SQL> select [Link] from emp1 where [Link] in(select [Link] from sal1 where
salary in(select max(salary) from sal1));

SQL> select max(salary) from emp1,sal1 where [Link]=[Link];

Modification of the Database

• The content of the database may be modified using the following

operations:
• Deletion
• Insertion
• Updating

• All these operations are expressed using the assignment operator.

Deletion:

• A delete request is expressed similarly to a query, except instead of

displaying tuples to the user, the selected tuples are removed from
the database.

• Can delete only whole tuples; cannot delete values on only particular
attributes

• A deletion is expressed in relational algebra by:

rr–E
where r is a relation and E is a relational algebra query.
Deletion Examples
customer (customer-name, customer-street, customer-only)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
• Delete all account records in the Perryridge branch.
account  account – branch-name = “Perryridge” (account)

• Delete all loan records with amount in the range of 0 to 50

loan  loan –  amount 0 and amount  50 (loan)
Insertion

• To insert data into a relation, we either:

• specify a tuple to be inserted
• write a query whose result is a set of tuples to be inserted

• in relational algebra, an insertion is expressed by:

r r  E
where r is a relation and E is a relational algebra expression.

• The insertion of a single tuple is expressed by letting E be a constant

relation containing one tuple.
Insertion Examples
customer (customer-name, customer-street, customer-only)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)

• Insert information in the database specifying that Smith has $1200 in

account A-973 at the Perryridge branch.

• account  account  {(“Perryridge”, A-973, 1200)}

• depositor  depositor  {(“Smith”, A-973)}
Updating

• A mechanism to change a value in a tuple without charging all values

in the tuple

• Use the generalized projection operator to do this task

r   F1, F2, …, FI, (r)

• Each Fi is either
• the ith attribute of r, if the ith attribute is not updated, or,
• if the attribute is to be updated Fi is an expression, involving only constants
and the attributes of r, which gives the new value for the attribute
 Update Examples

customer (customer-name, customer-street, customer-only)

account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)

• Make interest payments by increasing all balances by 5 percent.

account   AN, BN, BAL * 1.05 (account)

where AN, BN and BAL stand for account-number, branch-name

and balance, respectively.

• Pay all accounts with balances over $10,000 6 percent interest

Update Examples

customer (customer-name, customer-street, customer-only)

account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)

• Pay all accounts with balances over $10,000 6 percent interest

and pay all others 5 percent
account   AN, BN, BAL * 1.06 ( BAL  10000 (account))
 AN, BN, BAL * 1.05 (BAL  10000 (account))
Views
• In some cases, it is not desirable for all users to see the entire logical
model (i.e., all the actual relations stored in the database.)

• Consider a person who needs to know a customer’s loan number but

has no need to see the loan amount. This person should see a
relation described, in the relational algebra, by
customer-name, loan-number (borrower loan)

• Any relation that is not of the conceptual model but is made visible
to a user as a “virtual relation” is called a view.
View Definition
• A view is defined using the create view statement which has the form
create view v as <query expression>
where <query expression> is any legal relational algebra query
expression. The view name is represented by v.

• Once a view is defined, the view name can be used to refer to the
virtual relation that the view generates.

• View definition is not the same as creating a new relation by

evaluating the query expression
• Rather, a view definition causes the saving of an expression; the expression is
substituted into queries using the view.
View Examples
• Consider the view (named all-customer) consisting of branches and
their customers.
create view all-customer as
branch-name, customer-name (depositor account)
 branch-name, customer-name (borrower loan)

• We can find all customers of the Perryridge branch by writing:

customer-name (branch-name = “Perryridge” (all-customer))
Updates Through View

• Consider the person who needs to see all loan data in the loan
relation except amount. The view given to the person, branch-loan,
is defined as:
create view branch-loan as
branch-name, loan-number (loan)

• Since we allow a view name to appear wherever a relation name is

allowed, the person may write:

branch-loan  branch-loan  {(“Perryridge”, L-37)}

Thank You

51