Type or paste question here

a) Create dept_manager table with emp_no as primary key and auto increment and not null for other attributes. Ensure the DBMS deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key. [3 marks] *

b) Show the employee last name, title and order by title in descending order. [1 mark] *

c) Find employee name that start with P in the first name or last name. [1 mark] *

d) List the employees first names and salaries where their salary is above the average salary among the employees. [2 marks] *

e) Create a trigger that will insert new emp_no, salary, from_date and to_date into salaries table after a new employee record is inserted. Assume the new employee salary is 10000 and from_date is current date and to_date is 9999-01-01. [3 marks] *

QUESTION 4 Consider the following ERD diagram and write the appropriate SQL statements. Figure 3: ERD diagram employees titles emp_no int(11) titlevarchar(50) from_date date to_date date O employees dept_manager emp no int(11) dept no char(4) from date date sto_date: date emplo employees emp_noint(11) birth_date: date first_name: varchar(14) last name : varchar(16) gender: enum (MF) hire date: date vo dept_emp emp no int(11) dept_no : char(4) from date date to_date: date employees salaries emp_noint(11) #salary: int(11) from_date: date to date date departments dept_no:char(4) dept_name : varchar(40)