Sybase Questions Set V

1. Explain about Exists,Join,Distinct?

2. Write a query to find the duplicate rows from a table?

Select name, count(*) from tablename group by name having count(*)>1

3. You have created a view as Vi_emp and writing the query like select * from vi_emp order by name, Is the query have any Issue?

4.There are two tables master is Employees and the detail is Attendance. The Employee_ID field is common to both tables. How do you find the name of the employees those have missed more than 5 days of work in last 30 days

Select name from Employees a where a.id in (select b.id from attendance b where a.id=b.id and date between (lastmonth+1 ,lastmonth) presence=0 group by id having count(presence)>4)

5.State True or False

a)We can run Truncate command inside the Trigger F

b)Trigger can be recursive and stored procedure in reentrant

c)In the transaction rollback Trigger reset the @@trancount value to previous command value. T

d)In chain mode we have to end the transaction with commit or rollback Tran Statement.

6.Give me the Global variable names for the description given below

Error number reported for last SQL statement ( @@error)

Current transaction mode (chained or unchained)(@@tranchained)

Status of prevoius fetch statement in a cursor(@@sqlstatus)

Transaction nesting level(@@trancount)

Current process ID(@@spid)

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 makers producing at least three distinct models of PCs. Result set: maker, number of models.

Select maker,count(*) from product where type='pc' group by maker having count(*)>2

2.Find the model number of the product (PC, laptop) with the highest speed. Result set: model.

Select model from (select model,speed from pc where speed=(select max(speed) from pc) union select model,speed from laptop where speed=(select max(speed) from laptop)) a where speed=(select max(speed) from (select speed from pc union select speed from laptop) b)

3.Define the minimum price of color printer produced by each maker. Result set: maker, minimum price

select product.maker,min(pc.price) from product,pc where pc.price in (select min(price) from pc) and type='printer' and color='y'

4.Find the ram that are equal among two or more PCs. Result set: ram.

Select ram from pc where ram in (select ram from pc group by ram having count(*)>=2)