orclSELECT.sql

  ---------ASSINGMENT NO 1----- *


1.SELECT ENAME,SAL*12
FROM EMP

 2.SELECT ENAME,SAL*12
 FROM EMP;
 
3.SELECT EMP.*,(SAL*12)+2000
FROM EMP;

4. SELECT ENAME,SAL,SAL+SAL*10/100
 FROM EMP;

 5.  SELECT ENAME,SAL-SAL*25/100
 FROM EMP;

 6. SELECT ENAME,SAL+50,(SAL+50)
FROM EMP;

7.  SELECT ENAME,(SAL*12)-(SAL*12)*(1/10)
 FROM EMP;

 8.
SELECT EMP.*,(SAL+COMM)
FROM EMP;

 9. 
SELECT EMP.*,(SAL*12)
FROM EMP;

10.  SELECT ENAME,JOB,(SAL-100)
 FROM EMP;







 2 ---------ASSINGMENT 2----- *On Where Clause*

 1. SELECT ENAME SAL,SAL*12 ANUSAL
FROM EMP
WHERE ENAME='SMITH';

 2. 
 SELECT*
 FROM EMP
 WHERE JOB='CLERK';

3. SELECT ENAME ,SAL
FROM EMP
WHERE JOB='SALESMAN';

 4.  SELECT*
 FROM EMP
 WHERE SAL>2000;

 5. 
 SELECT*FROM EMP
 WHERE ENAME='JONES';

 6. 
 
 SELECT*
 FROM EMP
 WHERE HIREDATE>'01-JAN-81';

7.  SELECT ENAME ,SAL,(SAL*12) AS ANNUALSALARY
 FROM EMP
 WHERE SAL*12>12000;

 8. 
 SELECT EMPNO
 FROM EMP
 WHERE DEPTNO=30;

 9.  SELECT ENAME,HIREDATE
 FROM EMP
 WHERE HIREDATE<'01-JAN-81';

 10. SELECT *
     FROM EMP
    WHERE JOB ='MANAGER';

11. 
SELECT ENAME,SAL
FROM EMP
WHERE COMM=1400;

12.  SELECT *
 FROM EMP
 WHERE COMM>SAL;

13. 
SELECT EMPNO
FROM EMP
WHERE HIREDATE<'01-JAN-87';

14. 
SELECT *
FROM EMP
WHERE JOB='ANALYST';

15. 
 SELECT *
 FROM EMP
 WHERE SAL > 2000;



 -------------- ASSINGMENT NO- 03 --------*Logical Operator*

 1. SELECT *
FROM EMP
WHERE JOB='CLERK'
AND SAL < 1500;

2. 
 SELECT ENAME,HIREDATE
 FROM EMP
 WHERE JOB='MANAGER'
 AND DEPTNO=30;

 3. 
 SELECT ENAME,JOB DEPTNO,SAL,SAL*12
 FROM EMP
 WHERE DEPTNO=30
 AND JOB='SALESMAN';

                            OR

SELECT EMP.*,SAL*12
FROM EMP
WHERE DEPTNO=30 AND JOB='SALESMAN' AND SAL*12 > 12000;

 4. 
 SELECT *
 FROM EMP
 WHERE DEPTNO = 30
OR JOB = 'ANALYST';

5. SELECT *
FROM EMP
WHERE SAL < 1100
AND JOB = 'CLERK';

6. 
SELECT ENAME,SAL,(SAL*12) DEPTNO
FROM EMP
WHERE DEPTNO=20 AND SAL>1100 AND SAL*12 >12000;

7.SELECT EMPNO,ENAME
FROM EMP
WHERE JOB ='MANAGER'
AND DEPTNO=20;

8. 
SELECT *
FROM EMP
WHERE DEPTNO=20
OR DEPTNO=30;

9. 
 SELECT *
 FROM EMP
 WHERE DEPTNO=10
 AND JOB='ANALYST';
                  
FOR OR


SELECT *
FROM EMP
WHERE DEPTNO=10
OR JOB = 'ANALYST';

10. 
 SELECT *
 FROM EMP
 WHERE JOB = 'PRESIDENT'
 AND SAL = 4000;

 11. 
SELECT ENAME,DEPTNO
FROM EMP
WHERE JOB ='CLERK'
AND (DEPTNO=10 OR DEPTNO=20);

                  OR



 SELECT ENAME,DEPTNO
 FROM EMP
 WHERE JOB ='CLERK'
