Question 9:
Create a trigger named insertDoctor for the INSERT statement on the "Doctors" table so that when we execute an insert statement to add one or more rows to the "Doctors" table, if any of the inserted rows have the DepartmentID as null, the system will automatically set the DepartmentID of those rows to the DepartmentID of the department named 'Cardiology'.
For example, if we execute the following statements to insert two doctors (one with DepartmentID=NULL and the other with DepartmentID=5) and then select these two doctors,we can see that the DepartmentID of the doctor with DepartmentID=null is set to the id of the department named 'Cardiology' as shown in the following figure:
insert into Doctors (DoctorID, FirstName, LastName, Phone Number, Email, DepartmentID)values (70,'Van Chien', 'Nguyen', '0928367812', '
[email protected]', null),(71, 'Thi Binh', 'Nguyen', '0989278127', '
[email protected]',5)
select * from Doctors where DoctorID in (70,71)
DoctorID
70
71
FirstName
LastName
Phone Number
1
2
Email
DepartmentID
Van Chien Nguyen Thi Binh Nguyen 0928367812 0989278127
[email protected] 1
[email protected] 5
Picture 9.1
Question 10:
7 of 7
Paper No: 4
Write statements to delete from the Tests table two tests named 'Blood Test' and 'CT Scan'and to delete also from the Appointment Tests table all rows corresponding to those two tests.
Zoom
+ 91%
Close
