Oracle Sql - Questions and Answers :
16. The HR department wants to run reports based on manager. Create a query
that prompts the user for a MGR and generates the empno, ename, salary and
department for that manager's employees. The HR department wants the ability
to sort the report on a selected column.
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR=&MANAGER_ID ORDER BY &COLUMNNUMBER; Enter value for manager_id: 7839 Enter value for columnnumber: 1 old 1: SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR=&MANAGER_ID ORDER BY &COLUMNNUMBER new 1: SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR=7839 ORDER BY 1 EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7566 JONES 2975 20 7698 BLAKE 3000 30 7782 CLARK 2450 10
17. Display all employee last names in which the third letter of the name is A.
SELECT ENAME FROM EMP WHERE ENAME LIKE '__A%'; ENAME ---------- BLAKE CLARK ADAMS
18. Display the last name of all employees who have both an A and S in
their ename.
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%S%'; ENAME ---------- ADAMS JAMES ESWAR ESWAR
19. Display the Ename, Job, Sal for all employees whose jobs are CLERK
and whose salary is in 800 or 950 or 1300.
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='CLERK' AND SAL IN (800, 950, 1300); ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 JAMES CLERK 950 MILLER CLERK 1300
20. Write a query to display the current date. Label the column Date.
SELECT SYSDATE "DATE" FROM DUAL; DATE --------- 28-SEP-20
21. The HR department needs a report to display the employee number, last
name, salary, and salary increased by 15.5% (expressed as a whole number)
for each employee. Label the column New Salary.
SELECT EMPNO,ENAME, SAL, ROUND(SAL+ SAL * 15.5/100) "NEW SALARY" FROM EMP; EMPNO ENAME SAL NEW SALARY ---------- ---------- ---------- ---------- 7839 KING 5500 6353 7698 BLAKE 3000 3465 7782 CLARK 2450 2830 7566 JONES 2975 3436 7788 SCOTT 3000 3465 7902 FORD 3000 3465 7369 SMITH 800 924 7499 ALLEN 1900 2195 7521 WARD 1250 1444 7654 MARTIN 1250 1444 7844 TURNER 1500 1733 7876 ADAMS 1100 1271 7900 JAMES 950 1097 7934 MILLER 1300 1502
22. Modify the previous query to add a column alias that subtracts the old
salary from the new salary. Label the column Increase.
SELECT EMPNO,ENAME, SAL, ROUND(SAL+ SAL * 15.5/100) AS "NEW SALARY", ROUND(SAL+ SAL * 15.5/100)-SAL "INCREASE" FROM EMP; EMPNO ENAME SAL NEW SALARY INCREASE ---------- ---------- ---------- ---------- ---------- 7839 KING 5500 6353 853 7698 BLAKE 3000 3465 465 7782 CLARK 2450 2830 380 7566 JONES 2975 3436 461 7788 SCOTT 3000 3465 465 7902 FORD 3000 3465 465 7369 SMITH 800 924 124 7499 ALLEN 1900 2195 295 7521 WARD 1250 1444 194 7654 MARTIN 1250 1444 194 7844 TURNER 1500 1733 233 7876 ADAMS 1100 1271 171 7900 JAMES 950 1097 147 7934 MILLER 1300 1502 202
23. Write a query that displays the ename (with the first letter uppercase
and all other letters lowercase ) and the length of the ename for all
employees whose name starts with the letters J, A, or M. Give each column an
appropriate label. Sort the results by the employee's enames.
SELECT INITCAP(ENAME) AS NAME, LENGTH(ENAME) AS "LENGTH OF NAME" FROM EMP WHERE ENAME LIKE 'J%' OR ENAME LIKE 'A%' OR ENAME LIKE 'M%' ORDER BY ENAME; NAME LENGTH OF NAME ---------- -------------- Adams 5 Allen 5 James 5 Jones 5 Martin 6 Miller 6
24. Rewrite the query so that user is prompted to enter a letter that
starts the last name. For example, if the user enters H when prompted
for a letter, then the output should show all employees whose last
name starts with the letter H.
SELECT INITCAP(ENAME) AS NAME, LENGTH(ENAME) AS "LENGTH OF NAME" FROM EMP WHERE ENAME LIKE '&LETTER%' ORDER BY ENAME; Enter value for letter: A old 1: SELECT INITCAP(ENAME) AS NAME, LENGTH(ENAME) AS "LENGTH OF NAME" FROM EMP WHERE ENAME LIKE '&LETTER%' ORDER BY ENAME new 1: SELECT INITCAP(ENAME) AS NAME, LENGTH(ENAME) AS "LENGTH OF NAME" FROM EMP WHERE ENAME LIKE 'A%' ORDER BY ENAME NAME LENGTH OF NAME ---------- -------------- Adams 5 Allen 5
25. The HR department wants to find the length of employment for each
employee. For each employee, display the ename and calculate the number of
months between today and the date on which the employee was hired. Label the
column MONTHS_WORKED. Order your results by the number of months employed.
Round the number of months up to the closest whole number.
SELECT ENAME, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS_WORKED FROM EMP ORDER BY MONTHS_WORKED; ENAME MONTHS_WORKED ---------- ------------- ADAMS 453 SCOTT 454 MILLER 464 FORD 466 JAMES 466 KING 466 MARTIN 468 TURNER 469 CLARK 472 BLAKE 473 JONES 474 ALLEN 475 WARD 475 SMITH 477
26. Create a report that produces the following for each employee :
earns monthly but wants <3 times salary>. Label the column Dream
Salaries.
SELECT SAL, SAL*3 "DREAM SALARIES" FROM EMP; SAL DREAM SALARIES ---------- -------------- 5500 16500 3000 9000 2450 7350 2975 8925 3000 9000 3000 9000 800 2400 1900 5700 1250 3750 1250 3750 1500 4500 1100 3300 950 2850 1300 3900
27. create a query to display the last name and salary for all
employees. Format the salary to be 15 characters long, left-padded with the
$ symbol. Label the column SALARY.
SELECT ENAME, LPAD(SAL, 15, '$') FROM EMP; ENAME LPAD(SAL,15,'$') ---------- ------------------------------------------------------------ KING $$$$$$$$$$$5500 BLAKE $$$$$$$$$$$3000 CLARK $$$$$$$$$$$2450 JONES $$$$$$$$$$$2975 SCOTT $$$$$$$$$$$3000 FORD $$$$$$$$$$$3000 SMITH $$$$$$$$$$$$800 ALLEN $$$$$$$$$$$1900 WARD $$$$$$$$$$$1250 MARTIN $$$$$$$$$$$1250 TURNER $$$$$$$$$$$1500 ADAMS $$$$$$$$$$$1100 JAMES $$$$$$$$$$$$950 MILLER $$$$$$$$$$$1300
28. Display each employee's last name, hire date, and salary review date,
which is the first Monday after six months of service. Label the column
REVIEW. Format the dates to appear in the format similar to "Monday, the
Thirty-First of July, 2000."
SELECT ENAME, HIREDATE, TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 6), 'MONDAY'), 'FMDAY, "THE "DDSPTH" OF "MONTH, YYYY') AS "REVIEW" FROM EMP; ENAME HIREDATE REVIEW ---------- --------- ------------------------------------------------------------------------------------------------------ KING 17-NOV-81 MONDAY, THE TWENTY-FOURTH OF MAY, 1982 BLAKE 01-MAY-81 MONDAY, THE SECOND OF NOVEMBER, 1981 CLARK 09-JUN-81 MONDAY, THE FOURTEENTH OF DECEMBER, 1981 JONES 02-APR-81 MONDAY, THE FIFTH OF OCTOBER, 1981 SCOTT 09-DEC-82 MONDAY, THE THIRTEENTH OF JUNE, 1983 FORD 03-DEC-81 MONDAY, THE SEVENTH OF JUNE, 1982 SMITH 17-DEC-80 MONDAY, THE TWENTY-SECOND OF JUNE, 1981 ALLEN 20-FEB-81 MONDAY, THE TWENTY-FOURTH OF AUGUST, 1981 WARD 22-FEB-81 MONDAY, THE TWENTY-FOURTH OF AUGUST, 1981 MARTIN 28-SEP-81 MONDAY, THE TWENTY-NINTH OF MARCH, 1982 TURNER 08-SEP-81 MONDAY, THE FIFTEENTH OF MARCH, 1982 ADAMS 12-JAN-83 MONDAY, THE EIGHTEENTH OF JULY, 1983 JAMES 03-DEC-81 MONDAY, THE SEVENTH OF JUNE, 1982 MILLER 23-JAN-82 MONDAY, THE TWENTY-SIXTH OF JULY, 1982 14 rows selected.
29. Display the last name, hire date and day of the week on which the employee
started. Label the column DAY. Order the results by the day of the week,
starting with Monday.
SELECT ENAME, HIREDATE, TO_CHAR(HIREDATE, 'FMDAY') "DAY" FROM EMP ORDER BY TO_CHAR(HIREDATE-1, 'D'); ENAME HIREDATE DAY ---------- --------- ------------------------------------ MARTIN 28-SEP-81 MONDAY KING 17-NOV-81 TUESDAY TURNER 08-SEP-81 TUESDAY CLARK 09-JUN-81 TUESDAY SMITH 17-DEC-80 WEDNESDAY ADAMS 12-JAN-83 WEDNESDAY JAMES 03-DEC-81 THURSDAY JONES 02-APR-81 THURSDAY FORD 03-DEC-81 THURSDAY SCOTT 09-DEC-82 THURSDAY ALLEN 20-FEB-81 FRIDAY BLAKE 01-MAY-81 FRIDAY MILLER 23-JAN-82 SATURDAY WARD 22-FEB-81 SUNDAY
30. Create a query that displays the employee's last names and commission
amounts. If an employee does not earn commission, show "No Commission". Label
the column COMM.
SELECT ENAME, NVL(TO_CHAR(COMM), 'NO COMMISSION') "COMM" FROM EMP; ENAME COMM ---------- ---------------------------------------- KING NO COMMISSION BLAKE NO COMMISSION CLARK NO COMMISSION JONES NO COMMISSION SCOTT NO COMMISSION FORD NO COMMISSION SMITH NO COMMISSION ALLEN 300 WARD 500 MARTIN 1400 TURNER 0 ADAMS NO COMMISSION JAMES NO COMMISSION MILLER NO COMMISSION