a. Write a SQL query that would enable the user 'ww10_StaffMember' to view all of the...

 

  

Transcribed Image Text:

a. Write a SQL query that would enable the user 'ww10_StaffMember' to view all of the data in the ww10_student table. 1b. (1 query) Assuming the SQL query from #5 has executed, write a SQL query that would remove the granted permission for 'ww10 StaffMember' to view all of the data in the ww10_student table. 1c. (1 trigger) Using the database tables provided on the next page, create a trigger that will fire when an update to the ww10_tutor table takes place and the status of a tutor is changed to 'Dropped' or 'Temp Stop'. The trigger will update the ww10_match_history table by setting the end date of all records for the associated tutor to be the current date. a. Hint: To set the end date in a query, use the GETDATE() function, such as: SET EndDate = GETDATE() ww10 tutor (tutor id, tutor_cert_date, tutor_status) Data Type Column Name Allow Nulls 8 tutor_id int tutor_cert_date date tutor_status varchar(50) tutor_id tutor_cert_date tutor_status 1 100 2020-01-05 Active 2 101 2020-01-05 Temp Stop 3 102 2020-01-05 Dropped 103 2020-05-22 Active 104 2020-05-22 Active 6 105 2020-05-22 Temp Stop 7 106 2020-05-22 Active ww8_student (student_id, student_group, student_read_score) Column Name Data Type Allow Nulls 8 student_id int student_group int student_read_score numeric(2, 1) student_id student_group student_read_score 1 3000 3 2.3 2 3001 2 5.6 3002 3 1.3 3003 1 3.3 3004 2.7 6 3005 4 4.8 7 3006 3 7.8 8 3007 4 1.5 a. Write a SQL query that would enable the user 'ww10_StaffMember' to view all of the data in the ww10_student table. 1b. (1 query) Assuming the SQL query from #5 has executed, write a SQL query that would remove the granted permission for 'ww10 StaffMember' to view all of the data in the ww10_student table. 1c. (1 trigger) Using the database tables provided on the next page, create a trigger that will fire when an update to the ww10_tutor table takes place and the status of a tutor is changed to 'Dropped' or 'Temp Stop'. The trigger will update the ww10_match_history table by setting the end date of all records for the associated tutor to be the current date. a. Hint: To set the end date in a query, use the GETDATE() function, such as: SET EndDate = GETDATE() ww10 tutor (tutor id, tutor_cert_date, tutor_status) Data Type Column Name Allow Nulls 8 tutor_id int tutor_cert_date date tutor_status varchar(50) tutor_id tutor_cert_date tutor_status 1 100 2020-01-05 Active 2 101 2020-01-05 Temp Stop 3 102 2020-01-05 Dropped 103 2020-05-22 Active 104 2020-05-22 Active 6 105 2020-05-22 Temp Stop 7 106 2020-05-22 Active ww8_student (student_id, student_group, student_read_score) Column Name Data Type Allow Nulls 8 student_id int student_group int student_read_score numeric(2, 1) student_id student_group student_read_score 1 3000 3 2.3 2 3001 2 5.6 3002 3 1.3 3003 1 3.3 3004 2.7 6 3005 4 4.8 7 3006 3 7.8 8 3007 4 1.5

Answer rating: 100% (QA)

1 You cant provide schema for WW10StaffMember table so you can ... View the full answer