Below is a comprehensive SQL revision guide with practical code snippets covering:

• Basic SELECT & filtering
• Joins (inner, outer, self, cross)
• Aggregation & GROUP BY
• Subqueries (correlated & non-correlated)
• CTEs (Common Table Expressions)
• Window functions
• CASE logic
• NULL handling
• Constraints & schema design
• Indexing
• Stored procedures
• Functions
• Transactions
• Error handling
• Loops (WHILE, cursors)
• Temporary tables
• Table variables
• Dynamic SQL
• Performance considerations
• Interview-style scenario questions

All examples use T-SQL (SQL Server syntax) since that’s most common in enterprise environments (including Microsoft-heavy stacks like the one you work in). If your interview is MySQL or PostgreSQL, I can tailor it.

---

1️⃣ Database Setup for Practice

Let’s assume an example database for an Asset Management System (relevant to your experience).

CREATE TABLE Stations (
    StationID INT PRIMARY KEY,
    StationName VARCHAR(100),
    Location VARCHAR(100)
);

CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY,
    StationID INT,
    VehicleType VARCHAR(50),
    PurchaseDate DATE,
    Status VARCHAR(20),
    FOREIGN KEY (StationID) REFERENCES Stations(StationID)
);

CREATE TABLE Equipment (
    EquipmentID INT PRIMARY KEY,
    VehicleID INT,
    EquipmentName VARCHAR(100),
    PurchasePrice DECIMAL(10,2),
    LastInspectionDate DATE,
    FOREIGN KEY (VehicleID) REFERENCES Vehicles(VehicleID)
);

2️⃣ Basic SELECT Statements

Simple SELECT

SELECT * FROM Stations;

Selecting Specific Columns

SELECT StationName, Location
FROM Stations;

Filtering with WHERE

SELECT *
FROM Vehicles
WHERE Status = 'Active';

Multiple Conditions

SELECT *
FROM Vehicles
WHERE Status = 'Active'
AND VehicleType = 'Fire Engine';

BETWEEN

SELECT *
FROM Equipment
WHERE PurchasePrice BETWEEN 1000 AND 5000;

IN

SELECT *
FROM Vehicles
WHERE Status IN ('Active', 'Maintenance');

LIKE

SELECT *
FROM Stations
WHERE StationName LIKE 'North%';

3️⃣ Sorting Results

SELECT *
FROM Vehicles
ORDER BY PurchaseDate DESC;

Multiple columns:

ORDER BY Status ASC, PurchaseDate DESC;

4️⃣ Aggregate Functions

COUNT

SELECT COUNT(*) AS TotalVehicles
FROM Vehicles;

SUM

SELECT SUM(PurchasePrice) AS TotalEquipmentCost
FROM Equipment;

AVG, MIN, MAX

SELECT 
    AVG(PurchasePrice) AS AvgPrice,
    MIN(PurchasePrice) AS MinPrice,
    MAX(PurchasePrice) AS MaxPrice
FROM Equipment;

5️⃣ GROUP BY & HAVING

Total vehicles per station

SELECT StationID, COUNT(*) AS VehicleCount
FROM Vehicles
GROUP BY StationID;

Using HAVING

SELECT StationID, COUNT(*) AS VehicleCount
FROM Vehicles
GROUP BY StationID
HAVING COUNT(*) > 5;

6️⃣ Joins

INNER JOIN

SELECT s.StationName, v.VehicleType
FROM Stations s
INNER JOIN Vehicles v
ON s.StationID = v.StationID;

LEFT JOIN

SELECT s.StationName, v.VehicleType
FROM Stations s
LEFT JOIN Vehicles v
ON s.StationID = v.StationID;

RIGHT JOIN

SELECT s.StationName, v.VehicleType
FROM Stations s
RIGHT JOIN Vehicles v
ON s.StationID = v.StationID;

FULL OUTER JOIN

SELECT s.StationName, v.VehicleType
FROM Stations s
FULL OUTER JOIN Vehicles v
ON s.StationID = v.StationID;

SELF JOIN

Find vehicles at the same station:

SELECT v1.VehicleID, v2.VehicleID
FROM Vehicles v1
JOIN Vehicles v2
ON v1.StationID = v2.StationID
AND v1.VehicleID <> v2.VehicleID;

7️⃣ Subqueries

Non-Correlated Subquery

SELECT *
FROM Equipment
WHERE PurchasePrice > (
    SELECT AVG(PurchasePrice)
    FROM Equipment
);

Correlated Subquery

SELECT e.*
FROM Equipment e
WHERE PurchasePrice > (
    SELECT AVG(PurchasePrice)
    FROM Equipment
    WHERE VehicleID = e.VehicleID
);

8️⃣ Common Table Expressions (CTE)

WITH VehicleCountCTE AS (
    SELECT StationID, COUNT(*) AS VehicleCount
    FROM Vehicles
    GROUP BY StationID
)
SELECT *
FROM VehicleCountCTE
WHERE VehicleCount > 3;

Recursive CTE Example

WITH Numbers AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1
    FROM Numbers
    WHERE Num < 10
)
SELECT * FROM Numbers;

9️⃣ Window Functions

ROW_NUMBER

SELECT 
    VehicleID,
    ROW_NUMBER() OVER (ORDER BY PurchaseDate DESC) AS RowNum
FROM Vehicles;

RANK

SELECT 
    VehicleID,
    RANK() OVER (ORDER BY PurchaseDate DESC) AS RankNum
FROM Vehicles;

Partition By

