-- create table DEPT(
-- DEPTNO int primary key,
-- DEPTNAME varchar2(20)
-- );
-- create table EMP(
-- EMPNO int primary key,
-- EMPNAME varchar2(20),
-- DEPTNO int,
-- JOB varchar2(20),
-- SALARY int,
-- constraint fk_EMP_DEPT foreign key (DEPTNO) references DEPT(DEPTNO)
-- );
-- INSERT INTO DEPT VALUES (10, 'Sales');
-- INSERT INTO DEPT VALUES (20, 'Marketing');
-- INSERT INTO DEPT VALUES (30, 'Finance');
-- INSERT INTO DEPT VALUES (40, 'Human Resources');
-- INSERT INTO DEPT VALUES (50, 'IT');
-- INSERT INTO DEPT VALUES (60, 'Operations');
-- INSERT INTO DEPT VALUES (70, 'Research');
-- INSERT INTO DEPT VALUES (80, 'Development');
-- INSERT INTO DEPT VALUES (90, 'Quality Assurance');
-- INSERT INTO DEPT VALUES (100, 'Customer Support');
-- INSERT INTO EMP VALUES (1, 'John Doe', 10, 'Salesman', 50000);
-- INSERT INTO EMP VALUES (2, 'Jane Smith', 20, 'Manager', 60000);
-- INSERT INTO EMP VALUES (3, 'Bob Johnson', 30, 'Financial Analyst', 70000);
-- INSERT INTO EMP VALUES (4, 'Alice Brown', 40, 'HR Specialist', 55000);
-- INSERT INTO EMP VALUES (5, 'David Miller', 10, 'Salesman', 48000);
-- INSERT INTO EMP VALUES (6, 'Emily Davis', 20, 'Marketing Coord.', 55000);
-- INSERT INTO EMP VALUES (7, 'Chris Wilson', 30, 'Manager', 80000);
-- INSERT INTO EMP VALUES (8, 'Samantha Turner', 40, 'Manager', 65000);
-- INSERT INTO EMP VALUES (9, 'Michael Johnson', 50, 'Research Scientist', 72000);
-- INSERT INTO EMP VALUES (10, 'Laura Adams', 60, 'Software Engineer', 90000);
-- select * from DEPT;
-- select * from EMP;
-- select count(*) from EMP;
-- select count(*) from DEPT;
-- SELECT D.DEPTNO, D.DEPTNAME, E.EMPNO, E.EMPNAME, E.JOB, E.SALARY
-- FROM DEPT D
-- JOIN EMP E ON D.DEPTNO = E.DEPTNO
-- ORDER BY D.DEPTNO, E.EMPNO;
-- select sum(SALARY), max(SALARY), min(SALARY) from EMP where DEPTNO= 30;
-- select EMPNAME from EMP where SALARY in(select max(SALARY) from EMP);
-- SELECT D.DEPTNO, SUM(E.SALARY) AS TOTAL_SALARY
-- FROM DEPT D
-- JOIN EMP E ON D.DEPTNO = E.DEPTNO
-- GROUP BY D.DEPTNO, D.DEPTNAME
-- ORDER BY D.DEPTNO;
-- SELECT JOB, SUM(SALARY) AS TOTAL_SALARY from EMP group by JOB;
-- SELECT D.DEPTNO, D.DEPTNAME, E.JOB, SUM(E.SALARY) AS TOTAL_SALARY
-- FROM DEPT D
-- JOIN EMP E ON D.DEPTNO = E.DEPTNO
-- GROUP BY D.DEPTNO, D.DEPTNAME, E.JOB
-- ORDER BY D.DEPTNO, E.JOB;
-- select JOB, avg(SALARY) from EMP where DEPTNO= 20 group by JOB;
-- SELECT E.JOB, SUM(E.SALARY) AS TOTAL_SALARY
-- FROM DEPT D
-- JOIN EMP E ON D.DEPTNO = E.DEPTNO
-- where JOB not in ('Manager', 'Salesman')
-- GROUP BY D.DEPTNO, D.DEPTNAME, E.JOB
-- ORDER BY D.DEPTNO, E.JOB;
select JOB, avg(SALARY) from EMP where DEPTNO= 20 and avg(SALARY) group by JOB;
