Saturday, March 12, 2016

SQL QUERIES

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

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...