Q 8

I) 1.What are the Advantage of Stored Procedure?

2. What is Default Parameter in stored Procedure?

3. What are the limitation on Parameter?

4. What is Output Keyword in Stored procedure?

5. What are the Limitation on Stored Procedure?

6. What is the ‘with recompile’ and ‘sp_recompile’?

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. List out the head ships in the Database.

2. Find the ship classes which have at least one ship sunked in a battle.

Ans: select class from ships where name in(select ship from outcomes where result='sunk' ) union select class from classes where class in(select ship from outcomes where result='sunk')

3. Find the names of the ships belong to Japan having the gun caliber of 8 in (taking into account Outcomes table).

Ans: select name from ships where class in(select class from classes where country=’japan’and bore=8) union select ship from outcomes where ship in(select class from classes where country=’japan’ and bore=8)