SQL

Just Do It
MARZ TECHNOLOGIES Karaikudi + 91 90427 10472
Contents
SQL COMMANDS ..................................................................................................... 4
Data Definition Language (DDL) ............................................................................. 4
1. CREATE Command ..................................................................................... 4
2. ALTER Command ....................................................................................... 4
3. DROP Command ........................................................................................... 5
4. TRUNCATE Command.................................................................................... 6
5. RENAME Command ....................................................................................... 6
Data Manipulation Language (DML) in SQL ............................................................. 6
1. INSERT Command ...................................................................................... 6
2. UPDATE Command ........................................................................................ 7
3. DELETE Command ........................................................................................ 7
4. SELECT Command ........................................................................................ 7
Data Query Language (DQL) .................................................................................. 8
SELECT Command ............................................................................................ 8
Data Control Language (DCL) ................................................................................ 8
1. GRANT Command ......................................................................................... 8
2. REVOKE Command........................................................................................ 8
Transaction Control Language (TCL) ....................................................................... 9
1. COMMIT Command ....................................................................................... 9
2. ROLLBACK Command ................................................................................... 9
3. SAVEPOINT Command ............................................................................. 10
Key Differences Between DROP, TRUNCATE, and DELETE ...................................... 11
SQL Constraints..................................................................................................... 12
Types of SQL Constraints .................................................................................... 12
1. NOT NULL................................................................................................ 12
2. UNIQUE................................................................................................... 12
3. PRIMARY KEY ........................................................................................... 12
4. FOREIGN KEY........................................................................................... 13
5. CHECK .................................................................................................... 13
6. DEFAULT.................................................................................................. 13
7. AUTO_INCREMENT (IDENTITY in SQL Server) .............................................. 13

1
FILTERING AND SORTING DATA ............................................................................... 14
Filtering Data ...................................................................................................... 14
Using IS NULL Operator ...................................................................................... 15
Operators in SQL ................................................................................................ 15
Sorting Data ....................................................................................................... 15
Combining Filtering and Sorting ........................................................................... 16
Limiting Results (TOP, FETCH) .............................................................................. 16
Aggregate Functions ........................................................................................... 16
Grouping Data .................................................................................................... 17
GROUP BY AND HAVING ......................................................................................... 18
Using GROUP BY ................................................................................................ 18
Using GROUP BY with HAVING............................................................................. 18
Using Multiple Columns in GROUP BY .................................................................. 18
Using HAVING with GROUP BY and Aggregate Functions ....................................... 19
Key Differences Between WHERE and HAVING Clauses ........................................ 19
SQL FUNCTIONS.................................................................................................... 20
Aggregate Functions ........................................................................................... 20
String Functions ................................................................................................. 20
Date Functions ................................................................................................... 21
Mathematical Functions ..................................................................................... 22
Ranking Functions in SQL ....................................................................................... 23
RANK() ............................................................................................................... 23
DENSE_RANK() ................................................................................................... 24
ROW_NUMBER()................................................................................................. 24
JOINS IN SQL ......................................................................................................... 25
Example Tables in SQL Server .............................................................................. 25
1. INNER JOIN .................................................................................................... 26
2. LEFT JOIN (LEFT OUTER JOIN) .......................................................................... 27
3. RIGHT JOIN (RIGHT OUTER JOIN) ..................................................................... 28
4. FULL OUTER JOIN ........................................................................................... 28
5. CROSS JOIN ................................................................................................... 29
6. SELF JOIN ....................................................................................................... 29

2
SUBQUERIES IN MS SQL SERVER ............................................................................ 32
Example 1: Subquery in the SELECT Clause .......................................................... 32
Example 2: Subquery in the WHERE Clause .......................................................... 33
Example 3: Subquery in the FROM Clause ............................................................ 33
Example 4: Correlated Subquery ......................................................................... 34
VIEWS IN SQL SERVER ........................................................................................... 35
Creating a View .................................................................................................. 35
Alter a view ........................................................................................................ 36
Dropping a View (DROP VIEW) ............................................................................. 36
Updatable Views ................................................................................................ 36
INDEX ................................................................................................................... 38
Create an Index .................................................................................................. 38
Query the Table Using the Indexed Column........................................................... 39
STORED PROCEDURES .......................................................................................... 40
CREATING STORED PROCEDURES WITHOUT PARAMETERS ...................................... 40
CREATING A STORED PROCEDURES WITH PARAMETERS ......................................... 41
CREATING STORED PROCEDURE TO CREATE A TABLE .............................................. 41
CREATING STORED PROCEDURE TO INSERT DATA INTO A TABLE .............................. 42
CREATING STORED PROCEDURE TO UPDATE DATA IN THE TABLE ............................. 42
User-defined function (UDF) in SQL Server .................................................................. 43
Types of User-Defined Functions: ........................................................................ 43
Creating a Scalar Function .................................................................................. 43
Creating a Table-Valued Function:........................................................................ 44
Difference Between Stored Procedures and Functions ............................................. 46
Triggers in MS SQL Server ....................................................................................... 47
DML Triggers in MS SQL Server............................................................................. 47
Temporary tables in SQL Server ................................................................................. 50

3
SQL COMMANDS
SQL commands can be classified into five categories based on functionality, along with
their syntax and examples:

Data Definition Language (DDL)

DDL commands are used to define and modify database schema objects such
as tables, indexes, and views.

Key DDL Commands

1. CREATE – Creates a new database object (e.g., table, view, index).

2. ALTER – Modifies an existing database object.

3. DROP – Deletes a database object permanently.

