Sql Question and answer 1

1. Difference between group functions and single row functions.

Group Function Single Row Function
A group function operates A single row function
on many rows returns one and result for one row.
returns single result.

Not allowed in Pl/sql procedural Allowed in Pl/Sql
Procedural statements
statements.
eg SUM(),AVG,MIN,MAX etc eg UPPER,LOWER,CHR...



2. Difference between DECODE and TRANSLATE

DECODE is value by value TRANSLATE is character by
character replacement. replacement.
Ex SELECT DECODE('ABC','A',1,'B',2,'ABC',3) eg SELECT
from dual; o/p 3 TRANSLATE('ABCGH',
'ABCDEFGHIJ', 1234567899)
FROM DUAL; o/p 12378

(DECODE command is used to bring IF,THEN,ELSE logic to SQL.It tests for the IF values(s) and then aplies THEN value(s) when true, the ELSE value(s) if not.)


3. Difference between TRUNCATE and DELETE

TRUNCATE deletes much faster than DELETE

Truncate Delete
It is a DDL statement It is a DML statement
It is a one way trip,cannot One can Rollback
ROLLBACK
Doesn't have selective features (where clause) Has
Doesn't fire database triggers Does
It requires disabling of referential Does not require
constraints.


4. What is a CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation
name as table or view designator in the FROM clause of the outer
query and the same correlation name as a qualifier of a search
condition in the WHERE clause of the subquery.

eg
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y
where
field1=X.field1);

(The subquery in a correlated subquery is revaluated
for every row of the table or view named in the outer query.)


5. What are various joins used while writing SUBQUERIES

Self join-Its a join foreign key of a table references the same table.

Outer Join--Its a join condition used where One can query all the rows of one of the
tables in the join condition even though they don't satisfy the join condition.

Equi-join--Its a join condition that retrieves rows from one or more tables in which one
or more columns in one table are equal to one or more columns in the second table.


6. What are various constraints used in SQL

NULL
NOT NULL
CHECK
DEFAULT