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;

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;

0 comments:

Followers

About Me

My photo
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

Subscribeto this blog
Follow me onTwitter
Connect onFacebook
Gallery onFlickr

Logo

Logo

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.