AND DEPTNO=10
OR DEPTNO=20;

12.  SELECT *
 FROM EMP
 WHERE DEPTNO = 10
 AND JOB ='CLERK'
OR JOB='MANAGER';

13.  SELECT ENAME
 FROM EMP
 WHERE DEPTNO IN (10,20,30,40);

14. SELECT *
FROM EMP
WHERE EMPNO IN (7902,7839);

15.  
 SELECT *
 FROM EMP
 WHERE JOB IN ('MANAGER','SALESMAN','CLERK');

16.  SELECT ENAME,HIREDATE
 FROM EMP
 WHERE HIREDATE > '31-DEC-81'
 AND HIREDATE < '01-JAN-87';

 17. 
SELECT *
FROM EMP
WHERE SAL > 1250
AND SAL < 3000;

18.
SELECT ENAME, HIREDATE, DEPTNO
FROM EMP
WHERE HIREDATE > '31-DEC-81'
AND DEPTNO IN (10,30);

19. 
 SELECT ENAME ,(SAL*12) AS ANNUALSAL
  FROM EMP
  WHERE JOB IN('MANAGER','CLERK')
  AND DEPTNO IN (10,30);

  20. 
 SELECT EMP.*,  (SAL*12) AS ANNUALSAL
 FROM EMP
 WHERE SAL BETWEEN 1000 AND 4000
 AND (SAL * 12) > 15000;


    -----* Special OPERATORS*-------             -------------- ASSINGMENT NO- 04 --------

1.  SELECT *
 FROM EMP
 WHERE COMM IS NULL;

 2. 
SELECT *
FROM EMP
WHERE MGR IS NULL;

3.  SELECT *
 FROM EMP
 WHERE JOB='SALESMAN'
 AND DEPTNO=30;

 4.  SELECT *
 FROM EMP
 WHERE JOB='SALESMAN'
AND DEPTNO=30
AND SAL>1500;

5. 
SELECT *
FROM EMP
WHERE ENAME LIKE 'S%'
OR ENAME LIKE 'A%';

6. SELECT *
FROM EMP
WHERE DEPTNO  NOT IN (10,20);   (EXPECT MEANS IS NOT)

7.  SELECT ENAME
 FROM EMP
 WHERE ENAME NOT LIKE 'S%';

8. SELECT ENAME
FROM EMP
WHERE MGR IS NOT NULL
AND DEPTNO IN 10;

9.  SELECT *
 FROM EMP
 WHERE COMM IS NULL
 AND JOB ='CLERK';

 10.  SELECT *
 FROM EMP
 WHERE MGR IS NULL
AND DEPTNO IN (10,30);

11. SELECT *
 FROM EMP
 WHERE JOB ='SALESMAN'
 AND DEPTNO = 30
AND SAL>2450;

12.  SELECT *
 FROM EMP
 WHERE DEPTNO=20
AND SAL>2500;

13. SELECT *
FROM EMP
WHERE ENAME LIKE 'M%'
OR ENAME LIKE 'J%';

14. 
 SELECT ENAME ,SAL*12 AS ANNUAL
 FROM EMP
 WHERE DEPTNO NOT IN 30;

 15.  SELECT *
 FROM EMP
 WHERE ENAME NOT LIKE 'ES%' 
 OR ENAME NOT LIKE 'R';

 16. SELECT ENAME,SAL*1.10 AS ANNUAL_SALARY
FROM EMP
WHERE MGR IS NOT NULL AND DEPTNO IN 30;

17. 





18.  SELECT *
FROM EMP
WHERE HIREDATE > ('31-DEC-81');

19. SELECT *
FROM EMP
WHERE HIREDATE LIKE '%FEB%';

20. 
 SELECT *
 FROM EMP WHERE JOB NOT IN ('MANAGER','CLERK')
 AND DEPTNO IN (10,20)
 AND SAL BETWEEN 1000 AND 3000;


 ------Assignment NO. 5 --- MRF()----------

 1. SELECT COUNT(*)
FROM EMP
WHERE DEPTNO=10 AND SAL<2000;

2.  SELECT SUM(SAL)
  FROM EMP
  WHERE JOB ='CLERK';

  3.  SELECT AVG(SAL)
 FROM EMP;

 4.  SELECT COUNT(*)
 FROM EMP
 WHERE ENAME LIKE 'A%';

