UNIT-3: SQL: QUERIES, CONSTRAINTS,

TRIGGERS:
SQL:
It is a database language which is compatible with
RDBMS.
It provides an interface to simple relational database.
It is a simple portable language that does not require
enough learning to work with its syntax and commands.
It performs the following functions,
a). Creates tables and database structures.
b). Performs addition, deletion and modification operations
on the database structures.
c). Performs difficult queries to convert raw data into useful
information.
All these operations are performed using the concept of
DML(Data Manipulation Language) and DDL(Data
Definition Language).
Features of SQL:
1. SQL is a non-procedural and English-like language
containing statements in English sentences. Hence
SQL statements are easily understandable.
2. It minimizes the time required in creating and
maintaining the database systems.
3. It is a portable language and can be executed on
various computer systems.
4. It gives quick solutions to complex queries.
5. It enables communication between clients and
servers over a network.
6. It is also an interactive query language that allows
users to store data on adhoc bases.
7. It enables programmers to write applications for
accessing database.
Basic Form of SQL Query:
Clauses are used to construct SQL
statements so that when executed the
statement will perform a particular function.
The basic form of SQL Query is
SELECT [DISTINCT] {*I column_name
(, column_name,….)}
From table_name[alias] (, table_name,…)
[WHERE condition]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]
The most commonly used SQL Clauses are shown
in below table. An SQL Clause with in bracket [ ]
indicates the Clause is optional.

S.NO SQL CLAUSE FUNCTIONS PERFORMED

1 SELECT Specifies which columns are to
appear in the output
2 DISTINCT Eliminates duplicates
3 FROM Specifies the tables to be used
4 WHERE Filters the rows according to the
conditions
5 GROUP BY Forms groups of rows with the
same column value
6 HAVING Filters the group
7 ORDER BY Sorts the order of the output
SELECT Statement and its Clauses in DBMS:
The select statement is used to get the required data from the
database according to the conditions, if any. This data is
returned in the form of a table.
The basic syntax of the select statement is −
Select column 1, column 2 ... column N From table_name
An example of the select statement is −
<Student>
Student_Num Student_Name Student_Ph Student_Mark Student_Major
ber one s Subject
1 Andrew 6615927284 95 Literature
2 Sara 6583654865 65 Maths
3 Harry 4647567463 48 Literature
4 Sally 6537837084 30 Literature
5 Anne 7457337732 88 Maths
Query –
Select Student_Name from Student
This query yields the following result −

Student_Name
Andrew
Sara
Harry
Sally
Anne
Clauses in Select statement:
The ex. of select statement given above is quite simple and
not that useful in practice. So, there are many other clauses
associated with select statement that make it more
meaningful. Some of these are −
Where Clause:
The where clause is used to filter out data i.e it returns
information that satisfies a certain condition. For example −
Select Student_Name
From Student
Where Student_Marks >50
This query will return the following result:
Student_Name
Andrew
Sara
Anne
Group by Clause:
This is mostly used with aggregate functions to group
the result set according to the value of a column.
For example −
Select Count (Student_Number),
Student_MajorSubject
From Student
Group by Student_MajorSubject
This query will return the following result −

Count (Student_number) Student_MajorSubject

3 Literature
2 Maths
Having Clause:
This is used along with Group By clause because
Where clause could not be used by aggregate
functions. For Example −
Select Count(Student_number),
Student_MajorSubject
From Student
Group by Student_MajorSubject
Having Count(Student_Number) > 2
This query will return the following result −

Count (Student_Number) Student_MajorSubject

3 Literature
Order by Clause:
The order by keyword is used to sort the results in ascending
or descending order. By default, the order is assumed to be
ascending.
For Example −
Select Student_Name
From Student
Where Student_Marks>50
Order by Student_Marks
This query will return the following result −

Student_Name
Sara
Anne
Andrew
SQL Commands:
•SQL commands are instructions. It is used to communicate
with the database. It is also used to perform specific tasks,
functions, and queries of data.
•SQL can perform various tasks like create a table, add data
to tables, drop the table, modify the table, set permission for
users.
There are two types of SQL commands: DDL, DML.
1. Data Definition Language (DDL)
•DDL changes the structure of the table like creating a table,
deleting a table, altering a table, etc.
•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:

•CREATE
•ALTER
•DROP
a. CREATE It is used to create a new table in the
database.
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME
DATATYPES [,....]);
Example:
CREATE TABLE EMPLOYEE(Name VARCHAR2(20
), Email VARCHAR2(100), DOB DATE);
b. DROP: It is used to delete both the structure and
record stored in the table.
Syntax
DROP TABLE table_name;
Example
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
ALTER TABLE table_name ADD column_name COLUMN-
definition;
To modify existing column in the table:
ALTER TABLE table_name MODIFY (column_definitions....);
EXAMPLE
ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
d. TRUNCATE: It is used to delete all the rows from the table and
free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
2. Data Manipulation Language:
•DML commands are used to modify the
database.
•It is responsible for all form of changes in the
database.
•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:
•INSERT
•UPDATE
•DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data
into the row of a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);
For example:
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:
UPDATE table_name SET [column_name1= value1,...column_nameN = val
ueN] [WHERE CONDITION]
For example:
UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'
c. DELETE: It is used to remove one or more row from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
For example:
DELETE FROM javatpoint WHERE Author="Sonoo";
SQL Table:
SQL Table is a collection of data which is organized in terms of rows and
columns.
In DBMS, the table is known as relation and row as a tuple.
Table is a simple form of data storage. A table is also considered as a
convenient representation of relations.
•Let's see an example of the EMPLOYEE table:
In the above table, "EMPLOYEE" is the table name, "EMP_ID",
"EMP_NAME", "CITY", "PHONE_NO" are the column names. The
combination of data of multiple columns forms a row, e.g., 1, "Kristen",
"Washington" and 7289201223 are the data of one row.

