DBMS_PRACTICAL-6.sql

SELECT * FROM EMPLOYEE;
SELECT * FROM JOB;
SELECT * FROM DEPOSIT;
SELECT * FROM BRANCH;
SELECT DEPT_NAME, COUNT(*) FROM EMPLOYEE GROUP BY DEPT_NAME;
------------------------------------------------------------PRACTICAL-6-----------------------------------------------------------
-------------------------------------------------------------QUERIES--------------------------------------------------------------
1. SELECT COUNT(ACTNO) FROM DEPOSIT WHERE ADATE >'1-JAN-96';
2. SELECT COUNT(ACTNO) FROM DEPOSIT JOIN BRANCH ON DEPOSIT.BNAME=BRANCH.BNAME WHERE BRANCH.CITY='NAGPUR';
3. SELECT MAX(AMOUNT) FROM DEPOSIT JOIN BRANCH ON DEPOSIT.BNAME=BRANCH.BNAME WHERE BRANCH.CITY='BOMBAY';
4. SELECT MAX(EMP_SAL) AS MAXIMUM, MIN(EMP_SAL) AS MINIMUM, SUM(EMP_SAL) AS SUM, ROUND(AVG(EMP_SAL)) AS AVERAGE FROM EMPLOYEE;
5. SELECT MAX(EMP_SAL)-MIN(EMP_SAL) AS DIFFERENCE FROM EMPLOYEE;
6. SELECT TO_CHAR(HIREDATE,'YYYY'),COUNT(*) FROM EMPLOYEE GROUP BY TO_CHAR(HIREDATE,'YYYY') ORDER BY TO_CHAR(HIREDATE,'YYYY');
7. SELECT DEPT_NAME, AVG(EMP_SAL) FROM EMPLOYEE GROUP BY DEPT_NAME;
8. SELECT JOB_TITLE,SUM(EMP_SAL) FROM EMPLOYEE JOIN JOB ON EMPLOYEE.JOB_ID=JOB.JOB_ID GROUP BY JOB_TITLE; 
9. SELECT DEPT_NAME,AVG(EMP_SAL) FROM EMPLOYEE GROUP BY DEPT_NAME HAVING AVG(EMP_SAL)>2000;
10. SELECT JOB_ID,SUM(EMP_SAL) FROM EMPLOYEE GROUP BY JOB_ID HAVING SUM(EMP_SAL)>3000 ORDER BY SUM(EMP_SAL);
11. SELECT BRANCH.BNAME,SUM(AMOUNT) FROM BRANCH JOIN DEPOSIT ON DEPOSIT.BNAME=BRANCH.BNAME WHERE BRANCH.CITY='BOMBAY' GROUP BY BRANCH.BNAME HAVING SUM(AMOUNT)>5000 ORDER BY BRANCH.BNAME;