T-SQL LABS – ADVENTUREWORKS
(FULL)
🔹 LAB 1: BASIC SELECT & FILTERING
Lab 1.1 – Select all products
Task: Display all products.
✅ Answer
SELECT *
FROM [Link];
Lab 1.2 – WHERE clause
Task: Show products with list price > 1000.
✅ Answer
SELECT Name, ListPrice
FROM [Link]
WHERE ListPrice > 1000;
Lab 1.3 – BETWEEN
Task: Products priced between 500 and 1000.
✅ Answer
SELECT Name, ListPrice
FROM [Link]
WHERE ListPrice BETWEEN 500 AND 1000;
Lab 1.4 – LIKE
Task: Products starting with “Mountain”.
✅ Answer
SELECT Name
FROM [Link]
WHERE Name LIKE 'Mountain%';
🔹 LAB 2: SORTING & LIMITING (ORDER BY / OFFSET)
Lab 2.1 – ORDER BY
Task: Sort products by price descending.
✅ Answer
SELECT Name, ListPrice
FROM [Link]
ORDER BY ListPrice DESC;
Lab 2.2 – OFFSET / FETCH
Task: Show top 10 expensive products.
✅ Answer
SELECT Name, ListPrice
FROM [Link]
ORDER BY ListPrice DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
🔹 LAB 3: AGGREGATE FUNCTIONS
Lab 3.1 – COUNT
Task: Count number of customers.
✅ Answer
SELECT COUNT(*) AS TotalCustomers
FROM [Link];
Lab 3.2 – SUM
Task: Total sales amount.
✅ Answer
SELECT SUM(TotalDue) AS TotalSales
FROM [Link];
Lab 3.3 – GROUP BY
Task: Total sales per customer.
✅ Answer
SELECT CustomerID, SUM(TotalDue) AS TotalSales
FROM [Link]
GROUP BY CustomerID;
Lab 3.4 – HAVING
Task: Customers with sales > 50,000.
✅ Answer
SELECT CustomerID, SUM(TotalDue) AS TotalSales
FROM [Link]
GROUP BY CustomerID
HAVING SUM(TotalDue) > 50000;
🔹 LAB 4: JOINS
Lab 4.1 – INNER JOIN
Task: Customers and their orders.
✅ Answer
SELECT [Link], [Link], [Link]
FROM [Link] c
JOIN [Link] soh
ON [Link] = [Link];
Lab 4.2 – LEFT JOIN
Task: Customers with or without orders.
✅ Answer
SELECT [Link], [Link]
FROM [Link] c
LEFT JOIN [Link] soh
ON [Link] = [Link];
Lab 4.3 – Multiple JOIN
Task: Order → Product → Quantity.
✅ Answer
SELECT [Link], [Link], [Link]
FROM [Link] soh
JOIN [Link] sod
ON [Link] = [Link]
JOIN [Link] p
ON [Link] = [Link];
Lab 4.4 – SELF JOIN
Task: Employees and managers.
✅ Answer
SELECT [Link] AS Employee,
[Link] AS Manager
FROM [Link] e
LEFT JOIN [Link] m
ON [Link](1) = [Link];
🔹 LAB 5: SUBQUERIES
Lab 5.1 – Scalar Subquery
Task: Orders above average value.
✅ Answer
SELECT SalesOrderID, TotalDue
FROM [Link]
WHERE TotalDue >
(SELECT AVG(TotalDue)
FROM [Link]);
Lab 5.2 – IN Subquery
Task: Customers with orders.
✅ Answer
SELECT CustomerID
FROM [Link]
WHERE CustomerID IN
(SELECT CustomerID
FROM [Link]);
🔹 LAB 6: DML (INSERT, UPDATE, DELETE)
Lab 6.1 – INSERT
Task: Insert new department.
✅ Answer
BEGIN TRAN;
INSERT INTO [Link]
(Name, GroupName, ModifiedDate)
VALUES
('Data Engineering', 'IT', GETDATE());
ROLLBACK;
Lab 6.2 – UPDATE
Task: Rename department group.
✅ Answer
BEGIN TRAN;
UPDATE [Link]
SET GroupName = 'Technology'
WHERE Name = 'Data Engineering';
ROLLBACK;
Lab 6.3 – DELETE
Task: Delete test department.
✅ Answer
BEGIN TRAN;
DELETE FROM [Link]
WHERE Name = 'Data Engineering';
ROLLBACK;
🔹 LAB 7: MERGE (UPSERT)
Lab 7.1
Task: Insert or update department.
✅ Answer
MERGE [Link] AS t
USING (SELECT 'AI Research' AS Name, 'IT' AS GroupName) s
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET GroupName = [Link],
ModifiedDate = GETDATE()
WHEN NOT MATCHED THEN
INSERT (Name, GroupName, ModifiedDate)
VALUES ([Link], [Link], GETDATE());
🔹 LAB 8: VIEWS
Lab 8.1
Task: Create sales summary view.
✅ Answer
CREATE VIEW vw_CustomerSales
AS
SELECT CustomerID, SUM(TotalDue) AS TotalSales
FROM [Link]
GROUP BY CustomerID;
🔹 LAB 9: STORED PROCEDURES
Lab 9.1
Task: Procedure to get customer sales.
✅ Answer
CREATE PROCEDURE usp_GetCustomerSales
@CustomerID INT
AS
BEGIN
SELECT SUM(TotalDue) AS TotalSales
FROM [Link]
WHERE CustomerID = @CustomerID;
END;
🔹 LAB 10: TRANSACTIONS & ERROR HANDLING
Lab 10.1
Task: Transaction with TRY/CATCH.
✅ Answer
BEGIN TRY
BEGIN TRAN;
UPDATE [Link]
SET TotalDue = TotalDue * 1.1
WHERE SalesOrderID = 1;
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
🔹 LAB 11: INDEXING
Lab 11.1
Task: Create index on OrderDate.
✅ Answer
CREATE INDEX IX_SalesOrderHeader_OrderDate
ON [Link](OrderDate);
🔹 LAB 12: REAL-WORLD CHALLENGE
Lab 12.1
Task: Top 5 customers by sales.
✅ Answer
SELECT CustomerID,
SUM(TotalDue) AS TotalSales
FROM [Link]
GROUP BY CustomerID
ORDER BY TotalSales DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;