Important Topics

 Inline Query: An inline query is a query that is defined in the FROM clause. Inline views can contain multiple columns.

Sub Query: A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. Subqueries (in the Oracle meaning) should return only one.

1. In which language has Oracle been developed?

Oracle has been developed using C Language.

2. What is the Difference between varchar and varchar2 data types?

Varchar can store up to 2000 bytes, and varchar2 can store up to 32672 bytes. Varchar will occupy space for NULL values, and Varchar2 will not occupy any space. Both differ with respect to space.

 3. What is a RAW datatype?

RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.

4. What is the use of the NVL and NVL2 functions?

The NVL function replaces NULL values with another or given value.

The NVL2 function will take 3 arguments NVL2('A', 'B',' C' ). It will check the first value. If it is not null, then it will return the second value. If it is null, then it will return the Third value.

NULLIF function takes two argument values. If both are exactly the same, then it will return a NULL value. If both values are not the same, then it will return the first value. If the first value is null, then it will return an error. If the second argument is null, then it will return the First value.

5. What is COALESCE function?

The coalesce function takes any number of argument values and will return the first, not the null value. COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.

Coalesce(value1, value2,value3,…)

6. What is DML?
Data Manipulation Language (DML) is used to access and manipulate data in the existing objects.  DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.
7. What is the difference between TRANSLATE and REPLACE?
Translate is used for character by character substitution and Replace is used substitute a single character with a word.
8. What is the usage of Merge Statement?
Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations. Actually merge is the combination of two DML statement Insert & Update.
Error Log table create Package:
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name=>'EMPLOYEE_SALARY_PROCESS');
END;
9. What is a sub query and what are the different types of subqueries?
Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.
There are two different types of subqueries:
Correlated sub query
A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.
Non-Correlated subquery
This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.
10. What is cross join?
Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table. 
11. What are temporal data types in Oracle?
Oracle provides following temporal data types:
Date Data Type – Different formats of Dates
TimeStamp Data Type – Different formats of Time Stamp
Interval Data Type – Interval between dates and time
12. How do we create privileges in Oracle?
A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]
13. What is VArray?
Description the varray (variable size array) is one of the three types of collections in PL/SQL (assosiated array, nested table, varray). VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.
Collection: A collection is an ordered group of logically realated elements. In a collection the internal component always have the same datatype, and are called elements.
Records: In a records the internal components always have different data types, and are called fields.
14. How do we get field details of a table?
Describe <Table_Name> is used to get the field details of a specified table.
15. What is the difference between rename and alias?
Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.
16. What is a View?
View is a logical table based on other base tables or views. Views will not have data of his own,It has typically  fetch data of his underlying base table or view. Views contains no data itself (except materialized view). There are two types of view .
1. Simple view : Simple view created based on single table without having aggregrate column,Distinct keyword. DML operation is allowed in simple view.
2. Complex view: Complex view created based on any number of tables. It can contain aggregrate column,distinct keyword or pseudocolumn. No DML operation allowed.
By using WITH READ ONLY constraints we are not able to operation any DML on the view.
Syntex: create or replace view v_emp as select * from emp with read only;
By using WITH CHECK OPTION CONSTRAINTS we can restrict a view to process the dml operation. Suppose I have create a view based on emp table where department is CSE and I want only CSE department's data will be changed. For this we can create view with check option.
Syntex: create or replace view v_emp as select * from emp where department='CSE' with check option;
View recompile : If view is going under Invalid status then need to recompile view.There are three way to compile view .one is -Alter view view_name compile; another one is to recreate view. Beside this if we call the view then it will try to compile view.

Different between View and Materialized view :
View is just a named query. It soesn't store anything. when there is a query on view, it runs the of the view defination. Actually data comes form table. no need to refresh data.
Materialized view stores data physically and get updated periodically. While querying MV, it gives data from MV. Need to refresh seperately.
17. What are SET operators?
SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.

 Union : UNION operator is used to combine the result sets of 2 or more Oracle SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.Union will remove duplicate and sort the data.

Union ALL: If you want to retain the duplicate rows, you explicitly use UNION ALL.Union will not remove duplicate and will not sort the data.
Intersect :The intersect of T1 and T2 result returns 2 and 3. Because these are distinct values that are output by both queries. The following picture illustrates the intersection of T1 and T2:
The illustration showed that the INTERSECT returns the intersection of two circles (or sets).
Minus : The Oracle MINUS operator compares two queries and returns distinct rows from the first query that are not output by the second query. In other words, the MINUS operator subtracts one result set from another.
The following picture illustrates the result of the MINUS of T1 and T2:

18. How can we delete duplicate rows in a table?
Duplicate rows in the table can be deleted by using ROWID.
19. What is an integrity constraint?
An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.
20. What are the various constraints used in Oracle?
Following are constraints used:
NULL – It is to indicate that 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 default value
21. What is difference between SUBSTR and INSTR?
SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.
SUBSTR returns string whereas INSTR returns numeric.
22. What is the parameter mode that can be passed to a procedure?
IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.
23. What are the different Oracle Database objects?
There are different data objects in Oracle –
Tables – set of elements organized in vertical and horizontal
Views  – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespaces – Logical storage unit in Oracle
24. What are the differences between LOV and List Item?
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
25. What is the maximum number of triggers that can be applied to a single table?
A trigger is a PL/SQL block structure which is automatically fired when an event occurs in the database. 12 is the maximum number of triggers that can be applied to a single table.
There ar 5 types of trigger.
1.DML Triggger 
2.DDL Trigger 
3. SYSTEM Trigger 
4.Insted of trigger 
5. Compound Trigger
There are 2 types of DML trigger in oracle. 
1.Row level Trigger
    1.Insert (Before,After)
    2.Update (Before After)
    3.Delete (Before, After)
