Oracle Sql - Questions and Answers 1

 


Oracle Sql - Questions and Answers ;


                         Back                                                                                      Next 

 1. Determine the Structure of DEPT Table and its Contents.

DESC DEPT;

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                NOT NULL NUMBER(2)
 DNAME                                                          VARCHAR2(14)
 LOC                                                            VARCHAR2(13)



2. Determine the Structure of EMP Table and its Contents.

DESC EMP;

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                           NUMBER(7,2)
 DEPTNO                                                         NUMBER(2)



3. Display the Ename and Deptno from Emp table whose Empno is 7788.

SELECT ENAME, DEPTNO FROM EMP WHERE EMPNO=7788;

ENAME          DEPTNO
---------- ----------
SCOTT              20


4.  Display the Ename, Sal, Comm from Emp table who earn commission ans sort the records in descending order of Salary and Comm. Use column's numeric position in the ORDER BY Clause.
       
SELECT ENAME, SAL, COMM FROM EMP WHERE COMM >=0 ORDER BY 2 DESC, 3 DESC;

ENAME             SAL       COMM
---------- ---------- ----------
ALLEN            1900        300
TURNER           1500          0
MARTIN           1250       1400
WARD             1250        500



5. The HR department needs a query to display all unique job codes from the EMP table.
       
SELECT DISTINCT JOB FROM EMP;

JOB
---------

CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST



6. The HR department wants more description column headings for its report on employees. Name the column headings EMp#, Employee, Job, Hire Date respectively by giving Column Alias.
    
 SELECT EMPNO AS EMP#, ENAME AS EMPLOYEE, JOB AS JOB, HIREDATE AS HIREDATE FROM EMP;

      EMP# EMPLOYEE   JOB       HIREDATE
---------- ---------- --------- ---------
      7839 KING       PRESIDENT 17-NOV-81
      7698 BLAKE      MANAGER   01-MAY-81
      7782 CLARK      MANAGER   09-JUN-81
      7566 JONES      MANAGER   02-APR-81
      7788 SCOTT      ANALYST   09-DEC-82
      7902 FORD       ANALYST   03-DEC-81
      7369 SMITH      CLERK     17-DEC-80
      7499 ALLEN      SALESMAN  20-FEB-81
      7521 WARD       SALESMAN  22-FEB-81
      7654 MARTIN     SALESMAN  28-SEP-81
      7844 TURNER     SALESMAN  08-SEP-81
      7876 ADAMS      CLERK     12-JAN-83
      7900 JAMES      CLERK     03-DEC-81
      7934 MILLER     CLERK     23-JAN-82



7. The HR department has requested a report of all employees and their job id's. Display the last name concatenated with the job id (separated by a comma and space) and  name the column Employee and Title by giving Column Alias. 
       
SELECT ENAME||', '||JOB "EMPLOYEE AND TITLE" FROM EMP;

EMPLOYEE AND TITLE
---------------------
KING, PRESIDENT
BLAKE, MANAGER
CLARK, MANAGER
JONES, MANAGER
SCOTT, ANALYST
FORD, ANALYST
SMITH, CLERK
ALLEN, SALESMAN
WARD, SALESMAN
MARTIN, SALESMAN
TURNER, SALESMAN
ADAMS, CLERK
JAMES, CLERK
MILLER, CLERK



8. To familiarize yourself with the data in the EMP table, create a query to display all the data from that table . Separate each column output by a comma. ENAME, JOB, HIREDATE,MGR. Name the column title "THE_OUTPUT".

SELECT ENAME||', '||JOB||', '||HIREDATE||', '||MGR "THE_OUTPUT" FROM EMP;

THE_OUTPUT
-----------------------------------------------------------------------------------
KING, PRESIDENT, 17-NOV-81,
BLAKE, MANAGER, 01-MAY-81, 7839
CLARK, MANAGER, 09-JUN-81, 7839
JONES, MANAGER, 02-APR-81, 7839
SCOTT, ANALYST, 09-DEC-82, 7566
FORD, ANALYST, 03-DEC-81, 7566
SMITH, CLERK, 17-DEC-80, 7902
ALLEN, SALESMAN, 20-FEB-81, 7698
WARD, SALESMAN, 22-FEB-81, 7698
MARTIN, SALESMAN, 28-SEP-81, 7698
TURNER, SALESMAN, 08-SEP-81, 7698
ADAMS, CLERK, 12-JAN-83, 7788
JAMES, CLERK, 03-DEC-81, 7698
MILLER, CLERK, 23-JAN-82, 7782



9. Create a report to display the ename, job, hiredate for the employees name is SCOTT or TURNER. Order the query in ascending order by hiredate.

SELECT ENAME,JOB,HIREDATE FROM EMP WHERE ENAME='SCOTT' OR ENAME='TURNER' ORDER BY HIREDATE;

ENAME      JOB       HIREDATE
---------- --------- ---------
TURNER     SALESMAN  08-SEP-81
SCOTT      ANALYST   09-DEC-82



10. Display the ename and department number of all employees in departments 20 or 30 in ascending order by ename.

SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO=20 OR DEPTNO=30 ORDER BY ENAME;

ENAME          DEPTNO
---------- ----------
ADAMS              20
ALLEN              30
BLAKE              30
FORD               20
JAMES              30
JONES              20
MARTIN             30
SCOTT              20
SMITH              20
TURNER             30
WARD               30



11. Modify the previous query to display the last name and salary of employees who earn between 2000 and 3000 and are in department 20 or 30. Label the columns Employee and Monthly Salary, respectively giving Column Alias.

SELECT ENAME AS EMPLOYEE, SAL "MONTHLY SALARY"  FROM EMP WHERE (SAL BETWEEN 2000 AND 3000) AND (DEPTNO=20 OR DEPTNO=30) ORDER BY ENAME;

EMPLOYEE   MONTHLY SALARY
---------- --------------
BLAKE                3000
FORD                 3000
JONES                2975
SCOTT                3000



12. The HR department needs a report that displays the last name and hiredate for all employees who were hired in 1981.

 SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE LIKE '%81';

ENAME      HIREDATE
---------- ---------
KING       17-NOV-81
BLAKE      01-MAY-81
CLARK      09-JUN-81
JONES      02-APR-81
FORD       03-DEC-81
ALLEN      20-FEB-81
WARD       22-FEB-81
MARTIN     28-SEP-81
TURNER     08-SEP-81
JAMES      03-DEC-81



13. Display the Ename, Sal of employees who earn more than an amount the user specifies after a prompt.

 SELECT ENAME, SAL FROM EMP WHERE SAL>&SALARY;

Enter value for salary: 2000
old   1: SELECT ENAME, SAL FROM EMP WHERE SAL>&SALARY
new   1: SELECT ENAME, SAL FROM EMP WHERE SAL>2000

ENAME             SAL
---------- ----------
KING             5500
BLAKE            3000
CLARK            2450
JONES            2975
SCOTT            3000
FORD             3000
ESWAR           12000



14. Create a report to display the last name and job title of all employees who do not have a manager.

 SELECT ENAME, JOB FROM EMP WHERE MGR IS NULL;

ENAME      JOB
---------- ---------
KING       PRESIDENT


15. Create a query that prompts the user for Manager ID and generate EMPNO, ENAME, SAL, DEPTNO. The user should have the ability to sort the records 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