Relational Database Management System

Introduction to SQL
● SQL stands for Structured Query Language
● SQL lets you access and manipulate databases
● Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
● This database language is mainly designed for maintaining the data in relational
database management systems.
● It is a special tool used by data professionals for handling structured data (data
which is stored in the form of tables).
● It is also designed for stream processing in RDBMS.
● If you want to get a job in the field of data science, then it is the most important
query language to learn. Big enterprises like Facebook, Instagram, and LinkedIn,
use SQL for storing the data in the back-end.

SQL Data Types

Data types are mainly classified into three categories for every database.

● String Data types

● Numeric Data types

● Date and time Data types

Oracle String data types

CHAR(size) It is used to store character data within the predefined length. It can
be stored up to 2000 bytes.

NCHAR(size It is used to store national character data within the predefined length.
) It can be stored up to 2000 bytes. Ex.: $, #, and @

Miss. Hemangi Panchal Page 1

 Relational Database Management System

VARCHAR2( It is used to store variable string data within the predefined length. It
size) can be stored up to 4000 byte.

VARCHAR(S It is the same as VARCHAR2(size). You can also use

IZE) VARCHAR(size), but it is suggested to use VARCHAR2(size)

NVARCHAR It is used to store Unicode string data within the predefined length.
2(size) We have to must specify the size of NVARCHAR2 data type. It can
be stored up to 4000 bytes. Ex.: cm, m, km, inch, etc.

Oracle Numeric Data Types

NUMBER(p, It contains precision p and scale s. The precision p can range from 1
s) to 38, and the scale s can range from -84 to 127.

FLOAT(p) It is a subtype of the NUMBER data type. The precision p can range
from 1 to 126.

BINARY_FL It is used for binary precision( 32-bit). It requires 5 bytes, including

OAT length byte.

BINARY_D It is used for double binary precision (64-bit). It requires 9 bytes,

Miss. Hemangi Panchal Page 2

 Relational Database Management System

OUBLE including length byte.

Oracle Date and Time Data Types

DATE It is used to store a valid date-time format with a fixed length. .

TIMEST It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss
AMP format.

SQL Components
● DDL:-Data Definition Language
● DML:-Data Manipulation Language
● DCL:-Data Control Language
● TCL:-Transaction Control Language

DDL-: Data Definitions Language

Data Definition Language (DDL) is used to define database structures
● Create
● Alter
● Drop
● Rename
● Truncate

Create Command:

● CREATE TABLE statement is used for creating relations.

● Each column is described with three parts: column name, data type.

Syntax:

Miss. Hemangi Panchal Page 3

 Relational Database Management System

Create table <table_name>

(col1 datatype(size),

Col2 datatype(size));

Example:-

CREATE TABLE Emp

(emp_no number(3),

emp_name varchar2(10),
Job varchar2(30),

Salary number(12,2),

Dept_no number(2));

Alter Statement:

● ALTER statement changes table structure, properties, or constraints after it has

been created
● Modify the structure of object (table).

Syntax:

Alter table <table_name>

Add / modify (col_name

Data type (size));

– Alter table <table_name>

Constraint (col_name) REFERENCES <Table_name> (col_name));

Example:-

Miss. Hemangi Panchal Page 4

 Relational Database Management System

● Alter table Emp add commission number(8,2);

● Alter table Emp modify ename varchar2(25);

DROP Statements:-

● DROP TABLE statement removes tables and their data from the database
● A table cannot be dropped if it contains foreign key values needed by other
tables

Syntax:

DROP TABLE <table_name>

Example:

DROP TABLE CUSTOMER;

RENAME Statement:-

● RENAME statement change the table name from the database

Syntax:

RENAME <old table_name> to <new table_name>

Example:

RENAME emp to employee;

Truncate Command:

● Remove all records from a table, including all spaces allocated for the records
are removed.

Miss. Hemangi Panchal Page 5

 Relational Database Management System

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Employee;

DML-: Data Manipulations Language

Data Manipulation Language (DML) is used to query and Update data.

● Select
● Insert
● Update
● Delete

Select Command:
Syntax:

SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM

Name_of_table;

Here, column_Name_1, column_Name_2, ….., column_Name_N are the names of

those columns whose data we want to retrieve from the table.

If we want to retrieve the data from all the columns of the table, we have to use the
following SELECT command:

SELECT * FROM table_name;

Example:

Example 1: This example shows all the values of every column from the table.

Miss. Hemangi Panchal Page 6

 Relational Database Management System

➔ SELECT Emp_Id, Emp_Salary FROM Employee;

Example 2: This example shows all the values of a specific column from the table.

➔ SELECT * FROM Student;

Example 3: This example describes how to use the WHERE clause with the SELECT
DML command.

➔ SELECT * FROM Student WHERE Stu_Marks = 80;

Insert Command:
INSERT is another most important data manipulation command in Structured Query
Language, which allows users to insert data in database tables.

