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 15^th 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 13^th august nineteen ninety’.

 Select ename || ‘has joined the company on ‘ || to_char (hiredate,’day ddth month ear’) from emp;