Oracle SQL/PLSQL Questions & Answers:

Oracle SQL/PLSQL Questions & Answers:

What is SQL statement?

SQL stands for Structured Query Language that is use for updating, deleting and inserting the  information from databases. SQL is an ANSI and ISO standard and is the de-facto standard database query language. Oracle database also support this language.

What are database triggers and how many types of triggers?

Database triggers are a set of code that run or fire automatically when the specific operation will performed like Insert, Update and Delete. They are store procedures that are auto configure and fire when certain events take place in database.

There are two types of triggers

1-      Tables level trigger

2-      Row level trigger

Table level trigger are fire at one time but row level trigger fire on every row or on any transaction in the table.

What are the means of values of: new and: old in the trigger?

These two: new and: old are use in database triggers. These are basically use for referencing the trigger value by using triggers. For example we want to update anything in table and this table have database trigger on After Update then we use these two keywords for updating the values again this table.

 What is the Mutating in the trigger?

The mutating will occur when in Oracle when the table that originally fired the triggering event is being accessed in the body of the trigger code, directly or implicitly in a procedure, or in a nested trigger called from the trigger body. This is because the table is in middle of a transaction, and referencing the same table again in the middle of the updating action causes the trigger to mutate.

 Can we write a trigger for view?

Yes we can write a trigger on view by using key word “instead of” but did not use Before or After option on trigger.

 Can we create a view on another view?

Yes we can create a view on another view.

What are the advantages of view?

1-      Restrict the data and column s of the base tables.

2-      Use complex quires easy and show the data of complex quires.

3-      Make different views according to requirement on many on table.

4-      We can hide business rules by using view.

What are function and how many types of functions?

Functions or user defined function are set of SQL/PLSQL code that are store in database and return the value. In function some data is manipulating by using some statement then return a specific value.

What is the difference between row level and Table level triggers?

Table level trigger are fire at one time but row level trigger fire on every row or on any transaction in the table.

What is the difference between Database triggers and Form level triggers?

Database trigger are written in database directly and fire on behalf of any transaction like Insert, Update and delete on table automatically. On the other hand we form level trigger are use in forms and fire on any level like item level, row level or on block level on requirement of application. Main difference in database trigger and form level triggers is that database trigger fire automatically and form level trigger fire on user or application requirement.

What is the difference between BEFORE and AFTER in Database Triggers?

BEFORE triggers are usually used when validation needs to take place before accepting the change. They run before any change is made to the database. Let’s say you run a database for a bank. You have a table accounts and a table transactions. If a user makes a withdrawal from his account, you would want to make sure that the user has enough credits in his account for his withdrawal. The BEFORE trigger will allow to do that and prevent the row from being inserted in transactions if the balance in accounts is not enough.

AFTER triggers are usually used when information needs to be updated in a separate table due to a change. They run after changes have been made to the database (not necessarily committed). Let’s go back to our back example. After a successful transaction, you would want balance to be updated in the accounts table. An AFTER trigger will allow you to do exactly that.

 What is the advantage of stored procedure over the database triggers?

Store procedure are compile one time and stay in database and wait for calling but on the other hand database trigger fire every time when we have to perfume any change in database.

 What are cascading of triggers?

Cascading of trigger means if we insert data in one table and that table have trigger on it then trigger fire. And in this trigger there is another table that we are using for insert the data in it and this table has also trigger on it then this trigger also fire. This is called cascading of triggers.

 What is Pseudo column?

Pseudo columns are database columns that are using for different purposes in oracle database like ROWNUM, ROWID, SYSDATE, UID, USER, ORA_ROWSCN, SYSTIMESTAMP,

 What is Dual and where it can be use?

Dual is a database table owner is sys and reside in data dictionary. It have only one column name DUMMY and one data X. It is normally use for select the values of Pseudo columns.

 Can we insert, update, delete, alter and drop DUAL table?

Yes we can Insert, update, delete alter and also drop Dual table in oracle.

 If we create a table with name DUAL it can be work same as DUAL?

Yes we create table with same name of DUAL and also it will work like same of dual table.

What are group functions and how many types of group function?

Group functions return the single result on based on many rows. Like count, sum, min, max, avg first, last these functions return one row result based on many rows.


Other Related Questions & Answers: