Oracle Interview Questions and Answers
ORACLE is primarily a relational database management system. Oracle is also known as Oracle DB or Oracle RDMS. By using Oracle, the user can directly access the objects through the Structured Query Language(SQL).
Oracle is designed to support multiple data models. The best part about Oracle is that it has its network component which allows the communications across the networks. Oracle runs on Windows, UNIX, Linux, and Mac OS. Oracle is written in Assembly language, C & C++.in today's world Oracle database is considered as one of the most reliable database engines in the world. Oracle interview questions we are mentioning below will help you to gain enough of the knowledge about Oracle.
Most Frequently Asked Oracle Interview Questions
An UPDATE statement is used to update the records that already exist in the table.
SYNTAX:-
UPDATE table
SET column1 = expression1,
column2 = expression2,
...
column_n = expression_n
[WHERE conditions];
| S.no | SAP | ORACLE |
|---|---|---|
| 1. | The language used is ABAP | The language used is PL/SQL |
| 2. | It provides the tools for the flow of information in the business | ORACLE is the multiple model database management system |
| 3. | The functional areas are Sales, Accounting, etc | The functional areas are transaction processing, data warehousing, etc |
ORACLE 12c created the change in the architecture of the ORACLE database. In this version came with the concept of the Container Database (CDB) and the Pluggable Database (PDB)
- Container Database (CDB) :- Container database contains the Oracle Metadata.This database includes control files, data files, temp files, etc. and this includes the data dictionary for those objects which are owned by the root container.
- Pluggable Database (PDB):- Pluggable Database contains the data files and temp files to handle PDB's objects only, and this includes the data dictionary for those objects which are in PDB only.
This is the favorite interviewer question in Oracle interview questions for experienced.
- Character Datatypes-CHAR, VARCHAR2, VARCHAR, NCHAR, NVARCHAR2, etc
- Numeric Datatypes:-NUMBER, BINARY_FLOAT, BINARY_DOUBLE
- DATE Datatype:-DATE, DD-MON-YY, TIMESTAMP, etc
- LOB Datatypes:-BLOB, CLOB, NCLOB, etc
- RAW and LONG RAW Datatypes
- ROWID and UROWID Datatypes
- ANSI, DB2, and SQL/DS Datatypes
- XML Datatypes:-XMLType
- URI Datatypes:-UriType
| S.no | SUBSTR (sub-string) | INSTR (in-string) |
|---|---|---|
| 1. | It extracts the specific part of the string from the whole string | It searches the entire string for finding the sub-string |
| 2. | The output data type is the number for the numeric input and the character for the date and the character input | The output data type is always the number |
| 3. | The second argument is always the number | The second argument should be a number or the character depending upon the data type of the input. |
| S.no | REPLACE() | TRANSLATE() |
|---|---|---|
| 1. | It replaces the complete string at once | It replaces the character one by one |
| 2. | If no match is found, then it returns the string | If no match is found, then it returns the NULL value. |
| 3. | REPLACE( string1, string_to_replace [, replacement_string] ) | TRANSLATE( string1, string_to_replace, replacement_string ) |
| S.no | Primary Key | Unique Key | Foreign Key |
|---|---|---|---|
| 1. | Cannot have a NULL value | It can have a NULL value | It can accept the NULL value |
| 2. | It is a clustered index | It is a non-clustered index | The Index is manually created whether clustered or non-clustered |
| 3. | The Primary key can only be 1 in a table | The Unique key can be more than 1 in a table | The Foreign key can be more than 1 in a table |
The trigger is the procedure which is implicitly executed by the Oracle server when the INSERT, UPDATE, DELETE statement is executed against the associated table in the Oracle database.
Based on the event these are the types of trigger:-
- DDL Trigger:-INSERT/UPDATE/DELETE
- DML Trigger:-CREATE/ALTER
- DATABASE Trigger:-LOGON/LOGOFF/STARTUP/SHUTDOWN
Development History
- Flashback Technology recovers the data which is lost or deleted efficiently
- Reliable even when the tasks are demanding
- Manages a large amount of data
- Has the capability to maintain multiple databases.
All your questions related to Oracle will be answered in the Oracle interview questions for freshers mentioned below.
Development History
Oracle was developed by Lawrence Ellison and his co-workers Bob Miner and Ed Oates in the year 1977. The first version ORACLEv2 was released in the year 1979 which was the first available SQL based RDBMS.
Latest Version
Oracle Database 18c is the newest version which got released on 16th February 2018.