University Database Development Report: Data Manipulation and Testing

This report presents a comprehensive analysis of SQL database development, covering both data manipulation and database testing aspects. Task 1 focuses on data manipulation, providing SQL queries to list employee details, salaries, department information, and to modify and delete employee records. It includes examples using SQL's `CONCAT_WS`, `INNER JOIN`, `UPDATE`, and `DELETE` statements, as well as the creation of database views. Task 2 delves into database testing, emphasizing its importance and providing examples of field size validation, not null values, primary key, and foreign key constraints, with corresponding SQL queries and screenshots to illustrate the concepts. The report references key SQL concepts and database testing principles, providing a practical understanding of database management systems. The assignment demonstrates the student's ability to use manipulation and querying tools including SQL and test and document relational database systems.

Documentation
Task 1: Data Manipulation:
1. Write an SQL statement to list all employees with their full names, hire date and title
Query:
Select CONCAT_WS(" ",first_name,last_name) as full_name, hire_date, title from employees
inner join titles on titles.emp_no=employees.emp_no;
Output:
Picture 1

2. Write an SQL statement to show the salary of all employees and their department name.
Query:
select salaries.emp_no, salary, dept_name from (dept_emp inner join departments on
departments.dept_no=dept_emp.dept_no) inner join salaries on
salaries.emp_no=dept_emp.emp_no;
Output:
Picture 2

3. Write an SQL statement to show the full names and genders of HR department staff.
Query:
Select CONCAT_WS(" ",first_name,last_name) as full_name, gender from (employees inner
join dept_emp on dept_emp.emp_no=employees.emp_no) inner join departments on
departments.dept_no=dept_emp.dept_no where dept_name like 'Human Resources'
Output:
Picture 3

4. Write an SQL statement to show the all departments’ name and their departments’ managers.
Query:
Select dept_name, CONCAT_WS(" ",first_name,last_name) as full_name from
(employees inner join dept_manager on dept_manager.emp_no=employees.emp_no)
inner join departments on departments.dept_no=dept_manager.dept_no;
Output:
Picture 4

5. Write an SQL statement to show a list of department managers who were hired after 1986
Query:
Select dept_name, CONCAT_WS(" ",first_name,last_name) as full_name from
(employees inner join dept_manager on dept_manager.emp_no=employees.emp_no)
inner join departments on departments.dept_no=dept_manager.dept_no where
year(hire_date)>1986;
Output:
Picture 5

6. Write an SQL statement to change any employee’s title. Assume the employee has just phoned in
with his/her last name.
Query:
update employees inner join titles on titles.emp_no=employees.emp_no set title="Engineer"
where last_name LIKE "Kalloufi"
Output:
Before Update:
Picture 6

Update:
Picture 7
After Update:
Picture 8

7. Write an SQL statement to delete employee’s record who belongs to department 'd004'
and ID 10003.
Query:
delete employees from employees inner join dept_emp on
employees.emp_no=dept_emp.emp_no
where employees.emp_no=10003 and dept_emp.dept_no LIKE 'd004';
Output:
Before Delete:
Picture 9

Delete:
Picture 10
After Delete:

Picture 11
8. Create a database view to list full names of all employees, their department managers and
salaries.
Query:
create view EmployeeManagerList as
SELECT employee_name, manager_name, salary FROM ( ( SELECT employees.emp_no,
CONCAT_WS(" ", first_name, last_name) AS employee_name, salary, dept_no FROM
( ( employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no ) INNER JOIN
dept_emp ON dept_emp.emp_no = employees.emp_no ) ) AS employeeList INNER
JOIN( SELECT dept_manager.dept_no, CONCAT_WS(" ", first_name, last_name) AS
manager_name FROM employees INNER JOIN dept_manager ON dept_manager.emp_no =
employees.emp_no ) AS managerList ON managerList.dept_no = employeeList.dept_no )
Output:
Picture 12

Picture 13

9. Create a database view to list all departments and their department managers, who were
hired between 1980 and 1990.
Query:
create view ManagerList as
Select dept_name, CONCAT_WS(" ",first_name,last_name) as full_name from (employees inner
join dept_manager on dept_manager.emp_no=employees.emp_no) inner join departments on
departments.dept_no=dept_manager.dept_no where year(hire_date) between 1980 and 1990;
Output:
Picture 14