Set - 6

Question 76 :

Use the ADD_GROUP_ROW procedure to add a row to a static record group?
I) TRUE II) FALSE

Answer :

I) FALSE


Question 77 :

maxvalue.sql Select the Nth Highest value from a table?

Answer :

select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level


Question 78 :

Find out nth highest salary from emp table?

Answer :

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For E.g.:-
Enter value for n: 2
SAL
---------
3700


Question 79 :

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Answer :

SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)


Question 80 :

How you will avoid your query from using indexes?

Answer :

SELECT * FROM emp
Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp
where emp_no=1234;
i.e using HINTS