This article contains Top 49 Oracle Interview questions : Basics , Oracle SQL asked in various interview. I hope it should help in interviews
Oracle Interview questions : Basics , Oracle SQL
Question 1. What is a table Cluster ?
Answer: Table Cluster is a group of related tables that share common columns are store related data in the same block.
Question 2. What is exists usage in SQL?
Answer “exists” statement helps us to do the job of If statement. Main query will get executed if the sub query returns at least one row. So we can consider the sub query as “If condition” and the main query as “code block” inside the If condition. We can use any SQL commands (Joins, Group By , having etc) in sub query. This command will be useful in queries which need to detect an event and do some activity
SELECT * FROM emp WHERE EXISTS (SELECT * FROM dept WHERE emp.dept_id = dept.dept_id);
Question 3. How will you convert string to a date in oracle database?
Answer : We can use to_date function to convert string into date
Question 4. What is Save Points in Oracle database?
Answer : SAVE POINTS are used to divide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed. Whenever we encounter error we can rollback from the point where we set our SAVEPOINT. This is useful for multistage transaction and conditional transaction where commit and rollback depend on certain condition.
Question 5. How do you find current date and time in oracle?
Answer: we can find using sysdate function
SQL> SELECT SYSDATE FROM dual; SYSDATE --------- 2-Nov-16 SQL> SELECT SYSDATE Today, SYSDATE - 1 Yesterday, SYSDATE + 1 Tomorrow FROM dual; TODAY YESTERDAY TOMORROW --------- --------- --------- 2-Nov-16 1-Nov-16 3-Nov-16
Question 6. How does a Query executes in Oracle? Can you list steps involved?
Answer: Following steps are involved in parsing SQL query :
Question 7. How do you find MONTH and YEAR from a date in Oracle?
Answer : By using EXTRACT function, you can use it to get MONTH and YEAR from a DATE object.
SELECT EXTRACT(YEAR FROM DATE '2016-01-12') Year_date FROM DUAL; Year_date --------------------------------- 2016
Question 8. What is an Oracle view?
Answer: A oracle view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns like tables do. It has following advantages
1. View the data without storing the data into the object.
2. Restrict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restrict the access of a table so that nobody can insert the rows into the table.
Question 9. What is Oracle table?
Answer:Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees , and a set of columns
In-dept article on oracle table
Question 10. Difference between VARCHAR and VARCHAR2 oracle datatypes?
Answer: VARCHAR can store up-to 2000 bytes and varchar2 can store up-to 4000 bytes. VARCHAR will occupy space for NULL values and VARCHAR2 will not occupy any space. Both are differed with respect to space.
Question 11. What is the difference between CHAR and VARCHAR datatype
CHAR Data Type is a Fixed Length Data Type. For example, if you declare a variable/column of CHAR (5) data type, then it will always take 5 bytes irrespective of whether you are storing 1 character or 5 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(5), so we can store max 5 characters in this column.
VARCHAR is a variable length Data Type. For example, if you declare a variable/column of VARCHAR (10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column
Question 12 What is the use of NVL function?
Answer:The NVL function is used to replace NULL values with another or given value.
Example is – NVL(Value, replace value)
Question 13 What is DML?
Answer:Data Manipulation Language (DML) is used to access and manipulate data in the existing objects. DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.
Question 14 What is DDL ?
Answer: DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. it does implicit commit
Question 15. What is the difference between TRANSLATE and REPLACE?
Answer:Translate is used for character by character substitution and Replace is used substitute a single character with a word.
Question 16. How do we display rows from the table without duplicates?
Answer:Duplicate rows can be removed by using the keyword DISTINCT in the select statement.
How to delete duplicate rows from a table
Question 17. What is NULL value in oracle?
Answer:NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.
Question 18. What is the usage of Merge Statement?
Answer:Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.
Question 19. What are the various constraints used in Oracle?
Answer:Following are constraints used:
NULL – It is to indicate that particular column can contain NULL values
NOT NULL – It is to indicate that particular column cannot contain NULL values
CHECK – Validate that values in the given column to meet the specific criteria
DEFAULT – It is to indicate the value is assigned to default value
Question 20. What are the different Oracle Database objects?
Answer:There are different data objects in Oracle –
Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespace – Logical storage unit in Oracle
Question 21. What is materialized views?
Answer A materialized view is a database object that contains the results of a query. It is different from simple view. These materialized view have data stored and when you query the materialized view,it returns data from the data stored. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.
Check below link for Details
Question 22. What is decode statement?
Question 23. What is difference between TRUNCATE & DELETE?
Answer: Truncate resets the high water mark of the table while delete does not reclaim any space
Question 24 What is a join? Explain the different types of joins?
Answer: An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.
INNER JOIN operation
LEFT OUTER JOIN operation
RIGHT OUTER JOIN operation
CROSS JOIN operation
NATURAL JOIN operation
Question 25. You have just compiled a PL/SQL package but got errors, how would you view the errors?
Answer dba_errors or show errs
Question 26. What is a transaction?
When a session update /delete/insert ,then a transaction is started. It is completed when the commit or rollback happened. A transaction is identified by transaction identifier(XID). The transaction identifies consists of three part
- Rollback or undo segment number
- Transaction table Slot number
- Sequence or wrap no
Question 27. How can you enable a trace for a session?
Normal trace execute dbms_system.set_sql_trace_in_session (‘sid’,’serial’,true); — To put tracing on execute dbms_system.set_sql_trace_in_session (‘sid’,’serial’,true); — To put tracing off Full level with wait event And bind trace execute dbms_system.set_ev(‘sid’,’serial’,10046,12,’’); To put trace off execute dbms_system.set_ev(‘sid’,’serial’,10046,0,’’);
Question 28. What is difference between Cartesian Join and Cross Join?
Answer:There are no differences between the join. Cartesian and Cross joins are same. Cross join gives Cartesian product of two tables – Rows from first table is multiplied with another table which is called Cartesian product.
Cross join without where clause gives Cartesian product.
Question 29. What is the data type of DUAL table?
Answer:The DUAL table is a one-column table present in oracle database. The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.
Question 30. How to deal with duplicate records in the table?
create table my_table1 as select distinct * from my_table; drop my_table; rename my_table1 to my_table; Delete from my_table where rowid not in ( select max(rowid) from my_table group by my_col_name );
Question 31. What is the fastest query method to fetch data from the table?
Answer:Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.
Question 32. What are privileges and Grants?
Answer:Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.
Question 33. What is an integrity constraint?
Answer:An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.
Question 34. what is referential integrity ?
Answer:A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
Question 35. What is VARRAY?
Answer: VARRAY is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.
Question 36 . How do we get field details of a table?
Answer:Describe <Table_Name> is used to get the field details of a specified table.
Question 37. What is the use of Aggregate functions in Oracle?
Answer:Aggregate function is a function where values of multiple rows or records are joined together to get a single value output. Common aggregate functions are –
Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
Question 38. What do you mean by GROUP BY Clause?
A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.
Question 39 . What is the difference between rename and alias?
Answer: Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.
Question 40. What is COALESCE function?
Answer:COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.
Question 41. What is the difference between PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?
PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.
Question 42. How can you fetch first 5 characters of the column in the table?
This can be done using the Substr function
select substr(ename,1, 5) from emp;
Question 43.What is replace function ?
A replace function is used to replace a string with another string
Question 44.How do we analyze statistics on the table?
We generate stats using dbms_stats.gather_table_stats procedure
Question 45.What is Optimizer?
Optimizer is the engine in Oracle database which determines most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query
The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides the cost-based (CBO) and rule-based (RBO) optimization. In general, use the cost-based approach. Oracle Corporation is continually improving the CBO and new features require CBO.
Question 46.What are hints in Oracle?
Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria.
SELECT /*+ FULL(e) */ employee_id, last_name FROM employees e WHERE last_name LIKE :b1;
Question 47.What is explain plan?
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT , UPDATE , INSERT , and DELETE statements. A statement’s execution plan is the sequence of operations Oracle performs to run the statement. The row source tree is the core of the execution plan.
Question 48.How to change date format?
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Question 49.How to compile the package body and package?
Alter package <package name > compile; Alter package <package name > compile body;
Few more questions on Oracle interview questions to increase your skills
1.Which two statements about views are true? (Choose two.)
A. A view can be created as read only.
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.
Answer: A, BA view can be created as read only object. However, it is possible to change data in the
underlying table(s) with some restrictions.A view also can be created as a join on two or more
tables. This type of view is called complex view. Complex views provide complicated data
models where many base tables are drawn together into one virtual table.
2.Which data dictionary table should you query to view the object privileges granted to
the user on specific columns?
The USER_COL_PRIVS data dictionary view will show the object privileges granted to the
user on specific columns.
3.For which two constraints does the Oracle Server implicitly create a unique index?
A. NOT NULL
B. PRIMARY KEY
C. FOREIGN KEY
Answer: B, E
Indexes are created automatically by Oracle to support integrity constraints that enforce
uniqueness. The two types of integrity constraints that enforce uniqueness are PRIMARY
KEY and UNIQUE constraints. When the primary key or UNIQUE constraint is declared, a
unique index to support the column’s uniqueness is also created, and all values in all columns
that were defined as part of the primary key or UNIQUE constraint are placed into the index
4. Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Answer: C, D
It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.
5. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause
placed in the SELECT statement?
A. Immediately after the SELECT clause
B. Before the WHERE clause
C. Before the FROM clause
D. After the ORDER BY clause
E. After the WHERE clause
The GROUP BY clause can be place only after the WHERE clause, or after FROM clause if
there is no the WHERE clause in the statement.
6.Which two tasks can you perform using only the TO_CHAR function?
A. convert 10 to ‘TEN’
B. convert ’10’ to 10
C. convert ’10’ to ’10’
D. convert ‘TEN’ to 10
E. convert a date to a character expression
F. convert a character expression to a date
7.In which case would you use a FULL OUTER JOIN?
A. Both tables have NULL values.
B. You want all unmatched data from one table.
C. You want all matched data from both tables.
D. You want all unmatched data from both tables.
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
8.You are granted the CREATE VIEW privilege. What does this allow you to do?
A. Create a table view.
B. Create a view in any schema.
C. Create a view in your schema.
D. Create a sequence view in any schema.
E. Create a view that is accessible by everyone.
F. Create a view only if it is based on tables that you created.
9.Which constraint can be defined only at the column level?
B. NOT NULL
D. PRIMARY KEY
E. FOREIGN KEY
10.Which clause should you use to exclude group results?
D. GROUP BY
E. ORDER BY
You can try the SQL statement by installing developer. Download the below SQL developer guide for it
I hope you like this stuff on Oracle interview questions with answer. This is useful for freshers and experienced person both.Please do provide the feedback
Here is the nice Udemy Course for Oracle SQL