Syntax:

INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 , ....

column_NameN ) VALUES (value_1, value_2, value_3, .... value_N ) ;

Example:

INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age) VALUES (104,

Anmol, 89, 19);

Update Command:
UPDATE is another most important data manipulation command in Structured Query
Language, which allows users to update or modify the existing data in database tables.

Syntax:

UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N]

WHERE CONDITION;

Here, 'UPDATE', 'SET', and 'WHERE' are the SQL keywords, and 'Table_name' is the
name of the table whose values you want to update.

Miss. Hemangi Panchal Page 7

 Relational Database Management System

Examples
Example 1: This example describes how to update the value of a single field.

➔ UPDATE Product SET Product_Price = 80 WHERE Product_Id = 'P102' ;

Example 2: This example describes how to update the value of multiple fields of the
database table.

➔ UPDATE Student SET Stu_Marks = 80, Stu_Age = 21 WHERE Stu_Id = 103

AND Stu_Id = 202;

DELETE Command:

DELETE is a DML command which allows SQL users to remove single or multiple
existing records from the database tables.

This command of Data Manipulation Language does not delete the stored data
permanently from the database. We use the WHERE clause with the DELETE
command to select specific rows from the table.

Syntax

DELETE FROM Table_Name WHERE condition;

Examples

Example 1: This example describes how to delete a single record from the table.

➔ DELETE FROM Product WHERE Product_Id = 'P202' ;

Example 2: This example describes how to delete the multiple records or rows from the
database table.
➔ DELETE FROM Student WHERE Stu_Marks > 70 ;

DCL-: Data Control Language

Miss. Hemangi Panchal Page 8

 Relational Database Management System

DCL is an abbreviation for Data Control Language in SQL. It is used to provide different
users access to the stored data. It enables the data administrator to grant or revoke the
required access to act as the database. When DCL commands are implemented in the
database, there is no feature to perform a rollback. The administrator must implement
the other DCL command to reverse the action.

● GRANT

● REVOKE

GRANT Command
It helps to provide any kind of access to any user.

GRANT, as the name itself suggests, provides. This command allows the administrator
to provide particular privileges or permissions over a database object, such as a table,
view, or procedure. It can provide user access to perform certain database or
component operations.

In simple language, the GRANT command allows the user to implement other SQL
commands on the database or its objects. The primary function of the GRANT
command in SQL is to provide administrators the ability to ensure the security and
integrity of the data is maintained in the database.

To have a better understanding of implementing the GRANT statement in the database.

Let us use an example.

Implementing GRANT Statement

Consider a scenario where you are the database administrator, and a student table is in
the database. Suppose you want a specific user Aman to only SELECT (read)/ retrieve
the data from the student table. Then you can use GRANT in the below GRANT
statement.

Create User:

Syntax:

SQL> CREATE USER <USER_NAME> IDENTIFIED BY PWD;

Example:

SQL> CREATE USER C##HEMANGI IDENTIFIED BY PWD;

Miss. Hemangi Panchal Page 9

 Relational Database Management System

Now create new user,

user-name= C##HEMANGI, and Password = PWD

SQL> GRANT CONNECT TO C##HEMANGI;

Here, now you are connect new user C##HEMANGI.

All privileges system to new user.

SQL> GRANT ALL ON Salesman TO C##HEMANGI;

Fetch all Salesman records for new user.

SQL> SELECT *FROM SYSTEM.Salesman;

GRANT SELECT ON student TO Aman;

This command will allow Aman to implement the SELECT queries on the student table.
This will enable the user to read or retrieve information from the student table.

Note: Implementing the above statement will also limit Aman's operations. Aman won't be
able to modify the data stored in the table. It will prevent the user from user to insert, to
update, or deleting the data in the student table in the database.

REVOKE Command

It is used to take back access from the user.

As the name suggests, revoke is to take away. The REVOKE command enables the
database administrator to remove the previously provided privileges or permissions
from a user over a database or database object, such as a table, view, or procedure.
The REVOKE commands prevent the user from accessing or performing a specific
operation on an element in the database.

In simple language, the REVOKE command terminates the ability of the user to perform
the mentioned SQL command in the REVOKE query on the database or its component.
The primary reason for implementing the REVOKE query in the database is to ensure
the data's security and integrity.

Miss. Hemangi Panchal Page 10

 Relational Database Management System

Let us use an example to better understand how to implement the REVOKE command
in SQL.

Implementing REVOKE Command

Consider a scenario where the user is the database administrator. In the above
implementation of the GRANT command, the user Aman was provided permission to
implement a SELECT query on the student table that allowed Aman to read or retrieve
the data from the table. Due to certain circumstances, the administrator wants to revoke
the above mentioned permission. To do so, the administrator can implement the below
REVOKE statement:

All permission is revoke from the new user.

SQL> REVOKE ALL ON SYSTEM.Salsman FROM C##HEMANGI;

