Oracle Interview Questions and Answers Pdf

31. Explain all Joins used in Oracle 8i? 
Answer: Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. Forex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.

Non-Equi Joins A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=). The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.

Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information (Top 50 Oracle Interview Questions and Answers pdf). The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator

32. Explain all Joins used in Oracle 9i and later release? 
Answer: Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.

Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, then the Natural Join syntax causes an error.

Join with the USING clause: If several columns have the same names but the data types do not match, then the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.

Forex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where loc_id=10;

Joins with the ON clause: Use the ON clause to specify a join condition. The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.

Left/ Right/ Full Outer Joins: Left Outer Join displays all rows from the table that is Left to the LEFT OUTER JOIN clause, right outer join displays all rows from the table that is right to the RIGHT OUTER JOIN clause, and full outer join displays all rows from both the tables either left or right to the FULL OUTER JOIN clause

33. What is a DEFAULT option in a table? 
Answer: A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.

34. In what scenario you can modify a column in a table? 
Answer: During modifying a column:

• You can increase the width or precision of a numeric column.
• You can increase the width of numeric or character columns.
• You can decrease the width of a column only if the column contains null values or if the table has no rows.
• You can change the data type only if the column contains null values (Top 50 Oracle Interview Questions and Answers pdf).
• You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.

35. Describe types of Constraints in brief? 
Answer: NOT NULL: NOT NULL Constraint ensures that the column contains no null values.

UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates an index on the column it is created.

PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.

FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of the same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.

CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:

1. References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM Pseudo columns.
2. Calls to SYSDATE, UID, USER and USERENV Functions

36. What is PL/SQL? 
Answer: PL/SQL is a procedural language extension over SQL provided by Oracle. It facilitates the declaration of variables, functions, and conditional operators in SQL syntax thereby giving the developer more freedom and ease to design complex queries. 

37. What is dynamic SQL? When to use dynamic SQL? 
Answer: Dynamic SQL is an enhancement over static SQL. It enables writing SQL queries at run-time. It comes into picture when we need to customize SQL queries during execution

Few common cases where Dynamic SQL can be utilized:

• If database objects do not exist at compile-time, we can use dynamic SQL to reference them.
• Certain DDL (Data Definition Language) statements and SCL (Session Control Language) Statements are not supported by Static SQL in PL/SQL. These statements can be executed by via dynamic SQL programming.
• Dynamic SQL can be used for executing Dynamic PL/SQL block. By using EXECUTE IMMEDIATE clause, PL/SQL calls can be determined at runtime.

38. What are the packages in PL SQL? 
Answer: A Package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.

39. What is WITH CHECK OPTION? 
Answer: The WITH CHECK option clause specifies the level of check to be done in DML statements. It aids in preventing changes to a view that would produce results not contained in the subquery.

40. Explain Oracle Grid Architecture? 
Answer: The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes the demand for resources and adjusts supply accordingly.

For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.
Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.