Module 2 – Databases and Backend Development
SQL Exercise - Intermediate
Overview
In this exercise, you will make use of pre-defined SQL scripts to query an existing database of employee
data. The source files for the database can be found on Github at bytebase/employee-sample-
database. For this exercise, you will make use of the “dataset_small” resource folder.
The resource folder for this exercise has also been included on the LMS. To help you navigate the
database structure, it is advisable that you review the ERD Schema – which has also been included.
Setup Instructions:
Step 1: Extract the resources folder you’ve downloaded from either Github or the LMS.
Step 2: Run the [Link] file in MySQL Workbench – this will create the required tables.
Step 3: Run the following SQL scripts to load the data into the table you’ve just created:
• load_department.sql
• load_employee.sql
• load_dept_emp.sql
• load_dept_manager.sql
• load_title.sql
• load_salary1.sql
Step 4: Once your successfully created the database and loaded all data the data, assume the role of
a database developer and/or administrator and complete the queries for each requirement that
follows.
Step 5: Download the “sql_intermediate_exercise_answers.sql” file from the LMS and modify the
script with your queries/answers.
Query Requirements
1. The HR department needs a report of all employees with a first name that begins with H. Write a
query that generates the first and last name of all these employees. Ensure that the results are
ordered by first name.
2. The HR department would like to include additional information to the previous report. Modify
your query to include the employee number, hire date and gender of the employees.
3. The payroll consultant requires a report of all current employees and their salaries. Write a query
that generates the employees first name, last name and salary.
4. After meeting with the finance manager, the payroll consultant has been requested to conduct a
salary review. The decision has been made to increase the salaries of all existing Assistant Engineers
by 5%. Create a query to achieve this.
5. Yishay Tzvieli, one of the Senior Engineers has decided to retire from the company. To ensure the
companies database is up to date, remove this employee from the database.
6. Since the company’s gradual implementation roll out of its digital transformation strategy, the CEO
has expressed a strong interest in strengthening its cybersecurity efforts. The first point of order is
to add this department to the database. Write a query that creates a new department named
Cybersecurity with a department number of d010.
7. The HR department has recently found a suitable candidate to lead the newly created
Cybersecurity department. You are required to add the new employee to the database. Write a
query to achieve this – below are the details of the employee:
• Employee Number: 11001
• Birth Date: 1982-05-25
• First Name: John
• Last Name: Doe
• Gender: M
• Hire Date: Current Date
8. A new job title is required for John Doe. The title awarded to him is Head of Cybersecurity.
Write a query that creates this new title which is to be assigned to John Doe – assume
assignment on the current date.
9. Given the previous query, John Doe will also lead the Cybersecurity department. Write a query
to assign him as the department manager – assume assignment on the current date.
10. John Doe does not yet have a salary assigned. Write a query that assigns him a salary of 85000
– assume assignment on the current date.
11. John Doe has expressed an interest in expanding the Cybersecurity team. After consulting with
the HR department, he would like to review the company’s internal talent for a new role –
Cybersecurity Analyst. John Doe is quite impressed with Weijing Bottner, an Assistant
Engineer at the company. Her portfolio, skills and competencies are ideal for the new role.
After offering Weijing the new role, she decided to accept the promotion. Write a query to
update Weijing Bottner’s job title to Cybersecurity Analyst.
12. With her new role, Weijing Bottner has also received a salary increase. Write a query that
captures her new salary earnings – below are the details:
• Employee Number: Remains the same
• Amount: 72000
• From Date: Current Date
• To Date: Assume indefinitely
13. In addition to the above, she has also been assigned to the newly created Cybersecurity
department. Write a query that captures her assignment to the new department – below are
the details:
• Employee Number: Remains the same
• Department Number: d010
• From Date: Current Date
• To Date: Assume indefinitely
Submission:
Save your SQL queries in the file named sql_intermediate_exercise_answers.sql and upload it to the
LMS. Share your code and results with your lecturer for feedback and further discussion.