Here is a comprehensive list of Oracle Interview Questions and Answers that covers the Oracle basics, SQL and PL/SQL. Developers/Anyone who work on Oracle PL/SQL programming should have answers for all these questions. These questions are sourced from/contributed by experienced developers who work on real-time projects in large organizations for many years.
What Oracle is and what are its different editions?
Answer: Oracle is one of the most popular database management systems used in the Industry today. Oracle supports relational database management concepts hence it is also called as Oracle RDBMS. It is mainly used in large scale enterprise applications which involves high volume of online transaction processing(OLTP), data warehousing, and enterprise grid computing.
It was developed and released by Oracle Corporation in 1979, since its release there were many subsequent versions and editions and the last stable version widely being used is Oracle 12c. There are couple of recent versions 18c and 19c released in 2018 & 2019 respectively, “c” in the suffixes stands for cloud.
What is the difference between VARCHAR & VARCHAR2 datatypes?
Answer: Both VARCHAR & VARCHAR2 Oracle data types are used to store character strings of variable length, the differences are below,
|VARCHAR can store characters up to 2000 bytes.||VARCHAR2 can store characters up to 4000 bytes.|
|VARCHAR column will hold the entire declared memory space even if the stored data is smaller in size.||VARCHAR2 will use only the memory space required for the data to be stored and will release the unused memory space.|
What is the difference between a Primary Key and a Unique Key?
Answer: Both Primary Key and Unique Key are used to enforce constraints over the data stored in database tables, below are few key differences,
|Primary Key||Unique Key|
|Primary Key is used to identify each row in a table uniquely.||Unique Key prevents duplicate values in a table column.|
|There can be only one Primary Key in a table.||There can be multiple Unique Keys in a table.|
|Primary Key cannot hold null value.||Unique Key can hold one null value per column.|
|Primary Key is a clustered index.||Unique Key is a non-clustered index.|