Notes of Unit - II DBMS[By Dr K.S.

Mishra]
Program: MCA
MIET,Meerut

Unit-II(DBMS KCA-204, MCA II Sem)

Relational Model
Relational Model (RM) represents the database as a collection of relations. A
relation is nothing but a table of values. Every row in the table represents a
collection of related data values. These rows in the table denote a real-world entity
or relationship.

The table name and column names are helpful to interpret the meaning of values in
each row. The data are represented as a set of relations. In the relational model,
data are stored as tables. However, the physical storage of the data is independent
of the way the data are logically organized.

Some popular Relational Database management systems are:

● DB2 and Informix Dynamic Server - IBM

● Oracle and RDB – Oracle
● SQL Server and Access - Microsoft

Relational Model Concepts

1. Attribute: Each column in a Table. Attributes are the properties which define
a relation. e.g., Student_Rollno , NAME ,etc.
2. Tables – In the Relational model the relations are saved in the table format.
It is stored along with its entities. A table has two properties rows and
columns. Rows represent records and columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single
record.
4. Relation Schema: A relation schema represents the name of the relation
with its attributes.
5. Degree: The total number of attributes which in the relation is called the
degree of the relation.
 6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS
system. Relation instances never have duplicate tuples.
9. Relation key - Every row has one, two or multiple attributes, which is called
relation key.
10.Attribute domain – Every attribute has some pre-defined value and scope
which is known as attribute domain

Relational Integrity Constraints

Relational Integrity constraints in DBMS are referred to conditions which must be
present for a valid relation. These Relational constraints in DBMS are derived from
the rules in the mini-world that the database represents.

There are many types of Integrity Constraints in DBMS. Constraints on the

Relational database management system is mostly divided into three main
categories are:

1. Domain Constraints
2. Key Constraints
3. Referential Integrity Constraints

Domain Constraints
Domain constraints can be violated if an attribute value is not appearing in the
corresponding domain or it is not of the appropriate data type.

Domain constraints specify that within each tuple, and the value of each attribute
must be unique. This is specified as data types which include standard data types
integers, real numbers, characters, Booleans, variable length strings, etc.

Example:
Create DOMAIN CustomerName
CHECK (value not NULL)

The example shown demonstrates creating a domain constraint such that

CustomerName is not NULL

Key Constraints

An attribute that can uniquely identify a tuple in a relation is called the key of the
table. The value of the attribute for different tuples in the relation has to be unique.

Example:

In the given table, CustomerID is a key attribute of Customer Table. It is most likely
to have a single key for one customer, CustomerID =1 is only for the CustomerName
=" Google".

CustomerID CustomerName Status

1 Google Active

2 Amazon Active

3 Apple Inactive
Referential Integrity Constraints

Referential Integrity constraints in DBMS are based on the concept of Foreign Keys.
A foreign key is an important attribute of a relation which should be referred to in
other relationships. Referential integrity constraint state happens where relation
refers to a key attribute of a different or same relation. However, that key element
must exist in the table.

Example:

In the above example, we have 2 relations, Customer and Billing.

Tuple for CustomerID =1 is referenced twice in the relation Billing. So we know

CustomerName=Google has billing amount $300

Operations in Relational Model

Four basic update operations performed on relational database model are

Insert, update, delete and select.

 ● Insert is used to insert data into the relation
● Delete is used to delete tuples from the table.
● Update allows you to change the values of some attributes in existing tuples.
● Select allows you to choose a specific range of data.

Whenever one of these operations are applied, integrity constraints specified on

the relational database schema must never be violated.

Insert Operation

The insert operation gives values of the attribute for a new tuple which should be
inserted into a relation.

Update Operation

You can see that in the below-given relation table CustomerName= 'Apple' is
updated from Inactive to Active.

Delete Operation

To specify deletion, a condition on the attributes of the relation selects the tuple to
be deleted.
In the above-given example, CustomerName= "Apple" is deleted from the table.

The Delete operation could violate referential integrity if the tuple which is deleted
is referenced by foreign keys from other tuples in the same database.

Select Operation

In the above-given example, CustomerName="Amazon" is selected

Relational Algebra:- RELATIONAL ALGEBRA is a widely used

procedural query language. It collects instances of relations as
input and gives occurrences of relations as output. It uses
various operations to perform this action. SQL Relational
algebra query operations are performed recursively on a
relation. The output of these operations is a new relation,
which might be formed from one or more input relations.

Relational Algebra Operations:-

Unary Relational Operations

● SELECT (symbol: σ)
● PROJECT (symbol: π)
● RENAME (symbol: ρ)
Relational Algebra Operations From Set Theory

● UNION (υ)
● INTERSECTION ( ),
 ● DIFFERENCE (-)
● CARTESIAN PRODUCT ( x )

Binary Relational Operations

● JOIN
● DIVISION

Let's study them in detail with solutions:

SELECT (σ)

The SELECT operation is used for selecting a subset of

the tuples according to a given selection condition.
Sigma(σ)Symbol denotes it. It is used as an expression to
choose tuples which meet the selection condition. Select
operator selects tuples that satisfy a given predicate.
σp(r)

σ is the selection operation

r stands for relation which is the name of the table

p is prepositional logic

Example 1
σ topic = "Database" (Tutorials)

Output - Selects tuples from Tutorials where topic =

'Database'.
Example 2
σ ( Tutorials)
topic = "Database" and author = "guru99"

Output - Selects tuples from Tutorials where the topic is

'Database' and 'author' is guru99.

Example 3
σ sales > 50000 (Customers)

Output - Selects tuples from Customers where sales is greater

than 50000
Projection(π)

The projection eliminates all attributes of the input relation

but those mentioned in the projection list. The projection
method defines a relation that contains a vertical subset of
Relation.

This helps to extract the values of specified attributes to

eliminates duplicate values. (pi) symbol is used to choose
attributes from a relation. This operator helps you to keep
specific columns from a relation and discards the other
columns.

Example of Projection:

Consider the following table

CustomerID CustomerNam Status
e

1 Google Active

2 Amazon Active

3 Apple Inactiv
e

4 Alibaba Active

Here, the projection of CustomerName and status will give

Π CustomerName, Status (Customers)
CustomerName Status

Google Active

Amazon Active

Apple Inactive

Alibaba Active
Rename (ρ)

Rename is a unary operation used for renaming output of

sequence of operations as a relation.

ρ X(R )will rename the attribute R as X '.

Union operation (υ)

UNION is symbolized by ∪ symbol. It includes all tuples that

are in tables A or in B. It also eliminates duplicate tuples. So,
set A UNION set B would be expressed as:

The result <- A ∪ B

For a union operation to be valid, the following conditions

must hold -

● R and S must be the same number of attributes.

● Attribute domains need to be compatible.
● Duplicate tuples should be automatically removed.

Example

Consider the following tables.

Table A Table B

colum colum colum colum

n1 n2 n1 n2
1 1 1 1

1 2 1 3

A ∪ B gives

Table A ∪ B

column column
1 2

1 1

1 2

1 3

Set Difference (-)

- Symbol denotes it. The result of A - B, is a relation which

includes all tuples that are in A but not in B.

● The attribute name of A has to match with the attribute

name in B.
● The two-operand relations A and B should be either
compatible or Union compatible.
 ● It should be defined relation consisting of the tuples that
are in relation A, but not in B.

Example
A-B
Table A – B

column column
1 2

1 2

Intersection