EMP_ID EMP_NAME CITY PHONE_NO

1 Kristen Washington 7289201223

2 Anna Franklin 9378282882

3 Jackson Bristol 9264783838

4 Kellan California 7254728346

5 Ashley Hawaii 9638482678

Operation on Table:
1.Create table
2.Drop table
3.Delete table
4.Rename table
1. Create Table:
SQL create table is used to create a table in the database. To define the
table, you should define the name of the table and also define its columns
and column's data type.
Syntax
create table "table_name"
("column1" "data type", "column2" "data type",
"column3" "data type", ... "columnN" "data type");
Example
SQL> CREATE TABLE EMPLOYEE (
EMP_ID INT NOT NULL,
EMP_NAME VARCHAR (25) NOT NULL,
PHONE_NO INT NOT NULL,
ADDRESS CHAR (30),
PRIMARY KEY (ID)
);
If you create the table successfully, you can verify
the table by looking at the message by the SQL
server. Else you can use DESC command as
follows:
SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

•4 rows in set (0.35 sec)

Now you have an EMPLOYEE table in the database,
and you can use the stored information related to the
employees.
2. Drop table:
A SQL drop table is used to delete a table definition and all the
data from a table. When this command is executed, all the
information available in the table is lost forever, so you have to
very careful while using this command.
Syntax
DROP TABLE "table_name";
Firstly, you need to verify the EMPLOYEE table using the
following command:
SQL> DESC EMPLOYEE;
Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

EMP_N varchar NO NULL

AME (25)

PHONE NO int(11) NULL

_NO

ADDRE YES NULL char(30

SS )
•4 rows in set (0.35 sec)
This table shows that EMPLOYEE table is
available in the database, so we can drop it as
follows:
SQL>DROP TABLE EMPLOYEE;
Now, we can check whether the table exists or
not using the following command:
Query OK, 0 rows affected (0.01 sec)
As this shows that the table is dropped, so it
doesn't display it.
3. DELETE table:
In SQL, DELETE statement is used to delete rows from a table. We can use
WHERE condition to delete a specific row from a table. If you want to delete all
the records from the table, then you don't need to use the WHERE clause.
Syntax
DELETE FROM table_name WHERE condition;
Example
Suppose, the EMPLOYEE table having the following records:

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Krishna Chicago 9737287378 150000

2 Ram Austin 9262738271 200000

3 Divya Boston 7353662627 100000

4 Anshka Denver 9232673822 600000

5 Roopa Washingt 9367238263 350000

on

6 swathi Los 7253847382 260000

angels
The following query will DELETE an employee whose ID is 3.
SQL> DELETE FROM EMPLOYEE
WHERE EMP_ID = 3;
Now, the EMPLOYEE table would have the following records.
EMP_I EMP_NAME CITY PHONE_N SALARY
D O

1 Krishna Chicago 9737287378 150000

2 Ram Austin 9262738271 200000

4 Anshka Denver 9232673822 600000

5 Roopa Washington 9367238263 350000

6 swathi Los angels 7253847382 260000

If you don't specify the WHERE condition, it will remove all the rows from the table.
DELETE FROM EMPLOYEE;
Now, the EMPLOYEE table would not have any records.
SQL - UNION Operator:
The SQL UNION operator is used to combine data from
multiple tables by eliminating duplicate rows (if any).
To use the UNION operator on multiple tables, all these
tables must be union compatible. And they are said to be
union compatible if and only if they meet the following criteria
•The same number of columns selected with the same
datatype.
•These columns must also be in the same order.
•They need not have same number of rows.
Once these criterions are met, the UNION operator returns
the rows from multiple tables as a resultant table which is
void of all duplicate values from these tables.
Note − the column names in the final result set will be based
on the column names selected in the first SELECT
statement. If you want to use a different name for a column
in the final result set, you can use an alias in the SELECT
Syntax
The basic syntax of a UNION operator is as follows −
SELECT column1 [,column2]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2]
FROM table1 [, table2 ]
[WHERE condition]
Here, the given condition could be any given expression based
on your requirement.
UNION on a Single Field:
If we want to use UNION to combine the result sets of two or
more SELECT statements on a single field, we can simply
include that field in the SELECT statement of each query. The
UNION operator will automatically remove any duplicate
values in the final result set.
Example: Assume we have created a table with name
CUSTOMERS in SQL database using CREATE TABLE
statement as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Following query inserts values into this table using the
INSERT statement −
insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32,
'Ahmedabad', 2000.00);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi',
1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota',
2000.00);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25,
'Mumbai', 6500.00);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal',
8500.00);
insert INTO CUSTOMERS VALUES(6, 'Komali', 22, 'MP',
4500.00);
insert INTO CUSTOMERS VALUES(7, 'Mukesh', 24, 'Indore',
10000.00);
If we verify the contents of the CUSTOMERS
table using the SELECT statement, we can
observe the inserted records as shown below −
Table 1 − CUSTOMERS Table is as follows.
SELECT * from CUSTOMERS;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmadabad 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 Komali 22 MP 4500.00
7 Mukesh 24 Indore 10000.00
Now, creating the second table ORDERS using CREATE
TABLE statement as shown below
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATES DATETIME NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT INT NOT NULL,
PRIMARY KEY (OID)
);
Following query inserts values into this table using the INSERT
statement −
insert INTO ORDERS VALUES(102, '2009-10-08 00:00:00', 3,
3000);
insert INTO ORDERS VALUES(100, '2009-10-08 00:00:00', 3,
1500);
insert INTO ORDERS VALUES(101, '2009-11-20 00:00:00', 2,
1560);
insert INTO ORDERS VALUES(103, '2008-05-20 00:00:00', 4,
2060);
If we verify the contents of the ORDERS
table using the SELECT statement, we can
observe the inserted records as shown
below −
Table 2 − ORDERS Table is as follows.
SELECT * from ORDERS;
OID DATES CUSTOMER_ID AMOUN
T

