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:
