Oracle Multi-Table query and subquery exercises

--1. Lists all department and department information for at least three employees.
SELECT D.deptno, D.dname, D.loc, t.counts
From DEPT D, (SELECT DEPTNO, COUNT (*) COUNTS from EMP GROUP by DEPTNO) T
WHERE D.deptno = T.deptno
and T.counts >= 3;

--2. List the number, name, and department name of all employees whose employment date is earlier than the direct superior
SELECT E.empno, E.ename, D.dname
From EMP E, EMP M, DEPT D
WHERE e.mgr = M.empno
and E.hiredate < M.hiredate
and E.deptno = D.deptno;

--3. List the name of the employee whose position is "clerk" and the number of the department in which it is located:
SELECT e.ename,d.dname,t.counts
From EMP E, Dept D,
(SELECT deptno de, COUNT (*) counts from EMP GROUP by Deptno) t
WHERE E.deptno = d.deptno and E.deptno = t.de and e.job = ' clerk ';

--4. List all employees and department names that are in the same job as "SCOTT":
SELECT e.*, D.dname
From EMP E, DEPT D
WHERE E.deptno = D.deptno
and E.job = (SELECT JOB from EMP WHERE ename = ' SCOTT ');

--5. List the number of employees, average wages, and average service duration (in years) for each department's work
Select DEPTNO "department number",
Count (EMPNO) "Number of people",
ROUND (avg. SAL) "Average salary",
ROUND (AVG (Months_between (sysdate, HireDate)/12) "Service life"
From EMP
GROUP by DEPTNO;

--6, list the manager's minimum salary for each department:
SELECT Deptno, MIN (SAL) from the emp WHERE job = ' MANAGER ' GROUP by Deptno;

--7, giving a salary increase of 10% for persons who have been in office for more than 10 years;
SELECT e.*, E.sal * 1.1
From EMP E
WHERE (Months_between (Sysdate, HireDate))/> 10;

Oracle Multi-Table query and subquery exercises