MSSQL SQL Database

create database amazon

use amazon

create table customers(

customer_id int primary key,

first_name varchar(100),

last_name varchar(100),

age int,

salary decimal(10,2),

city varchar(100),

address_id int

);

INSERT INTO customers(customer_id, first_name, last_name, age, salary, city, address_id)

VALUES

(1, 'ravi', 'rajput', 24, 10000.00, 'delhi', 1),

(2, 'anku', 'yadav', 24, 15000.00, 'delhi', 2),

(3, 'mani', 'kumar', 34, 18000.00, 'delhi', 3),

(4, 'Ajay', 'kumar', 14, 7000.00, 'noida', 4),

(5, 'John', 'Doe', 44, 71000.00, 'USA', 5);

select*from customers

update customers

set

first_name ='Anku'

where customer_id=2

select*from customers

------------------------------------

sp_help;

EXEC sp_help 'customers';

exec sp_help 'customers'

--------------------------

create table payments

(

customer_id int primary key,

amount int,

mode varchar(100)

);

drop table payments

insert into payments(customer_id, amount,mode)

values

(1, 500, 'upi'),

(2, 1500, 'neft'),

(3, 5200, 'credit card'),

(4, 5500, 'debit card')

select*from payments

---------------------------------

--create index-

Search Speed: Index ek "quick lookup table" ki tarah kaam karta hai, jisse data ko faster search kiya ja sakta hai, jaise kisi book ke index se page number dhoondhna.

Reduce Full Table Scans: Agar index bana ho, to SQL query ko poore table ko scan karne ki zarurat nahi padti. Isse query execution time kam ho jata hai.

Efficiency: Jab aapke table mein bahut zyada rows hote hain (jaise millions), tab index data ko quickly retrieve karne mein madad karta hai.

-------------------------------------------------------

--create index

create index IndAge on customers(age)

--create unique index

create unique index uniquename on customers(first_name)

----composite index-- for multiple fields

create index ct1 on customers(last_name, age)

-------------

sp_helpindex customers

select*from payments

where amount >=1500

select*from customers

where age >=10

------------------------------------------------------

---Joins----

select*from customers AS c

inner join payments AS p

ON c.customer_id= p.customer_id

--or----

select c.customer_id, c.first_name, c.age, p.amount, p.mode from customers AS c

inner join payments AS p

ON c.customer_id = p.customer_id

----left joins-----------

select*from customers AS c

left join payments AS p

ON c.customer_id = p.customer_id

------------------

-- right joins----

select*from customers AS c

right join payments AS p

ON c.customer_id = p.customer_id

----------------

--full join

select*from customers AS c

full outer join payments AS p

ON c.customer_id = p.customer_id

----------------------------

Stored Procedure SQL mein ek precompiled set of SQL statements hota hai jo ek specific task ko perform karta hai. Stored procedures ko ek baar likhkar bar-bar call kiya ja sakta hai, isse aap apne code ko reusability, security, aur performance ke liye optimize kar sakte ho.

Stored Procedure Ka Kya Fayda Hai?

Reusability: Ek baar stored procedure create karne ke baad, aap usse multiple times alag-alag queries ke liye call kar sakte ho. Isse code duplication nahi hota.

Performance: Stored procedures ko SQL Server precompile kar leta hai, matlab har baar SQL query ko compile karne ki zarurat nahi hoti. Yeh execution ko faster bana deta hai.

Security: Stored procedures ko permissions dekar secure kiya ja sakta hai, taaki directly table access na ho. User sirf procedure ko execute kar sake.

Maintainability: Agar aapko koi query change karni ho, to aap stored procedure ko update kar sakte ho bina har query ko individually modify kiye.

Transaction Management: Aap stored procedure mein transactions ko manage kar sakte ho, jaise commit ya rollback karna, jo transactional integrity ensure karta hai.

--------------------------------------------

--stored procedure

create procedure spGetCustomers

as

begin

select first_name, last_name, age, salary from customers

end;

exec spGetCustomers;

--or

spGetCustomers;

------------------

---stored procedures by id--------

create procedure spGetCustomerByID

@id int

as

begin

select*from customers where customer_id = @id

end

