Oracle is an important part of the tech world, and if you plan on working with Oracle Database, you will likely be presented with interviews, questionnaires, or tests full of questions like the following. This article will not teach you everything you need to know about Oracle. However, it will provide a refresher for the experienced, and a preview for the inexperienced.
Sample Oracle Database Interview Questions:
Q: What is Oracle?
A: Oracle Database is a multi-model database management system created by the Oracle corporation.
Q: What is Oracle typically used for?
A: Oracle is most often used for data warehousing (DW), online transaction processing (OLTP), enterprise grid computing, and mixed (OLTP & DW) database workloads.
Q: How can you tell the difference between VARCHAR and VARCHAR2 data types?
A: The main difference is that VARCHAR can only store up to 2000 bytes and VARCHAR2 can store double that, 4000. VARCHAR will occupy the empty null values, and VARCHAR2 will release them.
Q: What is the difference between Oracle and SQL?
A: SQL, the language, can be used by Oracle to access objects. Oracle uses PL/SQL specifically.
Microsoft SQL Server is a database software, different and separate from Oracle. It uses the T-SQL language.
Q: What is PL/SQL?
A: PL/SQL is a custom procedural version of SQL used by Oracle. PL/SQL allows the declaration of variables, conditional operators, and functions in SQL syntax. It gives the developer more freedom when making complicated queries.
PL/SQL uses block structure, and anonymous blocks and nested blocks can be used in it.
Q: What’s the difference between UNIQUE and PRIMARY KEY Constraints?
A: PRIMARY KEY and UNIQUE columns can contain unique values, however UNIQUE columns can contain NULL data while a PRIMARY KEY one cannot.
Q: What is the RAW datatype, and how much can it hold?
A: The RAW datatype stores values in binary data format. RAW datatypes can hold 32767 bytes in a table.
Q: What does the NVL function do?
A: The NVL function is used to replace NULL data. The command typically looks like this:
NVL(value to be replaced, replace value)
Q: What is a BLOB datatype?
A: A BLOB (Binary Large OBject) datatype is a binary string that can be different lengths. It is used to store two gigabytes of memory, and the length should be specified in bytes. It’s usually used to store audio or video files.
Q: What does the COALESCE function do?
A: The COALESCE function will return a value which is set to be not null in a list. If every value in a list is null, then the function will return NULL. It typically looks like this:
Coalesce(v1, v2, v3, …)
Q: How do we make comments in Oracle code?
A: There are two ways to make comments in Oracle, a double dash (–) for a single line of comments, or /*–*/ to contain a block of comments.
"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"
Venus, Software Engineer at Rockbot
Q: What is DML?
A: DML stands for Data Manipulation Language. It’s used to change data in objects that already exist. DML has statements, which are are insert, select, update and delete.
Q: What is the difference between the TRUNCATE and DELETE commands?
A: While both remove data from the database, the DELETE command leaves the structure intact so the removal is reversible, where TRUNCATE frees the space and isn’t reversible. Also, DELETE is a DML operation, while TRUNCATE is a DDL operation.
Q: How do you use a MERGE statement?
A: A Merge statement is used to merge the data from two tables. It gets rows from data sources to update them and insert them into a view or table based on the condition of the MERGE statement.
Q: What are joins?/List the types of joins.
A: Joins are used to get data from more than one table using a common column or condition.
The six types of join are:
- INNER JOIN
- OUTER JOIN
- SEMI JOIN
- CROSS JOIN
- EQUI JOIN
- ANTI JOIN
Q: How is the TRANSLATE command different from the REPLACE command?
A: The TRANSLATE command changes characters one by one from the string with a substitution character, handling each character separately. REPLACE will substitute a character or a number of characters with a completely different string.
Q: What is the fastest way to get data from a table?
A: The fastest way to get data from a table is to use ROWID in the SQL Query.
Q: What is the importance of integrity constraints in a database?
A: Integrity constraints are a requirement for enforcing business rules. The purpose of this is to keep the integrity of the database, and prevent invalid data entry.
Q: What is VArray?
A: VArray is an oracle data type. It’s used to hold columns with attributes that have multiple values, and it can hold values in a bounded array.
Q: What is the difference between rename and alias?
A: Rename is a permanent name that can be given to a table. Alias is a temporary name given to a table. Rename replaces the name completely, as opposed to Alias which provides an alternate name, or nickname.
Q: What are database objects? Can you list some of them?
A: A database object is used to hold data, or references to data. Some types of database objects are:
- tables
- views
- indexes
- constraints
- stored procedures
- triggers
Q: What is a database schema?
A: A schema is a collection of database objects that are assigned to a particular database user. Said database user can edit or create objects within this schema.
Q: What is a cursor variable?
A: A cursor variable is a reference type that’s paired with statements which hold unique values at runtime. The cursor has a number of attributes that let an application test the state of the cursor. They include:
- %FOUND
- %NOT FOUND
- %ISOPEN
- %ROWCOUNT
Q: What is a data dictionary?
A: A data dictionary is a set of read-only tables and views that contain metadata for a database. It’s created when one creates a new database, and it gets updated as the database is updated.
Q: What is the difference between a view and a table?
A: A view is a database object made by a user that stores SQL query results. Views don’t store hard data, they just store a reference to data; they are called logical tables. They can also be used in new SQL queries, and the data in them cannot be updated or deleted. Tables, on the other hand, can hold data but not the results of an SQL query and can be updated or deleted.
Q: What’s the difference between SUBSTR and INSTR?
A: A SUBSTR returns specific part of a string and INSTR returns a specific character position in a string via a matching pattern. SUBSTR return the string value found, as opposed to INSTR returns an INT value of the location.
Q: What is a trigger?
A: A trigger is a program that runs automatically when a specific event happens. That event can be any operation. There are two types of triggers in Oracle: row level and statement Level.
Q: What is the difference between a local variable and a global one?
A: In Oracle global variables are declared at the beginning, and it’s accessible from anywhere. Local variables only have a local scope; they can only be accessed by the methods that created them.
Q: How are $ORACLE_BASE and $ORACLE_HOME different?
A: $ORACLE_HOME is a special directory location that is beneath the base folder that Oracle is installed on, whereas $ORACLE_BASE is the root directory.
Q: What is a hash cluster?
A: Hash Clusters is a technique for faster data retrieval. It’s done by adding a hash value to tables to retrieve values from rows faster than normal.
Q: How many triggers can one table have?
A: A table cannot have more than 12 triggers.
Q: What is an index?
A: An index is an object which is made to earmark data in a table and they can be clustered or non-clustered. They are typically created on columns with a high volume of accesses, this lets us find that data more efficiently.
Q: How are set operators used?
A: SET operators are used to combine or modify multiple queries, and they are Union, Union All, Minus, and Intersect:
- UNION – returns all rows from all tables and excludes any duplicate rows.
- UNION ALL – returns all rows from all tables and includes any duplicate rows.
- MINUS – returns all of the unique rows in one table that are not duplicated in a second table.
- INTERSECT – only returns rows that all the tables have.
Q: What is a deadlock?
A: A deadlock is when two users are trying to access the same information that is also locked by both parties for the opposite party. The result is that no one can access the data.
Q: What is a key preserved table?
A: A key preserved table is when every key contained in that table could also be the key that’s a result of a join.
Conclusion
While this is not a complete guide to all the Oracle interview questions you will encounter, keeping the answers to these questions on hand can help out quite a bit in an interview. It’s important to have some fundamental concepts on hand when going into an interview, as the interviewer will want to make sure you have a full understanding of Oracle.
For more interview advice, check out Career Karma’s Ultimate Guide to further prepare you for success in your next technical interview. After your interview, visit this article to read up on signs that your interview went well.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.
