Data Modelling Sql Language Assessment Answer

Task 1:  Structure Query Language (SQL) [20%]

1.Explain the two Data Manipulation Language (DML) statements, Update and Delete, with correct syntax and appropriate examples of each statement.

2.Explain the two Data Control Language (DCL) statements, Grant and Revoke, with correct syntax and appropriate examples of each statement.

Task 2: Data Manipulation

Consider the above ERD diagram and write the following SQL statements. Each SQL statement has 5 marks.

Note: First need to create above database/tables

Kindly see the SQL script in appendix to create the above database / tables and to insert the data)

1.Write an SQL statement to list all employees with their full names, gender and salary

2.Write an SQL statement to show the title of all employees and their department name.

3.Write an SQL statement to show the full names and gender who belong to department number 'd004'.

4.Write an SQL statement to show the all departments and their department’s managers.

5.Write an SQL statement to show a list of department’s managers who were hired after 1990

6.Write an SQL statement to change any employee’s date of birth. Assume the employee has just phoned in with his/her last name.

7.Write an SQL statement to increase salaries of all employees up to 5.5% who are working from 1997.

8.Write an SQL statement to delete employee’s record who belongs to department 'd004' and ID is 10003.

9.Create a database view to list full names of all employees their departments and salaries.

10. Create a database view to list all departments and their department’s managers, who were hired between 1990 and 2000.

Task 3: Database Testing

1.What is Database testing and why database testing is important?

2.Provide the screen shots of testing process on Employees database for the following constraints

field size validation

Null values

Check constraints.

referential integrity

Task 4: Referencing and Bibliography

You are required to follow the Harvard referencing system when citing others' work. An accompanying list of references must also be provided as part of your report.