Emp - EmpNo, EmpName, DeptId
Dept - DeptId, DeptName
1. Find unique rows in Emp table? Include queries with functions
select distinct(*) from emp;
2. Find top 10 rows in Emp table
Select top 10 * from emp;
Select * from emp limit 10; - MySQL
Select * from emp fetch first 10 rows only; -Oracle
3. Find last 10 rows in Emp table
Select top 10* from emp order by emp_id desc;
4. Find 50th row to 75th row (***order clause must be present)
Select orderid, orderdate, custId, empId
From sales.orders
Order by orderdate desc, ordereid desc
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
5. Find duplicate rows in Emp table?
(select * from emp) except (select distinct (*) from emp);
6. Remove duplicate rows from emp table
method 1:- (need to add all columns in group by clause)
delete from emp where empid in (select empid from emp group by empid, ename.. having count (empid)>=2);
method 2:-
select distinct * into temp_std from students
group by Std_ID, Std_Name, DOB, Dept ID, DOJ
having count(Std_Id)>=2;
delete Students where Std_ID in (Select Std_ID from temp_std);
insert into Students
select * from temp_std;
drop table temp_std;
method 3:-
create table temp as select distinct * from emp;
drop table emp;
alter table temp
rename to emp
7. Find top 3 earners from emp table?
Select top 3(*) from (select * from emp order by sal desc);
8. Find 3rd highest salary from emp table?
Select distinct (e1.sal) from emp e1 where 3 = (select count(distinct(e2.sal)) from emp e2 where e2.sal > = e1.sal);
9. Find employees whose salary is higher than average salary of their department
select e1.* from emp e1 inner join (select e2.deptId, avg(e2.sal) asal from emp e2 group by deptId) SalaryInfo on e1.deptId = SalaryInfo.DeptId and e1.Sal > SalaryInfo.asal;
select e.ename, e.sal from emp e inner join (select e1.dno dnum, avg(e1.sal) asal from emp e1 group by e1.dno.) temp on e.sal>temp.asal and e.dno = temp.dnum
10. Find empname and his mgrname from emp table?
Select e1.empname, e2.empname as mgrname from emp e1, e2 where e1.mgrid = e2.empid;
11. Find no.of emps in each dept?
Select e.deptid, d.deptname, e.count(*) empcount group by e.deptid having e.deptid = d.deptid;
12. Find the dept having more no. of employees? –
Select deptid, count(*) from emp group by deptid having count(*) = (select max(count(*) from emp group by deptid);
13. Find max salary in each department
Select deptid, max(sal) from emp group by deptid
14. Find the employees getting more than average salary their own department
select e.empID, e.ename from emp e inner join
(select deptID, avg(sal) asal from Emp
group by deptID) as temp
on e.deptID=temp.deptID
where e.sal>temp.asal;
select e.ename from emp e where e.deptID in
(select deptID, avg(sal) asal from Emp
group by deptID) as temp where e.sal>temp.asal;
15. Find the dept name having
select top1 dname from
(select d.dno, d.dname, count(e.empID) as Empcount from Employee e
inner join Department d on e.DeptId=d.DeptID
group by e.empID
order by Empcount desc);
16. Find 2nd highest salary of in each dept from emp table?
Select distinct (e1.sal) from emp e1 where 3 = (select count(distinct(e2.sal) from emp e2 where e2.sal > = e1.sal and e1.dno =e2.dno);
17. Get alternate rows
Select Std_id, Std_name, Dob, Dept_Id, DOJ from(select ROW_NUMBER() over (order by std_id) as Row, * from Students) A;Where Row%2=0;
Join Queries with multiple tables
Customers - CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country
Orders - OrderID, CustomerID, EmployeeID, OrderDate, ShipperID
OrderDetails - OrderDetailID, OrderID, ProductID, Quantity
Products - ProductID, ProductName, SupplierID, CategoryID, Unit, Price
Employees - EmployeeID, LastName, FirstName, BirthDate, Photo, Notes
Suppliers - SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone
Shippers - ShipperID, ShipperName, Phone
Categories - CategoryID, CategoryName, Description
select p.ProductName, c.CustomerName, e.LastName, e.FirstName, s.SupplierName, s.ContactName
from (((((Products p inner join OrderDetails od on p.ProductID = od.ProductID) inner join Orders o
on od.OrderID = o.OrderID) inner join Customers c on o.CustomerID = c.CustomerID) inner join
Employees e on o.EmployeeID=e.EmployeeID) inner join Suppliers s on p.SupplierID = s.SupplierID)
select p.ProductName, c.CustomeirName, e.LastName, e.FirstName, s.SupplierName, s.ContactName
from Products p inner join OrderDetails od inner join Orders o inner join Customers c inner join
Employees e inner join Suppliers s on p.ProductID=od.ProductID and od.OrderID=o.OrderID and
o.CustomerID=c.CustomerID and o.EmployeeID=e.EmployeeID and p.SupplierID=s.SupplierID
select p.ProductID, p.ProductName, Count(od.OrderID) as OrderCount from Products p inner join
OrderDetails od on p.ProductID=od.ProductID
group by p.ProductID
order by OrderCount desc;
For more queries please refer:
Learn SQL - Online SQL Terminal - Practice SQL Querys (sql-practice.com)
###############################################################################
Merge Query
MERGE INTO target_table
USING source_table
ON merge_condition
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 …]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 …])
VALUES (value1 [, value2 …]);
#############################################################################
/* Selecting the Target and the Source */ MERGE PRODUCT_LIST AS TARGET USING UPDATE_LIST AS SOURCE /* 1. Performing the UPDATE operation */ /* If the P_ID is same, check for change in P_NAME or P_PRICE */ ON (TARGET.P_ID = SOURCE.P_ID) WHEN MATCHED AND TARGET.P_NAME <> SOURCE.P_NAME OR TARGET.P_PRICE <> SOURCE.P_PRICE /* Update the records in TARGET */ THEN UPDATE SET TARGET.P_NAME = SOURCE.P_NAME, TARGET.P_PRICE = SOURCE.P_PRICE /* 2. Performing the INSERT operation */ /* When no records are matched with TARGET table Then insert the records in the target table */ WHEN NOT MATCHED BY TARGET THEN INSERT (P_ID, P_NAME, P_PRICE) VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE) /* 3. Performing the DELETE operation */ /* When no records are matched with SOURCE table Then delete the records from the target table */ WHEN NOT MATCHED BY SOURCE THEN DELETE /* END OF MERGE */
################################################################################
Recursive Query A recursive query in SQL refers to a query that repeatedly references itself in order to retrieve hierarchical or tree-structured data. Recursive queries are typically used to handle cases where data is organized in a parent-child relationship, like organizational charts, file systems, or category hierarchies. How Recursive Queries Work Recursive queries are typically written using a Common Table Expression (CTE), which allows a query to reference itself. A recursive CTE is made up of two parts: Anchor Member: This is the base query that returns the initial result set. It represents the starting point of the recursion. Recursive Member: This part references the CTE itself and continues to generate results based on the previous iteration until a specified condition is met. WITH RECURSIVE employee_hierarchy AS ( -- Anchor query: Start with Ankit (employee_id = 1) SELECT Emp_id, Emp_name, Mgr_id FROM employee WHERE Mgr_id is NULL UNION ALL -- Recursive query: Join the employees table with itself to get the employees reporting to each manager SELECT e.Emp_id, e.Emp_name, e.Mgr_id FROM employee e INNER JOIN employee_hierarchy eh ON e.Mgr_id = eh.Emp_id ) SELECT * FROM employee_hierarchy;
