IMP Q

1) Display details of all employees

SQL> select * from emp;


2) Display the employee no and totalsalary for all the employees

SQL> select empno,ename,sal,comm, sal+nvl(comm,0) total_sal from emp;


3) Display the names of all the employees who are working in department 10.

SQL> select emame from emp where deptno=10;


4) Display the employee number and name who are earning comm.

SQL> select empno,ename from emp where comm is not null;


5) Display the names of the employees who are working in the company for the past 5 years;

SQL> select ename from emp where hiredate < add_months(sysdate,-60);


6) Display the names of employees working in department number 10 or 20 or 40 and working as CLERKS,SALESMAN or ANALYST.

SQL> select ename from emp where deptno in(10,20,40) and job in('CLERKS','SALESMAN','ANALYST');


7) Display the Employee names for employees whose name ends with alaphabet S.

SQL> select ename from emp where ename like '%S';


8) Display the maximum salary being paid to CLERK.

SQL> select max(sal) from emp where job='CLERK';


9) Display the total salary drawn by an ANALYST working in department 40.

SQL> select sum(sal) from emp where job='ANALYST' and deptno=40;


10) Display the department numbers with more than three employees in each dept.

SQL> select deptno,count(deptno) from emp group by deptno having count(*)>3;


11) Display the name of the employee who earns highest salary.

SQL> select ename from emp where sal=(select max(sal) from emp);


12) Display the employee number and name for employee working as clerk and earning highest salary among clerks.

SQL> select empno,ename from emp where where job='CLERK' and sal=(select max(sal) from emp where job='CLERK');


13) Display the names of the employees who earn highest salary in their respective departments.

SQL> select ename,sal,deptno from emp e where sal in(select max(sal) from emp m where m.deptno = e.deptno);


14) Display the current '15-Aug-2012' as 15th wednesday August twenty twelve.

SQL> select to_char(to_date('15-Aug-2012'),'ddth day Month year') from dual;


15) Display the Employee name and Managers names.

SQL> select e.ename employee, m.ename manager from emp e, emp m where m.empno=e.mgr;


16) Find out top 5 earners of company.

SQL> select * from (select * from emp order by sal desc) where rownum <= 5;


17) Display the department name and total number of employees in each department.

SQL> select dname, count(ename) from emp, dept where emp.deptno=dept.deptno group by dname;


18) Increase salary of all managers by 10%.

SQL> update emp set sal=sal*1.1 where empno in (select mgr from emp);


19) Delete duplicate department from dept table.

SQL> delete from dept where rowid not in (select max(rowid) from dept group by dname);


20) Create a backup of employee table with emp_tbd name

SQL> create table emp_tbd as select * from emp;


21) Get the details of the employee getting 3rd highest salary

SQL> select * from(select e.*, dense_rank() over (order by sal desc) r from emp e) where r=3;


22) Get the details of the employees getting department wise 3rd highest salary

SQL> select * from(select empno, ename, sal, dname, dense_rank() over (partition by dname order by sal desc) r from scott.emp e, scott.dept d where e.deptno=d.deptno) where r=3;