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.
Most Popular SQL Interview Questions and Answers
Q1. Display the name of current user?
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.
Q2. Display the names of all tables from current user?
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?
SELECT username FROM DBA_USERS;
Q4: How to get the Table definitions in Oracle?
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?
SELECT banner FROM V$VERSION;
Q6: How to go to the DOS command prompt from SQL*PLUS client editor?
Q7: How to view all the environment variables for your session in SQL*PLUS client?
Q8: How to set the terminating character in Oracle?
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?
ALTER SESSION SET NLS_DATE_FORMAT= 'MONTH-DD-YYYY';
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';