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.
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?
DELETE FROM <table_name> WHERE ROWID NOT IN ( SELECT MAX(ROWID) FROM <table_name> GROUP BY <Primary_key_columns> );
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 );
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 ) );
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
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;