EXPERIMENT NO6

Document Sample

```					         EXPERIMENT NO.6

Name:                      Roll No.:

Batch:                     Date:

Q-1> Find the ASCII destination of alphabet ‘A’?
Answer: SQL> select ascii(‘A’) from dual;

Q-2> Find the remainder of any number?
Answer: SQL> select mod(15,4) from dual;

Q-3> Show all employees which have salary greater than 1500 and above it all are displayed as
1500?
Answer: SQL> select empno,ename,greatest(sal,1500) from emp;

Q-4> List all the employee having salaries below 2000 are displayed and above it all are
displayed as 2000?
Answer: SQL> select empno,ename,least(sal,2000) from emp;

Q-5> Round any particular no. to two decimal places?
Answer: SQL> select round(442.23453) from dual;

Q-6> Truncate any particular no. to two decimal places?
Answer: SQL> select round(422.23453,2) from dual;

Q-7> Concatenate two column names department name and location to form one column and
name it as dept?
Answer: SQL> select dname||loc as department from dept;

Q-8> Translate the value of department no into ten,twenty,thirty?
Answer: SQL> select deptno,decode(deptno,10,’TEN’,20,’TWENTY’,30,’THIRTY’) as decode
from dept;

Q-9> Determine which department have more than two people holding a particular job?
Answer: SQL> select deptno from emp group by deptno having count(job)>2;

Q-10> Find all department that have atleast two clerk?
Answer: SQL> select deptno from emp where jod like ‘clerk’ group by deptno having
count(job)>2;

Q-11> Divide all employee into group dept and job and also specify deptno?
Answer: SQL> select deptno,count(ename),count(job) from emp group by deptno;

Q-12> List annual salary from all job consisting of more than one employee?
Answer: SQL> select job,count(*),12*sal as annual from emp group by job,12*sal having
count(*)>1;

Q-13> List all dept with atleastane analyst?
Answer: SQL> select deptno from emp where job like ‘ANALYST” group by deptno having
count(job)>1 or count(job)=1;
Q-14> List the job that have average annual salary greater than all manager?
Answer: SQL> select job,avg(sal*12) as annual from emp group by job having
avg(sal*12)>all(select avg(sal*12) from emp where job like ‘MANAGER’);

Q-15> Find the no. employees and job group by deptno.?
Answer: SQL> select deptno,count(ename),count(job) from emp group by deptno;

Q-16> Find the location number of character ‘A’ in employee table?
Answer: SQL> select instr(ename,’A’) from emp;

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 11 posted: 6/22/2012 language: pages: 3
Description: DBMS SQL QUERIES 10 EXPERIMENTS
How are you planning on using Docstoc?