SQL QUERIES
1Q) DISPLAY ALL GROUP
FUNCTIONS OUTPUT ON SAL COLUMN?
SELECT
MIN(SAL),MAX(SAL),COUNT(SAL),AVG(SAL),SUM(SAL)
FROM
EMP;
2Q) DISPLAY THOSE
EMPLOYEES WHOSE SALARY IS EVEN NO.?
SELECT *
FROM
EMP
WHERE MOD(SAL,2)=0;
3Q) DISPLAY THOSE
EMPLOYEES WHOSE SALARY KEEPS DECIMAL DIGIT?
select *
from emp
where
instr(sal,'.',1,1)> 0;
4Q) DISPLAY 3% OF
SALARY UPTO TWO DECIMAL DIGIT?
SELECT
ROUND(SAL*0.03,2)
FROM
EMP;
5Q) Display the employee whose name have the
char ‘A’?
Select ename
From emp
Where ename like ‘%A%’;
6Q) Display the name in upper case, and job in
lower case?
From emp;
7Q) Display the employee name ,job, and annual
salary?
Select ename ,job,sal*12
From emp;
8Q) Display the different type of job in
emptable ?
Select distinct(job)
From emp;
9Q) Display the employee whose comm is more
than 25% of his salary?
Select *
From emp
Where comm>sal*0.25
10Q) Display the name ,job&magr for employee
who is salesman or manager?
Select ename,job,mgr
From emp
Where job in(‘SALESMAN’,’MANAGER’);
11Q) Display the name,job,sal with the following
conditions?
A)wheredeptno=20 or
deptno=10 and job=’manager’)
B)wheredeptno=20
or(deptno=10 and job=’manager’)
c)where (deptno=20 or
deptno=10)and job=’manager’)
A) Select
ename,job,sal
From emp
Where deptnoin(10,20)
And job =’manager’;
B) Select ename,job,sal
From emp
Where deptno=20
Or deptno=10
And job=’manager’;
C) Select ename ,job,sal
From emp
Where deptnoin(10,20)
And job =’manager’;
12Q) Display employee whose empno is greater than
7799 and less than 7901?
Select *
From emp
Where empno between
7799 and 7901;
13Q) Display the name which start with ‘j’ ends
with ‘s’ and having the char ‘e’ in the name ?
Select ename
From emp
Where ename like ‘J%E%S’;
14Q) Display the name of 05 chars and ends with
‘s’?
Select ename
From emp
where ename like
‘-----%S’;
15Q) Display dept wise total salary?
Select deptno,sum(sal)
From emp
Group by deptno;
16Q) Display avg(sum(sal)),sum(avg(sal)) see the
diff?
Select
avg(sum(sal)),sum(avg(sal)) ,( avg(sum(sal))-sum(avg(sal)))
From emp
Group by EMPNO;
17Q) Display the dept which having 2 or more
employee of same job?
Select
ename,job,deptno
From emp
Where job in(select
job from emp
Group by job
Having count(*)>2);
18Q) Display the employee who join the company
other than the year 81
Select *
From emp
Where
to_char((hiredate),’yyyy’)<>’1981’;
19Q) Display the empno, ename, job using
sub-query in place of table?...INLINEVIEW
SELECT EMPNO,
ENAME,JOB
FROM EMP
WHERE DEPTNO IN(SELECT
DEPTNO
FROM EMP);
20Q) Display the employee whose grade is 3 and
job other than manager?
Select *
From empe,xx_grade g
Where g.grade=’3’
And e. job not like
‘%MANAGER’;
21Q) Display
the employee who works in his managers dept?
SELECT
ENAME,MGR
FROM
EMP
WHERE
MGR IS NOT NULL;
23Q) DISPLAY THE
EMPLOYEE WHO GETS MORE SALARY THAN THE AVG SALARY IN THEIR DEPT?
SELECT
ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL>(SELECT
AVG(SAL) FROM EMP);
24Q)Display the
employee who get max(sal) in his dept?
select
ename,deptno,sal
from emp
where sal in (select
max(sal)
from
emp
group by deptno);
25Q) Display the empno,ename, job,deptno,dname
,loc with dept which has no employee?
select
e.ename,
e.empno,
e.job,
e.deptno,
d.dname,
d.loc
from emp e,
dept d
where
e.deptno(+)=d.deptno;
26Q) Display managers names and total salary paid
for employee under them?
Select ename,job
From emp
Where job=’manager’
And (select
(sal+comm), ename
From emp );
27Q) Display grades and total salary paid for
each grade?
select
G.GRADE,
E.SAL,
SAL+OLD_SAL TOTAL_SAL
from emp E,
GRADE G
WHERE SAL+OLD_SAL
BETWEEN G.LOWSAL AND G.HIGHSAL;
28Q) Display
eachdeptname and no of employs working for it?
SELECT
COUNT(1),D.DNAME,D.DEPTNO
FROM DEPT D,EMP E
WHERE D.DEPTNO =
E.DEPTNO
GROUP BY
D.DNAME,D.DEPTNO;
29Q) Display each job category and no of emp
working for it?
SELECT
COUNT(1),JOB
FROM EMP
GROUP BY JOB;
30Q) Display managers who are managers for least
no of employs?
SELECT COUNT(1),MGR
FROM EMP
GROUP BY MGR
HAVING COUNT(1)>0;
31Q) Display managers who are managers for max no
of employs?
SELECT ROWNUM, X.MGR
FROM (SELECT
COUNT(1),MGR
FROM EMP
GROUP BY MGR
HAVING COUNT(1)>0
ORDER BY COUNT(1)
DESC)X
WHERE ROWNUM=1;
32Q) Display least 3 sal paid employs?
SELECT ROWNUM,X.SAL
FROM(SELECT SAL
FROM EMP
ORDER BY SAL)X
WHERE ROWNUM<=3;
33Q) Display the
department where minimum no.ofempl working ?
SELECT
ROWNUM,DEPTNO,EMPLOYEES
FROM(SELECT COUNT(1)
EMPLOYEES,DEPTNO
FROM EMP
GROUP BY DEPTNO ORDER BY DEPTNO)
WHERE ROWNUM=1;
34Q) DISPLAY THE ROW
BETWEEN 6TH AND 10TH ?
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE RN BETWEEN 6 AND
10;
35Q)DISPLAY 9TH
ROW?
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE RN=9;
36Q) DISPLAY THE 1ST
ROW AND LAST ROW?
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE RN=4
UNION
SELECT * FROM(SELECT
ROWNUM RN,X.*
FROM(SELECT *
FROM emp
ORDER BY ROWNUM DESC)X)
WHERE RN=1;
37Q) DISPLAY THE LAST
10 ROWS
SELECT * FROM(SELECT
ROWNUM RN,X.*
FROM(SELECT *
FROM emp
ORDER BY ROWNUM DESC)X)
WHERE RN<=10;
38Q) DISPLAY THE 1ST 6 ROWS?
SELECT ROWNUM,E.*
FROM EMP E
WHERE ROWNUM<=6;
39Q) DISPLAY
ALTERNATIVE ROW?
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE MOD(RN,2)>0;
40Q) DISPLAY THOSE
EMPLOYEES WHO GET SAME SALARY?
SELECT ENAME,SAL
FROM EMP
WHERE SAL in (SELECT
sal
FROM emp
GROUP BY sal HAVING COUNT(sal) > 1);
42Q) DISPLAY THE
EMPLOYEE WHO WORKS WITH BLAKE AND HAVING GRADE>=2?
select
ename,JOB,G.GRADE
from emp,GRADE G
where JOB IN (SELECT
JOB
FROM EMP
WHERE ename = 'BLAKE' )
AND SAL BETWEEN
G.LOWSAL AND G.HIGHSAL
AND GRADE >=2;
43Q)DISPLAY THE
EMPLOYEE WHOSE MANGER GETTING SALARY >=3000?
SELECT JOB, SAL
FROM EMP
WHERE JOB='MANAGER'
AND SAL>=3000;
44Q) DISPLAY THE JOB
WHICH HAVING MORE EMPLOYEE OF GRADE 3?
SELECT JOB,SAL,G.GRADE
FROM EMP,GRADE G
WHERE SAL BETWEEN
LOWSAL
AND HIGHSAL AND
GRADE=3;
(OR)
SELECT JOB,SAL
FROM EMP
WHERE SAL IN (SELECT
SAL
FROM GRADE
WHERE SAL BETWEEN LOWSAL AND HIGHSAL
AND GRADE=3);
45Q) DISPLAY THE GRADE
WHICH HAVING MORE EMPLOYEES OF GRADE 3?
SELECT ROWNUM ,GRADE
FROM(SELECT
COUNT(1),GRADE
FROM
GRADE,EMP E
WHERE SAL BETWEEN LOWSAL AND HIGHSAL
GROUP BY GRADE
ORDER BY COUNT(1) DESC)
WHERE ROWNUM=1;
46Q) PRINT WEBNOLOGY
AS WEB-NO-LOGY?
SELECT
'WEB'||'-'||'NO'||'-'||'LOGY'
FROM DUAL;
47Q) DISPLAY THOSE
EMPLOYEES WHO GET SAME SALARY?
SELECT ENAME,SAL
FROM EMP
WHERE SAL in (SELECT
sal
FROM emp
GROUP BY sal HAVING COUNT(sal) >
1);
48Q) DISPLAY LAST 5
RECORDS?
SELECT * FROM(SELECT
ROWNUM RN,X.*
FROM(SELECT *
FROM emp ORDER BY ROWNUM
DESC)X)
WHERE RN<=5;
49Q) DISPLAY
ALTERNATIVE RECORDS?
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE MOD(RN,2)>0;
50Q) DISPLY THOSE
EMPLOYEES WHO ARE NOT MANAGER?
SELECT *
FROM EMP
WHERE JOB NOT IN
(SELECT JOB
FROM EMP
WHERE JOB='MANAGER');
51Q) DISPLAY THE THIRD
HIEGHEST PAID EMPLOYEES?
SELECT * FROM(SELECT
ROWNUM RN,X.*
FROM (SELECT *
FROM EMP ORDER BY SAL DESC)X)
WHERE RN=3;
52Q) DISPLAY 7TH
RECORD
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE RN=7;
53Q) DISPLAY RECORDS
BETWEEN 10TH AND 12TH
SELECT * FROM(SELECT
ROWNUM RN ,x.*
FROM(SELECT *
FROM emp)X)
WHERE RN BETWEEN 10
AND 12;
54Q) DISPLAY LAST
RECORD?
SELECT * FROM(SELECT
ROWNUM RN,X.*
FROM (SELECT *
FROM EMP ORDER BY ROWNUM DESC)X)
WHERE RN=1;
55Q) DELETE DUPLICATE
RECORD ?
DELETE
FROM EMP
WHERE ROWID NOT
IN(SELECT ROWID
FROM EMP);
56Q) DISPLAY THE
EMPLOYEE WHO GETS MAX SAL IN THEIR DEPT?
SELECT ROWID
RD,SAL,ENAME
FROM EMP
WHERE ROWID NOT IN
(SELECT MAX(ROWID)
FROM EMP);
57Q) DISPLAY THE
EMPLOYEE WHO GETS MAX SAL IN THEIR DEPT?
select
ename,deptno,sal
from emp
where sal in (select
max(sal)
from emp
group by deptno);
58Q) DISPLAY THE
EMPLOYEE WHO GETS MORE SALARY THAN THE AVG SALARY IN THEIR DEPT?
SELECT
ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL>(SELECT
AVG(SAL) FROM EMP);
59Q) DISPLAY THE NAME
OF MANAGER FOR EACH EMPLOYEE?
SELECT ENAME,JOB
FROM EMP
WHERE JOB='MANAGER';
60Q) DISPLAY THE
EMPLOYEE WHO JOIN THE DEPT BEFORE THEIR MANAGER?
SELECT * FROM
EMP E,EMP M
WHERE E.MGR=M.EMPNO
AND E.HIREDATE<M.HIREDATE;
61Q) DISPLAY THE FIRST
FIVE HIGHEST PAID EMPLOYEES?
SELECT ROWNUM,X.SAL
FROM(SELECT SAL
FROM EMP
ORDER BY SAL DESC)X
WHERE ROWNUM<=5;
62Q)DISPLAY THE FIVE
LAST LEAST PAID EMPLOYEES?
SELECT ROWNUM,X.SAL
FROM(SELECT SAL
FROM EMP
ORDER BY SAL)X
WHERE ROWNUM<=5;
63Q) DISPLAY THE
EMPLOYEE WHO HAS THE SAME JOB IN EACH DEPT?
SELECT *
FROM EMP
WHERE JOB IN (SELECT
JOB FROM EMP);
64Q) DISPLAY THOSE
EMPLOYEE WHOSE SAL IS GREATER THAN THE AVG SALARY OF THEIR DEPT?
SELECT ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL>(SELECT
AVG(SAL) FROM EMP);
65Q) DISPLAY THOSE
EMPLOYEES WHOSE GRADE IS 3?
SELECT *
FROM EMP
WHERE SAL IN(SELECT
SAL
FROM GRADE
WHERE SAL BETWEEN LOWSAL AND HIGHSAL
AND GRADE = 3);
66Q) DISPLAY THOSE EMPLOYEES
WHOSE DEPARTMENT IS SALES?
select *
from emp
WHERE DEPTNO IN(SELECT
DEPTNO FROM DEPT WHERE DNAME='SALES');
67Q) DISPLAY THOSE
EMPLOYEES WHOSE DEPT IS SALES OR ACCOUNTING?
select *
from emp
WHERE DEPTNO IN(SELECT
DEPTNO
FROM DEPT
WHERE DNAME IN
('SALES','ACCOUNTING'));
68Q) DISPLAY THOSE
EMPLOYEES WHO ARE IN BOSTON'S DEPT?
SELECT *
FROM EMP
WHERE DEPTNO IN(SELECT
DEPTNO
FROM DEPT
WHERE LOC='BOSTON');
69Q) DISPLAY THOSE
EMPLOYEES WHOSE NAME STARTS WITH J AND HIS DEPT ENDS WITH S?
SELECT *
FROM EMP
WHERE ENAME LIKE 'J%'
AND DEPTNO IN(SELECT
DEPTNO
FROM DEPT
WHERE DNAME LIKE '%S');
70Q) DISPLAY THOSE
EMPLOYEES WHO ARE WORKING UNDER KING?
SELECT *
FROM EMP
WHERE ENAME IN (SELECT
ENAME
FROM EMP
WHERE ENAME='KING');
71Q) DISPLAY THOSE WHO
ARE WORKING UNDER JONES/CLARK?
SELECT *
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
WHERE ENAME IN
('JONES','CLARK'));
72Q) DISPLAY THOSE
EMPLOYEES WHOSE MANAGER JOINED IN THE YEAR 81?
SELECT *
FROM EMP
WHERE TO_CHAR (HIREDATE,'YY')='81'
AND JOB='MANAGER';
73Q) DISPLAY THOSE
EMPLOYEES WHOSE JOINING YEAR SAME AS THEIR MANAGER'S JOINING YEAR i.e 81?
SELECT *
FROM EMP
WHERE TO_CHAR
(HIREDATE,'YY')='81'
AND JOB IN (SELECT JOB
FROM EMP);
74Q) DISPLAY THOSE
EMPLOYEES WHOSE MANAGER'S DEPARTMENT IS SALES?
SELECT *
FROM EMP
WHERE JOB='MANAGER'
AND DEPTNO IN (SELECT
DEPTNO
FROM DEPT
WHERE DNAME ='SALES');
75Q) DISPLAY THOSE
EMPLOYEES WHOSE DEPT LOCATION IS NEW YORK/CHICAGO?
SELECT *
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM DEPT
WHERE LOC IN('NEW YORK','CHICAGO'));
76Q) DISPLAY THOSE
EMPLOYEES WHOSE JOB ARE CLERK & LOCATED AT CHICAGO?
SELECT *
FROM EMP
WHERE JOB='CLERK'
AND DEPTNO IN (SELECT
DEPTNO
FROM DEPT
WHERE LOC='CHICAGO');
78Q) LIST THE DETAILS
OF THE SENIOR EMPLOYEE BELONGS TO 1981?
SELECT MIN(HIREDATE)
FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY')='1981';
79Q) LIST THE EMPS WHO
ARE SENIOR TO MILLER?
SELECT *
FROM EMP
WHERE HIREDATE <(
SELECT HIREDATE
FROM EMP
WHERE ENAME='MILLER');
80Q) LIST THE EMP WHO
ARE CLERK'S WHO HAVE EXP MORE THAN 8YEARS?
SELECT *
FROM EMP
WHERE
ROUND(MONTHS_BETWEEN (SYSDATE,HIREDATE)/12)>8
AND JOB='CLERK';
No comments:
Post a Comment