ALRIGHT!

SQL TUTORIAL

ZERO TO HERO
Section - 1
What is database?

Database vs DBMS

DBMS vs RDBMS

Other available databases

SQL vs MS SQL

Installation
What is a Database?
What is a Database?

A structured collection of data stored in a

computer system that can be easily
accessed and managed.
Store details of
students

id
name
age
grade
Student_ID Name Age Grade

101 Raju 10 5

102 Sham 12 7

103 Baburao 14 9
DATABASE
vs
DBMS
 DBMS
A DBMS (Database Management System) is software
that is used to store, manage, and retrieve data
efficiently and securely in a structured way.
 Stud Name Age Grad
App
101 Raju 10 5
102 Sham 12 7
103 Babu 14 9

Database
User
 Stud Name Age Grad
App
101 Raju 10 5
102 Sham 12 7
103 Babu 14 9
MSSQL

DBMS Database
User
 MSSQL

DBMS Database

PostGresql, MySQL, MSSQL etc

What is RDBMS?
 What is RDBMS?
A type of database system that stores data in
structured tables (using rows and columns)
and uses SQL for managing and querying data.
Student_ID Name Age Grade

101 Raju 10 5

102 Sham 12 7

103 Baburao 14 9
 What if we want to store the subjects each
student is enrolled in and the marks they got?
Student_ID Name Age Grade Subject Marks

101 Raju 10 5 Maths 95

101 Raju 10 5 Science 88

102 Sham 12 7 Maths 76

103 Baburao 14 9 History 91

103 Baburao 14 9 Maths 85

 marks students
Subject Marks Student_ID
Student_ID Name Age Grade
Maths 95 101

Science 88 101
101 Raju 10 5

Maths 76 102
102 Sham 12 7
History 91 103
103 Baburao 14 9
Maths 85 103
Some Other Databases are:

Oracle
MySQL
PostgreSQL
Firebird
MongoDB
Redis
SQL vs MSSQL
SQL

Structured Query Language

Which is used to talk to our databases.

Example: SELECT * FROM person_db;

App Stu Na Age Gra
101 Raju 10 5
102 Sha 12 7
MSSQL 103 Bab 14 9

DBMS Database
User

SELECT * FROM person_db

Installation
Download from the link
[Link]

Install SQL Server Management Studio (SSMS)

Section - 2
Database
Creating, connect, listing, droping

CRUD
Create - New Table
Inserting data
Read - How to read data
Update data
Delete data
Databases
List down existing databases

SELECT name FROM [Link];

EXEC sp_databases;
Creating a new Database

CREATE DATABASE <db_name>;

Change or Use a Database

use <db_name>;

SELECT DB_NAME();
Deleting a Database

DROP DATABASE <db_name>;

CRUD
CREATE
READ
UPDATE
DELETE
CREATING Tables
Table

A table is a collection of related data

held in a table format within a database.

Student_ID Name Age Grade

101 Raju 10 5

102 Sham 12 7

103 Baburao 14 9
C re at i n g a n ew Ta b l e
Student_ID Name Age Grade
101 Raju 10 5
102 Sham 12 7
103 Baburao 14 9

Store details of
students
Creating a new Table

CREATE TABLE students (

student_id INT,
name VARCHAR(100),
age INT,
grade INT
);

CREATE TABLE students (id INT, name VARCHAR(100), city

VARCHAR(50));
Checking your table

EXEC sp_help 'users';

INSERTING Data
Adding data into a Table

INSERT INTO students(student_id, name, age, grade)

VALUES (101, ‘Raju’, 10, 5);

INSERT INTO students VALUES (102, ‘Sham’, 12, 7)

READING DATA
Reading data from a Table

SELECT * FROM <table_name>

SELECT <column_name> from students

UPDATING DATA
Modify/Update data from a Table

UPDATE users
SET city=’London’
WHERE id=102;
DELETING DATA
DELETE data from a Table

DELETE FROM users

WHERE name='Raju';
TRUNCATE
T R U N CAT E TA B L E e m p l oye e s ;
Exercise:
Write a query to change Grade of Raju from 5 to 6.

Add a new student to the table:

Student_ID = 104, Name = 'Alex', Age = 11, Grade = 6.

Write a query to remove 'Baburao' from the table.

Write a query to retrieve only the details for the student

named 'Sham'.
Write a query to print/get age of Raju
WHERE
UPDATE students SET grade=12 WHERE student_id=102;

DELETE FROM students WHERE name='Raju';

SELECT * FROM students WHERE name=’Raju’

SELECT age FROM students WHERE name=’Sham’

Section - 3
Datatypes

Constraint
DataTypes

An attribute that defines the kind of

data a column in a database table can
hold, such as numbers, text, dates, or
boolean values.
CREATE TABLE students (
student_id INT,
name VARCHAR(100),
age INT,
grade INT
);
Can we store this number in
a column with INT
datatype?

4,735,892,104,326
What will happen when
we store values like
15.35?
Most widely used are

Numeric - INT | BIGINT | FLOAT | DECIMAL/NUMERIC

String - VARCHAR | CHAR
Date - DATE
Date Time - DateTime
Boolean - BIT (0/1)
 DATAYPES
Digits after decimal

DECIMAL(5,2)
Total digit
DECIMAL(5,2) DATAYPES
Example: 155.38

119.12
28.15
1150.1
Constraint
A constraint decides what kind of data is
allowed in a column.
P R I M A RY K E Y
NOT NULL
D E FAU LT
IDENTITY
UNIQUE
Let’s First Understand the Problems with
Current Table Structure
Primary Key
The PRIMARY KEY constraint uniquely identifies each
record in a table.
Primary keys must contain UNIQUE values, and cannot
contain NULL values.
A table can have only ONE primary key.
If you need to use two or more columns to
uniquely identify a record
UNIQUE
🔹 UNIQUE constraint makes sure that no two rows in a table have the
same value in a column.

👉 It helps to prevent duplicate data, like the same email or phone

number being used twice.

❗ However, NULL is allowed — but only once (because NULL is treated

as “unknown”, and SQL allows one unknown value in a unique column).
NOT NULL
DEFAULT Value
id name email created_at

1 raju raju@[Link] 2025-06-22 18:06:59

2 sham sham@[Link] 2025-06-22 18:07:08

3 baburao baburao@[Link] 2025-06-22 18:07:19

IDENTITY
It is used to automatically generate unique numbers for a
column when new rows are inserted into a table.

It works like auto-increment, usually for primary key

columns.
 TASK
Creating New Table
employees
Requirement:

emp_id set as primary key and its value should be auto-

increment by 1 starting from 101.
Email should be Unique
Null value should not be allowed in
fname, lname, email, job_title
Salary column - default set to 30,000 if not provided
Hire_date - default set to today’s date
CREATE TABLE employees (
emp_id INT IDENTITY(101,1) PRIMARY KEY,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
job_title VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2) DEFAULT 30000.00,
hire_date DATE NOT NULL DEFAULT CONVERT(date, GETDATE()),
city VARCHAR(50)
);
INSERT INTO employees
(fname, lname, email, job_title, department, salary, hire_date, city)
VALUES
('Aarav', 'Sharma', '[Link]@[Link]', 'Director', 'Management', 180000, '2019-02-10', 'Mumbai'),
('Diya', 'Patel', '[Link]@[Link]', 'Lead Engineer', 'Tech', 120000, '2020-08-15', 'Bengaluru'),
('Rohan', 'Mehra', '[Link]@[Link]', 'Software Engineer', 'Tech', 85000, '2022-05-20', 'Bengaluru'),
('Priya', 'Singh', '[Link]@[Link]', 'HR Manager', 'Human Resources', 95000, '2019-11-05', 'Mumbai'),
('Arjun', 'Kumar', '[Link]@[Link]', 'Data Scientist', 'Tech', 110000, '2021-07-12', 'Hyderabad'),
('Ananya', 'Gupta', '[Link]@[Link]', 'Marketing Lead', 'Marketing', 90000, '2020-03-01', 'Delhi'),
('Vikram', 'Reddy', '[Link]@[Link]', 'Sales Executive', 'Sales', 75000, '2023-01-30', 'Mumbai'),
('Sameera', 'Rao', '[Link]@[Link]', 'Software Engineer', 'Tech', 88000, '2023-06-25', 'Pune'),
('Ishaan', 'Verma', '[Link]@[Link]', 'Recruiter', 'Human Resources', 65000, '2022-09-01', 'Mumbai'),
('Kavya', 'Joshi', '[Link]@[Link]', 'Product Designer', 'Design', 92000, '2021-04-18', 'Bengaluru'),
('Zain', 'Khan', '[Link]@[Link]', 'Sales Manager', 'Sales', 115000, '2019-09-14', 'Delhi'),
('Nisha', 'Desai', '[Link]@[Link]', 'Jr. Data Analyst', 'Tech', 70000, '2024-02-01', 'Hyderabad'),
('Aditya', 'Nair', '[Link]@[Link]', 'Marketing Analyst', 'Marketing', 68000, '2022-10-10', 'Delhi'),
('Fatima', 'Ali', '[Link]@[Link]', 'Sales Executive', 'Sales', 78000, '2022-11-22', 'Mumbai'),
('Kabir', 'Shah', '[Link]@[Link]', 'DevOps Engineer', 'Tech', 105000, '2020-12-01', 'Pune');
 Section - 4