4. TRUNCATE – Removes all records from a table but retains its structure.

5. RENAME – Changes the name of a database object.

1. CREATE Command

Used to create new tables, databases, indexes, or views.

Syntax:

CREATE TABLE table_name (

column1 datatype CONSTRAINT,
column2 datatype CONSTRAINT,
...
);

Example:

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT,
Salary DECIMAL(10,2)
);

2. ALTER Command
Used to modify an existing database object, such as adding, deleting, or
modifying columns.

4
 Syntax (Add a Column):

ALTER TABLE table_name

ADD column_name datatype;
Example (Add a Column):

ALTER TABLE Employees

ADD Email VARCHAR(100);

Syntax (Drop a Column):

ALTER TABLE table_name

DROP COLUMN column_name;
Example (Drop a Column):

ALTER TABLE Employees

DROP COLUMN Salary;

Syntax (Modify a Column):

ALTER TABLE table_name

ALTER COLUMN column_name datatype;
Example (Modify a Column):

ALTER TABLE Employees

ALTER COLUMN Age SMALLINT;

Syntax (Rename a Column):

EXEC sp_rename 'table_name.old_column_name', 'new_column_name

', 'COLUMN';
Example (Rename a Column):

EXEC sp_rename '[Link]', 'Email_ID', 'COLUMN';

3. DROP Command
Used to delete a database object permanently.

Used to delete an entire table, view, or database

Syntax :

DROP TABLE table_name;

Example :

DROP TABLE Employees;

5
4. TRUNCATE Command
Used to remove all records from a table without deleting its structure.

Syntax :

TRUNCATE TABLE table_name;

Example :

TRUNCATE TABLE Employees;

5. RENAME Command
Used to rename an existing table.

Syntax :

Exec sp_rename 'old_table_name','new_table_name';

Example :

exec sp_rename 'Employees', 'Employees_Details'

Data Manipulation Language (DML) in SQL

DML commands are used to manipulate the data in the database.

DML commands are responsible for inserting, updating, deleting, and retrieving data
from tables.

DML Commands in SQL

1. INSERT – Adds new records to a table.

2. UPDATE – Modifies existing records in a table.

3. DELETE – Removes records from a table.

4. SELECT – Retrieves records from a table. (Though SELECT is sometimes

categorized under DQL - Data Query Language, it is often considered a part of
DML as well.)

1. INSERT Command

Used to add new rows to a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)

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

6
 Example:

INSERT INTO Employees (EmpID, Name, Age, Department)

VALUES (101, 'John Doe', 30, 'HR');

2. UPDATE Command
Used to modify existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Employees
SET Age = 31
WHERE EmpID = 101;

3. DELETE Command
Used to remove records from a table.

Syntax:

DELETE FROM table_name

WHERE condition;
Example:

DELETE FROM Employees

WHERE EmpID = 101;
Omitting the WHERE clause will delete all records in the table.

4. SELECT Command
Used to retrieve data from a table.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition;
Example:

SELECT Name, Age

FROM Employees
WHERE Department = 'HR';

7
Data Query Language (DQL)
DQL is used to retrieve data from the database.

SELECT Command
Fetches records from a table

Used to retrieve data from a table.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition;
Example:

SELECT Name, Age

FROM Employees
WHERE Department = 'HR';

Data Control Language (DCL)

Data Control Language (DCL) in SQL is used to manage access privileges and
permissions in a database. It primarily consists of two commands:

1. GRANT – Provides specific privileges to users or roles.

2. REVOKE – Removes specific privileges from users or roles.

1. GRANT Command

The GRANT command is used to give access permissions to a user for performing
specific actions on a database object.

Syntax:

GRANT privilege_name ON object_name TO user_name;

Example:

Grant SELECT and INSERT privileges on the Customers table to the user John:

GRANT SELECT, INSERT ON Customers TO John;

2. REVOKE Command
The REVOKE command is used to take back previously granted permissions from
a user or role.
8
 Syntax:

REVOKE privilege_name ON object_name FROM user_name;

Example:

Revoke the INSERT privilege from the user John on the Customers table:

REVOKE INSERT ON Customers FROM John;

• DCL is used for security management in SQL databases.

• GRANT assigns permissions, while REVOKE removes them.
• Privileges can be assigned to users, roles, or groups.
• Used mostly by DBAs (Database Administrators) to control access.

Transaction Control Language (TCL)

Transaction Control Language (TCL) is used to manage transactions in a database.

These commands are used to control the changes made by DML (Data Manipulation
Language) statements such as INSERT, UPDATE, and DELETE. TCL ensures the
integrity and consistency of the database.

1. COMMIT Command
• The COMMIT command is used to save all the changes made by the current
transaction permanently in the database.

• Once committed, the changes cannot be rolled back.

Syntax:

COMMIT;

Example:

BEGIN TRANSACTION;
INSERT INTO Customers (CustomerID, Name, City) VALUES (1,
'Alice', 'New York');
COMMIT;

2. ROLLBACK Command
• The ROLLBACK command is used to undo all changes made by the current
transaction before a COMMIT is executed.

• If an error occurs, ROLLBACK ensures that no partial changes are saved.

9
 Syntax:

ROLLBACK;

Example:

BEGIN TRANSACTION;
UPDATE Customers SET City = 'Los Angeles' WHERE CustomerID =
1;
ROLLBACK;

This will revert the city of the customer back to its original value.

3. SAVEPOINT Command
The SAVEPOINT command allows setting a point within a transaction to which
changes can be rolled back partially, instead of rolling back the entire
transaction.

Create an example table:

