02_Practice_Questions.sql

/*
1. Produce a report showing every employee in the company. 
*/

SELECT *
FROM Employees;

/*
2. Show only each employee's first and last names.
*/

SELECT
	FirstName,
	LastName
FROM Employees;

/* 
3. Display the names and salaries of employees. 
*/

SELECT
	FirstName,
	LastName,
	Salary
FROM Employees;

/*
4. Find every employee who works in Lagos. 
*/

SELECT 
	FirstName,
	LastName,
	City
FROM Employees 
WHERE City = 'Lagos';

/*
5. Identify employees who earn more than 5000. 
*/

SELECT 
	FirstName,
	LastName,
	Salary
FROM Employees 
WHERE Salary > 5000;

/*
6. Display employees from the youngest to the oldest. 
*/

SELECT
	FirstName,
	LastName,
	Age
FROM Employees 
ORDER BY Age ASC;

/*
7. Arrange employees by salary from highest to lowest. 
*/

SELECT *
FROM Employees 
ORDER BY Salary DESC;

/* 
8. Show employees who have worked for more than five years. 
*/

SELECT
	FirstName,
	LastName,
	YearsAtCompany
FROM Employees 
WHERE YearsAtCompany > 5; 

/* 
9. Display all employees in the Finance department. 
*/

SELECT
	FirstName,
	LastName,
	Department
FROM Employees 
WHERE Department = 'Finance';

/* 
10. Find employees whose performance rating is 5. 
*/

SELECT *
FROM Employees 
WHERE PerformanceRating = 5;

/* 
11. Produce a report showing the total payroll for each department. 
*/

SELECT
	Department,
	SUM(Salary) AS TotalSalary
FROM Employees 
GROUP BY Department;

/* 
12. Determine the average salary in every city. 
*/

SELECT 
	City,
	AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY City;

/* 
13. Find the highest salary in each department. 
*/

SELECT 
	Department,
	MAX(Salary) AS MaxSalary
FROM employees
GROUP BY Department; 

/*
14. Find the youngest employee in every department. 
*/

SELECT
	Department,
	MIN(Age) AS LowestAge
FROM employees
GROUP BY Department; 

/*
15. Count how many employees work in each city. 
*/

SELECT 
	City,
	COUNT(EmployeeID) AS EmployeesByCity
FROM Employees
GROUP BY City;

/*
16. Determine the total number of years employees have spent in each department.
*/

SELECT
	Department,
	SUM(YearsAtCompany) AS TotalYearsInDepartment
FROM Employees 
GROUP BY Department;

/*
17. Display only the different departments in the company. 
*/

SELECT DISTINCT
	Department
FROM Employees;

/*
18. Produce a list of all cities represented in the company without duplicates. 
*/

SELECT DISTINCT
	City
FROM Employees;

/*
19. Display the different job titles available in the company. 
*/

SELECT DISTINCT
	JobTitle
FROM Employees;

/*
20. Show the first five employees in the table. 
*/

SELECT TOP 5
	*
FROM Employees;

/*
21. Display the five highest-paid employees. 
*/

SELECT TOP 5
*
FROM Employees
ORDER BY Salary DESC;

/*
22. Show the three youngest employees. 
*/

SELECT TOP 3
*
FROM Employees
ORDER BY Age ASC;

/*
23. Display the ten employees who have been with the company the longest. 
*/

SELECT TOP 10
*
FROM Employees
ORDER BY YearsAtCompany DESC;

/*
24. Determine which departments have more than four employees. 
*/

SELECT 
	Department,
	COUNT(EmployeeID) AS EmployeesCount
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeID) > 4;

/*
25. Find cities whose average salary is greater than 4500. 
*/

SELECT 
	City,
	AVG(Salary) AS AvergeSalary
FROM Employees  
GROUP BY City
HAVING AVG(Salary) > 4500;

/*
26. Identify departments whose total payroll exceeds 20000. 
*/

SELECT 
	Department,
	SUM(Salary) AS TotalSalary
FROM Employees 
GROUP BY Department
HAVING SUM(Salary) > 20000;

/*
27. Determine which performance ratings have more than three employees. 
*/

SELECT
	PerformanceRating, 
	COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY PerformanceRating
HAVING COUNT(EmployeeID) > 3;

/*
28. Find cities that have fewer than six employees. 
*/

SELECT
	City,
	COUNT(EmployeeID) AS TotalEmployees
FROM Employees 
GROUP BY City
HAVING COUNT(EmployeeID) < 6;

/*
29. Produce a report showing departments with an average employee age greater than 33. 
*/

SELECT
	Department,
	AVG(Age) AS AverageAge
FROM Employees 
GROUP BY Department
HAVING AVG(Age) > 33;

/*
30. Create a new table called Departments. 
31. Design the Departments table to store a department ID, department name, and office location.
*/

CREATE TABLE Departments (
	DepartmentID INT PRIMARY KEY, 
	DepartmentName VARCHAR(50) NOT NULL, 
	OfficeLocation VARCHAR(50) NOT NULL
);

/*
32. Create a table named Projects with columns suitable for storing project information. 
*/

CREATE TABLE Projects (
	id INT NOT NULL
);

/*
33. Add a column named Email to the Employees table. 
*/

ALTER TABLE Employees
ADD Email VARCHAR(30);

/*
34. Add a column named DateHired to the Employees table. 
*/

ALTER TABLE Employees
ADD DateHired DATE;

/*
35. Change the length of the JobTitle column so it can store longer job titles. 
*/

ALTER TABLE Employees
ALTER COLUMN JobTitle VARCHAR (100);

/*
36. Remove the Email column from the Employees table. 
*/

ALTER TABLE Employees
DROP COLUMN Email;
SELECT *
FROM Employees

/*
37. Insert a new employee into the Employees table. 
*/

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle, City, Salary, Age, YearsAtCompany, PerformanceRating, DateHired)
VALUES 
	(26, 'Joe', 'Patrick', 'Finance', 'Financial Analyst', 'Lagos', 2300, 23, 1, 2, '2025-07-22');


/*
38. Insert three new employees in a single statement. 
*/

INSERT INTO Employees
VALUES
	(27, 'Aaron', 'Chidi', 'IT', 'Network Engineer', 'Port Harcourt', 6500, 45, 12, 5, '2012-07-23'),
	(28, 'Peter', 'Nnamdi', 'Sales', 'Sales Executive', 'Port Harcourt', 4500, 35, 6, 3, '2020-07-23'),
	(29, 'Sharon', 'Letila', 'IT', 'Software Engineer', 'Abuja', 4500, 30, 4, 5, '2022-07-23'); 

/*
39. Add two new departments to the Departments table. 
*/

INSERT INTO Departments (DepartmentID, DepartmentName, OfficeLocation)
VALUES
	(30, 'Sports', 'Abuja'),
	(31, 'Entertainment', 'Lagos'); 

/*
40. Correct the salary of John Smith to 4200. 
*/

UPDATE Employees
SET Salary = 4200
WHERE EmployeeID = 1; 

/*
41. Increase every employee's salary by 500. 
*/

UPDATE Employees
SET Salary = Salary + 500
WHERE EmployeeID BETWEEN 1 AND 29;

/*
42. Change the city of every employee currently in Kano to Abuja. 
*/

UPDATE Employees
SET CITY = 'Abuja'
WHERE CITY = 'Kano'; 

/*
43. Update the performance rating of all employees in the IT department to 5. 
*/

UPDATE Employees
SET PerformanceRating = 5
WHERE Department = 'IT';

/*
44. Rename the Marketing department to Business Development. 
*/

UPDATE Employees
SET DEPARTMENT = 'Business Development'
WHERE DEPARTMENT = 'Marketing';

/*
45. Delete the employee whose EmployeeID is 25. 
*/

DELETE FROM Employees
WHERE EmployeeID = 25;

/*
46. Remove all employees working in the HR department. 
*/

DELETE FROM Employees
WHERE Department = 'HR';

