1
Week 4 – Structured Query Langauge (SQL)
Problem 7.1:
-- Selecting specific columns from the EMPLOYEE table
SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL
-- Filtering records where the last name starts with 'SMITH'
FROM EMPLOYEE
WHERE EMP_LNAME LIKE 'SMITH%';
Problem 7.2:
-- Selecting specific columns from the PROJECT, EMPLOYEE, and JOB tables
SELECT
P.PROJ_NAME, -- Project name
P.PROJ_VALUE, -- Project value
P.PROJ_BALANCE, -- Project balance
E.EMP_LNAME, -- Employee last name
E.EMP_FNAME, -- Employee first name
E.EMP_INITIAL, -- Employee initial
J.JOB_CODE, -- Job code
J.JOB_DESCRIPTION, -- Job description
J.JOB_CHG_HOUR -- Job change per hour
-- Joining EMPLOYEE table with JOB table on the job code
FROM EMPLOYEE E
INNER JOIN JOB J ON J.JOB_CODE = E.JOB_CODE
2
-- Joining EMPLOYEE table with PROJECT table on the employee number
INNER JOIN PROJECT P ON P.EMP_NUM = E.EMP_NUM
-- Sorting the result set by project value
ORDER BY P.PROJ_VALUE;
Problem 7.3
SELECT P.PROJ_NAME, P.PROJ_VALUE, P.PROJ_BALANCE, E.EMP_LNAME,
E.EMP_FNAME, E.EMP_INITIAL, J.JOB_CODE, J.JOB_DESCRIPTION,
J.JOB_CHG_HOUR
FROM EMPLOYEE E
INNER JOIN JOB J ON J.JOB_CODE = E.JOB_CODE
INNER JOIN PROJECT P ON P.EMP_NUM = E.EMP_NUM
ORDER BY E.EMP_LNAME;
3
Problem 7.5
-- Selecting specific columns and calculating the assignment charge
SELECT
ASSIGN_NUM, -- Assignment number
EMP_NUM, -- Employee number
PROJ_NUM, -- Project number
ASSIGN_CHARGE, -- Assignment charge
-- Calculating the assignment charge by multiplying the assignment change per hour
with the assignment hours and rounding to 2 decimal places
ROUND((ASSIGN_CHG_HR * ASSIGN_HOURS), 2) AS
CALC_ASSIGN_CHARGE
-- Retrieving data from the ASSIGNMENT table
FROM ASSIGNMENT
-- Sorting the result set by assignment number
ORDER BY ASSIGN_NUM;
Problem 7.6
-- Selecting employee number, employee last name, sum of assignment hours, and sum
of assignment charge
SELECT
E.EMP_NUM, -- Employee number
E.EMP_LNAME, -- Employee last name
4
-- Rounding the sum of assignment hours to 1 decimal place
ROUND(SUM(A.ASSIGN_HOURS), 1) AS SUMOFASSIGN_HOURS,
-- Rounding the sum of assignment charge to 2 decimal places
ROUND(SUM(A.ASSIGN_CHARGE), 2) AS SUMOFASSIGN_CHARGE
-- Joining EMPLOYEE table with ASSIGNMENT table based on employee number
FROM EMPLOYEE E
INNER JOIN ASSIGNMENT A ON E.EMP_NUM = A.EMP_NUM
-- Grouping the result set by employee number and employee last name
GROUP BY E.EMP_NUM, E.EMP_LNAME;
Problem 7.7
-- Selecting project number, sum of assignment hours, and sum of assignment charge
SELECT
P.PROJ_NUM, -- Project number
-- Rounding the sum of assignment hours to 1 decimal place
ROUND(SUM(A.ASSIGN_HOURS), 1) AS SUMOFASSIGN_HOURS,
-- Rounding the sum of assignment charge to 2 decimal places
ROUND(SUM(A.ASSIGN_CHARGE), 2) AS SUMOFASSIGN_CHARGE
-- Joining PROJECT table with ASSIGNMENT table based on project number
FROM PROJECT P
INNER JOIN ASSIGNMENT A ON P.PROJ_NUM = A.PROJ_NUM
-- Grouping the result set by project number
GROUP BY P.PROJ_NUM;
5
Problem 8.1
-- Creating table EMP_1
CREATE TABLE EMP_1 (
EMP_NUM CHAR(3) PRIMARY KEY, -- Employee number (Primary Key)
EMP_LNAME VARCHAR(15) NOT NULL, -- Employee last name
EMP_FNAME VARCHAR(15) NOT NULL, -- Employee first name
EMP_INITIAL CHAR(1), -- Employee initial
EMP_HIREDATE DATE, -- Employee hire date
JOB_CODE CHAR(3), -- Job code
-- Defining a foreign key constraint for the JOB_CODE column, referencing the JOB
table's JOB_CODE column
FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
);
Problem 8.2
INSERT INTO EMP_1 VALUES ('101', 'News', 'John', 'G', '2000-11-08', '502');
INSERT INTO EMP_1 VALUES ('102', 'Senior', 'David', 'H', '1989-07-12', '501');
Problem 8.5
UPDATE EMP_1 SET job_code = '501' WHERE emp_num = '107';
6
Problem 8.6
-- Deleting records from the EMP_1
table where specific conditions are met
DELETE FROM EMP_1
-- Specifying conditions for deletion
WHERE EMP_FNAME = 'William' --
Employee first name is 'William'
AND EMP_LNAME = 'Smithfield' --
Employee last name is 'Smithfield'
AND EMP_HIREDATE = '2004-06-22'
-- Employee hire date is '2004-06-22'
AND JOB_CODE = '500'; -- Job code
is '500'