Getting the Data You Really Need
 Section - 4
4.1 - Where | Distinct | Order By | Like | TOP

4.2 - Logical Operators

4.3 - IN | NOT IN | BETWEEN

Section - 4.1
Clauses
A SQL clause is a part of a SQL statement that defines
specific actions or conditions for querying, filtering, or
organizing data in a database.

Where | Distinct | Order By | Like | TOP

 Clause What It Does How to Use (Example)

WHERE Filters rows based on a condition SELECT * FROM employees WHERE dept = 'IT';

DISTINCT Removes duplicate rows from the result set SELECT DISTINCT dept FROM employees;

ORDER BY Sorts results by one or more columns SELECT * FROM employees ORDER BY salary DESC;

LIKE Finds patterns in text (wildcards: %, _) SELECT * FROM employees WHERE fname LIKE 'A%';

TOP Limits number of rows returned (MSSQL only) SELECT TOP 5 * FROM employees;
Relational Operators
We have relational
operators

=
 DISTINCT
SELECT DISTINCT fname FROM employees;
 ORDER BY
SELECT * FROM employees ORDER BY fname;
 LIKE
Select * FROM employees
WHERE dept LIKE "%Acc%";
Starts with 'A': LIKE 'A%'
Starts with 'A' or B: LIKE '[AB]%'
Not starts with A: LIKE '[^A]%'
Ends with 'A': LIKE '%A'
Contains 'A': LIKE '%A%'
Second character is 'A': LIKE '_A%'
 Exercise
DISTINCT, ORDER BY, LIKE and TOP
1: Find Different type of departments in database?

2: Display records with High-low salary

3: How to see only top 3 records from a table?

4: Show records where first name start with letter 'A'

5: Show records where length of the lname is 4 characters

Section - 4.2
Logical Operators
AND

OR
Condition 1 AND Condition 2

When both the conditions are true

salary = 75000 AND dept = Sales
Condition 1 OR Condition 2

When either of the condition is true

city = Mumbai OR dept = 'Tech'
 Section - 4.3

IN | NOT IN | BETWEEN
Find employees From following department

Tech
Sales
Marketing
 SELECT * FROM employees
WHERE department = 'Marketing'
OR department = 'Sales'
OR department = 'Tech';
SELECT * FROM employees
WHERE department IN ('Marketing', 'Sales', 'Tech');
BETWEEN
Find employees whose salary is more than
60000 and Less than 65000

>40000
<65000
SELECT * FROM employees
WHERE
salary >=40000 AND salary <=65000;
SELECT * FROM employees
WHERE
salary BETWEEN 55000 AND 65000;
Additional Topcis
CASE
Calculate a bonus amount. Sales and Marketing get a 10% bonus, Tech
gets a 12% bonus, and everyone else gets a standard 5% bonus.
Task
IS NULL
 IS NULL

SELECT * FROM employees

WHERE fname IS NULL;
NOT LIKE
 IS NULL
SELECT * FROM employees
WHERE fname NOT LIKE 'A%';
SECTION - 5
5.1 Aggregate Functions
5.2 Group By
 Section - 5.1

Aggregate functions
How to find total no. of employees?

Employee with Max or Min salary

Average salary of employees

Sum/total salary paid

COUNT
SUM
AVG
MIN
MAX
 COUNT
SELECT COUNT(*) FROM employees;
 MAX & MIN
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
 SUM & AVG
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT emp_id, fname, salary FROM employees
WHERE
salary = (SELECT MAX(salary) FROM employees);
Section - 5.2
GROUP BY
No. of employees in each department
HR IT Finance Deposit Marketing

BANK
HR Finance Marketing

Deposit
IT
SELECT dept FROM employees GROUP BY dept;

SELECT dept, COUNT(fname) FROM employees GROUP

BY dept;
Find number of employees in each department
Find number of employees in each city
Find average salary in each department
Multi-Level Grouping
HR Finance Marketing