100 2009-10-08 00:00:00 3 1500

101 2009-11-20 00:00:00 2 1560

102 2009-10-08 00:00:00 3 3000

103 2008-05-20 00:00:00 4 2060

Using the following query, let us try to combine the SALARY
and AMOUNT columns from CUSTOMERS and ORDERS
respectively since these columns have similar data types.
SELECT SALARY
FROM CUSTOMERS
UNION
SELECT AMOUNT
FROM ORDERS
Output: Output of the above query is as follows –
SALARY
2000.00
1500.00
6500.00
8500.00
4500.00
10000.00
1560.00
3000.00
2060.00
UNION on Multiple Fields:
When we use UNION on multiple fields, the number and
order of the fields in each SELECT statement must match.
Also, the data types of the fields in each SELECT statement
must be compatible for the UNION to work correctly.
If the data types are not compatible, you may need to use
conversion functions such as CAST or CONVERT to ensure
that the data types match.
Example
•As the CUSTOMERS and ORDERS tables are not union-
compatible individually, let us first join these two tables into a
bigger table using Left Join and Right Join.
•The joined tables retrieved will have same number of columns
with same data types, becoming union compatible.
•Now, these tables are combined using UNION query shown
below −
SELECT ID, NAME, AMOUNT, DATES
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATES
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Output
This would produce the following result –
ID NAME AMOUNT DATES
1 Ramesh NULL NULL
2 Khilan 1560.00 2009-11-20 00:00:00
3 kaushik 1500.00 2009-10-08 00:00:00
3 kaushik 3000.00 2009-10-08 00:00:00
4 Chaitali 2060.00 2008-05-20 00:00:00
5 Hardik NULL NULL
6 Komali NULL NULL
7 Mukesh NULL NULL
INTERSECTION in SQL:
The INTERSECTION operator in SQL is used to retrieve the records that
are identical/common between the result sets of two SELECT (tables)
statements.
In real-time scenarios, there will be a huge number of tables in a
database that contains information.
The user may find it challenging to gather common information from
various tables.
So we use the INTERSECTION operator to accomplish that. It helps to
retrieve the common data from various tables.
Syntax: To retrieve identical records from two different tables, we use the
following syntax −
SELECT column1, column2,…, columnN
FROM table1, table2… tableN
INTERSECT
SELECT column1, column2,…, columnN
FROM table1, table2,…, tableN
Note − There are some mandatory rules for INTERSECTION operations
such as the number of columns, data types, and other columns must be the
same in both SELECT statements for the INTERSECTION operator to work
correctly.
Example: First of all, let us create a table named “STUDENTS” using the
following query −
SQL> CREATE TABLE STUDENTS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
HOBBY VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY(ID)
);
Once the table is created, let us insert some values to the table using the query
below −
SQL> INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, 'Vijay',
'Cricket', 18);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(2, 'Varun',
'Football', 26);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(3, 'Surya',
'Cricket', 19);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(4, 'Karthik',
'Cricket', 25);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(5, 'Sunny',
'Football',26);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(6, 'Dev',
'Cricket', 23);
Let us verify whether the table “STUDENTS” is
created or not using the following query −
SQL> SELECT * FROM STUDENTS;
As we can see in the below output, the table has
been created in the database.

ID NAME HOBBY AGE

1 Vijay Cricket 18
2 Varun Football 26
3 Surya Cricket 19
4 Karthik Cricket 25
5 Sunny Football 26
6 Dev Cricket 23
Let us create another table “ASSOCIATES” using the following query −
SQL> CREATE TABLE ASSOCIATES(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SUBJECT VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
HOBBY VARCHAR(20) NOT NULL,
PRIMARY KEY(ID)
);
Let’s insert some values into the table using the following query −
SQL> INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY)
VALUES(1, 'Naina', 'Maths', 24, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY)
VALUES(2, 'Varun', 'Physics', 26, 'Football');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY)
VALUES(3, 'Dev', 'Maths', 23, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY)
VALUES(4, 'Priya', 'Physics', 25, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY)
VALUES(5, 'Aditya', 'Chemistry', 21, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY)
VALUES(6, 'Kalyan', 'Maths', 30, 'Football');
query −
SQL> SELECT * FROM ASSOCIATES;
As we can see in the below output, the table has been created in the database.
ID NAME SUBJECT AGE HOBBY
1 Vijay Maths 24 Cricket
2 Varun Physics 26 Football
3 Surya Maths 23 Cricket
4 Karthik Physics 25 Cricket
5 Sunny Chemistry 21 Cricket
6 Dev Maths 30 Football

Now, we are trying to retrieve the common records from both the tables using the
following query −
SQL> SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
Output: When we execute the above query, the output is obtained as follows –
NAME AGE HOBBY
Dev 23 Cricket
Varun 26 Football
 SQL – EXCEPT: The EXCEPT operator in SQL is used to
retrieve the unique records that exist in the first table, not the
common records of both tables. This operator acts as the
opposite of the SQL UNION operator.
For better understanding consider two tables with records as
shown in the following image –

If we perform the EXCEPT operator on the above two tables to

