Q:1) Display the name of employees along with their annual salary(sal*12).
the name of the employee earning highest annual salary should appear first?
Ans: select ename,sal,sal*12 "Annual Salary" from emp order by "Annual Salary" desc;
Q:2)Display name,salary,Hra,pf,da,TotalSalary for each employee.
The out put should be in the order of total salary ,hra 15% of salary ,
DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?
Ans: select ename,sal SA,sal*0.15 HRA,sal*0.10 DA,sal*5/100 PF,
sal+(sal*0.15)+(sal*0.10)-(sal*.05) TOTALSALARY from emp ORDER BY TOTALSALARY DESC;
Q:3) Display Department numbers and total number of employees working in each Department?
Ans: select deptno,count(*) from emp group by deptno;
Q:4) Display the various jobs and total number of employees working in each job group?
Ans: select job,count(*) from emp group by job;
Q:5) Display department numbers and Total Salary for each Department?
Ans: select deptno,sum(sal) from emp group by deptno;
Q:6) Display department numbers and Maximum Salary from each Department?
Ans: select deptno,max(sal) from emp group by deptno;
Q:7) Display various jobs and Total Salary for each job?
Ans: select job,sum(sal) from emp group by job;
Q:8) Display each job along with min of salary being paid in each job group?
Ans: select job ,min(sal) from emp group by job;
Q:9) Display the department Number with more than three employees in each department?
Ans: select deptno ,count(*) from emp group by deptno having count(*)>3;
Q:10) Display various jobs along with total salary for each of the job
where total salary is greater than 40000?
Ans: select job,sum(sal) from emp group by job having sum(sal)>40000;
Q:11) Display the various jobs along with total number of employees in each job.The output should contain only those jobs with more than three employees?
Ans: select job,count(*) from emp group by job having count(*)>3;
Q:12) Display the name of employee who earn Highest Salary?
Ans: select ename, sal from emp where sal>=(select max(sal) from emp );
Q:13) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?
Ans: select ename,empno from emp where sal=(select max(sal) from emp where
job='CLERK') and job='CLERK' ;
Q:14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?
Ans: select ename,sal from emp where sal>(select max(sal) from emp
where job='CLERK') AND job='SALESMAN';
Q:15) Display the names of clerks who earn a salary more than the lowest Salary of any Salesman?
Ans: select ename,sal from emp where sal>(select min(sal) from emp where job='SALESMAN') and job='CLERK';
Q:16) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?
Ans: select ename,sal from emp where sal>all(select sal from emp where
ename='JONES' OR ename='SCOTT');
Q:17) Display the names of employees who earn Highest salary in their respective departments?
Ans: select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);
Q:18) Display the names of employees who earn Highest salaries in their respective job Groups?
Ans: select ename,job from emp where sal in (select max(sal) from emp group by job);
Q:19) Display employee names who are working in Accounting department?
Ans: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';
Q:20) Display the employee names who are Working in Chicago?
Ans: select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO';
Q:21) Display the job groups having Total Salary greater than the maximum salary for Managers?
Ans: select job ,sum(sal) from emp group by job having sum(sal) >(select max(sal) from emp where job='MANAGER');
Q:22) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?
Ans: select ename,deptno from emp where sal>any(select min(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
Q:23) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?
Ans: select ename,deptno from emp where sal>all(select max(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
Q:24) Display the names of employees in Upper Case?
Ans: select upper(ename) from emp;
Q:25) Display the names of employees in Lower Case?
Ans: select Lower(ename) from emp;
Q:26) Display the names of employees in Proper case?
Ans: select InitCap(ename)from emp;
Q:27) Find the length of your name using Appropriate Function?
Ans: select lentgh('SRINIVASARAO') from dual;
Q:28) Display the length of all the employee names?
Ans: select length(ename) from emp;
Q:29) Display the name of employee Concatinate with Employee Number?
Ans: select ename||' '||empno from emp;
Q:30) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
Ans: select substr('Oracle',3,2) from dual;
Q:31) Find the first occurance of character a from the following string Computer Maintenance Corporation?
Ans: select lstr('Computer Maintenance Corporation','a' ) from dual;
Q:32) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)?
Ans: select translate('Alliens','A','B') from Dual;
Q:33) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
Ans: select ename ,replace(job,'MANAGER','BOSS') from emp;
Q:34) Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?
Ans: select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING'
,20,'RESEARCH',30,'SALES','OPERATIONS')DName from emp;
Q:35) Display your Age in Days?
Ans: select sysdate-to_date('30-jul-1977') from dual;
Q:36) Display your Age in Months?
Ans: select months_between(sysdate,to_date('30-jul-1977')) from dual;
Q:37) Display current date as 15th August Friday Nineteen Nienty Seven?
Ans: select To_char(sysdate,'ddth Month Day year') from dual;
Q:38) Display the following output for each row from emp table?
Ans: Q:39
Q:39) Scott has joined the company on 13th August ninteen ninety?
Ans: select empno,ename,to_char(Hiredate,'Day ddth Month year') from emp;
Q:40) Find the nearest Saturday after Current date?
Ans: select next_day(sysdate,'Saturday') from dual;
Q:41) Display the current time?
Ans: select To_Char(sysdate,'HH:MI:SS') from dual;
Q:42) Display the date three months before the Current date?
Ans: select Add_months(sysdate,-3) from dual;
Q:43) Display the common jobs from department number 10 and 20?
Ans: select job from emp where job in (select job from emp where deptno=20) and deptno=10;
Q:44) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
Ans: select Distinct job from emp where deptno in(10,20);
Q:45) Display the jobs which are unique to department 10?
Ans: select job from emp where deptno=10;
Q:46) Display the details of those employees who do not have any person working under him?
Ans: select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null );
Q:47)Display the details of those employees who are in sales department and grade is 3?
Ans: select e.ename,d.dname,grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3;
Q:48) Display those who are not managers?
Ans: select ename from emp where job!='MANAGER';
Q:49) Display those employees whose name contains not less than 4 characters?
Ans: select ename from emp where length(ename)>=4;
Q:50) Display those department whose name start with"S" while location name ends with "K"?
Ans: select e.ename,d.loc from emp e ,dept d where d.loc like('%K') and ename like('S%');
Q:51) Display those employees whose manager name is Jones?
Ans: select e.ename Superior,e1.ename Subordinate from emp e,e1 where e.empno=e1.mgr and e.ename='JONES';
Q:52) Display those employees whose salary is more than 3000 after giving 20% increment?
Ans: select ename,sal,(sal+(sal*0.20)) from emp where (sal+(sal*0.20))>3000;
Q:53) Display all employees with their department names?
Ans: select e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
Q:54) Display ename who are working in sales department?
Ans: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';
Q:56) Display employee name,dept name,salary,and commission for those sal in between 2000 to 5000 while location is Chicago?
Ans: Select e.ename,d.dname,e.sal,e.comm from emp e,dept d where e.deptno=d.deptno and sal between 2000 and 5000;
Q:57) Display those employees whose salary is greater than his managers salary?
Ans: Select e.ename,e.sal,e1.ename,e1.sal from emp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;
Q:58) Display those employees who are working in the same dept where his manager is work?
Ans: select e.ename,e.deptno,e1.ename,e1.deptno from emp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;
Q:59) Display those employees who are not working under any Manager?
Ans: select ename from emp where mgr is null;
Q:60) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?
Ans: select ename,grade,deptno,sal from emp ,salgrade where ( grade,sal) in
( select grade,sal from salgrade,emp where sal between losal and hisal)
and grade!=4 and deptno in (10,30) and hiredate<'31-Dec-82';
Q:61) Update the salary of each employee by 10% increment who are not eligible for commission?
Ans: update emp set sal= (sal+(sal*0.10)) where comm is null;
Q:62) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
Ans: select e.ename,e.hiredate,d.loc from emp e,dept d where
e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in('NEW YORK','CHICAGO');
Q:63) Display employee name ,job,deptname,loc for all who are working as manager?
Ans: select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno
and e.empno in (select mgr from emp where mgr is not null);
Q:64) Display those employees whose manager name is jones and also display their manager name?
Ans: select e.ename sub,e1.ename from emp e,e1 where e.mgr=e1.empno and e1.ename='JONES';
Q:65) Display name and salary of ford if his salary is equal to hisal of his grade?
Ans: select ename,grade,hisal,sal from emp,salgrade where ename='FORD' and sal=hisal;
OR
select grade,sal,hisal from emp,salgrade where ename='FORD' and sal between losal and hisal;
OR
select ename,sal,hisal,grade from emp,salgrade where ename='FORD'
and (grade,sal) in (select grade,hisal from salgrade,emp where
sal between losal and hisal);
Q66) Display employee name ,job,deptname,his manager name ,his grade and make an under department wise?
Ans: select e.ename sub,e1.ename sup,e.job,d.dname ,grade from emp e,e1,salgrade,dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno group by d.deptno,e.ename,e1.ename,e.job,d.dname,grade;
OR
select e.ename sub,e1.ename sup,e.job,d.dname ,grade from emp e,e1,salgrade,tvsdept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno;
Q:67) List out all the employee names ,job,salary,grade and deptname for every one in a company except 'CLERK' . Sort on salary display the highest salary?
Ans: select e.ename ,e.job,e.sal,d.dname ,grade from emp e,salgrade,dept d where (e.deptno=d.deptno and e.sal between losal and hisal ) order by e.sal desc;
Q:68) Display employee name,job abd his manager .Display also employees who are with out managers?
Ans: select e.ename ,e1.ename,e.job,e.sal,d.dname from emp e,e1,dept d where e.mgr=e1.empno(+) and e.deptno=d.deptno;
Q:69) Display Top 5 employee of a Company?
Ans:
Q:70) Display the names of those employees who are getting the highest salary?
Ans: select ename,sal from emp where sal in (select max(sal) from emp);
Q:71) Display those employees whose salary is equal to average of maximum and minimum?
Ans: select * from emp
where sal=(select (max(sal)+min(sal))/2 from emp);
Q:72) Select count of employees in each department where count >3?
Ans: select count(*) from emp group by deptno having count(*)>3
Q:73) Display dname where atleast three are working and display only deptname?
Ans: select d.dname from dept d, emp e where e.deptno=d.deptno group by d.dname having count(*)>3;
Q:74) Display name of those managers name whose salary is more than average salary of Company?
Ans: select distinct e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal> (select avg(sal) from emp);
Q:75) Display those managers name whose salary is more than average salary salary of his employees?
Ans: select distinct e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal>any (select avg(sal) from emp group by deptno);
Q:76) Display employee name,sal,comm and netpay for those employees whose netpay is greater than or equal to any other employee salary of the company?
Ans: select ename,sal,NVL(comm,0),sal+NVL(comm,0) from emp where
sal+NVL(comm,0) >any (select e.sal from emp e );
Q:77) Display those employees whose salary is less than his manager but more than salary of other managers?
Ans: select e.ename sub,e.sal from emp e,e1,dept d where
e.deptno=d.deptno and e.mgr=e1.empno
and e.sal<e1.sal
and e.sal >any (select e2.sal from emp e2, e,dept d1 where
e.mgr=e2.empno and d1.deptno=e.deptno);
Q:78) Display all employees names with total sal of company with each employee name?
Ans:
Q:79) Find the last 5(least) employees of company?
Ans:
Q:80) Find out the number of employees whose salary is greater than their managers salary?
Ans: select e.ename,e.sal,e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal;
Q:81) Display the manager who are not working under president but they are working under any other manager?
Ans: select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job!='PRESIDENT';
Q:82) Delete those department where no employee working?
Ans: delete from emp where empno is null;
Q:83) Delete those records from emp table whose deptno not available in dept table?
Ans: delete from emp e where e.deptno not in (select deptno from dept);
Q:84) Display those enames whose salary is out of grade available in salgrade table?
Ans: select empno,sal from emp where sal<(select min(LOSAL) from salgrade )
OR sal>(select max(hisal) from salgrade);
Q:85) Display employee name,sal,comm and whose netpay is greater than any other in the company?
Ans: select ename,sal,comm,sal+comm from emp where sal+comm>any
(select sal+comm from emp );
Q:86) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?
Ans: select empno, hiredate,sysdate, to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')
from emp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30;
Q:87) Display those employees whose salary is odd value?
Ans: select ename ,sal from emp where mod(sal,2)!=0;
Q:88) Display those employees whose salary contains atleast 3 digits?
Ans: select ename,sal from emp where length(sal)=3;
Q:89) Display those employees who joined in the company in the month of Dec?
Ans: Select empno,ename from emp where trim(to_char(hiredate,'Mon'))=trim('DEC');
Q:90) Display those employees whose name contains A?
Ans: select ename from emp where ename like('%A%');
Q:91) Display those employees whose deptno is available in salary?
Ans: select ename,sal from emp where deptno in (select distinct sal from emp);
Q:92) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
Ans: select empno,hiredate,sal from emp where trim(substr(hiredate,1,2))=trim(substr(sal,-2,2));
or
select hiredate,sal from emp where to_Char(hiredate,'dd')=trim(substr(sal,-2,2));
Q:93) Display those employeess whose 10% of salary is equal to the year joining?
Ans: select ename ,sal,0.10*sal from emp where 0.10*sal=trim(to_char(hiredate,'yy'));
Q:94) Display those employees who are working in sales or research?
Ans: select e.ename from emp e ,dept d where e.deptno=d.deptno and d.dname in('SALES','RESEARCH');
Q:95) Display the grade of jones?
Ans: select ename,grade from emp,salgrade where ( grade,sal) =
(select grade,sal from salgrade,emp where sal between losal and hisal and ename='JONES');
Q:96) Display those employees who joined the company before 15th of the month?
Ans: select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';
Q:97) Display those employees who has joined before 15th of the month?
Ans: select ename ,hiredate from emp where hiredate<'15-Jul-02'
Q:98) Delete those records where no of employees in particular department is less than 3?
Ans: delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3 ;
Q:99A) Delete those employeewho joined the company 10 years back from today?
Ans: delete from emp where empno in (select empno from emp
where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10);
Q:99B) Display the deptname the number of characters of which is equal to no of employee in any other department?
Ans:
Q:100) Display the deptname where no employee is working?
Ans: select deptno from emp where empno is null;
the name of the employee earning highest annual salary should appear first?
Ans: select ename,sal,sal*12 "Annual Salary" from emp order by "Annual Salary" desc;
Q:2)Display name,salary,Hra,pf,da,TotalSalary for each employee.
The out put should be in the order of total salary ,hra 15% of salary ,
DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?
Ans: select ename,sal SA,sal*0.15 HRA,sal*0.10 DA,sal*5/100 PF,
sal+(sal*0.15)+(sal*0.10)-(sal*.05) TOTALSALARY from emp ORDER BY TOTALSALARY DESC;
Q:3) Display Department numbers and total number of employees working in each Department?
Ans: select deptno,count(*) from emp group by deptno;
Q:4) Display the various jobs and total number of employees working in each job group?
Ans: select job,count(*) from emp group by job;
Q:5) Display department numbers and Total Salary for each Department?
Ans: select deptno,sum(sal) from emp group by deptno;
Q:6) Display department numbers and Maximum Salary from each Department?
Ans: select deptno,max(sal) from emp group by deptno;
Q:7) Display various jobs and Total Salary for each job?
Ans: select job,sum(sal) from emp group by job;
Q:8) Display each job along with min of salary being paid in each job group?
Ans: select job ,min(sal) from emp group by job;
Q:9) Display the department Number with more than three employees in each department?
Ans: select deptno ,count(*) from emp group by deptno having count(*)>3;
Q:10) Display various jobs along with total salary for each of the job
where total salary is greater than 40000?
Ans: select job,sum(sal) from emp group by job having sum(sal)>40000;
Q:11) Display the various jobs along with total number of employees in each job.The output should contain only those jobs with more than three employees?
Ans: select job,count(*) from emp group by job having count(*)>3;
Q:12) Display the name of employee who earn Highest Salary?
Ans: select ename, sal from emp where sal>=(select max(sal) from emp );
Q:13) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?
Ans: select ename,empno from emp where sal=(select max(sal) from emp where
job='CLERK') and job='CLERK' ;
Q:14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?
Ans: select ename,sal from emp where sal>(select max(sal) from emp
where job='CLERK') AND job='SALESMAN';
Q:15) Display the names of clerks who earn a salary more than the lowest Salary of any Salesman?
Ans: select ename,sal from emp where sal>(select min(sal) from emp where job='SALESMAN') and job='CLERK';
Q:16) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?
Ans: select ename,sal from emp where sal>all(select sal from emp where
ename='JONES' OR ename='SCOTT');
Q:17) Display the names of employees who earn Highest salary in their respective departments?
Ans: select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);
Q:18) Display the names of employees who earn Highest salaries in their respective job Groups?
Ans: select ename,job from emp where sal in (select max(sal) from emp group by job);
Q:19) Display employee names who are working in Accounting department?
Ans: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';
Q:20) Display the employee names who are Working in Chicago?
Ans: select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO';
Q:21) Display the job groups having Total Salary greater than the maximum salary for Managers?
Ans: select job ,sum(sal) from emp group by job having sum(sal) >(select max(sal) from emp where job='MANAGER');
Q:22) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?
Ans: select ename,deptno from emp where sal>any(select min(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
Q:23) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?
Ans: select ename,deptno from emp where sal>all(select max(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
Q:24) Display the names of employees in Upper Case?
Ans: select upper(ename) from emp;
Q:25) Display the names of employees in Lower Case?
Ans: select Lower(ename) from emp;
Q:26) Display the names of employees in Proper case?
Ans: select InitCap(ename)from emp;
Q:27) Find the length of your name using Appropriate Function?
Ans: select lentgh('SRINIVASARAO') from dual;
Q:28) Display the length of all the employee names?
Ans: select length(ename) from emp;
Q:29) Display the name of employee Concatinate with Employee Number?
Ans: select ename||' '||empno from emp;
Q:30) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
Ans: select substr('Oracle',3,2) from dual;
Q:31) Find the first occurance of character a from the following string Computer Maintenance Corporation?
Ans: select lstr('Computer Maintenance Corporation','a' ) from dual;
Q:32) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)?
Ans: select translate('Alliens','A','B') from Dual;
Q:33) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
Ans: select ename ,replace(job,'MANAGER','BOSS') from emp;
Q:34) Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?
Ans: select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING'
,20,'RESEARCH',30,'SALES','OPERATIONS')DName from emp;
Q:35) Display your Age in Days?
Ans: select sysdate-to_date('30-jul-1977') from dual;
Q:36) Display your Age in Months?
Ans: select months_between(sysdate,to_date('30-jul-1977')) from dual;
Q:37) Display current date as 15th August Friday Nineteen Nienty Seven?
Ans: select To_char(sysdate,'ddth Month Day year') from dual;
Q:38) Display the following output for each row from emp table?
Ans: Q:39
Q:39) Scott has joined the company on 13th August ninteen ninety?
Ans: select empno,ename,to_char(Hiredate,'Day ddth Month year') from emp;
Q:40) Find the nearest Saturday after Current date?
Ans: select next_day(sysdate,'Saturday') from dual;
Q:41) Display the current time?
Ans: select To_Char(sysdate,'HH:MI:SS') from dual;
Q:42) Display the date three months before the Current date?
Ans: select Add_months(sysdate,-3) from dual;
Q:43) Display the common jobs from department number 10 and 20?
Ans: select job from emp where job in (select job from emp where deptno=20) and deptno=10;
Q:44) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
Ans: select Distinct job from emp where deptno in(10,20);
Q:45) Display the jobs which are unique to department 10?
Ans: select job from emp where deptno=10;
Q:46) Display the details of those employees who do not have any person working under him?
Ans: select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null );
Q:47)Display the details of those employees who are in sales department and grade is 3?
Ans: select e.ename,d.dname,grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3;
Q:48) Display those who are not managers?
Ans: select ename from emp where job!='MANAGER';
Q:49) Display those employees whose name contains not less than 4 characters?
Ans: select ename from emp where length(ename)>=4;
Q:50) Display those department whose name start with"S" while location name ends with "K"?
Ans: select e.ename,d.loc from emp e ,dept d where d.loc like('%K') and ename like('S%');
Q:51) Display those employees whose manager name is Jones?
Ans: select e.ename Superior,e1.ename Subordinate from emp e,e1 where e.empno=e1.mgr and e.ename='JONES';
Q:52) Display those employees whose salary is more than 3000 after giving 20% increment?
Ans: select ename,sal,(sal+(sal*0.20)) from emp where (sal+(sal*0.20))>3000;
Q:53) Display all employees with their department names?
Ans: select e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
Q:54) Display ename who are working in sales department?
Ans: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';
Q:56) Display employee name,dept name,salary,and commission for those sal in between 2000 to 5000 while location is Chicago?
Ans: Select e.ename,d.dname,e.sal,e.comm from emp e,dept d where e.deptno=d.deptno and sal between 2000 and 5000;
Q:57) Display those employees whose salary is greater than his managers salary?
Ans: Select e.ename,e.sal,e1.ename,e1.sal from emp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;
Q:58) Display those employees who are working in the same dept where his manager is work?
Ans: select e.ename,e.deptno,e1.ename,e1.deptno from emp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;
Q:59) Display those employees who are not working under any Manager?
Ans: select ename from emp where mgr is null;
Q:60) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?
Ans: select ename,grade,deptno,sal from emp ,salgrade where ( grade,sal) in
( select grade,sal from salgrade,emp where sal between losal and hisal)
and grade!=4 and deptno in (10,30) and hiredate<'31-Dec-82';
Q:61) Update the salary of each employee by 10% increment who are not eligible for commission?
Ans: update emp set sal= (sal+(sal*0.10)) where comm is null;
Q:62) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
Ans: select e.ename,e.hiredate,d.loc from emp e,dept d where
e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in('NEW YORK','CHICAGO');
Q:63) Display employee name ,job,deptname,loc for all who are working as manager?
Ans: select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno
and e.empno in (select mgr from emp where mgr is not null);
Q:64) Display those employees whose manager name is jones and also display their manager name?
Ans: select e.ename sub,e1.ename from emp e,e1 where e.mgr=e1.empno and e1.ename='JONES';
Q:65) Display name and salary of ford if his salary is equal to hisal of his grade?
Ans: select ename,grade,hisal,sal from emp,salgrade where ename='FORD' and sal=hisal;
OR
select grade,sal,hisal from emp,salgrade where ename='FORD' and sal between losal and hisal;
OR
select ename,sal,hisal,grade from emp,salgrade where ename='FORD'
and (grade,sal) in (select grade,hisal from salgrade,emp where
sal between losal and hisal);
Q66) Display employee name ,job,deptname,his manager name ,his grade and make an under department wise?
Ans: select e.ename sub,e1.ename sup,e.job,d.dname ,grade from emp e,e1,salgrade,dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno group by d.deptno,e.ename,e1.ename,e.job,d.dname,grade;
OR
select e.ename sub,e1.ename sup,e.job,d.dname ,grade from emp e,e1,salgrade,tvsdept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno;
Q:67) List out all the employee names ,job,salary,grade and deptname for every one in a company except 'CLERK' . Sort on salary display the highest salary?
Ans: select e.ename ,e.job,e.sal,d.dname ,grade from emp e,salgrade,dept d where (e.deptno=d.deptno and e.sal between losal and hisal ) order by e.sal desc;
Q:68) Display employee name,job abd his manager .Display also employees who are with out managers?
Ans: select e.ename ,e1.ename,e.job,e.sal,d.dname from emp e,e1,dept d where e.mgr=e1.empno(+) and e.deptno=d.deptno;
Q:69) Display Top 5 employee of a Company?
Ans:
Q:70) Display the names of those employees who are getting the highest salary?
Ans: select ename,sal from emp where sal in (select max(sal) from emp);
Q:71) Display those employees whose salary is equal to average of maximum and minimum?
Ans: select * from emp
where sal=(select (max(sal)+min(sal))/2 from emp);
Q:72) Select count of employees in each department where count >3?
Ans: select count(*) from emp group by deptno having count(*)>3
Q:73) Display dname where atleast three are working and display only deptname?
Ans: select d.dname from dept d, emp e where e.deptno=d.deptno group by d.dname having count(*)>3;
Q:74) Display name of those managers name whose salary is more than average salary of Company?
Ans: select distinct e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal> (select avg(sal) from emp);
Q:75) Display those managers name whose salary is more than average salary salary of his employees?
Ans: select distinct e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal>any (select avg(sal) from emp group by deptno);
Q:76) Display employee name,sal,comm and netpay for those employees whose netpay is greater than or equal to any other employee salary of the company?
Ans: select ename,sal,NVL(comm,0),sal+NVL(comm,0) from emp where
sal+NVL(comm,0) >any (select e.sal from emp e );
Q:77) Display those employees whose salary is less than his manager but more than salary of other managers?
Ans: select e.ename sub,e.sal from emp e,e1,dept d where
e.deptno=d.deptno and e.mgr=e1.empno
and e.sal<e1.sal
and e.sal >any (select e2.sal from emp e2, e,dept d1 where
e.mgr=e2.empno and d1.deptno=e.deptno);
Q:78) Display all employees names with total sal of company with each employee name?
Ans:
Q:79) Find the last 5(least) employees of company?
Ans:
Q:80) Find out the number of employees whose salary is greater than their managers salary?
Ans: select e.ename,e.sal,e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal;
Q:81) Display the manager who are not working under president but they are working under any other manager?
Ans: select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job!='PRESIDENT';
Q:82) Delete those department where no employee working?
Ans: delete from emp where empno is null;
Q:83) Delete those records from emp table whose deptno not available in dept table?
Ans: delete from emp e where e.deptno not in (select deptno from dept);
Q:84) Display those enames whose salary is out of grade available in salgrade table?
Ans: select empno,sal from emp where sal<(select min(LOSAL) from salgrade )
OR sal>(select max(hisal) from salgrade);
Q:85) Display employee name,sal,comm and whose netpay is greater than any other in the company?
Ans: select ename,sal,comm,sal+comm from emp where sal+comm>any
(select sal+comm from emp );
Q:86) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?
Ans: select empno, hiredate,sysdate, to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')
from emp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30;
Q:87) Display those employees whose salary is odd value?
Ans: select ename ,sal from emp where mod(sal,2)!=0;
Q:88) Display those employees whose salary contains atleast 3 digits?
Ans: select ename,sal from emp where length(sal)=3;
Q:89) Display those employees who joined in the company in the month of Dec?
Ans: Select empno,ename from emp where trim(to_char(hiredate,'Mon'))=trim('DEC');
Q:90) Display those employees whose name contains A?
Ans: select ename from emp where ename like('%A%');
Q:91) Display those employees whose deptno is available in salary?
Ans: select ename,sal from emp where deptno in (select distinct sal from emp);
Q:92) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
Ans: select empno,hiredate,sal from emp where trim(substr(hiredate,1,2))=trim(substr(sal,-2,2));
or
select hiredate,sal from emp where to_Char(hiredate,'dd')=trim(substr(sal,-2,2));
Q:93) Display those employeess whose 10% of salary is equal to the year joining?
Ans: select ename ,sal,0.10*sal from emp where 0.10*sal=trim(to_char(hiredate,'yy'));
Q:94) Display those employees who are working in sales or research?
Ans: select e.ename from emp e ,dept d where e.deptno=d.deptno and d.dname in('SALES','RESEARCH');
Q:95) Display the grade of jones?
Ans: select ename,grade from emp,salgrade where ( grade,sal) =
(select grade,sal from salgrade,emp where sal between losal and hisal and ename='JONES');
Q:96) Display those employees who joined the company before 15th of the month?
Ans: select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';
Q:97) Display those employees who has joined before 15th of the month?
Ans: select ename ,hiredate from emp where hiredate<'15-Jul-02'
Q:98) Delete those records where no of employees in particular department is less than 3?
Ans: delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3 ;
Q:99A) Delete those employeewho joined the company 10 years back from today?
Ans: delete from emp where empno in (select empno from emp
where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10);
Q:99B) Display the deptname the number of characters of which is equal to no of employee in any other department?
Ans:
Q:100) Display the deptname where no employee is working?
Ans: select deptno from emp where empno is null;
No comments:
Post a Comment