QAC020C155A - SQL Queries and Database Testing Report 2018/19

This report provides a comprehensive overview of SQL queries and database testing techniques. It includes SQL code examples for tasks such as listing employees, showing salaries by department, and managing department managers. The report also covers database testing, explaining its importance and providing examples of field size validation, null value constraints, primary key uniqueness, and foreign key integrity. Practical examples and query results are included to illustrate the concepts. The report concludes with a discussion on database testing, emphasizing its role in ensuring data integrity and system reliability. Desklib offers a wealth of similar solved assignments and past papers to aid students in their learning journey.

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.