An intersection is defined by the symbol ∩

A∩B

Defines a relation consisting of a set of all tuple that are in

both A and B. However, A and B must be union-compatible.

Visual
Definition of Intersection
Example:
A ∩ B
Table A ∩ B

column column
1 2

1 1

Cartesian Product(X) in DBMS

Cartesian Product in DBMS is an operation used to merge

columns from two relations. Generally, a cartesian product is
never a meaningful operation when it performs alone.
However, it becomes meaningful when it is followed by other
operations. It is also called Cross Product or Cross Join.

Example – Cartesian product

σ column 2 = '1' (A X B)

Output – The above example shows all rows from relation A

and B whose column 2 has value 1

σ column 2 = '1'
(A X B)
column column
1 2

1 1

1 1

Join Operations

Join operation is essentially a cartesian product followed by a

selection criterion.

Join operation denoted by ⋈.

JOIN operation also allows joining variously related tuples

from different relations.

Types of JOIN:

Various forms of join operation are:

Inner Joins:

● Theta join
● EQUI join
● Natural join

Outer join:

● Left Outer Join

 ● Right Outer Join
● Full Outer Join

Inner Join:

In an inner join, only those tuples that satisfy the matching

criteria are included, while the rest are excluded. Let's study
various types of Inner Joins:

Theta Join:

The general case of JOIN operation is called a Theta join. It is

denoted by symbol θ

Example

A ⋈θ B

Theta join can use any conditions in the selection criteria.

For example:

A ⋈ A.column 2 > B.column 2 (B)

A ⋈ A.column 2 >
B.column 2 (B)

column 1 column 2

1 2
EQUI join:

When a theta join uses only equivalence condition, it becomes

a equi join.

For example:

A ⋈ A.column 2 = B.column 2 (B)

A ⋈ A.column 2 =
B.column 2 (B)

column 1 column 2

1 1

EQUI join is the most difficult operations to implement

efficiently using SQL in an RDBMS and one reason why RDBMS
have essential performance problems.

NATURAL JOIN (⋈)

Natural join can only be performed if there is a common

attribute (column) between the relations. The name and type
of the attribute must be same.

Example

Consider the following two tables

 C

Num Square

2 4

3 9

Num Cube

2 8

3 27

C ⋈ D
C⋈D

Num Square Cube

2 4 8
3 9 27

OUTER JOIN

In an outer join, along with tuples that satisfy the matching

criteria, we also include some or all tuples that do not match
the criteria.

Left Outer Join(A B)

In the left outer join, operation allows keeping all tuple in the
left relation. However, if there is no matching tuple is found in
right relation, then the attributes of right relation in the join
result are filled with null values.

Consider the following 2 Tables

Num Square

2 4
3 9

4 16

Num Cube

2 8

3 18

5 75

A B

Num Square Cube

2 4 8

3 9 18

4 16 NULL
Right Outer Join: ( A B)

In the right outer join, operation allows keeping all tuple in the
right relation. However, if there is no matching tuple is found
in the left relation, then the attributes of the left relation in the
join result are filled with null values.

A B
A⋈B

Num Cube Square

2 8 4

3 18 9

5 75 NULL

Full Outer Join: ( A B)

In a full outer join, all tuples from both relations are included
in the result, irrespective of the matching condition.
A B
A⋈B

Num Square Cube

2 4 8

3 9 18

4 16 NULL

5 NULL 75

Summary

Operation(Symbols) Purpose

Select(σ) The SELECT operation is used

for selecting a subset of the tuples
according to a given selection
condition

Projection(π) The projection eliminates all

attributes of the input relation but
those mentioned in the projection
list.
Union Operation(∪) UNION is symbolized by symbol.
It includes all tuples that are in
tables A or in B.

Set Difference(-) - Symbol denotes it. The result of

A - B, is a relation which includes
all tuples that are in A but not in
B.

Intersection(∩) Intersection defines a relation

consisting of a set of all tuple that
are in both A and B.

Cartesian Product(X) Cartesian operation is helpful to

merge columns from two
relations.

Inner Join Inner join, includes only those

tuples that satisfy the matching
criteria.

Theta Join(θ) The general case of JOIN

operation is called a Theta join. It
is denoted by symbol θ.

EQUI Join When a theta join uses only

equivalence condition, it becomes
a equi join.
Natural Join(⋈) Natural join can only be
performed if there is a common
attribute (column) between the
relations.

Outer Join In an outer join, along with tuples

that satisfy the matching criteria.

Left Outer Join( ) In the left outer join, operation

allows keeping all tuple in the left
relation.

Right Outer join( ) In the right outer join, operation

allows keeping all tuple in the
right relation.

Full Outer Join( ) In a full outer join, all tuples from

both relations are included in the
result irrespective of the matching
condition.

Extended operators are those operators which can be

derived from basic operators.There are mainly three types
of extended operators in Relational Algebra:
● Join

● Intersection

● Divide

The relations used to understand extended operators are

STUDENT, STUDENT_SPORTS, ALL_SPORTS and
EMPLOYEE which are shown in Table 1, Table 2, Table 3
and Table 4 respectively.

STUDENT
ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 9156768971 18

Table 1
STUDENT_SPORTS
ROLL_NO SPORTS

1 Badminton

2 Cricket

2 Badminton

4 Badminton

Table 2

ALL_SPORTS
SPORTS

Badminton

Cricket
Table 3

EMPLOYEE
AG
EMP_NO NAME ADDRESS PHONE E

1 RAM DELHI 9455123451 18

5 NARESH HISAR 9782918192 22

6 SWETA RANCHI 9852617621 21

4 SURESH DELHI 9156768971 18

Table 4

Intersection (∩): Intersection on two relations R1 and R2

can only be computed if R1 and R2 are union
compatible (These two relation should have same
number of attributes and corresponding attributes in two
relations have same domain). Intersection operator when
applied on two relations as R1∩R2 will give a relation with
tuples which are in R1 as well as R2. Syntax:
Relation1 ∩ Relation2
Example: Find a person who is student as well
as employee- STUDENT ∩ EMPLOYEE
In terms of basic operators (union and minus) :
STUDENT ∩ EMPLOYEE = STUDENT + EMPLOYEE -
(STUDENT U EMPLOYEE)
RESULT:
AG
ROLL_NO NAME ADDRESS PHONE E

1 RAM DELHI 9455123451 18

4 SURESH DELHI 9156768971 18

Conditional Join(⋈c): Conditional Join is used when you

want to join two or more relation based on some
conditions. Example: Select students whose ROLL_NO is
greater than EMP_NO of employees
STUDENT⋈c STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE
In terms of basic operators (cross product and selection) :
σ (STUDENT.ROLL_NO>EMPLOYEE.EMP_NO)(STUDENT×EMPLOYEE)
RESULT:
A A
ROLL NAM ADDR G EMP NA ADDR G
_NO E ESS PHONE E _NO ME ESS PHONE E

RAM GURG 965243 RA 945512

2 ESH AON 1543 18 1 M DELHI 3451 18

SUJI ROHT 915625 RA 945512

3 T AK 3131 20 1 M DELHI 3451 18
 SUR 915676 RA 945512
4 ESH DELHI 8971 18 1 M DELHI 3451 18

Equijoin(⋈): Equijoin is a special case of conditional

join where only equality condition holds between a pair of
attributes. As values of two attributes will be equal in result
of equijoin, only one attribute will be appeared in result.
Example:Select students whose ROLL_NO is equal to
EMP_NO of employees
STUDENT⋈STUDENT.ROLL_NO=EMPLOYEE.EMP_NOEMPLOYEE
In terms of basic operators (cross product, selection and
projection) :
∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE
(σ
EMPLOYEE.NAME, EMPLOYEE.ADDRESS, EMPLOYEE.PHONE, EMPLOYEE>AGE)
(STUDENT.ROLL_NO=EMPLOYEE.EMP_NO) (STUDENT×EMPLOYEE))
RESULT:
ROLL_ ADDRE AG ADDRE AG
NO NAME SS PHONE E NAME SS PHONE E

9455123 9455123
1 RAM DELHI 451 18 RAM DELHI 451 18

SURE 9156768 SURE 9156768

4 SH DELHI 971 18 SH DELHI 971 18

Natural Join(⋈): It is a special case of equijoin in which

equality condition hold on all attributes which have same
name in relations R and S (relations on which join
operation is applied). While applying natural join on two
relations, there is no need to write equality condition
explicitly. Natural Join will also return the similar attributes
only once as their value will be same in resulting relation.
Example: Select students whose ROLL_NO is equal to
ROLL_NO of STUDENT_SPORTS as:
STUDENT⋈STUDENT_SPORTS
In terms of basic operators (cross product, selection and
projection) :
∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE
(σ (STUDENT.ROLL_NO=STUDENT_SPORTS.ROLL_NO)
STUDENT_SPORTS.SPORTS)
(STUDENT×STUDENT_SPORTS))
RESULT:
ROLL_ ADDRE AG SPORT
NO NAME SS PHONE E S

9455123 Badmint
1 RAM DELHI 451 18 on

RAME GURGA 9652431

2 SH ON 543 18 Cricket

RAME GURGA 9652431 Badmint

2 SH ON 543 18 on

SURE 9156768 Badmint

4 SH DELHI 971 18 on
Natural Join is by default inner join because the tuples
which does not satisfy the conditions of join does not
appear in result set. e.g.; The tuple having ROLL_NO 3 in
STUDENT does not match with any tuple in
STUDENT_SPORTS, so it has not been a part of result
set.
Left Outer Join(⟕): When applying join on two relations
R and S, some tuples of R or S does not appear in result
set which does not satisfy the join conditions. But Left
Outer Joins gives all tuples of R in the result set. The
tuples of R which do not satisfy join condition will have
values as NULL for attributes of S.
Example:Select students whose ROLL_NO is greater than
EMP_NO of employees and details of other students as
well
STUDENT⟕STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE
RESULT
ROL A ADD A
L_N NAM ADD PHON G EMP NA RES PHON G
O E RESS E E _NO ME S E E

GUR
RAM GAO 96524 RA DEL 94551
2 ESH N 31543 18 1 M HI 23451 18

SUJI ROH 91562 RA DEL 94551

3 T TAK 53131 20 1 M HI 23451 18

SUR DELH 91567 RA DEL 94551

4 ESH I 68971 18 1 M HI 23451 18

N
DELH 94551 NUL NU UL
1 RAM I 23451 18 L LL NULL NULL L

Right Outer Join(⟖): When applying join on two relations

R and S, some tuples of R or S does not appear in result
set which does not satisfy the join conditions. But Right
Outer Joins gives all tuples of S in the result set. The
tuples of S which do not satisfy join condition will have
values as NULL for attributes of R.
Example: Select students whose ROLL_NO is greater
than EMP_NO of employees and details of other
Employees as well
STUDENT⟖STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE
RESULT:
A
ROLL NAM ADDR PHON AG EMP NAM ADDR PHON G
_NO E ESS E E _NO E ESS E E

RAM GURG 965243 945512

2 ESH AON 1543 18 1 RAM DELHI 3451 18

SUJI ROHT 915625 945512

3 T AK 3131 20 1 RAM DELHI 3451 18

SUR 915676 945512

4 ESH DELHI 8971 18 1 RAM DELHI 3451 18

NU NAR HISA 978291

NULL NULL NULL NULL LL 5 ESH R 8192 22

NU SWE RANC 985261

NULL NULL NULL NULL LL 6 TA HI 7621 21

NU SUR 915676
NULL NULL NULL NULL LL 4 ESH DELHI 8971 18

FullOuter Join(⟗): When applying join on two relations

R and S, some tuples of R or S does not appear in result
set which does not satisfy the join conditions. But Full
Outer Joins gives all tuples of S and all tuples of R in the
result set. The tuples of S which do not satisfy join
condition will have values as NULL for attributes of R and
vice versa.
Example:Select students whose ROLL_NO is greater than
EMP_NO of employees and details of other Employees as
well and other Students as well
STUDENT⟗STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE
RESULT:
ROLL NAM ADDR PHON AG EMP NAM ADDR PHON AG
_NO E ESS E E _NO E ESS E E

RAM GURG 965243 DELH 945512

2 ESH AON 1543 18 1 RAM I 3451 18

SUJI ROHT 915625 DELH 945512

3 T AK 3131 20 1 RAM I 3451 18

SUR 915676 DELH 945512

4 ESH DELHI 8971 18 1 RAM I 3451 18

NU NAR HISA 978291

NULL NULL NULL NULL LL 5 ESH R 8192 22

NU SWE RANC 985261

NULL NULL NULL NULL LL 6 TA HI 7621 21

NU SUR DELH 915676

NULL NULL NULL NULL LL 4 ESH I 8971 18

945512 NUL NU
1 RAM DELHI 3451 18 NULL L NULL NULL LL

Division Operator (÷): Division operator A÷B can be

applied if and only if:
● Attributes of B is proper subset of Attributes of A.

● The relation returned by division operator will have

attributes = (All attributes of A – All Attributes of B)

 ● The relation returned by division operator will return
those tuples from relation A which are associated to
every B’s tuple.
Consider the relation STUDENT_SPORTS and
ALL_SPORTS given in Table 2 and Table 3 above.
To apply division operator as
STUDENT_SPORTS÷ ALL_SPORTS
● The operation is valid as attributes in ALL_SPORTS
is a proper subset of attributes in
STUDENT_SPORTS.
● The attributes in resulting relation will have
attributes
{ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO
● The tuples in resulting relation will have those
ROLL_NO which are associated with all B’s tuple
{Badminton, Cricket}. ROLL_NO 1 and 4 are
associated to Badminton only. ROLL_NO 2 is
associated to all tuples of B. So the resulting
relation will be:
ROLL_NO

2
Q1.Consider the banking database :
Customer(Cust_Name, Cust_Street,Cust_City) ,
Branch(Branch_Name,Branch_City,Assets), Account(Branch_
Name, Account__Number, Balance) , Loan(Branch_Name,
LoanNo, Amount), Depositor(Cust_Name,Account_Number),
Borrower(Cust_Name, Loan_No)
Write Relational Algebra query for finding names of all the
customers who have taken a loan from & also have an account at
the bank.

Solution:-
Step 1: Identify the relations that would be required to frame the
resultant query.
First half of the query : (Names of customers who have taken
loan)
So, we get it from Borrower table

Second Half of query :(Customer_Name& A/C No)

So this we get it from table depositor

Step 2: Identify the columns which you require from the tables
obtained in step 1 .
Column1: Customer_Name from Borrower
So we get it from ∏ Customer_Name (Borrower)
Column2 : Customer_Name from Depositor
∏ Customer_Name (Depositor)

Step 3: Identify the operator to be used, we need to find out the

names of customers who are present in both Borrower table &
Depositor table . Intersection is the required operator.
So the query is ∏ Customer_Name (Borrower)∩∏ Customer_Name
(Depositor)

Q2. Consider the following database :

Employee(eid, Name, Salary, did, m-did), Project(pid,

description), Works_On(eid, pid, hours), Departments(did,
location)
(i) List the names of the project that have employees from the
systems department working less than 5 hours. Pid is also the
name of the project.
Ans: ∏ pid (σ hours<5 (Works_On)⋈ σ did=’Systems’
(Employee))

(ii) List names of employees having salary

greater than manager’s salary
Ans: e:= employee, m:= employee

∏ e.Name (σ e.salary > m.salary (e ⋈ e.m-did = m.eid m))

(iii) List the names of employees working on all projects)

