SQL statements for data manipulation and database testing on Employees database
Verified
Added on 2023/05/30
|19
|1637
|351
AI Summary
This documentation provides SQL statements for data manipulation and database testing on Employees database. It also discusses the importance of database testing and provides examples of field size validation, not null values, primary key, and foreign key.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
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
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
2.Write an SQL statement to show the salary of all employees and their department name. Query: selectsalaries.emp_no,salary,dept_namefrom(dept_empinnerjoindepartmentson departments.dept_no=dept_emp.dept_no)innerjoinsalarieson 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 joindept_empondept_emp.emp_no=employees.emp_no)innerjoindepartmentson 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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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) innerjoindepartmentsondepartments.dept_no=dept_manager.dept_nowhere 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
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
7.Write an SQL statement to delete employee’s record who belongs to department 'd004' and ID 10003. Query: deleteemployeesfromemployeesinnerjoindept_empon 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)ASemployee_name,salary,dept_noFROM ( ( employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no ) INNER JOIN dept_empONdept_emp.emp_no=employees.emp_no))ASemployeeListINNER JOIN(SELECTdept_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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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
Picture 15
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
10.Write an SQL statement to increase salaries of all employees up to 10% who are working in marketing department Query: UPDATE employees inner join dept_emp on dept_emp.emp_no=employees.emp_no inner join departmentsondepartments.dept_no=dept_emp.dept_noinnerjoinsalarieson employees.emp_no=salaries.emp_no set salary = 1.1*salary where dept_name like 'Marketing' Output: Picture 16
Task 2:Database Testing[35%] 1.Discuss why database testing is important and give examples to illustrate your answer? (15 marks) Solution: Database is defined as the collection of tables that are linked with one another. These tables store information about the particular application for which it is developed. Database are used in complex application like banking to store the account details of the customer, transactions performed by them etc. The data stored in the database must be correct, so it must be tested for correctness to ensure the proper functioning of the banking system. Database testing is a software testing. The database is tested such that i)The data stored in the database is same as the one entered by the user ii)The data displayed in the UI to the user is same as the stored data Some of items tested while doing database testing are i)Database schema is implemented correctly as specified in the design document ii)Transaction are performed correctly, maintaining ACID properties iii)Ensure that the triggers are executed properly on performing particular operation iv)The procedures/ function calls are working correctly and ensure that function returns the correct value v)Check for the field constraints vi)Table columns are inserted with the correct value from the front-end application vii)Front end application correctly displays the table column value. Thus, the database testing is classified broadly into 3 types: 1) Structural Testing 2) Functional Testing 3) Non-functional Testing Structural testing involves testing the table, attributes, views, procedures/functions, trigger of the database. Functional testing involves testing the database from the external user point of view. Functional testing includes both black box and white box testing. Non-functional testing includes testing for load, stress, risk determination, optimization etc. 2.Provide the screen shots of the testing process on Employees database with the following constraints field size validation with an example (5 marks)
Not Null values with an example(5 marks) Primary Key with example(5 marks) Foreign Key with example(5 marks) Solution: Field size validation: This constraint checks for the field size of the attribute before insert/update operation. If the length of the data exceeds the field size the insert/update operation is not executed, and error is thrown by the mysql. For example: In the Departments table, dept_no field has the field size of 4 characters. So, when the data of size greater than 4 is inserted the operation is not executed. Query: Insert into departments values ('d1001','Work force'); Output: Picture 17 Not Null Validation: When an attribute of the table is mentioned as not null, insertion of the data with null values in the field is forbidden with an error message. For example:
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
In the Departments table the attribute dept_name is specified as NOT NULL. So when we try to insert the data in the departments table with dept_name as null, an error message is displayed. Query: Insert into departments(dept_no) values ('d100'); Output: Picture 18 Primary Key Validation: Primary key is the attribute of the table which uniquely identifies each row in the table. When we insert a data into the table, the primary key data should be unique i.e. same key value must not exist already in the table. If primary key violates the operation is not denied with a error message for primary key violation. For example: In the Departments table, dept_no is the primary key. The table already contains the department with dept_no ‘d001’. So when a data is inserted again with dept_no ‘d001’ an error message is displayed. Query: Insert into departments values ('d001','Work force'); Output:
Picture 19 Foreign Key Validation: Foreign key is attribute in the one table which refers to the primary key in another table. When a data is inserted into the foreign key attribute, the database first checks if the corresponding primary key already exist in another table. If the primary key does not already exist, the operation reports the error. For example: In dept_emp table, the dept_no attribute acts as a foreign key referring to the dept_no attribute of the departments table. So, when a row is inserted in the dept_emp table, the data for dept_no is checked against the dept_no field in the departments table. If the particular dept_no does not exist an error message is thrown. Query: INSERT INTO `dept_emp` VALUES (10001,'d100','1986-09-26','9999-01-01'); Output: In the above insert statement, the dept_no 'd100' does not exist in the department table.
Picture 20 References: Laine Campbell, Charity Majors, "Database Reliability Engineering: Designing and Operating Resilient Database", O'Reilly Publisher, 2017 Tutorial Point, 2018, Database Testing [Interactiv] Available at: https://www.tutorialspoint.com/software_testing_dictionary/database_testing.htm[Accessat December 2018]. Tutorial Point, 2018, SQL - SELECT Query [Interactiv] Available at: https://www.tutorialspoint.com/sql/sql-select-query.htm[Access at December 2018]. w3schools.com,2018.SQLUPDATEStatement.[Interactiv]Availableat: https://www.w3schools.com/sql/sql_update.asp[Access at December 2018]. w3schools,2018.SQLDELETEStatement.[Interactiv]Availableat: https://www.w3schools.com/sql/sql_delete.asp[Acces at December 2018].