5. 
SELECT COUNT (*)
FROM EMP
WHERE JOB IN ('CLERK','MANAGER');

6. 
SELECT SUM(SAL)
FROM EMP
WHERE HIREDATE LIKE '%FEB%';

7. SELECT COUNT (*)
FROM EMP
WHERE MGR ='7839';

8. 
SELECT COUNT(COMM)
FROM EMP
WHERE DEPTNO=30;

9.  SELECT AVG(SAL),SUM(SAL),COUNT(*),MAX(SAL)
 FROM EMP
 WHERE JOB = 'PRESIDENT';

 10. SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%A%';

11. 
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE ENAME LIKE '%LL%';

12. SELECT COUNT(DEPTNO)
FROM EMP;

13. SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%Z%';

14.
SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%$%';

15. 
SELECT SUM(SAL)
FROM EMP
WHERE JOB ='CLERK' AND DEPTNO=30;

16.  SELECT MAX(SAL)
 FROM EMP
 WHERE JOB IN 'ANALYST';

 17. 
SELECT COUNT (DISTINCT SAL)
FROM EMP;

18.   
 SELECT COUNT(DISTINCT JOB)
 FROM EMP;

 19. SELECT AVG(SAL)
FROM EMP
WHERE JOB IN 'CLERK';

20. 
 SELECT MIN(SAL)
 FROM EMP
 WHERE DEPTNO IN 10 AND JOB IN ('MANAGER','CLERK');


  
 ---------------ASSINGMENT NO. 6-----ON GROUP BY-----------

  

 1. SELECT COUNT(*),DEPTNO
 FROM EMP
 WHERE JOB!='PRESIDENT'
 GROUP BY DEPTNO;

2. 
SELECT SUM(SAL),JOB
 FROM EMP
 GROUP BY JOB;

 3. SELECT COUNT(*),DEPTNO
FROM EMP
WHERE JOB ='MANAGER'
GROUP BY DEPTNO;

4.SELECT AVG(SAL),DEPTNO
FROM EMP
WHERE DEPTNO IN 20
GROUP BY DEPTNO;

5.  SELECT COUNT(*),JOB
 FROM EMP
 WHERE ENAME LIKE '%A%'
 GROUP BY JOB;

 6.  SELECT COUNT(*),AVG(SAL),DEPTNO
  FROM EMP
  WHERE SAL>2000
  GROUP BY DEPTNO;

  7. SELECT SUM(SAL), DEPTNO
FROM EMP
WHERE JOB ='SALESMAN'
GROUP BY DEPTNO;

8.  SELECT COUNT(*),MAX(SAL),JOB
 FROM EMP
 GROUP BY JOB;

 9.  SELECT MAX(SAL),DEPTNO
  FROM EMP
  GROUP BY DEPTNO;

  10.SELECT COUNT(*),SAL
FROM EMP
GROUP BY SAL;


 ---------------ASSINGMENT NO. 7-----ON HAVING CLAUSE ---------
---------------ASSINGMENT NO- 8. -----------
---ON HAVING CASE 1 ---------


1. SELECT *
 FROM EMP
 WHERE ENAME=(SELECT SAL
FROM EMP
 WHERE ENAME='ADAMS');


2. SELECT ENAME,SAL
 FROM EMP
 WHERE SAL<(SELECT SAL
 FROM EMP
 WHERE ENAME='KING');


3.SELECT ENAME,DEPTNO
 FROM EMP
 WHERE DEPTNO=(SELECT DEPTNO
 FROM EMP
 WHERE ENAME='JONES');

 4.SELECT ENAME,JOB
FROM EMP
WHERE JOB=(SELECT JOB
FROM EMP
WHERE ENAME='JAMES');

5. SELECT EMPNO,ENAME,(SAL*12)
FROM EMP
WHERE SAL>(SELECT (SAL*12)
FROM EMP
WHERE ENAME='WARD');

6.
  SELECT ENAME,HIREDATE
  FROM EMP
  WHERE HIREDATE<(SELECT HIREDATE
 FROM EMP
 WHERE ENAME='SCOTT');

 7.SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE JOB='PRESIDENT');

8. SELECT ENAME,SAL
FROM EMP
WHERE SAL<(SELECT SAL
FROM EMP
WHERE EMPNO='7839');