Ans : ∏ Name (( ∏ eid,pid(works_On)÷ ∏ pid
(Project))⋈ employee)
(iv) List the names of employees making more than dollar
100,00 and working on zero projects.
Ans : 𝑡1← ∏ eid (σSalary> 100000 (employee))-∏ eid
(Works_On)
∏ Name(t1 ⋈ employee)
(V) List the names of employees working on both projectX
& projectY
Ans: 𝑡1← ∏ eid (σpid = ‘projectX’ (Works_On))∩
∏ eid (σpid = ‘projectY’ (Works_On))
∏Name(t1 ⋈ employee)
Relational Calculus
Relational calculus is a non-procedural query language, and
instead of algebra, it uses mathematical predicate calculus. The
relational calculus is not the same as that of differential and
integral calculus in mathematics but takes its name from a branch
of symbolic logic termed as predicate calculus. When applied to
databases, it is found in two forms. These are

● Tuple relational calculus which was originally proposed by

Codd in the year 1972 and
● Domain relational calculus which was proposed by Lacroix
and Pirotte in the year 1977
In first-order logic or predicate calculus, a predicate is a
truth-valued function with arguments. When we replace with
values for the arguments, the function yields an expression, called
a proposition, which will be either true or false.
Example:
For example, steps involved in listing all the employees who
attend the 'Networking' Course would be:
SELECT the tuples from COURSE relation with
COURSENAME = 'NETWORKING'

PROJECT the COURSE_ID from above result

SELECT the tuples from EMP relation with COURSE_ID resulted
above.

Tuple Relational Calculus

In the tuple relational calculus, you will have to find tuples for
which a predicate is true. The calculus is dependent on the use of
tuple variables. A tuple variable is a variable that 'ranges over' a
named relation: i.e., a variable whose only permitted values are
tuples of the relation. It uses logical connectives ∧ (and), ∨
(or) and ┓ (not).
Example:
For example, to specify the range of a tuple variable S as the
Staff relation, we write:
Staff(S)

To express the query 'Find the set of all tuples S such that F(S) is
true,' we can write:
{S | F(S)}

Here, F is called a formula (well-formed formula, or wff in

mathematical logic). For example, to express the query 'Find the
staffNo, fName, lName, position, sex, DOB, salary, and branchNo
of all staff earning more than £10,000', we can write:
{S | Staff(S) ∧ S.salary > 10000}

Example:
{t | TEACHER(t) and t.SALARY>20000}

- It implies that it selects the tuples from the TEACHER in such a

way that the resulting teacher tuples will have a salary higher than
20000. This is an example of selecting a range of values.
{t | TEACHER (t) AND t.DEPT_ID = 8}

- it selects all the tuples of teachers' names who work under

Department 8. Any tuple variable with 'For All' (∀) or 'there exists'
(∃) condition is termed as a bound variable. In the last example,
for any range of values of SALARY greater than 20000, the
meaning of the condition does not alter. Bound variables are
those ranges of tuple variables whose meaning will not alter if
another tuple variable replaces the tuple variable.
In the second example, you have used DEPT_ID= 8, which
means only for DEPT_ID = 8 display the teacher details. Such a
variable is called a free variable. Any tuple variable without any
'For All' or 'there exists' condition is called Free Variable.
In TRC, we can use Existential (∃) and Universal
Quantifiers (∀).
For example:
{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.
name)}

Table: Student

First_Name Last_Name Age

---------- --------- ----
Ajeet Singh 30
Chaitanya Singh 31
Rajeev Bhatia 27
Carl Pratap 28
Lets write relational calculus queries.

Q1.write tuple relational Query to display the last name of those

students where age is greater than 30

Sol:-
{ t.Last_Name | Student(t) AND t.age > 30 }
In the above query you can see two parts separated by | symbol.
The second part is where we define the condition and in the first
part we specify the fields which we want to display for the
selected tuples.

The result of the above query would be:

Last_Name
---------
Singh
Q2. Write Query using relational tuple calculus to display all the
details of students where Last name is ‘Singh’
{ t | Student(t) AND t.Last_Name = 'Singh' }
Output:

First_Name Last_Name Age

---------- --------- ----
Ajeet Singh 30
Chaitanya Singh 31

Domain Relational Calculus

In the tuple relational calculus, you use variables that have a
series of tuples in a relation. In the domain relational calculus, you
will also use variables, but in this case, the variables take their
values from domains of attributes rather than tuples of relations. A
domain relational calculus expression has the following general
format:
{d1, d2, . . . , dn | F(d1, d2, . . . , dm)} m ≥ n

where d1, d2, . . . , dn, . . . , dm stand for domain variables and

F(d1, d2, . . . , dm) stands for a formula composed of atoms.
Domain relational calculus uses the same operators
as tuple calculus. It uses logical connectives ∧ (and),
∨ (or) and ┓ (not).

It uses Existential (∃) and Universal Quantifiers (∀)

to bind the variable.

