1 Section-A (SQL)

Employee Database An enterprise wishes to maintain a database to automate its operations. Enterprise divided into to certain departments and each department consists of employees. The following two tables describes the automation schemas DEPT (DEPTNO, DNAME, LOC) EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
create table associate as select * from emp where empno<>mgr and mgr is not null; create table manager as select * from emp where job='MANAGER'; 1. Find out the detail of Top 3 earners of the company. SQL> select [Link],[Link],[Link],[Link] from emp e where (select count(*) from emp where sal>[Link])<3 order by sal desc; 2. Display those manager names whose salary is more than average salary of his employees. SQL> select [Link] from associate, manager where [Link]> (select avg(sal) from emp where [Link]=[Link]); 3. Display those employees who joined the company before 15th of the month. SQL> select empno,ename,hiredate from emp where extract(day from hiredate) <15; 4. Display the manager who is having maximum number of employees working under him. select [Link], count([Link]) from manager, associate where [Link]=[Link] group by [Link] having count([Link])= (select max(count([Link])) from manager, associate where [Link]=[Link] group by [Link]); 5. Print a list of employees displaying Less Salary if less than 1500 if exactly 1500 Display as Exact Salary and if greater than 1500 display more salary. SAL> select ename,sal , (Case when sal<1500 then 'Less Salary' when sal=1500 then 'Exact Salary' when sal>1500 then 'More Salary' else 'No Salary' end) from emp; [Link] the employee salary by 15%, whose experience is greater than 10 years. SQL> update emp set sal=sal+(15/100)*sal where extract(year from sysdate)extract(year from hiredate)>10;

IIMC

Prashanth Kumar Head-Dept of Computers

2
7. Delete the employees, who completed 30 years of service. SQL> delete from emp1 where extract(year from sysdate)-extract(year from hiredate)>=30; 8. Determine the minimum salary of an employee and his details who joined on the same year. select * from emp where extract(year from hiredate)=(select extract(year from hiredate) from emp where sal=(select min(sal) from emp)) [Link] the count of employees who are taking commission. SQL> select count(*) "emp getting comm" from emp where comm is not null and comm<>0; 10. Create a view which contains employee names and their manager names working in sales department. SQL> create view sales_emp as select [Link] "AENA ME" ,[Link] "MENAME" from associate,manager where [Link]=[Link] and [Link]=(select deptno from dept where dname='SALES'); SQL> select * from sales_emp; 11. Determine the names of employee who earn more than their managers. SQL> select [Link], [Link], [Link] from associate, manager where [Link]=[Link] and [Link]>[Link]; 12. Determine the name of employees, who take the highest salary in their departments. SQL> select ename,deptno,sal from emp where sal in(select max(sal) from emp group by deptno); 13. Determine the employees, who located at the same place. SQL> select [Link],[Link],[Link],[Link],[Link] from emp ,dept where [Link]=[Link] order by [Link] 14. Determine the employees whose total salary is like the minimum salary of any department. select * from emp where sal*12 in(select max(sal) from emp group by deptno); [Link] the department does not contain any employees. SQL> select dname from dept where deptno not in(select deptno from emp);

IIMC

Prashanth Kumar Head-Dept of Computers