Q 122

I) 1. Give some notes on Indexes.

2. Expalin the steps involved during the first execution and subsequent executions of a stored

procedure.

3.State whether the following statements are true or false

1) Wildcards can be contained in values passed to stored procedure-T(like)

2) Rules, defaults and column properties do not apply to parameters defined with user defiend datatypes -T

3) Parameter name should be 20 characters in length-F(29)

4) In order to make changes in the stored procedure, we can alter the stored procedure-F(drop)

5) The stored procedure return 0 on success and 1 for errors-F (0 , -1 to -99)

4. Explain the set commands given below

1) set rowcount (Instruct the server to rturn only the first n rows of data)

2) set statistics io on (Asks the server for the no. of logical and physical page requests)

3) set nocount on ( stops reporting the number of rows returned)

4) set noexec on ( parse and optimize, but don’t exec the query (used with show plan for looking at the plan without running a query)) showplan gives the final optimization plan for a query)

5) set statistics time on (requests the execution time)

5. What is isolation level, list different isolation levels in Sybase and what is default?

To avoid the manual overriding of locking, we have transaction isolation level which are tied

with transaction.

List of different isolation levels are isolation level 0,1,2,3.

Isolation level 1- this allow read operation can only read pages. No dirty reads are allowed.

Isolation level 0-This allows reading pages that currently are being modified. It allows dirty

read

Isolation level 2-Allows a single page to be read many times within same transaction and guarantees that same value is read each time. This prevent other users to read

Isolation level 3- preventing another transaction from updating, deleting or inserting rows for pages previously read within transaction

Isolation level 1 is the default.

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. Point out the battles in which at least three ships from the same country took part.
2. Find all ship names beginning with letter "R".
3. For each class, define the number of ships of this class that were sunk in battles. Result set: class, number of sunked ships.
4. For each class, consisting of at least three ships, define the number of ships (that must be at least 1) of this class sunk in battles. Result set: class, number of sunken ships.