Sql Question and answer 4

1. When you use WHERE clause and when you use HAVING clause

HAVING clause is used when you want to specify a condition for a group function and it
is written after GROUP BY clause

The WHERE clause is used when you want to specify a condition for columns, single
row functions except group functions and it is written before GROUP BY clause if it is
used.

2. Which is more faster - IN or EXISTS

EXISTS is more faster than IN because EXISTS returns
a Boolean value whereas IN returns a value.

3. What is a OUTER JOIN

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

4. How you will avoid your query from using indexes

SELECT * FROM emp
Where emp_no+' '=12345;

i.e you have to concatenate the column name with
space within codes in the where condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp
where emp_no=1234;
i.e using HINTS

5. What is a pseudo column. Give some examples

It is a column that is not an actual column in the
table.

eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL