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.
WITH ref AS ( SELECT 'Test LEVEL Clause' AS str FROM DUAL ) SELECT str AS result FROM ref CONNECT BY LEVEL <= length(str);
Using Recursive WITH
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.
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);
Using Recursive WITH
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.
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;
Using Recursive WITH
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.
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);
Using Recursive WITH
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.
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);
Using Recursive WITH
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.
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);
Using Recursive WITH
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