SQL INTERVIEW QUESTION
Q. Write a query to generate sequence numbers from 1 to the specified number N?
A: SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=&N;
Q. Write a query to display only Friday dates from Jan, 2000 to till now?
A: SELECT C_DATE, TO_CHAR (C_DATE,’DY’)
SELECT TO_DATE (’01-JAN-2000′,’DD-MON-YYYY’)+LEVEL-1 C_DATE
CONNECT BY LEVEL <=
(SYSDATE – TO_DATE (’01-JAN-2000′,’DD-MON-YYYY’)+1)
WHERE TO_CHAR (C_DATE,’DY’) = ‘FRI’;
Q. Write a query to duplicate each row based on the value in the repeat column? The input table data looks like as below Products, Repeat
Now in the output data, the product A should be repeated 3 times, B should be repeated 5 times and C should be repeated 2 times. The output will look like as below
A: SELECT PRODUCTS,REPEAT
FROM T, (SELECT LEVEL L FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX (REPEAT) FROM T)
WHERE T.REPEAT >= A.L
ORDER BY T.PRODUCTS;