BD PS3 - Rozwiązania zadań SQL z pracowni 3 PDF

Preview

Summary

Download BD PS3 - Rozwiązania zadań SQL z pracowni 3 PDF

Description

--SELECT COUNT(ENAME) FROM EMP1; /*SELECT COUNT(DISTINCT COMM) FROM EMP1; SELECT COUNT(DISTINCT JOB) FROM EMP1;*/ /*SELECT ENAME FROM EMP1 WHERE ENAME LIKE ('BL%'); --SELECT ENAME FROM EMP1 WHERE SUBSTR(ENAME,1,2) = 'BL'; --SELECT ENAME FROM EMP1 WHERE ENAME INSTR(ENAME, 'BL')=1;*/ --SELECT ENAME FROM EMP1 WHERE ENAME LIKE '%\_%' ESCAPE'\'; --SELECT MIN(SAL), MAX(SAL), AVG(SAL), SUM(SAL) FROM EMP1; --SELECT COUNT(*) FROM EMP1 WHERE JOB='MANAGER'; --SELECT MIN(SAL), MAX(SAL) FROM EMP1 E1, DEPT1 D1 WHERE E1.DEPTNO=D1.DEPTNO AND DNAME = 'RESEARCH'; --SELECT COUNT(*) FROM EMP1, SALGRADE1 WHERE GRADE = 1 AND SAL BETWEEN LOSAL AND HISAL; --SELECT COUNT(*), SUM(COMM) FROM EMP1 E, DEPT1 D WHERE COMM IS NOT NULL AND LOC = 'DALLAS' AND E.DEPTNO=D.DEPTNO; --SELECT JOB, MIN(SAL), MAX(SAL) FROM EMP1 GROUP BY JOB; --SELECT COUNT(*), GRADE FROM EMP1, SALGRADE1 WHERE SAL BETWEEN LOSAL AND HISAL GROUP BY GRADE ORDER BY GRADE; /*SELECT DNAME, SUM(SAL), SUM(NVL(COMM, 0)) FROM EMP1 E, DEPT1 D WHERE D.DEPTNO = E.DEPTNO GROUP BY DNAME; SELECT DNAME, SUM(SAL) + SUM(NVL(COMM, 0)) FROM EMP1 E, DEPT1 D WHERE D.DEPTNO = E.DEPTNO GROUP BY DNAME; SELECT DNAME, SUM(SAL + NVL(COMM, 0)) FROM EMP1 E, DEPT1 D WHERE D.DEPTNO = E.DEPTNO GROUP BY DNAME;*/ SELECT DISTINCT JOB, MAX(SAL) FROM EMP1 GROUP BY JOB HAVING MAX(SAL) < 2500; SELECT DNAME FROM EMP1 E, DEPT1 D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO, DNAME, JOB HAVING COUNT(*)>2; SELECT GRADE FROM EMP1, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL GROUP BY GRADE HAVING COUNT(*) BETWEEN 2 AND 5;

SELECT JOB FROM EMP1 GROUP BY JOB HAVING MAX(SAL)-MIN(SAL)=2 ORDER BY 3; SELECT SUM(NVL(COMM)) FROM EMP1 WHERE DEPTNO=10; SELECT P.PRONAME, COUNT(1) FROM PROJECT1 P, IMPLPROJECT I WHERE P.PRONO=I.PRONO AND I.START_DATE BETWEEN TO_DATE('01/01/15', 'DD/MM/YY') AND TO_DATE('31/12/15', 'DD/MM/YY') AND I.END_DATE BETWEEN TO_DATE('01/01/15', 'DD/MM/YY') AND TO_DATE('31/12/15', 'DD/MM/YY') GROUP BY PRONAME;...