This article contains good Oracle sql interview questions
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
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
6) How does a Query executes in Oracle? Can you list steps involved?
Answer: Following steps are involved in parsing SQL query :
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.
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
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.
OVARCHAR 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.
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
22) What is decode statment?
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 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.