SQL Server

Basic SQL Query 

USE Britain

SELECT * FROM DBO.Britain

***********************************************************************************

SQL Query with filter condition (Where)

(Two filters)

SELECT * FROM DBO.Britain WHERE City = 'LONDON' AND State = 'ENGLAND'

(Between Condition)

SELECT * FROM DBO.Britain WHERE Salary between 12000 and 30000

SELECT COUNT(Salary) FROM DBO.Britain WHERE Salary between 12000 and 30000

***********************************************************************************

SQL Query with count

***********************************************************************************

SELECT COUNT(City) FROM dbo.Britain  

SELECT COUNT(City) FROM dbo.Britain  WHERE City = 'LONDON' AND State = 'ENGLAND'

SQL Query Backup table

***********************************************************************************

SELECT * INTO TEMPBRITAIN FROM DBO.Britain

Update table with Case...end based on another table

***********************************************************************************

UPDATE Flowslake

SET LOAN_SUBTYPE = 

    CASE

        WHEN LOANTYPES = 'Secured Loan' THEN 'SL'

        WHEN LOANTYPES = 'Unsecured Loan' THEN 'UL'

        ELSE LOAN_SUBTYPE  -- If not 'Secured Loan' or 'Unsecured Loan, leave it as is

    END;

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

-- Declare and initialize the variable

DECLARE @Loantypes NVARCHAR(100)

-- Initialize the variable with 'Unsecured Loan'

SET @Loantypes = 'LOANTYPES'

-- Retrieve the value from the table based on the condition

SELECT @Loantypes = LOANTYPES FROM Flowslake

-- Check the value of the variable and update the table accordingly

IF @Loantypes = 'Secured Loan'

BEGIN

    UPDATE Flowslake SET LOAN_SUBTYPE = 'SL' WHERE LOANTYPES = 'Secured Loan'

END

ELSE

BEGIN

    UPDATE Flowslake SET LOAN_SUBTYPE = 'UL' WHERE LOANTYPES = 'Unsecured Loan'

END

Update table with Joints

***********************************************************************************

UPDATE TBLN

SET TBLN.[01-Nov] = TBLB.Status

FROM tblNovember_2023 AS TBLN

INNER JOIN Book7 AS TBLB ON TBLN.AHA_ID = TBLB.AHA_ID

Insert the data in new table 

***********************************************************************************

  --Insert data in new table

  select * into BackUp_tblStudent from tblStudent

Insert the data in already existing table which has data

***********************************************************************************

  --Insert in already existing table which already have data

  insert into tblStudent select * from Dataset_P

Vlookup Like connection in SQL

***********************************************************************************

SELECT Sht3.*, Sht1.[Name of Student]

FROM Sheet3 AS Sht3

LEFT JOIN Sheet1 AS Sht1 ON Sht3.[Name of Student] = Sht1.[Name of Student]

WHERE Sht1.[Name of Student] IS NULL

ORDER BY Sht1.[Name of Student];

SELECT 

    Sht3.*, 

    Sht1.[Name of Student] AS [Sheet1_Name_of_Student] -- Rename this column uniquely

INTO #JointInserted

FROM Sheet3 AS Sht3

LEFT JOIN Sheet1 AS Sht1 ON Sht3.[Name of Student] = Sht1.[Name of Student]

WHERE Sht1.[Name of Student] IS NULL

ORDER BY Sht1.[Name of Student];

select * from #JointInserted

Delete the duplicate Data with Group by Max

***********************************************************************************

Select SHT2.*, SHT1.BRANCH into #TempAdded2

from Sheet2 as SHT2

INNER JOIN Sheet1 as SHT1

ON SHT2.STATE = SHT1.STATE

order by STATE

select * from #TempAdded2 order by ID

select distinct State,branch,ID from #TempAdded2 order by ID

select State, branch, count(*) from #TempAdded2 

group by State, branch 

having count (*)>1

