Oracle Interview Questions

oracle interview questions

Facing interviews are just not easy but when you are looking forward to getting a career in such an industry who deals with databases then you need to get enough data about them. The following information of Oracle with answer many of the Oracle Interview Questions.

About Oracle

Oracle is found to be the master of database management. It is a software which is extremely helpful for the software developers. Every business requires some vendors and Oracle is found to be the top-notch IT vendor. It provides a database which is the most essential element needed in every IT offices. Oracle has been on the top of the list for providing a database for a long period. It has reached companies globally and doing good business. Many corporate IT companies are associated with Oracle and availing the unbeatable services throughout. The database provided by Oracle supports different programming hub and languages like Java. It has a unique feature of managing database with proper distinguishing.

Best Oracle Interview Questions of 2020

1. What is Oracle?

Oracle is one of the popular database provided by Oracle Corporation, which works on relational management concepts and hence it is referred as Oracle RDBMS as well.

2. How will you identify Oracle Database Software Release?

 Oracle follows a number of format for every release.

For Example, release 10.1.0.1.1 can be referred as below mentioned:

10: Major DB Release Number

1: DB Maintenance Release Number

0: Application Server Release Number

1: Component Specific Release Number

1: Platform Specific Release Number

3. How will you differentiate between VARCHAR & VARCHAR2?

Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length.

VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.

VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.

4. What is the difference between TRUNCATE & DELETE command?

The finer differences between the two include:

  • TRUNCATE is a DDL operation while DELETE is a DML operation.
  • TRUNCATE drops the structure of a database and hence cannot be rolled back while DELETE command can be rolled back.
  • The TRUNCATE command will free the object storage space while the DELETE command does not.
  • 5. What is meant by RAW datatype?

    RAW datatype is used to store variable-length binary data or byte strings.

    The difference between RAW & VARCHAR2 datatype is that PL/SQL does not recognize this data type and hence, cannot do any conversions when RAW data is transferred to different systems. This data type can only be queried or inserted in a table.

    Syntax: RAW (precision)

    6. What is meant by Joins? List out the types of joins. 

     oins are used to extract data from multiple tables using some common column or condition.

    There are various types of Joins as listed below:

    • INNER JOIN
    • OUTER JOIN
    • CROSS JOINS or CARTESIAN PRODUCT
    • EQUI JOIN
    • ANTI JOIN
    • SEMI JOIN

    7. What is the difference between SUBSTR & INSTR functions?

    SUBSTR function returns the sub-part identified by numeric values from the provided string.

    Example: [Select SUBSTR (‘India is my country’, 2, 4) from dual] will return “Indi”.

    INSTR will return the position number of the sub-string within the string.

    Example: [SELECT INSTR (‘India is my country’, ‘a’) from dual] will return 6.

    7. How can we find out the duplicate values in an Oracle table?

    We can use the below example query to fetch the duplicate records.

    SELECT EMP_NAME, COUNT (EMP_NAME)

    FROM EMP

    GROUP BY EMP_NAME

    HAVING COUNT (EMP_NAME) > 1;

    9. How does the ON-DELETE-CASCADE statement work?

    Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.

    We can add ON DELETE CASCADE option on an existing table using the below set of commands.

    Syntax:

    ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES

    PARENT_T1 (COLUMN1) ON DELETE CASCADE;

    10. What is a NVL function? How can it be used?

    NVL is a function, which helps the user to substitute a value if null is encountered for an expression.

    It can be used as the below syntax.

    [NVL (Value_In, Replace_With)]

    11. What is the difference between a Primary Key & a Unique Key?

    Primary key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

    Given below are few differences:

    • The primary key can be only one on the table while unique keys can be multiple.
    • The primary key cannot hold null value at all while Unique key allows multiple null values.
    • The primary key is a clustered index while a unique key is a non-clustered index.

    12. How TRANSLATE command is different from REPLACE? 

    TRANSLATE command translates characters one by one in the provided string with the substitution character. REPLACE will replace a character or a set of characters with a complete substitution string.

    Example:

    TRANSLATE (‘Missisippi’,’is’,’15) => M155151pp1

    REPLACE (‘Missisippi’,’is’,’15) =>  M15s15ippi

    13. How can we find out the current date and time in Oracle?   

    We can find the current Date & Time using SYSDATE in Oracle.

    Syntax:

    SELECT SYSDATE into CURRENT_DATE from dual;

    14. Why do we use COALESCE function in Oracle?

    COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. Minimum two arguments should be there in an expression.

    Syntax:

    COALESCE (expr 1, expr 2, expr 3…expr n)

    15. How will you write a query to get a 5th RANK student from a table STUDENT_REPORT?

    The Query will be as follows:

    SELECT TOP 1 RANK

    FROM (SELECT TOP 5 RANK

    FROM STUDENT_REPORT

    ORDER BY RANK DESC) AS STUDENT

    ORDER BY RANK ASC;

    16. When do we use GROUP BY clause in a SQL Query?

    GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG etc.

    Syntax:

    SELECT COLUMN_1, COLUMN_2

    FROM TABLENAME

    WHERE [condition]

    GROUP BY COLUMN_1, COLUMN_2

    17. What is the quickest way to fetch the data from a table?

    The quickest way to fetch the data would be to use ROWID in the SQL Query.

    18. Where do we use DECODE and CASE Statements?

    Both DECODE & CASE statements will function like IF-THEN-ELSE statement and they are the alternatives for each other. These functions are used in Oracle to transform the data values.

    Example:

    DECODE Function 

    Select ORDERNUM,
    DECODE (STATUS,'O', ‘ORDERED’,'P', ‘PACKED,’S’,’SHIPPED’,’A’,’ARRIVED’)
    FROM ORDERS;

    CASE Function

    Select ORDERNUM
    , CASE (WHEN STATUS ='O' then ‘ORDERED’
    WHEN STATUS ='P' then PACKED
    WHEN STATUS ='S' then ’SHIPPED’
    ELSE ’ARRIVED’) END
    FROM ORDERS;

    Both the commands will display Order Numbers with respective Status as,

    If,

    Status O= Ordered
    Status P= Packed
    Status S= Shipped
    Status A= Arrived

    19. Why do we need integrity constraints in a database?

     Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.

    Various integrity constraints available include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

    20. What do you mean by MERGE in Oracle and how can we merge two tables?

    MERGE statement is used to merge the data from two tables. It selects the data from the source table and inserts/updates it in the other table based on the condition provided in the MERGE query.

    Syntax:

    MERGE INTO TARGET_TABLE_1
    USING SOURCE_TABLE_1
    ON SEARCH_CONDITION
    WHEN MATCHED THEN
    INSERT (COL_1, COL_2…)
    VALUES (VAL_1, VAL_2…)
    WHERE <CONDITION>
    WHEN NOT MATCHED THEN
    UPDATE SET COL_1=VAL_1, COL_2=VAL_2…
    WHEN <CONDITION>

    21. What is the use of Aggregate functions in Oracle?

    Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations.

    Few of them are listed below:

    • AVG
    • MIN
    • MAX
    • COUNT
    • SUM
    • STDEV

    22. What are the set operators UNION, UNION ALL, MINUS & INTERSECT meant to do?

    Set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are same in the source tables.

    • UNION operator returns all the rows from both the tables except the duplicate rows.
    • UNION ALL returns all the rows from both the tables along with the duplicate rows.
    • MINUS returns rows from the first table, which does not exist in the second table.
    • INTERSECT returns only the common rows in both the tables.

    23. Can we convert a date to char in Oracle and if so, what would be the syntax?

    We can use the TO_CHAR function to do the above conversion.

    The syntax will be as follows:

    SELECT to_char (to_date ('30-01-2018′, ‘DD-MM-YYYY'), ‘YYYY-MM-DD') FROM dual;]

    24. What do you mean by a database transaction & what all TCL statements are available in Oracle?

    Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements.

    The set of statements include:

    COMMIT: Used to make a transaction permanent.

    ROLLBACK: Used to roll back the state of DB to last the commit point.

    SAVEPOINT: Helps to specify a transaction point to which rollback can be done later.

    25. What do you understand by a database object? Can you list a few of them?

    An object used to store the data or references of the data in a database is known as a Database object.

    The database consists of various types of DB objects such as tables, views, indexes, constraints, stored procedures, triggers etc.

    26. What is a Nested table and how is it different from a normal table?

    A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.

    Example:

    CREATE TABLE EMP (
    EMP_ID NUMBER,
    EMP_NAME  TYPE_NAME)

    Here we are creating a normal table as EMP and referring a nested table TYPE_NAME as a column.

    27. Can we save images in a database and if yes, how?

    BLOB stands for Binary Large Object, which is a datatype that is generally used to hold images, audio & video files or some binary executables.

    This datatype has the capacity of holding data up to 4 GB.

    28. What do you understand by database schema and what does it hold?

    Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema.

    The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions etc.

    29. What is a Data Dictionary and how can it be created?

    Whenever a new database is created, a database specific data dictionary gets created by the system. This dictionary maintains all the metadata related to the database and owned by the SYS user.

    It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.

    30. What is a View and how is it different from a table?

    A view is a user-defined database object that is used to store the results of a SQL query, which can be referenced later. Views do not store this data physically but as a virtual table, hence it can be referred as a logical table.

    A table can hold data but not SQL Query results whereas View can save the query results, which can be used in another SQL Query as a whole.

    The table can be updated or deleted while Views cannot be done so.

    Career scopes and salary scale

    When you are trained with proper discipline and know what you are operating then what more you can ask for. Oracle technologies provide a wide scope of learning as well as earning. These amazing services can bring you hand full of salary approximately a lac or more per month. The job is in high demand because of the unique fields it works with and also the handsome salary offered by the companies. As a fresher, you can up to 35,000 to 45,000 dollars per annum, while for an experienced candidate, can earn up to 90,000 to 110,000 dollars per annum.

    Conclusion

    The IT companies have been waiting for such database vendor for so long but now with the launch of Oracle services the IT companies are blessed with high-end databases. Be prepared with your data about the company and the industry if you wish to crack the interview like a pro. It is not that much thought to get the details about the industry but you need to recall it on time. If you love the industry then it will be a cake walk for you.

    Intellinuts Terms : A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z