Deposit
Tech
 Mumbai 2

Pune 3

Tech Hyderabad 3
HAVING Clause
Find Departments with More Than 2 Employees

Find Job Titles with an Average Salary Above 90000

Find department with Total Salary Above

300000
GROUP BY ROLLUP
 GROUP BY ROLLUP is an extension of the
GROUP BY clause that generates subtotals and
a grand total for a set of columns.
Usecase

Employee Headcount by City and Department.

You want a report showing the number of

employees for each city within each department, a
subtotal for each department, and a grand total for
the entire company.
 Exercise
COUNT, GROUP BY, MIN, MAX and SUM and AVG
1: Find Total no. of employees in database?

2: Find no. of employees in each department.

3: Find lowest salary paying

4: Find highest salary paying

5: Find total salary paying in Loan department?

6: Average salary paying in each department

 Section 5.3
SUB-QUERIES
Usecases:

Find Employees Earning More Than the Company

Average

Find Employees Who Work in the Same City as a

Specific Person (ex: aarav sharma)

Find the Highest-Paid Employee name.

Find the Highest-Paid Employee in Each Department
 A SubQuery (also called an inner query
or nested query) is a query inside
another query.

🔸 The subquery runs first and gives a result.

🔸 The main query (outer query) then uses that result.
Types of SubQueries
Find employees who work in departments with at least one
person in Mumbai

👉 Multi-row subquery.
Find employees with the highest salary in each department

👉 Correlated subquery.
If we want to find departments whose average salary is above 90,000.

👉 Inline View subquery.

 WINDOW
FUNCTIONS
Window functions, also known as analytic
functions allow you to perform calculations
across a set of rows related to the current row.

Defined by an OVER() clause.

Compare each employee’s salary to total salary
Compare each
employee’s
salary to total
salary in each
department
Add row number for each row in table.
ROW_NUMBER()

RANK()

DENSE_RANK()

LAG()

LEAD()
Rank all employees based on salary from
High to Low
LAG LEAD
Lag

Current Row

Lead
Compare an Employee's Salary to the Previous Hire
Usecases:

Rank Employees Within Each Department by Salary.

Calculate a Running Total of Salary Budget in each department.

Compare an Employee's Salary to the Previous Hire

Rank Employees Within Each Department by Salary.
Calculate a Running Total of Salary Budget in each department.
Benefits of Window Functions

Advanced Analytics: They enable complex calculations like running

totals, moving averages, rank calculations, and cumulative
distributions.

Non-Aggregating: Unlike aggregate functions, window functions do

not collapse rows. This means you can calculate aggregates while
retaining individual row details.

Flexibility: They can be used in various clauses of SQL, such as

SELECT, ORDER BY, and HAVING, providing a lot of flexibility in
writing queries.
 ROWS BETWEEN

ROWS BETWEEN is a clause in a window function which

tells that -

For the row I'm currently on, calculate the result using
only this specific group of surrounding rows.
Running Total of Salary

Output Expected
Find Running Total of Salary
 Current Row

480000 = Current ROW + Sum of all preceding row

= 95k + (85K + 120K + 180K)
 Calculate 3-Rows Moving Average
Calculate the average salary of the current employee,
the one hired just before, and the one hired just after.
FIRST_VALUE
LAST_VALUE
NTILE
Divide all employees into four groups (quartiles) based on
their salary, from highest to lowest.
Find the top, middle, and bottom earners within
each department.
CTE
Common Table Expression
CTE (Common Table Expression) is a temporary
result set that you can define within a query to
simplify complex SQL statements.
Use Cases - 1

Finding Employees Who Earn More Than

Their Department's Average Salary
WITH avgsal AS (
SELECT
department,
AVG(salary) AS dept_avg
FROM employees
GROUP BY department
)
SELECT
emp_id, fname, [Link], salary, a.dept_avg
FROM employees e JOIN avgsal a
ON [Link] = [Link]
WHERE salary > a.dept_avg
Use Cases - 2

We want to find the highest-paid

employee in each department.
WITH maxsal AS (
SELECT
department,
MAX(salary) AS dept_max
FROM employees
GROUP BY department
)
SELECT
emp_id, fname, [Link], salary, m.dept_max
FROM employees e JOIN maxsal m
ON [Link] = [Link]
WHERE salary = m.dept_max
Points:

Once CTE has been created it can only be

