Query 2

61) 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 tvsemp where sal>any(select min(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
62) 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 tvsemp where sal>all(select max(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;
63) Display the names of mployees in Upper Case?
Ans: select upper(ename) from tvsemp;
64) Display the names of employees in Lower Case?
Ans: select Lower(ename) from tvsemp;
65) Display the names of employees in Proper case?
Ans: select InitCap(ename)from tvsemp;
Q:66) Find the length of your name using Appropriate Function?
Ans: select lentgh('RAMA') from dual;
67) Display the length of all the employee names?
Ans: select length(ename) from tvsemp;
68) Display the name of employee Concatinate with Employee Number?
Ans: select ename' 'empno from tvsemp;
69) 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;
70) Find the first occurance of character a from the following string Computer Maintenance Corporation?
Ans: select lstr('Computer Maintenance Corporation','a' ) from dual;
71) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)?
Ans: select translate('Alliens','A','B') from Dual;
72) 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 tvsemp;
73) Display empno,ename,deptno from tvsemp 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 tvsemp;
74) Display your Age in Days?
Ans: select sysdate-to_date('30-jul-1977') from dual;
75) Display your Age in Months?
Ans: select months_between(sysdate,to_date('30-jul-1977')) from dual;
76) Display current date as 15th August Friday Nineteen Nienty Seven?
Ans: select To_char(sysdate,'ddth Month Day year') from dual;
77) Display the following output for each row from tvsemp table?
Ans: Q:78
78) Scott has joined the company on 13th August ninteen ninety?
Ans: select empno,ename,to_char(Hiredate,'Day ddth Month year') from tvsemp;
79) Find the nearest Saturday after Current date?
Ans: select next_day(sysdate,'Saturday') from dual;
80) Display the current time?
Ans: select To_Char(sysdate,'HH:MI:SS') from dual;
81) Display the date three months before the Current date?
Ans: select Add_months(sysdate,-3) from dual
82) Display the common jobs from department number 10 and 20?
Ans: select job from tvsemp where job in (select job from tvsemp where deptno=20) and deptno=10;
83) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
Ans: select Distinct job from tvsemp where deptno in(10,20);
84) Display the jobs which are unique to department 10?
Ans: select job from tvsemp where deptno=10;
85) Display the details of those employees who do not have any person working under him?
Ans: select empno,ename,job from tvsemp where empno not in (select mgr from tvsemp where mgr is not null );
86) 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;
87) Display thoes who are not managers?
Ans: select ename from tvsemp where job!='MANAGER';
88) Display those employees whose name contains not less than 4 characters?
Ans: select ename from tvsemp where length(ename)>=4
89) Display those department whose name start with"S" while location name ends with "K"?
Ans: select e.ename,d.loc from tvsemp e ,tvsdept d where d.loc like('%K') and ename like('S%')
90) Display those employees whose manager name is Jones?
Ans: select e.ename Superior,e1.ename Subordinate from tvsemp e,e1 where e.empno=e1.mgr and e.ename='JONES';
91) Display those employees whose salary is more than 3000 after giving 20% increment?
Ans: select ename,sal,(sal+(sal*0.20)) from tvsemp where (sal+(sal*0.20))>3000;
92) Display all employees with their department names?
Ans: select e.ename,d.dname from tvsemp e, tvsdept d where e.deptno=d.deptno
93) 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';
94) 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 tvsemp e,dept d where e.deptno=d.deptno and sal between 2000 and 5000;
95) Display those employees whose salary is greater than his managers salary?
Ans: Select e.ename,e.sal,e1.ename,e1.sal from tvsemp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;
96) 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 tvsemp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;
97) Display those employees who are not working under any Manager?
Ans: select ename from tvsemp where mgr is null;
98) 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 tvsemp ,salgrade where ( grade,sal) in
( select grade,sal from salgrade,tvsemp where sal between losal and hisal)
and grade!=4 and deptno in (10,30) and hiredate<'31-Dec-82';
99) Update the salary of each employee by 10% increment who are not eligible for commission?
Ans: update tvsemp set sal= (sal+(sal*0.10)) where comm is null;
100) 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 tvsemp e,tvsdept d where
e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in('NEW YORK','CHICAGO');