Q 11

I) 1.Explain update Trigger.

2.List out the Trigger Limitations

3. State the Following

a) Trigger are recursive

b) If more than one trigger is defined for a same action on a table, all the trigger will be executed

c) Can we create trigger on temporary table

d) Can we execute remote SP using trigger

e) A delete trigger will not fire on a truncate command

4. Explain sp_depends

5. Explain Sp_recompile

II) Under consideration is the database of naval ships that took part in World War II.
Classes(class,type,country,numGuns,bore,displacement)
Ships(name,class,launched)
Battles(name,date)
Outcomes(ship,battle,result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, class name does not coincide with any ship name in thedatabase).The relation Classes includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The relation Ships includes the ship's name, its class name, and launch year. The relation Battles covers the name and date of the battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the relation Outcomes. Note: the relation Outcomes may include the ships not included in the relation Ships.

1. To within two decimal digits, define the average amount of guns for the battleship classes

select round((sum(numguns)*1.00)/count(*),2) from classes where type='bb'

2. For each class, define the year in which the first ship of this class was launched.

Result set: class, year.

select distinct c.class,min(s.launched) from ships s,classes c where c.class*=s.class group by c.class

3. For each class, define the number of ships of this class that were sunked in a battles.

Result set: class, number of sunked ships

4. Find the names of the ships having the largest amount of guns among all the ships with the same displacement (taking into account Outcomes table).