SQL_for_Data_Analysis.sql

-- ============================================================
-- SQL FOR DATA ANALYSIS
-- SECTION 1 : DATABASE DESIGN (DDL), CONSTRAINTS & DML
-- ============================================================

-- ============================================================
-- DDL - DATABASE DESIGN
-- ============================================================

-- Q1. Create the Department table.
CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50),
    Location VARCHAR(50)
);

-- Q2. Add PhoneNo column to Authors table.
ALTER TABLE Authors
ADD COLUMN PhoneNo VARCHAR(15);

-- Q3. Rename Name column to AuthorName.
ALTER TABLE Authors
CHANGE COLUMN Name AuthorName VARCHAR(50);

-- Q4. Change Title datatype to VARCHAR(100).
ALTER TABLE Books
MODIFY COLUMN Title VARCHAR(100);

-- Q5. Drop PhoneNo column from Authors table.
ALTER TABLE Authors
DROP COLUMN PhoneNo;

-- ============================================================
-- CONSTRAINTS
-- ============================================================

-- Q6. Create Student table with PRIMARY KEY and UNIQUE constraint.
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50),
    Age INT,
    Email VARCHAR(100) UNIQUE
);

-- Q7. Create Orders_Details table with FOREIGN KEY.
CREATE TABLE Orders_Details (
    OrderID INT PRIMARY KEY,
    ProductCode INT,
    FOREIGN KEY (ProductCode)
        REFERENCES Products(PCode)
);

-- Q8. Create Employee table with UNIQUE Email.
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

-- Q9. Drop Department table.
DROP TABLE Department;

-- ============================================================
-- DML - INSERT
-- ============================================================

-- Q10. Insert a new author.
INSERT INTO Authors (AuthorID, AuthorName)
VALUES
(10, 'Dan Brown');

-- Q11. Insert a new book written by Dan Brown.
INSERT INTO Books (BookID, Title, AuthorID)
VALUES
(15, 'Harry Potter and the Last War', 10);

-- ============================================================
-- DML - UPDATE
-- ============================================================

-- Q12. Update salary of Employee 103.
UPDATE Employee_Master
SET Salary = 10000
WHERE Emp_ID = 103;

-- Q13. Increase salary of every employee by 10%.
UPDATE Employee_Master
SET Salary = Salary * 1.10;

-- Q14. Change category of 'Real Steel' to Action.
UPDATE Movies
SET Category = 'Action'
WHERE MovieName = 'Real Steel';

-- Q15. Update price of Coffee.
UPDATE Drinks
SET Rate = 20
WHERE DrinkName = 'Coffee';

-- ============================================================
-- DML - DELETE
-- ============================================================

-- Q16. Delete all books written by AuthorID = 7.
DELETE FROM Books
WHERE AuthorID = 7;

-- Q17. Delete a drink named Sprite.
DELETE FROM Drinks
WHERE DrinkName = 'Sprite';

-- ============================================================
-- END OF SECTION 1
-- ============================================================

-- ============================================================
-- SECTION 2 : AGGREGATE FUNCTIONS
-- ============================================================

-- Q18. Find the total number of employees.
SELECT COUNT(*) AS TotalEmployees
FROM Employees;

-- Q19. Find the total salary paid to all employees.
SELECT SUM(Salary) AS TotalSalary
FROM Employees;

-- Q20. Find the average salary of employees.
SELECT ROUND(AVG(Salary), 2) AS AverageSalary
FROM Employees;

-- Q21. Find the highest salary.
SELECT MAX(Salary) AS HighestSalary
FROM Employees;

-- Q22. Find the lowest salary.
SELECT MIN(Salary) AS LowestSalary
FROM Employees;

-- Q23. Find the number of employees in each department.
SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

-- Q24. Find the total salary for each department.
SELECT Department,
       SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

-- Q25. Find the average salary for each department.
SELECT Department,
       ROUND(AVG(Salary), 2) AS AverageSalary
FROM Employees
GROUP BY Department;

-- Q26. Find the highest salary in each department.
SELECT Department,
       MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;

-- Q27. Find the lowest salary in each department.
SELECT Department,
       MIN(Salary) AS LowestSalary
FROM Employees
GROUP BY Department;

-- Q28. Display departments having more than 5 employees.
SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

-- Q29. Display departments where the average salary exceeds 60000.
SELECT Department,
       ROUND(AVG(Salary), 2) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;

-- Q30. Find the total sales amount.
SELECT SUM(SalesAmount) AS TotalSales
FROM Sales;

-- Q31. Find the average sales amount.
SELECT ROUND(AVG(SalesAmount), 2) AS AverageSales
FROM Sales;

-- Q32. Find the maximum and minimum sales amount.
SELECT MAX(SalesAmount) AS MaximumSales,
       MIN(SalesAmount) AS MinimumSales
FROM Sales;

-- Q33. Find the total sales generated by each product.
SELECT ProductName,
       SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductName;

-- Q34. Find the average sales generated by each product.
SELECT ProductName,
       ROUND(AVG(SalesAmount), 2) AS AverageSales
FROM Sales
GROUP BY ProductName;

-- Q35. Find the total number of orders placed by each customer.
SELECT CustomerID,
       COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID;

-- Q36. Find customers who placed more than 3 orders.
SELECT CustomerID,
       COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 3;

-- Q37. Find the total revenue generated by each customer.
SELECT CustomerID,
       SUM(OrderAmount) AS TotalRevenue
FROM Orders
GROUP BY CustomerID;

-- Q38. Find the department with the highest average salary.
SELECT Department,
       ROUND(AVG(Salary), 2) AS AverageSalary
FROM Employees
GROUP BY Department
ORDER BY AverageSalary DESC
LIMIT 1;

-- Q39. Find the department with the highest total salary.
SELECT Department,
       SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
ORDER BY TotalSalary DESC
LIMIT 1;

-- ============================================================
-- SECTION 3 : JOINS
-- ============================================================

-- Q40. Display employees along with their department names.
SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

-- Q41. Display all employees with their department names.
-- Include employees even if they are not assigned to a department.
SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

-- Q42. Display all departments along with their employees.
-- Include departments that have no employees.
SELECT d.DepartmentID,
       d.DepartmentName,
       e.EmployeeName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

-- Q43. Display customers and their orders.
SELECT c.CustomerID,
       c.CustomerName,
       o.OrderID,
       o.OrderDate
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;

-- Q44. Display all customers including those who never placed an order.
SELECT c.CustomerID,
       c.CustomerName,
       o.OrderID
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;

-- Q45. Display every order with customer name.
SELECT o.OrderID,
       o.OrderDate,
       c.CustomerName
FROM Orders o
INNER JOIN Customers c
ON o.CustomerID = c.CustomerID;

-- Q46. Display order details with product names.
SELECT od.OrderID,
       p.ProductName,
       od.Quantity,
       od.UnitPrice
FROM OrderDetails od
INNER JOIN Products p
ON od.ProductID = p.ProductID;

-- Q47. Display customer name, order ID and product name.
SELECT c.CustomerName,
       o.OrderID,
       p.ProductName
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails od
ON o.OrderID = od.OrderID
INNER JOIN Products p
ON od.ProductID = p.ProductID;

-- Q48. Find employees earning more than their managers.
SELECT e.EmployeeName AS Employee,
       m.EmployeeName AS Manager,
       e.Salary,
       m.Salary AS ManagerSalary
FROM Employees e
INNER JOIN Employees m
ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;

-- Q49. Display employees and their managers.
SELECT e.EmployeeName AS Employee,
       m.EmployeeName AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerID = m.EmployeeID;

-- Q50. Display products that have never been ordered.
SELECT p.ProductID,
       p.ProductName
FROM Products p
LEFT JOIN OrderDetails od
ON p.ProductID = od.ProductID
WHERE od.ProductID IS NULL;

-- Q51. Display customers who never placed any order.
SELECT c.CustomerID,
       c.CustomerName
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

-- Q52. Display suppliers and the products they supply.
SELECT s.SupplierName,
       p.ProductName
FROM Suppliers s
INNER JOIN Products p
ON s.SupplierID = p.SupplierID;

-- Q53. Display employees working on projects.
SELECT e.EmployeeName,
       p.ProjectName
FROM Employees e
INNER JOIN EmployeeProjects ep
ON e.EmployeeID = ep.EmployeeID
INNER JOIN Projects p
ON ep.ProjectID = p.ProjectID;

-- Q54. Display students with their enrolled courses.
SELECT s.StudentName,
       c.CourseName
FROM Students s
INNER JOIN Enrollments e
ON s.StudentID = e.StudentID
INNER JOIN Courses c
ON e.CourseID = c.CourseID;

-- Q55. Find the total sales amount for each customer.
SELECT c.CustomerName,
       SUM(o.OrderAmount) AS TotalSales
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

-- Q56. Find the number of orders placed by each customer.
SELECT c.CustomerName,
       COUNT(o.OrderID) AS TotalOrders
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

-- Q57. Find the top 5 customers by total purchase amount.
SELECT c.CustomerName,
       SUM(o.OrderAmount) AS TotalPurchase
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
ORDER BY TotalPurchase DESC
LIMIT 5;

-- Q58. Display each employee with their department and project.
SELECT e.EmployeeName,
       d.DepartmentName,
       p.ProjectName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID
INNER JOIN EmployeeProjects ep
ON e.EmployeeID = ep.EmployeeID
INNER JOIN Projects p
ON ep.ProjectID = p.ProjectID;

-- Q59. Display all products with supplier names.
SELECT p.ProductName,
       s.SupplierName
FROM Products p
INNER JOIN Suppliers s
ON p.SupplierID = s.SupplierID;

-- ============================================================
-- SECTION 4 : SUBQUERIES
-- ============================================================

-- Q60. Find employees earning more than the average salary.
SELECT EmployeeID,
       EmployeeName,
       Salary
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

-- Q61. Find employees earning less than the average salary.
SELECT EmployeeID,
       EmployeeName,
       Salary
FROM Employees
WHERE Salary <
(
    SELECT AVG(Salary)
    FROM Employees
);

-- Q62. Find employees who earn the highest salary.
SELECT EmployeeID,
       EmployeeName,
       Salary
FROM Employees
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
);

-- Q63. Find employees who earn the lowest salary.
SELECT EmployeeID,
       EmployeeName,
       Salary
FROM Employees
WHERE Salary =
(
    SELECT MIN(Salary)
    FROM Employees
);

-- Q64. Find the second highest salary.
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary <
(
    SELECT MAX(Salary)
    FROM Employees
);

-- Q65. Find employees working in the Sales department.
SELECT EmployeeName
FROM Employees
WHERE DepartmentID =
(
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Sales'
);

-- Q66. Find customers who placed at least one order.
SELECT CustomerID,
       CustomerName
FROM Customers
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
);

-- Q67. Find customers who never placed an order.
SELECT CustomerID,
       CustomerName
FROM Customers
WHERE CustomerID NOT IN
(
    SELECT CustomerID
    FROM Orders
);

-- Q68. Find products that have been ordered.
SELECT ProductID,
       ProductName
FROM Products
WHERE ProductID IN
(
    SELECT ProductID
    FROM OrderDetails
);

-- Q69. Find products that were never ordered.
SELECT ProductID,
       ProductName
FROM Products
WHERE ProductID NOT IN
(
    SELECT ProductID
    FROM OrderDetails
);

-- Q70. Find employees working in departments located in Bangalore.
SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN
(
    SELECT DepartmentID
    FROM Departments
    WHERE Location = 'Bangalore'
);

-- Q71. Find orders whose amount is greater than the average order amount.
SELECT OrderID,
       CustomerID,
       OrderAmount
FROM Orders
WHERE OrderAmount >
(
    SELECT AVG(OrderAmount)
    FROM Orders
);

-- Q72. Find products priced above the average price.
SELECT ProductName,
       Price
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

-- Q73. Find employees whose salary equals the highest salary in their department.
SELECT EmployeeID,
       EmployeeName,
       DepartmentID,
       Salary
FROM Employees e
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
    WHERE DepartmentID = e.DepartmentID
);

-- Q74. Find customers whose total purchase exceeds the average customer purchase.
SELECT CustomerID,
       SUM(OrderAmount) AS TotalPurchase
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) >
(
    SELECT AVG(CustomerTotal)
    FROM
    (
        SELECT SUM(OrderAmount) AS CustomerTotal
        FROM Orders
        GROUP BY CustomerID
    ) AS CustomerSales
);

-- Q75. Find departments having more employees than the average department size.
SELECT DepartmentID,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) >
(
    SELECT AVG(EmployeeCount)
    FROM
    (
        SELECT COUNT(*) AS EmployeeCount
        FROM Employees
        GROUP BY DepartmentID
    ) AS DeptCount
);

