Oracle Practice SQL Queries 02:

Last Post Oracle Practice SQL Queries 01:

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?

SELECT job, COUNT (*)
FROM emp
GROUP BY job
HAVING COUNT (*) > 3;


12) Display the name of employees who earn Highest Salary?

SELECT ename, sal
FROM emp
WHERE sal >= (SELECT MAX (sal)
FROM emp);


13) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?

SELECT ename, empno
FROM emp
WHERE sal = (SELECT MAX (sal)
FROM emp
WHERE job = 'CLERK') AND job = 'CLERK';


14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX (sal)
FROM emp
WHERE job = 'CLERK') AND job = 'SALESMAN';


15) Display the names of clerks who earn a salary more than the lowest Salary of any Salesman?

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MIN (sal)
FROM emp
WHERE job = 'SALESMAN') AND job = 'CLERK';


16) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?

SELECT ename, sal
FROM emp
WHERE sal > ALL (SELECT sal
FROM emp
WHERE ename = 'JONES' OR ename = 'SCOTT');


17) Display the names of employees who earn Highest salary in their respective departments?

SELECT ename, sal, deptno
FROM emp
WHERE sal IN (SELECT MAX (sal)
FROM emp
GROUP BY deptno);

18) Display the names of employees who earn Highest salaries in their respective job Groups?

SELECT ename, job
FROM emp
WHERE sal IN (SELECT MAX (sal)
FROM emp
GROUP BY job);

19)Display employee names who are working in Accounting department?

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = 'ACCOUNTING';

20) Display the employee names who are Working in Chicago?

SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO';


21) Display the job groups having Total Salary greater than the maximum salary for Managers?

SELECT job, SUM (sal)
FROM emp
GROUP BY job
HAVING SUM (sal) > (SELECT MAX (sal)
FROM emp
WHERE job = 'MANAGER');


22) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?

SELECT ename, deptno
FROM emp
WHERE sal > ANY (SELECT MIN (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno) AND deptno = 10;


23) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?

SELECT ename, deptno
FROM emp
WHERE sal > ALL (SELECT MAX (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno) AND deptno = 10;

24) Display the names of Employees in Upper Case?
select upper(ename) from emp;

25) Display the names of employees in Lower Case?

select Lower(ename) from emp;

26) Display the names of employees in Proper case?
select InitCap(ename)from emp;

27) Find the length of your name using Appropriate Function?
select lentgh('RAMA') from dual;

28) Display the length of all the employee names?
select length(ename) from emp;

29) Display the name of employee Concatinate with Employee Number?
select ename' 'empno from emp;

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?
select substr('Oracle',3,2) from dual;

For More Check Oracle Practice SQL Queries 03: