CS444 - MP 2

The Assignment

Several tables were created by mp02.sql. We can get a list of available tables by forming a Select statement to the USER_CATALOG as::

SQL> SELECT TABLE_NAME FROM USER_CATALOG;

TABLE_NAME
------------------------------
DEPART
EMP
EMP_ACT
IN_TRAY
PROJ

Before trying to summarize information from the tables, use the Select command to determine the column names from the tables (from USER_TAB_COLUMNS). For example, to get a description of fields from the employee table we would enter:

SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS 
  2  WHERE TABLE_NAME = 'EMP';

COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------
EMPNO                          CHAR
FIRSTNME                       VARCHAR2
MIDINIT                        CHAR
LASTNAME                       VARCHAR2
WORKDEPT                       CHAR
PHONENO                        CHAR
HIREDATE                       DATE
JOB                            CHAR
EDLEVEL                        NUMBER
SEX                            CHAR
BIRTHDATE                      DATE
SALARY                         NUMBER
BONUS                          NUMBER
COMM                           NUMBER

14 rows selected.

Or you may use the Describe command as:

SQL> describe emp;
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL CHAR(6)
 FIRSTNME                        NOT NULL VARCHAR2(12)
 MIDINIT                         NOT NULL CHAR(1)
 LASTNAME                        NOT NULL VARCHAR2(15)
 WORKDEPT                                 CHAR(3)
 PHONENO                                  CHAR(4)
 HIREDATE                                 DATE
 JOB                                      CHAR(8)
 EDLEVEL                         NOT NULL NUMBER(38)
 SEX                                      CHAR(1)
 BIRTHDATE                                DATE
 SALARY                                   NUMBER(9,2)
 BONUS                                    NUMBER(9,2)
 COMM                                     NUMBER(9,2)

Perform the following queries on the tables created by mp02.sql:

1. Obtain a listing of all data from the DEPARTMENT table.
2. Obtain a listing of the departments from the DEPARTMENT table.
3. Obtain a listing of the employees from the EMPLOYEE table whose first name is James.
4. Obtain a listing of the employees from the EMPLOYEE table who work in department D21.
5. Obtain a listing of the employees from the EMPLOYEE table who work in department E21 and D21.
6. Obtain a listing of the employees from the EMPLOYEE table who are female designers.
7. Obtain a listing of the employees from the EMPLOYEE table who received bonuses of $900 or more.
8. Obtain a listing of the employees from the EMPLOYEE table who were hired after 1/1/80.
9. Obtain a listing of employee numbers who have worked on project MA2112 from the PROJECT table.
10. Obtain a listing of employee numbers between 180 and 280 who have worked on project MA2112 from the PROJECT table.

Use the spool command and turn in the SELECT statements and the output they generate for the above queries. Capture the results from the queries. Be sure to clearly label your answers (that is 1, 2, ...).

Get the information you captured in a file called mp02.txt. Make sure the file is readable with Notepad. All assignments must be submitted to the online homework submission system. Submit the file mp02.txt to the online homework submission system.