SQL
T rai ni ng Assi gnments
Program Code SQL
Version 1.1
Effective Date 23/10/2023
Hanoi, 10/2023
Training Assignments SQL Issue/Revision: x/y
RECORD OF CHANGES
*A - Added M - Modified D - Deleted
Date Changes A* Contents Version
M, D
14-Oct-2016 Create A Add the new assignments. v1.0
14-Oct-2018 Update M Template. v1.1
01-Jun-2019 Update M Update Objective v1.2
20-Oct-2023 Update M Update name code, logo v1.3
25e-BM/HR/HDCV/FSOFT v1.1 Internal use 2/5
Training Assignments SQL Issue/Revision: x/y
Contents
For the following assignments: ........................................................................................................4
Advanced DML Statements .............................................................................................................4
Assignment 3_Opt3: Employee Management System .................................................................4
25e-BM/HR/HDCV/FSOFT v1.1 Internal use 3/5
Training Assignments SQL Issue/Revision: x/y
CODE: SQL_Assignment3_Opt3
TYPE: Medium
LOC: n/a
DURATION: 180 MINUTES
For the following assignments:
• Print out respectively the screenshots to show the query results.
• Pack screenshots and SQL scripts or your answers into the zip file named
SQL_Assignment<i>_AccountName.zip (for instance: SQL_Assignment3_NamNT.zip) then handle to
the evaluator via email (XYZ@[Link] ) or follow the guidance of the class admin.
Advanced DML Statements
Assignment 3_Opt3: Employee Management System
Barems: a - 10%, b - 10%, c - 10%, d - 10%, e - 15%, f - 15%, g - 15%, h - 15%
Objectives: H5SD - SQL skills
Problem Descriptions:
In the database design for the Employee Management System of FSOFT Company, given tables of the
application with below:
EMPLOYEE:
• EmpNo: employee code, primary key.
• EmpName: employee name.
• BirthDay: birth day of the employee.
• Email: email of the employee. Make sure that the database will not allow the value for Email to be
inserted into a new row if that value has already been used in another row.
• DeptNo: department code of the employee.
• MgrNo: manager code, not null (default values to 0).
• StartDate: starting date of work.
• Salary: salary of the employee, data type is money (VND).
• Level: level of the employee (accepts value range from 1 to 7 only).
• Status: status of the employee (0: working, 1: unpaid leave, 2: out, default values to 0).
• Note: some note about employee, free text.
EMP_SKILL:
• SkillNo: skill code, foreign key.
• EmpNo: employee code, foreign key.
• SkillLevel: skill level of the employee (accepts value range from 1 to 3 only).
• RegDate: registration date.
• Description: skill description, free text.
Primary key (SkillNo, EmpNo)
25e-BM/HR/HDCV/FSOFT v1.1 Internal use 4/5
Training Assignments SQL Issue/Revision: x/y
SKILL:
• SkillNo: skill code, primary key, auto increment.
• SkillName: name of skill.
• Note: some note about skill, free text.
DEPARTMENT:
• DeptNo: department code, primary key, auto increment.
• DeptName: department name.
• Note: some note about department, free text.
Questions:
a. Create the tables with the most appropriate/economic field/column constraints & types. Add at least
8 records into each created tables.
b. Specify name, email and department name of the employees that have been working at least six
months.
c. Specify the names of the employees whore have either ‘C++’ or ‘.NET’ skills.
d. List all employee names, manager names, manager emails of those employees.
e. Specify the departments which have >=2 employees, print out the list of departments’ employees
right after each department.
f. List all name, email and skill number of the employees and sort ascending order by employee’s
name.
g. Use SUB-QUERY technique to list out the different employees (include name, email, birthday) who
are working and have multiple skills.
h. Create a view to list all employees are working (include: name of employee and skill name,
department name)
-- THE END --
25e-BM/HR/HDCV/FSOFT v1.1 Internal use 5/5