Oracle Sql - Questions and Answers 2

 


Oracle Sql - Questions and Answers :


                            Back                                                                                       Next 

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