Notation:
1. { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
Where
a1, a2 are attributes
P stands for formula built by inner attributes
For example:
{< article, page, subject > | ∈ javatpoint ∧ subject = '
database'}
Output: This query will yield the article, page, and
subject from the relational javatpoint, where the subject
is a database.

It is to be noted that these queries are safe. The use domain

relational calculus is restricted to safe expressions; moreover, it is
equivalent to the tuple relational calculus, which in turn is similar
to the relational algebra.

Introduction to SQL
o SQL stands for Structured Query Language. It is
used for storing and managing data in relational
database management system (RDMS).
o It is a standard language for Relational Database
System. It enables a user to create, read, update
and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS
Access and SQL Server use SQL as their standard
database language.
o SQL allows users to query the database in a number
of ways, using English-like statements.

Rules:

SQL follows the following rules:

o Structure query language is not case sensitive.
Generally, keywords of SQL are written in uppercase.
 o Statements of SQL are dependent on text lines. We
can use a single SQL statement on one or multiple
text line.
o Using the SQL statements, you can perform most of
the actions in a database.
o SQL depends on tuple relational calculus and
relational algebra.

SQL process:

o When an SQL command is executing for any RDBMS,

then the system figure out the best way to carry out
the request and the SQL engine determines that how
to interpret the task.
o In the process, various components are included.
These components can be optimization Engine,
Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic
query engine, but SQL query engine won't handle
logical files.
Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database
management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and
manipulate it when needed.
o SQL is used to create and drop the database and
table.
 o SQL is used to create a view, stored procedure,
function in a database.
o SQL allows users to set permissions on tables,
procedures, and views.

Need of SQL :
● It is widely used in the Business Intelligence tool.
● Data Manipulation and data testing are done through
SQL.
● Data Science tools depend highly on SQL. Big data tools
such as Spark, Impala are dependant on SQL.
● It is one of the demanding industrial skills.
Advantages of SQL :
SQL has many advantages which makes it popular and highly
demanded. It is a reliable and efficient language used for
communicating with the database. Some advantages of SQL are
as follows:
1. Faster Query Processing –
Large amount of data is retrieved quickly and efficiently.
Operations like Insertion, deletion, manipulation of data is
also done in almost no time.
2. No Coding Skills –
For data retrieval, large number of lines of code is not
required. All basic keywords such as SELECT, INSERT
INTO, UPDATE, etc are used and also the syntactical
rules are not complex in SQL, which makes it a
user-friendly language.
3. Standardised Language –
Due to documentation and long establishment over years,
it provides a uniform platform worldwide to all its users.
 4. Portable –
It can be used in programs in PCs, server, laptops
independent of any platform (Operating System, etc).
Also, it can be embedded with other applications as per
need/requirement/use.
5. Interactive Language –
Easy to learn and understand, answers to complex
queries can be received in seconds.
6. Multiple data views –
Disadvantages of SQL :
Although SQL has many advantages, still there are a few
disadvantages.
Various Disadvantages of SQL are as follows:
1. Complex Interface –
SQL has a difficult interface that makes few users
uncomfortable while dealing with the database.
2. Cost –
Some versions are costly and hence, programmers
cannot access it.
3. Partial Control –
Due to hidden business rules, complete control is not
given to the database.
Applications of SQL :
● SQL is used by developers and DBAs (Database
Administrators) in writing Data Integration Scripts.
● It is used to deal with analytical queries to analyze the
data and get instincts from it.
● Retrieving Information
● Modification/Manipulation of data and database table such
as Insertion, Deletion and Updation .

SQL Data Types

SQL data types can be broadly divided into following categories.

1. Numeric data types such as int, tinyint, bigint, float, real etc.
2. Date and Time data types such as Date, Time, Datetime etc.
3. Character and String data types such as char, varchar, text etc.
4. Unicode character string data types, for example nchar, nvarchar, ntext
etc.
5. Binary data types such as binary, varbinary etc.
6. Miscellaneous data types – clob, blob, xml, cursor, table etc.

SQL Data Types important points

● Not all data types are supported by every relational database vendors.
For example, Oracle database doesn’t support DATETIME and MySQL
doesn’t support CLOB data type. So while designing database schema
 and writing sql queries, make sure to check if the data types are
supported or not.
● Data types listed here doesn’t include all the data types, these are the
most popularly used data types. Some relational database vendors have
their own data types that might be not listed here. For example,
Microsoft SQL Server has money and smallmoney data types but since
it’s not supported by other popular database vendors, it’s not listed here.
● Every relational database vendor has it’s own maximum size limit for
different data types, you don’t need to remember the limit. Idea is to
have the knowledge of what data type to be used in a specific scenario.

SQL Numeric Data Types

Datatyp
From To
e

bit 0 1

tinyint 0 255

smallint -32,768 32,767

int -2,147,483,648 2,147,483,647

-9,223,372,036, 9,223,372,036,
bigint
854,775,808 854,775,807

decimal -10^38 +1 10^38 -1

numeric -10^38 +1 10^38 -1

float -1.79E + 308 1.79E + 308

real -3.40E + 38 3.40E + 38

SQL Date and Time Data Types

Datatype Description

DATE Stores date in the format YYYY-MM-DD

TIME Stores time in the format HH:MI:SS

Stores date and time information in the format

DATETIME
YYYY-MM-DD HH:MI:SS

Stores number of seconds passed since the Unix

TIMESTAMP
epoch (‘1970-01-01 00:00:00’ UTC)

Stores year in 2 digit or 4 digit format. Range 1901

YEAR to 2155 in 4-digit format. Range 70 to 69,
representing 1970 to 2069.

SQL Character and String Data Types

Datatype Description
 Fixed length with maximum length of 8,000
CHAR
characters

Variable length storage with maximum length

VARCHAR
of 8,000 characters

Variable length storage with provided max

VARCHAR(max)
characters, not supported in MySQL

Variable length storage with maximum size of

TEXT
2GB data

Note that all the above data types are for character stream, they
should not be used with unicode data.

SQL Unicode Character and String Data Types

Datatype Description

Fixed length with maximum length of 4,000

NCHAR
characters

Variable length storage with maximum length

NVARCHAR
of 4,000 characters

Variable length storage with provided max

NVARCHAR(max)
characters
 Variable length storage with maximum size of
NTEXT
1GB data

Note that above data types are not supported in MySQL database.

SQL Binary Data Types

Datatype Description

Fixed length with maximum length of 8,000

BINARY
bytes

Variable length storage with maximum length

VARBINARY
of 8,000 bytes

Variable length storage with provided max

VARBINARY(max)
bytes

Variable length storage with maximum size

IMAGE
of 2GB binary data

SQL Miscellaneous Data Types

Datatyp
Description
e

CLOB Character large objets that can hold up to 2GB

BLOB For binary large objects

XML for storing xml data

JSON for storing JSON data

SQL: Literals
This SQL tutorial explains how to use literals (strings, integers,
decimals, and datetime values) in SQL with examples.

Description
In SQL, a literal is the same as a constant. We'll cover several
types of literals - string, integer, decimal, and datetime literals.

String Literals
String literals are always surrounded by single quotes (').
For example:

'TechOnTheNet.com'
'This is a literal'
'XYZ'
'123'

These string literal examples contain of strings enclosed in single

quotes.
Integer Literals
Integer literals can be either positive numbers or negative
numbers, but do not contain decimals. If you do not specify a
sign, then a positive number is assumed. Here are some
examples of valid integer literals:

536
+536
-536

Decimal Literals
Decimal literals can be either positive numbers or negative
numbers and contain decimals. If you do not specify a sign, then
a positive number is assumed. Here are some examples of valid
decimal literals:

24.7
+24.7
-24.7

Datetime Literals
Datetime literals are character representations of datetime values
that are enclosed in single quotes. Here are some examples of
valid datetime literals:

'April 30, 2015'

'2015/04/30'
'2015/04/30 08:34:25'

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL,
TCL, and DQL.

1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating

a table, deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that
means it permanently save all the changes in the
database.
Here are some commands that come under DDL:

o CREATE
o ALTER
o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the

database.

Syntax:

1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATAT

YPES[,....]);

Example:

1. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Em

ail VARCHAR2(100), DOB DATE);

b. DROP: It is used to delete both the structure and

record stored in the table.

Syntax

1. DROP TABLE ;

Example

1. DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the

database. This change could be either to modify the
characteristics of an existing attribute or probably to add
a new attribute.
Syntax:

To add a new column in the table

1. ALTER TABLE table_name ADD column_name COLUM

N-definition;

To modify existing column in the table:

1. ALTER TABLE MODIFY(COLUMN DEFINITION....);

EXAMPLE

1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHA

R2(20));
2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHA
R2(20));

d. TRUNCATE: It is used to delete all the rows from the

table and free the space containing the table.

Syntax:

1. TRUNCATE TABLE table_name;

Example:

1. TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

o DML commands are used to modify the database. It

is responsible for all form of changes in the
database.
 o The command of DML is not auto-committed that
means it can't permanently save all the changes in
the database. They can be rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