2.Statement Level trigger:
    1.Insert (Before,After)
    2.Update (Before After)
    3.Delete (Before, After)
Trigger Execution Order:
    1.Before Statement level trigger
    2.Before Row level trigger
    3.After Row level trigger
    4.After Statement Level trigger
26. What is the data type of DUAL table?
The DUAL table is a one-column table present in oracle database.  The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.
27. What is difference between Cartesian Join and Cross Join?
There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.
Cross join without where clause gives Cartesian product.
28. 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.
29. 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.
30. 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)
31. What are the data types available in PL/SQL?
There are two data types available in PL/SQL. They are namely:
Scalar data types
Example: Char, Varchar, Boolean, etc.
Composite datatypes
Example: Record, table etc.
32. What are the uses of a database trigger
Triggers are the programs which are automatically executed when some events occur:
Implement complex security authorizations.
Drive column values.
Maintain duplicate tables.
Implement complex business rules.
Bring transparency in log events. 
33. Explain the difference between Triggers and Constraints?
Triggers are very different from Constraints in the following ways:
Triggers
Constraints
Only affect those rows added after the
 trigger is enabled
Affect all rows of the table including that 
already exist when the constraint is enabled
Triggers are used to implement complex
 business rules which cannot be
 implemented using integrity constraints
Constraints maintain the integrity of the
 database
34. Exception handling in PL/SQL
An error is an abnormal termination during a normal execution of the program. When an error occurs in PL/SQL, the corresponding exception is raised. This also means, to handle undesired situations where PL/SQL scripts gets terminated unexpectedly, error-handling code is included in the program. In PL/SQL, all exception handling code is placed in the Exception section.
There are 3 types of Exceptions:
Predefined Exceptions: Common errors with predefined names.
Undefined Exceptions: Less common errors with no predefined names.
User-defined Exceptions: Do not cause runtime error but violate business rules.
35.  What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)?
COUNT (*): This returns a number of rows in a table including the duplicates rows and the rows containing null values in the columns.
COUNT (EXP): This returns the number of non-null values in the column identified by an expression.
COUNT (DISTINCT EXP): It returns the number of unique, non-null values in the column identified by an expression.
36. List out the difference between Commit, Rollback, and Savepoint?
The major differences between these are listed below:
Commit: This ends the current transaction by ensuring that all pending data changes are made permanent.
Rollback: This ends the current transaction by discarding or deleting all pending data changes.
Savepoint: It divides a transaction into smaller parts. You can rollback the transaction until you find a particular named savepoint.
37. How will you differentiate between VARCHAR & VARCHAR2?
Answer: Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length. Their differences are:
VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.
38.  What is the difference between TRUNCATE & DELETE command?
Answer: Both the commands are used to remove data from the database.
The difference between the two include:
TRUNCATE is a DDL operation while DELETE is a DML operation.
TRUNCATE  removes all the rows but leaves the table structure intact. It can not be rolled back as it issues COMMIT before and after the command execution while the DELETE command can be rolled back.
The TRUNCATE command will free the object storage space while the DELETE command does not.
TRUNCATE is faster compared to DELETE.
39. What is meant by Joins? List the types of Joins.
Answer: Joins are used to extract data from multiple tables using some common columns or conditions.
There are various types of Joins as listed below:
INNER JOIN
OUTER JOIN
CROSS JOINS or CARTESIAN PRODUCT
EQUI JOIN
ANTI JOIN
SEMI JOIN
40. How does the ON-DELETE-CASCADE statement work?
Answer: Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.
We can add ON DELETE CASCADE option on an existing table using the below set of commands.
41. What is a trigger and what are its types?
Answer: A trigger is a stored program which is written in such a way that it gets executed automatically when some event occurs. This event can be any DML or a DDL operation.
PL/SQL supports two types of triggers:
Row Level
Statement Level


 

  

  
 Row Level Triggers
  
  

  
 Statement Level Triggers
  
 
 

  

  
Row level triggers executes once 
for each and every row in the
transaction.
  
  
Statement level triggers executes
only once for each single
transaction. 
  
 
 

  

  
Specifically used for data
auditing purpose.
  
  
Used for enforcing all additional
security on the transactions
performed on the table. 
  
 
 

  

  
“FOR EACH ROW” clause is
present in CREATE TRIGGER
command.
  
  

  
“FOR EACH ROW” clause is omitted in
CREATE TRIGGER command.
  
 
 

  

  
Example: If 1500 rows are to be
inserted into a table, the row
level trigger would execute 1500
times.
  
  
Example: If 1500 rows are to be
inserted into a table, the
statement level trigger would
execute only once. 
  
 


42. How will you distinguish a global variable with a local variable in PL/SQL?
Answer: Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared.
42. 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.
PL/SQL Package structure consists of 2 parts: package specification & package body.
PL/SQL Package Benefits: Summary
This article lists key benefits provided by PL/SQL packages for PL/SQL subprograms over standalone procedures and functions. These are:
Organized code management
Easy (top-down) application design
Easy changes to implementation
Security and maintainability
Session-wide persistence of variables
Better berformance
Overloading