used once. It will not be persisted.
Section - 6
String Functions
CONCAT, CONCAT_WS
SUBSTRING
LEFT, RIGHT
LEN
UPPER, LOWER
TRIM, LTRIM, RTRIM
REPLACE
CHARINDEX
 CONCAT
CONCAT(first_col, sec_col)
CONCAT(first_word, sec_word, ...)
CONCAT_WS
CONCAT_WS('-', fname, lname)
 SUBSTRING
SELECT SUBSTRING('Hey Buddy', 1, 4);
 SUBSTRING
SELECT SUBSTRING('Hey Buddy', 1, 4);
1 4
Result: Hey
REPLACE
Hey Buddy

Hello Buddy
REPLACE(str, from_str, to_str)

REPLACE('Hey Buddy', 'Hey', 'Hello')

 REVERSE
SELECT REVERSE('Hello World');
 LENGTH
Select LEN('Hello World');
UPPER & LOWER
SELECT UPPER('Hello World');
SELECT LOWER('Hello World');
Other Functions
SELECT LEFT('Abcdefghij', 3);
SELECT RIGHT('Abcdefghij', 4);

SELECT TRIM(' Alright! ');

SELECT CHARINDEX('OM','ThOMAS');
Exercise
Task 1:
101:Aarav:Sharma:Management

Task2:
102:Diya Pa[Link]
Task3
104:Priya:HUMAN RESOURCES

Task4
H104 Priya
M101 Aarav
DATE Functions
GETDATE()

DATEADD(interval, number, date)

DATEDIFF(interval, start_date, end_date)

DATEPART(interval, date) / YEAR(date), MONTH(date), DAY(date)

FORMAT(date, format_string)

formats like 'MM/dd/yyyy' or 'DD-MMM-yyyy'

Find out each employee's 5-year anniversary date.
Find employees hired in March.
Show the year, month, and day each employee was
hired, separately.
Display the hire date in a standard US format
(MM/dd/yyyy)
Section - 7
ALTERING
Tables
How to add or remove a column?
ALTER TABLE employees
ADD phone VARCHAR(15);

ALTER TABLE employees

DROP COLUMN phone;
How to modify a column?

Ex: Changing datatype

 How to change datatype of a column?
Ex: VARCHAR limit

ALTER TABLE employees

ALTER COLUMN lname VARCHAR(100) NOT NULL;
 How to set NOT NULL to a column?

ALTER TABLE employees

ALTER COLUMN email VARCHAR(100) NOT NULL;
How to rename a column or table name?
 How to rename a column?

EXEC sp_rename
'[Link]', 'first_name', 'COLUMN';
 How to rename a table?

EXEC sp_rename
'employees', 'staff';
ADD/DROP Constraint
 Constraint
A constraint in SQL is a rule applied to a column or table to
control the type of data that can be stored in it, ensuring
accuracy, validity, and integrity of the data.
How to set Default Value to a column?

ALTER TABLE employees

ADD CONSTRAINT default_dept DEFAULT 'Trainee'
FOR department;
 CHECK
CONSTRAINT
We want to make sure
salary of an employee is
positive..
CREATE TABLE emp(
name varchar(50),
salary DECIMAL(10,2) CHECK (salary>0)
)
 NAMED CONSTRAINT

CREATE TABLE contacts (

name VARCHAR(50),
salary DECIMAL(10,2),
CONSTRAINT chk_emp_positive_salary CHECK (salary>0)
);
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);
SECTION - 8
RElATIONSHIP
 A database relationship is a connection
between two or more tables, established using a
primary key and a foreign key.
Employees
Let’s understand problems with our
current database
1. Data Redundancy (Duplication)

2. What if I want to add a Finance department & its related details?

3. What if Kayva leaves the company which was only one in Design
department?

4. What if there is a typo in department column?

department employees
Foreign Key
 A foreign key is a column in one
table that links to the primary key
of another table.
 department employees

Primary Key Foreign Key

 Salary Attendance

Employees

requests offices task

Types of Relationship
One to One
One to Many
Many to Many
1:1
Employees

Employee Bank Details

1 : MANY
 department

employees
Many : Many
Books Authors
 Author A

Book A

Author B
 Book B

Book C

Book D
Author A

Book A

Author B
 Projects

employee
 Projects

employees

employee
PRACTICAL
1 : Many
Suppose we need to store the following data

customer name
customer email
order date
order price
Customers Orders

cust_id order_id
cust_name order_date
cust_email order_amount
Customers Orders

cust_id order_id
cust_name order_date
cust_email order_amount
cust_id
Customers

