gg

Part A first please

3. (b) Amend the SQL you wrote for part (a) so that it will only include lecturers who are NOT currently delivering any modules. Identify what the output should be when the SQL is executed for the data provided. Hint: Use an Outer join and think about how you restrict output from a select using GROUP BY (6 marks) 3. (e) Write the SQL needed to create a view called LecturerPayDetails which has the following columns Firstname, LastName, Modules Delivered, Salary and Bonus. You need to: Include the Firstname and lastname of the lecturer; Include ModulesDelivered which is the number of modules delivered by the Lecturer; Include Salary which is the lecturer salary; Include Bonus which is the lecturer bonus or 0 if bonus has no value; Include a row for each lecturer where the salary is less than 71000 or greater than 85000 Hint: Think UNION (6 marks) 3. Suppose the data in the Lecturer and ModuleDelivery tables is as shown below: Lecturer LECTURERID LASTNAME EMAIL FIRSTNAME SALARY BONUS 101 Byrne pb@techu.com Pat 75000 (null) 102 Smith ss@techu.com Sam 89000 7000 103 Dillon ad@techu.com Andrew 70000 7000 Module Delivery LECTURERNO MODULECODE 101 P101 101 D102 102 D102 102 P101 102 L103 (a) Write SQL to calculate for each lecturer the total number of modules they deliver and output this with their salary and bonus. You need to: Format your output to follow this template: Lecturer is teaching and a bonus of . NOTE: You do not include the in your output. Retrieve firstname, lastname, salary and bonus and calculate the no. of modules being taught. Output one row for each lecturer, Output 0 if a bonus does not have a value for a lecturer; Explain how the SQL works. . Hint: This SQL requires a GROUP clause; Use a function to output 0 (8 marks) Question three continues on the next page