Delete from #TempAdded2 where ID not in ( select Max(ID) from #TempAdded2 group by State, branch)

Delete the duplicate Data with CTE and ROW Function

***********************************************************************************

With TempAdded_CTE As

(

select *, ROW_NUMBER() over (partition by Branch order by Branch) as RowNumber

from #TempAdded

)

select * from TempAdded_CTE

Delete from TempAdded_CTE where RowNumber >1

Find all the store procedures associated to particular table

**************************************************************************************************

SELECT 

    OBJECT_NAME(object_id) AS ProcedureName

FROM 

    sys.sql_modules

WHERE 

    definition LIKE '%STUDENT_MASTER_TABLE%'

AND 

    OBJECTPROPERTY(object_id, 'IsProcedure') = 1;

Select/ Find the version of sql server 

**************************************************************************************************

SELECT @@VERSION;

Check SQL Server Instance Name 

**************************************************************************************************

SELECT @@SERVERNAME AS ServerName;

Check Current Database Info 

**************************************************************************************************

SELECT 

    DB_NAME() AS CurrentDatabase,

    DATABASEPROPERTYEX(DB_NAME(), 'Status') AS Status,

    DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS RecoveryModel;

List All Databases on the Server

**************************************************************************************************

SELECT 

    name AS DatabaseName,

    state_desc AS Status,

    recovery_model_desc AS RecoveryModel

FROM 

    sys.databases;

List All Tables in a Specific Database

**************************************************************************************************

SELECT 

    TABLE_SCHEMA, 

    TABLE_NAME 

FROM 

    INFORMATION_SCHEMA.TABLES

WHERE 

    TABLE_TYPE = 'BASE TABLE';

Find SQL Server Login Info

**************************************************************************************************

SELECT 

    name AS LoginName,

    type_desc AS LoginType,

    create_date,

    modify_date,

    is_disabled

FROM 

    sys.sql_logins;

Find SQL Randomize

**************************************************************************************************

CREATE TABLE salesTable

(

    SalesPersonName VARCHAR(100),

    [Month] VARCHAR(20),

    ProductName VARCHAR(50),

    Qty INT,

    City VARCHAR(50),

    [Year] INT,

    Rate INT

);

WHILE 1 = 1

BEGIN

    DECLARE @RowCount INT;

    SET @RowCount = (ABS(CHECKSUM(NEWID())) % 7) + 1; -- 1 to 7 rows

    ;WITH Numbers AS

    (

        SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n

        FROM sys.objects

    )

    INSERT INTO salesTable

    (

        SalesPersonName,

        [Month],

        ProductName,

        Qty,

        City,

        [Year],

        Rate

    )

    SELECT

        -- Random Sales Person

        (SELECT TOP 1 Name FROM

            (VALUES

                ('Asfiya Pathan'),

                ('Priyanshu Somani'),

                ('Rajni.Waghmare'),

                ('Akshay Vibhute'),

                ('Abhishek Jadhav'),

                ('Krushna Khedkar'),

                ('Ujvala Shamrav Shete'),

                ('Rohini Deshmukh')

            ) AS Names(Name)

         ORDER BY NEWID()),

        -- Random Month

        DATENAME(MONTH, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, 0)),

        -- Random Product

        (SELECT TOP 1 Product FROM

            (VALUES

                ('Laptop'),

                ('Refriderator'),

                ('Washing Machine'),

                ('Television'),

                ('Microwave Owen'),

                ('Steam Iron')

            ) AS Products(Product)

         ORDER BY NEWID()),

        -- Random Qty (1–10)

        (ABS(CHECKSUM(NEWID())) % 10) + 1,

        -- Random City

        (SELECT TOP 1 City FROM

            (VALUES

                ('Mumbai'),

                ('Delhi'),

                ('Bengalore'),

                ('Pune'),

                ('Chennai'),

                ('Hyderabad')

            ) AS Cities(City)

         ORDER BY NEWID()),

        -- Random Year (2018–2024)

        2018 + (ABS(CHECKSUM(NEWID())) % 7),

        -- Random Rate

        CASE 

            WHEN (ABS(CHECKSUM(NEWID())) % 6) = 0 THEN 55000

            WHEN (ABS(CHECKSUM(NEWID())) % 6) = 1 THEN 25000

            WHEN (ABS(CHECKSUM(NEWID())) % 6) = 2 THEN 30000

            WHEN (ABS(CHECKSUM(NEWID())) % 6) = 3 THEN 40000

            WHEN (ABS(CHECKSUM(NEWID())) % 6) = 4 THEN 15000

            ELSE 2000

        END

    FROM Numbers;

    -- Wait for 2 seconds

    WAITFOR DELAY '00:00:02';

END

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

CREATE OR ALTER PROCEDURE GenerateSalesData

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @RowCount INT;

    SET @RowCount = (ABS(CHECKSUM(NEWID())) % 7) + 1;

    ;WITH Numbers AS

    (

        SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n

        FROM sys.objects

    )

    INSERT INTO salesTable

    (

        SalesPersonName, [Month], ProductName, Qty, City, [Year], Rate

    )

    SELECT

        (SELECT TOP 1 Name FROM (VALUES

            ('Asfiya Pathan'), ('Priyanshu Somani'), ('Rajni.Waghmare'),

            ('Akshay Vibhute'), ('Abhishek Jadhav'), ('Krushna Khedkar'),

            ('Ujvala Shamrav Shete'), ('Rohini Deshmukh')

        ) AS N(Name) ORDER BY NEWID()),

        DATENAME(MONTH, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, 0)),

        (SELECT TOP 1 Product FROM (VALUES

            ('Laptop'), ('Refriderator'), ('Washing Machine'),

            ('Television'), ('Microwave Owen'), ('Steam Iron')

        ) AS P(Product) ORDER BY NEWID()),

        (ABS(CHECKSUM(NEWID())) % 10) + 1,

        (SELECT TOP 1 City FROM (VALUES

            ('Mumbai'), ('Delhi'), ('Bengalore'),

            ('Pune'), ('Chennai'), ('Hyderabad')

        ) AS C(City) ORDER BY NEWID()),

        2018 + (ABS(CHECKSUM(NEWID())) % 7),

        CASE ABS(CHECKSUM(NEWID())) % 6

            WHEN 0 THEN 55000

            WHEN 1 THEN 25000

            WHEN 2 THEN 30000

            WHEN 3 THEN 40000

            WHEN 4 THEN 15000

            ELSE 2000

        END

    FROM Numbers;

END

SET NOCOUNT ON;

SET QUERY_GOVERNOR_COST_LIMIT 0;

WHILE 1 = 1

BEGIN

    EXEC GenerateSalesData;

    WAITFOR DELAY '00:00:02';

END

CMD Loop

**************************************************************************************************

Open CMD.

Type color 0a and press Enter.

Run the Infinite Tree command from above: :loop & tree C:\ /f & goto loop

Join Query

**************************************************************************************************

Use ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

 Select CENTURA_LONGMONT_UNITED_HOSPITAL30032026.*, ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL.Patient_ID AS TPATIETID

Into #Joinedtable16000

From CENTURA_LONGMONT_UNITED_HOSPITAL30032026

Left Join ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

on ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL.Patient_ID = CENTURA_LONGMONT_UNITED_HOSPITAL30032026.patient_ID

where ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL.Patient_ID is Null

 

Select * From #Joinedtable16000

Alter Table #Joinedtable16000

DROP Column TPATIETID

 

Insert Into ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select * From #Joinedtable16000

Select * From ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL order by Reference_Add_Date DESC

Autdit MIS

**************************************************************************************************

use ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select reference_Add_Date, Discharge_Summary From ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select * From ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Where Reference_Add_Date Between '2026-03-20' And '2026-03-30'

Above Solution

**************************************************************************************************

