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’)

FROM

(

SELECT TO_DATE (’01-JAN-2000′,’DD-MON-YYYY’)+LEVEL-1 C_DATE

FROM DUAL

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

A, 3
B, 5
C, 2
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


Products Repeat
—————————
A, 3
A, 3
A, 3
B, 5
B, 5
B, 5
B, 5
B, 5
C, 2
C, 2


A: SELECT PRODUCTS,REPEAT

FROM T, (SELECT LEVEL L FROM DUAL

CONNECT BY LEVEL <= (SELECT MAX (REPEAT) FROM T)

) A

WHERE T.REPEAT >= A.L

ORDER BY T.PRODUCTS;