I) 1What is Trigger? how many trigger can a table have and what are?
| If you are create trigger for insert in which you are going to insert 10 rows how many time it will fire? |
2.What are the limitation on trigger?
3.Explain Delete Trigger
4.Explain Insert Trigger
5.Explain Update Trigger
6.Explain Trigger in Transaction
II) Under consideration is the database of naval ships that took part in World War II. The database has the following relations:
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 the database).
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. In accordance with the Washington International Treaty on the begining of 1922, it was prohibited to build battle ships with displacement more than 35 thousand tons. Define the ships which violate this treaty. Result set: ship name.
34 ans:b) select s.name from ships s where s.class in(select c.class from classes c where s.class=c.class and c.displacement >=35000 and c.type='bb')and s.launched>1921
2) List the names of head ships in the database (taking into account Outcomes table).
36)ans: select name from ships where name in(select class from classes) union select ship from outcomes where ship in(select class from classes)
3) Find the ship which have the second largest gun among the databases
1 comment:
I find the query posted as an answer to the treaty question to be quite odd. I don't recall ever seeing an IN clause subquery that correlates on the column the IN clause is matching. I hope I don't see another one any time soon. With the correlation it could have been an equality test, rather than an IN clause, particularly since the column returned is the key to the table. Or better yet just write it as an EXISTS test. Or as a JOIN for that matter, since the join would be on the key to the table in the subquery.
Post a Comment