What is a JOIN? Explain types of JOIN in oracle.

A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.

Types of JOINS:

Simple JOIN

SELECT p.last_name, t.deptName FROM person p, dept t WHERE p.id = t.id;

Find name and department name of students who have been allotted a department

Inner/Equi/Natural JOIN

SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid

Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.

Outer Join

SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid

It includes non matching rows also unlike Inner Join.

Self JOIN

SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber

Joining a Table to itself.

Read More

What are cascading triggers?

A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers. Here’s the order of execution of statements in case of cascading triggers:

• Execute all BEFORE statement triggers that apply to the current statement.
• Loop for each row affected statement.
• Execute all BEFORE row triggers that apply to the current statement in the loop.
• Lock and change row, perform integrity constraints check; release lock.
• Execute all AFTER row triggers that apply to the current statement.
• Execute all AFTER statement triggers that apply to the current statement.

Read More

Advantage of a stored procedure over a database trigger.

Stored procedures can accept parameters and can return values. Triggers can neither accept parameters nor return values. A Trigger is dependent on a table and the application has no control to not fire a trigger when not needed. On the other hand, a stored procedure can be called as needed.

Read More

Write a PL/SQL program for a trigger

PL/SQL program for tracking operation on a emp table.
Create or Replace Trigger EmpTracking
Before Insert or Delete or Update on Emp
For each row
Declare
Begin
          If Inserting then
               /*Can perform operations just before record is inserted into the emp table*/
          Else if Updating then
              /*Can perform operations just before record is about to be updated*/
          Else if Deleting then
               /*Can perform operations just before record is about to be deleted*/
           End If
End EmpTracking

Read More

Row level and statement level trigger

A trigger if specified FOR EACH ROW; it is fired for each of the table being affected by the triggering statement. For example if a trigger needs to be fired when rows of a table are deleted, it will be fired as many times the rows are deleted.
If FOR EACH ROW is not specified, it is application to a statement and the trigger is executed at a statement level.


Row Level Trigger
Row Level Trigger is fired each time row is affected by Insert, Update or Delete command. If statement doesn’t affect any row, no trigger action happens.
Statement Level Trigger
This kind of trigger fires when a SQL statement affects the rows of the table. The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement.

Read More

Difference between trigger and stored procedure

• A stored procedure can accept parameters while a trigger cannot.
• A trigger can’t return any value while stored procedures can.
• A trigger is executed automatically on some event while a stored procedure needs to be explicitly called.
• Triggers are used for insertions, update and deletions on tables while stored procedures are often using independently in the database.
• A trigger cannot be written in a stored procedure. However, the reverse is not possible.

Read More

What do you mean by Redo Log file mirroring ?

- The process of having a copy of redo log files is called mirroring.
- It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group.
- In case a group fails, the database automatically switches over to the next group. It diminishes the performance.

Read More

What is hot backup and logical backup?

Hot backup
- Backing up the archive log files when database is open is called Hot backup.
- To do this, the ARCHIVELOG mode is enabled.
- Following files are backed up - All data files, Archive log, redo log files and control files.

Logical backup
- Logical back ip is reading a set of database records and writing them into a file.
- An Export utility is required to take the backup while an Import utility is required to recover from the backup.

Read More

What is the purpose of Save Points in Oracle database?

- Save Points are used to divide a transaction into smaller phases.
- It enables rolling back part of a transaction.
- Maximum 5 save points are allowed in Oracle Database.
- Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

Read More

Differentiate between: a.) TRANSLATE and REPLACE. b.) What is Merge Statement used for?

a.) TRANSLATE and REPLACE.

- Translate is used to substitute character by character.
- Replace is used to substitute a single character with a word.
 b.) What is Merge Statement used for?

- Merge statement is used to select rows from one or more data source to updating and insert into a table or a view.

Read More

What are the various Oracle Database objects?

Various database objects are as follows:

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

Read More

Explain the following: a.) BLOB datatype. b.) DML.

a.) BLOB datatype.

- It is a data type with varying length binary string, used to store two gigabytes memory.
- For BLOB, the length needs to be specified in bytes.

b.) DML.

- DML - it is also termed as Data Manipulation Language (DML).
- It is used to access and manipulate data in the existing objects.
- DML statements are insert, select, update and delete.

Read More

Explain: a.) Integrity constraint. b.) COALESCE function

a.) Integrity constraint.
- It is a declaration defined in a business rule for a table column.
- Integrity constraint ensures the accuracy and consistency of data in a database.
- It is of three types - namely - Domain Integrity, Referential Integrity and Domain Integrity.





b.) COALESCE function

- This function is used to return the value which is set not to be null in the list.
- Incase all values in the list are null the coalesce function will return NULL.
- Its representation:
Coalesce(value1, value2, value3,…)

Read More

Will you be able to store pictures in the database?Explain.

- Yes, pictures can be stored in the database using Long Raw Data type.
- This datatype is used to store binary data for 2 gigabytes of length.
- However, the table can have only one Long Raw data type.

Read More

What is a sub query? What are its various types?

- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.

There can be two types of subqueries:

a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .

b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query

Read More

What suggestions do you have to reduce the network traffic?

Following are some of the actions which can be taken to reduce the network traffic:

- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.

Read More

What do you mean by a deadlock?

- When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.

Read More

What is a synonym? What are its various types?

synonym can be called as an alias for a table, view, sequence or program unit. It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.

Read More

a.) What do you mean by a tablespace?

- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.

Read More

List out the components of logical database structure of Oracle database.

- Tablespaces
- Database's schema objects.

Read More