Oracle
Random

Click random to get a fresh chapter.

All The Useful Oracle Interview Questions & Answers - 2

Q 1. What are the new features in Oracle 12c?
Here is the list of Oracle12c new features: 

Pluggable Database: This feature has changed the ORACLEdatabase architecture. In this, the Metadata is stored in the container database, and the user data is stored in the Pluggable Database.
Data Redaction: This is the essential feature of 12C.Data Redaction basically means masking of the data.
RMAN command: RMAN (Recovery Manager)command is used to restore a particular table which used to be a hard job before.
Multiple indexes: Now, the column can be in more than one index. For example -Column can be included in the B-tree index as well as a Bit Map index but only one used at a time.

Q 2. What is Oracle and why is it used?
Oracle is fundamentally a relational database management system. By using ORACLE, the user can directly access the objects through the Structured Query Language(SQL). Oracle is designed to support multiple data models. Oracle runs on Windows, UNIX, Linux, and Mac OS.Oracle is written in Assembly language, C & C++.

Because of the following reasons Oracle is a very popular database software.

Flashback Technology: Recovery of the data is done efficiently
ACID tool: maintains the integrity of the information
Oracle can be used for small business applications as well as large business applications.

Q 3. What is Oracle latest version?
The latest version of Oracle Database is 18c. This version got released on 16th February 2018.

Q 4. Who developed Oracle & when?
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.

Q 5. Explain the features of Oracle.

Here are some of the features of the ORACLE: 

Backup and Recovery of Data: ORACLE provides Flashback technology which leads to efficient recovery of data.
Portable: The ORACLE database has been ported to many platforms, and as a result, the applications on Oracle are safe when the OS is changed.
Speed: Speed of the ORACLE database is good
Multiple Database Management: ORACLE manages various databases at the same time. The user can quickly move at a place where data is actually stored from the node to node in a network
Efficient Data Integrity: ORACLE uses the integrity constraints due to which invalid data entry is restricted to be entered into the database tables.
Provides Cursor Support: With the help of cursor, programming becomes easy due to the row-to-row processing.

Q 6. Explain the difference between SQL and Oracle.

SQL    Vs  ORACLE

1.    Use T-SQL (Transact-SQL) language -    Use PL/SQL(Procedural Language/ SQL) language
2.    The database is shared among the users    - The database is not shared among the users
3.    Complex but very powerful    - Simple and easy to implement

Q 7. Explain the truncate in Oracle.
The TRUNCATE statement removes all the records from the table in the ORACLE database. If once the TRUNCATE statement truncate the table then the data cannot be recovered back.

Example:
Syntax:  TRUNCATE TABLE table_name

Q 8. What is PL SQL and why it is used for?
PL/SQL is the language which is used to write the codes in the database.

For Example - PL/SQL is used to write the ORACLE database.PL/SQL is basically to combine the SQL and the procedural feature of the programming language. This language was developed to enhance the characteristics of the SQL language.

Some of the features of the PL/SQL are: 

PL/SQL functions can be called directly from the SQL.
PL/SQL can run without the involvement of human.
Programs can run on the different Operating Systems.
PL/SQL supports Loops, Conditions, and OOPS concepts.

Q 9. Explain the difference between delete and truncate.

TRUNCATE Vs    DELETE

Q 1.    Rollback of the data is not possible  -     Rollback of the data is possible
2.    All the records are deleted -    Can delete a specific record using the WHERE condition.
3.    Uses less transaction space -    Use more transaction space

Q 10. Explain Oracle insert into command.
An INSERT statement is used in the ORACLE database to insert the single record or the multiple records in a table.

This particular ORACLE interview question explains the importance of keys in the database.

Example:
                                                   
SYNTAX: 
INSERT INTO table (column1, column2, ... column_n ) VALUES
(expression1, expression2, ... expression_n );

Q 11. Write a syntax for update query in Oracle.
An UPDATE statement is used to update the records that already exist in the table.

Example:
                                                   
SYNTAX: 

UPDATE table
SET column1 = expression1,
column2 = expression2,
 ...
column_n = expression_n
[WHERE conditions];

Q 12. Explain the difference between SAP and Oracle.

SAP    Vs 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

Q 13. What do you mean by CDB and PDB in Oracle 12c?
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.

Q 14. Please explain oracle data types with examples.

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

Q 15. How to convert raw data type into text in Oracle? Explain.
You can use UTL_RAW.CAST_TO_VARCHAR2.

The CAST_TO_VARCHAR2 function converts the raw input string into a VARCHAR2 datatype. The data is not altered; only the data type is changed. The current NLS language is used.

