Employee Management System Queries

The assignment content involves creating a database with tables for EMPLOYEE, DEPARTMENT, and ISSUES. The EMPLOYEE table contains information about employees such as their name, salary, phone number, and department number. The DEPARTMENT table contains information about departments such as the department name, employee number, and office ID. The ISSUES table contains information about issues such as the issue date, status, details, remarks, and employee number. The assignment also includes five SQL queries that can be used to retrieve specific data from the tables.

0 | P a g e
TERM PROJECT: DRAFT SQL IMPLEMENTATION
STUDENT NAME:
15 October 2016

Contents
LATEST DRAFT ER DIAGRAM..................................................................................................................2
TRANSLATED RELATIONAL SCHEMA......................................................................................................2
TABLE CREATION...................................................................................................................................2
RECORD INSERTION...............................................................................................................................5
SQL QUERIES..........................................................................................................................................9

LATEST DRAFT ER DIAGRAM
TRANSLATED RELATIONAL SCHEMA
A. EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO*)
B. DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO*, OFFICE_ID*)
C. OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE)
D. ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO*)
TABLE CREATION
CREATE TABLE OFFICE
(
OFFICE_ID VARCHAR(4) PRIMARY KEY,
OFFICE_NAME VARCHAR(30) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
PHONE NUMBER(10,0)

);
CREATE SEQUENCE seq_emp
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
CREATE TABLE EMPLOYEE
(
EMP_NO INT,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
SALARY NUMBER(10,2),
PHONE NUMBER(10,0),
DEP_NO VARCHAR(4),
PRIMARY KEY (EMP_NO)
);

CREATE TABLE DEPARTMENT
(
DEP_NO varchar(4) primary key,
DEPT_NAME varchar(30),
EMP_NO int,
OFFICE_ID varchar(4),
FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO),
FOREIGN KEY(OFFICE_ID) REFERENCES OFFICE(OFFICE_ID));
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK1 FOREIGN KEY(DEP_NO) REFERENCES
DEPARTMENT(DEP_NO);
CREATE SEQUENCE SEQ_ISUE
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

CREATE TABLE ISSUES
(
ISSUE_ID INT PRIMARY KEY,
ISSUE_DATE DATE,
STATUS VARCHAR(50),
DETAILS VARCHAR(100),
REMARKS VARCHAR(100),
EMP_NO INT,
FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO),
CONSTRAINT CHK1 CHECK (STATUS IN ('OPEN', 'CLOSED', 'PENDING') )
);
RECORD INSERTION
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES ('O1' , 'HEAD OFFICE' ,
'21 HILL VIEW ROAD, LONG ISLAND, USA', 9999999983);
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES ('O2' , 'SATELLITE OFFICE
1' , '222 PARK STREET, NYC, USA', 9999998983);
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES ('O3' , 'SATELLITE OFFICE
2' , '55/455 DENISE ROAD, LA, USA', 9999997983);
SELECT * FROM OFFICE;

INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JOHN', 'LAMES', 8900.90, 9981234567, 'D1');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'DIA', 'STEVEN', 99900.90, 777789876, 'D1');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'DAVE', 'LANISTER', 7900.90, 771178876, 'D2');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'DAVID', 'STONES', 199900.90, 722778876, 'D2');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JAMES', 'JOHNSON', 69000.90, 73778876, 'D3');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JAMIE', 'BELE', 8900.90, 977778876, 'D3');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JULIE', 'STEPHEN', 89900.90, 177778876, 'D4');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'MIA', 'DRAKES', 8900.90, 577778876, 'D4');

INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D1', 'SALES', 10,
'O1');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D2', 'ADMIN', 12,
'O2');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D3', 'TECHNICAL',
13, 'O3');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D4', 'SUPPORT',
15, 'O3');
SELECT * FROM DEPARTMENT;
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (1, '13-
OCT-16', 'OPEN', 'SOFTWARE FAILURE', '', 10);

INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (2, '12-
OCT-16', 'OPEN', 'HARDWARE FAILURE', '', 12);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (3, '10-
OCT-16', 'PENDING', 'HARDWARE REPAIR', '', 14);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (4, '09-
OCT-16', 'CLOSED', 'BACKUP ISSUES', '', 12);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (5, '08-
OCT-16', 'CLOSED', 'SOFTWARE UPDATE', '', 14);
SELECT * FROM ISSUES;

SQL QUERIES
Question 1: Director needs the name of all employees who are head of department along with office
and department details for a meeting.
Sql: select e.fname as head, d.dept_name as department_name, o.office_id as offices from
employee e, department d, office o where e.emp_no = d.emp_no and o.office_id = d.office_id;

Question 2: Manager needs to how many hardware issues were there till date.
Sql: select count(issue_id) AS NO_OF_HARDWARE_ISSUES from issues where details like
'%HARDWARE%';
Question 3: Director wants to update salary of all the employee by 10% who are head of
departments.
SQL: UPDATE EMPLOYEE SET SALARY = SALARY + (0.1 * SALARY) WHERE EMP_NO IN (SELECT
EMP_NO FROM DEPARTMENT);
Question 4: Manager needs to know the number of open issues and their registered dates.
SQL: SELECT ISSUE_ID, ISSUE_DATE FROM ISSUES WHERE STATUS = 'OPEN';
Question 5: Director needs to know the number of employees (including heads) in each department
of an office so as to decide the number of employees that have to be hired in future.
SQL: SELECT COUNT(e.EMP_NO) as NO_OF_EMPLOYEES, D.DEPT_NAME, O.OFFICE_NAME from
employee e, department d, office o where e.dep_no = d.dep_no and o.office_id = d.office_id GROUP
BY D.DEPT_NAME, O.OFFICE_NAME;