CREATE TABLE Employee (

EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Position NVARCHAR(50) NOT NULL,
Age INT NOT NULL
);
Start a Transaction
BEGIN TRANSACTION;

Insert Initial Data

INSERT INTO Employee (Name, Position, Age) VALUES ('John Doe', 'Manager', 40);

Create a Savepoint
SAVE TRANSACTION SavePoint1;

Insert More Data

INSERT INTO Employees (Name, Position, Age) VALUES ('Jane Smith', 'Developer', 30);
INSERT INTO Employees (Name, Position, Age) VALUES ('Alice Brown', 'Analyst', 25);

10
 Simulate an Error
INSERT INTO Employees (Name, Position, Age) VALUES ('Invalid Entry', NULL, 20); --
This will fail!

Roll Back to Savepoint

ROLLBACK TRANSACTION SavePoint1;

Commit the Transaction

COMMIT TRANSACTION;

Finally, commit the transaction to permanently save the changes made

before the savepoint.

Example:

BEGIN TRANSACTION;
INSERT INTO Customers (CustomerID, Name, City) VALUES (2, 'Bob',
'Chicago');
SAVEPOINT sp1;
UPDATE Customers SET City = 'San Francisco' WHERE CustomerID
= 2;
ROLLBACK TO sp1;
COMMIT;
The rollback will undo the UPDATE statement but keep the INSERT operation.

Key Differences Between DROP, TRUNCATE, and DELETE

COMMAND PURPOSE CAN BE DELETES PERFORMANCE
ROLLED STRUCTURE?
BACK?
DROP Deletes table No Yes Fast
completely
TRUNCATE Deletes all rows No No Faster than
but keeps DELETE
structure
DELETE Deletes specific Yes (if in a No Slower
rows based on transaction)
condition

11
SQL Constraints
SQL constraints are rules enforced on table columns to maintain data integrity and
ensure accuracy and reliability. They define the rules that data must follow within a
database.

Types of SQL Constraints

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
7. AUTO_INCREMENT (IDENTITY in SQL Server)

1. NOT NULL
• Ensures that a column cannot have NULL values.

Example:

CREATE TABLE Employees (

ID INT NOT NULL,
Name VARCHAR(50) NOT NULL
);

2. UNIQUE
• Ensures that all values in a column are unique.

Example:

CREATE TABLE Employees (

ID INT UNIQUE,
Email VARCHAR(100) UNIQUE
);

3. PRIMARY KEY
• Combines NOT NULL and UNIQUE to identify each record uniquely.

Example:

CREATE TABLE Employees (

ID INT PRIMARY KEY,
Name VARCHAR(50)
);

12
4. FOREIGN KEY
• Ensures referential integrity by linking one table’s column to another table’s
primary key.

Example:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

5. CHECK
• Ensures that values in a column meet specific conditions.

Example:

CREATE TABLE Employees (

ID INT PRIMARY KEY,
Age INT CHECK (Age >= 18)
);

6. DEFAULT
• Assigns a default value to a column if no value is provided.

Example:

CREATE TABLE Employees (

ID INT PRIMARY KEY,
Status VARCHAR(10) DEFAULT 'Active'
);

7. AUTO_INCREMENT (IDENTITY in SQL Server)

• Automatically generates a unique number for new records.
Example:

CREATE TABLE Employees (

ID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50)
);

13
FILTERING AND SORTING DATA
Filtering and sorting data in SQL is essential for retrieving specific results from a
database in an organized manner.

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

Examples:

Filter by a Single Condition

SELECT * FROM Customers

WHERE Country = 'USA';

Filter with Multiple Conditions (AND, OR)

SELECT * FROM Orders

WHERE OrderDate >= '2024-01-01' AND Status = 'Shipped';

Filter with IN (Multiple Values)

SELECT * FROM Employees

WHERE Department IN ('HR', 'IT', 'Finance');

Filter with LIKE (Pattern Matching)

The LIKE operator is used for pattern matching with wildcard characters:

• % – Matches zero or more characters.

• _ – Matches exactly one character.

SELECT * FROM Products

WHERE ProductName LIKE 'A%';
Finds products whose names start with "A"

SELECT * FROM Employees

WHERE Name LIKE '%n';
Find employees whose names end with 'n'.

SELECT * FROM Employees

14
 WHERE Name LIKE 'A____';
Find employees with a 5-letter name starting with 'A'.

Filter with BETWEEN (Range)

SELECT * FROM Sales

WHERE SaleAmount BETWEEN 500 AND 1000;

Using IS NULL Operator

The IS NULL operator filters records where a column has no value (NULL).

Example:

Find employees who haven't provided their phone numbers.

SELECT * FROM Employees

WHERE PhoneNumber IS NULL;

Operators in SQL
Comparison Operators:

Operator Description Example

= Equal to WHERE Age = 30
!= or <> Not equal to WHERE Age != 30
< Less than WHERE Salary < 50000
> Greater than WHERE Salary > 60000
<= Less than or equal to WHERE Age <= 25
>= Greater than or equal to WHERE Experience >= 5

Sorting Data
The ORDER BY clause is used to sort results in ascending (ASC) or descending (DESC)
order.

Examples:

Sort by a Single Column

SELECT * FROM Employees

ORDER BY LastName ASC;

Sort in Descending Order

SELECT * FROM Orders

ORDER BY OrderDate DESC;

15
Sort by Multiple Columns

SELECT * FROM Products

ORDER BY Category ASC, Price DESC;

Combining Filtering and Sorting

SELECT * FROM Customers
WHERE Country = 'Canada'
ORDER BY CustomerName ASC;
Filters customers from Canada and sorts them by name.

