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