exec spGetCustomerByID 3

--stored procedure by particular colomn

create procedure spGetCustomerByAge

@age int

as

begin

select*from customers where age = @age

end

exec spGetCustomerByAge 34

-- store procedure with multiple colomn-----

create procedure spGetCustomerByIdandName

@id int,

@name varchar(100)

as

begin

select*from customers where customer_id=@id and first_name = @name

end

exec spGetCustomerByIdandName 1, 'Ravi'

sp_helptext spGetCustomerByIdandName

--------------------------

--with encryption store procedure

alter procedure spGetCustomerByIdandName

@id int,

@name varchar(100)

with encryption

as

begin

select*from customers where customer_id=@id and first_name = @name

end

sp_helptext spGetCustomerByIdandName

--without encryption stored procedures

alter procedure spGetCustomerByIdandName

@id int,

@name varchar(100)

as

begin

select*from customers where customer_id=@id and first_name = @name

end

sp_helptext spGetCustomerByIdandName

--------------------------------------------------

-- triggers--

riggers SQL mein ek special type ka stored procedure hote hain jo automatically execute hote hain jab koi specific event occur hota hai, jaise INSERT, UPDATE, ya DELETE operations on a table. Triggers ko mainly data integrity aur business rules enforce karne ke liye use kiya jata hai.

Trigger Ka Basic Concept:

Triggers automatically execute hote hain jab koi data modification operation (INSERT, UPDATE, DELETE) ek table par hota hai.

Aap in triggers ko table ke specific columns pe conditions laga ke set kar sakte ho.

Triggers kaafi useful hote hain jab aapko data consistency maintain karni ho ya koi logging system implement karna ho.

-------------

Types of Triggers:

AFTER Trigger (Post-Action Trigger):

Yeh trigger tab execute hota hai jab koi data modification operation complete ho jata hai (insert, update, or delete).

AFTER Trigger ka use usually data consistency ya auditing purposes ke liye hota hai.

BEFORE Trigger (Pre-Action Trigger):

Yeh trigger tab execute hota hai jab koi data modification operation perform hone se pehle hota hai.

SQL Server mein BEFORE trigger ka support nahi hota, lekin kuch other databases mein yeh available hota hai.

INSTEAD OF Trigger:

Yeh trigger data modification operation ko replace kar deta hai. Matlab, jo action normally execute hota, uske badle yeh trigger execute hota hai.

INSTEAD OF Trigger ko aap use karte ho jab aap kisi action ko customize karna chahte ho.

----------------------------

-- ----------------------------------------------------------------------------------------------------

-- Trigger for INSERT operation on the customers table

CREATE TRIGGER tr_customer_for_insert

ON customers

AFTER INSERT

AS

BEGIN

-- Print a message after a new customer has been added

PRINT 'A new customer has been added to the customers table.';

-- If you want to track inserted data, you can also display the inserted data

SELECT * FROM inserted;

END;

-- Select all customers to verify data

SELECT * FROM customers;

-- Insert a new customer to test the trigger

INSERT INTO customers (customer_id, first_name, last_name, age, salary, address_id)

VALUES (9, 'Affyvi', 'Singh', 23, 10000, 9);

--------------------------------------------

-- ----------------------------------------------------------------------------------------------------

-- Another INSERT Trigger with more detailed information about the customer added

CREATE TRIGGER tr_customer_for_insertnext

ON customers

AFTER INSERT

AS

BEGIN

-- Display the inserted data

PRINT 'A new customer has been inserted into the customers table with the following details:';

-- Show the inserted data using the inserted pseudo-table

SELECT * FROM inserted;

END;

-- Insert another customer to test this new trigger

INSERT INTO customers (customer_id, first_name, last_name, age, salary, address_id)

VALUES (8, 'Kavi', 'Singh', 63, 10000, 8);

-- ----------------------------------------------------------------------------------------------------

-- Trigger for UPDATE operation on the customers table (INSTEAD OF UPDATE)

CREATE TRIGGER tr_customer_for_insertnexts

ON customers

INSTEAD OF UPDATE

AS

BEGIN

-- Print a message indicating that an update is happening

PRINT 'An update is happening on the customer data.';

