Oracle Interview Questions


Oracle Interview Questions

  1.  What are Schema Objects?

A schema is said to be a collection of the logical structures of schema objects or data. A schema has the same name as that user and is owned by the database user. Schema objects are manipulated and created with SQL which comprises of the following object types: Clusters, Database links, Dimensions, Indexes, Java sources, Tables, Views, Operators, Sequences, Synonyms, etc.

Learn these Oracle Interview Questions to get placements in MNC.

  1. What is the difference between varchar & varchar2 data types?

Varchar is capable of storing up to 2000 bytes whereas Varchar2 stores up to 4000 bytes. Varchar occupies space for NULL values and Varchar2 won’t occupy any space. Both are different with respect to space.

  1. In what language Oracle has been built?

Oracle is developed with the use of C language

  1. What is known as Raw datatype?

Raw datatype has been used to store the values in the binary data format. The maximum size for a raw present in a table is said to be 32767 bytes.

  1. Define the use of NVL function

The NVL function has been used for replacing the NULL values using given value or another value. For e.g., NVL(Value, replace value)

Prepare these Oracle Interview Questions with Answers well in order to crack the interview easily.

  1. What commands are used for months calculation?

In Oracle, the months_between function has been used to identify the number of months which exists between the given dates. For e.g., Months_between(Date 1, Date 2)

  1. Define nested tables

The nested table is referred as a data type in Oracle. It has been used to support columns which comprise of multi-valued attributes. Also, it holds all the subtable.

  1. What is known as COALESCE function?

COALESCE function is used for returning the value that is set to be null present in the list. If all values in the list are said to be null, then the coalesce function returns NULL.

Coalesce(value1, value2, value3, …)

  1. Define BLOB datatype

A BLOB datatype is considered to be a varying length binary string that has been used for storing two gigabytes memory. Length must be particularly in Bytes for the BLOB.

These Oracle Interview Questions helps you to brush up your knowledge in an effective way.

  1. How will we represent comments in Oracle?

Comments in Oracle are represented in two ways. They are

  • Single statement: Two dashes(-) before starting the line.
  • Block of statement: /*¬¬¬–*/ has been used to represent it as comments.
  1. Describe the difference between Replace and Translate?

Replace has been used to substitute a single character using a word whereas the Translate has been used for the character by character substitution.

  1. Define DML

DML is known as Data Manipulation Language which is used to manipulate and access data in the existing objects. DML statements include select, insert, delete and update. It will not entirely commit the current transaction.

  1. How can we display rows in the table without the duplicates?

Duplicate rows may be removed with the help of the keyword DISTINCT in the select statement.

These Oracle Interview Questions with Answers help you obtain your desired goal of getting placed in a highly paid job.

  1. Define the usage of Merge statement

Merge statement is said to be used for selecting rows from one or more data source for the purpose of insertion and updating into a table or view. It is used to merge multiple operations.

  1. What is referred as a NULL value in Oracle?

NULL value represents unknown or missing data. This has been used as a placeholder or it is represented in as the default entry in order to indicate which there has no actual data present.

  1. What is known as USING Clause? Give example.

The USING clause has been used to represent the column to test for equality if two tables are joined together.

[sql]Select * from the employee to join salary using the employee ID[/sql]

Employee tables join the Salary tables using the Employee ID.

  1. Define the Key preserved table

A table has been set to be a key-preserved table if every table key may also be the result key of the join. It guarantees to return the only single copy of each row from the base table.

  1. What is referred as WITH CHECK option?

The WITH CHECK option clause refers the check level to be done in the DML statements. It has been used to prevent the changes to a view which would deliver results that are not contained in the subquery.

  1. What is the use of the aggregate functions in Oracle?

An aggregate function is said to be a function where the values of the multiple records or rows have been combined together to obtain a single value output. The common aggregate functions include average, count, sum.

  1. What is known as GROUP BY clause?

A GROUP BY clause has been used in choosing the statement where it gathers data from the multiple records and collects the results by one or more columns.

  1. What is known as a subquery and what are the various kinds of subqueries?

A subquery is also known as an inner query or nested query which has been used to obtain data from multiple tables. A subquery has been added in the where clause of the main query. There are two various kinds of subqueries. They are

  1. Correlated subquery

A correlated subquery could not be as an independent query but could reference column in a table which is listed in the outer query from the list.

  1. Non-correlated subquery

This could be examined as if it was an independent query. The results of the subquery have been submitted to the parent query or main query.

  1. Define cross join?

The cross join is referred as the Cartesian product of records from the tables which is present in the join. Cross join delivers a result that merges each row from the 1st table with each row from the 2nd table.

  1. What is referred as temporal data types in Oracle?

Oracle offers the following temporal data types:

Date Data Type: It is referred as the different formats of Dates

TimeStamp Data Type: It specifies various formats of Time Stamp

Interval Data Type: It is the interval between the time and date

