Important Query for Beginners PART (3)

201.Department where no employee  is working ?

SELECT D.Deptno || ' has no employee' DeptNo

FROM Emp E,Dept D

WHERE E.Deptno(+)=D.Deptno

                 AND

                E.Deptno IS NULL

202.Dislpay Department with highest annual remuneration bill as compensation ?

SELECT Deptno,SalSum

FROM  (  SELECT DeptNo,SUM(Comm) SalSum

                 FROM Emp

                 GROUP BY Deptno

                 ORDER BY SalSum )

 WHERE ROWNUM<=1;

203.Department wise average salary ?

SELECT DeptNo,TRUNC(AVG(Sal))  DeptAvgSalary

FROM Emp

GROUP BY Deptno

ORDER BY 1

204.Employees salary more than lowest salary of dept 10 ?

SELECT EName,Deptno,Sal Salary

FROM Emp

WHERE Sal > (SELECT MIN(Sal)  Salary

           FROM Emp

           WHERE Deptno=10)

205.Deptname,Deptno,Sum of salary ?

SELECT D.Deptno,DName,DeptSalSum

FROM Dept D,(SELECT Deptno,SUM(Sal) DeptSalSum

                             FROM Emp

                            GROUP BY Deptno) E

WHERE D.Deptno=E.Deptno

ORDER BY 1

206.Depatrment having more than 3 employee ?

SELECT Deptno,COUNT(*) No_Of_Employee

FROM Emp

GROUP BY Deptno

HAVING COUNT(*) >3

207.List Lowest paid employee of a group excluding any group where minimum salary less than 1000  sort in order of salary ?

SELECT EName,Deptno,Sal

 FROM  (SELECT  EName,Deptno

                                ,ROW_NUMBER()

                                 OVER(PARTITION BY Deptno

                                                           ORDER BY Sal) DeptPos

                                 ,Sal

                 FROM Emp )

 WHERE DeptPos=1

               AND

               Sal > 1000

208.Delete the recode where department has no worker ?

DELETE FROM Dept

WHERE Deptno= (SELECT D.Deptno

                                    FROM Emp E,Dept D

                                    WHERE E.Deptno(+)=D.Deptno

                                    AND

                                    E.Deptno IS NULL)

209.Employes going to retired those have experience more than 30 year ?

SELECT Ename

FROM Emp

WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HireDate)/12) >30

210.Emlpoyees those salary is odd ?

SELECT * FROM Emp

WHERE MOD(Sal,2) <> 0;

211.Emploees salary contain at least 3 digit ?

SELECT * FROM Emp

WHERE LENGTH(Sal) > 3;

212.Employees joined on month of December ?

SELECT * FROM Emp

WHERE TO_CHAR(HireDate,’MON’)=’DEC’;

213.Employees name contain ‘a’ ?

SELECT * FROM Emp

WHERE UPPER(EName) LIKE(‘%A%’);

214.Employees deptno available in salary ?

SELECT * FROM Emp

WHERE  Sal LIKE(‘%’||Deptno||’%’);

215.Employees 10% salary equals to year of join ?

SELECT * FROM Emp

WHERE TO_CHAR(Hiredate,’YY’)=Sal*.1;

216.Employees joined before 15th of the month ?

SELECT * FROM Emp

WHERE TO_CHAR(Hiredate,’DD’) > 15

217.Employees joined 10 year back?

SELECT * FROM Emp

WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HireDate)/12) > 10

218.Employes working as manager?

SELECT EName FROM Emp E

WHERE EXISTS (SELECT EName FROM Emp M

                                 WHERE E.Empno=M.mgr)

219.Display unique job in emp ?

SELECT DISTINCT Deptno FROM Emp;

220.Employees name concatenated with job separated by a comma and a space and employees as title ?

SELECT Ename||’, ‘||job Employees FROM Emp;

221.Display all data of table separatd by a comma and title as output ?

SELECT Empno ||’,’|| EName ||’,’||  Job ||’,’||   Deptno ||’,’||  Mgr||’,’||    HireDate ||’,’||  Sal||’,’||   Comm   OutPut

FROM Emp;

222.Display as the format Employee <name>works in department <number> and appointed on <date> ?

SELECT ‘Employee ‘|| Ename || ‘ works in department ’ || Deptno || ‘ and     appointed on ’  || Hiredate  EmpInfo

FROM Emp;