-- Display the "old" data from the deleted pseudo-table (before the update)

PRINT 'Old data (before update):';

SELECT * FROM deleted;

-- Display the "new" data from the inserted pseudo-table (after the update)

PRINT 'New data (after update):';

SELECT * FROM inserted;

-- Insert the new data back into the customers table (this mimics the update)

INSERT INTO customers (customer_id, first_name, last_name, age, salary, address_id)

SELECT customer_id, first_name, last_name, age, salary, address_id

FROM inserted;

-- Print a confirmation message that the update has been processed

PRINT 'The customer data has been updated successfully!';

END;

-- Update a customer to test the INSTEAD OF update trigger

UPDATE customers

SET first_name = 'Savi', last_name = 'Singh', age = 67, salary = 10500

WHERE customer_id = 9;

--------------------------------

-- ----------------------------------------------------------------------------------------------------

-- Trigger for DELETE operation on the customers table

CREATE TRIGGER tr_customer_for_delete

ON customers

AFTER DELETE

AS

BEGIN

-- Print a message showing that a delete operation has happened

PRINT 'A customer has been deleted from the customers table.';

-- Display the deleted data using the deleted pseudo-table

SELECT * FROM deleted;

END;

-- Delete a customer to test the DELETE trigger

DELETE FROM customers WHERE customer_id = 6;

-- Select to verify if deletion has occurred

SELECT * FROM customers;

--for drop trigger

IF OBJECT_ID('tr_customer_for_insert', 'TR') IS NOT NULL

DROP TRIGGER tr_customer_for_insert;

--drop multiple trigger columns

DROP TRIGGER IF EXISTS tr_customer_for_insert, tr_customer_for_update, tr_customer_for_delete;

or

-- all triggers removee

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 'DROP TRIGGER IF EXISTS ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name) + ';' + CHAR(13)

FROM sys.triggers

WHERE parent_id = OBJECT_ID('customers'); -- 'customers'

EXEC sp_executesql @sql;

----------------------------------------------Audit-------------------------------------------------------------------------------------------------

Audit ka concept SQL mein data changes ko track karna hota hai. Jab aapko kisi table mein changes (insert, update, delete) ko monitor karna ho ya log karna ho, tab aap Audit tables ka use karte ho.

Audit tables ek tarah se log tables hote hain, jinmein aap record karte ho ki kis user ne kab aur kaunse data ko change kiya tha. Ye aapko track karne mein madad karte hain ki data modifications kab aur kaise hua.

Audit ka Concept:

INSERT Operation: Jab koi naya record add hota hai, tab aap us action ko audit table mein record karte ho.

UPDATE Operation: Jab kisi existing record ko modify kiya jata hai, tab us record ke purane aur naye values ko audit table mein store karte ho.

DELETE Operation: Jab koi record delete hota hai, to us record ka purana data audit table mein store hota hai.

---------------------------------------------------

-- Create the audit table

CREATE TABLE customer_audit

(

audit_id INT PRIMARY KEY IDENTITY,

audit_info VARCHAR(MAX)

);

-- ----------------------------------------------------------------------------------------------------

-- Trigger for INSERT operation on customers table

CREATE TRIGGER tr_customer_audit_for_insert

ON customers

AFTER INSERT

AS

BEGIN

DECLARE @customer_id INT;

SELECT @customer_id = customer_id FROM inserted;

INSERT INTO customer_audit

VALUES

('Customer with ID: ' + CAST(@customer_id AS VARCHAR(100)) + ' is added at ' + CAST(GETDATE() AS VARCHAR(100)));

END;

-- View the contents of customer_audit

SELECT * FROM customer_audit;

-- ----------------------------------------------------------------------------------------------------

-- Trigger for DELETE operation on customers table

CREATE TRIGGER tr_customer_audit_for_delete

ON customers

AFTER DELETE

AS

BEGIN

DECLARE @customer_id INT;

SELECT @customer_id = customer_id FROM deleted;

INSERT INTO customer_audit

VALUES

('Existing Customer with ID: ' + CAST(@customer_id AS VARCHAR(100)) + ' is deleted at ' + CAST(GETDATE() AS VARCHAR(100)));

END;

