Data Base

[store|manage|automate]
A database is an organized collection of data that is stored and managed so it can be easily
accessed, updated, and analyzed.

In simple terms, a database is like a digital filing system where information is kept in a structured
way.

Key Idea

A database helps you:

 Store data (like names, numbers, records)

 Retrieve data quickly
 Update or delete data efficiently
 Keep data organized and secure

Real-Life Examples

 A school database stores student names, grades, and attendance

 A bank database keeps account details and transactions
 A social media platform stores user profiles, posts, and messages

Types of Databases

 Relational databases (e.g., tables with rows and columns)

 NoSQL databases (flexible formats like documents or key-value pairs)
 Distributed databases (data stored across multiple systems)

Database Software
Programs used to manage databases are called Database Management Systems (DBMS), such
as:

 MySQL
 Oracle Database
 Microsoft SQL Server

DBMS (Database Management System)

A DBMS is software used to create, store, manage, and retrieve data from a database.

It helps users:

 Store data
 Update data
 Delete data
 Retrieve data easily

Examples of DBMS

 Microsoft Access
 dBASE
 FoxPro

Features

 Data storage
 Data manipulation
 Backup and recovery
 Security management

RDBMS (Relational Database Management System)

An RDBMS is an advanced type of DBMS where data is stored in the form of tables (rows and
columns) and relationships are created between tables.

It follows the concept of relations.

Examples of RDBMS

 MySQL
 Oracle Database
 PostgreSQL
  Microsoft SQL Server

Difference Between DBMS and RDBMS

Feature DBMS RDBMS

Full Form Database Management System Relational Database Management System

Data Storage Files or simple structure Tables (rows & columns)

Relationship Between Data Not supported well Supported using keys

Normalization Less support Strong normalization support

Security Lower Higher

Multi-user Support Limited Better multi-user access

Data Redundancy More Less

Examples FoxPro, dBASE MySQL, Oracle

Create Database and Use Database – Notes

1. CREATE DATABASE

The CREATE DATABASE statement is used to create a new database in SQL.

Syntax
CREATE DATABASE database_name;

Example
CREATE DATABASE SchoolDB;

This command creates a database named SchoolDB.

2. USE DATABASE

The USE statement is used to select a database so that SQL operations can be performed on it.

Syntax
USE database_name;

Example
USE SchoolDB;

After using this command, all tables and operations will be performed inside SchoolDB.

Complete Example
CREATE DATABASE SchoolDB;

USE SchoolDB;

Explanation
Command Purpose

CREATE DATABASE Creates a new database

USE Selects the database to work on

Important Points
 Database names should be unique.
 After creating a database, use USE to activate it.
 SQL commands are usually ended with a semicolon ;.

CREATE TABLE in SQL

The CREATE TABLE statement is used to create a new table inside a database.

A table stores data in the form of rows and columns.

🔹 Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint
);

🔹 Example
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT,
Email VARCHAR(100) UNIQUE
);

🔹 Explanation
Column Datatype Constraint Meaning

Student_ID INT PRIMARY KEY Unique ID for each student

Name VARCHAR(50) NOT NULL Name cannot be empty

Age INT — Stores age

Email VARCHAR(100) UNIQUE Duplicate emails not allowed

What is a Datatype?
A datatype defines the type of value that can be stored in a column.

It tells the database:

 What kind of data to store

 How much storage is needed
 What operations can be performed
🔹 Common Datatypes
Datatype Description Example

INT Integer numbers 10, 25

FLOAT Decimal numbers 45.6

CHAR(n) Fixed-length text 'A'

VARCHAR(n) Variable-length text "Rahul"

DATE Date values '2026-05-09'

BOOLEAN True/False values TRUE

What are Constraints?

Constraints are rules applied to columns to maintain data accuracy and integrity.

They prevent invalid data from entering the table.

🔹 Common Constraints
Constraint Purpose

PRIMARY KEY Uniquely identifies each row

NOT NULL Prevents empty values

UNIQUE Prevents duplicate values

DEFAULT Assigns default value

CHECK Applies condition on values

FOREIGN KEY Creates relationship between tables

