SQL Training Practice - Part01

 

1. Create a Sample Database

Step 1: Create the Database

CREATE DATABASE TrainingDB;
GO

USE TrainingDB;
GO

Step 2: Create Tables

 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    HireDate DATE,
    Salary DECIMAL(10, 2),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName NVARCHAR(50)
);

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY IDENTITY(1,1),
    ProjectName NVARCHAR(100),
    Budget DECIMAL(10, 2),
    StartDate DATE,
    EndDate DATE
);

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    HoursWorked INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);
GO

Step 3: Insert Sample Data

 
INSERT INTO Departments (DepartmentName)
VALUES ('HR'), ('Finance'), ('IT'), ('Marketing');

INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES 
('John', 'Doe', 'johndoe@example.com', '2020-01-15', 55000, 1),
('Jane', 'Smith', 'janesmith@example.com', '2019-03-20', 65000, 2),
('Mark', 'Johnson', 'markjohnson@example.com', '2021-05-10', 75000, 3),
('Emily', 'Davis', 'emilydavis@example.com', '2018-11-02', 70000, 4);

INSERT INTO Projects (ProjectName, Budget, StartDate, EndDate)
VALUES 
('Project A', 100000, '2023-01-01', '2023-12-31'),
('Project B', 150000, '2023-03-01', '2024-02-28'),
('Project C', 200000, '2022-06-01', '2023-05-31');

INSERT INTO EmployeeProjects (EmployeeID, ProjectID, HoursWorked)
VALUES 
(1, 1, 120),
(2, 2, 150),
(3, 3, 200),
(4, 1, 160),
(3, 2, 140);
GO

2. SQL Query Practice

Step 4: Basic SELECT Queries

 
-- Select all employees
SELECT * FROM Employees;

-- Select specific columns
SELECT FirstName, LastName, Email FROM Employees;

-- Select employees with salary greater than 60000
SELECT * FROM Employees WHERE Salary > 60000;

-- Select employees from a specific department (IT)
SELECT * FROM Employees WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'IT');

Step 5: Aggregate Functions

 
-- Count the number of employees
SELECT COUNT(*) AS TotalEmployees FROM Employees;

-- Get the average salary
SELECT AVG(Salary) AS AverageSalary FROM Employees;

-- Get the total budget for all projects
SELECT SUM(Budget) AS TotalBudget FROM Projects;

Step 6: Joins

 
-- Join Employees and Departments
SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID;

-- Join Employees, Departments, and Projects
SELECT E.FirstName, E.LastName, D.DepartmentName, P.ProjectName
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID
JOIN EmployeeProjects EP ON E.EmployeeID = EP.EmployeeID
JOIN Projects P ON EP.ProjectID = P.ProjectID;

Step 7: Subqueries

 
-- Subquery to find employees working on 'Project A'
SELECT * FROM Employees WHERE EmployeeID IN 
(SELECT EmployeeID FROM EmployeeProjects WHERE ProjectID = 
(SELECT ProjectID FROM Projects WHERE ProjectName = 'Project A'));

-- Subquery to find the highest salary
SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);

3. Stored Procedures

Step 8: Creating and Executing Stored Procedures

 
-- Create a stored procedure to get employees by department
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentName NVARCHAR(50)
AS
BEGIN
    SELECT E.FirstName, E.LastName, E.Email, D.DepartmentName
    FROM Employees E
    JOIN Departments D ON E.DepartmentID = D.DepartmentID
    WHERE D.DepartmentName = @DepartmentName;
END;
GO

-- Execute the stored procedure
EXEC GetEmployeesByDepartment @DepartmentName = 'Finance';

4. Functions

Step 9: Creating and Using Functions

 
-- Create a function to calculate yearly salary
CREATE FUNCTION CalculateYearlySalary (@EmployeeID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @YearlySalary DECIMAL(10, 2);
    SELECT @YearlySalary = Salary * 12 FROM Employees WHERE EmployeeID = @EmployeeID;
    RETURN @YearlySalary;
END;
GO

-- Use the function
SELECT FirstName, LastName, dbo.CalculateYearlySalary(EmployeeID) AS YearlySalary FROM Employees;

5. Error Handling

Step 10: Implementing Error Handling

 
-- Example of TRY...CATCH for error handling
BEGIN TRY
    -- Intentionally cause a division by zero error
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    -- Handle the error
    SELECT ERROR_MESSAGE() AS ErrorMessage, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState;
END CATCH;

6. Transactions

Step 11: Using Transactions

 
-- Example of a transaction with commit and rollback
BEGIN TRANSACTION;

BEGIN TRY
    -- Update employee salary
    UPDATE Employees SET Salary = Salary + 5000 WHERE EmployeeID = 1;

    -- Insert a new project
    INSERT INTO Projects (ProjectName, Budget, StartDate, EndDate)
    VALUES ('Project D', 120000, '2024-01-01', '2024-12-31');

    -- Commit the transaction
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Rollback if there's an error
    ROLLBACK TRANSACTION;

    -- Show error message
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;


7. Common Table Expressions (CTE)
Step 12: Using CTEs
 
-- Simple CTE to get employees with salaries greater than the average salary
WITH HighEarners AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > (SELECT AVG(Salary) FROM Employees)
)
SELECT * FROM HighEarners;

-- Recursive CTE to generate a sequence of numbers from 1 to 10
WITH NumberSequence AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM NumberSequence
    WHERE Number < 10
)
SELECT * FROM NumberSequence;
GO
8. Temporary Tables
Step 13: Working with Temporary Tables
 
-- Create a temporary table to store top-earning employees
CREATE TABLE #TopEarners (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Insert data into the temporary table
INSERT INTO #TopEarners (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 60000;

-- Select data from the temporary table
SELECT * FROM #TopEarners;

-- Drop the temporary table
DROP TABLE #TopEarners;
GO
9. Views
Step 14: Creating and Using Views
 
-- Create a view to display employee details along with their department names
CREATE VIEW EmployeeDepartmentView AS
SELECT 
    E.EmployeeID,
    E.FirstName,
    E.LastName,
    E.Email,
    D.DepartmentName,
    E.Salary
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID;
GO

-- Query the view
SELECT * FROM EmployeeDepartmentView;

-- Create a view to display project details and associated employee information
CREATE VIEW ProjectEmployeeView AS
SELECT 
    P.ProjectName,
    P.Budget,
    E.FirstName,
    E.LastName,
    EP.HoursWorked
FROM 
    Projects P
JOIN 
    EmployeeProjects EP ON P.ProjectID = EP.ProjectID
JOIN 
    Employees E ON EP.EmployeeID = E.EmployeeID;
GO

-- Query the view
SELECT * FROM ProjectEmployeeView;
10. UNION and UNION ALL
Step 15: Using UNION and UNION ALL
 
-- Use UNION to combine results of employees from HR and IT departments
SELECT FirstName, LastName, Email, 'HR' AS Department
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR')

UNION

SELECT FirstName, LastName, Email, 'IT' AS Department
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'IT');
GO

-- Use UNION ALL to combine results and include duplicates
SELECT FirstName, LastName, Email, 'HR' AS Department
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR')

UNION ALL

SELECT FirstName, LastName, Email, 'HR' AS Department
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');
GO


Thanks you!