Question { Wipro, 4606 }

4. Display the order number and client number from the ORDER
table. Output the result in the format. Client
ordered

Answer

select 'Client '|| nvl (to_char(client_no),'unknown') ||'
has placed order to the value of '|| nvl(sum(order_no),0)
order_summary
from order
group by client_no

OUTPUT...

ORDER_SUMMARY
---------------------------------------------------
Client unknown has placed order to the value of 200
Client 1006 has placed order to the value of 4
Client 1008 has placed order to the value of 0
Client 1004 has placed order to the value of 54
Client 1005 has placed order to the value of 450

Question { Wipro, 4884 }

5. Display full details from the ORDER_LINE table where the
item number is (first condition) between 1 and 200 (no > or
< operators) OR the item number is greater than 1000 AND
(second condition) the item cost is not in the list 1000,
2000, 3000 OR the order number is not equal to 1000.

Answer

select *
from order_line
where item_number between 1 and 200
or item_number > 1000
intersect
select *
from order_line
where order_number not in (1000,2000,3000)
or order_number <> 1000

Question { 80249 }

SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;

Answer

ANS:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2) as "result"
FROM dual;

Result
------
0

Explanation:
-----------
MOD(1600,10)---> 0
TRUNC(0,-1)----> 0
ROUND(0,2)-----> 0

Question { KPIT, 7429 }

Q1.all the depts which has more then 10 empls?
Q2.all the dept which does not have any emply?
Q3 all the emp which does not have any dept?
Q4 get all the emply detais with the dept details it dept is
exit otherwise any emp details?
Q5 how to debugg the dynamic sql and packages?

Answer

ANS-2:

SELECT department_id,COUNT(employee_id) AS "NO. OF EMPLOYEES"
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) = 0;

DEPARTMENT_ID NO. OF EMPLOYEES
201 0
311 0

Question { KPIT, 7429 }

Q1.all the depts which has more then 10 empls?
Q2.all the dept which does not have any emply?
Q3 all the emp which does not have any dept?
Q4 get all the emply detais with the dept details it dept is
exit otherwise any emp details?
Q5 how to debugg the dynamic sql and packages?

Answer

ANS-3:

SELECT employee_id , department_id
FROM employees
WHERE department_id IS NULL;