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