🔹 Example with Constraints
CREATE TABLE Employees (
Emp_ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary FLOAT CHECK (Salary > 0),
City VARCHAR(30) DEFAULT 'Delhi'
);

Advantages of Using Correct Datatypes

Advantage Explanation

Saves Storage Space Correct datatype uses less memory

Improves Performance Faster searching and processing

Better Data Accuracy Prevents wrong data entry

Easier Data Handling Sorting and calculations become easy

Increases Security Reduces chances of invalid data

Advantages of Constraints
Advantage Explanation

Maintains Data Integrity Keeps data accurate and consistent

Prevents Duplicate Data UNIQUE and PRIMARY KEY avoid repetition

Avoids Invalid Entries CHECK and NOT NULL validate data

Maintains Relationships FOREIGN KEY links tables correctly

Improves Reliability Data becomes trustworthy

Real-Life Example
In a banking system:

 Account number must be unique → PRIMARY KEY

  Balance cannot be negative → CHECK
 Customer name cannot be empty → NOT NULL

This ensures safe and accurate data storage.

Important Point
Using proper datatypes and constraints makes a database:

 Faster
 Safer
 More reliable
 Easier to manage

ALTER Command in SQL

The ALTER command is used to modify the structure of an existing table in a database.

Using ALTER, we can:

 Add new columns

 Modify existing columns
 Delete columns
 Rename columns or tables
 Add or remove constraints

🔹 Syntax
ALTER TABLE table_name
operation;

1. ADD COLUMN
Used to add a new column to an existing table.

🔹 Syntax
ALTER TABLE table_name
ADD column_name datatype;
🔹 Example
ALTER TABLE Students
ADD Phone VARCHAR(15);

This adds a Phone column to the Students table.

2. MODIFY COLUMN
Used to change the datatype or size of a column.

🔹 Syntax
ALTER TABLE table_name
MODIFY column_name new_datatype;
🔹 Example
ALTER TABLE Students
MODIFY Name VARCHAR(100);

This changes the size of the Name column.

3. DROP COLUMN
Used to delete a column from a table.

🔹 Syntax
ALTER TABLE table_name
DROP COLUMN column_name;
🔹 Example
ALTER TABLE Students
DROP COLUMN Phone;

This removes the Phone column.

4. RENAME COLUMN
Used to change the name of a column.

🔹 Syntax
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
🔹 Example
ALTER TABLE Students
RENAME COLUMN Name TO Student_Name;

5. ADD CONSTRAINT
Used to add constraints to existing columns.

🔹 Example
ALTER TABLE Students
ADD UNIQUE (Email);

This makes the Email column unique.

6. RENAME TABLE
Used to rename a table.

🔹 Syntax
ALTER TABLE old_table_name
RENAME TO new_table_name;
🔹 Example
ALTER TABLE Students
RENAME TO Student_Details;

Advantages of ALTER Command

Advantage Explanation

Flexible Modify table structure anytime

Saves Time No need to recreate tables

Easy Maintenance Update database according to requirements

Better Data Management Add/remove columns easily

Important Notes
 ALTER changes only the table structure, not the whole database.
 Some SQL databases may use slightly different syntax.
 Be careful while using DROP COLUMN because deleted data cannot be recovered easily.

Real-Life Example
Suppose a company initially stores:

 Employee ID
 Name

Later they also need:

 Email
 Phone number

Instead of creating a new table, they use:

ALTER TABLE Employees

ADD Email VARCHAR(100);

This updates the existing table easily.

SELECT Statement in SQL

The SELECT statement is used to retrieve data from a table.

🔹 Syntax
SELECT column_name
FROM table_name;

🔹 Example
SELECT * FROM Students;

* means all columns.

Column Selection
Used to display specific columns only.

🔹 Example
SELECT Name, Age
FROM Students;

This displays only Name and Age.

Aggregate Functions
Aggregate functions perform calculations on multiple rows.

1. SUM()
Returns total sum.

SELECT SUM(Salary)
FROM Employees;

2. MIN()
Returns smallest value.

SELECT MIN(Salary)
FROM Employees;

3. MAX()
Returns largest value.

