SQL WEEK6

                                                SQL  QUREIES

6.1

SELECT SUM(SAL), JOB FROM EMP GROUP BY JOB

6.2

WITH WORK AS (SELECT COUNT(EMPNO), DEPTNO FROM EMP E , DEPT D WHERE E.DEPTNO=D.DNO PRJ_ID='P1' GROUP BY DEPTNO) SELECT DNAME FROM DEPT,WORK WHERE DNO=WORK.DEPTNO

6.3

select ENAME,job from emp e,depart d where e.DEPTNO = d.dno and daname in('MARKETING','RESEARCH')

select ENAME,job from emp e,depart d where e.DEPTNO = d.dno and (daname ='MARKETING' OR DANAME='RESEARCH')

6.4

SELECT ENAME ,SAL FROM EMP WHERE SAL>SOME(SELECT MIN(SAL)FROM EMP)

6.5

SELECT DEPTNO FROM EMP WHERE SAL> ALL(SELECT AVG(SAL) FROM EMP GROUP BY JOB)

6.6

SELECT D.DNAME FROM DEPART D WHERE (SELECT COUNT(PRJ_NO)FROM PROJ P WHERE D.DNO = P.DNO)>1

6.7

SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='MAHESH')

6.8

CREATE VIEW EMP_PRJ_VW AS SELECT E.EMPNO,E.ENAME,E.JOB,E.DATE_BIRTH,E.DEPTNO,E.DATE_JOIN,E.PRJ_ID,E.MGRID,P.PRJ_NAME,P.PRJ_NO,D.DNAME,D.DNO FROM EMP E,PROJ P,DEPART D WHERE D.DNO=E.DEPTNO AND D.DNAME='MARKETING' AND P.PRJ_NO=E.PRJ_ID AND P.DNO=D.DNO

6.9

SELECT ENAME,PRJ_NAME FROM EMP_PRJ_VW WHERE PRJ_NO=PRJ_ID AND DEPTNO=DNO

6.10

SELECT COUNT(EMPNO) ,TO_CHAR(DATE_JOIN,'MON')FROM EMP 
GROUP BY TO_CHAR(DATE_JOIN,'MON') 
ORDER BY TO_CHAR(DATE_JOIN,'MON') 

6.11

CREATE INDEX JOB_NAME_IND ON EMP(ENAME,JOB)