Q 6

I) 1.Explain the Following

a) How do you check the batch for syntax,and then stops

b) How to tell the server to stop reporting the number of rows returned

c) Set statistics io on

d) Set noexec on

2.What is Cursor and the steps involed in creating the Cursor

3.Why we should go for close and deallocating the cursor

4.What is @@sqlstatus

5. What is isnull function

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. Find the classes including only one ship in the database (taking into account Outcomes table).

2. Define the names of all ships in the database launched before 1941.

3. Find the all ship names consisting of three or more words (for example King George V)

4. For each class, define the year in which the first ship of this class was launched.
Result set: class, year

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