-- Delete a customer from the customers table (for testing DELETE trigger)

SELECT * FROM customers;

DELETE FROM customers WHERE customer_id = 9;

SELECT * FROM customer_audit;

-- ------------------------------------------------------------

-- Trigger for UPDATE operation on customers table

CREATE TRIGGER tr_customer_audit_for_update

ON customers

AFTER UPDATE

AS

BEGIN

DECLARE @old_customer_id INT, @new_customer_id INT;

SELECT @old_customer_id = customer_id FROM deleted;

SELECT @new_customer_id = customer_id FROM inserted;

INSERT INTO customer_audit

VALUES

('Customer with ID: ' + CAST(@old_customer_id AS VARCHAR(100)) + ' updated to ID: ' + CAST(@new_customer_id AS VARCHAR(100)) + ' at ' + CAST(GETDATE() AS VARCHAR(100)));

END;

-- View the contents of customers table

SELECT * FROM customers;

-- Update a customer to trigger the UPDATE operation

UPDATE customers

SET first_name = 'Ravii',

last_name = 'Rana'

WHERE customer_id = 4;

-- View the audit logs after the update

SELECT * FROM customer_audit;

-- ------------------------------------------------------

-- View the trigger definition for 'tr_customer_audit_for_update'

sp_helptext 'tr_customer_audit_for_update';

--for drop case

-- Drop the triggers first to avoid foreign key or dependency issues

IF OBJECT_ID('tr_customer_audit_for_insert', 'TR') IS NOT NULL

DROP TRIGGER tr_customer_audit_for_insert;

IF OBJECT_ID('tr_customer_audit_for_delete', 'TR') IS NOT NULL

DROP TRIGGER tr_customer_audit_for_delete;

IF OBJECT_ID('tr_customer_audit_for_update', 'TR') IS NOT NULL

DROP TRIGGER tr_customer_audit_for_update;

-- Drop the customer_audit table

IF OBJECT_ID('customer_audit', 'U') IS NOT NULL

DROP TABLE customer_audit;

-- Drop the customers table (make sure there are no foreign key constraints or dependent objects)

IF OBJECT_ID('customers', 'U') IS NOT NULL

-- DROP TABLE customers;

------------------------Transactions-----------------------------

Transactions SQL mein ek important concept hai jo aapko data integrity aur consistency maintain karne mein madad karta hai. Transactions ka use karte waqt aap ensure karte ho ki multiple SQL queries ek saath execute ho, aur agar koi error aata hai toh sari queries rollback ho jati hain, taaki data inconsistent na ho.

Transaction Ka Concept:

Transaction ek group of SQL statements hote hain jo ek unit ke roop mein execute hote hain.

Agar transaction successfully execute ho jata hai, toh COMMIT command use karke changes ko permanent bana dete hain.

Agar koi error aata hai, toh ROLLBACK command use karke sari changes ko undo kar sakte hain, jisse data consistent rahe.

----------------------

ACID Properties of Transactions:

Atomicity:

Transaction ke andar jitne bhi steps hote hain, wo sab complete hona chahiye. Agar kisi bhi step mein problem aaye, toh saari changes rollback ho jati hain.

Consistency:

Transaction data ko valid state mein rakhta hai. Agar koi transaction complete hota hai, toh wo database ko ek valid state mein chhodta hai.

Isolation:

Ek transaction ka effect dusre transactions pe nahi padta. Har transaction ko ek isolated environment mein execute karte hain.

Durability:

Jab ek transaction commit ho jata hai, toh uski changes permanent ho jati hain, chahe system crash ho ya koi aur issue ho.

-------------------------

-- Pehle se existing data dekhna

SELECT * FROM customers;

-- Ek customer ka naam update karna

UPDATE customers

SET first_name = 'Ravii'

WHERE customer_id = 1;

-- Transaction shuru karna

BEGIN TRANSACTION;

-- Customer ka naam update karna

UPDATE customers

SET first_name = 'Ravi'

WHERE customer_id = 1;

-- Agar sab kuch sahi raha to changes ko commit karna

COMMIT TRANSACTION;

-- Agar koi error aaye to transaction ko rollback karna

ROLLBACK TRANSACTION;