SQL> REVOKE CONNECT FROM C##HEMANGI;

Now, user-name and password are revoke from the system.

REVOKE SELECT ON student FROM Aman;

This will stop the user Aman from implementing the SELECT query on the student table.
The user may be able to implement other queries in the database.

TCL-: Transaction Control Language

● Commit
● Rollback
● Savepoint

In SQL, TCL stands for Transaction control language.

A single unit of work in a database is formed after the consecutive execution of

commands is known as a transaction.

There are certain commands present in SQL known as TCL commands that help the
user manage the transactions that take place in a database.

Miss. Hemangi Panchal Page 11

 Relational Database Management System

COMMIT. ROLLBACK and SAVEPOINT are the most commonly used TCL commands
in SQL.

Now let us take a deeper dive into the TCL commands of SQL with the help of
examples. All the queries in the examples will be written using the MySQL database.

COMMIT

COMMIT command in SQL is used to save all the transaction-related changes

permanently to the disk. Whenever DDL commands such as INSERT, UPDATE and
DELETE are used, the changes made by these commands are permanent only after
closing the current session. So before closing the session, one can easily roll back the
changes made by the DDL commands. Hence, if we want the changes to be saved
permanently to the disk without closing the session, we will use the commit command.

Syntax:

COMMIT;

Example:

SQL> INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION) VALUES

(1001, 'PIYUSH', 'LONDON', 12);

SQL> commit;

So ,records are permanently saved in the database.

ROLLBACK:-

● Rollback is used for cancel the transaction.

● Rollback is used to remove transactions.
● Rollback is one type of undo statement.

Syntax:

Rollback;

Example:

Miss. Hemangi Panchal Page 12

 Relational Database Management System

➔ SQL> SELECT *FROM Salesman;

➔ SQL>INSERT INTO Salesman (SNUM, SNAME) VALUES (1003,’MOHIT’);
➔ SQL>ROLLBACK;

In example , do not insert the record in the Salesman table because use rollback.

SAVEPOINT :-

Savepoint is a bookmark for reader

Savepoint is create one area for statement

Syntax:

Savepoint <savepoint name>

Example:

➔ SQL> INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)

VALUES (1001, 'PIYUSH', 'LONDON', 12);
➔ SQL> INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1002, 'NIRAJ', 'SURAT', 13);
➔ SQL> SAVEPOINT A;
➔ SQL> INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1003, 'MITI', 'LONDON', 11);
➔ SQL> SAVEPOINT B;
➔ SQL> INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1004, 'RAJESH', 'BARODA', 15);
➔ SQL> INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1005, 'ANAND', 'NEW DELHI', 10);
➔ SQL> ROLLBACK TO SAVEPOINT B;
➔ SQL> SELECT *FROM Salesman;

In the savepoint B statement do not insert 1004, 1005 data because use rollback.

SQL constructs
Group by Clause:-

Miss. Hemangi Panchal Page 13

 Relational Database Management System

Group by clause from a group on the basis of similar types data and produce a separate
table.

Note: The group by clause performs its operation in the buffer not in the database.

In SQL, The Group By statement is used for organizing similar data into groups. The
data is further organized with the help of equivalent function. It means, if different rows
in a precise column have the same values, it will arrange those rows in a group.

Group By single column: Group By single column is used to place all the rows with the
same value. These values are of that specified column in one group. It signifies that all
rows will put an equal amount through a single column, which is of one appropriate
column in one group.

○ The SELECT statement is used with the GROUP BY clause in the SQL query.

○ WHERE clause is placed before the GROUP BY clause in SQL.

○ ORDER BY clause is placed after the GROUP BY clause in SQL.

Emp:

EID DNAME ESAL

1 Sale 10000

2 HR 15000

3 Sale 20000

4 Sale 10000

Miss. Hemangi Panchal Page 14

 Relational Database Management System

5 HR 23000

6 Product 25000

Example:
SQL> SELECT DNAME FROM Emp GROUP BY DNAME;

Output:

DNAME
----------
Sale
HR
Product

Example:
SQL> SELECT DNAME,COUNT(*) FROM Emp GROUP BY DNAME;

Output:

DNAME COUNT(*)
---------- --------
Sale 4
HR 2
Product 1

Having Clause:-

● Having clause restricting the group.

● Having clause will always we use with group by clause and evaluated after
forming of groups.
● It eliminates the non-matching records.
● The HAVING clause is used in database systems to fetch the data/values from
the groups according to the given condition.

Miss. Hemangi Panchal Page 15

 Relational Database Management System

● The HAVING clause can include SQL aggregate functions in a query or

statement. Ex.: aggregate functions is Count(), Sum(), Avg(), Min(), Max().
● We can implements this SQL clause in column operations.

Syntax:

SELECT column_Name1, column_Name2, ....., column_NameN