-- Q76. Find employees whose salary is greater than their manager's salary.
SELECT EmployeeName,
       Salary
FROM Employees e
WHERE Salary >
(
    SELECT Salary
    FROM Employees m
    WHERE m.EmployeeID = e.ManagerID
);

-- Q77. Find customers who purchased the most expensive product.
SELECT DISTINCT c.CustomerName
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
JOIN OrderDetails od
ON o.OrderID = od.OrderID
WHERE od.ProductID =
(
    SELECT ProductID
    FROM Products
    WHERE Price =
    (
        SELECT MAX(Price)
        FROM Products
    )
);

-- Q78. Find employees who work in the same department as 'John'.
SELECT EmployeeName
FROM Employees
WHERE DepartmentID =
(
    SELECT DepartmentID
    FROM Employees
    WHERE EmployeeName = 'John'
)
AND EmployeeName <> 'John';

-- Q79. Find products costing more than all products in Category 'Electronics'.
SELECT ProductName,
       Price
FROM Products
WHERE Price >
ALL
(
    SELECT Price
    FROM Products
    WHERE Category = 'Electronics'
);

-- ============================================================
-- SECTION 5 : WINDOW FUNCTIONS
-- ============================================================

-- Q80. Assign a unique row number to each employee.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

-- Q81. Rank employees based on salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

-- Q82. Assign dense ranks based on salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseSalaryRank
FROM Employees;

-- Q83. Find the top 3 highest-paid employees.
SELECT *
FROM (
    SELECT EmployeeID,
           EmployeeName,
           Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
) RankedEmployees
WHERE SalaryRank <= 3;

-- Q84. Find the second highest salary.
SELECT *
FROM (
    SELECT EmployeeID,
           EmployeeName,
           Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
) RankedEmployees
WHERE SalaryRank = 2;

-- Q85. Rank employees within each department.
SELECT EmployeeID,
       EmployeeName,
       DepartmentID,
       Salary,
       DENSE_RANK() OVER
       (
           PARTITION BY DepartmentID
           ORDER BY Salary DESC
       ) AS DepartmentRank
FROM Employees;

-- Q86. Find the highest-paid employee in each department.
SELECT *
FROM (
    SELECT EmployeeID,
           EmployeeName,
           DepartmentID,
           Salary,
           DENSE_RANK() OVER
           (
               PARTITION BY DepartmentID
               ORDER BY Salary DESC
           ) AS DepartmentRank
    FROM Employees
) RankedEmployees
WHERE DepartmentRank = 1;

-- Q87. Find the second highest-paid employee in each department.
SELECT *
FROM (
    SELECT EmployeeID,
           EmployeeName,
           DepartmentID,
           Salary,
           DENSE_RANK() OVER
           (
               PARTITION BY DepartmentID
               ORDER BY Salary DESC
           ) AS DepartmentRank
    FROM Employees
) RankedEmployees
WHERE DepartmentRank = 2;

-- Q88. Display the previous employee's salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       LAG(Salary) OVER (ORDER BY Salary) AS PreviousSalary
FROM Employees;

-- Q89. Display the next employee's salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;

-- Q90. Calculate salary difference from the previous employee.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       Salary -
       LAG(Salary) OVER (ORDER BY Salary) AS SalaryDifference
FROM Employees;

-- Q91. Display cumulative salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       SUM(Salary) OVER
       (
           ORDER BY EmployeeID
       ) AS RunningTotalSalary
FROM Employees;

-- Q92. Display cumulative sales.
SELECT OrderDate,
       SalesAmount,
       SUM(SalesAmount) OVER
       (
           ORDER BY OrderDate
       ) AS RunningSales
FROM Sales;

