Q 2

I) 1. How to insert several rows with select statement

2. Compare Union and Union All

3. Compare count() and count(*)

4. Compare Truncate and Delete

5. Compare Select into and Insert into

6. Explain the Query:

a) select * from emp where salary like( select ….)

b) select * from emp where salary in(select ….)

c) select * from emp where exists(select ….)

d) select * from emp where salary=(select ….)

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. Insert into pc table all the columns except screen from laptop table where laptop.code =12 assume code column in pc and laptop tables are identity

2. Add to the Product table following products from maker Z:printer model 4003, PC model 4001 and laptop model

4002

Ans: insert into product select 'Z',4001,'pc' union all select 'Z',4002,'laptop' union all select 'Z',4003,'printer'

3. List out the second maximum price in the Laptop