aggregate_function_name(column_Name) FROM table_name GROUP BY
column_Name1 HAVING condition;

Example:
SQL> SELECT DNAME,COUNT(*) FROM Emp GROUP BY DNAME HAVING
COUNT(*) >=2;

Output:

DNAME COUNT(*)
---------- --------
Sale 3
HR 2

Order by Clause:-

● We use order by clause to arrange the records of one or more columns either
ascending or descending order.
● Whenever we want to sort the records based on the columns stored in the tables
of the SQL database, then we consider using the ORDER BY clause in SQL.

● The ORDER BY clause in SQL will help us to sort the records based on the
specific column of a table. This means that all the values stored in the column on
which we are applying ORDER BY clause will be sorted, and the corresponding
column values will be displayed in the sequence in which we have obtained the
values in the earlier step.

● Using the ORDER BY clause, we can sort the records in ascending or

descending order as per our requirement. The records will be sorted in

Miss. Hemangi Panchal Page 16

 Relational Database Management System

ascending order whenever the ASC keyword is used with ORDER by clause.
DESC keyword will sort the records in descending order.

● If no keyword is specified after the column based on which we have to sort

the records, in that case, the sorting will be done by default in the
ascending order.

Syntax:
SELECT ColumnName1,...,ColumnNameN FROM TableName ORDER BY
ColumnName ASC/DESC;

Example:
SQL> SELECT *FROM Emp ORDER BY EID ASC;

SQL Functions
String Functions
SQL String functions are the predefined functions that allow the database users for
string manipulation. These functions only accept, process, and give results of the string
data type.

Miss. Hemangi Panchal Page 17

 Relational Database Management System

In Oracle SQL, the DUAL table is a special one-row, one-column table provided by
Oracle.The DUAL table has one column called DUMMY of type VARCHAR2(1) with a
value of 'X'.

Following are the most important string functions in Structured Query Language:

1) Upper(string)

– Upper function convert string into the upper latter.

– Sql> update emp set ename = upper(ename);

– Sql> select upper(‘my india is great’) from dual;

Output: MY INDIA IS GREAT

2) Lower(string)

– Lower function convert string into upper letter.

– Sql> select lower(‘INDIA’)from dual;

Output: india

– Sql>select *from Emp where lower(job)=’clerk’;

3) Initcap(string)

– initcap function convert string into proper case or title case.

– Sql> SELECT INITCAP('my india is great') FROM DUAL;

Output: My India Is Great

4) Ascii(character)

– Ascii function return the ascii value of character

– Sql> SELECT ASCII('A') FROM DUAL;

Miss. Hemangi Panchal Page 18

 Relational Database Management System

Output: 65

5) Char(number)

– Char function return character value of number

– Sql> SELECT CHR(65) FROM DUAL;

Output: A

6) Length(string)

– length function return length of string.

– Sql> SELECT LENGTH('india') FROM DUAL;

Output: 5

7) Ltrim(string)

– ltrim function to space character in string from left side.

– Sql> SELECT LTRIM('GoodMorning', 'Good') FROM DUAL;

Output: Morning

8) Rtrim(string)

– rtrim function to space character in string from right side.

– Sql> SELECT RTRIM('GoodMorning', 'Morning') FROM DUAL;

Output: Good

9) Substr(string,starting position,no of character extract)

– substr function to find(extract)characterr in string .

– Sql> SELECT SUBSTR('GOOD MORNING',3) FROM DUAL;

Output: OD MORNING

– Sql> SELECT SUBSTR('GOODMORNING',3,3) FROM DUAL;

Miss. Hemangi Panchal Page 19

 Relational Database Management System

Output: ODM

10) Instr(string)

– Instr function return character position.

– Sql> SELECT INSTR('GOOD MORNING','M') FROM DUAL;

Output: 6

Sql> SELECT INSTR('GOOD MORNING','O') FROM DUAL;

Output: 2

11) Replace(string,finding text,replace text)

– replace function to replace character in string.

– Sql> SELECT REPLACE ('WIFE','W','KN') FROM DUAL;

Output: KNIFE

12) Rpad(string,length,character to pad)

– rpad function to put character in string from right side.

– Sql> SELECT RPAD('GOOD',11,'MORNING') FROM DUAL;

Output: GOODMORNING

13) Lpad(string,length,character to pad)

– lpad function to put character in string from left side.

– Sql> SELECT LPAD('MORNING',11,'GOOD') FROM DUAL;

Output: GOODMORNING

Conversion Functions
1)To_char(Date,Format)

– To_char function return and convert into character string.

Miss. Hemangi Panchal Page 20

 Relational Database Management System

– Sql> SELECT TO_CHAR(SYSDATE, 'DD-MM-YY') FROM DUAL;

Output: 29-07-24

– Sql> SELECT TO_CHAR(SYSDATE, 'DD-MONTH-YYYY') FROM DUAL;

Output: 29-JULY-2024

2)To_date(string,’Format’)

– To_date function return and convert string into date.

– Sql> SELECT TO_DATE('29-07-2024','DD-MM-YY') FROM DUAL;

Output: 29-JUL-24

– Sql> SELECT TO_DATE(SYSDATE, 'DD-MONTH-YYYY') FROM DUAL;

Output: 29-JUL-24

Numeric Functions
1) Round(number,rounding place)

– round function return rounded value of number.

– Sql> SELECT ROUND(50.40) FROM DUAL;

Output: 50

– Sql> SELECT ROUND(50.60) FROM DUAL;

Output: 51

– Sql> SELECT ROUND(50.6070,2) FROM DUAL;

Output: 50.61

2) Mod(number,no. to devide with)

– mod function returns module value of number.

Miss. Hemangi Panchal Page 21

 Relational Database Management System

– Sql> SELECT MOD(5,2) FROM DUAL;

Output: 1

3) Sqrt(number)

– sqrt function returns the sqrt root number.

– Sql> SELECT SQRT(9) FROM DUAL;

Output: 3

4) Abs(number)

– abs function return positive value number.

– Sql> SELECT ABS(-50) FROM DUAL;

Output: 52

– Sql> SELECT ABS(-5.50) FROM DUAL;

Output: 5.5

5) Power(number,paised number)

– abs function return squre value of number.

– Sql> SELECT POWER(5,2) FROM DUAL;

Output: 25

6) Trunc(number)

– trunc function return integer value of number.

– Sql> SELECT TRUNC(52.5) FROM DUAL;

Output: 52

7) Ceil(number)

– ceil function return decimal value number to upper bound.

– Sql>SELECT CEIL(140.1) FROM DUAL;

Miss. Hemangi Panchal Page 22

 Relational Database Management System

Output: 141

– Sql>SELECT CEIL(140) FROM DUAL;

Output: 140

8) Floor(number)

– floor function return decimal value number to lower bound.

– Sql> SELECT FLOOR(140.70) FROM DUAL;

Output: 140

Aggregate Functions
1)Sum(column name)

– Sum function return the sum value of given column.

– Sql> select sum(salary) from Emp;

Output: 45000

– Sql> select sum(amount) from Dept;

Output: 15000.

2) Avg(column name)

– Avg function return the average value of given column.

– Sql> select avg(salary) from Emp;

Output: 5345.50

3) Min(column name)

– min function return the minimum value of given column.

– Sql> select min(salary) from Emp;

Output: 4500

Miss. Hemangi Panchal Page 23

 Relational Database Management System

– Sql> select min(date) from Emp;

Output: 12-09-08

4) Max(column name)

– max function return the maximum value of given column.

– Sql> select max(salary) from Emp;

Output: 8000

5) Count(*)

– Count(*) function count total number of record in table.

– Sql> select count(*) from Emp;

Output: 8 (total row)

6) Count(column)

– Count() function count total number of record in table without null value.

– Sql> select count(ename) from Emp;

Output: 6

Total row 8 but two null value in ename column.

Set Operators
SET operators are special type of operators which are used to combine the result of two
queries.

Operators covered under SET operators are:

Miss. Hemangi Panchal Page 24

 Relational Database Management System

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

There are certain rules which must be followed to perform operations using SET
operators in SQL. Rules are as follows:

1. The number and order of columns must be the same.

2. Data types must be compatible.

Let us see each of the SET operators in more detail with the help of examples.

All the examples will be written using the MySQL database.

Consider we have the following tables with the given data.

Table 1: t_employees

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

2 Abhishek Pawar Production 45000 1

3 Pranav Deshmukh HR 59900 3

4 Shubham Mahale Accounts 57000 2

5 Sunil Kulkarni Development 87000 3

6 Bhushan Wagh R&D 75000 2

Miss. Hemangi Panchal Page 25

 Relational Database Management System

7 Paras Jaiswal Marketing 32000 1

Table 2: t2_employees

ID Name Department Salary Year_of_Experience

1 Prashant Wagh R&D 49000 1

2 Abhishek Pawar Production 45000 1

3 Gautam Jain Development 56000 4

4 Shubham Mahale Accounts 57000 2

5 Rahul Thakur Production 76000 4

6 Bhushan Wagh R&D 75000 2

7 Anand Singh Marketing 28000 1

1. UNION:

● UNION will be used to combine the result of two select statements.

● Duplicate rows will be eliminated from the results obtained after performing the
UNION operation.

Example:

Write a query to perform union between the table t_employees and the table
t2_employees.

Miss. Hemangi Panchal Page 26

 Relational Database Management System

Query:

sql> SELECT *FROM t_employees UNION SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT query
will fetch the records from the t_employees table and perform a UNION operation with
the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

2 Abhishek Pawar Production 45000 1

3 Pranav Deshmukh HR 59900 3

4 Shubham Mahale Accounts 57000 2

5 Sunil Kulkarni Development 87000 3

6 Bhushan Wagh R&D 75000 2

7 Paras Jaiswal Marketing 32000 1

1 Prashant Wagh R&D 49000 1

3 Gautam Jain Development 56000 4

5 Rahul Thakur Production 76000 4

7 Anand Singh Marketing 28000 1

Miss. Hemangi Panchal Page 27

 Relational Database Management System

2. UNION ALL

● This operator combines all the records from both the queries.

● Duplicate rows will be not be eliminated from the results obtained after
performing the UNION ALL operation.

Example:

Write a query to perform union all operation between the table t_employees and the
table t2_employees.

Query:

sql> SELECT *FROM t_employees UNION ALL SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT query
will fetch the records from the t_employees table and perform UNION ALL operation
with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

2 Abhishek Pawar Production 45000 1

3 Pranav Deshmukh HR 59900 3

4 Shubham Mahale Accounts 57000 2

5 Sunil Kulkarni Development 87000 3

Miss. Hemangi Panchal Page 28

 Relational Database Management System

6 Bhushan Wagh R&D 75000 2

7 Paras Jaiswal Marketing 32000 1

1 Prashant Wagh R&D 49000 1

2 Abhishek Pawar Production 45000 1

3 Gautam Jain Development 56000 4

4 Shubham Mahale Accounts 57000 2

5 Rahul Thakur Production 76000 4

6 Bhushan Wagh R&D 75000 2

7 Anand Singh Marketing 28000 1

3. INTERSECT:

● It is used to combine two SELECT statements, but it only returns the records
which are common from both SELECT statements.

Example:

Write a query to perform intersect operation between the table t_employees and the
table t2_employees.

Query:

sql> SELECT *FROM t_employees INTERSECT SELECT *FROM t2_employees;

Miss. Hemangi Panchal Page 29

 Relational Database Management System

Here, in a single query, we have written two SELECT queries. The first SELECT query
will fetch the records from the t_employees table and perform INTERSECT operation
with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

ID Name Hometown Percentage Favourite_Subject

2 Abhishek Pawar Production 45000 1

4 Shubham Mahale Accounts 57000 2

6 Bhushan Wagh R&D 75000 2

4. MINUS

● It displays the rows which are present in the first query but absent in the second
query with no duplicates.

Example:

Write a query to perform a minus operation between the table t_employees and the
table t2_employees.

Query:

sql> SELECT *FROM t_employees MINUS SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT query
will fetch the records from the t_employees table and perform MINUS operation with the
records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

ID Name Department Salary Year_of_Experience

Miss. Hemangi Panchal Page 30

 Relational Database Management System

1 Aakash Singh Development 72000 2

3 Pranav Deshmukh HR 59900 3

5 Sunil Kulkarni Development 87000 3

7 Paras Jaiswal Marketing 32000 1

Nested queries/ Sub Queries

In SQL a Subquery can be simply defined as a query within another query. In other
words we can say that a Subquery is a query that is embedded in WHERE clause of
another SQL query. Important rules for Subqueries:

● You can place the Subquery in a number of SQL clauses: WHERE clause,
HAVING clause, FROM clause. Subqueries can be used with SELECT,
UPDATE, INSERT, DELETE statements along with expression operator. It
could be equality operator or comparison operator such as =, >, =, <= and
Like operator.
● A subquery is a query within another query. The outer query is called as main
query and inner query is called as subquery.
● The subquery generally executes first when the subquery doesn’t have any
correlation with the main query, when there is a correlation the parser takes
the decision on the fly on which query to execute on precedence and uses
the output of the subquery accordingly.
● Subquery must be enclosed in parentheses.
● Subqueries are on the right side of the comparison operator.
● Use single-row operators with single row Subqueries. Use multiple-row
operators with multiple-row Subqueries.

Miss. Hemangi Panchal Page 31

 Relational Database Management System

Syntax: There is not any general syntax for Subqueries. However, Subqueries are seen
to be used most frequently with SELECT statement as shown below:

SELECT column_name

FROM table_name

WHERE column_name expression operator

(SELECT column_name FROM table_name WHERE ...);

Sample Table:

DATABASE

NAME ROLL_NO LOCATION PHONE_NUMBER

Ram 101 Chennai 9988775566

Raj 102 Coimbatore 8877665544

Sasi 103 Madurai 7766553344

Ravi 104 Salem 8989898989

Sumathi 105 Kanchipuram 8989856868

STUDENT

Miss. Hemangi Panchal Page 32

 Relational Database Management System

NAME ROLL_NO SECTION

Ravi 104 A

Sumathi 105 B

Raj 102 A

Sample Queries:

To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE

table whose section is A.

SELECT NAME, LOCATION, PHONE_NUMBER

FROM DATABASE

WHERE ROLL_NO IN (SELECT ROLL_NO

FROM STUDENT

WHERE SECTION='A');