Limiting Results (TOP, FETCH)

Examples:

Using TOP

Get the top 5 highest-paid employees.

SELECT TOP 5 * FROM Employees

ORDER BY Salary DESC;

For pagination, use OFFSET and FETCH:

SELECT * FROM Orders

ORDER BY OrderDate DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Skip first 10, get next 5

Aggregate Functions
• SUM(column_name) → Returns the total sum of a numeric column.

SELECT SUM(Price) AS TotalPrice

FROM Products;

• AVG(column_name) → Returns the average value of a numeric column

SELECT AVG(Salary) AS AvgSalary

FROM Employees;

• MIN(column_name) → Returns the minimum value in a column.

16
 SELECT MIN(Age) AS Youngest
FROM Employees;

• MAX(column_name) → Returns the maximum value in a column

SELECT MAX(Salary) AS
HighestSalary FROM Employees;

• COUNT(column_name) → Returns the number of rows in a column (excluding

NULL values).

SELECT COUNT(*) AS TotalEmployees

FROM Employees;

Grouping Data
a) GROUP BY

Groups data to perform aggregate calculations.

SELECT CustomerID, COUNT(OrderID) AS

TotalOrders
FROM Orders
GROUP BY CustomerID;

b) HAVING (Filter grouped data)

Unlike WHERE, HAVING filters after aggregation.

SELECT CustomerID, COUNT(OrderID) AS TotalOrders

FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;

-- Show only customers with more than 5 orders

17
GROUP BY AND HAVING
• GROUP BY is used with aggregate functions like SUM(), AVG(), COUNT(), MIN(),
MAX().
• HAVING filters grouped results (similar to WHERE, but for aggregated values).
• WHERE is used before GROUP BY to filter individual rows, whereas HAVING is
used after GROUP BY to filter groups.

CREATE TABLE Sales (

SalesID INT PRIMARY KEY,
Product_Name VARCHAR(100) NOT NULL,
Category VARCHAR(50) NOT NULL,
Quantity INT NOT NULL,
Price INT NOT NULL
);

Using GROUP BY
Suppose we want to find the total quantity sold for each product.

SELECT Product_Name, SUM(Quantity) AS TotalQuantity

FROM Sales
GROUP BY Product_Name;

Using GROUP BY with HAVING

Now, let's add a condition to filter only those products where the total quantity sold is
greater than 3.

SELECT Product_Name, SUM(Quantity) AS TotalQuantity

FROM Sales
GROUP BY Product_Name
HAVING SUM(Quantity) > 3;

Using Multiple Columns in GROUP BY

If we want to group by Category as well:

SELECT Category, Product_Name, SUM(Quantity) AS TotalQuantity

FROM Sales
GROUP BY Category, Product_Name;

18
Using HAVING with GROUP BY and Aggregate Functions
If we want to filter categories where the total quantity sold is more than 5:

SELECT Category, SUM(Quantity) AS TotalQuantity

FROM Sales
GROUP BY Category
HAVING SUM(Quantity) > 5;

Key Differences Between WHERE and HAVING Clauses

Feature WHERE Clause HAVING Clause

Purpose Filters rows before Filters groups after aggregation

aggregation

Works on Individual records Aggregate functions (e.g.,

SUM(), COUNT())

Used With SELECT, UPDATE, GROUP BY

DELETE

Can you Use Aggregate No Yes

Functions?

Execution Stage Before grouping After grouping

19
SQL FUNCTIONS
1. Aggregate Functions (Used for summarizing data)
2. String Functions (Used for string manipulation)
3. Date Functions (Used for date and time manipulation)
4. Mathematical Functions (Used for numerical calculations)

CREATE TABLE Products(

Product_ID INT NOT NULL PRIMARY KEY,
Product_Name VARCHAR(50),
Quantity INT NOT NULL,
Price FLOAT NOT NULL
)

Aggregate Functions
• SUM(column_name) → Returns the total sum of a numeric column.

SELECT SUM(Price) AS TotalPrice

FROM Products;

• AVG(column_name) → Returns the average value of a numeric column

SELECT AVG(Salary) AS AvgSalary

FROM Employees;

• MIN(column_name) → Returns the minimum value in a column.

SELECT MIN(Age) AS Youngest

FROM Employees;

• MAX(column_name) → Returns the maximum value in a column

SELECT MAX(Salary) AS
HighestSalary FROM Employees;

• COUNT(column_name) → Returns the number of rows in a column (excluding

NULL values).

SELECT COUNT(*) AS TotalEmployees

FROM Employees;

String Functions
• LEN(string) → Returns the length of a string

20
 SELECT LEN('SQL Server') AS
StringLength;

-- Output: 10

• SUBSTRING(string, start, length) → Extracts part of a string

SELECT SUBSTRING('Hello SQL', 1, 5) AS

ExtractedString;

-- Output: Hello

• UPPER(string) → Converts a string to uppercase

SELECT UPPER('sql server') AS UpperCaseText;

-- Output: SQL SERVER

• LOWER(string) → Converts a string to lowercase

SELECT LOWER('SQL SERVER') AS LowerCaseText;

-- Output: sql server

• CONCAT(string1, string2, ...) → Combines multiple strings.

SELECT CONCAT('Hello', ' ', 'World') AS

ConcatenatedText;

-- Output: Hello World

Date Functions
• GETDATE() → Returns the current system date and time.

SELECT GETDATE() AS CurrentDateTime;

• DATEADD(interval, number, date) → Adds a specified number of time units to a

date

SELECT DATEADD(DAY, 7, GETDATE()) AS