--for error handling-----------------

BEGIN TRY

BEGIN TRANSACTION;

-- Data modification statements

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Error handling statements

ROLLBACK TRANSACTION;

-- Error message display

END CATCH;

--------------------------------------------------------

---other example k sath transaction but you need create colunm and insert data-----------

BEGIN TRANSACTION;

-- Pehle account se paisa ghataana

UPDATE accounts

SET balance = balance - 100

WHERE account_id = 'A123';

-- Dusre account mein paisa jodna

UPDATE accounts

SET balance = balance + 100

WHERE account_id = 'B456';

-- Agar dono updates successful hain, to commit karna

COMMIT TRANSACTION;

-- Agar koi error aaye, to rollback karna

ROLLBACK TRANSACTION;

---other example k sath transaction but you need create colunm and insert data-----------

BEGIN TRY

-- Outer Transaction Start

BEGIN TRANSACTION;

-- 1. Inventory Update (Nested Transaction)

SAVE TRANSACTION InventoryUpdate;

BEGIN TRANSACTION InventoryUpdate;

BEGIN TRY

-- Order ke items ki quantity ko update karna

UPDATE products

SET quantity_in_stock = quantity_in_stock - oi.quantity

FROM order_items oi

WHERE oi.product_id = products.product_id

AND oi.order_id = @OrderId;

-- Agar sab kuch sahi raha to commit karna

COMMIT TRANSACTION InventoryUpdate;

END TRY

BEGIN CATCH

-- Agar error aaye to rollback karna

ROLLBACK TRANSACTION InventoryUpdate;

-- Error message ko handle karna

THROW;

END CATCH;

-- 2. Order Creation

INSERT INTO orders (customer_id, order_date, status)

VALUES (@CustomerId, GETDATE(), 'Pending');

SET @OrderId = SCOPE_IDENTITY(); -- Naye order ka ID prapt karna

-- Order items ko insert karna

INSERT INTO order_items (order_id, product_id, quantity, price)

SELECT @OrderId, product_id, quantity, price

FROM order_items_temp

WHERE temp_order_id = @TempOrderId;

-- 3. Payment Processing (Nested Transaction)

SAVE TRANSACTION PaymentProcessing;

BEGIN TRANSACTION PaymentProcessing;

BEGIN TRY

-- Payment details ko insert karna

INSERT INTO payments (order_id, payment_date, amount, payment_method)

VALUES (@OrderId, GETDATE(), @TotalAmount, @PaymentMethod);

-- Agar sab kuch sahi raha to commit karna

COMMIT TRANSACTION PaymentProcessing;

END TRY

BEGIN CATCH

-- Agar error aaye to rollback karna

ROLLBACK TRANSACTION PaymentProcessing;

-- Error message ko handle karna

THROW;

END CATCH;

-- Outer Transaction ko commit karna

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Outer Transaction ko rollback karna

ROLLBACK TRANSACTION;

-- Error message ko handle karna

THROW;

END CATCH;

-----------------------

---other example k sath transaction but you need create colunm and insert data-----------

-- Transaction shuru karte hain

BEGIN TRANSACTION;

-- Order details ko 'orders' table mein insert karte hain

INSERT INTO orders (order_id, customer_id, order_date, total_amount)

VALUES (101, 1, GETDATE(), 1500);

-- Inventory se product quantity ko update karte hain

UPDATE inventory

SET stock_quantity = stock_quantity - 1

WHERE product_id = 1001;

-- Agar dono operations safal rahe to transaction commit karte hain

COMMIT TRANSACTION;

-- Agar koi error aaye to transaction rollback karte hain

ROLLBACK TRANSACTION;

---next example------

-- Outer transaction shuru karte hain

BEGIN TRANSACTION;

-- Inventory update (Nested Transaction)

SAVE TRANSACTION InventoryUpdate;

BEGIN TRANSACTION InventoryUpdate;

BEGIN TRY

-- Product quantity ko update karte hain

UPDATE inventory

SET stock_quantity = stock_quantity - 1

WHERE product_id = 1001;

COMMIT TRANSACTION InventoryUpdate;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION InventoryUpdate;

THROW;

END CATCH;

