Oracle Sql - Questions and Answers
31. Create a query that displays the first eight characters of the
employees last names and indicates the amounts of
their salaries with asterisks. Each asterisk signifies a thousand dollars.
Sort the data in descending order of
salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.
SELECT RPAD(ENAME, 8, ' ')||' '||RPAD(' ', SAL/1000+1, '*') AS EMPLOYEES_AND_THEIR_SALARIES FROM EMP ORDER BY SAL DESC; EMPLOYEES_AND_THEIR_SALARIES ---------------------------------------------------------------------------------------------------- ESWAR ************ KING ***** BLAKE *** SCOTT *** FORD *** JONES ** CLARK ** ALLEN * TURNER * MILLER * MARTIN * WARD * ESWAR * ADAMS * VENKAT * JAMES SMITH
32. Using the DECODE function, write a query that displays the grade of all
employees based on the value of the
column JOB_ID, using the following data:
PRESIDENT - A, MANAGER - B, SALESMAN -
C, CLERK - D.
SELECT JOB,DECODE(JOB, 'PRESIDENT', 'A' ,'MANAGER', 'B', 'SALESMAN', 'C', 'CLERK', 'D') AS GRADE FROM EMP; JOB G --------- - PRESIDENT A MANAGER B MANAGER B MANAGER B ANALYST ANALYST CLERK D SALESMAN C SALESMAN C SALESMAN C SALESMAN C CLERK D CLERK D CLERK D
33. Rewrite the statement in the preceding exercise using the CASE
syntax.
SELECT JOB, CASE WHEN JOB='PRESIDENT' THEN 'A' WHEN JOB='MANAGER' THEN 'B' WHEN JOB='SALESMAN' THEN 'C' WHEN JOB='CLERK' THEN 'D' END AS GRADE FROM EMP; JOB G --------- - PRESIDENT A MANAGER B MANAGER B MANAGER B ANALYST ANALYST CLERK D SALESMAN C SALESMAN C SALESMAN C SALESMAN C CLERK D CLERK D CLERK D
34. Find the highest, lowest, sum, and average salary of all employees.
Label the columns.
SELECT MAX(SAL) AS "MAXIMUM SALARY", MIN(SAL) AS "MINIMUM SALARY", SUM(SAL) AS "SUM OF SALARIES", AVG(SAL) AS "AVERAGE OF SALARIES" FROM EMP; MAXIMUM SALARY MINIMUM SALARY SUM OF SALARIES AVERAGE OF SALARIES -------------- -------------- --------------- ------------------- 12000 800 44209 2600.52941
35. Maximum, Minimum, Sum, and Average respectively. Round your results to
the nearest whole
number.
SELECT ROUND(MAX(SAL)) AS "MAXIMUM SALARY", ROUND(MIN(SAL)) AS "MINIMUM SALARY", ROUND(SUM(SAL)) AS "SUM OF SALARIES", ROUND(AVG(SAL)) AS "AVERAGE OF SALARIES" FROM EMP; MAXIMUM SALARY MINIMUM SALARY SUM OF SALARIES AVERAGE OF SALARIES -------------- -------------- --------------- ------------------- 12000 800 44209 2601
36. Modify the above query to display the minimum, maximum, sum and average
salary for each job type.
SELECT JOB, ROUND(MAX(SAL)) AS "MAXIMUM SALARY", ROUND(MIN(SAL)) AS "MINIMUM SALARY", ROUND(SUM(SAL)) AS "SUM OF SALARIES", ROUND(AVG(SAL)) AS "AVERAGE OF SALARIES" FROM EMP GROUP BY JOB; JOB MAXIMUM SALARY MINIMUM SALARY SUM OF SALARIES AVERAGE OF SALARIES --------- -------------- -------------- --------------- ------------------- CLERK 1300 800 4150 1038 SALESMAN 1900 1250 5900 1475 PRESIDENT 5500 5500 5500 5500 MANAGER 3000 2450 8425 2808 ANALYST 3000 3000 6000 3000
37. Write a query to display the number of people with the same job.
SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB; JOB COUNT(*) --------- ---------- CLERK 4 SALESMAN 4 PRESIDENT 1 MANAGER 3 ANALYST 2
38. Determine the number of managers without listing them. Label the column
Number of Managers.
SELECT COUNT(DISTINCT MGR) AS "NUMBER OF MANAGERS" FROM EMP; NUMBER OF MANAGERS ------------------ 6
39. Find the difference between the highest and lowest salaries. Label the
column DIFFERENCE.
SELECT MAX(SAL)-MIN(SAL) AS DIFFERENCE FROM EMP; DIFFERENCE ---------- 4700
40. Create a report to display the manager number and the salary of the
lowest-paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any groups where the
minimum salary is 2000 or less. Sort the
output in descending order of salary.
SELECT MGR, MIN(SAL) FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING MIN(SAL)<2000 ORDER BY MIN(SAL) DESC; MGR MIN(SAL) ---------- ---------- 7782 1300 7788 1100 7698 950 7902 800
41. Create a query to display the total number of employees and of that total,
the number of employees hired in 1980, 1981,
1982. Create appropriate column headings.
SELECT COUNT(*) AS "TOTAL EMPLOYEES", SUM(DECODE(TO_CHAR(HIREDATE, 'YY'), 80, 1, 0)) "EMPLOYEES HIRED IN 1980", SUM(DECODE(TO_CHAR(HIREDATE, 'YY'), 81, 1, 0)) "EMPLOYEES HIRED IN 1981", SUM(DECODE(TO_CHAR(HIREDATE, 'YY'), 82, 1, 0)) "EMPLOYEES HIRED IN 1982" FROM EMP; TOTAL EMPLOYEES EMPLOYEES HIRED IN 1980 EMPLOYEES HIRED IN 1981 EMPLOYEES HIRED IN 1982 --------------- ----------------------- ----------------------- ----------------------- 14 1 10 2
42. Create a matrix query to display the job, the salary for that job based on
department number, and the total salary for that job, for departments 10, 20
and 30 giving each column an appropriate heading.
SELECT JOB AS JOB_ID, SUM(DECODE(DEPTNO, 10, SAL)) "DEPT10", SUM(DECODE(DEPTNO, 20, SAL)) "DEPT20", SUM(DECODE(DEPTNO, 30, SAL)) "DEPT30", SUM(SAL) "TOTAL" FROM EMP GROUP BY JOB; JOB_ID DEPT10 DEPT20 DEPT30 TOTAL --------- ---------- ---------- ---------- ---------- CLERK 1300 1900 950 4150 SALESMAN 5900 5900 PRESIDENT 5500 5500 MANAGER 2450 2975 3000 8425 ANALYST 6000 6000
43. Using set operator display the DEPTNO, SUM(SAL) for each deptno, JOB,
SUM(SAL) for each Job and Total Salary.
SELECT '' AS JOB, DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO UNION SELECT JOB, DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB; JOB DEPTNO SUM(SAL) --------- ---------- ---------- ANALYST 20 6000 CLERK 10 1300 CLERK 20 1900 CLERK 30 950 MANAGER 10 2450 MANAGER 20 2975 MANAGER 30 3000 PRESIDENT 10 5500 SALESMAN 30 5900 10 9250 20 10875 30 9850
44. Using set operator, display the JOB and DEPTNO in employees working in
deptno 20, 10, 30 in that order.
--first execute the below command. COLUMN DUMMY NOPRINT --now execute this command. SELECT JOB, DEPTNO, 1 DUMMY FROM EMP WHERE DEPTNO=20 UNION ALL SELECT JOB, DEPTNO, 2 FROM EMP WHERE DEPTNO=10 UNION ALL SELECT JOB, DEPTNO, 3 FROM EMP WHERE DEPTNO=30 ORDER BY 3; JOB DEPTNO --------- ---------- MANAGER 20 ANALYST 20 ANALYST 20 CLERK 20 CLERK 20 PRESIDENT 10 MANAGER 10 CLERK 10 MANAGER 30 SALESMAN 30 SALESMAN 30 SALESMAN 30 SALESMAN 30 CLERK 30