Oracle classical syntax (IV)

1. The average, maximum, minimum salary, and number of people in each department are listed in ascending order by Department number.
SELECT deptno AS department number, AVG (sal) AS average salary, MAX (sal) AS highest salary, MIN (sal) AS minimum wage, COUNT (*) AS count FROM emp group by deptno order by deptno ASC;

2. The number of employees with salaries greater than 5000 in each department.
SELECT deptno, COUNT (*) FROM emp WHERE sal> 5000 group by deptno;

3. The average salary and number of people in each department are listed in ascending order by department name.
Select dname, AVG (SAL), COUNT (*) FROM (select dept. dname from dept where dept. DEPTNO = EMP. DEPTNO) DNAME, EMP. sal from emp) group by dname order by dname;
 
4. List the statistics of employees with the same salary in each department, and their department numbers, salaries, and numbers.
SELECT EMP1.DEPTNO, EMP1.SAL, COUNT (*) from emp EMP1, EMP EMP2
WHERE EMP1.DEPTNO = EMP2.DEPTNO
AND EMP1.SAL = EMP2.SAL
AND EMP1.EMPNO <> EMP2.EMPNO
Group by EMP1.DEPTNO, EMP1.SAL;

5. List the departments with more than 1000 employees in the same department. The Department name and region name are displayed.
SELECT
D. DNAME, D. LOC, COUNT (*)
From emp e, DEPT D
Where e. DEPTNO = D. DEPTNO AND
E. SAL> 1000
Group by d. DNAME, D. LOC
Having count (*)> 2;

6. Employees whose salaries are higher than the average salaries of the entire company list their names and salaries (in descending order ).
Select ename, SAL
FROM EMP
Where sal> (
Select avg (SAL)
FROM EMP
)
Order by sal desc;

7. The salaries of employees are between the average salaries of departments 10 and 30.
Select ename, SAL
FROM EMP
WHERE SAL
BETWEEN
(Select avg (SAL) FROM EMP
Where deptno = 10)
AND (select avg (SAL) FROM EMP
Where deptno = 80 );
SELECT * FROM EMP

8. Names of employees whose salaries are higher than 5000 on average in their respective departments.
Select ename, SAL
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
Having avg (SAL) & gt; 5000 );

9. List the employees with the highest salaries in each department: name, Department number, and salary.
SELECT ENAME
, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO, SAL) IN
(Select deptno, MAX (SAL)
FROM EMP
Group by deptno );

10. What is the average salary of the highest department.
Select max (AVGSALARY)
FROM (select deptno, AVG (SAL) AVGSALARY
FROM EMP
Group by deptno );