-- Order creation

INSERT INTO orders (order_id, customer_id, order_date, total_amount)

VALUES (102, 2, GETDATE(), 2000);

-- Payment processing (Nested Transaction)

SAVE TRANSACTION PaymentProcessing;

BEGIN TRANSACTION PaymentProcessing;

BEGIN TRY

-- Payment details ko insert karte hain

INSERT INTO payments (payment_id, order_id, payment_date, amount)

VALUES (201, 102, GETDATE(), 2000);

COMMIT TRANSACTION PaymentProcessing;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION PaymentProcessing;

THROW;

END CATCH;

-- Agar sab kuch safal raha to outer transaction commit karte hain

COMMIT TRANSACTION;

-- Agar koi error aaye to transaction rollback karte hain

ROLLBACK TRANSACTION;

------------------------------------------

--Functions

--functions" kaafi important hote hain. Ye functions aapko data manipulation, calculations, aur querying mein madad karte hain.

--Functions ka use aapko SQL queries likhne mein efficiency aur simplicity laane ke liye hota hai

/* Types of Functions in MSSQL

Functions ke Types – Jaise Squad ke Members:

1. Scalar Functions

Ye woh function hote hain jo ek single value return karte hain.

🎯 Example: LEN(), GETDATE(), UPPER(), LOWER(), etc.

2. Aggregate Functions

Ye poore column ya group pe kaam karte hain aur ek hi result nikalte hain.

🎯 Example: SUM(), AVG(), COUNT(), MIN(), MAX()

3. Date and Time Functions

Time aur date ka jadoo dikhane wale function.

🎯 Example: GETDATE(), DATEADD(), DATEDIFF(), FORMAT()

4. String Functions

Strings (yaani ki text) ko manipulate karne ke liye.

🎯 Example: LEN(), LEFT(), RIGHT(), CHARINDEX(), REPLACE(), SUBSTRING()

5. Mathematical Functions

Maths wale kaam: rounding, power, abs, etc.

🎯 Example: ROUND(), CEILING(), FLOOR(), POWER(), ABS()

6. System Functions

Ye system ke info ya kaam provide karte hain.

🎯 Example: ISNULL(), COALESCE(), @@IDENTITY, SCOPE_IDENTITY()

7. Conversion Functions

Ek data type ko doosre mein convert karte hain.

🎯 Example: CAST(), CONVERT()

*/

/*

1. Scalar Functions

Definition:

Ye functions ek hi value return karte hain. Har row pe alag output nikal sakta hai.

Examples:

LEN(): String ki length.

GETDATE(): Current date/time.

UPPER(), LOWER(): Text uppercase/lowercase banaata hai.

*/

create database ecommerce

use ecommerce

Real-World Table: Customers

CREATE TABLE Customers (

CustomerID INT,

Name VARCHAR(100),

Email VARCHAR(100)

);

select*from Customers

INSERT INTO Customers VALUES

(1, 'Ravi Rajput', 'ravi@gmail.com'),

(2, 'Amit Singh', 'amit@outlook.com');

Query:

select*from Customers

SELECT

Name,

LEN(Name) AS NameLength, -- Scalar: String length

UPPER(Name) AS NameUpper, -- Scalar: Uppercase

LOWER(Email) AS EmailLower, -- Scalar: Lowercase

GETDATE() AS CurrentDate -- Scalar: System date

FROM Customers;

/*

2. Aggregate Functions

Definition:

Ye functions multiple rows pe kaam karte hain aur ek summary value return karte hain.

Examples:

SUM(), AVG(), COUNT(), MIN(), MAX()

*/

Real-World Table: Orders

CREATE TABLE Orders (

OrderID INT,

CustomerID INT,

OrderAmount DECIMAL(10,2),

OrderDate DATE

);

INSERT INTO Orders VALUES

(101, 1, 2500.50, '2024-12-01'),

(102, 1, 800.00, '2025-01-01'),

(103, 2, 1500.00, '2025-01-10');

Query:

select*from Orders

SELECT

COUNT(*) AS TotalOrders, -- Total rows

SUM(OrderAmount) AS TotalAmount, -- Total revenue

AVG(OrderAmount) AS AvgAmount, -- Average order

