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)