Oracle SQL Logical Questions

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.

Go to top

Test your skills now..

Q1: How to know the number of rows in a table without using COUNT(*)?

SELECT max(ROWNUM)
FROM Emp;

Q2: How to delete duplicate records from a table?

–Method 1

DELETE FROM <table_name>
WHERE ROWID NOT IN (
SELECT MAX(ROWID)
FROM <table_name>
GROUP BY <Primary_key_columns>
);

–Method 2

We can also do it by using Analytic Function. Consider the table Dup01. We shall use ROW_NUMBER() to delete the duplicate records. Watch…

DELETE FROM Dup01
WHERE ROWID IN (
SELECT ROWID
FROM
(
SELECT ROWID, Dup01.*,
ROW_NUMBER()
OVER(PARTITION BY DupID
  ORDER BY DupID) Dup
FROM Dup01
)
WHERE Dup > 1
);

–Method 3

DELETE FROM Dup01
WHERE ROWID IN (
			  SELECT ROWID
			  FROM Dup01 D1
			  WHERE ROWID > (
					 SELECT MIN(ROWID)
					 FROM Dup01 D2
					 WHERE D2.DupID = D1.DupID
					 AND D2.DupName = D1.DupName
							)
			);

–Method 4

DELETE FROM Sample
WHERE ROWID IN (
SELECT ROWID
FROM Sample
MINUS
SELECT MAX(ROWID)
FROM Sample
GROUP BY SampID
);

Q3: Write a SQL query to print the numbers from 1-10?

SELECT LEVEL Num
FROM DUAL
CONNECT BY LEVEL < 11;

Recursive subquery factoring was introduced in Oracle 11g Release 2, giving an alternative method of performing hierarchical queries and rewriting queries that uses LEVEL.

The following query uses a recursive WITH clause and rewrites query that uses LEVEL.

WITH data(p) AS (
   SELECT 1 p FROM dual
   UNION ALL
   SELECT p + 1 FROM data WHERE p <= 10
)
SELECT p Num
FROM data;

A 2nd alternate to generate the numbers from 1 -10 without using LEVEL when interviewer tries to trick you.

SELECT ROWNUM Num
FROM USER_OBJECTS
WHERE ROWNUM <= 10;

The above approach is good, but works only when USER_OBJECTS has 10 or more rows. If less rows in USER_OBJECTS, then you can use cartesian product to generate more rows.

SELECT ROWNUM Num
FROM USER_OBJECTS, USER_OBJECTS
WHERE ROWNUM <= 10;

Q4: Write a SQL query to print the numbers from 10 – 20?

SELECT Num FROM 
(
SELECT LEVEL Num
FROM DUAL
CONNECT BY LEVEL <= 20
)
WHERE Num BETWEEN 10 AND 20;

SELECT LEVEL Num
FROM DUAL
GROUP BY LEVEL
HAVING LEVEL BETWEEN 10 AND 20
CONNECT BY LEVEL < 21
ORDER BY LEVEL;

WITH data(p) AS (
   SELECT 1 p FROM dual
   UNION ALL
   SELECT p + 1 FROM data WHERE p <= 20
)
SELECT p Num
FROM data
WHERE p BETWEEN 10 AND 20;

SELECT Num
FROM (
SELECT ROWNUM Num
FROM USER_OBJECTS, USER_OBJECTS
)
WHERE Num BETWEEN 10 AND 20;

SELECT ROWNUM Num
FROM USER_OBJECTS, USER_OBJECTS
GROUP BY ROWNUM
HAVING ROWNUM BETWEEN 10 AND 20
ORDER BY  Num;

Test your skills now..

Login