SQL Questions and Answers

Difference between Store Procedure and Trigger?

* we can call stored procedure explicitly.
* but trigger is automatically invoked when the action defined in trigger is done.
ex: create trigger after Insert on
* this trigger invoked after we insert something on that table.
* Stored procedure can't be inactive but trigger can be Inactive.
* Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.

What is the advantage to use trigger in your PL?

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

* Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
* Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
* Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
* Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.


What the difference between UNION and UNIONALL?

Union will remove the duplicate rows from the result set while Union all does'nt.

What is the difference between TRUNCATE and DELETE commands?

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.

Explain normalization ?
Normalization means refining the redundancy and maintain stabilization. there are four types of normalization :
first normal forms, second normal forms, third normal forms and fourth Normal forms.

How to find out the database name from SQL*PLUS command prompt?
Select * from global_name;
This will give the database name which u r currently connected to.

What is difference between Co-related sub query and nested sub query?

Correlated subquery are used for row-by-row processing.Each subquery is executed once for every row of the outer query.It is one way of reading every row in a table and
comparing the values in each row against the related data.

Example:
select e1.emp_name, e1.emp_salary, e1.emp_dept from employee e1 where e1.emp_salary
= (select max(emp_salary) from employee e2 where e2.emp_dept = e1.emp_dept)

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
It is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query
Example:
SELECT Model FROM Product
WHERE ManufacturerID IN (SELECT ManufacturerID FROM Manufacturer
WHERE Manufacturer = 'IBM')

The nested query above will select all models from the Product table manufactured by IBM.