SQL - Interview Questions

Can one retrieve only the Nth row from a table?
SELECT * FROM (
   SELECT name,ROWNUM RN FROM test_employee WHERE ROWNUM < 8 )
 WHERE  RN = 7;


http://www.orafaq.com/wiki/ROWNUM


Any other efficient way:
--returns the 7th row, assuming minimum 7 rows exists
SELECT rownum,name,id,mid FROM test_employee a
 GROUP BY rownum,name,id,mid HAVING rownum = 7;


--returns the fourth row, assuming minimum 4 rows exists
SELECT * FROM test_employee WHERE rownum=1 AND rowid NOT IN
(SELECT rowid FROM test_employee WHERE rownum <= 3);


--returns 8th row, assuming, min of 8 rows exists
SELECT * FROM test_employee t1
WHERE  rowid = (
        SELECT rowid FROM test_employee t1
        WHERE  rownum <= 8
        MINUS
        SELECT rowid FROM test_employee t1
        WHERE  rownum < 8);


--Adding a column after a specific column
ALTER TABLE tablename ADD columnname AFTER columnname;


CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old; .
alter table test_employee add  dept_id number;
alter table test_employee add  sex varchar2(1);


--to find the males and females in a company
select  sum(  decode(sex,'M',1,0)) MALE,
             sum(  decode(sex,'F',1,0)) FEMALE,
             count(decode(sex,'M',1,'F',1)) TOTAL
  from test_employee;


--To find the number of males, females department wise
select dept_id, sum(  decode(sex,'M',1,0)) MALE,
             sum(  decode(sex,'F',1,0)) FEMALE,
             count(decode(sex,'M',1,'F',1)) TOTAL
  from test_employee
 group by dept_id;


--2nd max from the table
select max(id) from test_employee where id <
(select max(id) from test_employee);


Implementing IF-THEN-ELSE login in select statement:
One can use the CASE statement or functions like DECODE, NVL, NVL2, NULLIF, COALESCE, etc.