SELECT MAX(Salary)
FROM Employees;
4. AVG()
Returns average value.

SELECT AVG(Salary)
FROM Employees;

5. COUNT()
Counts rows.

SELECT COUNT(*)
FROM Employees;

DISTINCT
Removes duplicate values.

SELECT DISTINCT City

FROM Employees;

String Functions

LOWER()
Converts text to lowercase.

SELECT LOWER(Name)
FROM Students;

UPPER()
Converts text to uppercase.

SELECT UPPER(Name)
FROM Students;
PROPER()
Converts first letter of each word to uppercase.

SELECT PROPER(Name)
FROM Students;

LEFT()
Returns characters from left side.

SELECT LEFT(Name,3)
FROM Students;

RIGHT()
Returns characters from right side.

SELECT RIGHT(Name,2)
FROM Students;

MID()
Extracts characters from middle.

SELECT MID(Name,2,3)
FROM Students;

REPLACE()
Replaces text.

SELECT REPLACE(Name,'a','@')
FROM Students;

SUBSTRING_INDEX()
Returns substring before/after delimiter.

SELECT SUBSTRING_INDEX(Email,'@',1)
FROM Students;

CONCAT()
Joins strings.

SELECT CONCAT(First_Name,' ',Last_Name)

FROM Employees;

GROUP_CONCAT()
Combines values from multiple rows.

SELECT GROUP_CONCAT(Name)
FROM Students;

Date Functions

DAY()
Returns day.

SELECT DAY('2026-05-09');

MONTH()
Returns month.

SELECT MONTH('2026-05-09');

YEAR()
Returns year.

SELECT YEAR('2026-05-09');
Time Functions

TIME()
Returns time part.

SELECT TIME(NOW());

HOUR()
Returns hour.

SELECT HOUR(NOW());

SECOND()
Returns seconds.

SELECT SECOND(NOW());

DATEDIFF()
Returns difference between dates.

SELECT DATEDIFF('2026-05-20','2026-05-01');

STR_TO_DATE()
Converts string to date format.

SELECT STR_TO_DATE('09-05-2026','%d-%m-%Y');

Conditional Functions
IF()
Works like condition checking.

SELECT IF(Salary > 50000,'High','Low')

FROM Employees;

CASE
Used for multiple conditions.

SELECT Name,
CASE
WHEN Marks >= 90 THEN 'A'
WHEN Marks >= 75 THEN 'B'
ELSE 'C'
END AS Grade
FROM Students;

COALESCE()
Returns first non-null value.

SELECT COALESCE(NULL,NULL,'Hello','SQL');

Output:

Hello

Important Notes
Function Type Purpose

Aggregate Functions Perform calculations

String Functions Work with text

Date Functions Work with dates

Conditional Functions Apply conditions

Real-Life Example
In an employee database:

 SUM() → Total salary expense

 AVG() → Average salary
 UPPER() → Standardize names
 DATEDIFF() → Calculate leave days
 CASE → Generate grades or salary categories

These functions make SQL powerful for data analysis and reporting.

WHERE Clause in SQL

The WHERE clause is used to filter records based on conditions.

It displays only those rows that satisfy the condition.

🔹 Syntax
SELECT column_name
FROM table_name
WHERE condition;

🔹 Example
SELECT *
FROM Employees
WHERE Salary > 50000;

This displays employees whose salary is greater than 50000.

Common Operators in WHERE

Operator Meaning

= Equal to

> Greater than

< Less than

Operator Meaning

>= Greater than or equal

<= Less than or equal

!= or <> Not equal

AND Multiple conditions

OR Any one condition

NOT Opposite condition

GROUP BY Clause
The GROUP BY clause is used to group rows having the same values.

Mostly used with aggregate functions like:

 SUM()
 COUNT()
 AVG()

🔹 Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

🔹 Example
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department;

This counts employees department-wise.

ORDER BY Clause
The ORDER BY clause is used to sort records.

 Ascending → ASC
 Descending → DESC

🔹 Syntax
SELECT *
FROM table_name
ORDER BY column_name ASC|DESC;

