day5SQLQuery1nestedqueries.sql

--- USE DATABASE
USE SQL_ANJALI_SESSION;


--- SESSION 3 : SUBQUERIES (NESTED QUERIES)

--- Employee table creation for subquery practice
CREATE TABLE nestedqueries
(
    EID INT,
    Ename VARCHAR(30),
    Esalary INT
);

--- Inserting data
--- Two employees have the SAME highest salary (60000)
INSERT INTO nestedqueries VALUES
(1,'Amit',40000),
(2,'Neha',55000),
(3,'Ravi',60000),
(4,'Pooja',60000),
(5,'Arjun',45000);


--- View all records
SELECT * FROM nestedqueries;


--- Finding maximum salary
SELECT MAX(Esalary) FROM nestedqueries;


--- Using TOP 1 (out of the box approach)
SELECT TOP 1 EID, Ename, Esalary
FROM nestedqueries
ORDER BY Esalary DESC;


--- Using SUBQUERY (important interview question)
SELECT *
FROM nestedqueries
WHERE Esalary = (SELECT MAX(Esalary) FROM nestedqueries);


--- FINDING SECOND HIGHEST SALARY
SELECT MAX(Esalary) AS Second_Highest_Salary
FROM nestedqueries
WHERE Esalary < (SELECT MAX(Esalary) FROM nestedqueries);


--- Getting employee details having second highest salary
SELECT *
FROM nestedqueries
WHERE Esalary =
(
    SELECT MAX(Esalary)
    FROM nestedqueries
    WHERE Esalary < (SELECT MAX(Esalary) FROM nestedqueries)
);


--- Find all employees whose salary is higher than the average salary
SELECT Ename, Esalary
FROM nestedqueries
WHERE Esalary > (SELECT AVG(Esalary) FROM nestedqueries);


--- Generic example (COMMENTED to avoid error – table not created)
-- SELECT *
-- FROM Products
-- WHERE Price > (SELECT AVG(Price) FROM Products);


--- Drop table after usage
DROP TABLE nestedqueries;


---------------------------------------------------------
--- NEW TABLE 2 : EMPLOYEE
---------------------------------------------------------

CREATE TABLE Employeeee
(
    EID INT,
    Ename VARCHAR(30),
    Department VARCHAR(30),
    Age INT,
    Esalary INT
);

INSERT INTO Employeeee VALUES
(1,'Amit','IT',28,50000),
(2,'Neha','HR',32,60000),
(3,'Ravi','IT',35,60000),
(4,'Pooja','HR',26,45000),
(5,'Arjun','Sales',40,55000);


--- QUERY 1 (COMMENTED – Sales table not created)
-- SELECT DISTINCT ProductName
-- FROM Sales
-- WHERE ProductName IN
-- (
--     SELECT ProductName
--     FROM Sales
--     WHERE Region = 'Vadodara'
-- );


--- QUERY 2
--- Find departments having at least one employee older than 30

SELECT DISTINCT Department
FROM Employeeee
WHERE Department IN
(
    SELECT Department
    FROM Employeeee
    WHERE Age > 30
);


--- QUERY 3
--- Employees earning max salary in their department

SELECT Ename, Department, Esalary
FROM Employeeee
WHERE Esalary IN
(
    SELECT MAX(Esalary)
    FROM Employeeee
    GROUP BY Department
);