a. INSERT: The INSERT statement is a SQL query. It is

used to insert data into the row of a table.

Syntax:

1. INSERT INTO TABLE_NAME

2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);

Or

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value3, .... valueN);

For example:

1. INSERT INTO javatpoint (Author, Subject) VALUES ("

Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify

the value of a column in the table.

Syntax:
 1. UPDATE table_name SET [column_name1= value1,...
column_nameN = valueN] [WHERE CONDITION]

For example:

1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a

table.

Syntax:

1. DELETE FROM table_name [WHERE condition];

For example:

1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";

3. Data Control Language

DCL commands are used to grant and take back authority

from any database user.

Here are some commands that come under DCL:

o Grant
o Revoke

a. Grant: It is used to give user access privileges to a

database.

Example
 1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_US
ER, ANOTHER_USER;

b. Revoke: It is used to take back permissions from the

user.

Example

1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER

1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like

INSERT, DELETE and UPDATE only.

These operations are automatically committed in the

database that's why they cannot be used while creating
tables or dropping them.

Here are some commands that come under TCL:

o COMMIT
o ROLLBACK
o SAVEPOINT

a. Commit: Commit command is used to save all the

transactions to the database.

Syntax:

1. COMMIT;

Example:
 1. DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. COMMIT;

b. Rollback: Rollback command is used to undo

transactions that have not already been saved to the
database.

Syntax:

1. ROLLBACK;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to

a certain point without rolling back the entire transaction.

Syntax:

1. SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: This is the same as the projection operation

of relational algebra. It is used to select the attribute
based on the condition described by WHERE clause.
Syntax:

1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;

For example:

1. SELECT emp_name
2. FROM employee
3. WHERE age > 20;

What is an Operator in SQL?

An operator is a reserved word or a character used primarily in
an SQL statement's WHERE clause to perform operation(s),
such as comparisons and arithmetic operations. These Operators
are used to specify conditions in an SQL statement and to serve
as conjunctions for multiple conditions in a statement.

● Arithmetic operators
● Comparison operators

● Logical operators

● Operators used to negate conditions

SQL Arithmetic Operators

Assume 'variable a' holds 10 and 'variable b' holds 20, then −
Show Examples

Operator Description Example

 Adds values on either side of the a + b will
+ (Addition)
operator. give 30

Subtracts right hand operand a - b will

- (Subtraction)
from left hand operand. give -10

* Multiplies values on either side of a * b will

(Multiplication) the operator. give 200

Divides left hand operand by right b / a will

/ (Division)
hand operand. give 2

Divides left hand operand by right b % a

% (Modulus) hand operand and returns will give
remainder. 0

SQL Comparison Operators

Assume 'variable a' holds 10 and 'variable b' holds 20, then −
Show Examples

Operato Description Example

r

Checks if the values of two operands

(a = b) is
= are equal or not, if yes then condition
not true.
becomes true.
 Checks if the values of two operands
(a != b)
!= are equal or not, if values are not equal
is true.
then condition becomes true.

Checks if the values of two operands

(a <> b)
<> are equal or not, if values are not equal
is true.
then condition becomes true.

Checks if the value of left operand is

(a > b) is
> greater than the value of right operand,
not true.
if yes then condition becomes true.

Checks if the value of left operand is

(a < b) is
< less than the value of right operand, if
true.
yes then condition becomes true.

Checks if the value of left operand is

(a >= b)
greater than or equal to the value of
>= is not
right operand, if yes then condition
true.
becomes true.

Checks if the value of left operand is

less than or equal to the value of right (a <= b)
<=
operand, if yes then condition becomes is true.
true.

Checks if the value of left operand is (a !< b)

!<
not less than the value of right is false.
 operand, if yes then condition becomes
true.

Checks if the value of left operand is

not greater than the value of right (a !> b)
!>
operand, if yes then condition becomes is true.
true.

SQL Logical Operators

Here is a list of all the logical operators available in SQL.
Show Examples

Sr.No. Operator & Description

ALL
1
The ALL operator is used to compare a value to all
values in another value set.

AND
2 The AND operator allows the existence of multiple
conditions in an SQL statement's WHERE clause.

ANY
3 The ANY operator is used to compare a value to any
applicable value in the list as per the condition.

4 BETWEEN
 The BETWEEN operator is used to search for
values that are within a set of values, given the
minimum value and the maximum value.

EXISTS
5 The EXISTS operator is used to search for the
presence of a row in a specified table that meets a
certain criterion.

IN
6 The IN operator is used to compare a value to a list
of literal values that have been specified.

LIKE
7 The LIKE operator is used to compare a value to
similar values using wildcard operators.

NOT
The NOT operator reverses the meaning of the
8 logical operator with which it is used. Eg: NOT
EXISTS, NOT BETWEEN, NOT IN, etc. This is a
negate operator.

OR
9 The OR operator is used to combine multiple
conditions in an SQL statement's WHERE clause.

10 IS NULL
 The NULL operator is used to compare a value with
a NULL value.

UNIQUE
11 The UNIQUE operator searches every row of a
specified table for uniqueness (no duplicates).

Tables, views and indexes

A view is simply any SELECT query that has been given a name
and saved in the database. For this reason, a view is sometimes
called a named query or a stored query. To create a view, you
use the SQL syntax:
CREATE OR REPLACE VIEW <view_name> AS
SELECT <any valid select query>;
 The view query itself is saved in the database, but it is not
actually run until it is called with another SELECT statement. For
this reason, the view does not take up any disk space for data
storage, and it does not create any redundant copies of data that
is already stored in the tables that it references (which are
sometimes called the base tables of the view).
 Although it is not required, many database developers identify
views with names such as v_Customers or Customers_view. This
not only avoids name conflicts with base tables, it helps in reading
any query that uses a view.
 The keywords OR REPLACE in the syntax shown above are
optional. Although you don’t need to use them the first time that
you create a view, including them will overwrite an older version of
the view with your latest one, without giving you an error
message.
 The syntax to remove a view from your schema is exactly
what you would expect:
DROP VIEW <view_name>;
Using views

A view name may be used in exactly the same way as a table

name in any SELECT query. Once stored, the view can be used
again and again, rather than re-writing the same query many
times.
 The most basic use of a view would be to simply SELECT *
from it, but it also might represent a pre-written subquery or a
simplified way to write part of a FROM clause.
 In many systems, views are stored in a pre-compiled form.
This might save some execution time for the query, but usually
not enough for a human user to notice.
 One of the most important uses of views is in large multi-user
systems, where they make it easy to control access to data for
different types of users. As a very simple example, suppose that
you have a table of employee information on the scheme
Employees = {employeeID, empFName, empLName, empPhone,
jobTitle, payRate, managerID}. Obviously, you can’t let everyone
in the company look at all of this information, let alone make
changes to it.
 Your database administrator (DBA) can define roles to
represent different groups of users, and then grant membership in
one or more roles to any specific user account (schema). In turn,
you can grant table-level or view-level permissions to a role as
well as to a specific user. Suppose that the DBA has created the
roles managers and payroll for people who occupy those
positions. In Oracle®, there is also a pre-defined role
named public, which means every user of the database.
 You could create separate views even on just the Employees
table, and control access to them like this:
CREATE VIEW phone_view AS
SELECT empFName, empLName, empPhone FROM
Employees;
GRANT SELECT ON phone_view TO public;

CREATE VIEW job_view AS

SELECT employeeID, empFName, empLName, jobTitle,
managerID FROM Employees;
GRANT SELECT, UPDATE ON job_view TO managers;

CREATE VIEW pay_view AS

SELECT employeeID, empFName, empLName, payRate
FROM Employees;
GRANT SELECT, UPDATE ON pay_view TO payroll;
 Only a very few trusted people would have SELECT,
UPDATE, INSERT, and DELETE privileges on the entire
Employees base table; everyone else would now have exactly the
access that they need, but no more.
 When a view is the target of an UPDATE statement, the base
table value is changed. You can’t change a computed value in a
view, or any value in a view that is based on a UNION query. You
may also use a view as the target of an INSERT or DELETE
statement, subject to any integrity constraints that have been
placed on the base tables.
Materialized views

Sometimes, the execution speed of a query is so important that a

developer is willing to trade increased disk space use for faster
response, by creating a materialized view. Unlike the view
discussed above, a materialized view does create and store the
result table in advance, filled with data. The scheme of this table
is given by the SELECT clause of the view definition.
 This technique is most useful when the query involves many
joins of large tables, or any other SQL feature that could
contribute to long execution times. You might encounter this in a
Web project, where the site visitor simply can’t be kept waiting
while the query runs.
 Since the view would be useless if it is out of date, it must be
re-run, at the minimum, when there is a change to any of the
tables that it is based on.
Indexes

An index, as you would expect, is a data structure that the

database uses to find records within a table more quickly. Indexes
are built on one or more columns of a table; each index maintains
a list of values within that field that are sorted in ascending or
descending order. Rather than sorting records on the field or
fields during query execution, the system can simply access the
rows in order of the index.
Unique and non-unique indexes: When you create an index, you
may allow the indexed columns to contain duplicate values; the
index will still list all of the rows with duplicates. You may also
specify that values in the indexed columns must be unique, just
as they must be with a primary key. In fact, when you create a
primary key constraint on a table, Oracle and most other systems
will automatically create a unique index on the primary key
columns, as well as not allowing null values in those columns.
One good reason for you to create a unique index on non-primary
key fields is to enforce the integrity of a candidate key, which
otherwise might end up having (nonsense) duplicate values in
different rows.
Queries versus insertion/update: It might seem as if you should
create an index on every column or group of columns that will
ever by used in an ORDER BY clause (for example: lastName,
firstName). However, each index will have to be updated every
time that a row is inserted or a value in that column is updated.
Although index structures such as B or B+ trees allow this to
happen very quickly, there still might be circumstances where too
many indexes would detract from overall system performance.
Syntax: As you would expect by now, the SQL to create an index
is:
CREATE INDEX <indexname> ON <tablename> (<column>,
<column>...);
To enforce unique values, add the UNIQUE keyword:
CREATE UNIQUE INDEX <indexname> ON <tablename>
(<column>, <column>...);
To specify sort order, add the keyword ASC or DESC after each
column name, just as you would do in an ORDER BY clause.
To remove an index, simply enter:
DROP INDEX <indexname>;

SQL - Sub Queries

A Subquery or Inner query or a Nested query is a query within

another SQL query and embedded within the WHERE clause.
A subquery is used to return data that will be used in the main
query as a condition to further restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE,
and DELETE statements along with the operators like =, <, >, >=,
<=, IN, BETWEEN, etc.
There are a few rules that subqueries must follow −
● Subqueries must be enclosed within parentheses.
● A subquery can have only one column in the SELECT
clause, unless multiple columns are in the main query for
the subquery to compare its selected columns.
● An ORDER BY command cannot be used in a subquery,
although the main query can use an ORDER BY. The
GROUP BY command can be used to perform the same
function as the ORDER BY in a subquery.
● Subqueries that return more than one row can only be used
with multiple value operators such as the IN operator.
● The SELECT list cannot include any references to values
that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
● A subquery cannot be immediately enclosed in a set
function.
● The BETWEEN operator cannot be used with a subquery.
However, the BETWEEN operator can be used within the
subquery.
Subqueries with the SELECT Statement
Subqueries are most frequently used with the SELECT
statement. The basic syntax is as follows −
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Now, let us check the following subquery with a SELECT
statement.
SQL> SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;
This would produce the following result.
+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+
Subqueries with the INSERT Statement
Subqueries also can be used with INSERT statements. The
INSERT statement uses the data returned from the subquery to
insert into another table. The selected data in the subquery can
be modified with any of the character, date or number functions.
The basic syntax is as follows.
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
Example
Consider a table CUSTOMERS_BKP with similar structure as
CUSTOMERS table. Now to copy the complete CUSTOMERS
table into the CUSTOMERS_BKP table, you can use the
following syntax.
SQL> INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;
Subqueries with the UPDATE Statement
The subquery can be used in conjunction with the UPDATE
statement. Either single or multiple columns in a table can be
updated when using a subquery with the UPDATE statement.
The basic syntax is as follows.
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Assuming, we have CUSTOMERS_BKP table available which is
backup of CUSTOMERS table. The following example updates
SALARY by 0.25 times in the CUSTOMERS table for all the
customers whose AGE is greater than or equal to 27.
SQL> UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
This would impact two rows and finally CUSTOMERS table
would have the following records.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 125.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Subqueries with the DELETE Statement
The subquery can be used in conjunction with the DELETE
statement like with any other statements mentioned above.
The basic syntax is as follows.
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Assuming, we have a CUSTOMERS_BKP table available which
is a backup of the CUSTOMERS table. The following example
deletes the records from the CUSTOMERS table for all the
customers whose AGE is greater than or equal to 27.
SQL> DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
This would impact two rows and finally the CUSTOMERS table
would have the following records.
+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

SQL Aggregate Functions

o SQL aggregation function is used to perform the calculations on multiple rows of
a single column of a table. It returns a single value.
o It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT FUNCTION

o COUNT function is used to Count the number of rows

in a database table. It can work on both numeric and
non-numeric data types.
o COUNT function uses the COUNT(*) that returns the
count of all the rows in a specified table. COUNT(*)
considers duplicate and Null.

Syntax
1. COUNT(*)
2. or
3. COUNT( [ALL|DISTINCT] expression )
Sample table:
PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST

 Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example: COUNT()
1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
Output:
10
Example: COUNT with WHERE
1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
3. WHERE RATE>=20;
Output:
7
Example: COUNT() with DISTINCT
1. SELECT COUNT(DISTINCT COMPANY)
2. FROM PRODUCT_MAST;
Output:
3
Example: COUNT() with GROUP BY
1. SELECT COMPANY, COUNT(*)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;
Output:
Com1 5
Com2 3
Com3 2
Example: COUNT() with HAVING
1. SELECT COMPANY, COUNT(*)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;
Output:
Com1 5
Com2 3

2. SUM Function

Sum function is used to calculate the sum of all selected

columns. It works on numeric fields only.
Syntax
1. SUM()
2. or
 3. SUM( [ALL|DISTINCT] expression )
Example: SUM()
1. SELECT SUM(COST)
2. FROM PRODUCT_MAST;
Output:
670
Example: SUM() with WHERE
1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3;
Output:
320
Example: SUM() with GROUP BY
1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3
4. GROUP BY COMPANY;
Output:
Com1 150
Com2 170
Example: SUM() with HAVING
1. SELECT COMPANY, SUM(COST)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING SUM(COST)>=170;
Output:
Com1 335
Com3 170

3. AVG function
The AVG function is used to calculate the average value
of the numeric type. AVG function returns the average of
all non-Null values.
Syntax
1. AVG()
2. or
3. AVG( [ALL|DISTINCT] expression )
Example:
1. SELECT AVG(COST)
2. FROM PRODUCT_MAST;
Output:
67.00

4. MAX Function

MAX function is used to find the maximum value of a

certain column. This function determines the largest
value of all selected values of a column.
Syntax
1. MAX()
2. or
3. MAX( [ALL|DISTINCT] expression )
Example:
1. SELECT MAX(RATE)
2. FROM PRODUCT_MAST;
30

5. MIN Function
MIN function is used to find the minimum value of a
certain column. This function determines the smallest
value of all selected values of a column.
Syntax
1. MIN()
2. or
3. MIN( [ALL|DISTINCT] expression )
Example:
1. SELECT MIN(RATE)
2. FROM PRODUCT_MAST;
Output:
10

Insertion
The syntax for inserting a new record to a table is very straightforward:
INSERT INTO table_name (field1, field2, ...)
VALUES (value1, value2, ...);
Where
● field1 and field2 are fields from table_name.
● value1 and value2 are the values for field1 and field2, respectively.

INSERT INTO table_name (field2, field1, ...)

VALUES (value2, value1, ...);

It is worth noting that the data type and length should match the table
declaration. At best, boolean values (0 or 1) may fit into a field declared
as an integer but not necessarily the other way around. At worst,
attempting to enter 100-character strings in VARCHAR(50) fields will
simply end in an error or in data getting truncated. Thus, it is best to be
as accurate as possible in the data type declaration and its use.
 ● The ellipsis (...) is not part of the SQL code. It indicates that other
fields and their corresponding values may also be included in the
statement.

A variation of INSERT allows several comma-separated records to be

inserted at once as follows:
INSERT INTO table_name (field1, field2, ...)
VALUES (value3, value4, ...),
(value5, value6, ...),
(value7, value8, ...);

Above Fig. illustrates this approach (commonly referred to as bulk

insert), which was used to populate the customers table in the previous
guide. Since customer_id is an auto increment column, it is populated
automatically upon each insert.

Auto increment fields are better known as identity columns in SQL

Server. They are declared using
the IDENTITY property: customer_id INT
IDENTITY(1,1) where (1,1) indicates that it will start at 1 using
steps of the same value.
Additionally, you can also insert rows to a table using the results of
a SELECT query. For instance,
INSERT INTO table_name (field1, field2, ...)
SELECT fieldX, fieldY FROM other_table;
sql
will insert all values
from fieldX and fieldY in other_table to table_name. As you may well
expect, the SELECT can be simple as above or complex, involving two
or more tables.
To illustrate, let us add a new book called Kicking In the Wall written by
Barbara Abercrombie and published by Harper Collins to our library.
INSERT INTO books
(book_name, book_isbn, book_edition, author_id,
publisher_id)
SELECT
'Kicking in the wall' AS book_name,
'9781608681563' AS book_isbn,
1 AS book_edition, a.author_id, p.publisher_id
FROM authors AS a, publishers p
WHERE a.author_name = 'Barbara Abercrombie'
AND p.publisher_name = 'Harper Collins';

Updating Data
Whenever you need to change the value of certain fields in one or more
rows, you will come across the UPDATE statement. In its most simple
form, the syntax is the following:
UPDATE table_name
SET field1 = X,
field2 = Y
WHERE field1 = Z;

Where field1 and field2 are two fields from table_name whose values
will be changed to X and Y, respectively - but only on the record where
the current value of field1 is Z.
You can add as many field = value pairs as needed, as long as they are
separated by commas.
If you omit the WHERE clause, those fields will be updated for all the
records in the table.
UPDATE customers
SET customer_name = 'Jack and Jill Devera',
customer_address = '62 Fillmore Ave'
WHERE customer_name = 'Jill Devera';
In short, we are only safe to proceed if the SELECT returns the correct
row(s) when you apply the same filter condition.

Deletion
To delete the row(s) of table_name where the current value
of field1 is Z, do as follows:
DELETE FROM table_name
WHERE field1 = Z;

Sad news - our customer Tim Murphy has canceled his membership so
now we need to remove his name from the customers table. First off, we
need to make sure he has returned all the books he ever borrowed from
the library:
SELECT customer_id FROM customers WHERE
customer_name = 'Tim Murphy';
SELECT COUNT(customer_id) FROM loans
WHERE customer_id = 10;

Since the result is 0, we can write the DELETE query with confidence
using what we have just learned:
DELETE FROM customers
WHERE customer_id = 10;
Note that the WHERE clauses in the SELECT and DELETE queries are
identical.
The fact that we checked the loans table before deleting the customer
was not just to avoid losing books. Remember how
the customer_id foreign key in loans points to the same field
in customers (where it is a primary key)? This is what we call
a constraint in SQL, and its purpose is to prevent data integrity issues,
which is what would have occurred if we had a customer_id =
10 in loans but not in customers.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

● (INNER) JOIN: Returns records that have matching

values in both tables
● LEFT (OUTER) JOIN: Returns all records from the left
table, and the matched records from the right table
● RIGHT (OUTER) JOIN: Returns all records from the
right table, and the matched records from the left
table
● FULL (OUTER) JOIN: Returns all records when there is
a match in either left or right table
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Let's look at a selection from the "Orders" table:

OrderID CustomerID OrderDate

10308 2 1996-09-18

10309 37 1996-09-19
 10310 77 1996-09-20

CustomerI CustomerName ContactName Country

D

1 Alfreds Futterkiste Maria Anders Germany

2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico

3 Antonio Moreno Taquería Antonio Moreno Mexico

Then, look at a selection from the "Customers" table:

Then, we can create the following SQL statement (that

contains an INNER JOIN), that selects records that have
matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName,
Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.C
ustomerID;
and it will produce something like this:

OrderID CustomerName OrderDat

e

10308 Ana Trujillo Emparedados y helados 9/18/199

6

10365 Antonio Moreno Taquería 11/27/19

96

10383 Around the Horn 12/16/19

96

10355 Around the Horn 11/15/19

96

10278 Berglunds snabbköp 8/12/199

6

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT

OUTER JOIN.

Demo Database

In this tutorial we will use the well-known Northwind

sample database.

Below is a selection from the "Customers" table:

Customer CustomerNa ContactNa Address City PostalCo Countr

ID me me de y

1 Alfreds Maria Obere Str. Berlin 12209 Germa

Futterkiste Anders 57 ny
 2 Ana Trujillo Ana Trujillo Avda. de Méxic 05021 Mexico
Emparedado la o D.F.
s y helados Constituci
ón 2222

3 Antonio Antonio Matadero Méxic 05023 Mexico

Moreno Moreno s 2312 o D.F.
Taquería

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

SQL LEFT JOIN Example

 The following SQL statement will select all customers,
and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from
the left table (Customers), even if there are no matches
in the right table (Orders).
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo

1 Davolio Nancy 12/8/1968 EmpID1.pic

2 Fuller Andrew 2/19/1952 EmpID2.pic

3 Leverling Janet 8/30/1963 EmpID3.pic

SQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they
might have placed:

Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Note: FULL OUTER JOIN can potentially return very large result-sets!

A selection from the result set may look like this:

CustomerName OrderID

Alfreds Futterkiste Null

Ana Trujillo Emparedados y helados 10308

Antonio Moreno Taquería Null