MySQL Study day fourth (advanced query)

--Seventh Chapter
--1, check the earliest and latest date of entry date
Select min (hiredate), Max (HireDate)
From EMP

--2. Check the average salary of all employees starting with sales, minimum wage, most
-high wages, wages and
Select AVG (sal), Min (Sal), Max (SAL), sum (SAL)
From EMP
Where job like ' sales% '

--3, Query department 30 how many employees
Select COUNT (*)
From EMP
where deptno = ' 30 '

--4, the number of departments to inquire about employees
Select COUNT (Distinct deptno)
From EMP

--5, the average value of the prize (including those without bonuses)
Select AVG (ifnull (comm,0))
From EMP

--Exercise 1
--1.20 of the employees in the enquiry department, the sum of each month's wages and the average salary
Select sum (SAL), avg (SAL)
From EMP
where Deptno = 20
--2. The number of employees in Chicago, the maximum wage and the minimum wage
Select COUNT (*), Max (Sal), Min (sal)
From Emp,dept
where Emp.deptno = Dept.deptno
and loc = ' CHICAGO '
---3, query employee table in a total of several types of jobs
Select COUNT (Distinct job)
From EMP

--6, check the number of each department, average salary
Select Deptno,avg (SAL)
From EMP
GROUP BY Deptno

--7, the sum of wages for each job
Select Deptno,job,sum (SAL)
From EMP
GROUP BY Deptno,job

-1, the department number of each department, department name, department number, the maximum wage,
-minimum wage, sum of wages, average wage
Select Emp.deptno,dname,count (*), Max (Sal), Min (sal), sum (SAL), avg (SAL)
From Emp,dept
where Emp.deptno = Dept.deptno
GROUP BY Emp.deptno,dname
--2, query each department, department number of each post, department name,
--Job title, number of departments, maximum wage, minimum wage, sum of wages,
--Average salary
Select Emp.deptno,dname,job,count (*), Max (Sal), Min (sal), sum (SAL), avg (SAL)
From Emp,dept
where Emp.deptno = Dept.deptno
GROUP BY Emp.deptno,dname,job
---3. Query the number of people managed by each manager, manager number, manager name,
-Requirements include information on people who have not experienced
Select COUNT (*), T1.mgr,t2.ename
from EMP T1
Left JOIN EMP T2
On t1.mgr = T2.empno
GROUP BY T1.mgr,t2.ename

--8. Check the department number of each department with a salary greater than 2900, the maximum wage
Select Deptno,max (SAL)
From EMP
GROUP BY Deptno
Having Max (SAL) > 2900

--9, the query is not the beginning of sales, the sum of wages greater than 5000 of the job name and
--The sum of wages in ascending order of the sum of wages
Select Job,sum (SAL)
From EMP
Where job isn't like ' sales% '
GROUP BY Job
Having sum (SAL) > 5000
ORDER by sum (SAL)

--Exercise 3
--1, the number of departments to inquire more than 2 department number, department name, number of departments
Select Dept.deptno,dname,count (*)
From Dept,emp
where Dept.deptno = Emp.deptno
GROUP BY Dept.deptno,dname
Having count (*) > 2
--2, the department number of the department with the average salary greater than 2000, and the number greater than 2
--Department name, number of departments, average salary of department, and in ascending order by department number
Select Dept.deptno,dept.dname,count (*), AVG (SAL)
From Dept,emp
where Dept.deptno = Emp.deptno
GROUP BY Dept.deptno,dept.dname
Having avg (SAL) > 2000
and COUNT (*) >2
Order BY Count (*)

--10. Identify other employees who are higher than Jones ' salary
SELECT *
From EMP
Where Sal > (select Sal
From EMP
where ename = ' JONES ')

---11. The name and work of employees 7369 who are engaged in the same job and pay more than 7876 of the employees
Select Ename,job
From EMP
where job = (SELECT Job
From EMP
where empno = 7369)
and Sal > (select Sal
From EMP
where empno = 7876)

--12. Check the employee's name, job and salary at the lowest wage
Select Ename,job,sal
from emp
WHERE sal = (select min (sal)
from EMP)
--13, query Department number and minimum wage for departmental minimum wage higher than 20 department minimum wage
Select Deptno,min (SAL)
from the EMP
Group by Deptno
have min (sal) > (select Min (sal)
from emp
where deptno =)