retrieve the names, it will display the records only from the first table
which are not in common with the records of the second table.
Here, “Dev” is common in both tables. So, the EXECPT operator will
eliminate it and retrieves only “Sara” and “Jay” as output.
Syntax
Following is the syntax of the EXCEPT operator in
SQL −
SELECT column1, column2,…, columnN
FROM table1, table2,…, tableN
[Conditions] //optional
EXCEPT
SELECT column1, column2,…, columnN
FROM table1, table2,…, tableN
[Conditions] //optional
Note − The number and order of columns in both
SELECT statements should be the same.
Example
First of all, let us create a table named “STUDENTS”
using the following query −
SQL> CREATE TABLE STUDENTS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
HOBBY VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY(ID)
);
Once the table is created, let us insert some values to the table using the
query below −
SQL> INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1,
'Vijay', 'Cricket', 18);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(2, 'Varun',
'Football', 26);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(3, 'Surya',
'Cricket', 19);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(4, 'Karthik',
'Cricket', 25);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(5, 'Sunny',
'Football', 26);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(6, 'Dev',
'Cricket', 23);
Let us verify whether the table “STUDENTS” is created or not using the following
query −
SQL> SELECT * FROM STUDENTS;
As we can see in the below output, the table has been created in the database .
ID NAME HOBBY AGE
1 Vijay Cricket 18
2 Varun Football 26
3 Surya Cricket 19
4 Karthik Cricket 25
5 Sunny Football 26
6 Dev Cricket 23

