(Solved): Structure Query Language (SQL) ...

                Structure Query Language (SQL)                                                                  

1. Discuss the following SQL joins using practical examples
   1. Inner Join
   2. Right Join
   3. Left Join
2. Discuss the ACID property in a database with examples
   1. Atomicity
   2. Consistency
   3. Isolation
   4. Durability

Data Manipulation                                                                                                           

Consider the above ERD diagram and write the following SQL statements.

Firstly, you need to create the above database/tables.

Refer to the SQL script in the 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 the department managers.
5. Write an SQL statement to show a list of department 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 the record of any employees 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 the department managers, who were hired between 1990 and 2000. 

Database Testing                                                                                                                  

1. Discuss the Database testing and why it is important.
2. Provide screen shots of the testing process on the employees database with the following constraints
   • field size validation
   • Null values
   • Referential integrity

Referencing and Bibliography                                                                                             

You are required to follow the Harvard referencing system when citing others' work. An accompanying list of references as part of the work

1. All components of the assignment report must be word processed (hand written text or hand drawn diagrams are not acceptable), font size must be within the range of 12 point to 14 point including the headings, body text and any texts within diagrams.
2. Standard and commonly used fonts such as Times New Roman, Arial 
3. Your document must be aligned left or justified with line spacing of 1.5
4. All figures, graphs and tables must be numbered and labelled.
5. You must provide screen shots of working SQL statements and their output.
6. Material from external sources must be properly referenced and cited within the text using the Harvard referencing system.
7. All components of the assignment (text, diagrams, code, etc.) should be in one word file.