Oracle LEVEL and Recursive WITH Clauses examples

Knowledge of SQL is must if you are interested a career in databases. Demand for SQL-expertise is always high and is valued in the market. Oracle is a very popular secured database that is widely used across multinational companies. So, in this article, you get Oracle SQL questions that cover the most frequently asked interview questions and help you brush up your knowledge before the interview.

If you are a fresher or an experienced, these are the basic questions for you to test your SQL query writing skills.

Click download here to download all tables used in the questions. You must create all these table in your Oracle database to run through all SQL questions described in this article.

 

Tricky questions on LEVEL clause and WITH CTE queries

Q1. Write a query that prints the string as many times as it has number of characters.

Ans:

WITH ref AS ( SELECT 'Test LEVEL Clause' AS str FROM DUAL
)
SELECT str AS result FROM ref
CONNECT BY LEVEL <= length(str);

WITH ref(n, str) AS
( SELECT 1, 'Test LEVEL Clause' FROM DUAL
UNION ALL
SELECT n+1, 'Test LEVEL Clause' FROM ref WHERE n+1<=length(str)
)
SELECT str AS result FROM ref;

RESULT
-----------------
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause
Test LEVEL Clause

17 rows selected.

Q2. Write a query that prints 1st character in first line, 2nd character in second line and so on.

Ans:

WITH ref AS ( SELECT 'THIS IS A TEST' AS str FROM DUAL
)
SELECT substr(str, LEVEL, 1) AS result FROM ref
CONNECT BY LEVEL <= length(str);

WITH ref(n, str) AS
( SELECT 1, 'THIS IS A TEST' FROM DUAL
UNION ALL
SELECT n+1, str FROM ref WHERE n+1<=length(str)
)
SELECT substr(str, n, 1) AS result FROM ref;

RESULT
------
T
H
I
S

I
S

A

T
E
S
T

14 rows selected.

Q3. Write a query that prints 1st three characters in 1st line, next three characters in second line and so on.

Ans:

WITH ref AS ( SELECT 'LEVEL_Clause_Testing' AS str FROM DUAL
)
SELECT substr(str, (LEVEL-1)*3+1, 3) AS result FROM ref
CONNECT BY (LEVEL-1)*3+1 <= length(str)+1;

WITH ref(n, str) AS
( SELECT 1, 'LEVEL_Clause_Testing' FROM DUAL
UNION ALL
SELECT n+1, str FROM ref WHERE n+1<=length(str)/3+1
)
SELECT substr(str, (n-1)*3+1, 3) AS result FROM ref;

RESULT
------------
LEV
EL_
Cla
use
_Te
sti
ng

7 rows selected.

Q4. Write a query that prints 1st character in 1st line, first two characters in second line, first three characters in third line and so on.

Ans:

WITH ref AS ( SELECT 'Complex Query Writing using LEVEL' AS str FROM DUAL
)
SELECT substr(str, 1, LEVEL) AS result FROM ref
CONNECT BY LEVEL <= length(str);

WITH ref(n, str) AS
( SELECT 1, 'Complex Query Writing using LEVEL' FROM DUAL
UNION ALL
SELECT n+1, str FROM ref WHERE n+1<=length(str)
)
SELECT substr(str, 1, n) AS result FROM ref;

RESULT
------------------------------------------------
C
Co
Com
Comp
Compl
Comple
Complex
Complex
Complex Q
Complex Qu
Complex Que
Complex Quer
Complex Query
Complex Query
Complex Query W
Complex Query Wr
Complex Query Wri
Complex Query Writ
Complex Query Writi
Complex Query Writin
Complex Query Writing
Complex Query Writing
Complex Query Writing u
Complex Query Writing us
Complex Query Writing usi
Complex Query Writing usin
Complex Query Writing using
Complex Query Writing using
Complex Query Writing using L
Complex Query Writing using LE
Complex Query Writing using LEV
Complex Query Writing using LEVE
Complex Query Writing using LEVEL

33 rows selected.

Q5. Write a query that prints last character in 1st line, last two characters in second line, last three characters in third line and so on.

Ans:

WITH ref AS ( SELECT 'Complex Query Writing using LEVEL' AS str FROM DUAL
)
SELECT substr(str, -LEVEL) AS result FROM ref
CONNECT BY LEVEL <= length(str);

WITH ref(n, str) AS
( SELECT 1, 'Complex Query Writing using LEVEL' FROM DUAL
UNION ALL
SELECT n+1, str FROM ref WHERE n+1<=length(str)
)
SELECT substr(str, -n) AS result FROM ref;

RESULT
-----------------------------------------------
L
EL
VEL
EVEL
LEVEL
LEVEL
g LEVEL
ng LEVEL
ing LEVEL
sing LEVEL
using LEVEL
using LEVEL
g using LEVEL
ng using LEVEL
ing using LEVEL
ting using LEVEL
iting using LEVEL
riting using LEVEL
Writing using LEVEL
Writing using LEVEL
y Writing using LEVEL
ry Writing using LEVEL
ery Writing using LEVEL
uery Writing using LEVEL
Query Writing using LEVEL
Query Writing using LEVEL
x Query Writing using LEVEL
ex Query Writing using LEVEL
lex Query Writing using LEVEL
plex Query Writing using LEVEL
mplex Query Writing using LEVEL
omplex Query Writing using LEVEL
Complex Query Writing using LEVEL

33 rows selected.

Q6. Write a query that prints last character in 1st line, last two characters in reverse order second line, last three characters in reverse order in third line and so on.

Ans:

WITH ref AS ( SELECT 'Complex Query Writing using LEVEL' AS str FROM DUAL
)
SELECT reverse(substr(str, -LEVEL)) AS result FROM ref
CONNECT BY LEVEL <= length(str);

WITH ref(n, str) AS
( SELECT 1, 'Complex Query Writing using LEVEL' FROM DUAL
UNION ALL
SELECT n+1, str FROM ref WHERE n+1<=length(str)
)
SELECT reverse(substr(str, -n)) AS result FROM ref;

RESULT
-----------------------------------------------
L
LE
LEV
LEVE
LEVEL
LEVEL
LEVEL g
LEVEL gn
LEVEL gni
LEVEL gnis
LEVEL gnisu
LEVEL gnisu
LEVEL gnisu g
LEVEL gnisu gn
LEVEL gnisu gni
LEVEL gnisu gnit
LEVEL gnisu gniti
LEVEL gnisu gnitir
LEVEL gnisu gnitirW
LEVEL gnisu gnitirW
LEVEL gnisu gnitirW y
LEVEL gnisu gnitirW yr
LEVEL gnisu gnitirW yre
LEVEL gnisu gnitirW yreu
LEVEL gnisu gnitirW yreuQ
LEVEL gnisu gnitirW yreuQ
LEVEL gnisu gnitirW yreuQ x
LEVEL gnisu gnitirW yreuQ xe
LEVEL gnisu gnitirW yreuQ xel
LEVEL gnisu gnitirW yreuQ xelp
LEVEL gnisu gnitirW yreuQ xelpm
LEVEL gnisu gnitirW yreuQ xelpmo
LEVEL gnisu gnitirW yreuQ xelpmoC

33 rows selected.
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments