Sybase Questions Set II

(I)

1. What are the difference types of Joins in Sybase?

2. What is the difference between a sub-query and a correlated sub-query?

Ans: A subquery is a query that SQL Server must evaluate before it can process the main query. It doesn’t depend on a value in the outer subquery.

A correlated subquery is the one that depends on a value in a join clause in the outer subquery

3. Suppose I have a table with following column

E_Id

---------------

BLL12

BRR

BDGT8

NW23L

KO9G0

B___B

___BN

BSNMA

___12

BNG

What will be the output of the following query?

“select E_Id from table where E_Id like ‘___’”

Ans: BRR

% -0 or more

_ - any single chr

[]- any single chr listed in the bracket

4. Explain the following.

a) count()

b) count(*)

c) distinct

d) union

5. What is the difference between delete and truncate?

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 pc model which have the maximum price

2. Delete from PC table the computers having minimal hdd size

3. Maker A has passed manufacture of printers to maker Z. Perform necessary changes

4. Find the maker who produces matrix printer with minimum price

No comments: