Britt's DB Home    Purpose of the DB    Conceptual Schema

Mapping    Database     Back to Linda's Home Page

Database

Find all employees having PRP and the status of their area access.

Find all employees who have training courses due to expire in the month of October, 2001.

SQL> SELECT LNAME, COURSETITLE, EXPIREDATE
2 FROM BRITTSEMPLOYEE, KSC_COURSE, KSC_CLASS
3 WHERE SSN=KSSN AND C_COURSENUM=K_COURSENUM AND
4 EXPIREDATE BETWEEN '01-OCT-01' AND '31-OCT-01';

LNAME         COURSETITLE         EXPIREDAT
---------------    ------------------------------         ---------
BRATT          ELSA TRAINING         04-OCT-01
CHILD           ELSA TRAINING         04-OCT-01
COFFY         ELSA TRAINING         04-OCT-01
FOWL JR.    ELSA TRAINING         04-OCT-01
WALLACE   ELSA TRAINING         04-OCT-01
HERD           ELSA TRAINING         04-OCT-01
WHATSIT     ELSA TRAINING         04-OCT-01

7 rows selected.

Find all annual employee evaluations due in July, 2001.

SQL> SELECT LNAME, EDATE
2 FROM BRITTSEMPLOYEE, EVALUATION
3 WHERE SSN=ESSN AND EDATE BETWEEN '01-JUL-00' AND '31-JUL-00';

LNAME             EDATE
---------------         ---------
PRINGLE         03-JUL-00

List all employee's who have committed violations between 1/1/00 and 7/31/01.

SQL> SELECT LNAME, VIOLATIONDATE, VDESCRIPTION, COMMENTS
2 FROM BRITTSEMPLOYEE, EMPLOYEEWARNING
3 WHERE SSN=VSSN AND VIOLATIONDATE BETWEEN '01-JAN-00' AND '31-JUL-01';

LNAME VIOLATION VDESCRIPTION
--------------- --------- --------------------
COMMENTS
---------------------------------------------------------------------------------
DEADHEAD 05-JUL-00 attendance
Failure to report to work, failure to call in

WHATSIT 15-JAN-01 SUBSTANDARD WORK
FAILURE TO USE A LEVEL

WALLACE 24-APR-01 DISOBEDIENCE
INSUBORDINATION

List all employees, their level of education, and safety training courses in alphebetical order according to last name and safety course title.

SQL> SELECT LNAME, EDUCATION, COURSETITLE
2 FROM BRITTSEMPLOYEE, EVALUATION, KSC_COURSE, KSC_CLASS
3 WHERE SSN=ESSN AND SSN=KSSN AND K_COURSENUM=C_COURSENUM
4 ORDER BY LNAME, COURSETITLE;

LNAME             EDUCATION                 COURSETITLE
---------------     ------------------------------     ------------------------------
BRATT                 BA                                 ELSA TRAINING
BRATT                 BA                                 GENERAL PROCESSING SAFETY
BRATT                 BA                                 SLF-OPF-PAD-RPSF-VAB FA
BRATT                 BA                                 SPACE SHUTTLE SAFETY
CHILD                  HS                                 ELSA TRAINING
CHILD                  HS                                 GENERAL PROCESSING SAFETY
CHILD                  HS                                 SLF-OPF-PAD-RPSF-VAB FA
CHILD                  HS                                 SPACE SHUTTLE SAFETY
COFFY                HS                                  AFT SKIRT TEST FACILITY
COFFY                HS                                 ARF SAFETY ORIENTATION
COFFY                HS                                 ELSA TRAINING
COFFY                HS                                 SLF-OPF-PAD-RPSF-VAB FA
DEADHEAD      HS                                 AFT SKIRT TEST FACILITY
DEADHEAD      HS                                 ARF SAFETY ORIENTATION

List alphabetically employees and their OSHA Training Courses  according to date of completion.

SQL> SELECT LNAME, O_COURSETITLE, O_COMPLETEDATE
2 FROM BRITTSEMPLOYEE, OSHATRAINING
3 WHERE SSN=OSSN
4 ORDER BY LNAME, O_COMPLETEDATE;

LNAME                 O_COURSETITLE                         O_COMPLET
---------------         ------------------------------                         ---------
BRATT                 FIRE SAFETY                                   23-JAN-01
CHILD                 AERIAL LIFT SAFETY                      09-AUG-00
CHILD                 HAZARDOUS MATERIALS             02-JUN-98
COFFY               BIOHAZARDS                                    04-SEP-99
COFFY               SAFETY IS YOUR FRIEND              17-SEP-99
DEADHEAD     AERIAL LIFT SAFETY                       09-AUG-00
DEADHEAD     HAZARDOUS MATERIALS              02-JUN-98
DESOTO           ASBESTOS THEN AND NOW         07-DEC-00
DESOTO           FIRE SAFETY                                     23-JAN-01
FOWL JR.         AERIAL LIFT SAFETY                        09-AUG-00
FOWL JR.         HAZARDOUS MATERIALS               02-JUN-98
HERD                BIOHAZARDS                                      04-SEP-99
HERD                SAFETY IS YOUR FRIEND                 17-SEP-99
PRINGLE          ASBESTOS THEN AND NOW           07-DEC-00
PRINGLE          FIRE SAFETY                                       23-JAN-01
WALLACE        BIOHAZARDS                                      04-SEP-99
WALLACE        SAFETY IS YOUR FRIEND                 17-SEP-99
WHATSIT           ASBESTOS THEN AND NOW           07-DEC-00
WHATSIT           FIRE SAFETY                                       23-JAN-01

Copyright © 2001 Linda Cook
Last modified: March,  2002