Q 01. Which of the following SQL queries can be used to fetch ALTERNATE records from a…
- User
Q 01. Which of the following SQL queries can be used to fetch ALTERNATE records from a table 'emp' (Even Numbered)? Ops: A. Select * from emp where rowid in (select decode(mod(rownum,2),0,rowid) from emp);
B.Select * from emp where rowid in (select decode(mod(rownum,2),0, null) from emp);
C.Select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
D.Select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
- Assistant
To fetch alternate (even-numbered) records from the
emptable in SQL, you can utilize theROWNUMpseudocolumn in combination with theMODandDECODEfunctions. Let's analyze each option provided:Option A:
SELECT * FROM emp WHERE rowid IN ( SELECT DECODE(MOD(rownum, 2), 0, rowid) FROM emp );- Explanation: The
MOD(rownum, 2)function checks if the row number is even (0). If it is,DECODEreturns therowid; otherwise, it returnsNULL. The outer query then selects records whoserowidis in the list of evenrowids, effectively fetching even-numbered records.
Option B:
SELECT * FROM emp WHERE rowid IN ( SELECT DECODE(MOD(rownum, 2), 0, NULL) FROM emp );- Explanation: Here, if the row number is even,
DECODEreturnsNULL. Otherwise, it also implicitly returnsNULL(since no default is provided). This means theWHEREclause will always evaluate toNULL, and no records will be returned.
Option C:
SELECT * FROM emp WHERE rowid IN ( SELECT DECODE(MOD(rownum, 2), 0, rowid, NULL) FROM emp );- Explanation: Similar to Option A, if the row number is even,
DECODEreturns therowid; otherwise, it explicitly returnsNULL. This effectively filters out odd-numbered records, fetching only even-numbered ones.
Option D:
SELECT * FROM emp WHERE rowid IN ( SELECT DECODE(MOD(rownum, 2), 0, NULL, rowid) FROM emp );- Explanation: In this case, if the row number is even,
DECODEreturnsNULL; otherwise, it returns therowid. This would fetch odd-numbered records, which is not what we're aiming for.
Conclusion:
Both Option A and Option C correctly fetch even-numbered (alternate) records from the
emptable. They achieve this by returning therowidonly for even row numbers andNULLotherwise, allowing theWHEREclause to filter accordingly.Correct Answers:
A and C - Explanation: The