MAX(OrderAmount) AS MaxOrder, -- Highest

MIN(OrderAmount) AS MinOrder -- Lowest

FROM Orders;

/*

3. Date and Time Functions

Definition:

Date/time ko calculate aur format karne ke liye.

Examples:

GETDATE(), DATEADD(), DATEDIFF(), FORMAT()

Real-World Table: Deliveries

*/

CREATE TABLE Deliveries (

DeliveryID INT,

DeliveryDate DATE

);

INSERT INTO Deliveries VALUES

(1, '2025-01-01'),

(2, '2025-01-15');

Query:

select*from Deliveries

SELECT

DeliveryDate,

GETDATE() AS Today,

DATEDIFF(DAY, DeliveryDate, GETDATE()) AS DaysPassed, -- Diff

DATEADD(DAY, 7, DeliveryDate) AS FollowUpDate, -- Add 7 days

FORMAT(DeliveryDate, 'dd-MM-yyyy') AS Formatted -- Format nicely

FROM Deliveries;

/*

4. String Functions

Definition:

Text data ko modify ya extract karne ke liye use hote hain.

Examples:

LEFT(), RIGHT(), CHARINDEX(), REPLACE(), SUBSTRING(), LEN()

Real-World Table: Products

*/

CREATE TABLE Products (

ProductID INT,

ProductName VARCHAR(100)

);

INSERT INTO Products VALUES

(1, 'Samsung Galaxy S24'),

(2, 'Apple iPhone 15 Pro');

Query:

select*from Products

SELECT

ProductName,

LEFT(ProductName, 6) AS Brand, -- First 6 letters

RIGHT(ProductName, 3) AS Model, -- Last 3 letters

CHARINDEX('iPhone', ProductName) AS Pos, -- Search position

REPLACE(ProductName, ' ', '-') AS SEOUrl, -- Replace space with dash

SUBSTRING(ProductName, 1, 10) AS ShortName -- First 10 chars

FROM Products;

/*

5. Mathematical Functions

Definition:

Numbers ko calculate/transform karne ke liye.

Examples:

ROUND(), CEILING(), FLOOR(), POWER(), ABS()

Real-World Table: Prices

*/

CREATE TABLE Prices (

Item VARCHAR(50),

Cost DECIMAL(10,2)

);

INSERT INTO Prices VALUES

('Laptop', 49999.75),

('Phone', 19999.10);

Query:

select*from Prices

SELECT

Item,

ROUND(Cost, 0) AS Rounded,

CEILING(Cost) AS NextInt,

FLOOR(Cost) AS PreviousInt,

POWER(Cost, 2) AS SquareCost,

ABS(Cost - 30000) AS Difference

FROM Prices;

/*

6. System Functions

Definition:

SQL Server ke system values, NULLs ya identity ko handle karte hain.

Examples:

ISNULL(), COALESCE(), @@IDENTITY, SCOPE_IDENTITY()

Real-World Table: Feedback

*/

CREATE TABLE Feedback (

FeedbackID INT,

Remarks VARCHAR(200)

);

INSERT INTO Feedback VALUES

(1, NULL),

(2, 'Very Good!');

Query:

select*from Feedback

SELECT

FeedbackID,

ISNULL(Remarks, 'No Feedback') AS SafeRemarks,

COALESCE(Remarks, 'Pending') AS FirstNonNull

FROM Feedback;

Insert Identity Example:

INSERT INTO Feedback (Remarks) VALUES ('Excellent');

SELECT SCOPE_IDENTITY() AS LastInsertedID; -- Safer identity

/*

7. Conversion Functions

Definition:

Data type convert karne ke liye use hote hain.

Examples:

CAST(), CONVERT()

Real-World Table: Transactions

*/

CREATE TABLE Transactions (

Amount DECIMAL(10,2)

);

INSERT INTO Transactions VALUES (1234.56);

Query:

select*from Transactions

SELECT

Amount,

CAST(Amount AS INT) AS AmountAsInt,

CONVERT(VARCHAR, Amount, 1) AS AmountAsText

FROM Transactions;

This covers all main function categories in SQL Server with detailed table examples and interview-ready definitions.