1. SQL Server Cursor
Lab Exercise 1: Working with Cursors
Task 1:
Create a cursor that iterates over all rows in the Employees table and prints the FirstName and LastName of each employee.
DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50);
DECLARE EmployeeCursor CURSOR FOR
SELECT FirstName, LastName FROM Employees;
OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor INTO @FirstName, @LastName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + @FirstName + ' ' + @LastName;
FETCH NEXT FROM EmployeeCursor INTO @FirstName, @LastName;
END;
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;
Task 2:
Modify the cursor to update each employee's Salary by increasing it by 5%.
2. SQL Server Sub Queries
Lab Exercise 2: Using Sub Queries
Task 1: Write a query to find the names of employees who earn more than the average salary.
SELECT FirstName, LastName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Task 2: Use a subquery to find the department name for the employee with the highest salary.
3. Dynamic SQL in SQL Server
Lab Exercise 3: Writing and Executing Dynamic SQL
Task 1: Write a dynamic SQL query that selects all columns from a table, where the table name is provided as an input parameter.
DECLARE @TableName NVARCHAR(50) = 'Employees';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM ' + @TableName;
EXEC sp_executesql @SQL;
Task 2: Create a stored procedure that accepts a table name and a column name as parameters and returns all distinct values from the specified column.
4. PIVOT & UNPIVOT in SQL Server
Lab Exercise 4: Using PIVOT and UNPIVOT
Task 1: Create a PIVOT query that shows the total sales per product for each month.
SELECT *
FROM
(
SELECT ProductName, MonthName, SalesAmount
FROM Sales
) AS SourceTable
PIVOT
(
SUM(SalesAmount)
FOR MonthName IN ([January], [February], [March], [April])
) AS PivotTable;
Task 2: Create an UNPIVOT query to convert the pivoted data back to its original format.
5. Triggers in SQL Server
Lab Exercise 5: Creating and Using Triggers
Task 1:
Create a trigger that automatically logs any insert, update, or delete operation performed on the Employees table into an EmployeeAudit table.
CREATE TRIGGER trg_AuditEmployeeChanges
ON Employees
FOR INSERT, UPDATE, DELETE
AS
BEGIN
-- Insert into audit table
INSERT INTO EmployeeAudit (AuditType, EmployeeID, ChangeDate)
SELECT
CASE
WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'Update'
WHEN EXISTS(SELECT * FROM inserted) THEN 'Insert'
ELSE 'Delete'
END,
COALESCE(i.EmployeeID, d.EmployeeID),
GETDATE()
FROM inserted i
FULL OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;
GO
Task 2: Create a trigger that prevents any employee from being deleted if they are associated with an ongoing project.
6. SQL Joins
Lab Exercise 6: Mastering SQL Joins
Task 1:
Write queries using different types of joins (INNER, LEFT, RIGHT, FULL) to retrieve data from the Employees, Departments, and Projects tables.
-- Example of an INNER JOIN
SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;
Task 2: Use a LEFT JOIN to list all employees and their respective project names, even if they are not assigned to any project.
7. SQL Server: Clustered Index
Lab Exercise 7: Creating and Using Clustered Indexes
Task 1:
Create a clustered index on the EmployeeID column in the Employees table.
CREATE CLUSTERED INDEX IDX_EmployeeID ON Employees(EmployeeID);
Task 2: Analyze the performance of a SELECT query with and without the clustered index.
8. SQL Server: Non-Clustered Index
Lab Exercise 8: Creating and Using Non-Clustered Indexes
Task 1:
Create a non-clustered index on the LastName column in the Employees table.
CREATE NONCLUSTERED INDEX IDX_LastName ON Employees(LastName);
Task 2:
Write a query to retrieve employees by LastName and analyze the query execution plan to observe the impact of the non-clustered index.
9. Partition in SQL Server
Lab Exercise 9: Working with Partitioned Tables
Task 1: Create a partitioned table to store sales data by year, where each partition corresponds to a different year.
-- Example to create partition function and scheme
CREATE PARTITION FUNCTION SalesYearPartitionFunction (INT)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021);
CREATE PARTITION SCHEME SalesYearPartitionScheme
AS PARTITION SalesYearPartitionFunction
TO ([Primary], [Secondary], [Tertiary]);
CREATE TABLE SalesData (
SaleID INT PRIMARY KEY,
SaleDate DATE,
SalesAmount DECIMAL(10, 2),
SaleYear AS YEAR(SaleDate) PERSISTED
)
ON SalesYearPartitionScheme (SaleYear);
Task 2: Insert data into the partitioned table and write a query to retrieve sales data for a specific year, observing how partitions are used.
10. Query Plan in SQL Server
Lab Exercise 10: Analyzing Query Plans
Task 1:
Write a complex query involving multiple joins and subqueries, then use the EXPLAIN or Show Execution Plan feature to analyze its query plan.
-- Example query
SELECT E.FirstName, E.LastName, P.ProjectName, SUM(EP.HoursWorked) AS TotalHours
FROM Employees E
JOIN EmployeeProjects EP ON E.EmployeeID = EP.EmployeeID
JOIN Projects P ON EP.ProjectID = P.ProjectID
GROUP BY E.FirstName, E.LastName, P.ProjectName;
Task 2: Identify potential performance bottlenecks and suggest indexing strategies or query optimizations.
11. Profiler in SQL Server
Lab Exercise 11: Using SQL Server Profiler
Task 1: Set up SQL Server Profiler to monitor and capture query performance during peak usage times.
Task 2: Analyze the captured data to identify slow-running queries and any potential deadlocks.
12. Tuning Advisor in SQL Server
Lab Exercise 12: Using Database Engine Tuning Advisor
Task 1: Use the Database Engine Tuning Advisor to analyze a given workload (set of queries) and receive recommendations for indexing and query optimization.
Task 2: Apply the recommended indexing strategies and re-run the workload to compare performance improvements.
Thanks
0 comments:
Post a Comment