Q:1) 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:2) 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:3) 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:4) Display those employees whose salary is odd value?
Ans: select ename ,sal from emp where mod(sal,2)!=0;
Q:5) Display those employees whose salary contains atleast 3 digits?
Ans: select ename,sal from emp where length(sal)=3;
Q:6) 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:7) Display those employees whose name contains A?
Ans: select ename from emp where ename like('%A%');
Q:8) Display those employees whose deptno is available in salary?
Ans: select ename,sal from emp where deptno in (select distinct sal from emp);
Q:9) 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:10) 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:11) 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:12) 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:13) 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:14) Display those employees who has joined before 15th of the month?
Ans: select ename ,hiredate from emp where hiredate<'15-Jul-02'
Q:15) 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:16A) 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:16B) Display the deptname the number of characters of which is equal to no of employee in any other department?
Ans:
Q:17) Display the deptname where no employee is working?
Ans: select deptno from emp where empno is null;
Q:18) 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:19) 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:20) 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:21) 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:22) 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:23) 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:24) 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:25) 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:26) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?
Ans: select ename"EMPLOYEE_AND",job"JOB" FROM EMP;
Q:27) Lust of employees with hiredate in the format of 'June 4 1988'?
Ans: select ename,to_char(hiredate,'Month dd yyyy') from emp;
Q:28) 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:29) 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:3o) 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:31) 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:32) 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:33) 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
Ans: select empno,sal from emp where sal<(select min(LOSAL) from salgrade )
OR sal>(select max(hisal) from salgrade);
Q:2) 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:3) 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:4) Display those employees whose salary is odd value?
Ans: select ename ,sal from emp where mod(sal,2)!=0;
Q:5) Display those employees whose salary contains atleast 3 digits?
Ans: select ename,sal from emp where length(sal)=3;
Q:6) 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:7) Display those employees whose name contains A?
Ans: select ename from emp where ename like('%A%');
Q:8) Display those employees whose deptno is available in salary?
Ans: select ename,sal from emp where deptno in (select distinct sal from emp);
Q:9) 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:10) 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:11) 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:12) 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:13) 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:14) Display those employees who has joined before 15th of the month?
Ans: select ename ,hiredate from emp where hiredate<'15-Jul-02'
Q:15) 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:16A) 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:16B) Display the deptname the number of characters of which is equal to no of employee in any other department?
Ans:
Q:17) Display the deptname where no employee is working?
Ans: select deptno from emp where empno is null;
Q:18) 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:19) 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:20) 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:21) 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:22) 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:23) 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:24) 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:25) 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:26) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?
Ans: select ename"EMPLOYEE_AND",job"JOB" FROM EMP;
Q:27) Lust of employees with hiredate in the format of 'June 4 1988'?
Ans: select ename,to_char(hiredate,'Month dd yyyy') from emp;
Q:28) 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:29) 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:3o) 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:31) 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:32) 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:33) 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