Quintiles Interview Questions

1)How will you convert date -09-jan-2013 to 01-jan-2013?

Ans)By using Trunc function

Ex: trunc(to_date(’09-JAN-2013’),’YEAR’)

2) How will you join the dept_id in the below tables(employee and department),having null value?

Eid	Dept_id		Dept_id	Department
1	NULL		NULL	ABC
2	NULL		NULL	DEG

Ans)SELECT EID,Department from Tab_name where nvl(Dept_id,1)=nvl(dept_id,1)

3)Find out the monthly Sale and Monthly Average in the below table.

Pid	Date	Sales
101	1-Jan-13	100
101	2-Jan-13	200
103	1-Feb-13	300
103	2-Feb-13	400

Ans)

SELECT SUM(SALES_S), EXTRACT(MONTH FROM DATE_S)  

 FROM TAB_NAME GROUP BY EXTRACT(MONTH FROM DATE_S)

MONTHLY AVERAGE :

SELECT SUM(SALES_S)/2 AS AVG, EXTRACT(MONTH FROM DATE_S)  

 FROM TAB_NAME GROUP BY EXTRACT(MONTH FROM DATE_S)

4)How will you find count of EMP working in multiple Department?

Ans) select employee_id, count(department_id)

from employees

group by employee_id

having count(department_id) > 1

5)Would the result of Bangalore minus BANGLORE be the same ?

Ans) It would differ in ASCII Value of every digit,Hence minus would not give zero rather give Hex value.

6) what is the diff between intersect,union,unionall?

Ans)All are Set Operators in the first case.

Intersect-Record found in table A and table B (means intersecting in both).

select supplier_id

from suppliers

INTERSECT

select supplier_id

from orders;

Union ---

select supplier_id

from suppliers

Union

select supplier_id

from orders;

 combine the result sets of 2 or more SQL  SELECT statements. It removes duplicate rows between the various SELECT statements

Union All—Same QUERY but does not remove duplicates.