-- Q93. Calculate moving average of sales.
SELECT OrderDate,
       SalesAmount,
       AVG(SalesAmount) OVER
       (
           ORDER BY OrderDate
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS MovingAverage
FROM Sales;

-- Q94. Divide employees into four salary groups.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       NTILE(4) OVER (ORDER BY Salary DESC) AS SalaryQuartile
FROM Employees;

-- Q95. Display the first salary in each department.
SELECT EmployeeID,
       EmployeeName,
       DepartmentID,
       Salary,
       FIRST_VALUE(Salary) OVER
       (
           PARTITION BY DepartmentID
           ORDER BY Salary DESC
       ) AS HighestDepartmentSalary
FROM Employees;

-- Q96. Display the lowest salary in each department.
SELECT EmployeeID,
       EmployeeName,
       DepartmentID,
       Salary,
       LAST_VALUE(Salary) OVER
       (
           PARTITION BY DepartmentID
           ORDER BY Salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
       ) AS LowestDepartmentSalary
FROM Employees;

-- Q97. Display percentage contribution of each employee's salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       ROUND
       (
           Salary * 100.0 /
           SUM(Salary) OVER (),
           2
       ) AS SalaryPercentage
FROM Employees;

-- Q98. Find the employee with the highest salary in each department.
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER
           (
               PARTITION BY DepartmentID
               ORDER BY Salary DESC
           ) AS RN
    FROM Employees
) RankedEmployees
WHERE RN = 1;

-- Q99. Find the top 2 employees from each department.
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER
           (
               PARTITION BY DepartmentID
               ORDER BY Salary DESC
           ) AS RN
    FROM Employees
) RankedEmployees
WHERE RN <= 2;

-- ============================================================
-- SECTION 6 : STRING FUNCTIONS
-- ============================================================

-- Q100. Display employee names in uppercase.
SELECT EmployeeID,
       UPPER(EmployeeName) AS EmployeeName
FROM Employees;

-- Q101. Display employee names in lowercase.
SELECT EmployeeID,
       LOWER(EmployeeName) AS EmployeeName
FROM Employees;

-- Q102. Find the length of each employee's name.
SELECT EmployeeID,
       EmployeeName,
       LENGTH(EmployeeName) AS NameLength
FROM Employees;

-- Q103. Concatenate first name and last name.
SELECT EmployeeID,
       CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

-- Q104. Extract the first three characters of employee names.
SELECT EmployeeID,
       EmployeeName,
       LEFT(EmployeeName, 3) AS FirstThreeLetters
FROM Employees;

-- Q105. Extract the last three characters of employee names.
SELECT EmployeeID,
       EmployeeName,
       RIGHT(EmployeeName, 3) AS LastThreeLetters
FROM Employees;

-- Q106. Extract the first name from FullName.
SELECT EmployeeID,
       FullName,
       SUBSTRING_INDEX(FullName, ' ', 1) AS FirstName
FROM Employees;

-- Q107. Extract the last name from FullName.
SELECT EmployeeID,
       FullName,
       SUBSTRING_INDEX(FullName, ' ', -1) AS LastName
FROM Employees;

-- Q108. Replace 'Ltd' with 'Limited' in company names.
SELECT CompanyName,
       REPLACE(CompanyName, 'Ltd', 'Limited') AS UpdatedCompanyName
FROM Companies;

-- Q109. Remove leading spaces from customer names.
SELECT CustomerID,
       LTRIM(CustomerName) AS CustomerName
FROM Customers;

-- Q110. Remove trailing spaces from customer names.
SELECT CustomerID,
       RTRIM(CustomerName) AS CustomerName
FROM Customers;

-- Q111. Remove leading and trailing spaces.
SELECT CustomerID,
       TRIM(CustomerName) AS CustomerName
FROM Customers;

-- Q112. Find employees whose names start with 'A'.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'A%';

-- Q113. Find employees whose names end with 'n'.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%n';

-- Q114. Find employees whose names contain 'ar'.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%ar%';

-- Q115. Display the position of 'a' in employee names.
SELECT EmployeeID,
       EmployeeName,
       INSTR(EmployeeName, 'a') AS PositionOfA
FROM Employees;

-- Q116. Reverse employee names.
SELECT EmployeeID,
       EmployeeName,
       REVERSE(EmployeeName) AS ReversedName
FROM Employees;

-- Q117. Pad employee IDs with leading zeros.
SELECT EmployeeID,
       LPAD(EmployeeID, 5, '0') AS FormattedEmployeeID
FROM Employees;

-- Q118. Pad department codes with trailing zeros.
SELECT DepartmentCode,
       RPAD(DepartmentCode, 8, '0') AS DepartmentCode
FROM Departments;

-- Q119. Remove all '-' characters from phone numbers.
SELECT CustomerID,
       REPLACE(PhoneNumber, '-', '') AS PhoneNumber