🔹 Example
Ascending Order
SELECT *
FROM Employees
ORDER BY Salary ASC;
Descending Order
SELECT *
FROM Employees
ORDER BY Salary DESC;

LIMIT Clause
The LIMIT clause restricts the number of rows returned.

🔹 Syntax
SELECT *
FROM table_name
LIMIT number;

🔹 Example
SELECT *
FROM Students
LIMIT 5;

This shows only first 5 rows.

OFFSET Clause
The OFFSET clause skips a specified number of rows.

Usually used with LIMIT.

🔹 Syntax
SELECT *
FROM table_name
LIMIT number OFFSET number;

🔹 Example
SELECT *
FROM Students
LIMIT 5 OFFSET 2;

This skips first 2 rows and displays next 5 rows.

HAVING Clause
The HAVING clause filters grouped data.

Difference:

 WHERE filters rows before grouping

 HAVING filters groups after grouping

🔹 Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
🔹 Example
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

This displays departments having more than 5 employees.

Difference Between WHERE and HAVING

WHERE HAVING

Filters rows Filters groups

Used before GROUP BY Used after GROUP BY

Cannot use aggregate functions directly Can use aggregate functions

Combined Example
SELECT Department, AVG(Salary)
FROM Employees
WHERE Salary > 20000
GROUP BY Department
HAVING AVG(Salary) > 40000
ORDER BY AVG(Salary) DESC
LIMIT 3;

Explanation of Above Query

Clause Purpose

WHERE Filters employees with salary > 20000

GROUP BY Groups data by department

HAVING Keeps departments with avg salary > 40000

ORDER BY Sorts by average salary

LIMIT Shows top 3 rows

Real-Life Example
In an e-commerce company:

 WHERE → Find orders above ₹1000

 GROUP BY → Group orders by city
 HAVING → Cities with more than 50 orders
 ORDER BY → Sort highest sales first
 LIMIT → Show top 10 customers
 OFFSET → Pagination in websites

These clauses help in reporting and data analysis.

SQL Joins
Joins are used to combine data from two or more tables based on a related column.

Example:

 Students table
 Courses table

Using joins, we can combine student details with course details.

1. INNER JOIN
Returns only matching records from both tables.

🔹 Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];

🔹 Example
SELECT [Link], Courses.Course_Name
FROM Students
INNER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;
🔹 Result

Only students having matching course IDs are displayed.

2. LEFT JOIN (LEFT OUTER JOIN)

Returns:

 All records from left table

 Matching records from right table

If no match exists → NULL values appear.

🔹 Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON [Link] = [Link];

🔹 Example
SELECT [Link], Courses.Course_Name
FROM Students
LEFT JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

🔹 Result

All students are shown even if course is missing.

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns:

 All records from right table

  Matching records from left table

🔹 Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON [Link] = [Link];

🔹 Example
SELECT [Link], Courses.Course_Name
FROM Students
RIGHT JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

4. FULL OUTER JOIN

Returns:

 All records from both tables

 Matching where possible
 NULL where no match exists

🔹 Syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON [Link] = [Link];

🔹 Example
SELECT [Link], Courses.Course_Name
FROM Students
FULL OUTER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

5. CROSS JOIN
Returns Cartesian product.
Every row from first table combines with every row from second table.

🔹 Syntax
SELECT *
FROM table1
CROSS JOIN table2;

🔹 Example
SELECT [Link], Courses.Course_Name
FROM Students
CROSS JOIN Courses;

6. SELF JOIN
A table joins with itself.

Used for hierarchical relationships.

🔹 Syntax
SELECT [Link], [Link]
FROM table_name A, table_name B
WHERE A.common_column = B.common_column;

🔹 Example
SELECT [Link] AS Employee,
[Link] AS Manager
FROM Employees E1
JOIN Employees E2
ON E1.Manager_ID = E2.Employee_ID;

7. ANTI JOIN
Returns non-matching rows.

SQL does not directly support ANTI JOIN, but it can be achieved using:

 NOT EXISTS
  LEFT JOIN ... IS NULL

LEFT ANTI JOIN

Returns rows from left table with no match in right table.

