This article provides a step-by-step guide to creating and managing a database called CompanyDB with specific tasks that showcase practical SQL queries. Whether you're learning SQL or brushing up on database concepts, this example provides insights into creating tables, inserting data, performing queries, and using advanced SQL features.
Question SQL Practice :
Question :Create a database named CompanyDB and add the following tables:
Departments
DepartmentID - INT, Primary Key
DepartmentName - VARCHAR(100)
Employees
EmployeeID - INT, Primary Key
FirstName - VARCHAR(50)
LastName - VARCHAR(50)
Age - INT
Salary - DECIMAL(10, 2)
DepartmentID - INT, Foreign Key referencing Departments(DepartmentID)
JoiningDate - DATE
Insert at least 3 records into the Departments table and at least 10 records into the Employees table with different details.
Tasks :
Write a query to display all employees along with their department names.
Write a query to calculate the average salary of employees in each department.
Write a query to display all employees who have joined in the last 2 years.
Write a query to find the department with the highest number of employees.
Update the salary of employees whose age is greater than 40 by increasing it by 10%.
Write a query to delete all employees from the department named 'Sales'.
Create a view named HighEarnerEmployees that displays the EmployeeID, FirstName, LastName, and Salary of employees earning more than 100000.
Write a query using a common table expression (CTE) to find employees who report to the same department and have salaries greater than the average salary of that department.
Step 1: Create the CompanyDB Database
CREATE DATABASE CompanyDB;
USE CompanyDB;
Step 2: Create the Departments and Employees Tables
-- Creating Departments Table
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
-- Creating Employees Table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Salary DECIMAL(10, 2) NOT NULL,
DepartmentID INT,
JoiningDate DATE,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
Step 3: Insert Data into the Tables
Insert at least three records into the Departments table and ten into the Employees table.
-- Insert records into Departments
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Sales');
-- Insert records into Employees
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Salary, DepartmentID, JoiningDate)
VALUES
(1, 'John', 'Doe', 25, 55000, 1, '2023-03-15'),
(2, 'Jane', 'Smith', 30, 60000, 2, '2021-06-20'),
(3, 'Alice', 'Johnson', 45, 75000, 3, '2020-08-12'),
(4, 'Bob', 'Brown', 50, 85000, 2, '2019-11-30'),
(5, 'Charlie', 'Davis', 29, 58000, 1, '2022-04-18'),
(6, 'David', 'Wilson', 38, 62000, 3, '2023-02-10'),
(7, 'Ella', 'Martinez', 42, 98000, 2, '2021-09-01'),
(8, 'Frank', 'Garcia', 34, 72000, 1, '2022-12-05'),
(9, 'Grace', 'Lee', 28, 50000, 3, '2020-01-15'),
(10, 'Hank', 'Morris', 33, 56000, 1, '2023-07-07');
Step 4: Perform Queries
Display All Employees with Department Names
SELECT E.EmployeeID, E.FirstName, E.LastName, E.Age, E.Salary, D.DepartmentName, E.JoiningDate
FROM Employees E
JOIN Departments D
ON E.DepartmentID = D.DepartmentID;
Calculate the Average Salary in Each Department
SELECT D.DepartmentName, AVG(E.Salary) AS AverageSalary
FROM Employees E
JOIN Departments D
ON E.DepartmentID = D.DepartmentID
GROUP BY D.DepartmentName;
Display Employees Who Joined in the Last 2 Years
SELECT EmployeeID, FirstName, LastName, JoiningDate
FROM Employees
WHERE JoiningDate >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR);
Find the Department with the Highest Number of Employees
SELECT D.DepartmentName, COUNT(E.EmployeeID) AS EmployeeCount
FROM Employees E
JOIN Departments D
ON E.DepartmentID = D.DepartmentID
GROUP BY D.DepartmentName
ORDER BY EmployeeCount DESC
LIMIT 1;
Increase Salary of Employees Older Than 40 by 10%
UPDATE Employees
SET Salary = Salary * 1.10
WHERE Age > 40;
Delete All Employees from the Sales Department
DELETE E
FROM Employees E
JOIN Departments D
ON E.DepartmentID = D.DepartmentID
WHERE D.DepartmentName = 'Sales';
Create a View for High-Earning Employees
CREATE VIEW HighEarnerEmployees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 100000;
Find Employees with Salaries Above Department Average Using a CTE
WITH DepartmentAverage AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT E.EmployeeID, E.FirstName, E.LastName, E.Salary, D.DepartmentName
FROM Employees E
JOIN DepartmentAverage DA
ON E.DepartmentID = DA.DepartmentID
JOIN Departments D
ON E.DepartmentID = D.DepartmentID
WHERE E.Salary > DA.AvgSalary;
Conclusion
This guide walks you through creating and managing a functional database with meaningful queries. Each task illustrates how to manipulate and analyze data, ensuring a comprehensive understanding of SQL's capabilities. You can use these examples to practice or expand on similar projects in your SQL learning journey.
Comments