Database for in-class exercises and practice
CREATE TABLE JOB (
JOB_CODE CHAR(3) NOT NULL,
JOB_DESCRIPTION VARCHAR(25) NOT NULL,
JOB_CHG_HOUR DECIMAL(5,2) NOT NULL,
JOB_LAST_UPDATE DATE NOT NULL,
PRIMARY KEY (JOB_CODE),
CONSTRAINT Code_Ck CHECK (JOB_CODE > 499));
INSERT INTO JOB
VALUES ('500', 'Programmer', 35.75, '20-Nov-09');
INSERT INTO JOB
VALUES ('501', 'Systems Analyst', 96.75, '20-Nov-09');
INSERT INTO JOB
VALUES ('502', 'Database Designer', 125.00, '24-Mar-10');
INSERT INTO JOB
VALUES ('503', 'Electrical Engineer', 84.50, '20-Nov-09');
INSERT INTO JOB
VALUES ('504', 'Mechanical Engineer', 67.90, '20-Nov-09');
INSERT INTO JOB
VALUES ('505', 'Civil Engineer', 55.78, '20-Nov-09');
INSERT INTO JOB
VALUES ('506', 'Clerical Support', 26.87, '20-Nov-09');
INSERT INTO JOB
VALUES ('507', 'DSS Analyst', 45.95, '20-Nov-09');
INSERT INTO JOB
VALUES ('508', 'Applications Designer', 48.10, '24-Mar-10');
INSERT INTO JOB
VALUES ('509', 'Bio Technician', 34.55, '20-Nov-09');
INSERT INTO JOB
VALUES ('510', 'General Support', 18.36, '20-Nov-09');
Select *
From Job;
===============================================================================
CREATE TABLE EMPLOYEE (
EMP_NUM CHAR(3) NOT NULL UNIQUE,
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_INITIAL CHAR(1),
EMP_HIREDATE DATE NOT NULL,
JOB_CODE CHAR(3) NOT NULL,
PRIMARY KEY (EMP_NUM),
FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE));
INSERT INTO EMPLOYEE
VALUES ('101', 'News', 'John', 'G','18-Nov-00','502');
INSERT INTO EMPLOYEE
VALUES ('102', 'Senior', 'David', 'H','12-Jul-89','501');
INSERT INTO EMPLOYEE
VALUES ('103', 'Arbough', 'June', 'E','01-Dec-96','503');
INSERT INTO EMPLOYEE
VALUES ('104', 'Ramoras', 'Anne', 'K','15-Nov-87','501');
INSERT INTO EMPLOYEE
VALUES ('105', 'Johnson', 'Alice', 'K','01-Feb-93','502');
INSERT INTO EMPLOYEE
VALUES ('106', 'Smithfield', 'William', '','22-JUN-04','500');
INSERT INTO EMPLOYEE
VALUES ('107', 'Alonzo', 'Maria', 'D','10-Oct-93','500');
INSERT INTO EMPLOYEE
VALUES ('108', 'Washington', 'Ralph', 'B','22-Aug-91','501');
INSERT INTO EMPLOYEE
VALUES ('109', 'Smith', 'Larry', 'W','18-Jul-97','501');
INSERT INTO EMPLOYEE
VALUES ('110', 'Olendo', 'Gerald', 'A','11-Dec-95','505');
INSERT INTO EMPLOYEE
VALUES ('111', 'Walsh', 'Geoff', 'B','04-Apr-91','506');
INSERT INTO EMPLOYEE
VALUES ('112', 'Smithson', 'Darlene', 'M','23-Oct-94','507');
INSERT INTO EMPLOYEE
VALUES ('113', 'Joenbrood', 'Delbert', 'K','15-Nov-96','508');
INSERT INTO EMPLOYEE
VALUES ('114', 'Jones', 'Annelise', '','20-Aug-93','508');
INSERT INTO EMPLOYEE
VALUES ('115', 'Bawangi', 'Travis', 'B','15-Jan-92','501');
INSERT INTO EMPLOYEE
VALUES ('116', 'Pratt', 'Gerald', 'L','05-Mar-97','510');
INSERT INTO EMPLOYEE
VALUES ('117', 'Williamson', 'Angie', 'H','19-Jun-96','509');
INSERT INTO EMPLOYEE
VALUES ('118', 'Frommer', 'James', 'J','04-Jan-05','510');
Select *
From Employee;
CREATE TABLE PROJECT (
PROJ_NUM CHAR(2) NOT NULL UNIQUE,
PROJ_NAME VARCHAR(15) NOT NULL,
PROJ_VALUE DECIMAL(10,2) NOT NULL,
PROJ_BALANCE DECIMAL(10,2) NOT NULL,
EMP_NUM CHAR(3) NOT NULL,
PRIMARY KEY (PROJ_NUM),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM));
INSERT INTO PROJECT
VALUES ('15', 'Evergreen', 1453500.00, 1002350.00, '103');
INSERT INTO PROJECT
VALUES ('18', 'Amber Wave', 3500500.00, 2110346.00, '108');
INSERT INTO PROJECT
VALUES ('22', 'Rolling Tide', 805000.00, 500345.20, '102');
INSERT INTO PROJECT
VALUES ('25', 'Starflight', 2650500.00, 2309880.00, '107');
Select *
From Project;
CREATE TABLE ASSIGNMENT (
ASSIGN_NUM CHAR(4) NOT NULL UNIQUE,
ASSIGN_DATE DATE NOT NULL,
PROJ_NUM CHAR(2) NOT NULL,
EMP_NUM CHAR(3) NOT NULL,
ASSIGN_JOB CHAR(3) NOT NULL,
ASSIGN_CHG_HR DECIMAL(5,2) NOT NULL,
ASSIGN_HOURS DECIMAL(2,1) NOT NULL,
PRIMARY KEY (ASSIGN_NUM),
FOREIGN KEY (PROJ_NUM) REFERENCES PROJECT(PROJ_NUM),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (ASSIGN_JOB) REFERENCES JOB(JOB_CODE));
INSERT INTO ASSIGNMENT
VALUES ('1001', '22-Mar-10', '18', '103', '503', 84.50, 3.5);
INSERT INTO ASSIGNMENT
VALUES ('1002', '22-Mar-10', '22', '117', '509', 34.55, 4.2);
INSERT INTO ASSIGNMENT
VALUES ('1003', '22-Mar-10', '18', '117', '509', 34.55, 2.0);
INSERT INTO ASSIGNMENT
VALUES ('1004', '22-Mar-10', '18', '103', '503', 84.50, 5.9);
INSERT INTO ASSIGNMENT
VALUES ('1005', '22-Mar-10', '25', '108', '501', 96.75, 2.2);
INSERT INTO ASSIGNMENT
VALUES ('1006', '22-Mar-10', '22', '104', '501', 96.75, 4.2);
INSERT INTO ASSIGNMENT
VALUES ('1007', '22-Mar-10', '25', '113', '508', 50.75, 3.8);
INSERT INTO ASSIGNMENT
VALUES ('1008', '22-Mar-10', '18', '103', '503', 84.50, 0.9);
INSERT INTO ASSIGNMENT
VALUES ('1009', '23-Mar-10', '15', '115', '501', 96.75, 5.6);
INSERT INTO ASSIGNMENT
VALUES ('1010', '23-Mar-10', '15', '117', '509', 34.55, 2.4);
INSERT INTO ASSIGNMENT
VALUES ('1011', '23-Mar-10', '25', '105', '502', 105.00, 4.3);
INSERT INTO ASSIGNMENT
VALUES ('1012', '23-Mar-10', '18', '108', '501', 96.75, 3.4);
INSERT INTO ASSIGNMENT
VALUES ('1013', '23-Mar-10', '25', '115', '501', 96.75, 2.0);
INSERT INTO ASSIGNMENT
VALUES ('1014', '23-Mar-10', '22', '104', '501', 96.75, 2.8);
INSERT INTO ASSIGNMENT
VALUES ('1015', '23-Mar-10', '15', '103', '503', 84.50, 6.1);
INSERT INTO ASSIGNMENT
VALUES ('1016', '23-Mar-10', '22', '105', '502', 105.00, 4.7);
INSERT INTO ASSIGNMENT
VALUES ('1017', '23-Mar-10', '18', '117', '509', 34.55, 3.8);
INSERT INTO ASSIGNMENT
VALUES ('1018', '23-Mar-10', '25', '117', '509', 34.55, 2.2);
INSERT INTO ASSIGNMENT
VALUES ('1019', '24-Mar-10', '25', '104', '501', 110.50, 4.9);
INSERT INTO ASSIGNMENT
VALUES ('1020', '24-Mar-10', '15', '101', '502', 125.00, 3.1);
INSERT INTO ASSIGNMENT
VALUES ('1021', '24-Mar-10', '22', '108', '501', 110.50, 2.7);
INSERT INTO ASSIGNMENT
VALUES ('1022', '24-Mar-10', '22', '115', '501', 110.50, 4.9);
INSERT INTO ASSIGNMENT
VALUES ('1023', '24-Mar-10', '22', '105', '502', 125.00, 3.5);
INSERT INTO ASSIGNMENT
VALUES ('1024', '24-Mar-10', '15', '103', '503', 84.50, 3.3);
INSERT INTO ASSIGNMENT
VALUES ('1025', '24-Mar-10', '18', '117', '509', 34.55, 4.2);
Select *
From Assignment;
1. What is Alice Johnson's job title?
2. We're starting a new project and need to know the names of our systems analysts and applications designers. Include last names labeled Employee and job titles labelled Title.
3. What is the last name labeled Manager and job description (Job) of each person managing a project? Include the project name and label it In Charge of.
4. List the last name of all employees currently working on a project. Include the name of the project labeled Working On. Do not include duplicates.
5. Name the employees (first and last name) who worked more than 5 hours on a job? Include the job number and number of hours worked (More than 5 hours). You do not need to total hours per job.
6. What is the total number of hours worked so far on the Rolling Tide project? Label as Total Hours on Rolling Tide Project.
7. How many employees are working on the Rolling Tide project? Label the output
Total Employees on Rolling Tide.
(SQL JOINS QUESTION )