Saturday, March 06, 2010

SQL Interview Questions

1.What are the group functions describe about that?

A group functions:---
---------------

SUM, MAX, MIN, AVG, COUNT

SQL> select sum(sal) from emp;

SQL> select sum(sal),max(comm) from emp;

SQL> select count(*) from emp where deptno=10;

SQL> select count(comm) from emp;

SQL> select count(*) -count(comm) from emp;

2.what are the single row functions describe about that?

A. Single row functions (Scalar functions):--
---------------------------------------
Here result is displayed for each and every row returned by the query.

a. numberic functions
b. character functions
c. date functions
d. conversion functions
e. miscellaneous functions

3.what are the numeric functions describe about that?

A.Greatest SQL>select greatest(10,20,30) from dual; ans:- 30

least SQL>select least(10,20,30) from dual; ans:- 10

round SQL>select round(15.375,2) from dual; ans:- 15.38
SQL>select round(15.375,-1) from dual; ans:- 20

trunc SQL>select trunc(15.375,2) from dual; ans:- 15.37

4.what are the character functions describe about that?
A.initcap SQL>select initcap('hello') from dual; ans:- Hello

SQL>select initcap(ename) from emp;

upper SQL>select upper('hello') from dual; ans:- HELLO

SQL>select * from emp where upper(ename)='SMITH';

lower SQL>select lower('HELLO') from dual; ans:- hello
ltrim SQL> select ltrim('xyzx','zx') from dual; ans:- yzx





5.what about Date Functions Explain about that?

A. add_months:- SQL> select add_months(sysdate,2) from dual; ans:- (+ 2 months)

months_between:- SQL> select months_between(sysdate,hiredate)/12
EXPERIENCE from emp;

last_day:- SQL> select last_day(sysdate) from dual;

next_day SQ:-L> select next_day(sysdate,'sun') from dual;

(next sunday from sysdate will be displayed)

6.what are the conversion functions Explain about that?

A. Conversion functions:---
-------------------

To_char, To_date, To_number

SQL> select to_char(sysdate,'ddth "of" month yyyy') from dual;

25th of august 2004

7.what are the Miscellaneous functions Explain about that?

A. Miscellaneous functions:--
-----------------------

UID, USER, NVL, VSIZE, DECODE

SQL> select UID from dual;

SQL> select USER from dual;

SQL> select sal,comm,sal+nvl(comm,0) from emp;

8.HOw many types of joins are there and explain about them?

A. JOIN:--
----

1. Simple Join (equi join and nonequi join)

2. Self Join

3. Outer Join

9.what is the simple join explain about it?

A. SQL> select * from emp,dept where emp.deptno = dept.deptno;




10.what is the self join explain about it?

A. Q. display employees earning more than empno 7934

SQL> select Y.* from emp X,emp Y where X.empno = 7934 and X.sal < Y.sal; 11.what is the outjoin explain it ? A. Q. Include deptno 40 (where there are no employees) while joining with emp table with dept SQL> select ename,dept.deptno,dname,loc from emp, dept where
emp.deptno(+)=dept.deptno;

12.How many types of set operator explain about them?

A. SET OPERATORS:--
-------------
UNION , UNION ALL ,INTERSECT , MINUS

13.what is the diffrence between Union and Union All?

A. UNION:-
-----

SQL> select job from emp where deptno =10
UNION
select job from emp where deptno =20;

ANS. ANALYST
CLERK
MANAGER
PRESIDENT

UNION ALL:-
-----

SQL> select job from emp where deptno =10
UNION ALL
select job from emp where deptno =20;

ANS.
MANAGER
PRESIDENT
CLERK
CLERK
MANAGER
ANALYST
CLERK
ANALYST




14.what is the view and describe the syntax of view?

A. VIEWS:-
-----
It is a stored query. doesnot contain any record and is a database object

Syntax:-
-----
SQL> Create view as