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.
Most Popular SQL Interview Questions and Answers
Q1. Display the name of current user?
Ans:
SELECT USER FROM DUAL;
You can also use SHOW command which displays the value of a SQL*Plus system variable or the current SQL*Plus environment. SHOW is only used in a SQL*PLUS client and can’t be called from any application to return the value.
SHOW USER
Q2. Display the names of all tables from current user?
Ans:
SELECT tname FROM TAB WHERE tabtype = 'TABLE';
USER_OBJECTS is a single Oracle catalog view that describes all objects owned by the current user.
SELECT
object_name,
object_type
FROM USER_OBJECTS
WHERE object_type = 'TABLE'; Q3. Display the list of all users in your database?
Ans:
SELECT username FROM DBA_USERS;
Q4: How to get the Table definitions in Oracle?
Ans:
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP')
FROM DUAL;
DBMS_METADATA.GET_DDL returns LOB. Use SET LONG 4000 if using SQL* PLUS client to view the full string.
Q5: How to know the version of Oracle?
Ans:
SELECT banner FROM V$VERSION;
Q6: How to go to the DOS command prompt from SQL*PLUS client editor?
Ans:
HOST
Q7: How to view all the environment variables for your session in SQL*PLUS client?
Ans:
HELP SET
Q8: How to set the terminating character in Oracle?
Ans:
SET SQLT $ --Terminating character is set to $ SELECT * FROM Dept; --Produces error as ; is no more the SQL terminating character SELECT * FROM Dept$ --Correct SET SQLT ; --Terminating character is reverted to ;
Q9: How to change the default Oracle date format?
Ans:
ALTER SESSION SET NLS_DATE_FORMAT= 'MONTH-DD-YYYY';
SELECT HireDate
FROM Emp
WHERE HireDate BETWEEN '01-JAN-81' AND '31-dec-82';
WHERE HireDate BETWEEN '01-JAN-81' AND '31-dec-82'
*
ERROR at line 3:
ORA-01843: not a valid month
Since default date format is changed, you must provide the dates in Oracle accepted valid format.
SELECT HireDate FROM Emp WHERE HireDate BETWEEN 'January-01-1981' AND 'december-31-1982';