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;
Q:101) Display those employees who are working as manager?
Ans: select e2.ename from emp
e1,e2 where e1.mgr=e2.empno and e2.empno is not null;
Q:102) Count th number of employees who are working as managers
(Using set opetrator)?
Ans: select d.dname from dept d
where length(d.dname) in (select count(*) from emp e where e.deptno!=d.deptno
group by e.deptno);
Q:103) Display the name of the dept those employees who joined the
company on the same date?
Ans: select a.ename,b.ename
from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;
Q:104) Display those employees whose grade is equal to any number
of sal but not equal to first number of sal?
Ans: select ename,sal,grade
,substr(sal,grade,1) from emp,salgrade where
grade!=substr(sal,1,1)
and grade = substr(sal,grade,1)
and
sal between losal and hisal;
Q:105) Count the no of employees working as manager using set
operation?
Ans: Select count(empno) from
emp where
empno
in (select a.empno from emp a
intersect
select
b.mgr from emp b);
Q:106) Display the name of employees who joined the company on the
same date?
Ans: select a.ename,b.ename
from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;
Q:107) Display the manager who is having maximum number of
employees working under him?
Ans: select e2.ename,count(*)
from emp e1,e2 where e1.mgr=e2.empno group by e2.ename Having count(*)=(select
max(count(*)) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename);
Q:108) List out the employee name and salary increased by 15% and
express as whole number of Dollars?
Ans: select
ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from emp;
Q:109) Produce the output of the emptable "EMPLOYEE_AND
JOB" for ename and job ?
Ans: select
ename"EMPLOYEE_AND",job"JOB" FROM EMP;
Q:110) Lust of employees with hiredate in the format of 'June 4
1988'?
Ans: select
ename,to_char(hiredate,'Month dd yyyy') from emp;
Q:111) print list of employees displaying 'Just salary' if more
than 1500 if exactly 1500 display 'on taget' if less than 1500 display below
1500?
Ans: select ename,sal,
(
case
when sal < 1500 then
'Below_Target'
when
sal=1500 then
'On_Target'
when
sal > 1500 then
'Above_Target'
else
'kkkkk'
end
)
from
emp;
Q:112) Which query to calculate the length of time any employee
has been with the company
Ans: select
hiredate,to_char(hiredate,' HH:MI:SS') FROM emp;
Q:113) Given a string of the format 'nn/nn' . Verify that the
first and last 2 characters are numbers .And that the middle character is '/'
Print the expressions 'Yes' IF valid 'NO' of not valid . Use the following
values to test your solution'12/54',01/1a,'99/98'?
Ans:
Q:114) Employes hire on OR Before 15th of any month are paid on
the last friday of that month those hired after 15th are paid the last friday
of th following month .print a list of employees .their hiredate and first pay
date sort those who se salary contains first digit of their deptno?
Ans: select ename,hiredate,
LAST_DAY ( next_day(hiredate,'Friday')),
(
case
when to_char(hiredate,'dd') <=('15') then
LAST_DAY
( next_day(hiredate,'Friday'))
when
to_char(hiredate,'dd')>('15') then
LAST_DAY(
next_day(add_months(hiredate,1),'Friday'))
end
)
from
emp;
Q:115) Display those managers who are getting less than his
employees salary?
Ans: select a.empno,a.ename
,a.sal,b.sal,b.empno,b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal;
Q:116) Print the details of employees who are subordinates to
BLAKE?
Ans: select a.empno,a.ename
,b.ename from emp a, emp b where a.mgr=b.empno
and b.ename='BLAKE';
No comments:
Post a Comment