SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4
172) DISPLAY 9th FROM THE EMP TABLE? SQL>SELECT ENAME FROM EMP
WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS
SELECT ROWID FROM EMP WHERE ROWNUM <10) select second max salary from emp;
173).display employees who sal is grater then 30000 after giving the 20% increment?
-- select * from emp where sal*120/1000>30000; Or
Select * from emp where sal+sal*20/1000>30000; Or
Select ename,(sal*0.2)+sal incsal from emp where incsal>30000;
174) display those employees whose sal is grater then his mrg sal?
--select * from emp e where sal>(select sal from emp where empno=e.mgr);
178) display employees who has joined before in the month of 15 th?
--select * from emp where to_char(hiredate,’DD’)<15;
179) display the nth max sal of the employees ?
- select * from emp e where &N=(select count(distinct a.sal) from emp a where a.sal>=e.sal);
SQL Queries:-
1.Display the details of those who do not have any person working under them? A:select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename having
count(*)=1;2.Display the details of those employees who are in sales department and grade is 3?A:select * from emp where deptno = (select deptno from dept where
dname='SALES') and sal between(select losal from salgrade where grade=3) and (select hisal from salgrade where grade=3);3.Display those who are not managers and who
are manager any one?A:4.Display those employees whose name contains
not less than 4 characters?A:select ename from emp where length(ename)>4;5.Display those department whose name starts with “S” while the location name ends with “O”? A:select dname, loc from dept where dname like ‘S%’ and loc like ‘%O’;1row selectd;6.Display those employees whose manager name is Steven?A:select first_name from employees where manager_id in ( select employee_id from employees
wherefirst_name=’Steven’); 14 rows selected.7.Display those employees whose salary is more than 3000 after giving 20% increment?A:select first_name, last_name, salary old, (salary*0.2)+salary new_salary from employees where((salary*0.2)+salary)>3000;83 rows selected8 . D i s p l a y a l l e m p l o y e e s w i t h t h e i r d e p t n a m e s ? A:select e.first_name, e.last_name, e.department_id, d.department_name from employees e,departments d where e.department_id=d.department_id;107 rows selected.9 . D i s p l a y e n a m e w h o ar e w o r k i n g i n s a l e s d e p t ? A:select first_name, last_name from employees where department_id=( select department_id fromdepartments where department_name=’Sales’);34 rows selected.10.Display
employee name, dept name, salary and comm. For those sal in between 2000 to 5000
whilelocation is Chicago?A:select ename,dname,sal,comm from emp,dept where sal between 2000 and 5000 andloc='CHICAGO' and emp.deptno=dept.deptno;1 row selected11.Display those employees whose salary is greater than his manager salary?A:select e.last_name from employees e, employees m where e.employee_id=m.manager_id
ande.salary>m.salary;12.Display those employees who are working in the same dept where his manager is working?A:select m.ename from emp e, emp m where e.empno=m.mgr and
m.deptno=e.deptno;11 rowsselected.13.Display those employees who are not working under any manager?A:select ename from emp where mgr=null;no rows selected.14.Display grade and employees name for the dept no 10 or 30 but grade is not 4 while joined the company before 31-dec-82?
A:select grade,ename from salgrade,emp where sal between losal and hisal and deptno in (10,30) andgrade<>4 and hiredate<’31-DEC-82’;7 rows selected;15.Update the salary of each employee by 10% increment who are not eligible for commission?A:update emp set
sal=sal+sal*0.1 where comm=null;16.Delete those employee who joined the company before 31-dec-82 while their dept location is New york or Chicago?17.Display employee name, job, deptname, location for all who are working as manager?A:select ename, job, dname, loc from emp, dept where mgr is not null;54 rows selected.18.Display those employees whose manager name is jones, and also display their manager name?A:select m.ename from emp e, emp m where e.empno=m.mgr and m.ename=’JONES’;2 rowsselected.19.Display name and salary of ford if his salary is equal to hisal of his grade?A:select ename, sal from emp where sal=hisal and ename=’FORD’;1 row selected,20.Display employee name, his job, his department name, his manager name, his sal, his grade and makeout an under department wise?21.List out all the employees name, job, salary, grade and department name for everyone in the companyexcept “CLERK”. Sort on salary, display the highest salary?A:select ename, job, sal , grade, dname from emp, dept, salgrade where sal between losal and hisal andemp.deptno=dept.deptno and job<>’CLERK’ order by sal desc;10 rows selected.22.Display employee name, his job and his manager. Display also employees who are without manager?A:select e.empno, e.ename, e.job, m.ename MANAGER from emp e, emp m wherem.empno(+)=e.mgr order by empno;13 rows selectd.23.Find out the top 5 earners of the company?23.Display name of those employees who are getting the highest salary.A:select ename, sal from emp where sal=( select max(sal) from emp); 1row selected.24.Display those employees whose salary is equal to average of maximum and minimum plus 75 ? (tohighlight atleast one row)A:select ename, sal from emp where sal= ( select (max(sal)+min(sal))/2+75 from emp); 1row selctd.25.Select count of employees in each department where count greater than 3?26.Display dname where atleast 3 are working and display only department name?27.Display name of those managers salary is more than average salary of employees?28.Display those managers name whose salary is more than average salary of his employees?29.Display employee name, sal, comm. And net pay for those employees whose net pay is greater than Or equal to any other employee salary of the company?A:select ename, sal, comm, sal+nvl(comm,0) NETPAY from emp where
sal+nvl(comm,0) >= any(select sal from emp);13 rows selected.30.Display those employees whose salary is less than his manager but more than salary of any other managers? Only half 31.Display all the employees names with total sal of company with each employee name? 32.Find out the last 5(least) earners of the company?33.Find out the number of employees whose salary is greater than their manager salary?A:select e.ename from emp m, emp e where m.empno=e.mgr and m.sal<e.sal;2rows selected
34.Display those manager who are not working under president but they are working under any other manager?A:select dname from emp,dept where emp.deptno not in(emp.deptno);no rows selected.35.Delete those department where no employee working? 36.Delete those records from emp table whose deptno not available in dept table/37.Display those enames whose salary is out of the grade available in salgrade table?38.Display emplyee name, sal, comm. And net pay whose net pay is greater than any other in the company?39.Display name of those employees who are going to retire 31-dec-99. If the maximum job period is 30years?40.Display those employees whose salary is ODD value?41.Display those employees whose salary contains atleast 3 digits?A:select * from emp where length(sal)>=3;42.Display those employees who joined in the company in the month of Dec?A:select ename from emp where to_char(hiredate,'MON')='DEC';43.Display those employees whose name contains “A”? A:select ename from emp where ename like('%A%');44.Display those employees whose
deptno is available in salary?45.Display those employees whose first 2 characters from hiredate = last 2 characters of salary?46.Display those employees whose 10% of salary of equal to the year of joining.A:select ename from emp where to_char(hiredate,'YY')=sal*0.1;47.Display those employee who are working in sales or research?A:select ename from emp where deptno in(select deptno from dept wheredname in('SALES','RESEARCH'));11rows selected.48.Display the grade of Jones?A:select ename,grade from emp,salgrade where sal between losal and hisal and ename=’Jones’;49.Display those employees who joined the company before 15 th
of the month?A:select ename from emp where to_char(hiredate,'DD')<15;8rows
selected.50.Delete or Display those records where no .of employees in a particular department is less than 3?and department_id is not null) order by department_id;A:select * from emp where deptno=(select deptno from emp group by deptno having count(deptno)<3);51.Delete those employees who joined the company 10 years back from today?52.Display the department name the no of characters of which is equal to no of employees in any other department?53.Display the name of the department where no employee working?54.Display those employees who are working as manager?55.Count the no of employees who are working as manager(Using set operation)?56.Display the ename of the employees who joined the company on the same date?57.Display those employees whose grade is equal to any number of sal but not equal to first number of sal?59.Display the name of employees who joined on the same date?60.Display the manager who is having maximum number of employees working under him?61.List out the employees name and salary increased by 15% and expressed as whole number of dollars? 62.Produce the output of emp table “EMPLOYEE_AND_JOB” for ename and job?63.List all
employees with hiredate in the format “june 4, 1988”64.Print a list of employees displaying “Just Salary” if more than 1500 if exactly 1500 display “On target” if less ‘Belowtarget’?