Last updated on March 22nd, 2017 at 04:52 am
This article contains Top 40 Oracle sql interview questions asked in various interview. I hope it should help in interviews
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.
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
WHERE EXISTS (SELECT *
WHERE emp.dept_id = dept.dept_id);
3) How will you convert string to a date in oracle database?
Answer : We can use to_date function to convert string into date
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.
5) How do you find current date and time in oracle?
Answer: we can find using sysdate function
|SQL> SELECT SYSDATE FROM dual;
TODAY YESTERDAY TOMORROW
6) How does a Query executes in Oracle? Can you list steps involved?
Answer: Following steps are involved in parsing SQL query :
|SELECT EXTRACT(YEAR FROM DATE ‘2016-01-12’) Year_date FROM DUAL;
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. Restict 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. Restict the access of a table so that nobody can insert the rows into the table.
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
10) Difference between varchar and varchar2 data types?
Answer: Varchar can store upto 2000 bytes and varchar2 can store upto 4000 bytes. Varchar will occupy space for NULL values and Varchar2 will not occupy any space. Both are differed with respect to space.
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
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)
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.
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
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.
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
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.
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.
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
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
Tablespaces – Logical storage unit in Oracle
21) What is materialized views?
Answer check below link
what is Oracle materialized view and Materialized View Log
22) What is decode stateent?
Answer check the below link
23) What is difference between TRUNCATE & DELETE?
Answer: Truncate resets the highwater mark of the table while delete does not reclaim any space
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
25) You have just compiled a PL/SQL package but got errors, how would you view the errors?
Answer dba_errors or show errs
26) What is a transaction?
Answer: Check the below link
27) How can you enable a trace for a session?
Answer :check the below link
How to turn on the trace in database
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.
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’.
30) How to deal with duplicate records in the table?
Answer: check below link for detailed explanation
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
Few more questions on Oracle sql interview questions to increase your skills
- 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 sql interview questions. Please do provide the feedback