Orders
Let's work practically
with Foreign Key..
1-Many
 CREATE TABLE Customers (
customer_id INT IDENTITY(100,1) PRIMARY KEY,
Customers
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);

CREATE TABLE Orders (

order_id INT IDENTITY(500,1) PRIMARY KEY,
order_date DATE NOT NULL,
Orders total_amount DECIMAL(10, 2),
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
INSERT INTO Customers (customer_name, email)
VALUES
('Raju', 'raju@[Link]'),
('Sham', 'sham@[Link]'),
('Baburao', 'baburao@[Link]');

INSERT INTO Orders (order_date, total_amount, customer_id)

VALUES
('2025-09-15', 1500.00, 100), -- This links to Raju (customer_id 100)
('2025-09-28', 800.00, 101), -- This links to Sham (customer_id 101)
('2025-10-05', 2200.00, 100), -- This links to Raju (customer_id 100)
('2025-10-12', 500.00, 102), -- This links to Baburao (customer_id 102)
('2025-10-17', 1200.00, 101); -- New order for Sham (customer_id 101)
JOINS
 JOIN operation is used to combine rows
from two or more tables based on a related
column between them.
Types of Join
Cross Join
Inner Join
Left Join
Right Join
Full Join
 Cross Join
Every row from one table is combined with
every row from another table.
 Inner Join
Returns only the rows where there is a match
between the specified columns in both the
left (or first) and right (or second) tables.
Inner Join with Group By
 Left Join
Returns all rows from the left (or first) table and the
matching rows from the right (or second) table.
 Right Join
Returns all rows from the right (or second) table
and the matching rows from the left (or first) table.
 Full Outer Join
Returns all rows when there is a match in either the
left or right table.
 OUTER APPLY
OUTER APPLY is used to join each row from one table (the left table)
to the results of a table-valued function or subquery (the right side).
 Usecase of OUTER APPLY

For each customer, show their most recent order

(if they have one).
If they have no orders, still show the customer.
SELECT
c.customer_id,
c.customer_name,
o.order_id,
o.order_date,
o.total_amount
FROM Customers AS c

OUTER APPLY (
SELECT TOP 1 *
FROM Orders AS o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_date DESC
) AS o;
✅ What happens here:
For each customer (c), SQL Server runs the subquery on the right.

The subquery selects the latest order (because of ORDER BY ...

DESC and TOP 1).

If the customer has no orders, it returns NULL values for order

columns — similar to a LEFT JOIN.
 CROSS APPLY
CROSS APPLY is used to join each row from one table (the left table)
to the results of a table-valued function or subquery (the right side).

It behaves like an INNER JOIN, meaning it only returns rows

where the right-side subquery produces a result.
 UNION
UNION is used to combine the results of
two or more SELECT statements into a
single result set.

combines data vertically (adds rows, same structure).

Requirements:

[Link] SELECT must have the same number of columns.

[Link] columns must have compatible data types.
[Link] names are taken from the first SELECT.

Union vs Union ALL

Union removes the duplicates from combined result

 Mumbai Delhi

UNION
Mum & Delhi
 EXCEPT
EXCEPT returns rows from the first query
that do not exist in the second query.
SELF JOIN
A self join is a standard SQL join where a table
is joined to itself.

It's used when rows in a table are related to

other rows in the same table.
 CEO
Emp_ID - 1

Emp_ID - 2 Emp_ID - 3
Manager_ID - 1 Manager_ID - 1
Let’s create a new table
CREATE TABLE CompanyHierarchy (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
ManagerID INT
);

INSERT INTO CompanyHierarchy (EmployeeID, Name, ManagerID)

VALUES
(1, 'Sonia Verma', NULL), -- The CEO
(2, 'Rohan Gupta', 1), -- Reports to Sonia
(3, 'Amit Sharma', 2), -- Reports to Rohan
(4, 'Priya Singh', 1), -- Reports to Sonia
(5, 'Kabir Shah', 2); -- Reports to Rohan
SELECT
[Link] AS EmployeeName,
[Link] AS ManagerName
FROM
CompanyHierarchy AS e
LEFT JOIN
CompanyHierarchy AS m
ON [Link] = [Link];
Many : Many
Let's Understand a Use-Case of
Many : Many
Students Courses
 Course A

Student A

Course B

Course C
 Student A

Course A

Student B

Student C
 courses
students
id
id
course_name
student_name
fees
A single student can enroll in many courses (like Math, History, and Art).

A single course (like Math) can have many students enrolled in it.
students
id
student_name
enrollment
student_id
courses course_id
id
course_name
fees
students
id
student_name
enrollment
student_id
courses course_id
id
course_name
fees
TASK
e-store
Create a one-to-many and many-to-many relationship in a shopping
store context using four tables:

customers
orders
products
order_items

Include a price column in the products table and display the

relationship between customers and their orders, along with the
details of the products in each order.
Customers Orders Products

cust_id ord_id p_id

cust_name ord_date p_name
cust_id price

ord_items

items_id
ord_id
p_id
quantity
End Result
VIEWS
A view in MS SQL Server is a virtual table that
shows data from a saved query.
It doesn't store data, just displays it from
other tables.
How to check existing views

How to check code of views

sp_helptext 'YourViewName';
 Delete a View

DROP VIEW YourViewName;

DROP VIEW IF EXISTS YourViewName;
STORED ROUTINE
 STORED Routine
An SQL statement or a set of SQL Statement
that can be stored on database server
which can be call no. of times.
Order of Pizza
Receipe:
Prepare BASE
Add topping
Bake
 pizza burger

Order of Pizza
 sp_employees_get

sp_employees_get
Types of STORED Routine
STORED Procedure
User defined Functions
 STORED
PROCEDURE
STORED PROCEDURE

Set of SQL statements &

Procedural Logic that can perform operations
such as
INSERT, UPDATE, DELETE, and QUERING data.
[Link] without parameters

[Link] with INPUT parameters

[Link] with INPUT & OUTPUT parameters

How to Check Existing SP
SELECT
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
ORDER BY
ROUTINE_NAME;

EXEC sp_helptext 'YourProcedureName';

CREATE PROCEDURE update_emp_salary
@p_employee_id INT,
@p_new_salary NUMERIC(10, 2)
AS
BEGIN
UPDATE employees
SET salary = @p_new_salary
WHERE emp_id = @p_employee_id;
END;
How to execute

EXEC update_emp_salary
@p_employee_id = 102,
@p_new_salary = 125000.00;

EXEC update_emp_salary 102, 125000.00;

 CREATE PROCEDURE update_emp_salary
@p_employee_id INT = 102,
@p_new_salary NUMERIC(10, 2) = 125000
AS
EXEC update_emp_salary BEGIN
102, 125000.00; UPDATE employees
SET salary = 102
WHERE emp_id = 125000;
END;
CREATE PROCEDURE add_employee
@p_fname VARCHAR(50),
@p_lname VARCHAR(50),
@p_email VARCHAR(100),
@p_job_title VARCHAR(50),
@p_department VARCHAR(50),
@p_salary NUMERIC(10, 2),
@p_city VARCHAR(50)
AS
BEGIN
INSERT INTO employees (fname, lname, email, job_title, department, salary, city)
VALUES (@p_fname, @p_lname, @p_email, @p_job_title, @p_department,
@p_salary, @city);
END;
How to Modify or Delete a Procedure

ALTER PROCEDURE sp_GetAllTechEmployees ...

DROP PROCEDURE sp_GetAllTechEmployees;

 Procedural logic
Procedural logic in a stored procedure means

The ability to write SQL code that follows a step-by-step

(imperative) flow, just like in a programming language.
 USECASE

Create a procedure to update an employee's salary, but

only if the new salary is a raise
(i.e., greater than the current salary).
We also want to return a message about what happened.
Steps:

Get emp_id and new_salary as input

Check if emp_id exist and valid
Compare new_salary with current salary to check if new salary is
more than current.
If yes, update the salary
If No, give error message
CREATE PROCEDURE sp_SafelyUpdateSalary
-- Input parameters
@p_employee_id INT,
@p_new_salary NUMERIC(10, 2),

-- Output parameter for our message

@p_message VARCHAR(200) OUTPUT
AS
BEGIN
SET NOCOUNT ON; -- Stops "1 row(s) affected" messages

-- 1. Declare a variable
DECLARE @current_salary NUMERIC(10, 2);

-- 2. Check if the employee exists

IF NOT EXISTS (SELECT 1 FROM employees WHERE emp_id = @p_employee_id)
BEGIN
SET @p_message = 'Error: Employee ID does not exist.';
RETURN; -- This exits the procedure immediately
END

-- 3. Get the current salary

SELECT @current_salary = salary
FROM employees
WHERE emp_id = @p_employee_id;

-- 4. This is the procedural logic!

IF @p_new_salary > @current_salary
BEGIN
-- 5. Logic passed: Update the salary
UPDATE employees
SET salary = @p_new_salary
WHERE emp_id = @p_employee_id;

SET @p_message = 'Success: Salary updated.';

END
ELSE
BEGIN
-- 6. Logic failed: Do not update
SET @p_message = 'Error: New salary must be greater than the current salary.';
END
END;
USER DEFINED FUNCTIONS
custom function created by the user
to perform specific operations and
return a value.
 CREATE FUNCTION function_name
(
@param1 INT,
@param2 VARCHAR(50)
)
RETURNS return_data_type (INT, VARCHAR, DATE etc..)
Scalar Function
AS
BEGIN
DECLARE @result INT;
-- Example logic
SET @result = @param1 * 2;
RETURN @result;
END;
 Usecase:

Create a function which returns double the salary

 CREATE FUNCTION function_name
(
@param1 INT,
@param2 VARCHAR(50)
)
Inline Table-Valued RETURNS TABLE
Function (ITVF) AS
RETURN (
SELECT column1, column2
FROM your_table
WHERE some_column = @parameter1
)
Find name of the employees in each
department having maximum salary.
CREATE FUNCTION dept_max_sal_emp1 (@dept_name VARCHAR(100))
RETURNS TABLE
AS
RETURN
(
SELECT e.emp_id, [Link], [Link]
FROM
employees e
WHERE
[Link] = @dept_name
AND [Link] = (
SELECT MAX([Link])
FROM employees emp
WHERE [Link] = @dept_name
)
);
 TRIGGERS
Triggers are special procedures in a database
that automatically execute predefined
actions in response to certain events on a
specified table or view.
Use Case

Audit Salary Changes (Track Old vs

New Values)

Employees Salary SalaryAudit

 Database
Trigger
CREATE TABLE SalaryAudit (
AuditID INT IDENTITY PRIMARY KEY,
EmpID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2),
ChangedDate DATETIME DEFAULT GETDATE()
);
deleted

