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.

Q 121

I) 1. State whether the following is true or false

1) The object names of the SQL server can be upto 50 characters in length and are case-sensitive

2) The name of the tables can be same in the same database

3) The default property for the identity column is null

4) Identity columns must use the numeric datatype and have a scale of 0

Ans: F-30, T, F, T

2. Answer the following

1) A numeric column allows to store upto _38__ decimal places

2) The scale of the numeric datatype for identity column is_0___

3) Views can have __16__levels of nesting

4) Maximum number of parameters the stored procedure can accept is _255__

(set identity_insert table_name on)

3. Explain the views with check option

4. Give the function name for the description given

1) Largest integer less than or equal to the specified value-floor(num_expr)

2) Current active roles for user-show_role()

3) Returns a specified part of date_expr value as a string- datename(datepart,dateexpr)

4) Replace expr1, if null with expr2- isnull(expr1, expr2)

5) Generates string of int_expr spaces- space(int_exp)

6) Returns length of expr in bytes-datalength(expr)

5. What are the different kinds of locks in sybase?

Locking: The process of restricting access to resources in a multi-user environment to

maintain security and prevent concurrent access problems. SQL Server

automatically applies locks to tables or pages.

Shared locks—SQL server applies shared lock for read operations. All the processes can read, but no process can write. (Eg- select statement.)

Exclusive lock--- SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.

Update lock –allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed

II) The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

1) Find the maker producing at least two different PC having speed

not less than 750 MGz. Result set: Maker.

2) Find out Nth min price which includes pc,laptop,printer

2) For each group of laptops with the identical model number, add following record

into PC table: code: minimal code among laptops in the group +20;model:

laptop's max model number +1000;speed: maximal speed among laptops in the

group;ram: maximal ram size among laptops in the group *2 ,hd: maximal hd

capacity among laptops in the group *2;cd: default value;price: maximal price

among laptops in the group divided by 1.5;

insert pc(code,model,speed,ram,hd,price) select min(code)+20,model+1000,max(speed),max(ram)*2,max(hd)*2,max(price)/1.5 from laptop group by model

Q 15

I)

1.What are the two types of updates in Sybase?

2.Explain the steps performed by Deferred update when the modification statement have a cascading effect on

unique index column?

3.Direct update will occur when the following are true:

a)Variable –length column modification

b)The table has an update trigger

c)Join clause are used in update statement

d)The affected columns are not used for Referential Integrity

4. Explain Cheap direct update

5. Explain Expensive direct update

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 out the battle ships [Taking Outcomes table into the account]

2. Find out Average no of guns for Japan ships[Taking Outcomes table into the account]

3. Find out the Average displacement for the HeadShips and round it to the integer

4. Find the names of ships sunked in battles and the names of corresponding battles.

Q 14

1.What are the different types of locks available in Sybase?

2.What is Demand locks and Hotspots?

3.What are the Isolation levels in Transaction?

4.What is Optimistic locking?timestamp and primarykey

5.What is dead lock?

II) The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

Find the maker producing at least two different computers (both PC and laptop) having speed

not less than 750 MGz. Result set: Maker.

23ans: SELECT DISTINCT maker FROM product a,pc b,laptop c

WHERE(( b.speed >= 750 AND c.speed >= 750)AND a.model = b.model )OR(( b.speed >= 750 AND

c.speed >= 750) AND a.model = c.model )

2) Find out Nth max price which includes pc,laptop,printer

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name

3) For each group of laptops with the identical model number, add following record into PC table:
code: minimal code among laptops in the group +20;model: laptop's max model number +1000;
speed: maximal speed among laptops in the group;ram: maximal ram size among laptops in the

group *2 ,hd: maximal hd capacity among laptops in the group *2;cd: default value;

price: maximal price among laptops in the group divided by 1.5;

insert pc(code,model,speed,ram,hd,price) select min(code)+20,model+1000,max(speed),max(ram)*2,max(hd)*2,max(price)/1.5 from laptop group by model

Q 13

I)

1.If i want creation text of a particular object (such as SP,Trigger,view,rule,default)what shall i do other than

sp_helptext and defncopy

2.State wether the statement is true or false:

a) with a cursor locking is performed at table level

b)to achive fast bcp we should go for sp_dboption

c) The stored procedure parse tree is read in from disk,and sql server generates a query plan in procedure

cache

d)statistics have been updated on the table and you want stored procedure to create a new query plan based

on that updation we use WITH RECOMPILE to

3.what is difference between command permission and object permission

4. The Transaction log is

a)Shared by all users of a database

b)modified in cache

c)usefully manipulated or read with SQL

d)readable in any useful format

5.what is differenc between count() and count(*) explain with example

count()-returns no of non null expressions

count(*) -return the no of rows found

II) The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

1) Find the printer makers which also produce PCs with the lowest RAM and the highest-speed processor among PCs with the lowest RAM. Result set: maker

25ans: select maker from product where model in (select model from pc where speed in (select max(speed) from pc group by ram) and ram in (select ram from pc where ram in (select min(ram) from pc))) and maker in (select maker from product where type='printer')

2) Find the maker producing at least two different computers (both PC and laptop) having speed not

less than 750 MGz. Result set: Maker.

23ans: SELECT DISTINCT maker FROM product a,pc b,laptop c

WHERE(( b.speed >= 750 AND c.speed >= 750)AND a.model = b.model )OR(( b.speed >= 750 AND c.speed >= 750) AND a.model = c.model )

3) Find out Nth max price which includes pc,laptop,printer

Q 12

I) 1.. How Does Sybase know to what extent a transaction has to be rolled back ?

2. How do you check whether the rollback was correct?

3. State wether the statement is True or False:

a) -E option is use to handle error in BCP

b) -m option default value is 1

c) Model of operation apply only on BCP in.

d) BCP does not invoke Rules,Constraints,Defaults,Trigger

4. Explain the Defncopy options listed below

-I,-J,-S,-P

5. Explain the command

isql –Usa -P mytext.txt

II) The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

1. Find the model number, speed and hard drive capacity of the PCs having 12x CD and prices less than $600 or having 24x CD and prices less than $600 and which was produced by maker A.

Ans: select model,speed,hd from pc where cd in('12x','24x') and price<600

2. Find out the models and prices for all the products (of any type) produced by maker B.

ans: select distinct model,price from pc where model in(select model from product where maker='B') union select distinct model,price from laptop where model in( select model from product where maker='B') union select distinct model,price from printer where model in(select model from product where maker='B')

3. Find out the average speed of the pc priced over $500.

4. Delete from PC table the computers having maximum hdd size or minimal ram size.

Ans: delete pc where ram in(select min(ram) from pc) or hd in(select max(hd) from pc)

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).

Q 9

I) 1.What are the limitation on trigger?

2.Explain Update Trigger

3.What is Transaction and its characteristic?

4.How the transaction control statements affect @@trancount

Statement value of @@trancount

1.Begin tran -----

2.Commit tran -----

3.Save tran -----

4.Rollback trigger -----

5.Rollback tran -----

6.Rollback tran save item -----

5. What is Rollback Transaction and how it is differ from Rollback Trigger?

6. What is difference between chained and unchained mode 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. Modify data in Classes table so that gun calibers are measured in centimeters (1 inch = 2.5cm), and the

displacement - in metric tons (1 metric ton = 1.1 tons). Calculate displacement to within integer.

Ans: update classes set bore=bore*2.5,displacement=round(displacement/1.1,0)

2. Delete from Ships table all the ships that belongs to USA .

3. Find the names of ship in the ships sunked in battles and the names of corresponding battles.