SQL Queries for Employee Database and Importance of Database Testing

This article discusses SQL queries for an employee database including listing employees with their full names, salaries, and department names, as well as the importance of database testing and examples of tests such as field size validation, not null values, primary keys, and foreign keys.

COVER PAGE

Task 1:
1. Write an SQL statement to list all employees with their full names, hire date and title
SQL Code and Result
For this sql Query a list of all employees with their dull names, the date they were hired and
their title is needed. Since the employee table stores the name of the employee as first name
and last name as two separate columns the two column have to be joined using a MySQL inbuilt
function called concat() is used to concatenate the first and the last name of the employee. To
get the title of the employee an inner join has to be done on the titles table.
2. Write an SQL statement to show the salary of all employees and their department name.
SQL Code and result

For this query the full names of the employee, salary and the department that the employee is
in are needed. Concatenation is needed to display the first name and the last name of the
employee as one name. The salary field is found on the salaries table thus an inner join between
salary table and employee table is needed. To get the department name that the employee is in
a join between three tables is needed. The first join is between the employee table and the
dept_emp table. The next join is between the dept_emp table and the department table . The
dept_emp table is a joint table linking the employee table and the departments table.
3. Write an SQL statement to show the full names and genders of HR department staff.
SQL Code and Results

This query gets the full names and gender of all employees working in the human resources
department name. To get the full name of an employee concatenation of the first and the last
name is needed. To apply the condition to filter only employees working in the human resources
department, join between three tables is needed. The first join is between the employee table
and the dept_emp table. The next join is between the dept_emp table and the department
table . The dept_emp table is a joint table linking the employee table and the departments
table. A conditional statement is then applied to fetch only employees working in the human
resources department.
4. Write an SQL statement to show the all departments’ name and their departments’
managers.
SQL Code and Results

To get the department name and the manager of each department a join between three tables
is needed. The first join is between departments table and the dept_manager table. The next
join is between the dept_manager table and the employees table. The dept_manager is the joint
table joining employee and departments table. The full name of the manager is a result of
concatenating the first name and the last name.
5. Write an SQL statement to show a list of department managers who were hired after
1986
SQL Code and Result;

To get the department name and the manager of each department for all managers hired after
1986 a join between three tables is needed. The first join is between departments table and the
dept_manager table. The next join is between the dept_manager table and the employees
table. The dept_manager is the joint table joining employee and departments table. The full
name of the manager is a result of concatenating the first name and the last name. a conditional
statement is applied on the hire date field where the query is only supposed to return only
managers hired after 1986.
6. Write an SQL statement to change any employee’s title. Assume the employee has just phoned
in with his/her last name.
SQL Code and result before update:

Update Query and Results
Select query and results after update.
This query is used to update the title of an employee assuming that we only know the last name
of the employee. As shown in the first screenshot before the update, the employee with the last

name kalloufi is an Assistant engineer. The second screenshots shows the update query and the
results of the execution of the query. The third screenshots shows the same employee with a
different title meaning that the update query based on the last name of the employee was
successful.
7. Write an SQL statement to delete employee’s record who belongs to department 'd004'
and ID 10003.
SQL Code and result
To delete an employee under employee id 10003 working in department d004, a join is needed
between the employee table and the dept_emp table as shown in the query and results
screenshot above.
8. Create a database view to list full names of all employees, their department managers
and salaries.
SQL Code and Result of execution

Select from the view sql and results
The employee manager_ details is created to get all employee names, their managers and their
salaries. The first screen shot shows the sql query used to create the query and the result of
running the code. The second screenshot shows the results of the fetch statement.
9. Create a database view to list all departments and their department managers, who
were hired between 1980 and 1990.
SQL Code and Results

Fetch from department__managers view.
10. Write an SQL statement to increase salaries of all employees up to 10% who are
working in marketing department
SQL and Result before update

Update Query and Results
SQL query and results after the update

The update query updates the salary of all employees in the marketing department. The first
screenshot shows the salaries of the employees before the update is done. The second query
shows the update query and the results. The third query shows the query and the results after
the successful execution of the update query proofing the update query was successful
Task2: Database testing.
1. Discuss why database testing is important and give examples to illustrate your
answer?
Database testing involves running tests on the database to make sure the database
meets all the requirements. There are different types of tests that can be done on a
database including white box testing, black box testing and integration testing. Testing of
a database is very important because it’s a process undertaken to ensure that the
database meets all requirements. Examples of tests done on a database include;
 ACID properties validation- Testing the ACID properties involves testing the Atomicity,
Consistency, Isolation and Durability (Goel, 2018) of the database. Each of the property
tested independently on the target database.
o Atomicity- This type of test is done to ensure that all transactions pass or fail.
o Consistency- This test is done to determine whether transactions done on a
database will leave it in a consistent state.
o Isolation- This test is done to ensure that multiple transactions executed
simultaneously should execute as if the execution is happening in order.
o Durability- This test is done to test that transactions that are committed are
permanent.

2. Tests
a. Field size validation
Field size validation involves testing the size of the data for a specific column during
insert and update transactions. For example a column that is only supposed to take four
characters should not accept more than 4 characters.
For example inserting data in the departments table and exceeding the characters for
the dept_no column.
According to the output the data is truncated to 4 characters automatically but this is
dependent on the type of server. MySQL supports data truncation up to the valid size
b. Not null values
This type of testing is done to ensure that columns that should not accept null values
throw an error if an insert or update transaction is done. For example trying to update
the departments table and setting the department name to null.
According to the query and the output, this transaction results to a warning because the
dept_name column cannot accept a null value thus the transaction fails
c. Primary key
A primary key is supposed to unique and not null at all times thus violating any of these
rules means that the transaction should fails. Testing can be done by doing update or
insert transactions for a primary key that already exists. For example;

The transaction fails because of a duplicate key as shown in the output.
d. Foreign key
Testing foreign keys involves testing of database integrity where by data between
parent and child tables should always be consistent. For example testing if a record was
inserted into the dept_emp table for a department that does not exist or for an
employee that does not ecist, the transaction should fail
References
Goel, A. (2018). Database Testing: White Box and Black Box - Magoosh Data Science Blog. [online]
Magoosh Data Science Blog. Available at: https://magoosh.com/data-science/database-testing-white-
box-and-black-box/ [Accessed 4 Dec. 2018].