Oracle Course in Chennai at FITA Academy provides you an in-depth training of the Oracle concepts with certification.
    1. How can we create privileges in Oracle?

    A privilege is said to be nothing but the right to implement an SQL query or to access another user object. Privilege may be provided as user privilege or system privilege.

    [sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]

    These Oracle Interview Questions help you to brush up your knowledge before appearing for an interview.

    1. Define VArray?

    VArray is defined as an Oracle data type which has been used to have columns that contain multivalued attributes. It holds the bounded array of values.

    1. How can we obtain field details of a table?

    Describe <Table_Name> has been used to obtain the specific table field details.

    1. Explain the difference between alias and rename?

    Alias is referred to be a temporary name which is given to a column or table whereas Rename is known as a permanent name that is given to a column or table. Alias is said to be an alternate table name or column name and Rename is considered to be nothing but the replacement of name.

    These Oracle Interview Questions with Answers gives a clear idea about what an interviewer may ask during the time of interview.

    1. What is referred as a View?

    A view is defined as a logical table that based on one or more views or tables. The tables upon that the view is based are known as Base Tables and it does not have data.

    1. Define cursor variable

    A cursor variable has been associated with various statements that hold different values during the runtime. A cursor variable is said to be a kind of the reference type.

    1. What is called as the SET operators?

    SET operators have been used with two or more queries. Those operators are Minus, Union All, Intersect, Union.

    1. What is known as the cursor attributes?

    Each cursor exists in Oracle contain set of attributes that enable the application program to examine the cursor state. Thus the attributes may be used for checking whether a cursor is closed or opened, found or not found and also identify the row count.

    1. What is the procedure to delete the duplicate rows in a table?

    The duplicate rows in a table can be deleted with the use of ROWID.

    Prepare these Oracle Interview Questions well and get placed in a high paid job.

    1. Explain about the attributes of the cursor

    There are three types of Attributes of Cursor. They are

    %Found

    Returns NULL if the cursor is open and the fetch has not been implemented

    Returns TRUE if the cursor fetch has been successfully executed

    Returns False if no rows have been returned.

    %NOT FOUND

    Returns NULL if the cursor is open and fetch is not executed

    Returns False if the fetch is implemented

    Returns True if no row was returned

    %ISOPEN

    Returns true if a cursor is open

    Returns false if a cursor is closed

    %ROWCOUNT

    Returns the no of rows fetched. It must be iterated through the entire cursor to provide the exact real count

    1. Is it possible to store the pictures in the database and if so, How it is to be done?

    Yes, we are able to store pictures in the database by the Long Raw Datatype. This datatype has been used to store the binary data for 2 gigabytes of length. But the table should have only on the Long Raw datatype.

    1. Define integrity constraint?

    The integrity constraint is said to be a declaration defined a business rule for the table column. Integrity constraints have been used to make sure the consistency and accuracy of data in a database. There are types such as Domain Integrity, Referential Integrity & Domain Integrity.

    1. Define ALERT

    An alert is said to be a window that has been appearing in the center of the screen which is overlaying a portion of a current display.

    1. Explain hash cluster

    Hash cluster is referred as a technique that is used to store the table for rapid retrieval. Apply hash value on the table to retrieve rows from a table.

    1. Describe the various constraints that are used in Oracle

    The constraints used in the Oracle are listed below

    NULL: It specifies a particular column which can have NULL values

    NOT NULL: It indicates that the particular column cant have NULL values

    CHECK: Evaluate the values that in the provided column in order to reach the specific criteria.

    DEFAULT: It indicates the value that has been assigned to the default value.

    1. Describe the difference between INSTR and SUBSTR

    INSTR gives character position in which the pattern has been found in a string and SUBSTR returns the particular portion of a string.

    INSTR returns numeric whereas SUBSTR returns a string.

    1. What is referred as the parameter mode which can be passed to a procedure?

    IN, OUT, and INOUT are the parameter modes which can be passed to a procedure.

    1. Explain the various Oracle Database objects?

    There are various data objects in Oracle –

    Tables – it is set of elements organized in horizontal and vertical

    Views – it is the virtual table derived from one or more tables

    Indexes – it is the performance tuning method for generating the records

    Synonyms – Alias name for the tables

    Sequences – Multiple users process unique numbers

    Tablespaces – Logical storage unit in Oracle

    1. Describe the differences between List item and LOV

    List items are said to be single item whereas LOV is property. List of items has set to be a collection of the list of items. List of an item can contain only one column whereas LOV may have one or more columns.

    Learn these Oracle Interview Questions with Answers to get placed in a well-reputed company.

    1. What is known as Grants and Privileges?

    Privileges are referred as the rights to implement SQL statements which means right to connect. Grants are provided to the object, thus the objects may be accessed accordingly. Grants can be given by the creator or owner of an object.

    It is one of the commonly asked Oracle Interview Questions For Freshers at the time of interview.

    1. Define the difference between $ORACLE_BASE and $ORACLE_HOME

    ORACLE_BASE is referred to be the root or main directory of an Oracle whereas ORACLE_HOME has been located beneath base folder in which all the Oracle products reside.

    1. What is known as the fastest query method to obtain data from the tablet?

    The row may be obtained from the table with the use of ROWID. The usage of ROW ID is the fastest query method to get data from the table.

    1. What is referred as the maximum number of triggers which can be applied to a single table?

    12 is considered to be the maximum no of triggers which can be applied to a single table.

    These Oracle Interview Questions with Answers help you enhance your career.

    1. In what way we can display row numbers with records?

    For displaying row numbers with the record numbers:

    1 Select rownum, <fieldnames> from table;

    This query displays field name and row numbers from the table.

    1. How the last record added to a table can be viewed by us?

    Last record may be added to a table and this can be done by:

    1 Select * from (select * from employees order by rownumdesc) where rownum<2;

    1. Define the difference between Cross Join and Cartesian Join

    There are no differences that are occurred between the join. Cross and Cartesian joins are considered to be same. Cross joins provides a Cartesian product of two tables – Rows from the first table has been multiplied by another table which is known as a Cartesian product.

    Cross join without where the clause provides the Cartesian product.

    1. What is known as the data type of DUAL table?

    The DUAL table is said to be a one-column table that is present in Oracle database. The table contains a single VARCHAR(1) column known as DUMMY that holds a value of ‘X’.

    1. In what way we can display employee records who obtain more salary than the average one in the department?

    This can be done by the following query:

    1 Select * from employee where salary>(select avg(salary)) from dept, employee where

    1. What is known as PL SQL?

    PL SQL is considered to be a procedural language that has interactive SQL, as well as the procedural programming language which constructs such as iteration and conditional branching.

    Prepare these SQL Server Interview Questions well before appearing for the interview.

    1. Describe the difference between TYPE RECORD and % ROWTYPE

    The TYPE RECORD has been used when a query gives back the column of various tables or views.

    For e.g., TYPE r_emp is RECORD (snosmp.smpno%type,snamesmpsname %type)

    e_recsmp %ROWTYPE

    Cursor c1 is select smpno, dept from smp;

    e_rec c1 %ROWTYPE

    1. Describe the uses of the cursor

    The cursor is said to be a named private area present in SQL from which the information can be accessed. They are necessary for functioning each row individually for the queries which return multiple rows.

    It is one of the important PL SQL Interview Questions.

    1. Display cursor code for loops

    Cursor states %ROWTYPE as the loop index implicitly. Then it opens a cursor, obtains rows of values from active set in the fields of the record and shuts when all the records are being processed.

    For e.g., FOR smp_rec IN C1 LOOP

    Totalsal=totalsal+smp_recsal;

    ENDLOOP;

    These Oracle SQL Interview Questions help you to get through the interview easily.

    1. What are the uses of a database trigger?

    A PL/SQL program unit has been associated with a specific database table that is known as a database trigger. Its uses are listed below

    1. Log events transparently
    2. Maintaining replica tables
    3. Enforce complex business rules
    4. Executing complex security authorizations
    5. Deriving column values
    6. Auditing data modifications
    1. Describe the two various kinds of exceptions
    2. Error handling section of PL/SQL block is known as Exception. They contain two types such as predefined and user-defined.

      It is one of the important Oracle PL SQL Interview Questions which has been asked during the interview.

      1. Describe Raise_application_error.

      It is considered to be a procedure of package DBMS_STANDARD which permits issuing of user_defined error messages from the database trigger or saved sub-program.

      It is considered to be the commonly asked Basic SQL Interview Questions. So prepare well to crack the interview in a successful way.

      1. Display how procedures and functions are known in a PL SQL block.

      Function is known as the part of an expression.

      total:=calculate_sal(‘b644’)

      Procedure is known as a statement in PL/SQL.

      Calculate_bonus(‘b644’);

      Click Here to know about DevOps Interview Questions For Freshers
      1. Describe two virtual tables that are available during database trigger execution.

      Table columns are known as THEN.column_name and NOW.column_name.

      For INSERT related triggers, NOW.column_name values are available only.

      For DELETE related triggers, THEN.column_name values are available only.

      For UPDATE related triggers, both Table columns are available.

      1. Describe the rules to be applied to NULLs during comparisons.

      Prepare these Advanced SQL Interview Questions and brush up your knowledge to get placed in a high paid job.

      1. NULL is never TRUE or FALSE
      2. NULL cannot be unequal or equal to other values
      3. If a value present in an expression is said to be NULL, then the expression itself estimates to NULL except for the concatenation operator (||)
      1. Explain the process of PL SQL compiled
      2. The compilation process such as syntax check, bind and p-code generation processes.

        The syntax checking examines the PL SQL codes for compilation errors. When all the errors are said to be corrected, a storage address has been assigned to the variables which hold data. It is known as binding. P-code is considered to be a list of instructions for the PL SQL engine. P-code has been saved in the database for the name blocks and has been used the next time it is implemented.

        1. Explain the difference between Syntax and runtime errors.

        A syntax error can easily be detected by a PL/SQL compiler. For e.g., incorrect spelling.

        A runtime error is managed with the help of exception-handling part in a PL/SQL block. For e.g., SELECT INTO statement, that does not provide back any rows.

        1. Describe Commit, Rollback, and Savepoint.

        COMMIT statement

        1. Other users may see the data changes made by the transaction
        2. The locks accomplished by the transaction have been released
        3. The work which was done by the transaction becomes permanent

        ROLLBACK statement: it gets issued while the transaction ends. Hence the following is true.

        1. The work done in a transaction has been undone as if it was never issued.
        2. All locks accomplished by a transaction have been released.

        It undoes all the work which was done by the user in a transaction. Using SAVEPOINT, only part of the transaction may be undone.

        1. What is ORDBMS? Can we say oracle as ORDBMS?

        The full form of ORDBMS is object relational database management system. This form of database management system is called as a hybrid system as it is used for both objects based database and relational database. It helps to sort and locate files faster. It helps to filter and retrieve the files which share the same characteristics. Yes Oracle is an ORDBMS. Oracle is a vast subject with huge Oracle interview questions which requires constant learning.

        1. What is oracle index?

        Oracle index is created with one or more column of the table which is used to have access to the row of the data base. They are used to speed up the queries which access to the small portion of the data base. Oracle allows much number of indexes in the tables to access the different types of queries. For understanding the database management system learn about Pl SQL Interview questions.

        1. Explain the term grid and cluster in Oracle?

        A grid is the collection of server or collection of the cluster where the server is running the application server software. The grid infrastructure manages the demand and the supply. The grid consists of the application and the respective linked database server to manage the workload manageable. A Cluster is a single server used to create a grid. A Cluster is a place where the database or application resides and it is responsible for the high performance of the database system. The Cluster provides an alternative server in case of server failure. So many layers or components are used to make the application or the database available. Before attending the interview learn the SQL Server Interview questions to sharpen your knowledge.

        1. Explain how view option is useful in Oracle?

        The View is very helpful in seeing the data without storing it in the object, View help to apply the control by hiding some of the columns, it helps to join two or more tables as per the requirement, and ensure the security by controlling the access to tables. A view is a logical table and it does not have data. Start your learning with the Basic SQL Interview questions then learns the advanced level questions for better understanding of the subject.

        1. Explain the different types of queries?

        Normal queries, subqueries, co-related queries, nested queries, and compound queries are some of the types of queries. A query is used to execute the operation to one or more tables or views. A Query is a top-level select statement and subquery is nested query written for another SQL statement. The correlated query is the query that is followed by an outer query. When performing the compound query the control is obtained by using the parentheses matching record to be performed. SQL Interview Questions help you to understand the query language required for the database management.

        1. Differentiate clustered index and a non-clustered index?

        An index contains the key required for the table or view. The keys are stored in a server to find the row or row associated with the key values. Cluster Index uses the key values to store the data. A non-cluster index stores the data in a logical order which does not match the order of the physically stored data. Learning Oracle DBA interview questions for freshers and experienced enhances the confidence level to attend the interview with confidence.

        1. What is tablespace?

        A tablespace is a storage location where it specifies the database storage location logically. It is related to the logical database or database schema. Each object in the database may have different schema. A tablespace is used to group the different schema. Data is stored logically in tablespaces and physically in the data files. Oracle Interview questions with answers provide you the simple and detailed answers with relevant examples. Learning Oracle interview questions with answers would facilitate the knowledge required for the interview.

        1. What is material view? What is the difference between material view and standard view?

        A material view is a database object which shows the results of a query. It can be a data or a subset of the row, column or summary, etc. The standard view uses a query to list the data, whereas material views are used to view the result of a query. The material view is used to reduce the network loads, reduce the computing problems, create an environment for deployment, and group the data.

        1. What is a user-Exit in Oracle?

        A user exit is a C subroutine called from Oracle database to do a special process. You can use SQL statements and PL or SQL blocks in your user exit. A user exit is harder to write than SQL or PL/SQL and used for triggers.

        1. Define Roles in Oracle?

        A role is used to grant privileges to the users. A role is used to save time and effort to the database system. Roles are helpful to give the right to access another user’s object to execute a particular type of SQL statement.

        1. Describe Oracle instances?

        Database instance allocates memory area called as system global area {SGA} and it is used for the background process. It is used for maintaining the internal data which are subject to a processor thread for operations. Store the data blocks from the disk, buffer the redo data, and store the plans to be executed. The database instance can be configured by a single instance configuration and Oracle real application clusters configuration etc.

        1. What are synonyms in Oracle?

        A synonym is an alternative name for the table, view, sequence or schema and it is used to provide access to the users. The users don’t know that which schema owns the object but get the access to the database. It is easy to create a private synonym by using the create option in Oracle. There are two types of synonyms they are a public and private synonym. There is an order in using the synonyms like first access to the local objects, in case if there is no local object, then the private object is used, if there is no object or private synonym then public synonym is used.

        1. Define oracle pre compilers in Oracle database management system?

        An Oracle Precompiler is a tool used to combine the SQL statements and high-level language. Pre-compiler is used for the customized applications and it is compatible with ANSI SQL. It will run in Oracle database or ANSI SQL database management system. It is used for the static and dynamic linking with the Oracle libraries. If it is static linking then the libraries are linked to single executable program and for dynamic linking the code is partly stored in the executable program and partly stored in the libraries which are linked during the runtime. Join Oracle Training in Chennai at FITA Academy and learn more about its data management system under working professionals.

        1. What are the different types of SQL statements? Explain the types of SQL statements?

        SQL statements are divided into five types. They are:

        Data Retrieval: used for SELECT statement

        Data Retrieval {DML}: used for INSERT, UPDATE, DELETE, AND MERGE.

        Data Definition language {DDL}: This statement is used to CREATE, ALTER, DROP, RENAME, TRUNCATE.

        Transaction control statements: This statement is used for COMMIT, ROLLBACK, and SAVEPOINT

        Data control Language {DCL}: This language is used for GRANT, REVOKE.

        Example for the SELECT Statement is as follows. The Select statement appears like below.

        SELECT select_list

        FROM source

        WHERE condition{S}

        GROUP BY expression

        HAVING condition

        ORDER BY expression

        The first line tells about the SQL processor, the second line shows the database table used by the statement, the third line tells about the specific conditions. The last three lines tell about specific conditions. As per the business trend in 2018 PL SQL has a market share of about 2.2 percent. So, learning Pl SQL interview questions is a good opportunity to acquire the dream job of Oracle Pl SQL developer.

        1. What is an Alias in SQL statement and what is the difference between column, table and the alias?

        Column or table is used to get the desired result when writing the statement. Alias is the alternative name for the column or table. Alias is represented with quotation marks {“”} if you want to make it as case sensitive. When writing the code for the SELECT clause “AS” is assumed as the keyword before the alias name. SELECT is the condition, the alias is the place of the data, and “AS” is the key word used. Thus, when writing the statement alias makes the statement easier.

        1. What is a Literal? How literal is expressed in the statement?

        A Literal is a string which represents a character, a number, or a date in the SELECT list. A Literal is not a column name or column alias. The symbol for date and character literals is (”), the number literals is not represented through symbol. Learning the Basic SQL Interview Question is very helpful to understand the basic knowledge about the Oracle Database Management System.

        1. What is SQL and ISQL *plus? What is the difference between SQL and SQL *plus?

        SQL is the language used in relational database management system. SQL is also used for stream processing in the relational data stream management system. Before attending the interview learn SQL Interview Questions which boost up your confidence level. iSQL*plus is used to perform the task on the internet. It is the browser-based implementation to perform the task with the basic command line interface. The difference between the SQL and ISQL*plus are SQL is a language whereas iSQL*plus is an environment. Justification is of two types left justification and right justification whereas ISQL*Plus provides the default justification. It is not represented with short forms whereas iSQL*plus is represented with short forms. If the command is longer than one line then no need to write continuation character in SQL whereas (-) is the continuation character in the command if the command exceeds more than one line. Formatting has been done using the functions in SQL whereas commands are used to format data in iSQL*plus.

        1. What is the order of precedence used in executing SQL statements?

        The arithmetic operators (*,/,+,-), concatenation operators (||), comparison conditions, Is[NOT] NULL, LIKE, [NOT] IN, [NOT] BETWEEN, NOT Logical condition, and OR logical condition is the order used in the SQL statements. This order is called as the order of precedence in the SQL statements. Advanced SQL Interview Questions provides in-depth knowledge about the Oracle Database Management system.

        1. Describe the different type of SQL function?

        SQL functions are of two types. They are single-Row functions and multiple-Row functions. Character, number, date, conversion and general are the different types of single row functions. AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE are some of the functions which are called as multiple row functions.

        1. Explain the function in detail in the Oracle database management system?

        There are three types of functions in the relational database management system. They are character functions, number functions, and date functions. Character functions accept the character and return both the character and the number. There are two types of functions under this head. They are case-manipulation functions which include the LOWER, UPPER and the INITCAP and character-manipulation functions which include the CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, and REPLACE. The number functions accept numeric input and return the numeric values. The numeric functions are ROUND, TRUNC, and MOD. Date functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC.

        1. Explain how the conversion functions work?

        Conversion functions convert the class functions from one type to another type. This function converts the value of the data. There are two types of conversion data types. They are implicit data type conversion and explicit data type conversion. Implicit conversion is of three types and they are VARCHAR2 or CHAR TO NUMBER DATE, NUMBER TO VARCHAR2 AND DATE TO VARCHAR2. The three types of explicit data conversion are TO_NUMBER, TO_CHAR, AND TO_DATE. The format for the TO_NUMBER (char[,’format_model]). fx modifier explains the exact match and the model of TO_NUMBET function. TO_CHAR is used to convert number or date data type to character format. TO_DATE function is used to character string into date format in the Oracle database management system.

        1. What are the different types of the general function used in the SQL? Describe each function in detail?

        NVL, NVL2, NULLIF, COALESCE, and Conditional expressions are the five types of general function of the SQL. NVL converts the value of the data. For example, if a null value is in the place of exp1 then NVL function return value of exp2. In NVL2 exp1 is not null then it returns to exp2, if exp1 is null then nvL2 returns to exp3, NVL2 consists of exp1, exp2, and exp3. NULLIF is about the comparison. It compares two expressions and returns null if they are equal or return to the first expression if they are not equal. COASLESCE is the function which takes the multiple alternative values. It returns the first non-null expression in the expression list and the three COALESCE are exp1, exp2 and expn. IF-THEN-ELSE is used in the SQL statement in conditional expressions.

        1. Explain COUNT(*), COUNT (expression), COUNT (distinct expression)? And how it is written in the SQL statement? Explain the difference between these SQL functions?

        COUNT(*) is an expression which uses the information from any particular column. It returns only the rows in a specified table and counts each row separately. The number of rows includes the rows in the table, the duplicate rows and the rows with null values. Count (expression) is the function which counts the non-null values. This expression is used to sort the records in the result set at the end of the SQL statement. This expression is not encapsulated with the COUNT function and included at the end of the SQL statement as GROUP BY clause. The COUNT DISTINCT function returns the unique values and non-null values and ignores the NULL values. See the example below:

        SELECT expression1, expression2, … expression_n,

        COUNT(aggregate_expression)

        FROM tables

        [WHERE conditions]

        GROUP BY expression1, expression2, … expression_n;

        Parameters:

        expression1, expression2, … expression_n:

        The expressions are included in the GROUP BY clause at the end of the SQL statement and these expressions are not encapsulated.

        Aggregate_expression:

        The null values of the column or expression will be counted.

        Tables:

        Table is listed in the FROM clause and it can be a table which you want to retrieve the data.

        WHERE conditions:

        The WHERE condition is used only if it needed. If the condition met the records then it is used. Before attending the interview learn Basic Oracle Interview Questions for freshers to handle the interview with confidence.

        1. What is an operator? Explain the difference between ‘ANY’ and ‘ALL’ operators in the SQL statement?

        An operator is used to specify conditions and act as a link for multiple conditions in an SQL statement. Any and all are the logical operators in the SQL statement. ANY operator is used to compare the value in the list or sub query. ‘ALL’ operator is used to compare the value in the list or sub query. ANY and ALL are multiple row operators. See the below example for the ‘ANY’ and ‘ALL’ usage;

        >ANY means more than the minimum.

        < ANY means less than the maximum

        = ANY is equivalent to IN operator.

        > ALL means more than the maximum

        < ALL means less than the minimum

        <> ALL is equivalent to NOT IN condition.

        1. Differentiate “VERIFY” and “FEEDBACK” command in the SQL statement?

        VERIFY is the command used to verify the old and new values in the SQL statement. It is defined as SET VERIFY ON/OFF in the SQL statement. Feedback command is the command which shows the records returned by a query.

        1. How Is Double Ampersand (&&) used in SQL queries? Give example?

        If you want to reuse the variable then “&&” is used where no need to prompt the user each time. The two examples are:

        StandardCost < 300 && ListPrice > 500 .

        StandardCost < @SPrice && ListPrice > @LPrice

        The variables are standard cost, list price, and selling price.

        1. What is the difference between Oracle and SQL?

        Oracle and SQL both use structured query language. The SQL uses transact SQL whereas Oracle uses PL/SQL which is known as procedural language. The T-SQL is developed by Sybase and used by Microsoft. There are so many online materials available to help the students for the PL SQL Interview Questions. Learning is required when attending the interview and even after joining the job.

        1. What are the different types of joins in Oracle 8i? Explain all the joins in detail?

        There are five types of joins in Oracle 8i and prior. The joins are Equijoin, outer join, self-join, non-equi join and cartesian product.

        When a join condition is omitted then the result is called as cartesian product. The cartesian product or cross join produces two types of a result set. Cross join multiply the number of rows in the first and second table if there is no WHERE clause. The cross join works like an INNER JOIN if there is WHERE clause.

        Equijoin:

        Equi is also called an inner join or simple joins. This join contains the equality operator or comparison operator. This join returns the rows that have equivalent values in the specified columns. An inner join uses two or more tables and then return to the rows.

        Non-Equi joins:

        A non-equijoin is a condition which consists of join other than an equality operator. This join matches the column and tables with an inequality. It uses comparison operator instead of equal sign.

        Outer join:

        The outer join is used to retrieve data from multiple tables. This join is represented with plus sign{+}. There are three types of outer join left outer join, right outer join and full outer join.

        Self-join:

        This join is used to join the same table to it. Learn the Basic SQL Interview Questions for freshers experienced and keep yourself updated regarding the advance level questions in the Oracle to handle the difficult task on job.

        1. What are the joins used in Oracle 9i?

        The joins used in the Oracle are cross join, natural joins, join with the USING clause, and joins with the ON clause. Cross join produces the product from two tables. Natural joins are used to join two tables with matching data. It is similar to equijoin. Join with the using clause is used where the column name is the same but the data type is not matching. The Natural join clause and the USING clause are used internally. The natural join clause can be modified by using the USING clause to join the matching data. Joins with the ON clause are used to make the code easy and it is also used for the column with different names.

        1. Explain what is a transaction? What are the common errors that can occur when executing any transaction?

        The transaction is the logical unit which consists of a collection of a data manipulation language. The common error happens during the transaction are a mismatch of data type, size of column mismatch, server down  problem, the session is over when it comes to the concern page, and locking problem etc.

        1. What are implicit locking and explicit locking in the database?

        Implicit locks are placed by the developer and the application issue locks which are referred to as implicit locks. If the operation is read type then it applies the read lock and if the operation is written type then it applies the write lock. Some applications need access in advance. Explicit lock freezes the object and uses the object. So, it is unexpected lock from the database to provide access and the security.

        1. What is the naming rule when creating a table in the Oracle database management system?

        The naming rules are the table must begin with a letter, the character length is 1 to 30, the name can contain only alphabets, numbers from 0 to 9, _, $, #, the name should not be the duplicate name {for example the same user should not own any other object with the same name}, the table name cannot be reserved word from the Oracle server.

        1. Explain about the ‘Default option’ in a table?

        If default option is used then the column can be given default value and null value cannot be entered. The column value and the row value comes by default and the row value cannot be inserted for this particular column as the default option is used. The default value is the expression, SQL function like the date in the system and the user of the system. The value of the column cannot be the name to the pseudo column. Pseudo column of Oracle is NEXTVAL or CURRVAL. Plan your interview with reading Oracle Interview Questions.

        1. Differentiate USER TABLES and DATA DICTIONARY?

        User table consists of user information and data dictionary consist of database information. User information is stored in various systems whereas the database information is stored in the Oracle server. The data dictionary is a read-only set of tables and owned by the sys user. SQL Interview Questions and PL/SQL Interview questions are the basics for working in the Oracle database Management system.

        1. Describe the data type in Oracle?

        There are 15 types of data types according to the Oracle database system. They are CHARACTER or CHAR[length}, VARCHAR[Length}, BOOLEAN, SMALLINT, INTEGER or INT, DECIMAL{P{S} OR DEC [(P)S], NUMERI[(P[(S)], REAL, FLOAT(P), DOUBLE PRECISION, DATE, TIME, TIMESTAMP, CLOB[(length)], and BLOB [(length)]. CHARACTER data type is string type data and it is declared before itself. The minimum size of this type is 1 and the maximum size is called as CLOB. There is CHARACTER size which is larger than the table and sometimes the size of the CHARACTER is not mentioned in the database. If the size is not mentioned then it is assumed as 1.

         The minimum size of the VARCHAR is 1 and the maximum size is up to the size of the table. If the declared value and the real value differs then it results in truncation which is raised an error in the Oracle database. The BOOLEAN data type is true or false. SMALLINT shows the numeric values in between 2^-15 and 2^15-1 and values more than this range is stored in INTEGER data type. INTEGER is numeric value with 2^-31 and 2^31-1. In the DECIMAL data type the precision and the scale is declared by the user. The precision is the number of digit of the number and the scale is the number of digits to the right of the decimal point. The three ways of the declaration are DECIMAL, DECIMAL (P) and DECIMAL(P,S). NUMERIC is also the same as like the DECIMAL in the point base. The REAL data type is numeric value with a precision of 64. FLOAT data type is defined with a maximum of 64. If no value is declared then it is assumed as 64. The FLOAT data type default range is 126 binary or 38 decimal.

         The double precision range is 126 binary. The DATE data type is in the format of year-month and day. The values for the month is 1 to 12, the value for the day ranges from 1 to 31 and the value of the year starts from 0 to 9999. The date is written within the single quotes like DATE ‘2018-04-16. The format for the time is HH:MM:SS. This is also written in single quotes and optional fraction value can be used to explain the nanoseconds. The minute value range is 00 and 59, the second’s value range is 00 and 61.999999, and the hour values range is 0 and 23. TIMESTAMP is the data type with values of date and time. Date and time are mentioned with space for clear understanding. CLOB data type is the data with longer character than CHARACTER OR VARCHAR. BLOB shows the binary values. BLOB data type is 2 gigabytes in length.

        1. Is it possible to modify a table in a column? Explain how?

        The different types of modifications which are possible in the oracle are the numeric column change with increase in the width or precision, the numeric or character column change with increase in the width, the column or table with null values or no rows can be decreased, data type change is possible if the column value is null, in case of conversion for the CHAR column or VARCHAR2 data type the column should contain null values. Thus the column, table and data type can be modified only with certain conditions in the Oracle database. PL/SQL Interview Questions deal with the practical scenarios and it is difficult to clear the interview without preparation.

        1. What is long data set? Is it possible to use the long dataset in the oracle?

        One long column can be used for one table and long column is copied during the usage of subquery for the table. The long column is not included in the group or order by clause in the Oracle database system. The column is defined with a ratio to the length as 12 whereas long column is defined with dimension more than 12.

        Oracle database executive should know about the database design, database implementation, backup, and recovery. In all the phases of the project development communication skills and the technical skills are given paramount importance.

        The above-provided oracle interview questions are very helpful for the interview preparation. Most of the candidates focus on the latest questions for the interview preparation. We would like to help the students to win over the interview and excel in their professional life.






  • Quick Enquiry

    Please wait while submission in progress...


    Contact Us

    Chennai

      93450 45466

    Bangalore

     93450 45466

    Coimbatore

     95978 88270

    Online

    93450 45466

    Madurai

    97900 94102

    Pondicherry

    93635 21112

    For Hiring

     93840 47472
     hr@fita.in

    Corporate Training

     90036 23340


    Read More Read less

    FITA Academy Branches

    Chennai

    Bangalore

    Coimbatore

    Other Locations

    FITA Academy - Velachery
    Plot No 7, 2nd floor,
    Vadivelan Nagar,
    Velachery Main Road,
    Velachery, Chennai - 600042
    Tamil Nadu

        :   93450 45466

    FITA Academy - Anna Nagar
    No 14, Block No, 338, 2nd Ave,
    Anna Nagar,
    Chennai 600 040, Tamil Nadu
    Next to Santhosh Super Market

        :   93450 45466

    FITA Academy - T Nagar
    05, 5th Floor, Challa Mall,
    T Nagar,
    Chennai 600 017, Tamil Nadu
    Opposite to Pondy Bazaar Globus

        :   93450 45466

    FITA Academy - Tambaram
    Nehru Nagar, Kadaperi,
    GST Road, West Tambaram,
    Chennai 600 045, Tamil Nadu
    Opposite to Saravana Jewellers Near MEPZ

        :   93450 45466

    FITA Academy - Thoraipakkam
    5/350, Old Mahabalipuram Road,
    Okkiyam Thoraipakkam,
    Chennai 600 097, Tamil Nadu
    Next to Cognizant Thoraipakkam Office and Opposite to Nilgris Supermarket

        :   93450 45466

    FITA Academy - Porur
    17, Trunk Rd,
    Porur
    Chennai 600116, Tamil Nadu
    Above Maharashtra Bank

        :   93450 45466

    FITA Academy Marathahalli
    No 7, J J Complex,
    ITPB Road, Aswath Nagar,
    Marathahalli Post,
    Bengaluru 560037

        :   93450 45466

    FITA Academy - Saravanampatty
    First Floor, Promenade Tower,
    171/2A, Sathy Road, Saravanampatty,
    Coimbatore - 641035
    Tamil Nadu

        :   95978 88270

    FITA Academy - Singanallur
    348/1, Kamaraj Road,
    Varadharajapuram, Singanallur,
    Coimbatore - 641015
    Tamil Nadu

        :   95978 88270

    FITA Academy - Madurai
    No.2A, Sivanandha salai,
    Arapalayam Cross Road,
    Ponnagaram Colony,
    Madurai - 625016, Tamil Nadu

        :   97900 94102

    FITA Academy - Pondicherry
    410, Villianur Main Rd,
    Sithananda Nagar, Nellitope,
    Puducherry - 605005
    Near IG Square

        :   93635 21112

    Read More Read less
    • Are You Located in Any of these Areas

      Adyar, Adambakkam, Anna Salai, Ambattur, Ashok Nagar, Aminjikarai, Anna Nagar, Besant Nagar, Chromepet, Choolaimedu, Guindy, Egmore, K.K. Nagar, Kodambakkam, Koyambedu, Ekkattuthangal, Kilpauk, Meenambakkam, Medavakkam, Nandanam, Nungambakkam, Madipakkam, Teynampet, Nanganallur, Navalur, Mylapore, Pallavaram, Purasaiwakkam, OMR, Porur, Pallikaranai, Poonamallee, Perambur, Saidapet, Siruseri, St.Thomas Mount, Perungudi, T.Nagar, Sholinganallur, Triplicane, Thoraipakkam, Tambaram, Vadapalani, Valasaravakkam, Villivakkam, Thiruvanmiyur, West Mambalam, Velachery and Virugambakkam.

      FITA Velachery or T Nagar or Thoraipakkam OMR or Anna Nagar or Tambaram or Porur branch is just few kilometre away from your location. If you need the best training in Chennai, driving a couple of extra kilometres is worth it!