oracle 10g interview questions

Top 50 Oracle Interview Questions and Answers | Questions for Freshers and Experienced | Edureka

Searching for a new job can be more difficult know a day even searching itself might become a big job for you. So, to clear all your confusions on interview locations, interview questions we have given detail on our Wisdomjobs site. To be precise about the Oracle 10g, 10g is Oracles network calculating product group counting (among other things) a database management system (DBMS) and an application server. If you are familiar with the Oracle 10g Concepts then there are Nemours companies that offer job positions like Principal Consultant, Oracle Database Administrator, Oracle WebLogic 11g Admin, Oracle DBA Consultant – Rac/10g/11g, Oracle Database Administrator, Senior Oracle Applications DBA, Oracle SOA Suite Administrator and many other roles too. For more details on Oracle 10g job Interview Questions and Answers and Oracle 10g jobs visit our site.

Oracle Basic Interview Questions

Q1. How will you differentiate between Varchar & Varchar2?

Both Varchar & Varchar2 are the Oracle data types which are used to store character strings of variable length. To point out the major differences between these,

Varchar	Varchar2
Can store characters up to 2000 bytes	Can store characters up to 4000 bytes.
It will hold the space for characters defined during declaration even if all of them are not used	It will release the unused space

Q2. What are the components of logical database structure in Oracle database?

The components of the logical database structure in Oracle database are:

Tablespaces: A database mainly contains the Logical Storage Unit called tablespaces. This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.

Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user specified.

Q3. Describe an Oracle table

A table is a basic unit of data storage in the Oracle database. A table basically contains all the accessible information of a user in rows and columns.

To create a new table in the database, use the “CREATE TABLE” statement. First, you have to name that table and define its columns and datatype for each column.

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], … column_n datatype [ NULL | NOT NULL ] );

table_name: This specifies the name of the table that you want to create.

column..n: It specifies the number of columns which you want to add in the table. Here, every column must have a datatype and should either be defined as “NULL” or “NOT NULL”. If in case, the value is left blank, it is treated as “NULL” as default.

Q4. Explain the relationship among database, tablespace and data file?

An Oracle database possesses one or more logical storage units called tablespaces. Each tablespace in Oracle database consists of one or more files called the datafiles. These tablespaces collectively store the entire data of databases. Talking about the datafiles, these are the physical structure that confirms with the operating system as to which Oracle program is running.

Q5. What are the various Oracle database objects?

These are the Oracle Database Objects:

Tables: This is a set of elements organized in a vertical and horizontal manner. Tablespaces: It is a logical storage unit in Oracle. Views: Views are a virtual table derived from one or more tables. Indexes: This is a performance tuning method to process the records. Synonyms: It is a name for tables.

Q6. Explain about the ANALYZE command in Oracle?

This “Analyze” command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

Analyze command is used to identify migrated and chained rows of the table or a cluster.

It is used to validate the structure of an object.

This helps in collecting the statistics about the object used by the user and are then stored on to the data dictionary.

It also helps in deleting statistics that are used by an object from the data dictionary.

Q7. What types of joins are used in writing subqueries?

A Join is used to compare and combine, this means literally join and return specific rows of data from two or more tables in a database.

There are three types of joins in SQL that are used to write the subqueries.

Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key which references its own primary key.

Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.

Equi-join: An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.

Q8. RAW datatype in Oracle

The RAW datatype in Oracle is used to store variable-length binary data or byte string values. The maximum size for a raw in a given table in 32767 bytes.

You might get confused as to when to use RAW, varchar, and varchar2. Let me point out the major differences between them. PL/SQL does not recognize the data type and hence, it cannot have any conversions when RAW data is transferred to different systems. This data type can only be queried or can be inserted in a table.

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

An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations. Some common Aggregate functions are:

Q10. Explain Temporal data types in Oracle

Oracle mainly provides these following temporal data types:

Date Data Type: Different formats of Dates.

TimeStamp Data Type: Has different formats of Time Stamp.

Interval Data Type: Interval between dates and time.

Q11. What is a View?

A view is a logical table based on one or more tables or views. A View is also referred as a user-defined database object that is used to store the results of a SQL query, that can be referenced later in the course of time. Views do not store the data physically but as a virtual table, hence it can be referred as a logical table. The corresponding tables upon which the views are signified are called Base Tables and this doesn’t contain data.

Q12. How to store pictures on to the database?

It is possible to store pictures on to the database by using Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only on Long Raw data type.

Q13. Where do you use DECODE and CASE Statements?

Both these statements Decode and Case will work similar to the if-then-else statement and also they are the alternatives for each of them. These functions are used in Oracle for data value transformation.

Example:

Select OrderNum, DECODE (Status,’O’, ‘Ordered’,’P’, ‘Packed,’ S’,’ Shipped’, ’A’,’Arrived’) FROM Orders;

Select OrderNum , Case(When Status=’O’ then ‘Ordered’ When Status =’P’ then Packed When Status=’ S’ then ’Shipped’ else ’Arrived’) end FROM Orders;

Both these commands will display Order Numbers with their respective Statuses like this,

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

Q14. What do you mean by Merge in Oracle and how can you merge two tables?

Merge statement is used to merge the data from two tables subsequently. It selects the data from the source table and then inserts/updates it in the other table based on the condition provided in the query. It is also useful in data warehousing applications.