/*
47. Delete employees who have worked for less than two years. 
*/

DELETE FROM Employees
WHERE YearsAtCompany < 2;

/*
48. Remove every employee with a performance rating of 2. 
*/

DELETE FROM Employees
WHERE PerformanceRating = 2;

/*
49. Empty all records from the Projects table without deleting the table itself. 
*/

TRUNCATE TABLE Projects;

/*
50. Remove every record from the Departments table while keeping its structure. 
*/

TRUNCATE TABLE Departments;

/*
51. Permanently remove the Projects table from the database.
*/

DROP TABLE IF EXISTS Projects;

/*
52. Permanently remove the Departments table. 
*/

DROP TABLE IF EXISTS Departments;

/*
53. Create a new database named HRManagement. 
*/

CREATE DATABASE HRManagement;

/*
54. Switch to the HRManagement database. 
*/

USE HRManagement;

/*
55. Create an Employees table inside the HRManagement database with columns similar to those in the practice database. 
*/

CREATE TABLE Employees (
	EmployeeID INT PRIMARY KEY,
	Age INT NOT NULL,
	Firstname VARCHAR(50) NOT NULL,
	Lastname VARCHAR(50) NOT NULL
);

/*
56. Delete the HRManagement database. 
*/

USE master
DROP DATABASE HRManagement;

/*
57. Display all employees whose salary is between 4000 and 5500. 
*/

SELECT * 
FROM Employees
WHERE Salary BETWEEN 4000 AND 5500;

/*
58. Find employees whose first name starts with the letter J. 
*/

SELECT *
FROM Employees
WHERE FirstName LIKE 'J%';
 
/*
59. Display employees whose last name ends with son. 
*/

SELECT *
FROM Employees
WHERE LastName LIKE '%son';

/*
60. Show employees who work in either Lagos or Abuja. 
*/

SELECT 
	FirstName,
	LastName,
	City
FROM Employees
WHERE City = 'Lagos' OR City = 'Abuja';

/*
61. Produce a report showing the average salary for each department, beginning with the highest average salary. 
*/

SELECT 
	Department,
	AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
ORDER BY AVG(Salary) DESC;

/*
62. Count how many employees have each performance rating and display the ratings from most common to least common. 
*/

SELECT 
	PerformanceRating,
	COUNT(PerformanceRating) AS NoOfOccurence
FROM EMPLOYEES
GROUP BY PerformanceRating
ORDER BY NoOfOccurence DESC;

/*
63. Find the department that pays the highest individual salary. 
*/

SELECT TOP 1 
	Department,
	Salary
FROM EMPLOYEES
ORDER BY Salary DESC;

/*
64. Determine which city has the largest payroll. 
*/

SELECT TOP 1 
	city,
	SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY City
ORDER BY TotalSalary DESC;

/*
65. Display the oldest five employees. 
*/

SELECT TOP 5
	FirstName,
	LastName,
	Age
FROM employees
ORDER BY AGE DESC;


/*
66. Produce a report showing every department and its highest performance rating. 
*/

SELECT 
	Department,
	MAX(PerformanceRating) AS HighestRating
FROM Employees
GROUP BY Department;

/*
67. Find departments where the oldest employee is at least 40 years old. 
*/

SELECT 
	Department,
	MAX(Age) AS OldestAge
FROM Employees
GROUP BY Department
HAVING MAX(Age) >= 40;

/*
68. Determine which cities have an average employee age below 30. 
*/

SELECT 
	City, 
	AVG(Age)
FROM Employees
GROUP BY City
HAVING AVG(Age) < 30;

/*
69. Display the departments in alphabetical order without duplicates. 
*/

SELECT DISTINCT
	Department
FROM Employees
ORDER BY Department ASC;

/*
70. Produce a report showing the total number of employees in each department, beginning with the department that has the most employees. 
*/

SELECT 
	Department,
	COUNT(EmployeeID) AS TotalEmployees
FROM Employees
GROUP BY Department
ORDER BY TotalEmployees DESC;