SELECT 
    VehicleID,
    StationID,
    ROW_NUMBER() OVER (PARTITION BY StationID ORDER BY PurchaseDate DESC) AS StationRank
FROM Vehicles;

🔟 CASE Statements

SELECT 
    VehicleID,
    CASE 
        WHEN Status = 'Active' THEN 'Operational'
        WHEN Status = 'Maintenance' THEN 'Under Repair'
        ELSE 'Unknown'
    END AS StatusDescription
FROM Vehicles;

1️⃣1️⃣ Handling NULL

SELECT ISNULL(Status, 'Unknown')
FROM Vehicles;

SELECT COALESCE(Status, 'Unknown')
FROM Vehicles;

1️⃣2️⃣ Indexes

CREATE INDEX IX_Vehicles_StationID
ON Vehicles(StationID);

Composite index:

CREATE INDEX IX_Vehicles_Status_Station
ON Vehicles(Status, StationID);

1️⃣3️⃣ Stored Procedures

CREATE PROCEDURE GetVehiclesByStation
    @StationID INT
AS
BEGIN
    SELECT *
    FROM Vehicles
    WHERE StationID = @StationID;
END;

Execute:

EXEC GetVehiclesByStation @StationID = 1;

1️⃣4️⃣ Functions

Scalar Function

CREATE FUNCTION dbo.CalculateTax (@Price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * 0.2;
END;

Table-Valued Function

CREATE FUNCTION dbo.GetActiveVehicles()
RETURNS TABLE
AS
RETURN (
    SELECT *
    FROM Vehicles
    WHERE Status = 'Active'
);

1️⃣5️⃣ Transactions

BEGIN TRANSACTION;

UPDATE Vehicles
SET Status = 'Maintenance'
WHERE VehicleID = 1;

IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

1️⃣6️⃣ Error Handling (TRY/CATCH)

BEGIN TRY
    INSERT INTO Stations VALUES (1, 'North Station', 'North City');
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

1️⃣7️⃣ WHILE Loop (SQL “For Loop” Equivalent)

SQL Server doesn’t have traditional FOR, but you can simulate.

DECLARE @Counter INT = 1;

WHILE @Counter <= 10
BEGIN
    PRINT @Counter;
    SET @Counter = @Counter + 1;
END;

1️⃣8️⃣ Cursor Example

DECLARE @VehicleID INT;

DECLARE VehicleCursor CURSOR FOR
SELECT VehicleID FROM Vehicles;

OPEN VehicleCursor;

FETCH NEXT FROM VehicleCursor INTO @VehicleID;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @VehicleID;
    FETCH NEXT FROM VehicleCursor INTO @VehicleID;
END;

CLOSE VehicleCursor;
DEALLOCATE VehicleCursor;

⚠️ In interviews, mention:

“Cursors are usually avoided for performance reasons. Set-based operations are preferred.”

1️⃣9️⃣ Temporary Tables

CREATE TABLE #TempVehicles (
    VehicleID INT,
    Status VARCHAR(20)
);

INSERT INTO #TempVehicles
SELECT VehicleID, Status FROM Vehicles;

SELECT * FROM #TempVehicles;

2️⃣0️⃣ Table Variables

DECLARE @TempTable TABLE (
    VehicleID INT,
    Status VARCHAR(20)
);

INSERT INTO @TempTable
SELECT VehicleID, Status FROM Vehicles;

SELECT * FROM @TempTable;

2️⃣1️⃣ Dynamic SQL

DECLARE @SQL NVARCHAR(MAX);
DECLARE @StationID INT = 1;

SET @SQL = 'SELECT * FROM Vehicles WHERE StationID = ' + CAST(@StationID AS NVARCHAR);

EXEC sp_executesql @SQL;

Safer version:

EXEC sp_executesql 
    N'SELECT * FROM Vehicles WHERE StationID = @StationID',
    N'@StationID INT',
    @StationID = 1;

2️⃣2️⃣ Performance Concepts Interviewers Love

• Avoid SELECT *
• Use proper indexing
• Use set-based logic instead of cursors
• Filter early
• Understand execution plans
• Use EXISTS instead of IN when appropriate

Example:

SELECT *
FROM Stations s
WHERE EXISTS (
    SELECT 1
    FROM Vehicles v
    WHERE v.StationID = s.StationID
);

2️⃣3️⃣ Advanced Interview Questions

Find Duplicate Records

SELECT StationID, COUNT(*)
FROM Stations
GROUP BY StationID
HAVING COUNT(*) > 1;

Delete Duplicates (Keep One)

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY StationID ORDER BY StationID) AS RowNum
    FROM Stations
)
DELETE FROM CTE
WHERE RowNum > 1;

2️⃣4️⃣ Ranking & Top N per Group

WITH RankedVehicles AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY StationID ORDER BY PurchaseDate DESC) AS RowNum
    FROM Vehicles
)
SELECT *
FROM RankedVehicles
WHERE RowNum = 1;

2️⃣5️⃣ Pivot Example

SELECT *
FROM (
    SELECT StationID, Status
    FROM Vehicles
) AS SourceTable
PIVOT (
    COUNT(Status)
    FOR Status IN ([Active], [Maintenance])
) AS PivotTable;

2️⃣6️⃣ Interview Strategy Advice (Very Important)

During a technical test:

1. Write clean SQL
2. Alias tables properly
3. Format code clearly
4. Explain your thinking
5. Prefer CTEs for readability
6. Use window functions instead of subqueries where appropriate

If stuck:

• Break problem into smaller queries
• Test intermediate results
• Use temporary tables