Q15. What is the data type of DUAL table?

The Dual table is basically a one-column table that is present in the Oracle database. This table has a single Varchar2(1) column called Dummy which has a value of ‘X’.

SQL Interview Questions

Q16. Explain about integrity constraint?

An integrity constraint is actually a declaration that is defined as a business rule for a table column. They are used to ensure accuracy and consistency of data in the database. It can also be called as a declarative way to define a business rule for a table’s column. There are a few types, namely:

Domain Integrity

Referential Integrity

Domain Integrity

Q17. What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is used to communicate with the server in order to access, manipulate and control data. There are 5 different types of SQL statements available. They are:

Select: Data Retrieval

Insert, Update, Delete, Merge: Data Manipulation Language (DML)

Create, Alter, Drop, Rename, Truncate: Data Definition Language (DDL)

Commit, Rollback, Savepoint: Transaction Control Statements

Grant, Revoke: Data Control Language (DCL)

Q18. Briefly explain what is Literal? Give an example where it can be used?

A Literal is a string that contains a character, a number, or a date that is included in the Select list and which is not a column name or a column alias.

Also note that, Date and character literals must be enclosed within single quotes (‘ ‘), whereas you don’t have to do that for the number literals.

For example: Select last_name||’is a’||job_id As “emp details” from employee;

In this case, “is a” is literal.

Q19. How to display row numbers with the records?

In order to display row numbers along with their records numbers you can do this:

This above query will display the row numbers and the field values from the given table.

This query will display row numbers and the field values from the given table.

Q20. What is the difference between SQL and iSQL*Plus?

SQL	iSQL*Plus
It is a language	It is an environment
Character and date columns heading are left-justified and number column headings are right-justified	Default heading justification is in Centre
Cannot be Abbreviated (short forms)	Can be Abbreviated
Does not have a continuation character	Has a dash (-) as a continuation character if the command is longer than one line
Use Functions to perform some formatting	Use commands to format data

Q21. What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are a very powerful feature of SQL. These functions can take arguments but always return some value. There are two distinct types of SQL functions available. They are:

Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions are:

Character

Number

Date

Conversion

General

Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

avg

count

max

min

sum

stddev

variance

Q22. Describe different types of General Function used in SQL?

General functions are of following types:

NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.

NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)

NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)

COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.

Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

Q23. What is a Sub Query? Describe its Types?

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in where having and from clause.

Guidelines for using subqueries:

You should enclose the sub-queries within parenthesis.

Place these subqueries on the right side of the comparison condition.

Use Single-row operators with single-row subqueries.

Use Multiple-row operators with multiple-row subqueries.

Types of subqueries:

Single-Row Subquery:Queries that return only one row from the inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>

Multiple-Row Subquery:These are queries that return more than one row from the inner Select statement. You will also find multiple-column subqueries that return more than one column from the inner select statement. Operators include: IN, ANY, ALL.

Q24. What is the use of Double Ampersand (&&) in SQL Queries? Give an example

You can use && if you want to reuse the variable value without prompting the user each time.

For example: Select empno, ename, &&column_name from employee order by &column_name;

Q25. Describe VArray

VArray is basically an Oracle data type used to have columns containing multivalued attributes and it can hold a bounded array of values. All Varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Each element in a Varray has an index associated with it. It has a maximum size (max_size) that can be changed dynamically.

Q26. What are the attributes of the Cursor?

Each Cursor in Oracle has a set of attributes that enables an application program to test the state of the Cursor. The attributes can be used to check whether the cursor is opened or closed, found or not found and also find row count.

Q27. Name the various constraints used in Oracle

These are the following constraints used:

NULL: It is to indicate that a particular column can contain NULL values.

NOT NULL: It is to indicate that particular column cannot contain NULL values.

CHECK: Validate that values in the given column to meet the specific criteria.

DEFAULT: It is to indicate the value is assigned to a default value.

Q28. What is the fastest query method to fetch data from the table?

The fastest query method to fetch data from the table is by using the Row ID. A row can be fetched from a table by using RowID.

Q29. Difference between Cartesian Join and Cross Join?

There are no such differences between these Joins. Cartesian and Cross join are the same.

Cross join gives a cartesian product of two tables i.e., the rows from the first table is multiplied with another table that is called cartesian product.

Cross join without the where clause gives a Cartesian product.

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

Using this On Delete Cascade you can automatically delete a record in the child table when the same record is deleted from the parent table. This statement can be used with Foreign Keys as well.

You can add this On Delete Cascade option on an existing table.

Syntax:

Alter Table Child_T1 ADD Constraint Child_Parent_FK References Parent_T1(Column1) ON DELETE CASCADE;

Now let’s move on to the next part of this Oracle Interview Questions article.

What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate, and control data.

There are 5 different types of SQL statements.

Data Retrieval: SELECT

Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE

Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.

Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT

Data Manipulation Language (DCL): GRANT, REVOKE

FAQ

What is the use of Oracle 10g?

What are the three 3 major components of Oracle database?

• List the various components of the physical database structure of an Oracle database. …
• The Oracle database has been developed using which language? …
• What do you understand about the logical storage structure of Oracle? …
• Define a tablespace in context with the Oracle database.