inserted

Trigger Database
Use Case
Prevent accidental removal of
employee is a specific department.

Ex: Management
Random Data for Testing
CREATE TABLE Employees (
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(50),
Salary INT
);
INSERT INTO Employees (FirstName, LastName, Department, Salary)
SELECT TOP (500000)
LEFT(NEWID(), 8), -- random first name
LEFT(NEWID(), 8), -- random last name
CASE ABS(CHECKSUM(NEWID())) % 5
WHEN 0 THEN 'IT'
WHEN 1 THEN 'HR'
WHEN 2 THEN 'Finance'
WHEN 3 THEN 'Marketing'
ELSE 'Sales'
END,
ABS(CHECKSUM(NEWID())) % 100000 + 30000
FROM [Link] a
CROSS JOIN [Link] b;
INDEXES
Creating and using indexes in SQL Server is
a powerful way to improve database
performance.
How to add index?

CREATE INDEX i_name

ON employees(salary, emp_id);

How to see index?

EXEC sp_helpindex 'employees';

How to remove index?

DROP INDEX i_name ON employees;

Clustered Index
Non-Clustered Index
Performance Comparison

Without Index: Full table scan, time complexity O(n).

With Index: Indexed search, time complexity O(log n).
Normalization
Normalization is the process of organizing
data in a database efficiently

i.e. reduce redundancy and improve data integrity.

✅ First Normal Form (1NF)
Each column contains only atomic (indivisible) values.
→ No arrays, lists, or sets inside a single cell.

Each column stores values of a single data type.

→ Example: You shouldn’t mix text and numbers in the same column.

Each record (row) is unique.

→ Usually ensured by a primary key.

Example:
❌ courses = "Math, Physics"
✅ Second Normal Form (2NF)
Rule: Be in 1NF + every non-key column depends on the whole
primary key.
Applies to: Composite keys.

Example:
If table has (student_id, course_id) as key, then attributes like
student_name should not be there (it depends only on
student_id).
✅ Third Normal Form (3NF)
Rule: Be in 2NF + no transitive dependency
(non-key → non-key).
Does any non-key attribute depend on
another non-key attribute?

Example:
❌ employee(city, zip, state)
✅ Move zip → city, state to a separate
zip_codes table.
CASCADE ON DELETE
 Primary Key

Customers

Foreign Key
Primary Key

Orders
IMPORT / EXPORT
Database
Taking Backup
Restoring Database
Import data from CSV