🔹 Example
SELECT [Link]
FROM Students
LEFT JOIN Courses
ON Students.Course_ID = Courses.Course_ID
WHERE Courses.Course_ID IS NULL;

RIGHT ANTI JOIN

Returns rows from right table with no match in left table.

🔹 Example
SELECT Courses.Course_Name
FROM Students
RIGHT JOIN Courses
ON Students.Course_ID = Courses.Course_ID
WHERE Students.Course_ID IS NULL;

UNION
Combines results of two queries and removes duplicates.

🔹 Syntax
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
🔹 Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

UNION ALL
Combines results including duplicates.

🔹 Syntax
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

Difference Between UNION and UNION ALL

UNION UNION ALL

Removes duplicates Keeps duplicates

Slower Faster

Unique results All results

Difference Between Joins

Join Type Result

INNER JOIN Matching rows only

LEFT JOIN All left + matching right

RIGHT JOIN All right + matching left

FULL OUTER JOIN All rows from both

CROSS JOIN All combinations

 Join Type Result

SELF JOIN Table joins itself

ANTI JOIN Non-matching rows

Real-Life Example
In an online shopping system:

 INNER JOIN → Customers with orders

 LEFT JOIN → All customers including no orders
 RIGHT JOIN → All products including unsold products
 CROSS JOIN → Product combinations
 SELF JOIN → Employee-manager relationship
 UNION → Combine customer lists from multiple branches

Joins are very important for relational databases and data analysis.

Window Functions in SQL

Window functions perform calculations across a set of rows related to the current row without
grouping the result into a single row.

Unlike aggregate functions:

 Aggregate functions return one result for a group

 Window functions return results for each row

🔹 Syntax
function_name() OVER (
PARTITION BY column_name
ORDER BY column_name
)

🔹 Important Keywords
Keyword Purpose
 Keyword Purpose

OVER() Defines window

PARTITION BY Divides rows into groups

ORDER BY Defines order inside window

Sample Table: Employees

Emp_ID Name Department Salary

1 Rahul IT 50000

2 Aman IT 60000

3 Neha HR 45000

4 Simran HR 45000

Aggregate Window Functions

These functions calculate values while still showing all rows.

1. SUM()
SELECT Name,
Salary,
SUM(Salary) OVER() AS Total_Salary
FROM Employees;

2. AVG()
SELECT Name,
Salary,
AVG(Salary) OVER() AS Avg_Salary
FROM Employees;

3. COUNT()
SELECT Name,
 COUNT(*) OVER() AS Total_Employees
FROM Employees;

Ranking Functions
Used to assign ranks to rows.

1. RANK()
Same rank for duplicate values, but skips next rank.

🔹 Example
SELECT Name,
Salary,
RANK() OVER(ORDER BY Salary DESC) AS Rank_No
FROM Employees;

🔹 Example Output
Name Salary Rank

Aman 60000 1

Rahul 50000 2

Neha 45000 3

Simran 45000 3

Next rank becomes 5.

2. DENSE_RANK()
Same rank for duplicates but no gaps.
🔹 Example
SELECT Name,
Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS Dense_Rank
FROM Employees;

🔹 Output
Name Salary Dense Rank

Aman 60000 1

Rahul 50000 2

Neha 45000 3

Simran 45000 3

Next rank remains 4.

3. ROW_NUMBER()
Assigns unique row numbers.

🔹 Example
SELECT Name,
Salary,
ROW_NUMBER() OVER(ORDER BY Salary DESC) AS Row_No
FROM Employees;

Difference Between Ranking Functions

Function Duplicate Rank Gaps

RANK() Same Yes

DENSE_RANK() Same No

ROW_NUMBER() Different No
LEAD()
Accesses next row value.

🔹 Example
SELECT Name,
Salary,
LEAD(Salary) OVER(ORDER BY Salary) AS Next_Salary
FROM Employees;

LAG()
Accesses previous row value.

🔹 Example
SELECT Name,
Salary,
LAG(Salary) OVER(ORDER BY Salary) AS Previous_Salary
FROM Employees;

FIRST_VALUE()
Returns first value in window.