FROM Customers;

-- ============================================================
-- SECTION 7 : DATE FUNCTIONS
-- ============================================================

-- Q120. Display the current date.
SELECT CURDATE() AS CurrentDate;

-- Q121. Display the current date and time.
SELECT NOW() AS CurrentDateTime;

-- Q122. Display the current timestamp.
SELECT CURRENT_TIMESTAMP() AS CurrentTimestamp;

-- Q123. Extract only the date from OrderDateTime.
SELECT OrderID,
       DATE(OrderDateTime) AS OrderDate
FROM Orders;

-- Q124. Display the year of each order.
SELECT OrderID,
       YEAR(OrderDate) AS OrderYear
FROM Orders;

-- Q125. Display the month of each order.
SELECT OrderID,
       MONTH(OrderDate) AS OrderMonth
FROM Orders;

-- Q126. Display the month name of each order.
SELECT OrderID,
       MONTHNAME(OrderDate) AS MonthName
FROM Orders;

-- Q127. Display the day of the month for each order.
SELECT OrderID,
       DAY(OrderDate) AS DayOfMonth
FROM Orders;

-- Q128. Display the day name of each order.
SELECT OrderID,
       DAYNAME(OrderDate) AS DayName
FROM Orders;

-- Q129. Find orders placed in the year 2025.
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2025;

-- Q130. Find orders placed in January.
SELECT *
FROM Orders
WHERE MONTH(OrderDate) = 1;

-- Q131. Find orders placed on Monday.
SELECT *
FROM Orders
WHERE DAYNAME(OrderDate) = 'Monday';

-- Q132. Calculate the number of days between the order date and today.
SELECT OrderID,
       OrderDate,
       DATEDIFF(CURDATE(), OrderDate) AS DaysSinceOrder
FROM Orders;

-- Q133. Calculate the number of years each employee has worked.
SELECT EmployeeID,
       EmployeeName,
       HireDate,
       TIMESTAMPDIFF(YEAR, HireDate, CURDATE()) AS YearsOfService
FROM Employees;

-- Q134. Calculate the number of months each employee has worked.
SELECT EmployeeID,
       EmployeeName,
       HireDate,
       TIMESTAMPDIFF(MONTH, HireDate, CURDATE()) AS MonthsOfService
FROM Employees;

-- Q135. Add 30 days to each order date.
SELECT OrderID,
       OrderDate,
       DATE_ADD(OrderDate, INTERVAL 30 DAY) AS DeliveryDate
FROM Orders;

-- Q136. Subtract 7 days from each order date.
SELECT OrderID,
       OrderDate,
       DATE_SUB(OrderDate, INTERVAL 7 DAY) AS PreviousWeek
FROM Orders;

-- Q137. Display the last day of the order month.
SELECT OrderID,
       OrderDate,
       LAST_DAY(OrderDate) AS LastDayOfMonth
FROM Orders;

-- Q138. Extract the quarter from each order date.
SELECT OrderID,
       OrderDate,
       QUARTER(OrderDate) AS Quarter
FROM Orders;

-- Q139. Extract the week number from each order date.
SELECT OrderID,
       OrderDate,
       WEEK(OrderDate) AS WeekNumber
FROM Orders;

-- Q140. Display orders placed in the current month.
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = YEAR(CURDATE())
  AND MONTH(OrderDate) = MONTH(CURDATE());

-- Q141. Display orders placed in the previous month.
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
  AND MONTH(OrderDate) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH));

-- Q142. Find employees hired within the last 5 years.
SELECT EmployeeID,
       EmployeeName,
       HireDate
FROM Employees
WHERE HireDate >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

-- Q143. Find orders placed in the last 30 days.
SELECT *
FROM Orders
WHERE OrderDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Q144. Find the age of each order in days.
SELECT OrderID,
       OrderDate,
       DATEDIFF(CURDATE(), OrderDate) AS OrderAge
FROM Orders;

-- ============================================================
-- SECTION 8 : CASE EXPRESSIONS & NULL HANDLING
-- ============================================================

-- Q145. Categorize employees based on salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       CASE
           WHEN Salary >= 100000 THEN 'High Salary'
           WHEN Salary >= 60000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS SalaryCategory
FROM Employees;