⚡ Better Solution: Use Dynamic SQL + Date Parameter

You can make the table name dynamic based on date.

DECLARE @Date VARCHAR(8)

DECLARE @TableName VARCHAR(100)

DECLARE @SQL NVARCHAR(MAX)

-- Set date (DDMMYYYY format)

SET @Date = FORMAT(GETDATE(), 'ddMMyyyy')

-- Or manually:

-- SET @Date = '30032026'

SET @TableName = 'CENTURA_LONGMONT_UNITED_HOSPITAL' + @Date

SET @SQL = '

SELECT A.*, B.Patient_ID AS TPATIETID

INTO #Temp

FROM ' + @TableName + ' A

LEFT JOIN ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL B

ON B.Patient_ID = A.Patient_ID

WHERE B.Patient_ID IS NULL;

ALTER TABLE #Temp DROP COLUMN TPATIETID;

INSERT INTO ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

SELECT * FROM #Temp;

SELECT * 

FROM ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

ORDER BY Reference_Add_Date DESC;

'

EXEC sp_executesql @SQL

🚀 Benefits

✅ No need to rewrite query daily

Just change:

SET @Date = '31032026'



LEFT JOIN ... WHERE NULL

You can also use:
WHERE NOT EXISTS (
    SELECT 1 
    FROM ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL B
    WHERE B.Patient_ID = A.Patient_ID
)




Dynamic Query
**************************************************************************************************

Great 👍 let’s start with simple and clear examples so your students can understand the concept of Dynamic SQL step by step.

🔹 What is Dynamic SQL?
Dynamic SQL means building a SQL query as a string and executing it at runtime.
Instead of writing:
SELECT * FROM Students WHERE City = 'Pune'

We create it dynamically:
DECLARE @Query NVARCHAR(MAX)

SET @Query = 'SELECT * FROM Students WHERE City = ''Pune'''

EXEC(@Query)


🔹 1. Basic Dynamic Query (Hardcoded Value)
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM Employees'

EXEC(@SQL)

👉 Simple example to show how EXEC works.

🔹 2. Dynamic WHERE Condition (User Input)
DECLARE @City NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @City = 'Mumbai'