Q 16. What is the difference between substr and instr function in Oracle?

SUBSTR (sub-string)  Vs    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.

Q 17. Explain the difference between replace() and translate() functions in Oracle?

REPLACE()     Vs   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 )

Q 18. What is the difference between primary key and unique key and foreign key in Oracle?

Primary Key Vs    Unique Key Vs    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

Q 19. What is a trigger and what are its types in Oracle?
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

Q 20. Write a trigger example in Oracle.

To create a new trigger in Oracle, you use the following CREATE TRIGGER statement:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;
Code language: SQL (Structured Query Language) (sql)
Let’s examine the syntax of the CREATE TRIGGER statement in more detail.

A trigger has two main parts: header and body.

The following illustrates the trigger header:

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER } triggering_event ON table_name
    [FOR EACH ROW]
    [FOLLOWS | PRECEDES another_trigger]
    [ENABLE / DISABLE ]
    [WHEN condition]
Code language: SQL (Structured Query Language) (sql)
And this is the trigger body:

DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;
Code language: SQL (Structured Query Language) (sql)
As you can see, the trigger body has the same structure as an anonymous PL/SQL block.

Q 21. Please explain compound trigger in Oracle.
The compound trigger was in introduced in Oracle 11g. This single trigger on a table enables the user to specify the actions for each of the four timing points:

Before the firing statement
Before each row that the firing statement affects
After each row that the firing statement changes
After the firing statement
In other words, Compound trigger merges all the four triggering events into the single piece of code, and as a result, the performance of the database server is improved during the bulk operations. The Compound trigger is only for the DML triggers.

Q 22. What are packages in PL SQL and also explain its advantages?

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
 

Q 23. How to execute the package in Oracle?

The following shows the syntax for executing a procedure:

EXECUTE procedure_name( arguments);
Code language: SQL (Structured Query Language) (sql)

Or

EXEC procedure_name( arguments);
Code language: SQL (Structured Query Language) (sql)
For example, to execute the print_contact procedure that prints the contact information of customer id 100, you use the following statement:

EXEC print_contact(100);

 

Q 24. What is translate in Oracle?
The translate function replaces the characters in a string with the new set of characters one by one. For example: the first character in the string is replaced by the first character of the original string and so on. The translate function always returns a string value.

Example:
                                                   
Syntax: 

TRANSLATE( string1, string_to_replace, replacement_string )
String1: the string which has to be replaced
string_to_replace: the string that has to be searched in the string 1
replacement_string: the new string with which string1 will be replaced.

Q 25. What are the various constraints used in Oracle?
Once the table is created, then the user can add any value in the table, but the values have to be validated. In this case, the constraints act as a validator which checks the data validity.

There are five types of constraints: 
NOT NULL: The column will not accept the NULL value
UNIQUE: It will check whether the record entered is unique or not.
PRIMARY: The column will have a value, and it will be unique. In a table, only one primary key should be there.
CHECK: when the user wants the record in the column to be of a particular type.
FOREIGN KEY: to relate more than one table that means matching the value of one table with that of another table.

Q 26. Please explain drop constraint Oracle.
Drop constraint is used for removing the constraints from the table.

Syntax: 

ALTER TABLE table_name
DROP CONSTRAINT constraint_name

Example:
                                                   
For example: if the table name is INTERVIEW. QUESTIONS and the constraint name is TEST_ CONST

ALTER TABLE INTERVIEW. QUESTIONS
DROP CONSTRAINT TEST_ CONST

Q 27. What is the dynamic SQL in Oracle?
Dynamic SQL is the programming methods in which the SQL statements are constructed at the run time. Dynamic SQL is used when there is a requirement of the DDL(database definition language) statements while writing the programs or when the user is not aware of the compilation time of the SQL statement.

Q 28. Please explain joins in Oracle.
When the data required by the user is in more than one table, then Join comes into the picture. Oracle Join is used to combine the rows from more than one tables. Join also retrieves data from multiple tables and creates a new table.

Q 29. Please explain oracle left join with an example.
Left join returns all the records from the left-hand side of the table which is specified in the ON condition, only those records from the right table where the join condition is met.

Example:
                                                   
syntax: 
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Q 30. What is Oracle join syntax?

Example:
                                                   
Here is the syntax: 
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
An Inner join is also called a simple join, and it is the mostly used join.

Q 31. Explain self joins in Oracle.
A self join means in a table each row is joined to other rows in the table and also to itself

Here, the standard JOIN command is used in which both the tables in the join are the same tables.

Example:
                                                   
SELECT T2.name
FROM category T1
JOIN category T2
ON T2.parent = T1.id
WHERE T1.name = 'Fatskills'