Search This Blog

Friday 16 March 2012

SQL Query and Answers - Part3

51. Display the name of emp who earns highest sal.
  Select ename from emp where sal=(select max(sal) from emp);


52. Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS.
  Select empno,ename from emp where job=’CLERK’ and sal=(select max(sal) from emp where job=’CLERK’);


53. Display the name of the salesman who  earns a salary more than the highest salary of any clerk.
 Select ename from emp where job=’salesman’ and sal>(select max(sal) from emp where job=’clerk’);


54. Display the names of clerks who earn salary more than that of Jame sof that of sal lesser than that of Scott.
Select ename from emp where job=’clerk’ and sal<(select sal from emp where ename =’scott’) and sal>(select sal from emp where ename=’james’);


55. Display the names of employees who earn a Sal more than that of James or that of salary greater than that of Scott.
  Select ename from emp where sal<(select sal from emp where ename=’Scott’)And sal>(select sal from emp where ename=’James’);


56.  Display the names of employees who earn highest salary in their respective departments.
  Select * from emp e where sal =(select max(sal) form emp where deptno=e.deptno)


57.  Display the names of employees who earn highest salary in their respective job groups.
  Select * from emp e where sal  in(select max(sal) form emp  group by having  e.job=job).


58. Display the employee names who are working in accountings dept.
   Select ename from emp  where deptno=(select deptn0 from dept where dname=’ACCOUNTING’);
(OR)
Select ename from emp  where deptno IN(select deptn0 from dept where dname=’ACCOUNTING’);


59. Display the employee names who are working in CHICAGO.
   Select ename from emp  where deptno=(select deptno from dept where loc=’CHICAGO’);


60. Display the job groups having total salary greater than the maximum salary for managers.
Select job,sum(sal) form emp group by job having sum(sal)>(select max(sal) from emp where job=’MANAGERS’;


61. Display the names of employee from department number 10 with slary greater than that of all employee working in other departments.
  Select ename ,sal ,deptno from emp  e  where deptno=10 and sal>any(select sal from emp where e.deptno!=deptno);


62. Display the name of employees in Uppercase
Select upper(ename) from emp;


63. Display the name of employees in Lower case
Select lower(ename) from emp;


64. Find out the length of your name using appropriate function.
 Select length(‘India’)  from dual;


65. Display the length of all employees names.
 Select sum( length(ename))  from emp;


66. Display the name of the employee concatenate with empno
   Select  ename|| empno from emp;
(or)
Select concat(ename,empno) from emp;


67. Use appropriate function and extract 3 characters staring from 2 characters from the following string ‘Oracle’ i.e the output should be ‘rac’.
  Select substr(‘oracle’,’2’3) from dual;


68.Find the first occurrence of character a from the following string ‘computer maintenance corporation’.
  Select instr(‘computer maintenance corporation’,’a’,1,1) from dual;


69. Repalce every occurrence of alphabet A with B in the string Allen’s(user translate function).
Select replace(‘Allens’,’A’,’b’) from dual;


70. Display the information from emp table.Wherever job ‘manager’ is found it should be diaplayed as boss(replace function).
  Select empno,ename  replace(job,’MANAGER’,’Boss’) JOB from emp;


71. Display empnoo,ename,deptno from emp table .Instead of display department numbers display the related department name (use decode function).
  Select e.empno ,e.ename ,d.dname from emp e ,dept d where e.deptno=d.deptno;


72. Display your age in days.
  Select round(sysdate-to_date(’15-aug-1947’))from dual;


73. Display ypur age in months.
Select floor (months_between(sysdate,’15-auf-1947’)) “age in months” from dual;


74. Display current date as 15th august Friday nineteen forty seven.
Select to_char(sysdate,’date month day year’) from dual;


75.  Display the following output for each row from emp table as ‘scott has joined the company on Wednesday 13th august nineteen ninety’.
 Select ename || ‘has joined the company on ‘ || to_char (hiredate,’day ddth month ear’) from emp;

No comments:

Post a Comment

OBIEE 12c to OAS Migration

 Hi All, I have migrated successfully few projects from BI 12c to OAS 6.4. If  anyone wants help then please reach Me! Cheers! Bose

Recent Posts