NextWeek;
-- Adds 7 days

• DATEDIFF(interval, start_date, end_date) → Returns the difference between two

dates.

21
 SELECT DATEDIFF(YEAR, '2000-01-01', GETDATE()) AS
YearsDifference;

Mathematical Functions
• ROUND(number, decimals) → Rounds a number to the specified number of
decimal places.

SELECT ROUND(123.4567, 2) AS RoundedValue;

-- Output: 123.46

• CEILING(number) → Rounds a number up to the nearest integer.

SELECT CEILING(4.2) AS CeilValue;

-- Output: 5

• FLOOR(number) → Rounds a number down to the nearest integer

SELECT FLOOR(4.9) AS FloorValue;

-- Output: 4

22
Ranking Functions in SQL
1. RANK() – Assigns a rank to each row but skips numbers when there are ties.
2. DENSE_RANK() – Assigns a rank to each row but does not skip numbers when
there are ties.
3. ROW_NUMBER() – Assigns a unique sequential number to each row, ignoring
duplicate values.

Example Table: Employees

Imagine we have the following dataset:

EmployeeID Name Department Salary

1 Alice IT 75000

2 Bob HR 60000

3 Carol IT 90000

4 Dave IT 75000

5 Eve HR 85000

RANK()
Example (Ranks employees by salary, skipping numbers for ties)

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank

FROM Employees;

Output:

Name Salary Rank

Carol 90000 1

Eve 85000 2

Alice 75000 3

Dave 75000 3

Bob 60000 5

Notice: Since Alice and Dave have the same salary, they both get rank 3, but the next
rank 4 is skipped.

23
DENSE_RANK()
Example (Ranks employees without skipping numbers)

SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank

FROM Employees;

Output:

Name Salary DenseRank

Carol 90000 1

Eve 85000 2

Alice 75000 3

Dave 75000 3

Bob 60000 4

Difference from RANK(): The numbering remains continuous even when there are ties.

ROW_NUMBER()
Example (Assigns a unique row number)

SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum

FROM Employees;

Output:

Name Salary RowNum

Carol 90000 1

Eve 85000 2

Alice 75000 3

Dave 75000 4

Bob 60000 5

Unlike RANK() and DENSE_RANK(), this assigns a unique number to each row, even
when salaries are equal.

24
JOINS IN SQL
In MS SQL Server, joins retrieve data from multiple tables based on a related column.
The main types of joins are:

1. INNER JOIN

2. LEFT JOIN (LEFT OUTER JOIN)

3. RIGHT JOIN (RIGHT OUTER JOIN)

4. FULL JOIN (FULL OUTER JOIN)

5. CROSS JOIN

6. SELF JOIN

Example Tables in SQL Server

Let's create two tables: Customers and Orders.

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
City NVARCHAR(50)
);

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
OrderDate DATE,
Amount DECIMAL(10,2)
);

When to Use?

• Use VARCHAR if your data is strictly in English or does not require Unicode
support (saves storage). (1 byte per character)

• Use NVARCHAR if you need to store multiple languages or special characters. (2

bytes per character for most characters, but some may take 3-4 bytes).

Example Table Data:

25
Remove Foreign key constraints While entering the orders data with customer id not in
the customers table

1. INNER JOIN
• Returns only the matching rows from both tables.

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers
INNER JOIN Orders ON [Link] = [Link];

26
2. LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table (Customers) and matching records from the
right table (Orders). If there’s no match, NULL is returned for columns from the right
table.

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers
LEFT JOIN Orders ON [Link] = [Link];

27
3. RIGHT JOIN (RIGHT OUTER JOIN)
Returns all records from the right table (Orders) and matching records from the left
table (Customers). If no match is found, NULL appears in left table columns.

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers
RIGHT JOIN Orders ON [Link] = [Link];

4. FULL OUTER JOIN

Returns all records when there is a match in either left (Customers) or right (Orders)
table. If there is no match, NULL values appear.

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers
FULL OUTER JOIN Orders ON [Link] =
[Link];

28
5. CROSS JOIN
Produces a Cartesian product (all possible combinations of rows from both tables).

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers
CROSS JOIN Orders;

Example output:

6. SELF JOIN

29
A table joins itself. Useful for hierarchical or recursive relationships.

Consider an Employees table where each employee has a ManagerID that refers to
another employee in the same table. We want to find the names of employees along
with their managers.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
EmployeeName NVARCHAR(100),
ManagerID INT NULL
);

SELECT
[Link] AS Employee_ID,
[Link] AS Employee_Name,
[Link] AS Manager_ID,
[Link] AS Manager_Name
FROM Employees e1
LEFT JOIN Employees e2 ON [Link] = [Link];

Explanation

• e1 represents employees.

• e2 represents managers (employees who are referenced by ManagerID).

• LEFT JOIN is used to ensure that employees without managers (like Alice) are still
included in the result.

OUTPUT:

30
• Self-Join helps retrieve hierarchical relationships.
• We alias the table to distinguish between employees and managers.
• LEFT JOIN ensures even the top-level managers (who have no superior) are
included.

31
SUBQUERIES IN MS SQL SERVER
A subquery is a query nested inside another query, such as a SELECT, INSERT, UPDATE,
or DELETE statement. Subqueries help break down complex queries and retrieve data in
multiple steps.

1. Single-Row Subquery – Returns a single row and column as a result. Used with
operators like =, >, <, >=, <=, <>.

2. Multi-Row Subquery – Returns multiple rows but only one column. These are
often paired with operators like IN, ANY, or ALL.

3. Correlated Subquery – Refers to columns in the outer query, making it

dependent on the outer query for its execution. Depends on the outer query and
runs repeatedly for each row of the outer query.

Example 1: Subquery in the SELECT Clause

Find the total revenue for each product by multiplying the Price and the Quantity.
Use a subquery to calculate total revenue for each product.

CREATE TABLE Sales (

SaleID INT PRIMARY KEY,
ProductName NVARCHAR(50),
Price DECIMAL(10, 2),
Quantity INT
);

INSERT INTO Sales (SaleID, ProductName, Price, Quantity) VALUES (1, 'Laptop',
800.00, 3);
INSERT INTO Sales (SaleID, ProductName, Price, Quantity) VALUES (2, 'Tablet',
300.00, 5);
INSERT INTO Sales (SaleID, ProductName, Price, Quantity) VALUES (3,
'Headphones', 100.00, 10);

Query

SELECT ProductName,
Price,
Quantity,

32
 (SELECT Price * Quantity FROM Sales AS S2 WHERE [Link] = [Link]) AS
TotalRevenue
FROM Sales AS S1;

OUTPUT:

Example 2: Subquery in the WHERE Clause

Find all products whose price is greater than the average price of all products.

Query

SELECT ProductName, Price

FROM Sales
WHERE Price > (SELECT AVG(Price) FROM Sales);

OUTPUT:

Example 3: Subquery in the FROM Clause

List the average price of products, along with the highest-priced product in the table.

Query

SELECT [Link], [Link]

FROM
(SELECT AVG(Price) AS AvgPrice FROM Sales) AS A,
(SELECT MAX(Price) AS MaxPrice FROM Sales) AS B;

OUTPUT:

33
Example 4: Correlated Subquery
Find all products that cost more than the average price of their own category

Create a table with a category column and some data as below:

Query

SELECT Product_Name, Category, Price

FROM Sales AS P
WHERE Price > (SELECT AVG(Price)
FROM Sales AS C
WHERE [Link] = [Link]);

OUTPUT:

34
VIEWS IN SQL SERVER
In MS SQL Server, a VIEW is a virtual table that is based on the result set of a SQL
query.

A view does not store data itself but retrieves data dynamically from the underlying
tables whenever it is queried.

Condition for Updatable View:

• Must not use DISTINCT, GROUP BY, HAVING, JOIN, or AGGREGATE FUNCTIONS.

Creating a View
The basic syntax for creating a view:

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example Table:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Country VARCHAR(50)
);

Insert the data:

INSERT INTO Customers (CustomerID, CustomerName, Country)

VALUES
(1, 'Alice Johnson', 'USA'),
(2, 'Bob', 'INDIA'),
(3, 'Charlie', 'UK'),
(4, 'Smith', 'UAE'),
(5, 'Johnson', 'USA');

35
Now, we create a view that selects only customers from the USA:

CREATE VIEW US_Customers AS

SELECT CustomerID, CustomerName
FROM Customers
WHERE Country = 'USA';

Output:

Alter a view
If you need to change an existing view:

ALTER VIEW US_Customers AS

SELECT CustomerID, CustomerName, Country
FROM Customers
WHERE Country = 'USA';

Output:

Dropping a View (DROP VIEW)

To remove a view from the database:

DROP VIEW US_Customers;

Updatable Views
Condition for Updatable View:

• Must not use DISTINCT, GROUP BY, HAVING, JOIN, or AGGREGATE FUNCTIONS.

36
Updating Data Through View

UPDATE US_Customers
SET CustomerName = 'Jonathan'
WHERE CustomerID = 1;

Output:

Inserting Data Through View

INSERT INTO US_Customers (CustomerID,

CustomerName)
VALUES (6, 'Michael');

Output:

See the customers table data will be inserted

Deleting Data Through View

DELETE FROM US_Customers WHERE CustomerID = 1;

Output:

37
INDEX
An index is a database object that improves the speed of data retrieval operations on a
table.

Create a Sample Table

CREATE TABLE Employees_table (

EmployeeID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10, 2)
);

Insert some data into the table

Output table:

Create an Index
CREATE INDEX IDX_Salary
ON Employees_table (Salary);

Explanation:

• CREATE INDEX: The command to create an index.

• IDX_Salary: The name of the index (you can use any name you prefer).

• (Salary): The column on which the index is created. This makes queries on Salary
faster.

38
Query the Table Using the Indexed Column

SELECT EmployeeID, EmpName, Salary

FROM Employees_table
WHERE Salary > 55000;

The index on the Salary column allows SQL Server to quickly locate rows where the
Salary is greater than 55000 without scanning the entire table.

Query result for Salary > 55000:

Key Benefits of an Index:

1. Improved Query Performance: Indexes reduce the time to search and retrieve
data from a table.

2. Efficient Sorting: Sorting operations (e.g., ORDER BY) on indexed columns are
faster.

3. Drawback: Indexes slightly slow down INSERT, UPDATE, and DELETE operations
because the index needs to be maintained.

39
STORED PROCEDURES

• A stored procedure is a prepared SQL code that can be saved for reuse.
• If you have an SQL query that you use repeatedly, you can save it as a stored
procedure and then call it whenever needed.
• Additionally, you can pass parameters to a stored procedure so that it can
perform actions based on the parameter value(s) passed to it.

CREATING STORED PROCEDURES WITHOUT

PARAMETERS
SYNTAX
CREATE PROCEDURE procedure_name
AS
BEGIN

sql_statement

END;

EXECUTE SYNTAX
EXEC procedure_name;

EXAMPLE
CREATE PROCEDURE GetStudentDetails
AS
BEGIN