SET @SQL = 'SELECT * FROM Employees WHERE City = ''' + @City + ''''

EXEC(@SQL)

👉 Teach:


String concatenation


Quotes handling ('')



🔹 3. Dynamic Column Selection
DECLARE @ColumnName NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @ColumnName = 'Salary'

SET @SQL = 'SELECT ' + @ColumnName + ' FROM Employees'

EXEC(@SQL)

👉 Useful when user selects column from UI.

🔹 4. Dynamic Table Name
DECLARE @TableName NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @TableName = 'Employees'

SET @SQL = 'SELECT * FROM ' + @TableName

EXEC(@SQL)

👉 Explain:


Useful in multi-table systems



🔹 5. Dynamic ORDER BY
DECLARE @SortColumn NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @SortColumn = 'Salary'

SET @SQL = 'SELECT * FROM Employees ORDER BY ' + @SortColumn

EXEC(@SQL)


🔹 6. Using sp_executesql (BEST PRACTICE ✅)
DECLARE @City NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @City = 'Pune'

SET @SQL = 'SELECT * FROM Employees WHERE City = @City'

EXEC sp_executesql 
    @SQL,
    N'@City NVARCHAR(50)',
    @City

👉 Teach this clearly:


Prevents SQL Injection


Better performance



🔹 7. Dynamic Query with Multiple Filters
DECLARE @City NVARCHAR(50) = 'Pune'
DECLARE @Dept NVARCHAR(50) = 'IT'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM Employees WHERE 1=1'

IF @City IS NOT NULL
    SET @SQL = @SQL + ' AND City = ''' + @City + ''''

IF @Dept IS NOT NULL
    SET @SQL = @SQL + ' AND Department = ''' + @Dept + ''''

EXEC(@SQL)

👉 Important concept:


WHERE 1=1 trick for dynamic conditions



🔹 Teaching Tips for Your Class 🎯
Start in this order:


Static query


Simple dynamic (EXEC)


Concatenation


Problems (quotes, injection)


Then introduce sp_executesql



If you want next level 🚀

I can create:


Real-world stored procedure example


Student practice questions


Dynamic pivot example (very useful in interviews)


Just tell me 👍
create dynamic queries bases on excel file I iuploaed
Got it 👍 — I couldn’t reliably read your .xls file in this environment, but no problem. I’ll still create realistic dynamic SQL examples based on a typical Excel structure, and you can easily map them to your file.

🎯 Assume your Excel table looks like this (very common)
Table: StudentsData
StudentID | Name | City | Course | Fees | AdmissionDate


🔥 Dynamic SQL Examples (Based on Excel Data)

🔹 1. Dynamic Filter (Based on Excel Input Cell)
👉 Example: User selects City in Excel → pass to SQL
DECLARE @City NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @City = 'Pune'   -- coming from Excel

SET @SQL = 'SELECT * FROM StudentsData WHERE City = ''' + @City + ''''

EXEC(@SQL)


🔹 2. Dynamic Multiple Filters (Excel Form Scenario)
👉 Suppose Excel has filters:


City


Course


DECLARE @City NVARCHAR(50) = 'Mumbai'
DECLARE @Course NVARCHAR(50) = 'SQL'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM StudentsData WHERE 1=1'

IF @City IS NOT NULL
    SET @SQL = @SQL + ' AND City = ''' + @City + ''''

IF @Course IS NOT NULL
    SET @SQL = @SQL + ' AND Course = ''' + @Course + ''''

EXEC(@SQL)

👉 Perfect for teaching search forms

🔹 3. Dynamic Column Selection (User selects column in Excel)
👉 Example: User selects "Fees" or "Name"
DECLARE @Column NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @Column = 'Fees'

SET @SQL = 'SELECT ' + @Column + ' FROM StudentsData'

EXEC(@SQL)


🔹 4. Dynamic Sorting (Excel Dropdown → Sort Option)
DECLARE @SortColumn NVARCHAR(50) = 'Fees'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM StudentsData ORDER BY ' + @SortColumn

EXEC(@SQL)


🔹 5. Dynamic Date Filter (Very Practical 🔥)
DECLARE @StartDate DATE = '2024-01-01'
DECLARE @EndDate DATE = '2024-12-31'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
SELECT * 
FROM StudentsData 
WHERE AdmissionDate BETWEEN ''' 
+ CAST(@StartDate AS NVARCHAR) + ''' AND ''' 
+ CAST(@EndDate AS NVARCHAR) + ''''

EXEC(@SQL)


🔹 6. Best Practice Version (sp_executesql ✅)
👉 Teach this as professional way
DECLARE @City NVARCHAR(50) = 'Pune'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM StudentsData WHERE City = @City'

EXEC sp_executesql 
    @SQL,
    N'@City NVARCHAR(50)',
    @City


🔹 7. Dynamic Table (Multiple Excel Sheets)
👉 Suppose:


Sheet1 → Students2023


Sheet2 → Students2024


DECLARE @TableName NVARCHAR(50) = 'Students2024'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM ' + @TableName

EXEC(@SQL)


🎓 Classroom Teaching Idea (Very Powerful 💡)
Give students this task:
👉 “Create a dynamic search query where user can filter by:


City


Course


Fees range”


This combines everything:


WHERE 1=1


Conditions


Concatenation



⚠️ Important Concept to Teach
❌ Bad (SQL Injection risk)
'... WHERE Name = ''' + @Name + ''''

✅ Good
sp_executesql


🚀 If you want next level
I can:


Use your actual Excel file columns (if you re-upload as .xlsx)


Create stored procedure for your exact data


Build real-time Excel + SQL integration example


Give student assignment sheet + answers


Just tell me 👍