--Exercise 4
--1, check the earliest entry date employee name, date of entry
Select Ename,hiredate
from emp
WHERE hiredate = (select min (hiredate
from EMP)
--2, query the employee's name, salary, and department name
Select Ename,sal,dname
from Emp,dept
for salary higher than Smith's salary and work place in Chicago where Emp.deptno = Dept.deptno
and loc = ' CHICAGO '
and sal > (select Sal
from emp
where ename = ' SMITH ')
--3. Employee Name, entry date
Select min (hiredate)
from EMP
GROUP by Deptno
have min (for employees who have entered the date earlier than the 20-door entry date) HireDate) < (select min (hiredate)
from emp
where deptno =)
--4, the number of departments to inquire more than the average number of departments of the department number, department name, number of departments
Select Dept.deptno,dept.dname,count (*)
from emp,dept
where Emp.deptno = Dept.deptno
Group BY Deptno
Have count (*) > (select COUNT (*)/count (distinct deptno)
from EMP)

Select Dept.deptno,dept.dname,count (*)
From Emp,dept
where Emp.deptno = Dept.deptno
GROUP BY Deptno
Having count (*) > (select AVG (t.count)
From (select COUNT (*) count
From EMP
GROUP by Deptno) T)

--14. The name of the employee who is experienced, the salary
SELECT DISTINCT T2.ename,t2.sal
From EMP t1,emp T2
where t1.mgr = T2.empno

Select Ename,sal
From EMP
where Empno in (select Mgr
From EMP)

---15, the query department number is not 10, and wages than the 10 department of any one employee salary high employee number, name, position, salary
Select Empno,ename,job,sal
From EMP
where Deptno <> 10
and Sal >any (select Sal
From EMP
where Deptno = 10)

---16, the query department number is not 20, and wages than the 20 department of all employees pay high employee number, name, position, salary
Select Empno,ename,job,sal
From EMP
where Sal > All (select Sal
From EMP
where Deptno = 20)
and Deptno <> 20

--Exercise 5
---1. Check the employee's name and date of entry on the date of entry for any employee of the 10 department, not including 10 department staff
Select Ename,hiredate
From EMP
Where HireDate <any (select HireDate
From EMP
where Deptno = 10)
and Deptno <> 10
---2. Check the employee's name, entry date, and not including 10 department staff for the entry date of all employees in the 10 department.
Select Ename,hiredate
From EMP
Where HireDate <all (select HireDate
From EMP
where Deptno = 10)
and Deptno <> 10
---3, search jobs and 10 departments of any one employee position the same employee name, position, not including 10 Department of Staff
Select Ename,job
From EMP
where job in (select Job
From EMP
where Deptno = 10)
and Deptno <> 10

---17, the name of the employee who is not the manager
Select Ename
From EMP
where Empno not in (select Mgr
From EMP
Where Mgr is not NULL)

---18. The employee's name, salary, department number and the average salary of the department are higher than the average salary.
Select Ename,sal,emp.deptno,avgsal
From EMP, (select Deptno,avg (SAL) avgsal
From EMP
GROUP by Deptno) T
where Emp.deptno = T.deptno
and Emp.sal > T.avgsal

Select Ename,sal,deptno
From EMP t
Where Sal > (select AVG (SAL)
From EMP
where deptno = T.deptno)

Select Ename,sal,deptno, (select AVG (SAL)
From EMP
where deptno = T.deptno)
From EMP t
Where Sal > (select AVG (SAL)
From EMP
where deptno = T.deptno)

--Check the number of employee names and departments in which they are queried
Select Ename,c
From EMP, (select Deptno,count (*) c
From EMP
GROUP by Deptno) T
where Emp.deptno = T.deptno

Select Ename, (select COUNT (*)
From EMP
where Emp.deptno = T.deptno
Group BY Emp.deptno)
From EMP t

--query is the lowest wage employee information in the department
SELECT *
From EMP
where (Deptno,sal) in (select Deptno,min (SAL)
From EMP
Group BY Deptno)

SELECT *
From EMP, (select Deptno,min (SAL) minsal
From EMP
GROUP by Deptno) T
where Emp.deptno = T.deptno
and Sal = Minsal

SELECT *
From EMP t
Where Sal = (select min (sal)
From EMP
where Deptno = T.deptno
Group BY Deptno)

MySQL Study day fourth (advanced query)