Oracle Technical Interview Questions and Answers - Orapoint

Both fresher and experienced candidates will benefit from the Oracle Technical Interview Questions and Answers given below. Anyone should be able to identify the questions and explanations given by completing this questionnaire. All of these questions will help you prepare for technical interviews and online assessments that will take place during your campus placement. This exam covers topics such as Schema, Data File, Overloading, Oracle Instance, Tablespace, DML, Oracle programming, Operators, Query Types, Oracle SQL functions, Oracle Data Types, and more.

Q1. What is the definition of a table?

Ans: The table contains data from a list. The simplest form of rows and columns is a table. Data is organised into rows and columns.

Q2. What is the difference between the data types varchar and varchar2?

Ans: Varchar can hold up to 2000 bytes, while varchar2 can hold up to 4000. Varchar will take up space for NULL values, while Varchar2 will not. In terms of space, they are different.

Q3. What is the difference between a UNIQUE KEY CONSTRAINT and a PRIMARY KEY CONSTRAINT?

Ans: A column defined as PRIMARY KEY, like UNIQUE, can contain unique values, but UNIQUE can also contain NULLs, while a column defined as PRIMARY KEY cannot.

Q4. What does the NVL function do?

Ans: The NVL function replaces NULL values with another or specified value.

For example:  NVL(Value, replace_value)

Q5. What exactly is an index?

Ans: An index is used to tune query efficiency. which can be generated to improve data retrieval efficiency A table’s index may be built on one or more columns.

Q6. What are nested tables, and how do you use them?

Ans: In Oracle, a nested table is a data form that is used to serve columns of multiple values. It can also accommodate an entire sub table.

Q7. What are Schema Objects?

Ans: Tables, views, sequences, synonyms, indexes, databases, triggers, procedures, functions, and packages are all schema objects.

Q8. What exactly is the COALESCE function?

Ans: The COALESCE function is used to return the value in the list that is set to not be null. The coalesce function will return NULL if all of the values in the list are null.

For Example: Coalesce(value1, value2,value3,…)

Q9. In Oracle, how do we represent comments?

Ans: Oracle comments can be written in the following form:

• Single statement with two dashes (–) at the start of the line.
• /*—— */ is used to represent it as comments for a block of statements.

Q10. What exactly is a subquery?

Ans: A subquery is a query that is nested within another subquery or a SELECT, INSERT, UPDATE, or DELETE statement. Anywhere an expression is permitted, a subquery may be used.

Q11. What is the difference between a SUBSTR and an INSTR?

Ans: The INSTR function searches a string for sub-strings and returns an integer indicating the location of the first character of this occurrence in the string. The SUBSTR function returns a substring length characters long portion of a string, starting at character location. SUBSTR determines lengths based on the characters in the input character set.

Q12. What exactly is ROWID?

Ans: Each row of a table has a pseudo column called ROWID. It is 18 characters long. Number of rows. The elements of ROWID are file number.

Q13. What exactly is DML?

Ans: Data Manipulation Language (DML) is a programming language that allows you to access and manipulate data in existing objects. Insert, pick, update, and delete are DML statements that do not commit the current transaction.

Q14. What exactly do you mean when you say GROUP BY Clause?

Ans: In a select statement, a GROUP BY clause may be used to gather data from different records and group the results by one or more columns.

Q15. What are the various forms of subqueries and what is a sub query?

Ans: Sub Query, also known as Nested Query or Inner Query, is a form of query that retrieves data from multiple tables. In the where clause of the main query, a sub query is inserted.

Subqueries are divided into two categories:

1. Correlated sub query: A Correlated sub query cannot be run independently, but it may refer to a column in a table specified in the outer query’s from list.
2. Non-Correlated subquery: This can be analyzed as though it were a separate query. The sub query’s results are passed on to the main query or parent query.

Q16. What is a View?

Ans: A logical table based on one or more tables or views is called a view. Base Tables are the tables that the view is built on, and they don’t contain any info.

Q17. What is the purpose of Oracle’s Aggregate functions?

Ans: The aggregate function is a function that joins the values of multiple rows or records to produce a single value output. The following is a list of aggregate functions.

• Count()
• Sum()
• Average()

Q18. What is the data type of DUAL table?

Ans: The DUAL table in the Oracle database is a one-column table. A single VARCHAR2(1) column named DUMMY has a value of ‘X’ in the table.

Q19. Difference between Alias and Rename?

Ans: Alias is a temporary name for a table or column, while Rename is a permanent name for a table or column. A rename is simply a name change, whereas an Alias is a different name for the table or column.

Q20. What exactly is a database?

Ans: A database is a logically organized set of data that has some significance. The information is stored in the data’s stored rows and columns database.

Q21. What is the difference between the commands TRUNCATE and DELETE?

Ans: Both commands are used to delete information from a database.

1. DELETE is a DML operation, while TRUNCATE is a DDL operation.
2. TRUNCATE deletes all rows while preserving the table layout. It can’t be undone because it issues COMMIT before and after the command execution, while the DELETE command can.
3. The DELETE command does not free up object storage space, while the TRUNCATE command does.
4. When compared to DELETE, TRUNCATE is quicker.

Q22. How do we determine the values in an Oracle table are duplicates?

Ans: To get duplicate records, we can use the example query below.

SELECT EMP_NAME, COUNT (EMP_NAME)
FROM EMP
GROUP BY EMP_NAME
HAVING COUNT (EMP_NAME) > 1;

Q23. What’s the difference between a unique key and a primary key?

Ans: A Primary Key is used to uniquely identify each table row, while a Unique Key is used to prevent duplicate values in a table column. few differences are listed below:

1. On the table, there can only be one primary key, but there can be several unique keys.
2. The primary key cannot have any null values, while the unique key may have several null values.
3. A clustered index is the primary key, while a non-clustered index is the unique key.

Q24. What are the purposes of the set operators UNION, UNION ALL, MINUS, and INTERSECT?

Ans: If the columns and relative data types in the source tables are the same, the set operator allows the user to fetch data from two or more tables at once.

1. Except for duplicate rows, the UNION operator returns all rows from both tables.
2. UNION ALL returns all of the rows from both tables, including duplicates.
3. The MINUS function returns rows from the first table that do not exist in the second.
4. Only the common rows in both tables are returned by INTERSECT.