Saturday, December 17, 2011
SQL Social-Network Triggers Exercises
Question 1
Write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger runs, we should have ('Friendly', A) in the Likes table for every other Highschooler A in the same grade as 'Friendly'.
create trigger R1
before insert on Highschooler
for each row
when New.name="Friendly"
begin
insert into Likes
Select New.ID,ID from Highschooler where grade = New.grade ;
end;
Question 2
Write one or more triggers to manage the grade attribute of new Highschoolers. If the inserted tuple has a value less than 9 or greater than 12, change the value to NULL. On the other hand, if the inserted tuple has a null value for grade, change it to 9.
create trigger R2
after insert on Highschooler
for each row
when (New.grade is null)
begin
update Highschooler set grade=9 where ID=New.ID;
end;
|
create trigger R1
after insert on Highschooler
for each row
when (New.grade <9 or New.grade >12)
begin
update Highschooler set grade=null where ID=New.ID;
end;
create trigger R2
after insert on Highschooler
for each row
when (New.grade is null)
begin
update Highschooler set grade=9 where ID=New.ID;
end;
|
create trigger R1
after insert on Highschooler
for each row
when (New.grade <9 or New.grade >12)
begin
update Highschooler set grade=null where ID=New.ID;
end;
Question 3
Write one or more triggers to maintain symmetry in friend relationships. Specifically, if (A,B) is deleted from Friend, then (B,A) should be deleted too. If (A,B) is inserted into Friend then (B,A) should be inserted too. Don't worry about updates to the Friend table.
create trigger R1
after insert on Friend
for each row
begin
insert into Friend values (New.ID2, New.ID1);
end;
|
create trigger R2
after delete on Friend
for each row
begin
delete from Friend where ID1=Old.ID2 and ID2= Old.ID1;
end;
Question 4
Write a trigger that automatically deletes students when they graduate, i.e., when their grade is updated to exceed 12.
create trigger R1
after update on Highschooler
for each row
begin
delete from Highschooler where grade >12;
end;
Question 5
Write a trigger that automatically deletes students when they graduate, i.e., when their grade is updated to exceed 12 (same as Question 4). In addition, write a trigger so when a student is moved ahead one grade, then so are all of his or her friends.
create trigger R1
after update on Highschooler
for each row
begin
delete from Highschooler where grade >12;
end;
|
create trigger R2
after update on Highschooler
for each row
begin
update Highschooler set grade=grade+1 where ID in (Select ID2 from Friend where ID1=New.ID);
end;
Question 6
Write a trigger to enforce the following behavior: If A liked B but is updated to A liking C instead, and B and C were friends, make B and C no longer friends. Don't forget to delete the friendship in both directions, and make sure the trigger only runs when the "liked" (ID2) person is changed but the "liking" (ID1) person is not changed.
create trigger R2
after update on Likes
for each row
when Old.ID2<>New.ID2 and Old.ID1 = New.ID1
begin
delete from Friend where ID1=Old.ID2 and ID2=New.ID2;
delete from Friend where ID2=Old.ID2 and ID1=New.ID2;
end;
Subscribe to:
Post Comments
(Atom)
Followers
About Me
- the edwards
- welcome to my blog! I'm Oswald Edward, a professional IT Specialist and entertainer. if you need to request a movie contact me with the email shown! Thanx
Contact Me
Email me at oedward38@gmail.com
Search
Follow me on
Blog Archive
Logo
Facebook Badge
Copyright
© 2013 by Oswald Edward.
You're welcome to link to this site or use a single image and brief description to link back to any post. Republishing posts in their entirety is prohibited without permission.
0 comments:
Post a Comment