9.  SELECT *
 FROM EMP
 WHERE HIREDATE<(SELECT HIREDATE
 FROM EMP
 WHERE ENAME='MILLER');

 10.  
 SELECT ENAME,EMPNO
 FROM EMP
 WHERE SAL>(SELECT SAL
 FROM EMP
 WHERE ENAME='ALLEN');

11. 
SELECT ENAME,SAL
FROM EMP
WHERE SAL>(SELECT SAL
 FROM EMP
 WHERE ENAME='MILLER') AND SAL<(SELECT SAL
 FROM EMP
 WHERE ENAME='ALLEN');

 12.SELECT *
FROM EMP
WHERE DEPTNO=20 AND JOB=(SELECT JOB
FROM EMP
WHERE ENAME='SMITH');

13. SELECT *
 FROM EMP
 WHERE JOB='MANAGER' AND DEPTNO= (SELECT DEPTNO
  FROM EMP
  WHERE ENAME='TURNER');

  14. SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME='KING');

15. 
  SELECT ENAME,SAL,(SAL*12)
   FROM EMP 
  WHERE SAL>3500 AND SAL<(SELECT SAL
  FROM EMP
 WHERE ENAME='BLAKE');

 16. 
SELECT *
FROM EMP
WHERE SAL> (SELECT SAL
FROM EMP
WHERE ENAME='SCOTT') AND SAL<(SELECT SAL
FROM EMP
WHERE ENAME='KING');

17. SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%' AND DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='BLAKE');

18.  SELECT ENAME,COMM
 FROM EMP
 WHERE COMM IS NOT NULL AND JOB=(SELECT JOB
 FROM  EMP
 WHERE ENAME='SMITH');

19. SELECT *
FROM EMP
WHERE JOB='CLERK' AND DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='TURNER');

20. SELECT ENAME,SAL,JOB
FROM EMP
WHERE SAL*12>(SELECT SAL*12
FROM EMP
WHERE ENAME='SMITH') AND SAL<(SELECT SAL*12
FROM EMP
WHERE ENAME='KING');



---------------ASSINGMENT NO- 8. -----------* ON Case 2------------------


1. SELECT DNAME
 FROM DEPT
  WHERE DEPTNO=(SELECT DEPTNO
  FROM EMP
  WHERE ENAME='SMITH');

  
2.SELECT DNAME,LOC
FROM DEPT
WHERE DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='KING');

3. 
 SELECT *
 FROM DEPT
 WHERE DEPTNO=(SELECT DEPTNO
 FROM EMP
 WHERE EMPNO='7902');

 4. 
SELECT DNAME,LOC,DEPTNO
FROM DEPT
WHERE DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME IN '%R%');

5. 
 SELECT DNAME
 FROM DEPT
 WHERE DEPTNO=(SELECT DEPTNO
 FROM EMP
 WHERE JOB='PRESIDENT');

6. SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
FROM DEPT
WHERE DNAME='ACCOUNTING');

7.  SELECT ENAME,SAL
 FROM EMP
  WHERE DEPTNO=(SELECT DEPTNO
  FROM DEPT
  WHERE LOC='CHICAGO');

  8.SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
 FROM DEPT
 WHERE JOB='SALES');

9. SET LINES 100 PAGES 100;
SELECT EMP.*,(SAL*12) ANNUSAL
 FROM EMP
 WHERE DEPTNO=(SELECT DEPTNO
 FROM DEPT
 WHERE LOC='NEW YORK');

 10.   SELECT ENAME
  FROM EMP
  WHERE DEPTNO=(SELECT DEPTNO
 FROM DEPT
 WHERE DNAME='OPERATIONS');

 ------------------ ON CASE 1 & CASE 2---------------------

 1. SELECT ENAME
FROM EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME='SCOTT') AND DEPTNO=(SELECT DEPTNO
FROM DEPT
WHERE DNAME='ACCOUNTING');

2. SELECT * FROM EMP
 WHERE JOB IN 'MANAGER' AND DEPTNO IN(SELECT DEPTNO FROM DEPT 
WHERE LOC='CHICAGO');

3.  SELECT ENAME,SAL
 FROM EMP
 WHERE SAL>(SELECT SAL
 FROM EMP
 WHERE ENAME='KING') AND DEPTNO=(SELECT DEPTNO
 FROM DEPT
 WHERE DNAME='ACCOUNTING');

 4. SELECT ENAME
