QAC020C155A - SQL Statements & Database Testing in Data Modelling

This report details the implementation of SQL statements for various database operations, including data retrieval, manipulation, and view creation. It covers essential SQL functions like CONCAT for combining data, joins for linking tables, and conditional statements for filtering results. The report also addresses database testing, emphasizing the importance of validating ACID properties (Atomicity, Consistency, Isolation, Durability) and data integrity through field size validation, null value constraints, and primary/foreign key validations. Practical examples and SQL code snippets are provided to illustrate each concept, offering a comprehensive guide to SQL and database testing. Desklib provides a platform to access this document along with numerous other solved assignments and past papers to help students excel.

COVER PAGE

Task 1: SQL Statements;
1. SQL to list all employees with their full names, hire date and title.
SQL Code:
select concat(first_name,'
',last_name),hire_date,title from employees inner join titles ontitles.emp_no=employees.em
p_no;
Results:
This query is used to fetch the full names of an employee, the date that the employee was hired,
and the title of the employee. Because the full names are saved as first name and last name the
query uses an inbuilt sql function called concat which concatenates the two columns while
adding a space between the two columns. To get the title a join between employees table and
titles table is needed. The type of join used is inner join using the emp_no column in both tables
thus the query returns all columns for which the emp_no in the employees table matches the
emp_no in the titles table.

2. SQL to show salaries and department name of all employees
SQL Code:
select concat(first_name,'
',last_name),salary,dept_name from employees inner join salaries onsalaries.emp_no=employe
es.emp_no inner join dept_emp on dept_emp.emp_no=employees.emp_no innerjoin departments on
departments.dept_no=dept_emp.dept_no;
Results:
This query is used to show the full names of each employee, their current salary and the
department name the employee works in. Concat function is used to show the full names of the
employee by concatenating the first name and the last name of the employee. The salary of an
employee is found in the salaries table thus an inner join is done between the salaries table and
the employee table using the emp_no column in both tables. To get the department name that
an employee works in, a join between employees table and departments table is done using the
joint table dept_emp.
3. SQL to show full names and genders of HR department staff
SQL Code:
select concat(first_name,'
',last_name),gender from employees inner join dept_emp ondept_emp.emp_no=employees.emp_no

inner join departments ondepartments.dept_no=dept_emp.dept_no where departments.dept_name=
'Human Resources';
Results:
This query is used to show the full names and the gender of all employees working in the human
resources department. The first name and the last name are concatenated using the concat
function. To join the employees and departments tables a join of the dept_emp supporting table
is done. Finally a condition is used to filter only employees working in the human resources
department.
4. SQL to show all department name and their department managers
SQL Code:
select dept_name,concat(first_name,'
',last_name) from departments inner join dept_manager ondept_manager.dept_no=departments.d
ept_no inner join employees onemployees.emp_no=dept_manager.emp_no;
Results:

This query is used to show the names of all departments and the names of the manager for
each respective department. To get the full names of the manager, concatenation of the first
and last name of the manager is done using the concat function. An inner join between the
departments table and dept_manager table is done and another inner join between the
dept_manager and employees table is done. The dept_manager is the joint table joining
departments and employees table.
5. SQL to show department managers that were hired after 1986
SQL Code:
select dept_name,concat(first_name,'
',last_name),hire_date from departments inner joindept_manager on dept_manager.dept_no=dep
artments.dept_no inner join employees onemployees.emp_no=dept_manager.emp_no where hire_da
te>='1987-01-01';
Results:

This query Is used to get the department name, the name of the manager, the date that the
manager was hired for all managers that were hired after 1986 which is from 1st of January 1987.
To get the full names of the manager, concatenation of the first and last name of the manager is
done using the concat function. An inner join between the departments table and
dept_manager table is done and another inner join between the dept_manager and employees
table is done. The dept_manager is the joint table joining departments and employees table.A
condition is used to filter all managers who were hired after 1986.
6. SQL to change an employee’s title using their lastname
SQL Code:
Select query before update.
select * from employees where last_name='Simmel';

Update Query
update employees,titles set title='Engineer' where last_name='Simmel' and employees.emp_no
=titles.emp_no;
Results:
Select query after update.
select * from employees where last_name='Simmel';

This query is used to update the date of birth of an employee using their last name. The first
query shows the details of the employees before the update is done. The second query updates
only the date of birth of the employee with the last name as Simmels that is why according to
the screenshot, only 1 row has been affected. The last query shows the details of the employee
after the update query is done.
7. SQL to delete an employee belonging to department d004 with id 10003
SQL Code:
delete from employees where emp_no in (select emp_no from departments,dept_emp where dept_
emp.dept_no=departments.dept_no anddepartments.dept_no='d004' and dept_emp.emp_no='10003')
;
Results:
This query is used to delete an employee with employee id 10003 who is working in department
d004. The employee
8. SQL to create view to list full names of all employees, their department managers and salaries
SQL Code:
create view employee_manager_details as select concat(e.first_name,'
' ,e.last_name) as employee,s.salary, concat(m.first_name,'
' ,m.last_name) as manager from employees e inner join salaries s on s.emp_no=e.emp_no inn
er join dept_emp de onde.emp_no=e.emp_no inner join departments d on d.dept_no=de.dept_no
inner join dept_manager dm on dm.dept_no=d.dept_no inner join employees m on m.emp_no=dm.e
mp_no;
Results:
Fetching the results of the query.

This view is created to get the full names of employees, their salaries and the full names of the
department that the employee works in.
9. SQL to create vies to list all departments and their department managers who were hired
between 1980 and 1990.
SQL Code:
create view dept_managers as select dept_name,concat(first_name,'
',last_name),hire_date from departments inner join dept_manager on dept_manager.dept_no=de
partments.dept_no inner join employees on employees.emp_no=dept_manager.emp_nowhere hire_d
ate between '1980-01-01' and '1990-12-31';
Results:
Select all the rows in the view.
10. SQL to increase salaries for all employees working in marketing department by 10%.
SQL Code:
Before Update:
select employees.*,salaries.salary from employees inner join dept_emp on dept_emp.emp_no=e
mployees.emp_no inner join departments on departments.dept_no=dept_emp.dept_no inner join
salaries on salaries.emp_no=employees.emp_no where departments.dept_name='Marketing'

Update Query
update salaries s,employees e,departments d,dept_emp de set s.salary=s.salary+
(s.salary*0.01) where s.emp_no=e.emp_no and d.dept_no=de.dept_no and e.emp_no=de.emp_no an
d d.dept_name='Marketing';
Results:
After Update
select employees.*,salaries.salary from employees inner join dept_emp on dept_emp.emp_no=e
mployees.emp_no inner join departments on departments.dept_no=dept_emp.dept_no inner join
salaries on salaries.emp_no=employees.emp_no where departments.dept_name='Marketing';
This query is used to update the salaries of all employees working in the marketing department.
To update the salary you have to join the employees table to the supporting dept_emp table
and then join it to the departments table and finally join it to the salaries table and restrict the
update for only those employees working in the department with the department name
marketing.
Task2: Database testing.
1. Why database testing is important.
Database testing is important because complex applications require complex backend databases
to support the application. Thus there is a need to test the database to make sure it meets the
all requirements required for the efficient and accurate operation of the front end part of the
application. Testing in databases is done for;
 Validation of the acid properties of the database.- This type of testing involves testing
the Atomicity, Consistency, Isolation and durability of each database transaction.
Atomicity is done to test whether a transaction passes or fails where it has to pass the
all-or-nothing rule. Consistency involves testing whether each transaction will leave the
database in a consistent state. Isolation property involves testing the database to make
sure that if multiple transactions are executed at once then state of the database should
be as if the transactions were executed in order. Finally durability involves testing that
once a transaction is committed, no external factors can change its outcome.

 Data integrity- Testing to ensure data integrity involves making sure that for all tables
that are related CRUD operations on each of the tables should maintain consistent data
in all the tables. For example an update in the parent table should result to changes in
the child tables.
2. Testing process.
a. Field size validation
Field size validation involves testing the database to make sure that the size of the field
specified for a specific column is not violated. For example if a column has varchar data
type and is restricted to a size of 50 then no data should have data exceeding 50 for this
column.
To test field size validation we insert data in departments table where the dept_no
should have is char(4) so we insert a record with more than 4 characters.
INSERT INTO `employees`.`departments` (`dept_no`, `dept_name`) VALUES ('d11111112'
, 'Logistics');
The query executes successfully but the value inserted in the table is trimmed to 4
characters as shown in the results below.
The original data was d11111112 but its trimmed to d111.
b. Not null values
Not null validation ensures that a column that has a property of null must all always
have a value during the insertion of a record. For example in the departments table the
dept_name has not property thus inserting a null value would fail.
Query

insert into departments (dept_no,dept_name)
values('d123',null);
When this query is executed it fails because dept_name should not have a null value.
c. Primary key
Primary key validation involves making sure no duplicate primary keys exist in a table
because every primary key is supposed to be unique. For example in the departments
table, inserting a duplicate entry key results to an error as shown in below.
d. Foreign key
Foreign key validation involves testing that no integrity constraints are violated. For
example you cannot insert a record in the child table for a primary key that does not
exist in the parent table. For example considering two tables; employees the parent
table and salaries the child table, you cannot insert a salary record for an employee that
does not exist as demonstrated in the screenshot below.