Let us create another table named “ASSOCIATES” using the following query −
SQL> CREATE TABLE ASSOCIATES(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SUBJECT VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
HOBBY VARCHAR(20) NOT NULL,
PRIMARY KEY(ID)
);
Once the table is created, let us insert some values to the table using the query below −
SQL> INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1,
'Naina', 'Maths', 24, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(2, 'Varun',
'Physics', 26, 'Football');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(3, 'Dev',
'Maths', 23, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(4, 'Priya',
'Physics', 25, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(5, 'Aditya',
'Chemistry', 21, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(6, 'Kalyan',
'Maths', 30, 'Football');
Let us verify whether the table “ASSOCIATES” is created or not using the following query
−
SQL> SELECT * FROM ASSOCIATES;
As we can see in the below output, the table has been created in the database.
ID NAME SUBJECT AGE HOBBY
1 Naina Mathematics 24
2 Varun Physics 26
3 Dev Mathematics 23
4 Priya Physics 25
5 Adithya Chemistry 21
6 Kalyan Mathematics 30
Let us retrieve the records that are only unique in
the first table using the below query −
SQL> SELECT NAME, HOBBY, AGE
FROM STUDENTS
EXCEPT
SELECT NAME, HOBBY, AGE
FROM ASSOCIATES
Output: When we execute the above query, the
output is obtained as follows –

NAME HOBBY AGE

Karthik Cricket 25
Sunny Football 26
Surya Cricket 19
Vijay Cricket 18
Nested Queries in SQL:
A Subquery or Inner query or a Nested query is a query within another
SQL query and embedded within clauses, most commonly in the WHERE
clause. It is used to return data from a table, and this data 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
Nested queries 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 32 Ahmadaba 2000.00
d
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 Komali 22 MP 4500.00
7 Mukesh 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 ADDRES SALARY

S
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Mukesh 24 Indore 10000.00
Nested queries 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);
Nested queries with the UPDATE Statement
The Nested query 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 tableSET 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 32 Ahmadabad 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 Komali 22 MP 4500.00
7 Mukesh 24 Indore 10000.00
Nested queries 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.
 Nested queries are a way to perform more complex
queries by embedding one query within another.
A nested query is a query that appears inside another
query, and it helps retrieve data from multiple tables or apply
conditions based on the results of another query.
The result of inner query is used in execution of outer
query. We will use STUDENT, COURSE,
STUDENT_COURSE tables for understanding nested
queries.
ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komali 22 MP 4500.00
7 Mukesh 24 Indore 10000.00
STUDENT S_ID S_NAM S_ADDRE S_PHONE S_AG
E SS E
S1 RAM DELHI 9455123451 18
S2 RAMES GURGAON 9652431543 18
H
S3 SUJIT ROHTAK 9156253131 20
S4 SURES DELHI 9156768971 18
H

COURSE
C_ID C_NAME
C1 DSA
C2 Programming

C3 DBMS

S_ID C_ID
S1 C1
STUDENT_COURSE S1 C3
S2 C1
S3 C2

S4 C2
S4 C3
SQL Aggregate Functions:
•SQL aggregation function is used to perform
the calculations on multiple rows of a single
column of a table. It returns a single value.
•It is also used to summarize the data.
Types of SQL Aggregation Function
1. COUNT FUNCTION:
•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.
•COUNT function uses the COUNT(*) that returns the count of all the rows
in a specified table. COUNT(*) considers duplicate and Null.
Syntax
COUNT(*)
or
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
Example: COUNT()
SELECT COUNT(*)
FROM PRODUCT_MAST;
Output:
10
Example: COUNT with WHERE
SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;
Output:
7
Example: COUNT() with DISTINCT
SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;
Output:
3
Example: COUNT() with GROUP BY
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
Output:
Com1 5
Com2 3
Com 2
Example: COUNT() with HAVING
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
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
SUM()
or
SUM( [ALL|DISTINCT] expression )
Example: SUM()
SELECT SUM(COST)
FROM PRODUCT_MAST;
Output:
670
Example: SUM() with WHERE
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;
Output:
320
Example: SUM() with GROUP BY
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;
Output:
Com1 150
Com2 170
Example: SUM() with HAVING
SELECT COMPANY, SUM(COST)
FROM PRODUCT_MAST
GROUP BY COMPANY
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
AVG()
or
AVG( [ALL|DISTINCT] expression )
Example:
SELECT AVG(COST)
FROM PRODUCT_MAST;
Output:
67
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:
MAX()
or
MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(RATE)
FROM PRODUCT_MAST;
Output:
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
MIN()
or
MIN( [ALL|DISTINCT] expression )
Example:
SELECT MIN(RATE)
FROM PRODUCT_MAST;
Output:
10
SQL - NULL Value:
•A field with a NULL value is a field with no value.
•If a field in a table is optional, it is possible to insert a new record or
update a record without adding a value to this field. Then, the field will
be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that
contains spaces. A field with a NULL value is one that has been left
blank during record creation!
•It is not possible to test for NULL values with comparison operators,
such as =, <, or <>.
•We will have to use the IS NULL and IS NOT NULL operators
instead.
IS NULL Syntax:
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax:
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
For the demo purpose, the following Employee table will be
used in all examples here.

EmpId FirstName LastName Email phoneNo salary

1 Sandeep king sand@gm 86881765 80000

ail.com 66
2 haritha lion 81259698 95000
66
3 pallavi james pallavi@g 75000
mail.com

4 premsai lovesai prem@ya 83175391

hoo.com 78

The following example will select employees

with PhoneNo as NULL.
SELECT * FROM Employee WHERE PhoneNo IS NULL;
The above query will display the following result.
EmpId FirstName LastName Email phoneNo salary
3 pallavi james pallavi@gm 75000
ail.com
The following query uses IS NOT NULL to return data
whose Email value is not NULL.
SELECT * FROM Employee WHERE Email IS NOT NULL;
The above query will display the following result.
EmpId FirstName LastName Email phoneNo salary
1 Sandeep king sand@gmail.com 8688176566 80000

3 pallavi tiger pallavi@gmail.com 75000

4 premsai lovesai prem@yahoo.com 8317539178

Update the NULL value using the UPDATE statement, as shown below.
UPDATE Employee SET Salary = NULLWHERE EmpId = 1;
The above query will display the following result.
EmpId FirstName LastName Email phoneNo salary
1 Sandeep king sand@gmail.com 8688176566
2 haritha lion 8125969866 95000
3 pallavi tiger pallavi@gmail.com 75000
4 premsai lovesai prem@yahoo.com 8317539178
Triggers:
A trigger is a procedure which is automatically invoked by the DBMS in
response to changes to the database, and is specified by the database
administrator (DBA). A database with a set of associated triggers is
generally called an active database.
A triggers description contains three parts, which are as
•Event − An event is a change to the database which activates the
trigger.
•Condition − A query that is run when the trigger is activated is called
as a condition.
•Action −A procedure which is executed when the trigger is activated
and its condition is true.
Use of trigger:
Triggers may be used for any of the following reasons −
•To implement any complex business rule, that cannot be implemented
using integrity constraints.
•Triggers will be used to audit the process. For example, to keep track
of changes made to a table.
•Trigger is used to perform automatic action when another concerned
action takes place.
Types of triggers:
1. Statement level trigger − It is fired only once for DML statement
irrespective of number of rows affected by statement. Statement-level
triggers are the default type of trigger.

2. Before-triggers − At the time of defining a trigger we can specify

whether the trigger is to be fired before a command like INSERT,
DELETE, or UPDATE is executed or after the command is executed.
Before triggers are automatically used to check the validity of data
before the action is performed. For instance, we can use before
trigger to prevent deletion of rows if deletion should not be allowed in
a given case.

3. After-triggers − It is used after the triggering action is completed.

For example, if the trigger is associated with the INSERT command
then it is fired after the row is inserted into the table.
4. Row-level triggers − It is fired for each row that is affected by
DML command. For example, if an UPDATE command updates 150
rows then a row-level trigger is fired 150 times whereas a statement-
level trigger is fired only for once.
Create database trigger:
To create a database trigger, we use the CREATE TRIGGER
command. The details to be given at the time of creating a
trigger are as follows −
Name of the trigger.
Table to be associated with.
When trigger is to be fired: before or after.
Command that invokes the trigger- UPDATE, DELETE, or
INSERT.
Whether row-level triggers or not.
Condition to filter rows.
PL/SQL block is to be executed when trigger is fired.
The syntax to create database trigger is
CREATE [OR REPLACE] TRIGGER triggername{BEFORE|
AFTER}{DELETE|INSERT|UPDATE[OF COLUMNS]} ON
table[FOR EACH ROW {WHEN condition]][REFERENCE [OLD
AS old] [NEW AS new]]BEGINPL/SQL BLOCKEND.
Active databases:
An active Database is a database consisting of a set of triggers.
These databases are very difficult to be maintained because of
the complexity that arises in understanding the effect of these
triggers.
In such database, DBMS initially verifies whether the particular
trigger specified in the statement that modifies the database is
activated or not, prior to executing the statement.
If the trigger is active then DBMS executes the condition part and
then executes the action part only if the specified condition is
evaluated to true.
It is possible to activate more than one trigger within a single
statement.
In such situation, DBMS processes each of the trigger randomly.
The execution of an action part of a trigger may either activate
other triggers or the same trigger that Initialized this action. Such
type of trigger that activates itself is called as ‘recursive trigger’.
The DBMS executes such chains of trigger in some pre-defined
manner but it affects the concept of understanding.
Features of Active Database:
It possess all the concepts of a conventional database i.e.
data modeling facilities, query language etc.
It supports all the functions of a traditional database like data
definition, data manipulation, storage management etc.
It supports definition and management of ECA rules.
It detects event occurrence.
It must be able to evaluate conditions and to execute actions.
It means that it has to implement rule execution.
Advantages:
Enhances traditional database functionalities with powerful
rule processing capabilities.
Enable a uniform and centralized description of the business
rules relevant to the information system.
Avoids redundancy of checking and repair operations.
Suitable platform for building large and efficient knowledge
base and expert systems.
INTRODUCTION TO SCHEMA REFINEMENT:
Normalization or Schema Refinement is a technique
of organizing the data in the database.
It is a systematic approach of decomposing tables to
eliminate data redundancy and undesirable characteristics
like Insertion, Update and Deletion Anomalies.
The Schema Refinement refers to refine the schema by
using some technique.
The best technique of schema refinement is decomposition.
Identifying and clearing the future problems in the database
is called schema refinement.
In this refinement main problem is data
redundancy.
It is avoided by normalization technique.
The Basic Goal of Normalization is used to eliminate
redundancy.
Redundancy refers to repetition of same data or duplicate
copies of same data stored indifferent locations.
INTRODUCTION TO SCHEMA REFINEMENT:
Normalization or Schema Refinement is a technique of
organizing the data in the database.
It is a systematic approach of decomposing tables to eliminate
data redundancy and undesirable characteristics like Insertion,
Update and Deletion Anomalies.
The Schema Refinement refers to refine the schema by using
some technique.
The best technique of schema refinement is decomposition.
Identifying and clearing the future problems in the database is
called schema refinement.
In this refinement main problem is data redundancy.
It is avoided by normalization technique.
The Basic Goal of Normalization is used to eliminate
redundancy.
Redundancy refers to repetition of same data or duplicate copies
of same data stored indifferent locations.
Properties of Decomposition:
Following are the properties of Decomposition,
1. Lossless Decomposition
2. Dependency Preservation
3. Lack of Data Redundancy
1. Lossless Decomposition:
•Decomposition must be lossless. It means that the information
should not get lost from the relation that is decomposed.
•It gives a guarantee that the join will result in the same relation
as it was decomposed. Example: Let's take 'E' is the Relational
Schema, With instance 'e'; is decomposed into: E1, E2, E3, . . .
. En; With instance: e1, e2, e3, . . . . en, If e1 ⋈ e2 ⋈ e3 . . . .⋈
en, then it is called as 'Lossless Join Decomposition'.
•In the above example, it means that, if natural joins of all the
decomposition give the original relation, then it is said to be
lossless join decomposition.
Example: <Employee_Department> Table
Eid Ename Age City Salary Deptid Deptname

E001 DIVYA 21 KHAMMAM 80000 D001 CIVIL

E002 NAZIYA 25 HYDERABAD 75000 D002 ECE

E003 KAVYA 28 WARANGAL 90000 D003 EEE

E004 ISRATH 22 DUBAI 85000 D004 CSE

E005 SADIYA 29 GOA 70000 D005 MECH

•Decompose the above relation into two relations to check

whether decomposition is lossless or lossy.
•Now, we have decomposed the relation that is Employee and
Department.
Relation 1 : <Employee> Table
Employee Schema contains (Eid, Ename, Age, City, Salary).
Eid Ename Age City Salary
E001 DIVYA 21 KHAMMAM 80000
E002 NAZIYA 25 HYDERABAD 75000
E003 KAVYA 28 WARANGAL 90000
E004 ISRATH 22 DUBAI 85000
E005 SADIYA 29 GOA 70000
 Relation 2 : <Department> Table

Deptid Eid Deptname

D001 E001 CIVIL

D002 E002 ECE

D003 E003 EEE

D004 E004 CSE

D005 E005 MECH

•Department Schema contains (Deptid, Eid, DeptName).

•So, the above decomposition is a Lossless Join Decomposition,
because the two relations contain one common field that is 'Eid' and
therefore join is possible.
•Now apply natural join on the decomposed relations.
Employee∞Department
Example: <Employee_Department> Table
Eid Ename Age City Salary Deptid Deptname
E001 DIVYA 21 KHAMMAM 80000 D001 CIVIL
E002 NAZIYA 25 HYDERABAD 75000 D002 ECE
E003 KAVYA 28 WARANGAL 90000 D003 EEE
E004 ISRATH 22 DUBAI 85000 D004 CSE
E005 SADIYA 29 GOA 70000 D005 MECH

Hence, the decomposition is Lossless Join

Decomposition.
•If the <Employee> table contains (Eid, Ename,
Age, City, Salary) and <Department> table contains
(Deptid and DeptName), then it is not possible to
join the two tables or relations, because there is no
common column between them. And it becomes
Lossy Join Decomposition.
2. Dependency Preservation:
Dependency is an important constraint on the database.
Every dependency must be satisfied by at least one
decomposed table.
If {A → B} holds, then two sets are functional dependent. And, it
becomes more useful for checking the dependency easily if both
sets in a same relation.
This decomposition property can only be done by maintaining
the functional dependency.
In this property, it allows to check the updates without computing
the natural join of the database structure.
3. Lack of Data Redundancy:
Lack of Data Redundancy is also known as a Repetition of
Information.
The proper decomposition should not suffer from any data
redundancy.
The careless decomposition may cause a problem with the data.
The lack of data redundancy property may be achieved by
Normalization process.
 Functional Dependencies:
Functional dependency is a constraint between two sets of
attributes in a relation from a database.
In other words, functional dependency is a constraint that
describes the relationship between attributes in a relation.
A functional dependency (FD) is a kind of IC that generalizes
the concept of a key. Functional dependency says that if two tuples
have same values for attributes A1, A2,..., An, then those two tuples
must have to have same values for attributes B1, B2, ..., Bn.
Functional dependency is represented by an arrow sign (→)
that is, X→Y, where X functionally determines Y. The left-hand side
attributes determine the values of attributes on the right-hand side.
In other words, a dependency FD: X → Y means that the values
of Y are determined by the values of X. Two tuples sharing the
same values of X will necessarily have the same values of Y.
Let R be a relation schema and let X and Y be nonempty sets of
attributes in R. We say that an instance r of R satisfies the FD X→ Y
if the following holds for every pair of tuples t1 and t2 in r.
 If t1:X = t2:X, then t1:Y = t2:Y .
The following table illustrates A→ B
A B
1 1
2 4
3 9
4 16
2 4
7 9

Since for each value of A there is associated one and only one value of B.
The following table illustrates that A does not functionally determine B.
A B
1 1
2 4
3 9
4 16
3 10

Since for each value of A there is associated one and only one value of B.
The following illustrates the meaning of the FD AB  C by showing an
instance that satisfies this dependency.
A B C D
a1 b1 c1 d1
a1 b1 c1 d2
a1 b2 c2 d1
a2 b1 c3 d1
NORMAL FORMS: Normalization is a process of evaluating and
organizing the data in database to avoid data redundancy and
inconsistency of the data. It is the process of decomposing the
relations with anomaly to produce smaller, well structure relations It
is a formal process for describing which attributes should
be group together in a relation based on the concept of
determination
WHY WE NEED NORMALIZATION?
Normalization is the aim of well design Relational
Database Management System (RDBMS). It is step by step set
of rules by which data is put in its simplest forms. We normalize the
relational database management system because of the following
reasons:
Minimize data redundancy i.e. no unnecessarily duplication of
data.
To make database structure flexible i.e. it should be possible to
add new data values and rows without reorganizing the database
structure.
Data should be consistent throughout the database i.e. it should
not suffer from following anomalies.
Update anomalies: If data items are scattered and are
not linked to each other properly, then it could lead to
strange situations.
For example, when we try to update one data item having
its copies scattered over several places, a few instances
get updated properly while a few others are left with old
values. Such instances leave the database in an
inconsistent state.
Deletion anomalies: We tried to delete a record, but
parts of it was left undeleted because of unawareness,
the data is also saved somewhere else.
Insert anomalies: We tried to insert data in a record that
does not exist at all. Normalization is a method to remove
all these anomalies and bring the database to a consistent
state.
ADVANTAGES OF NORMALIZATION:
More efficient data structure.
Avoid redundant fields or columns.
More flexible data structure i.e. we should be able to add new rows and
data values easily
Better understanding of data.
Ensures that distinct tables exist when necessary.
Minimizes data duplication.
DISADVANTAGES OF NORMALIZATION:
You cannot start building the database before you know what the user
needs.
On Normalizing the relations to higher normal forms i.e. 4NF, 5NF the
performance degrades.
It is very time consuming and difficult process in normalizing relations of
higher degree.
Careless decomposition may leads to bad design of database which may
leads to serious problems.
Normalization rule are divided into following normal form.
1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. BCNF
 1. First Normal Form (1NF): As per First Normal Form, no
two Rows of data must contain repeating group of information i.e
each set of column must have a unique value, such that multiple columns
cannot be used to fetch the same row. Each table should be organized into
rows, and each row should have a primary key that distinguishes it as
unique.
The Primary key is usually a single column, but sometimes more than
one column can be combined to create a single primary key.
For example consider a table which is not in First normal form i.e an
attribute (column) of a table cannot hold multiple values. It should hold
only atomic values.
Example: Suppose a company wants to store the names and contact
details of its employees. It creates a table that looks like this:

emp_id emp_name emp_address emp_mobile

101 likhitha khammam 9949897651
102 mahitha bangaluru 9441317854
9390323231
103 chotu goa 9550196636
104 bhargavi hyderabad 9959169875
9291365870
 Two employees (mahitha & bhargavi) are having two mobile numbers
so the company stored them in the same field as you can see in the table
above.
This table is not in 1NF as the rule says “each attribute of a table must
have atomic (single) values”, the emp_mobile values for employee’s
mahitha & bhargavi violates that rule.
To make the table complies with 1NF we should have the data like
this:

emp_id emp_name emp_address emp_mobile

101 likhitha khammam 9949897651
102 mahitha bangaluru 9441317854
102 mahitha bangaluru 9390323231
103 chotu goa 9550196636
104 bhargavi hyderabad 9959169875
104 bhargavi hyderabad 9291365870
Second Normal Form (2NF):
A table is said to be in 2NF if both the following conditions hold:
•Table is in 1NF (First normal form).
•No non-prime attribute is dependent on the proper subset of any candidate
key of table.
•No Partial dependency exists between non key attributes and key
attributes
An attribute that is not part of any candidate key is known as non-prime
attribute.
Note: Every Non key attribute is fully functional dependant on key attribute.
Example: Suppose a school wants to store the data of teachers and the
subjects they teach. They create a table that looks like this: Since a teacher
can teach more than one subjects, the table can have multiple rows for a
same teacher.
Teacher_id subjects Teacher_age

111 ADC 38
111 EMI 38
222 SS 43
333 VLSI 45
333 DSP 45
 Candidate Keys: {teacher_id, subject} Non prime attribute: teacher_age
The table is in 1 NF because each attribute has atomic values. However, it is not in
2NF because non prime attribute teacher_age is dependent on teacher_id alone
which is a proper subset of candidate key. This violates the rule for 2NF
as the rule says “no non-prime attribute is dependent on the proper
subset of any candidate key of the table”.
To make the table complies with 2NF we can break it in two tables like this:
teacher_details table:

Teacher_id Teacher_age

111 38
222 43
333 45

teacher_subject table:

Teacher_id subjects
111 ADC
111 EMI
222 SS
333 VLSI
333 DSP
Third Normal form (3NF):
A table design is said to be in 3NF if both the following conditions hold:
•Table must be in 2NF
•Transitive functional dependency of non-prime attribute on any super key
should be removed.
An attribute that is not part of any candidate key is known as non-prime
attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and
for each functional dependency X-> Y at least one of the following conditions
hold:
•X is a super key of table
•Y is a prime attribute of table
An attribute that is a part of one of the candidate keys is known as prime
attribute.
Example: Suppose a company wants to store the complete address of each
employee, they create a table named employee_details that looks like this:

Emp_id Emp_name Emp_zip Emp_state Emp_city Emp_district

1001 Javeed 507001 telangana khammam khammam
1002 Trinesh 222008 Tamilnadu Chennai M-city
1006 Ram 222071 Tamilnadu chennai murappakam
1101 Ramesh 220876 UK Pauri Bhagwan
1201 mahesh 328002 MP Gwalior Ratan
Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so on
Candidate Keys: {emp_id}
Non-prime attributes: all attributes except emp_id are non-prime as they are not part of any
candidate keys.
Here, emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent
on emp_id that makes non-prime attributes (emp_state, emp_city & emp_district) transitively
dependent on super key (emp_id). This violates the rule of 3NF.
To make this table complies with 3NF we have to break the table into two tables to remove the
transitive dependency:
employee table:

Emp_id Emp_name Emp_zip

1001 Javeed 507001

1002 Trinesh 222008
1006 Ram 222071
1101 Ramesh 220876
1201 mahesh 328002

employee_zip table:
Emp_zip Emp_state Emp_city Emp_district
507001 telangana khammam khammam
222008 Tamilnadu Chennai M-city
222071 Tamilnadu chennai murappakam
220876 UK Pauri Bhagwan
328002 MP Gwalior Ratan
Boyce Codd normal form (BCNF):
It is an advance version of 3NF that’s why it is also
referred as 3.5NF. BCNF is stricter than 3NF.A table
complies with BCNF if it is in 3NF and for every
functional dependency X->Y, X should be the super
key of the table.
Example: Suppose there is a company wherein
employees work in more than one department. They
store the data like this:

Emp_id Emp_nationality Em_dept Emp_type Dept_no_of_emp

1001 America Production and D001 200
planning
1001 America Stores D001 250
1002 Australia Design and
technical support D134 100
1002 Australia Purchasing
department D134 600
Functional dependencies in the table above:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate key: {emp_id, emp_dept}
The table is not in BCNF as neither emp_id nor emp_dept alone are keys.
To make the table comply with BCNF we can break the table in three tables
like this:
emp_nationality table:

Emp_id Emp_nationality
1001 America
1002 Australia

emp_dept table:

Em_dept Emp_type Dept_no_of_emp

Production and planning D001 200
Stores D001 250
Design and technical
support D134 100
Purchasing department
D134 600
emp_dept_mapping table:
Emp_id Em_dept
1001 Production and planning
1001 Stores
1002 Design and technical support
1002 Purchasing department

Functional dependencies:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}
This is now in BCNF as in both the functional
dependencies left side part is a key.
Fourth Normal Form (4NF)
The Fourth Normal Form (4NF) is a level of database
normalization where there are no non-trivial multivalued
dependencies other than a candidate key. It builds on the first
three normal forms (1NF, 2NF, and 3NF) and the Boyce-Codd
Normal Form (BCNF). It states that, in addition to a database
meeting the requirements of BCNF; it must not contain more
than one multivalued dependency.
Properties
A relation R is in 4NF if and only if the following conditions are
satisfied:
1. It should be in the Boyce-Codd Normal Form (BCNF).2. The
table should not have any Multi-valued Dependency.
A table with a multivalued dependency violates the
normalization standard of the Fourth Normal Form (4NF)
because it creates unnecessary redundancies and can
contribute to inconsistent data. To bring this up to 4NF, it is
necessary to break this information into two tables.
 Example: Consider the database table of a class that has two relations
R1 contains student ID(SID) and student name (SNAME) and R2
contains course id(CID) and course name (CNAME).
Table R1
SID SNAME
S1 A
S2 B
Table R2
SID SNAME

C1 C

C2 D

When their cross-product is done it resulted in multivalued

dependencies.
Table R1 X R2
SID SNAME CID CNAME
S1 A C1 C
S1 A C2 D
S2 B C1 C
S2 B C2 D

Multivalued dependencies (MVD) are:

SID->->CID; SID->->CNAME; SNAME->->CNAME
Fifth Normal Form / Projected Normal Form (5NF):
A relation R is in Fifth Normal Form if and only if everyone joins
dependency in R is implied by the candidate keys of R. A relation
decomposed into two relations must have lossless join Property, which
ensures that no spurious or extra tuples are generated when relations are
reunited through a natural join.
Properties
A relation R is in 5NF if and only if it satisfies the following conditions:
1. R should be already in 4NF. 2. It cannot be further non loss decomposed
(join dependency).
Example – Consider the above schema, with a case as “if a company
makes a product and an agent is an agent for that company, then he
always sells that product for the company”. Under these circumstances,
the ACP table is shown as:
Table ACP

Agent Company Product

A1 PQR Nut
A1 PQR Bolt
A1 XYZ Nut
A1 XYZ Bolt
A2 PQR Nut
 The relation ACP is again decomposed into 3 relations. Now, the natural
Join of all three relations will be shown as:
Table R1
Agent Company
A1 PQR
A1 XYZ
A2 PQR

Table R2
Agent Product
A1 Nut
A1 Bolt
A2 Nut

Table R3
Company Product
PQR Nut
PQR Bolt
XYZ Nut
XYZ Bolt
The result of the Natural Join of R1 and R3 over ‘Company’ and then the
Natural Join of R13 and R2 over ‘Agent’ and ‘Product’ will be Table ACP.
Hence, in this example, all the redundancies are eliminated, and the
decomposition of ACP is lossless join decomposition. Therefore, the relation is in
5NF as it does not violate the property of lossless join.