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

Description
screen shots of working solution and sample output as evidence.
Task 1: Structure Query Language (SQL) [20%]
1. Explain the two Data Control Language (DCL) statements, Grant, and Revoke with the correct syntax and
appropriate examples of each statement.
2. Explain the term ACID in the relational database with appropriate examples
Task 2: Data Manipulation [50%]
Consider the above ERD diagram and write the following SQL statements.
(Each SQL statement has 5 marks)
Note: First you need to create the above database/tables
(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 who are working in the Sales department for which the hire
date is between 1995 – 2008
2. Write an SQL statement to show all titles of employees who are getting more than £20000 salary and for
which the hire date was after 1985
3. Write an SQL statement to show the full names, salaries and gender of those in the HR department.
4. Write an SQL statement to show all employees’ names, department names and their departments’
managers.
5. Write an SQL statement to show a list of employees who were hired between 1980 and 1995 and whose
first name starts with A or S
6. Write an SQL statement to change the employee’s title who works in the finance department and whose
first name starts with ‘S’.
7. Write an SQL statement to increase the salaries of all employees up to 10 % for those who have been
working since 1997 and work in the Marketing department.
8. Write an SQL statement to delete employees’ record in the sales department and whose last name starts
with A or S
9. Create a database view to list the full names of all employees, salaries, their departments, and departments
manager
10. Create a database view to list all employees, departments and their departments’ managers of those who
were hired between 1990 and 2000.
Task 3: Database Testing [25%]

1. What is Database Testing and how Jet Airline’s database can be tested?
2. Develop the Jet Airline’s database testing template and provide screen shots of the testing process of the
following database constraints.
• Field size validation
• Null values
• Referential integrity (Primary/Foreign Key)
Task 4: Referencing and Bibliography [5%]
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.