Explanation : First subquery executes “ SELECT ROLL_NO from STUDENT where

SECTION=’A’ ” returns ROLL_NO from STUDENT table whose SECTION is ‘A’.Then
outer-query executes it and return the NAME, LOCATION, PHONE_NUMBER from the
DATABASE table of the student whose ROLL_NO is returned from inner subquery.

Output:

Miss. Hemangi Panchal Page 33

 Relational Database Management System

NAME ROLL_NO LOCATION PHONE_NUMBER

Ravi 104 Salem 8989898989

Raj 102 Coimbatore 8877665544

● Insert Query Example:

Table1: Student1

NAME ROLL_NO LOCATION PHONE_NUMBER

Ram 101 chennai 9988773344

Raju 102 coimbatore 9090909090

Ravi 103 salem 8989898989

Table2: Student2

NAME ROLL_NO LOCATION PHONE_NUMBER

Raj 111 chennai 8787878787

Sai 112 mumbai 6565656565

Miss. Hemangi Panchal Page 34

 Relational Database Management System

Sri 113 coimbatore 7878787878

To insert Student2 into Student1 table:

INSERT INTO Student1

SELECT * FROM Student2;

Output:

NAME ROLL_NO LOCATION PHONE_NUMBER

Ram 101 chennai 9988773344

Raju 102 coimbatore 9090909090

Ravi 103 salem 8989898989

Raj 111 chennai 8787878787

Sai 112 mumbai 6565656565

Sri 113 coimbatore 7878787878

Miss. Hemangi Panchal Page 35

 Relational Database Management System

● To delete students from Student2 table whose rollno is same as that in

Student1 table and having location as chennai

DELETE FROM Student2

WHERE ROLL_NO IN (SELECT ROLL_NO

FROM Student1

WHERE LOCATION = 'chennai');

● To update name of the students to geeks in Student2 table whose location is

same as Raju,Ravi in Student1 table

UPDATE Student2 SET NAME=’Rajesh’

WHERE LOCATION IN (SELECT LOCATION FROM Student1

WHERE NAME IN ('Raju', 'Ravi'));

Correlated nested Queries

In a SQL database query, a correlated subquery (also known as a synchronized
subquery) is a subquery (a query nested inside another query) that uses values from
the outer query. Because the subquery may be evaluated once for each row processed
by the outer query, it can be slow.

Examples
Correlated subqueries in the WHERE clause
Here is an example for a typical correlated subquery. In this example, the objective is to
find all employees whose salary is above average for their department.

SELECT employee_number, name

Miss. Hemangi Panchal Page 36

 Relational Database Management System

FROM employees emp

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department = emp.department);

In the above query the outer query is

SELECT employee_number, name

FROM employees emp

WHERE salary > ...

and the inner query (the correlated subquery) is

SELECT AVG(salary)

FROM employees

WHERE department = emp.department

In the above nested query the inner query has to be re-executed for each employee. (A
sufficiently smart implementation may cache the inner query's result on a department-
by-department basis, but even in the best case the inner query must be executed once
per department.)

Correlated subqueries in the SELECT clause

Correlated subqueries may appear elsewhere besides the WHERE clause; for example,
this query uses a correlated subquery in the SELECT clause to print the entire list of
employees alongside the average salary for each employee's department. Again,

Miss. Hemangi Panchal Page 37

 Relational Database Management System

because the subquery is correlated with a column of the outer query, it must be re-
executed for each row of the result.

Correlated subqueries may appear elsewhere besides the WHERE clause; for example,
this query uses a correlated subquery in the SELECT clause to print the entire list of
employees alongside the average salary for each employee's department. Again,
because the subquery is correlated with a column of the outer query, it must be re-
executed for each row of the result.

SELECT employee_number, name,

(SELECT AVG(salary) FROM employees

WHERE department = emp.department) AS department_average

FROM employees emp

SQL Joins
Sometimes it is necessary to work with multiple tables as though they were a single
entity. Then a single SQL sentence can manipulate data from all the tables. Joins are
used to achieve this. Tables are joined on columns that have the same data type and
data width in the tables.

Tables in a database can be related to each other with keys. A primary key is a column
with a unique value for each row. The purpose is to bind data together, across tables,
without repeating all of the data in every table.

The JOIN operator specifies how to relate tables in the query.

Types of JOIN:

Miss. Hemangi Panchal Page 38

 Relational Database Management System

● Inner Join
● Outer Join (Left, Right, Full)

Inner Join (Equi Join)

Syntax

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

➔ Find the Emp name who worked in a department having location same as their
address.

Table Name: Emp

E_No E_Name Address

1 Ram Delhi

2 Varun Chd

3 Ravi Chd

4 Amrit Delhi

Table Name: Dept

Miss. Hemangi Panchal Page 39

 Relational Database Management System

Dept_No Location E_No

D1 Delhi 1

D2 Pune 2

D3 Patna 4

SQL> Select E_Name from Emp, Dept where Emp.E_No = Dept.E_No and
Emp.Address = Dept.Location;

Outer Join (Non - Equi Join)

Left Join

It gives the matching rows and the rows which are in left table but not in right table.

Syntax:

SELECT T1.C1, T2.C2

FROM TABLE T1
LEFT JOIN TABLE T2
ON T1.C1= T2.C1;

Table Name: Emp

Miss. Hemangi Panchal Page 40

 Relational Database Management System

E_No E_Name Dept_No

E1 Varun D1

E2 Amrit D2

E3 Ravi D1

E4 Nitin -

Table Name: Dept

Dept_No D_Name Location

D1 IT Delhi

D2 HR Hyd

D3 Finance Pune

SQL> Select Emp_No, E_Name, D_Name, Location from Emp LEFT OUTER JOIN

Dept ON (Emp.Dept_No = Dept.Dept_No);

Output:

Emp_No E_Name D_Name Location

E1 Varun IT Delhi

E2 Amrit HR Hyd

E3 Ravi IT Delhi

E4 Nitin - -

Right Join

Miss. Hemangi Panchal Page 41

 Relational Database Management System

It gives the matching rows and the rows which are in the Right table but not in the Left
table.

Syntax:

SELECT T1.C1, T2.C2

FROM TABLE T1
RIGHT JOIN TABLE T2
ON T1.C1= T2.C1;

Table Name: Emp

E_No E_Name Dept_No

E1 Varun D1

E2 Amrit D2

E3 Ravi D3

Table Name: Dept

Dept_No D_Name Location

D1 IT Delhi

D2 HR Hyd

D3 Finance Pune

Miss. Hemangi Panchal Page 42

 Relational Database Management System

D4 Testing Noida

SQL> Select Emp_No, E_Name, D_Name, Location from Emp RIGHT OUTER JOIN

Dept ON (Emp.Dept_No = Dept.Dept_No);

Output:

Emp_No E_Name D_Name Location

E1 Varun IT Delhi

E2 Amrit HR Hyd

E3 Ravi Finance Pune

- - Testing Noida

Full Outer Join

Full Outer Join contains the results of both the Left and Right outer joins. It is also
known as cross-join. It will provide a mixture of two tables.

Full Outer Join returns all the rows from both tables, including matching and non-
matching rows. If a row in one table does not have a matching row in the other table,
the result set will include NULL values for the columns in the table that do not have a
match.

Syntax:

Miss. Hemangi Panchal Page 43

 Relational Database Management System

SELECT * FROM T1
CROSS-JOIN T2;

Table Name: Emp

E_No E_Name Dept_No

E1 Varun D1

E2 Amrit D2

E3 Ravi D3

Table Name: Dept

Dept_No D_Name Location

D1 IT Delhi

D2 HR Hyd

D3 Finance Pune

D4 Testing Noida

SQL> Select Emp.E_Name, Dept.D_Name FROM Emp FULL JOIN Dept ON

Dept.Dept_No = Emp.Dept_No;

Output:

E_Name D_Name

Varun IT

Amrit HR

Miss. Hemangi Panchal Page 44

 Relational Database Management System

Ravi Finance

- Testing

Integrity constraints and its types

Integrity constraints are the set of predefined rules that are used to maintain the quality
of information. Integrity constraints ensure that the data insertion, data updating, data
deleting and other processes have to be performed in such a way that the data integrity
is not affected. They act as guidelines ensuring that data in the database remain
accurate and consistent. So, integrity constraints are used to protect databases. The
various types of integrity constraints are

Types of Integrity Constraints:

● Domain Constraints
● Entity integrity Constraints
● Referential integrity constraints
● Key Constraints

Miss. Hemangi Panchal Page 45

 Relational Database Management System

1. Domain constraints

● Domain constraints can be defined as the definition of a valid set of values for an
attribute.

● The data type of domain includes string, character, integer, time, date, currency,
etc. The value of the attribute must be available in the corresponding domain.

Example:

2. Entity integrity constraints

● The entity integrity constraint states that primary key value can't be null.

● This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those rows.

● A table can contain a null value other than the primary key field.

Example:

Miss. Hemangi Panchal Page 46

 Relational Database Management System

3. Referential Integrity Constraints

● A referential integrity constraint is specified between two tables.

● In the Referential integrity constraints, if a foreign key in Table 1 refers to the

Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be
null or be available in Table 2.

Example:

Miss. Hemangi Panchal Page 47

 Relational Database Management System

4. Key constraints

● Keys are the entity set that is used to identify an entity within its entity set
uniquely.

● An entity set can have multiple keys, but out of which one key will be the primary
key. A primary key can contain a unique and null value in the relational table.

Example:

Miss. Hemangi Panchal Page 48

 Relational Database Management System

Miss. Hemangi Panchal Page 49