FROM EMP
WHERE JOB='SALESMAN' AND DEPTNO=(SELECT DEPTNO
FROM DEPT 
WHERE DNAME='SALES');

5. 
 SELECT ENAME,SAL,JOB,HIREDATE
 FROM EMP
WHERE HIREDATE<(SELECT HIREDATE
FROM EMP
WHERE ENAME='KING') AND DEPTNO=(SELECT DEPTNO
FROM DEPT
WHERE DNAME='OPERATIONS');

6. SELECT *
FROM EMP
 WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
 WHERE DNAME  LIKE '%S');

7.  SELECT *
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE'%A%');

8.
 SELECT DNAME,LOC
 FROM DEPT
 WHERE DEPTNO IN(SELECT DEPTNO
 FROM EMP
 WHERE SAL=800);

 9.  SELECT *
  FROM DEPT
  WHERE DEPTNO IN (SELECT DEPTNO
  FROM EMP
  WHERE COMM IS NOT NULL);


10.  SELECT LOC
 FROM DEPT
 WHERE DEPTNO =40 AND DEPTNO IN (SELECT DEPTNO
  FROM EMP
  WHERE COMM IS NOT NULL);

---------------ASSINGMENT NO- 8. ----------CLASS TEST------------

1.SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC='NEWYORK');

2. SELECT EMP.*,SAL+SAL*0.1
 FROM EMP
 WHERE ENAME LIKE '%R';

3. SELECT EMP.*,SAL+SAL*0.1
FROM EMP
WHERE ENAME LIKE '%R';


4.SELECT *
FROM EMP
WHERE SAL LIKE '____';


5. SELECT DNAME
 FROM DEPT
 WHERE DEPTNO IN (SELECT DEPTNO
 FROM EMP
 WHERE ENAME IN ('ADAMS','SCOTT'));

6. SELECT DEPTNO
 FROM EMP
 WHERE ENAME LIKE '%A%' AND ENAME LIKE '%S%'
 GROUP BY DEPTNO
 HAVING COUNT(*)>3;

7. SELECT *
FROM EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME='SMITH') AND SAL<(SELECT SAL
FROM EMP
WHERE ENAME='KING');



8.SELECT ENAME,HIREDATE
FROM EMP
WHERE ENAME LIKE '%S' AND HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME = 'ADAMS');

9.SELECT ENAME,HIREDATE
FROM EMP
WHERE ENAME LIKE '%S' AND HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME = 'JAMES');


10.SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='JAMES') AND SAL>(SELECT SAL
FROM EMP
WHERE ENAME='ADAMS') AND JOB=(SELECT JOB
FROM EMP
WHERE ENAME='MILLER') AND HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME='MARTIN');

---------------ASSINGMENT NO- 8. ----------ON MAX & MIN-------

1. SELECT ENAME
 FROM EMP
 WHERE SAL=(SELECT MAX(SAL)
 FROM EMP);

2.  SELECT ENAME
 FROM EMP
 WHERE SAL=(SELECT MIN(SAL)
 FROM EMP);

3. SELECT ENAME,HIREDATE
FROM EMP
 WHERE HIREDATE=(SELECT MIN(HIREDATE)
FROM EMP);

 
4. SELECT ENAME,HIREDATE
 FROM EMP
  WHERE HIREDATE=(SELECT MAX(HIREDATE)
  FROM EMP);

 5. SELECT ENAME,HIREDATE
FROM EMP
 WHERE HIREDATE=(SELECT MIN(HIREDATE)
FROM EMP);


6. SELECT ENAME,COMM
 FROM EMP
 WHERE COMM=(SELECT MAX(COMM)
 FROM EMP);

7. SELECT *
FROM EMP
WHERE EMPNO=(SELECT MAX(EMPNO)
FROM EMP);

8.SELECT *
FROM EMP
WHERE EMPNO=(SELECT MIN(EMPNO)
 FROM EMP);

9. SELECT *
FROM EMP
WHERE (SAL*12)=(SELECT MIN(SAL*12)
FROM EMP);



10. SELECT ENAME,(SAL*12)
 FROM EMP
WHERE (SAL*12)>(SELECT MAX(SAL*12)
 FROM EMP

WHERE JOB='SALESMAN');