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

  1. Single statement: Two dashes(-) before starting the line.
  2. Block of statement: /*¬¬¬–*/ has been used to represent it as comments.
  3. 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

  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 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
  7. Describe the two various kinds of exceptions

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’);

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

62. Explain the process of PL SQL compiled

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 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.

Comments are closed.