-- Q146. Categorize customers based on total purchase amount.
SELECT CustomerID,
       CustomerName,
       TotalPurchase,
       CASE
           WHEN TotalPurchase >= 100000 THEN 'Premium'
           WHEN TotalPurchase >= 50000 THEN 'Gold'
           ELSE 'Regular'
       END AS CustomerCategory
FROM Customers;

-- Q147. Display employee bonus based on salary.
SELECT EmployeeID,
       EmployeeName,
       Salary,
       CASE
           WHEN Salary >= 100000 THEN Salary * 0.20
           WHEN Salary >= 60000 THEN Salary * 0.10
           ELSE Salary * 0.05
       END AS Bonus
FROM Employees;

-- Q148. Replace NULL commission with 0.
SELECT EmployeeID,
       EmployeeName,
       IFNULL(Commission, 0) AS Commission
FROM Employees;

-- Q149. Replace NULL phone numbers with 'Not Available'.
SELECT CustomerID,
       CustomerName,
       COALESCE(PhoneNumber, 'Not Available') AS PhoneNumber
FROM Customers;

-- Q150. Display employees who do not have a manager.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE ManagerID IS NULL;

-- Q151. Display employees who have a manager.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE ManagerID IS NOT NULL;

-- Q152. Replace NULL department with 'Unassigned'.
SELECT EmployeeID,
       EmployeeName,
       COALESCE(DepartmentID, 'Unassigned') AS Department
FROM Employees;

-- Q153. Prevent division by zero using NULLIF().
SELECT ProductID,
       TotalSales,
       QuantitySold,
       TotalSales / NULLIF(QuantitySold, 0) AS AveragePrice
FROM Sales;

-- Q154. Display order status.
SELECT OrderID,
       CASE
           WHEN Status = 'D' THEN 'Delivered'
           WHEN Status = 'P' THEN 'Pending'
           WHEN Status = 'C' THEN 'Cancelled'
           ELSE 'Unknown'
       END AS OrderStatus
FROM Orders;

-- Q155. Classify products based on stock.
SELECT ProductID,
       ProductName,
       StockQuantity,
       CASE
           WHEN StockQuantity = 0 THEN 'Out of Stock'
           WHEN StockQuantity < 20 THEN 'Low Stock'
           ELSE 'In Stock'
       END AS StockStatus
FROM Products;

-- Q156. Display employee experience level.
SELECT EmployeeID,
       EmployeeName,
       HireDate,
       CASE
           WHEN TIMESTAMPDIFF(YEAR, HireDate, CURDATE()) >= 10 THEN 'Senior'
           WHEN TIMESTAMPDIFF(YEAR, HireDate, CURDATE()) >= 5 THEN 'Mid-Level'
           ELSE 'Junior'
       END AS ExperienceLevel
FROM Employees;

-- Q157. Replace NULL email addresses.
SELECT EmployeeID,
       EmployeeName,
       COALESCE(Email, 'No Email') AS Email
FROM Employees;

-- Q158. Display customer loyalty level.
SELECT CustomerID,
       CustomerName,
       CASE
           WHEN TotalPurchase >= 200000 THEN 'Platinum'
           WHEN TotalPurchase >= 100000 THEN 'Gold'
           WHEN TotalPurchase >= 50000 THEN 'Silver'
           ELSE 'Bronze'
       END AS LoyaltyLevel
FROM Customers;

-- Q159. Display products with discount information.
SELECT ProductID,
       ProductName,
       IFNULL(Discount, 0) AS Discount
FROM Products;

-- Q160. Display employees with missing phone numbers.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE PhoneNumber IS NULL;

-- Q161. Display employees with available phone numbers.
SELECT EmployeeID,
       EmployeeName
FROM Employees
WHERE PhoneNumber IS NOT NULL;

-- Q162. Categorize order value.
SELECT OrderID,
       OrderAmount,
       CASE
           WHEN OrderAmount >= 10000 THEN 'High Value'
           WHEN OrderAmount >= 5000 THEN 'Medium Value'
           ELSE 'Low Value'
       END AS OrderCategory
FROM Orders;

-- Q163. Display shipping status.
SELECT OrderID,
       CASE
           WHEN ShippedDate IS NULL THEN 'Not Shipped'
           ELSE 'Shipped'
       END AS ShippingStatus
FROM Orders;