🔹 Example
SELECT Name,
Salary,
FIRST_VALUE(Salary)
OVER(ORDER BY Salary DESC) AS Highest_Salary
FROM Employees;
LAST_VALUE()
Returns last value in window.

🔹 Example
SELECT Name,
Salary,
LAST_VALUE(Salary)
OVER(
ORDER BY Salary
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS Lowest_Salary
FROM Employees;

PARTITION BY Example
Groups rows before applying window function.

🔹 Example
SELECT Name,
Department,
Salary,
RANK() OVER(
PARTITION BY Department
ORDER BY Salary DESC
) AS Dept_Rank
FROM Employees;

Advantages of Window Functions

Advantage Explanation

Row-level calculations Keeps all rows visible

Easy ranking Rank employees/students

Time analysis Compare previous and next values

Better reporting Advanced analytics

 Advantage Explanation

Simplifies queries Reduces complex subqueries

Real-Life Example
In a company database:

 RANK() → Top-performing employees

 LEAD() → Compare next month sales
 LAG() → Compare previous month revenue
 ROW_NUMBER() → Pagination
 FIRST_VALUE() → Highest salary in department

Window functions are heavily used in:

 Data analytics
 Business intelligence
 Reporting dashboards
 Data science projects

VIEW in SQL
A VIEW is a virtual table created from one or more tables.

It does not store data physically.

It stores only the SQL query.

🔹 Syntax
CREATE VIEW view_name AS
SELECT column_name
FROM table_name
WHERE condition;

🔹 Example
CREATE VIEW High_Salary_Employees AS
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000;
🔹 Using View
SELECT * FROM High_Salary_Employees;

Advantages of VIEW
Advantage Explanation

Security Restricts sensitive data

Simplicity Simplifies complex queries

Reusability Same query can be reused

Data Abstraction Hides table complexity

FUNCTION in SQL
A Function is a database object that:

 Accepts input parameters

 Performs calculations
 Returns a single value

🔹 Syntax
CREATE FUNCTION function_name(parameters)
RETURNS datatype
BEGIN
statements;
RETURN value;
END;

🔹 Example
CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
BEGIN
RETURN a + b;
END;
🔹 Calling Function
SELECT AddNumbers(10,20);

Advantages of Functions
Advantage Explanation

Code Reusability Use same logic multiple times

Easy Maintenance Centralized logic

Faster Development Reduces repeated code

Better Modularity Organizes SQL programs

PROCEDURE in SQL
A Stored Procedure is a set of SQL statements stored in the database.

Unlike functions:

 Procedures may or may not return values

 Can contain multiple SQL statements

🔹 Syntax
CREATE PROCEDURE procedure_name()
BEGIN
SQL statements;
END;

🔹 Example
CREATE PROCEDURE ShowEmployees()
BEGIN
SELECT * FROM Employees;
END;

🔹 Calling Procedure
CALL ShowEmployees();
Procedure with Parameter
CREATE PROCEDURE EmployeeByDept(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM Employees
WHERE Department = dept_name;
END;

🔹 Calling
CALL EmployeeByDept('IT');

Advantages of Procedures
Advantage Explanation

Improves Performance Precompiled queries

Reduces Network Traffic Executes multiple queries together

Security Restricts direct table access

Reusability Use repeatedly

TRIGGERS in SQL
A Trigger is a special type of stored procedure that automatically executes when an event occurs
on a table.

Events:

 INSERT
 UPDATE
 DELETE

🔹 Syntax
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
statements;
END;

🔹 Example
CREATE TRIGGER Before_Insert_Employee
BEFORE INSERT
ON Employees
FOR EACH ROW
BEGIN
SET [Link] = UPPER([Link]);
END;

🔹 Explanation

Before inserting data:

 Employee name automatically converts to uppercase.

Types of Triggers
Trigger Type Description

BEFORE INSERT Runs before insertion

AFTER INSERT Runs after insertion

BEFORE UPDATE Runs before update

AFTER UPDATE Runs after update

BEFORE DELETE Runs before deletion

AFTER DELETE Runs after deletion

Advantages of Triggers
Advantage Explanation

Automatic Execution No manual work needed

 Advantage Explanation

Data Integrity Enforces business rules

Audit Tracking Tracks changes automatically

Security Prevents invalid operations

Difference Between Function and Procedure

Function Procedure

Returns value May or may not return value

Used in SELECT Called using CALL

Mainly for calculations Performs operations

Real-Life Example
VIEW

Show only active customers to employees.

FUNCTION

Calculate tax amount automatically.

PROCEDURE

Generate monthly salary report.

TRIGGER

Store deleted employee records in backup table automatically.

These database objects help build secure, efficient, and automated database systems.
Normalization in Database
Normalization is the process of organizing data in a database to:

 Reduce data redundancy

 Avoid duplicate data
 Improve data consistency
 Maintain data integrity

It divides large tables into smaller related tables.

Why Normalization is Important?

Without normalization:

 Duplicate data increases

 Storage space is wasted
 Updating data becomes difficult
 Data inconsistency occurs

Example Without Normalization

Student_ID Name Course Teacher

1 Rahul Python Amit

2 Aman Python Amit

Teacher name repeats multiple times.

After Normalization
Students Table
Student_ID Name

1 Rahul
Student_ID Name

2 Aman

Courses Table
Course_ID Course Teacher

101 Python Amit

This removes redundancy.

Types of Normal Forms

1. First Normal Form (1NF)

Rules:

 Each column should contain atomic (single) values

 No multiple values in one column

❌ Not in 1NF
ID Name Phone

1 Rahul 9876, 8765

✅ In 1NF
ID Name Phone

1 Rahul 9876

1 Rahul 8765
2. Second Normal Form (2NF)
Rules:

 Must be in 1NF
 No partial dependency

All non-key columns must depend on the entire primary key.

3. Third Normal Form (3NF)

Rules:

 Must be in 2NF
 No transitive dependency

Non-key columns should depend only on the primary key.

❌ Example
Emp_ID Emp_Name Dept_Name Dept_Location

1 Rahul IT Delhi

Dept_Location depends on Dept_Name, not directly on Emp_ID.

4. BCNF (Boyce-Codd Normal Form)

Advanced version of 3NF.

Every determinant must be a candidate key.

Advantages of Normalization
Advantage Explanation
 Advantage Explanation

Reduces Redundancy Duplicate data minimized

Improves Consistency Same data stored once

Saves Storage Less repeated information

Easier Maintenance Easy updates and deletions

Better Integrity More accurate data

Disadvantages of Normalization
Disadvantage Explanation

More Tables Database becomes complex

More Joins Queries may become slower

Difficult Design Requires proper planning

ACID Properties in Database

ACID properties ensure reliable and safe database transactions.

ACID stands for:

 Atomicity
 Consistency
 Isolation
 Durability

These properties are mainly used in transaction management.

What is a Transaction?
A transaction is a group of SQL operations executed together.
Example:
Money transfer from one bank account to another.

1. Atomicity
“Either all operations happen or none happen.”

If one step fails, the entire transaction is rolled back.

Example
Transfer ₹1000:

 Deduct from Account A

 Add to Account B

If second step fails:

 Deduction is also cancelled

2. Consistency
A transaction must keep the database in a valid state.

Rules and constraints should always remain correct.

Example
Bank balance should never become negative.

3. Isolation
Multiple transactions should not interfere with each other.
Each transaction works independently.

Example
Two users booking same seat simultaneously should not create conflicts.

4. Durability
Once transaction is committed, data is permanently saved.

Even system crash should not remove committed data.

Example
After successful payment:

 Transaction remains saved even after power failure.

Summary Table of ACID Properties

Property Meaning

Atomicity All or nothing

Consistency Valid data state

Isolation Transactions independent

Durability Permanent storage

Real-Life Example of ACID

Online Banking

When transferring money:

 1. Amount deducted from sender
2. Amount added to receiver
3. Both operations succeed together
4. Data remains safe after completion

This ensures secure and reliable transactions.

Important Point
 Normalization improves database design.
 ACID properties improve transaction reliability.

Both are essential concepts in relational databases like:

 MySQL
 PostgreSQL
 Oracle Database