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