-- Q164. Replace NULL addresses with 'Address Not Available'.
SELECT CustomerID,
       CustomerName,
       COALESCE(Address, 'Address Not Available') AS Address
FROM Customers;

-- ============================================================
-- SECTION 9 : VIEWS & BUSINESS REPORTING QUERIES
-- ============================================================

-- Q165. Create a view for employee details.
CREATE VIEW EmployeeDetails AS
SELECT EmployeeID,
       EmployeeName,
       DepartmentID,
       Salary
FROM Employees;

-- Q166. Display all records from the EmployeeDetails view.
SELECT *
FROM EmployeeDetails;

-- Q167. Create a view for customer orders.
CREATE VIEW CustomerOrders AS
SELECT c.CustomerID,
       c.CustomerName,
       o.OrderID,
       o.OrderDate,
       o.OrderAmount
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;

-- Q168. Display all records from CustomerOrders.
SELECT *
FROM CustomerOrders;

-- Q169. Create a department salary summary view.
CREATE VIEW DepartmentSalarySummary AS
SELECT DepartmentID,
       COUNT(*) AS TotalEmployees,
       SUM(Salary) AS TotalSalary,
       AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

-- Q170. Display department salary summary.
SELECT *
FROM DepartmentSalarySummary;

-- Q171. Find the top 10 customers by total purchase.
SELECT c.CustomerID,
       c.CustomerName,
       SUM(o.OrderAmount) AS TotalPurchase
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
ORDER BY TotalPurchase DESC
LIMIT 10;

-- Q172. Find the top 10 best-selling products.
SELECT p.ProductID,
       p.ProductName,
       SUM(od.Quantity) AS TotalSold
FROM Products p
JOIN OrderDetails od
ON p.ProductID = od.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalSold DESC
LIMIT 10;

-- Q173. Find monthly sales.
SELECT YEAR(OrderDate) AS SalesYear,
       MONTH(OrderDate) AS SalesMonth,
       SUM(OrderAmount) AS TotalSales
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY SalesYear, SalesMonth;

-- Q174. Find yearly sales.
SELECT YEAR(OrderDate) AS SalesYear,
       SUM(OrderAmount) AS TotalSales
FROM Orders
GROUP BY YEAR(OrderDate)
ORDER BY SalesYear;

-- Q175. Find department-wise average salary.
SELECT d.DepartmentName,
       ROUND(AVG(e.Salary), 2) AS AverageSalary
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName
ORDER BY AverageSalary DESC;

-- Q176. Find customer lifetime value.
SELECT c.CustomerID,
       c.CustomerName,
       SUM(o.OrderAmount) AS LifetimeValue
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
ORDER BY LifetimeValue DESC;

-- Q177. Find employees hired each year.
SELECT YEAR(HireDate) AS HireYear,
       COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY YEAR(HireDate)
ORDER BY HireYear;

-- Q178. Find departments with the highest payroll.
SELECT d.DepartmentName,
       SUM(e.Salary) AS Payroll
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName
ORDER BY Payroll DESC;

-- Q179. Find average order value for each customer.
SELECT c.CustomerName,
       ROUND(AVG(o.OrderAmount), 2) AS AverageOrderValue
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
ORDER BY AverageOrderValue DESC;

-- Q180. Find the top-performing salesperson.
SELECT EmployeeID,
       SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY EmployeeID
ORDER BY TotalSales DESC
LIMIT 1;

-- Q181. Find products with low inventory.
SELECT ProductID,
       ProductName,
       StockQuantity
FROM Products
WHERE StockQuantity < 20
ORDER BY StockQuantity;

-- Q182. Find customers who haven't placed orders in the last 6 months.
SELECT c.CustomerID,
       c.CustomerName
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
HAVING MAX(o.OrderDate) < DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    OR MAX(o.OrderDate) IS NULL;

-- Q183. Find monthly revenue growth.
SELECT YEAR(OrderDate) AS SalesYear,
       MONTH(OrderDate) AS SalesMonth,
       SUM(OrderAmount) AS Revenue
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY SalesYear, SalesMonth;

-- Q184. Find employees earning above their department average.
SELECT EmployeeID,
       EmployeeName,
       DepartmentID,
       Salary
FROM Employees e
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = e.DepartmentID
);

-- Q185. Drop the EmployeeDetails view.
DROP VIEW IF EXISTS EmployeeDetails;