SQL,PL/SQL Interview Questions part-1

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 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.