SQL
QUERYS
* Do not
refer the serial numbers of querys
---------------------
1
select * from emp;
2 select empno,ename,job,hiredate,deptno from emp where deptno=10;
3 select
hiredate,empno,ename,job from emp where deptno=10;
4 select
ename,job from emp where job='CLERK';
5 select
ename ,job ,sal ,hiredate from emp where hiredate='17-dec-81';
6 select
ename,hiredate from emp where hiredate>'1-july-81';
7 select
dname,deptno,loc from dept where loc='BOSTON';
8 select
ename,sal,comm from emp where comm> sal;
9 select
*from emp order by empno;
10 select
* from emp order by deptno;
11 select
ename,deptno,job from emp where job='CLERK';
12 select
ename,deptno,job from emp
where job in ('ANALYST','MANAGER');
13 select
ename from emp where ename like '%R';
14 select
ename from emp where ename like 'S%' or ename like 'T%';
15 select
ename from emp where length(ename)=4;
16 select
ename, job from emp where job not in('CLERK');
OR select ename, job from emp where not job ='CLERK';
17 select ename , comm from emp where comm is not null;
18 select
ename,sal,(12*sal)*.011 "bonus" from emp;
19 select
ename,deptno from emp where deptno=10;
20 select
ename,hiredate from emp where hiredate>'2-april-81';
21 select
ename ,comm from emp where comm is not null;
22 select
ename from emp where ename like'%TH';
23 select
ename, sal,comm,sal+comm "compensation" from emp ;
24 select
ename,sal*12 from emp ;
25 select
ename, sal from emp where sal > 2000;
26 select
ename,sal from emp where sal between 1500 and 2000;
27 select
upper(ename),lower(ename),initcap(ename) from emp;
28 select
dname,loc,dname||'-'||loc from dept;
29 select Lpad(sal,7,'$') from emp;
30 select
empno "eno",ename "enm",sal "salary",comm "com",
deptno "dno",hiredate "hdate",job "post",
mgr "manager" from emp where job='ANALYST';
31 select
sal/30 "salary",round(sal/30,2) "roundsal" from emp;
32 select
ename,job, deptno from emp
where job='MANAGER' and not deptno =10;
33 select count(ename) from emp where comm is not null;
34 select
count(ename) from emp
where deptno=30 and sal is not null and comm is not null;
35 select
ename,comm from emp where comm=0;
36 select
ename,comm from emp order by comm desc;
37 select
e.ename,e.mgr,s.ename
from emp e, emp s
where e.mgr=s.empno and e.ename='BLAKE';
38 select
max(a.sal),min(b.sal), max(a.sal) -min(b.sal) "difference"
from emp a, emp b
where a.deptno=10 and b.deptno=20 ;
39 select
avg(a.sal),avg(b.sal),avg(a.sal)-avg(b.sal) "difference"
from emp a,emp b
where a.deptno=30 and b.deptno=20;
40 select
e.empno,e.ename,e.job,s.ename,s.job
from emp e,emp s
where e.mgr=s.empno;
41 select
avg(sal),max(sal),min(sal),deptno from emp
group by deptno
having deptno in(10,20);
42 select
avg(a.sal),avg(b.sal),avg(a.sal)-avg(b.sal) "Difference"
from emp a ,emp b
where a.job='MANAGER' and b.job='ANALYST';
43 select
ename,sal from emp
where sal > (select max(sal) from emp where job='CLERK');
44 select
ename,sal from emp
where sal< (select avg(sal) from emp where deptno=20);
45 select
ename,comm from emp
where comm > sal*o.o5;
46 select
ename,sal from emp
where sal < (select sal * 0.3 from emp where job='PRESIDENT');
47
48 select
deptno,count(ename) from emp
group by deptno having count(ename)=0;
49
50 select
max(sal) from emp where sal not in(select max(sal) from emp);
51 select
ename from emp where sal=(select max(sal) from emp where sal not i
70.SELECT
EMP.EMPNO,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
71.SELECT
EMP.ENAME,DEPT.LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC IN('DALLAS','NEW
YORK');
72.SELECT
EMPNO ||' '|| ENAME ||' '||JOB ||' '||MGR FROM EMP;
73.SELECT
INITCAP(ENAME) || ' - ' || INITCAP(JOB) FROM EMP WHERE DEPTNO=20;
74.SELECT
DNAME,LENGTH(DNAME) FROM DEPT;
75.SELECT
LAST_DATE('12-JAN-91') FROM DUAL;
76.SELECT
TO_CHAR(SYSDATE,'HH-MM-SS') FROM DUAL;
77.SELECT
TO_CHAR(LAST_DAY(SYSDATE),'DD') FROM DUAL;
78.SELECT
DISTINCT(LENGTH(ENAME)) FROM EMP;
79.SELECT
MAX(LENGTH(ENAME)),MAX(LENGTH(ENAME))- MIN(LENGTH(ENAME)) - 1" NEREST CHAR
LEN DIFF " FROM EMP;
80.SELECT
MAX(SAL),MIN(SAL),MAX(SAL) - MIN(SAL)" DIFFERENCE " FROM EMP;
81.SELECT
MAX(LENGTH(ENAME)) FROM EMP;
*82.SELECT
(COUNT(SAL)*12000) FROM EMP WHERE COMM IS NULL GROUP BY DEPTNO;
SELECT NVL(COMM,1000) FROM EMP;
83.SELECT
MIN(HIREDATE)"EARLIST EMP",MAX(HIREDATE)" LATEST EMP" FROM
EMP;
84.SELECT
HIREDATE,TO_CHAR(HIREDATE,'DD/MM/YY') " HIREDATE "FROM EMP WHERE DEPTNO=20;
85.SELECT
ROUND(MONTHS_BETWEEN(HIREDATE,SYSDATE),0) "PRESIDENT'S TOTAL WORK"FROM
EMP WHERE JOB='PRESIDENT';
86.SELECT
ENAME FROM EMP WHERE (TO_CHAR(HIREDATE,'MON'))='DEC';
*87.SELECT
MAX(COUNT(ENAME)) FROM EMP
88.SELECT
ENAME FROM EMP WHERE (TO_CHAR(HIREDATE,'RRRR'))=2080;
89.SELECT
ENAME FROM EMP WHERE (TO_CHAR(HIREDATE,'RRRR')) BETWEEN 2090 AND 2095;
90.SELECT
TO_CHAR(HIREDATE,'D')FROM EMP WHERE ENAME='SMITH';
91.SELECT
TO_CHAR(HIREDATE,'DD') FROM EMP WHERE ENAME='JAMES';
92.SELECT
TO_CHAR(HIREDATE,'MON') FROM EMP WHERE ENAME='ADAMS';
93.SELECT
TO_CHAR(HIREDATE,'DD MONTH RRRR') FROM EMP WHERE JOB='ANALYST';
94.SELECT
ENAME,JOB,DECODE(RTRIM(JOB),'CLERK',1,'MANAGER',3,'PRESIDENT',4,2)"TO JOB
CLASSIFICTION " FROM EMP;
95.SELECT
COUNT(JOB) FROM EMP WHERE JOB='ANALYST';
96.SELECT
DISTINCT(JOB) FROM EMP;
97.SELECT
COUNT(EMPNO) FROM EMP WHERE COMM IS NOT NULL;
98.SELECT
ENAME,ROUND(MONTHS_BETWEEN(HIREDATE,SYSDATE),0)"TOTAL MONTHS SERVICE"
FROM EMP;
99.SELECT
NEXT_DAY(SYSDATE) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'DY') FROM DUAL;
*100.SELECT
SAL,COMM,iff(SAL,comm,sal > COMM) FROM EMP;
SELECT DECODE(SAL,'>',1,COMM,2,COMM) FROM EMP;
*101.SELECT
SAL,COMM,IFF(SAL,COMM,SAL > COMM) FROM EMP;