SELECT * FROM StudentDetails

END

EXEC GetStudentDetails

40
CREATING A STORED PROCEDURES WITH
PARAMETERS

CREATE PROCEDURE GetStudentAge

(@studentAge INT)
AS
BEGIN

SELECT * FROM StudentDetails WHERE studentAge = @studentAge

END

EXEC GetStudentAge 27

EXAMPLE
CREATE PROC StudentNameDetails
@StudentName varchar(150) = '%'
AS
BEGIN
select * from StudentDetails
where StudentName LIKE @StudentName;
END

EXEC StudentNameDetails's%'

CREATING STORED PROCEDURE TO CREATE A TABLE

CREATE PROCEDURE CreateTableProcedure

AS
BEGIN
CREATE TABLE Student
(Id int PRIMARY KEY IDENTITY(101,1),
Name Varchar(140) NOT NULL,
City Varchar(150) NOT NULL)
END

EXEC CreateTableProcedure

41
CREATING STORED PROCEDURE TO INSERT DATA
INTO A TABLE
CREATE PROCEDURE InsertDataProcedure
@Name VARCHAR(150),
@City VARCHAR(150)
AS
BEGIN
INSERT INTO Student
(Name,City)
VALUES
(@Name,@City)
END

EXEC InsertDataProcedure 'Anu','Singapore'

CREATING STORED PROCEDURE TO UPDATE DATA IN

THE TABLE

CREATE PROCEDURE UpdateDataProcedure

@Id int,
@Name varchar(150)
AS
BEGIN
UPDATE Student SET
Name = @Name
WHERE
Id = @Id
END

EXEC UpdateDataProcedure 101,'Anucia'

42
User-defined function (UDF) in SQL Server
A user-defined function (UDF) in SQL Server is a programmable routine that you can create
to perform a specific task.

UDFs allow you to encapsulate reusable logic that can return a scalar value or a table,
depending on the type of function.

UDFs are particularly useful for tasks that need to be executed repeatedly, such as
formatting data, performing calculations, or encapsulating complex logic.

Types of User-Defined Functions:

Scalar Function: Returns a single value (e.g., int, nvarchar).

Table-Valued Function:

• Inline Table-Valued Function: Returns a table using a single SELECT statement.

• Multi-Statement Table-Valued Function: Returns a table using multiple statements.

Creating a Scalar Function

A scalar function returns a single value. Here's an example:

Syntax:

CREATE FUNCTION [SchemaName].[FunctionName]

(
@ParameterName DataType, -- Input parameter(s)
...
)
RETURNS ReturnDataType -- Specify the return data type
AS
BEGIN
-- Function logic
RETURN Expression
END

Example: Calculate Bonus

We’ll create a scalar function that calculates a 10% bonus on a salary.

CREATE FUNCTION [Link] (@Salary DECIMAL(10, 2))

RETURNS DECIMAL(10, 2)
AS
BEGIN
RETURN (@Salary * 0.10);
END;

43
Usage:

SELECT [Link](50000); -- Returns 5000

Creating a Table-Valued Function:

A table-valued function returns a table.

Syntax:

CREATE FUNCTION [SchemaName].[FunctionName]

(
@ParameterName DataType, -- Input parameter(s)
...
)
RETURNS TABLE
AS
RETURN
(
-- Select query to define the table
SELECT Columns
FROM SomeTable
WHERE Conditions
)

Example: Retrieve Employees in a Specific Department

We'll create a function to return employees from a specific department.

CREATE FUNCTION [Link] (@Department NVARCHAR(50))

RETURNS TABLE
AS
RETURN (
SELECT EmpID, EmpName, Department, Salary
FROM Employees
WHERE Department = @Department
);

Usage:

SELECT * FROM [Link]('IT');

Step-by-Step: Example with Table Creation and Data Insertion:

44
Create the Table:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
EmpName NVARCHAR(50),
Department NVARCHAR(50),
Salary DECIMAL(10, 2)
);

Insert Data:
INSERT INTO Employees (EmpID, EmpName, Department, Salary)
VALUES (101, 'John Doe', 'IT', 75000.00),
(102, 'Jane Smith', 'HR', 65000.00),
(103, 'Robert Brown', 'Finance', 80000.00),
(104, 'Emily White', 'IT', 70000.00);

Create and Use a Scalar Function:

CREATE FUNCTION [Link] (@Salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
RETURN (@Salary * 0.10);
END;

-- Usage:
SELECT EmpName, Salary, [Link](Salary) AS Bonus
FROM Employees;

Create and Use a Table-Valued Function:

CREATE FUNCTION [Link] (@Department NVARCHAR(50))
RETURNS TABLE
AS
RETURN (
SELECT EmpID, EmpName, Department, Salary
FROM Employees
WHERE Department = @Department
);

-- Usage:
SELECT * FROM [Link]('IT');

45
Difference Between Stored Procedures and Functions
Aspect Stored Procedure Function
Purpose Used to perform tasks like Primarily used to return a value
updates, inserts, or complex or perform calculations.
logic.
Perform operations
Return Type May return zero, one, or multiple Always returns a single value
values. (scalar, table).
Execution Called using EXEC or EXECUTE. Called as part of a query (e.g.,
SELECT).
Used in queries
Parameters Supports input-output Only supports input
parameters. parameters.
Transaction Can include transaction handling Cannot manage transactions.
Handling (e.g., BEGIN, COMMIT).
Usage in Cannot be directly used in SQL Can be used in queries and
Queries queries like SELECT. expressions.

46
Triggers in MS SQL Server
• A trigger in MS SQL Server is a database object that automatically executes or
"fires" in response to certain events on a table or view.
• Triggers are often used to enforce business rules, automatically update audit
logs, or validate data changes.

DML Triggers: Fired by data manipulation events such as INSERT, UPDATE, or DELETE.

DML Triggers in MS SQL Server

Create the Orders table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
ProductName NVARCHAR(50),
Quantity INT,
Price DECIMAL(10, 2)
);
Create the AuditLog Table
CREATE TABLE AuditLog (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
AuditAction NVARCHAR(50),
OrderID INT,
ActionTime DATETIME DEFAULT GETDATE()
);

Insert some data into the orders table

INSERT INTO Orders (OrderID, ProductName, Quantity, Price)
VALUES
(1, 'Laptop', 10, 50000),
(2, 'Mouse', 50, 500),
(3, 'Keyboard', 30, 1000);

Create the INSERT Trigger

CREATE TRIGGER trg_AfterInsert
ON Orders
AFTER INSERT
AS
BEGIN
INSERT INTO AuditLog (AuditAction, OrderID)

47
 SELECT 'INSERT', OrderID FROM INSERTED;
END;

Insert a Record to Test the Trigger

INSERT INTO Orders (OrderID, ProductName, Quantity, Price)

VALUES (4, 'Monitor', 20, 15000);

INSERT INTO Orders (OrderID, ProductName, Quantity, Price)

VALUES (5, 'CPU', 2, 15000);

SELECT * FROM AuditLog; -- Logs the insert action

Output:

AFTER DELETE Trigger

Create the DELETE Trigger
CREATE TRIGGER trg_AfterDelete
ON Orders
AFTER DELETE
AS
BEGIN
INSERT INTO AuditLog (AuditAction, OrderID)
SELECT 'DELETE', OrderID FROM DELETED;
END;

Delete a Record and Test the Trigger

DELETE FROM Orders
WHERE OrderID = 3;

Output:

48
AFTER UPDATE Trigger
Create the UPDATE Trigger
CREATE TRIGGER trg_AfterUpdate
ON Orders
AFTER UPDATE
AS
BEGIN
INSERT INTO AuditLog (AuditAction, OrderID)
SELECT 'UPDATE', OrderID FROM INSERTED;
END;

Update a Record and Test the Trigger

UPDATE Orders
SET Price = 80000
WHERE OrderID = 1;

SELECT * FROM AuditLog;

Output:

Key Takeaways
• Triggers help maintain data consistency, enforce rules, and automate tasks.

• DML triggers specifically track changes due to INSERT, UPDATE, or DELETE

operations.

• Using inserted and deleted pseudo-tables, triggers can access old and new
records dynamically.

49
Temporary tables in SQL Server
Temporary tables in SQL Server exist temporarily and are deleted automatically when the
session or procedure that created them ends. They are extremely useful for storing
intermediate results during query processing.

What is a Temporary Table?

A temporary table is created in the tempdb system database, which is used to temporarily
store data during the execution of a SQL query or procedure. It is automatically dropped
after the session or scope ends. Temporary tables are ideal for:

1. Simplifying complex queries.

2. Storing intermediate data for reuse.
3. Avoiding alterations to permanent tables.

Creating a Temporary Table:

Temporary tables are created using the CREATE TABLE statement, but their names must start
with a # symbol:

1. Single-hash (#) (Local Temp Table):

• A local temporary table is only visible to the session (or connection) in

which it was created.
• Once the session ends, the table is automatically deleted.
• Useful for temporary data storage within a specific session or procedure.
2. Double-hash (##) (Global Temp Table):

• Denotes a global temporary table accessible to all sessions and

connections until the session that created it ends.
• A global temporary table is visible to all sessions and connections across
the database.
• It is automatically deleted only when the session that created it ends and
no other sessions are referencing it.
• Useful when multiple sessions or users need to access the same
temporary data.

Syntax:

CREATE TABLE #TempTableName (

Column1 DataType,
Column2 DataType,
...
);

50
Step 1: Set Up the Original Table

Let's assume we have an Orders table with the following data:

OrderID CustomerID OrderAmount OrderDate

1 101 500 2025-03-01

2 102 1200 2025-03-02

3 101 700 2025-03-05

4 103 2000 2025-03-06

Step 2: Create a Temporary Table

We create a local temporary table to store aggregated data.

CREATE TABLE #TempCustomerSales (

CustomerID INT,
TotalSales MONEY
);

Step 3: Insert Data into the Temporary Table

Now, calculate the total sales per customer from the Orders table and insert it into the
temporary table.

INSERT INTO #TempCustomerSales (CustomerID, TotalSales)

SELECT CustomerID, SUM(OrderAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID;

After the insertion, the #TempCustomerSales table will have the following data:

CustomerID TotalSales

101 1200

102 1200

103 2000

Step 4: Query the Temporary Table

Use the temporary table for further analysis, such as filtering customers with total sales
exceeding ₹1,000.

51
 SELECT CustomerID, TotalSales
FROM #TempCustomerSales
WHERE TotalSales > 1000;

This query will return:

CustomerID TotalSales
101 1200

102 1200

103 2000

Dropping the Temporary Table (Optional):

Temporary tables are automatically dropped at the end of the session, but you can explicitly
drop them if needed:

DROP TABLE #EmployeeTemp;

Notes
1. Temporary tables are created in the tempdb database.
2. Local temporary tables (#TempTable) are only visible to the session that created
them, while global temporary tables (##TempTable) are visible across all sessions.
3. You cannot use temporary tables to persist data after the session ends.

52