MySQL Interview common topics 3

36 Large
Fengtang

The spring is born,
Chun Lin First Sheng,
Spring Breeze Ten Li, inferior you.
Autumn Leaves,
Continuous rain
Sorrow Heart on Autumn, only for you.

The structure and data of an employee information table are as follows:
ID Name Dept Salary edlevel HireDate
1 Zhang Three Development Department 2000 3 2009-10-11
2 Li Si Development Department 2500 3 2009-10-01
3 Harry Design Department 2600 5 2010-10-02
4 Wangliuqi Design Department 2300 4 2010-10-03
5 MA Seven Design Department 2100 4 2010-10-06
6 Zhao Eight Sales Department 3000 5 2010-10-05
7 Money Nine Sales Department 3100 7 2010-10-07
8 Sun 10 Sales Department 3500 7 2010-10-06
For example, list the results of the highest salary for each department
SELECT Dept,max (Salary) ' maximum salary ' from the Users GROUP by dept;
For example, query the total number of salaries for each department
SELECT dept,sum (Salary) ' total salary ' from the Users GROUP by dept;
For example, look for the highest salary in each level of the company's entry after 2010
SELECT Dept,edlevel,hiredate,max (Salary) ' highest pay ' from the users WHERE hiredate >= ' 2010-01-01 ' GROUP by Dept,edlevel;
SELECT Dept,edlevel,hiredate,max (Salary) ' highest salary ' from the users GROUP by Dept,edlevel have hiredate >= ' 2010-01-01 ';
For example: Looking for the highest and lowest salary of a department with more than 2 employees
SELECT Dept,max (Salary) ' Maximum Salary ', min (salary) ' minimum wage ', count (1) ' Number of departments ' from the Users GROUP by dept have count (1) > 2;
For example: Finding the highest and lowest salary for a department with an employee's average wage greater than 3000
SELECT Dept,max (Salary) ' Maximum Salary ', min (salary) ' minimum wage ', AVG (salary) ' average salary ' from users GROUP by dept have avg (salary) > 3000 ;
SELECT Dept,max (Salary) ' Maximum Salary ', min (salary) ' minimum wage ', round (avg (Salary), 2) ' average salary ' from users GROUP by dept have average salary > 30 00;

MySQL Interview common topics 3