python测试开发班——SQL复杂查询课程贴

4. 查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

3. 取出部门的历任管理者

4. 计算各个部门的平均工资并排序,给出平均工资、部门名称

给出一个员工的生日、收入、部门名字

1、查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)
select * from employees where hire_date like ‘1998%’ order by hire_date limit 10;
select * from employees where hire_date like ‘1998%’ order by hire_date limit 9,6;

2、查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)
select emp_no,max(salary) from salaries where 1998 between year(from_date) and year(to_date) group by emp_no order by max(salary) desc limit 10;
select emp_no,max(salary) from salaries where 1998 between year(from_date) and year(to_date) group by emp_no order by max(salary) desc limit 9,11;

3、取出部门的历任管理者
select dept_name, from_date,to_date, em.* from departments de left join dept_manager dm on de.dept_no=dm.dept_no left join employees em on dm.emp_no=em.emp_no;

4、计算各个部门的平均工资并排序,给出平均工资、部门名称
select dept_name, avg(salary) from departments de left join dept_emp dp on de.dept_no=dp.dept_no left join salaries sa on sa.emp_no=dp.emp_no GROUP BY dept_name order by avg(salary);

5、给出一个员工的生日、收入、部门名字
select em.emp_no, birth_date, salary,dept_name from employees em left join salaries sa on em.emp_no=sa.emp_no left join dept_emp de on em.emp_no=de.emp_no
left join departments dep on de.dept_no=dep.dept_no where em.emp_no=10001 and year(sa.to_date)=9999;

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第11到15个呢)

SELECT
	emp_no,
	first_name,
	last_name 
FROM
	employees 
WHERE
	hire_date >= '1998-01-01' 
ORDER BY
	hire_date,
	emp_no 
	LIMIT 10;

SELECT
	emp_no,
	first_name,
	last_name 
FROM
	employees 
WHERE
	hire_date >= '1998-01-01' 
ORDER BY
	hire_date,
	emp_no 
	LIMIT 10,5;

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第11到20个呢)

SELECT
	employees.emp_no,
	first_name,
	last_name,
	salary 
FROM
	salaries
	INNER JOIN employees ON employees.emp_no = salaries.emp_no 
WHERE
	from_date >= '1998-01-01' 
	AND to_date < '1998-12-31' 
ORDER BY
	salary DESC 
	LIMIT 10,10;

取出部门的历任管理者

SELECT dp.dept_name,first_name,last_name,from_date,to_date from dept_manager ma
INNER JOIN employees en ON ma.emp_no = en.emp_no
INNER JOIN departments dp ON dp.dept_no = ma.dept_no 
WHERE
	to_date != '9999-01-01' 
ORDER BY
	dp.dept_no,
	from_date;

计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT
	dept_name,
	AVG( salary ) 
FROM
	salaries sa
	INNER JOIN dept_emp de ON sa.emp_no = de.emp_no
	INNER JOIN departments dp ON de.dept_no = dp.dept_no 
WHERE
	sa.to_date = '9999-01-01' 
GROUP BY
	de.dept_no 
ORDER BY
	AVG( salary ) DESC;

给出一个员工的生日、收入、部门名字

SELECT
	first_name,
	last_name,
	birth_date,
	salary,
	dept_name 
FROM
	employees em
	INNER JOIN dept_emp de ON em.emp_no = de.emp_no
	INNER JOIN salaries sa ON em.emp_no = sa.emp_no
	INNER JOIN departments dp ON de.dept_no = dp.dept_no 
WHERE
	sa.to_date = '9999-01-01' 
	AND em.emp_no = '43624'
1 Like

#查询所有在1998年入职的员工信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)
select * from employees where year(hire_date) = ‘1998’ order by hire_date asc limit 10 #前十个
select * from employees where year(hire_date) = ‘1998’ order by hire_date asc limit 9,6 #10-15个

#查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)
select * from salaries where from_date > ‘1998-01-01’ and to_date <‘1998-12-31’ order by salary desc limit 10 #前十个
select * from salaries where from_date > ‘1998-01-01’ and to_date <‘1998-12-31’ order by salary desc limit 9,10 #10-20个

#取出部门的历任管理者
select a.dept_name,c.title,d.emp_no,d.first_name,d.last_name from departments a
inner join dept_manager b on a.dept_no = b.dept_no
inner join titles c on c.emp_no = b.emp_no
inner join employees d on c.emp_no = d.emp_no where c.title = ‘Manager’

#计算各个部门的平均工资并排序,给出平均工资、部门名称
select departments.dept_name,avg(salary) from departments
inner join dept_emp on departments.dept_no = dept_emp.dept_no
inner join salaries on salaries.emp_no = dept_emp.emp_no group by departments.dept_name order by avg(salary)

#给出一个员工的生日、收入、部门名字
select a.emp_no,a.birth_date,b.salary,d.dept_name from employees a
inner join salaries b on a.emp_no = b.emp_no
inner join dept_emp c on a.emp_no = c.emp_no
inner join departments d on c.dept_no = d.dept_no

1 Like

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

select * from employees where hire_date between ‘1988-01-01’ and ‘1988-12-31’ order by hire_date,emp_no asc limit 10 offset 0;

select * from employees where hire_date between ‘1988-01-01’ and ‘1988-12-31’ order by hire_date,emp_no asc limit 5 offset 10;

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

select distinct emp_no from salaries where from_date between ‘1988-01-01’ and ‘1988-12-31’ or to_date between ‘1988-01-01’ and ‘1988-12-31’ group by emp_no order by salary desc limit 0,10
select distinct emp_no from salaries where from_date between ‘1988-01-01’ and ‘1988-12-31’ or to_date between ‘1988-01-01’ and ‘1988-12-31’ group by emp_no order by salary desc limit 9,11

取出部门的历任管理者

select d.,e. from employees e, dept_manager m, departments d where m.emp_no = e.emp_no and m.dept_no=d.dept_no group by d.dept_no,m.emp_no;

计算各个部门的平均工资并排序,给出平均工资、部门名称

select d.dept_no, d.dept_name ,avg(s.salary) avgSalary from departments d,salaries s, dept_emp p where s.emp_no = p.emp_no and p.dept_no=d.dept_no group by d.dept_no order by avgSalary desc;

给出一个员工的生日、收入、部门名字

select e.emp_no,e.birth_date,d.dept_name,s.salary from employees e, departments d,dept_emp p,salaries s where e.emp_no=p.emp_no and p.dept_no=d.dept_no and e.emp_no = s.emp_no;
如果指定某员工,再加一个条件

1 Like

1. 查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

select * from employees where hire_date like ‘1998%’ order by hire_date asc limit 10
select * from employees where hire_date like ‘1998%’ order by hire_date asc limit 10,5

result:

emp_no birth_date first_name last_name gender hire_date
55055 1953-11-10 Weijing Coors M 1998-01-01
87403 1958-04-18 Valeri Guerreiro M 1998-01-01
240026 1959-10-29 Sachem Mikschl F 1998-01-01
203297 1953-03-31 Kwok Ginneken M 1998-01-01
102554 1956-10-19 Moon Varley F 1998-01-01
23614 1957-02-17 Djenana Christ M 1998-01-01
67219 1962-08-24 Jeanna Niizuma M 1998-01-01
86409 1961-03-16 Conrado Dehkordi F 1998-01-01
59106 1962-10-31 Macha Rubsam F 1998-01-01
78567 1963-02-24 Gil Babu M 1998-01-01

2. 查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT * from salaries where from_date like ‘1998%’ order by salaries.salary desc limit 10
SELECT * from salaries where from_date like ‘1998%’ order by salaries.salary desc limit 10,10

result:

emp_no salary from_date end_date
109334 151484 1998-02-12 1999-02-12
43624 151115 1998-03-23 1999-03-23
66793 146281 1998-06-17 1999-06-17
80823 145077 1998-02-23 1999-02-23
102962 144416 1998-09-21 1999-09-21
98169 142350 1998-01-17 1999-01-17
47978 141563 1998-07-15 1999-07-15
37558 140821 1998-02-21 1999-02-21
46439 140407 1998-05-16 1999-05-16
18006 140109 1998-08-31 1999-08-31

3. 取出部门的历任管理者

select departments., employees. from dept_manager
LEFT JOIN employees on dept_manager.emp_no=employees.emp_no
LEFT JOIN departments on dept_manager.dept_no=departments.dept_no
order by dept_manager.dept_no

result:

dept_no dept_name emp_no birth_date first_name last_name gender hire_date
d001 Marketing 110022 1956-09-12 Margareta Markovitch M 1985-01-01
d001 Marketing 110039 1963-06-21 Vishwani Minakawa M 1986-04-12
d002 Finance 110085 1959-10-28 Ebru Alpin M 1985-01-01
d002 Finance 110114 1957-03-28 Isamu Legleitner F 1985-01-14
d003 Human Resources 110183 1953-06-24 Shirish Ossenbruggen F 1985-01-01
d003 Human Resources 110228 1958-12-02 Karsten Sigstam F 1985-08-04
d004 Production 110303 1956-06-08 Krassimir Wegerle F 1985-01-01
d004 Production 110344 1961-09-07 Rosine Cools F 1985-11-22
d004 Production 110386 1953-10-04 Shem Kieras M 1988-10-14
d004 Production 110420 1963-07-27 Oscar Ghazalie M 1992-02-05
d005 Development 110511 1957-07-08 DeForest Hagimont M 1985-01-01
d005 Development 110567 1964-04-25 Leon DasSarma F 1986-10-21
d006 Quality Management 110725 1961-03-14 Peternela Onuegbe F 1985-01-01
d006 Quality Management 110765 1954-05-22 Rutger Hofmeyr F 1989-01-07
d006 Quality Management 110800 1963-02-07 Sanjoy Quadeer F 1986-08-12
d006 Quality Management 110854 1960-08-19 Dung Pesch M 1989-06-09
d007 Sales 111035 1962-02-24 Przemyslawa Kaelbling M 1985-01-01
d007 Sales 111133 1955-03-16 Hauke Zhang M 1986-12-30
d008 Research 111400 1959-11-09 Arie Staelin M 1985-01-01
d008 Research 111534 1952-06-27 Hilary Kambil F 1988-01-31
d009 Customer Service 111692 1954-10-05 Tonny Butterworth F 1985-01-01
d009 Customer Service 111784 1956-06-14 Marjo Giarratana F 1988-02-12
d009 Customer Service 111877 1962-10-18 Xiaobin Spinelli F 1991-08-17
d009 Customer Service 111939 1960-03-25 Yuchang Weedman M 1989-07-10

4. 计算各个部门的 平均 工资 并排序,给出平均工资、部门名称

select d.dept_name, avg(s.salary) from employees e
LEFT JOIN dept_emp de on de.emp_no=e.emp_no
LEFT JOIN departments d on de.dept_no=d.dept_no
LEFT JOIN salaries s on s.emp_no=e.emp_no
GROUP BY d.dept_no
ORDER BY d.dept_no

result:

dept_name avg(s.salary)
Marketing 71901.7237
Finance 70159.4662
Human Resources 55353.5203
Production 59539.7899
Development 59503.5750
Quality Management 57294.6570
Sales 80776.6204
Research 59866.2435
Customer Service 58755.4406

5. 给出一个员工的生日、收入、部门名字

select e.emp_no, e.birth_date, s.salary,d.dept_name from employees e
LEFT JOIN salaries s on e.emp_no=s.emp_no
LEFT JOIN dept_emp de on de.emp_no=e.emp_no
LEFT JOIN departments d on de.dept_no=d.dept_no
where e.emp_no=10001

result:

e.emp_no e.birth_date s.salary d.dept_name
10001 1953-09-02 60117 Development
10001 1953-09-02 62102 Development
10001 1953-09-02 66074 Development
10001 1953-09-02 66596 Development
10001 1953-09-02 66961 Development
10001 1953-09-02 71046 Development
10001 1953-09-02 74333 Development
10001 1953-09-02 75286 Development
10001 1953-09-02 75994 Development
10001 1953-09-02 76884 Development
10001 1953-09-02 80013 Development
10001 1953-09-02 81025 Development
10001 1953-09-02 81097 Development
10001 1953-09-02 84917 Development
10001 1953-09-02 85112 Development
10001 1953-09-02 85097 Development
10001 1953-09-02 88958 Development
1 Like

4. 查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT *
FROM employees
WHERE hire_date >= '1998-01-01'
ORDER BY hire_date
LIMIT 3, 5;

4.1 查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT
  e.emp_no,
  concat(e.first_name, ' ', e.last_name) AS emp_name,
  s.salary
FROM salaries s
  LEFT JOIN employees.employees e ON e.emp_no = s.emp_no
WHERE s.from_date >= '1998-01-01'
      AND s.to_date <= '1998-12-31'
ORDER BY s.salary DESC
LIMIT 10;

3. 取出部门的历任管理者

SELECT
  d.dept_name,
  concat(e.first_name, ' ', e.last_name) AS manage_name,
  m.from_date,
  CASE WHEN m.to_date = '9999-01-01'
    THEN CURDATE()
  ELSE m.to_date
  END                                    AS to_date
FROM dept_manager m
  LEFT JOIN departments d ON m.dept_no = d.dept_no
  LEFT JOIN employees e ON m.emp_no = e.emp_no
ORDER BY dept_name, from_date;

4. 计算各个部门的平均工资并排序,给出平均工资、部门名称;

优化前 15s左右

SELECT
  d.dept_name,
  avg(s.salary) AS avg_salary
FROM dept_emp
  LEFT JOIN departments d ON dept_emp.dept_no = d.dept_no
  LEFT JOIN salaries s ON dept_emp.emp_no = s.emp_no
GROUP BY d.dept_no
ORDER BY avg(s.salary);

优化后 4s 左右

SELECT
  d.dept_name,
  b.avg AS avg_salary
FROM (SELECT
        dept_no,
        sum(a.salary) / sum(a.count) AS avg
      FROM dept_emp emp LEFT JOIN (SELECT
                                     sum(salary) AS salary,
                                     emp_no,
                                     count(1)    AS count
                                   FROM salaries
                                   GROUP BY emp_no) AS a ON
                                                           emp.emp_no = a.emp_no
      GROUP BY emp.dept_no
      ORDER BY sum(a.salary) / sum(a.count)) AS b LEFT JOIN departments d ON b.dept_no = d.dept_no;
1 Like

4. 查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT * FROM employees WHERE hire_date BETWEEN '1998-01-01' and '1998-12-31' ORDER BY hire_date asc LIMIT 9,6;

image

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT DISTINCT salaries.* FROM salaries WHERE from_date >= '1998-01-01' AND to_date <='1998-12-31' ORDER BY salary DESC LIMIT 9,11;

image

3. 取出部门的历任管理者

SELECT dept_name,dept_manager.* FROM dept_manager LEFT JOIN employees ON employees.emp_no=dept_manager.emp_no LEFT JOIN departments ON departments.dept_no=dept_manager.dept_no;

image

4. 计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT avg(salaries.salary),departments.dept_name FROM departments LEFT JOIN dept_emp ON departments.dept_no=dept_emp.dept_no LEFT JOIN salaries ON salaries.emp_no=dept_emp.emp_no GROUP BY departments.dept_no ORDER BY avg(salaries.salary) DESC;

image

给出一个员工的生日、收入、部门名字

SELECTemployees.emp_no,employees.birth_date,salaries.salary,departments.dept_name FROM employees INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no INNER JOIN departments ON departments.dept_no=dept_emp.dept_no INNER JOIN salaries ON salaries.emp_no=employees.emp_no WHERE employees.emp_no='10001';

image

1 Like

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要10到15个)

SELECT
	emp_no,
	first_name,
	last_name 
FROM
	employees 
WHERE
	hire_date >= '1998-01-01' 
ORDER BY
	hire_date ASC 
	LIMIT 10;

SELECT
	emp_no,
	first_name,
	last_name
FROM
	employees 
WHERE
	hire_date >= '1998-01-01' 
ORDER BY
	hire_date ASC 
	LIMIT 9,6;

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT
	emp_no,
	salary 
FROM
	salaries 
WHERE
	from_date >= '1998-01-01' 
	AND to_date <= '1998-12-31' 
ORDER BY
	salary DESC 
	LIMIT 9,
	10;

取出部门的历任管理者

SELECT
	m.emp_no 
FROM
	dept_manager m
	INNER JOIN departments d ON m.dept_no = d.dept_no

计算各个部门的平均工资并排序,给出平均工资、部门名称

PS:sql执行顺序 -from>join>on>where>group by>Max等聚集函数>having>select>distinct >order by>limit。 :joy:因为顺序的时候踩了坑,order by的时候salary的时候没有求平均,一直报错,经查阅是select 这里salary平均了,所以在order by这不查不到salary字段

SELECT
	dept_name,
	AVG( salary ) 
FROM
	salaries s
	INNER JOIN dept_emp de ON s.emp_no = de.emp_no
	INNER JOIN departments d ON de.dept_no = d.dept_no 
GROUP BY
	de.dept_no 
ORDER BY
	AVG( salary ) DESC;

给出一个员工的生日、收入、部门名字

SELECT
	e.first_name,
	e.last_name,
	e.birth_date,
	s.salary,
	d.dept_name 
FROM
	employees e
	INNER JOIN dept_emp de ON e.emp_no = de.emp_no
	INNER JOIN salaries s ON e.emp_no = s.emp_no
	INNER JOIN departments d ON de.dept_no = d.dept_no 
WHERE
	e.emp_no = 10013;
1 Like

#查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT * from employees where hire_date BETWEEN '1998-01-01' AND '1998-12-31' 
ORDER BY hire_date ASC LIMIT 10

SELECT * from employees where hire_date BETWEEN '1998-01-01' AND '1998-12-31' 
ORDER BY hire_date ASC LIMIT 10,5

#查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT t1.emp_no,t1.birth_date,t1.first_name,t1.last_name,t1.gender,t1.hire_date,t2.salary FROM employees t1 
RIGHT JOIN salaries t2 ON t1.emp_no = t2.emp_no 
WHERE t1.hire_date BETWEEN '1998-01-01' AND '1998-12-31' 
ORDER BY t2.salary DESC LIMIT 10

SELECT DISTINCT t1.emp_no,t1.birth_date,t1.first_name,t1.last_name,t1.gender,t1.hire_date,t2.salary FROM employees t1 
RIGHT JOIN salaries t2 ON t1.emp_no = t2.emp_no 
WHERE t1.hire_date BETWEEN '1998-01-01' AND '1998-12-31' 
ORDER BY t2.salary DESC LIMIT 10,10

#取出部门的历任管理者

SELECT t1.emp_no,t1.first_name,t1.last_name,t3.dept_name FROM employees t1 
RIGHT JOIN dept_manager t2 on t1.emp_no = t2.emp_no 
LEFT JOIN departments t3 on t2.dept_no = t3.dept_no

#计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT t2.dept_name, AVG(t3.salary) as '平均工资' FROM 
dept_emp t1,departments t2,salaries t3 
where t1.dept_no = t2.dept_no and t3.emp_no = t1.emp_no 
GROUP BY t2.dept_name 

#给出一个员工的生日、收入、部门名字

SELECT t1.emp_no,t1.first_name,t1.last_name,t1.birth_date,t3.dept_name,t4.salary FROM employees t1
LEFT JOIN dept_emp t2 ON t1.emp_no = t2.emp_no
LEFT JOIN departments t3 ON t2.dept_no = t3.dept_no
LEFT JOIN salaries t4 ON t4.emp_no = t1.emp_no
LIMIT 1
1 Like

1. 查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

select * from employees where hire_date BETWEEN '1998-01-01' and '1998-12-31' ORDER BY hire_date, emp_no LIMIT 0, 15;
select * from employees where hire_date BETWEEN '1998-01-01' and '1998-12-31' ORDER BY hire_date, emp_no LIMIT 10, 5;

2. 查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT
	* 
FROM
	salaries 
WHERE
	( from_date BETWEEN '1998-01-01' AND '1998-12-31' ) 
	OR ( to_date BETWEEN '1998-01-01' AND '1998-12-31' ) 
	OR ( to_date > '1998-12-31' AND from_date < '1998-01-01' ) ORDER BY salary desc LIMIT 0, 10;

SELECT
	* 
FROM
	salaries 
WHERE
	( from_date BETWEEN '1998-01-01' AND '1998-12-31' ) 
	OR ( to_date BETWEEN '1998-01-01' AND '1998-12-31' ) 
	OR ( to_date > '1998-12-31' AND from_date < '1998-01-01' ) ORDER BY salary desc LIMIT 10, 10;

3. 取出部门的历任管理者

SELECT
	dp.dept_name,
	CONCAT( em.first_name, ' ', em.last_name ) NAME,
	dm.from_date 
FROM
	dept_manager dm
	JOIN employees em ON dm.emp_no = em.emp_no
	JOIN departments dp ON dm.dept_no = dp.dept_no 
ORDER BY
	dm.from_date;

4. 计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT
	dp.dept_no, dp.dept_name, AVG(sa.salary) avgSalary
FROM
	salaries sa
	JOIN dept_emp de ON de.emp_no = sa.emp_no
	JOIN departments dp ON dp.dept_no = de.dept_no 
WHERE
	sa.to_date = '9999-01-01' GROUP BY dept_no ORDER BY avgSalary;

5.给出一个员工的生日、收入、部门名字

SELECT
	em.emp_no,
	em.birth_date,
	dp.dept_name,
        sa.salary,
	CONCAT(sa.from_date,'/',sa.to_date) date
FROM
	employees em
	JOIN dept_emp de ON em.emp_no = de.emp_no
	JOIN departments dp ON de.dept_no = dp.dept_no
	JOIN salaries sa on em.emp_no = sa.emp_no where em.emp_no='10010';
1 Like

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT * from employees where hire_date like '1988%' order by hire_date LIMIT 10;
SELECT * from employees where hire_date like '1988%' order by hire_date LIMIT 9,6;

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT emp_no '员工ID',AVG(salary) '平均工资' from salaries where from_date 
like '%1988%' or to_date like '%1988%' GROUP BY emp_no ORDER BY avg(salary) DESC LIMIT 10;
SELECT emp_no '员工ID号',AVG(salary) '平均工资' from salaries where from_date 
like '%1988%' or to_date like '%1988%' GROUP BY emp_no ORDER BY avg(salary) DESC LIMIT 9,11;

取出部门的历任管理者

SELECT
	d.dept_no '部门ID',
	d.dept_name '部门名称',
	dm.emp_no '员工ID',
	e.first_name '员工名称',
	dm.from_date '任职日期',
	dm.to_date '离任日期'
FROM
	dept_manager dm
LEFT JOIN employees e ON dm.emp_no = e.emp_no
LEFT JOIN departments d ON dm.dept_no = d.dept_no
ORDER BY
	dm.dept_no,
	dm.from_date;

图片

计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT d.dept_name '部门名称',avg(s.maxs) '平均工资' from dept_emp de RIGHT JOIN (
SELECT emp_no,max(salary) maxs from salaries GROUP BY emp_no) s
on de.emp_no=s.emp_no LEFT JOIN departments d on de.dept_no=d.dept_no
GROUP BY de.dept_no ORDER BY avg(s.maxs);

图片

给出一个员工的生日、收入、部门名字

SELECT e.birth_date '生日',s.nowsalary '薪资',d.dept_name '部门名称' from employees e 
LEFT JOIN dept_emp de on e.emp_no=de.emp_no
LEFT JOIN departments d on de.dept_no=d.dept_no
LEFT JOIN (SELECT max(salary) nowsalary,emp_no from salaries GROUP BY emp_no) s on e.emp_no=s.emp_no
where e.first_name='员工名称' or e.last_name='员工名称' or e.emp_no='员工ID';
1 Like

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT
	* 
FROM
	employees 
WHERE
	YEAR ( hire_date )= '1998' 
ORDER BY
	hire_date,
	emp_no 
	LIMIT 10;
SELECT
	* 
FROM
	employees 
WHERE
	YEAR ( hire_date )= '1998' 
ORDER BY
	hire_date,
	emp_no 
	LIMIT 5 OFFSET 10;

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT
	emp_no 
FROM
	salaries 
WHERE
	YEAR ( from_date )<= '1998' 
	AND YEAR ( to_date )>= '1998' 
ORDER BY
	salary DESC 
	LIMIT 10;
SELECT
	emp_no 
FROM
	salaries 
WHERE
	YEAR ( from_date )<= '1998' 
	AND YEAR ( to_date )>= '1998' 
ORDER BY
	salary DESC 
	LIMIT 11 OFFSET 9;

取出部门的历任管理者

SELECT
	e.emp_no,
	e.first_name,
	e.last_name,
	de.dept_name,
	dm.from_date,
	dm.to_date
FROM
	employees e
	JOIN dept_manager dm ON dm.emp_no = e.emp_no
	JOIN departments de ON dm.dept_no = de.dept_no;

计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT
	avg( s.salary ),
	dm.dept_name 
FROM
	departments dm
	JOIN dept_emp de ON dm.dept_no = de.dept_no
	JOIN salaries s ON de.emp_no = s.emp_no 
WHERE
	YEAR ( s.to_date )= '9999' 
GROUP BY
	dm.dept_name 
ORDER BY
	avg( s.salary );

给出一个员工的生日、收入、部门名字

SELECT
	e.emp_no,
	e.first_name,
	e.last_name,
	e.birth_date,
	s.salary,
	dm.dept_name 
FROM
	employees e
	JOIN dept_emp de ON e.emp_no = de.emp_no
	JOIN departments dm ON de.dept_no = dm.dept_no
	JOIN salaries s ON e.emp_no = s.emp_no 
WHERE
	e.emp_no = '10001' 
ORDER BY
	s.to_date DESC 
	LIMIT 1;
1 Like

– 1.查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT * FROM employees WHERE hire_date BETWEEN '1998-01-01' AND '1998-12-13' ORDER BY hire_date ASC LIMIT 15;
SELECT * FROM employees WHERE hire_date BETWEEN '1998-01-01' AND '1998-12-13' ORDER BY hire_date ASC LIMIT 9, 6; 

– 2.查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

 SELECT * FROM salaries WHERE from_date BETWEEN '1998-01-01' AND '1998-12-13' ORDER BY salary DESC LIMIT 15;
SELECT * FROM salaries WHERE from_date BETWEEN '1998-01-01' AND '1998-12-13' ORDER BY salary DESC LIMIT 9, 11;

– 3. 取出部门的历任管理者

SELECT c.dept_no, c.dept_name, a.* FROM employees a 
RIGHT  JOIN dept_manager b ON a.emp_no = b.emp_no  
LEFT JOIN departments c ON b.dept_no = c.dept_no

– 4. 计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT c.dept_no, c.dept_name, AVG(a.salary) FROM salaries a 
LEFT JOIN  dept_emp b ON a.emp_no = b.emp_no 
LEFT JOIN departments c ON b.dept_no = c.dept_no GROUP BY c.dept_no ORDER BY AVG(a.salary)

– 5. 给出一个员工的生日、收入、部门名字

SELECT a.birth_date, b.salary, d.dept_name FROM employees a 
LEFT JOIN salaries b ON a.emp_no = b.emp_no 
LEFT JOIN dept_emp c ON b.emp_no = c.emp_no 
LEFT JOIN departments d ON c.dept_no = d.dept_no where a.emp_no = '10001'
1 Like

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)
select * from employees where hire_date BETWEEN ‘1998-1-1’ AND ‘1998-12-31’ ORDER BY hire_date ASC LIMIT 10;
select * from employees where hire_date BETWEEN ‘1998-1-1’ AND ‘1998-12-31’ ORDER BY hire_date ASC LIMIT 9,6;

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)
select * from salaries where from_date>=‘1998-1-1’ AND to_date<=‘1998-12-31’ order by salary DESC limit 10;
select * from salaries where from_date>=‘1998-1-1’ AND to_date<=‘1998-12-31’ order by salary DESC limit 9,11;

取出部门的历任管理者
select employees.,departments. from dept_manager left join departments on dept_manager.dept_no=departments.dept_no
LEFT JOIN employees ON dept_manager.emp_no=employees.emp_no;

计算各个部门的平均工资并排序,给出平均工资、部门名称
select avg(salaries.salary),departments.dept_name from departments left join dept_emp on departments.dept_no=dept_emp.dept_no
left join salaries on salaries.emp_no=dept_emp.emp_no GROUP BY departments.dept_name order by avg(salaries.salary) DESC;

给出一个员工的生日、收入、部门名字
select employees.emp_no,employees.birth_date,salaries.salary,departments.dept_name from employees left join salaries on employees.emp_no=salaries.emp_no
left join dept_emp on salaries.emp_no=dept_emp.emp_no
left join departments on departments.dept_no=dept_emp.dept_no where employees.emp_no=‘10029’ ORDER BY salaries.salary DESC;

1 Like
#查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工。
SELECT * FROM employees WHERE hire_date BETWEEN "1998-01-01" AND "1998-12-31" ORDER BY hire_date ASC LIMIT 10;
#查询所有在1998年入职的信息,按入职时间从早到晚排序,只要第10到15个。
SELECT * FROM employees WHERE hire_date BETWEEN "1998-01-01" AND "1998-12-31" ORDER BY hire_date ASC LIMIT 9,6;
#查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工。
SELECT * FROM salaries WHERE from_date BETWEEN "1998-01-01" AND "1998-12-31" ORDER BY salary DESC LIMIT 10;
#查询1998年所有的工资信息,按工资从高到低排序,只要第10到20个。
SELECT * FROM salaries WHERE from_date BETWEEN "1998-01-01" AND "1998-12-31" ORDER BY salary DESC LIMIT 9,11;
#取出部门的历任管理者
SELECT
	ep.emp_no,
	ep.first_name,
	ep.last_name,
	dp_m.dept_no
FROM
	employees AS ep
INNER JOIN dept_manager AS dp_m ON ep.emp_no = dp_m.emp_no;
#计算各个部门的平均工资并排序,给出平均工资及部门名称。写法有多种。
SELECT
	dp.dept_name,
	sal_dept.dep_sal_avg
FROM
	(
		SELECT
			new_sl.emp_no AS emp_no,
			dept_emp.dept_no AS dept_no,
			SUM(new_sl.emp_sum_sal) / SUM(new_sl.emp_cnt) AS dep_sal_avg
		FROM
			dept_emp
		LEFT JOIN (
			SELECT
				emp_no,
				SUM(salary) AS emp_sum_sal,
				COUNT(emp_no) AS emp_cnt
			FROM
				salaries
			GROUP BY
				emp_no
		) AS new_sl ON dept_emp.emp_no = new_sl.emp_no
		GROUP BY
			dept_emp.dept_no
		ORDER BY
			dep_sal_avg DESC
	) AS sal_dept
LEFT JOIN departments AS dp ON sal_dept.dept_no = dp.dept_no;

#给出一个员工的生日、收入、部门名字
SELECT
	emp.emp_no,
	emp.hire_date,
	sal.salary
FROM
	salaries AS sal
INNER JOIN (
	SELECT
		employees.emp_no,
		employees.hire_date,
		departments.dept_name
	FROM
		employees
	INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
	INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
	WHERE
		employees.emp_no = "10002"
) AS emp ON sal.emp_no = emp.emp_no;
1 Like

查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

SELECT 
	employees.* ,dept_emp.from_date
FROM 
	employees
LEFT JOIN
	dept_emp
ON
	employees.emp_no=dept_emp.emp_no
WHERE
	from_date BETWEEN '1998-01-01' AND '1998-12-31'
ORDER BY
	dept_emp.from_date ASC
LIMIT
	10,5

查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT 
	*
FROM
	salaries
WHERE
	from_date >= '1998-01-01' AND to_date <= '1998-12-31'	
ORDER BY
	salary DESC
LIMIT
	10,10

取出部门的历任管理者

SELECT 
	departments.dept_name,employees.*
	dept_manager
LEFT JOIN
	employees
ON
	dept_manager.emp_no=employees.emp_no
LEFT JOIN
	dept_emp
ON
	employees.emp_no=dept_emp.emp_no
LEFT JOIN
	departments
ON
	dept_emp.dept_no=departments.dept_no

计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT
	departments.dept_name,AVG(salary)
FROM
	salaries
LEFT JOIN
	dept_emp
ON 
	dept_emp.emp_no=salaries.emp_no
LEFT JOIN
	departments
ON
	departments.dept_no=dept_emp.dept_no
GROUP BY
	dept_emp.dept_no
ORDER BY
	AVG(salary) DESC

给出一个员工的生日、收入、部门名字

SELECT
	DISTINCT employees.emp_no,employees.birth_date,salaries.salary,departments.dept_name
FROM
	employees
LEFT JOIN
	salaries
ON
	employees.emp_no=salaries.emp_no
LEFT JOIN
	dept_emp
ON
	dept_emp.emp_no=employees.emp_no
LEFT JOIN
	departments
ON
	departments.dept_no=dept_emp.dept_no
WHERE 
	salaries.to_date='9999-01-01'
1 Like

1.查询所有在1998年入职的信息,按入职时间从早到晚排序,只要当年前十个新入职的员工(只要第10到15个呢)

//使用between and
SELECT  *   FROM  employees  WHERE  hire_date  BETWEEN '1998-01-01'   AND '1998-12-31'  ORDER BY hire_date  ASC   LIMIT  10;
//不使用between  and
SELECT  *   FROM  employees  WHERE  hire_date  >= '1998-01-01'  AND hire_date <= '1998-12-31'  ORDER BY hire_date  ASC   LIMIT  10;
//使用limit n m
SELECT  *   FROM  employees  WHERE  hire_date  BETWEEN '1998-01-01'   AND '1998-12-31'  ORDER BY hire_date  ASC   LIMIT  9,6;
//使用limit  n  offset m
SELECT  *   FROM  employees  WHERE  hire_date  BETWEEN '1998-01-01'   AND '1998-12-31'  ORDER BY hire_date  ASC   LIMIT  6 OFFSET 9;

2.查询1998年所有的工资信息,按工资从高到低排序,只要当年工资最高的前十个员工(只要第10到20个呢)

SELECT  salary  FROM   salaries  WHERE  from_date  BETWEEN '1998-01-01'   AND '1998-12-31'  ORDER BY salary DESC   LIMIT  10;
SELECT  salary  FROM   salaries  WHERE  from_date  BETWEEN '1998-01-01'   AND '1998-12-31'  ORDER BY salary DESC   LIMIT  9,11;

3.取出部门的历任管理者

SELECT   m.emp_no,d.dept_name  FROM  dept_manager m  
LEFT JOIN  employees  e ON    e.emp_no=m.emp_no
LEFT JOIN  departments  d ON  d.dept_no=m.dept_no;

4.计算各个部门的平均工资并排序,给出平均工资、部门名称

SELECT d.dept_no, d.dept_name, AVG(s.salary) FROM salaries s 
LEFT JOIN  dept_emp e ON s.emp_no = e.emp_no 
LEFT JOIN departments d ON e.dept_no = d.dept_no
GROUP BY d.dept_no ORDER BY AVG(s.salary)

5.给出一个员工的生日、收入、部门名字

SELECT   e.birth_date,d.dept_name,s.salary  FROM  employees  e
LEFT JOIN  dept_emp m   ON    e.emp_no=m.emp_no 
LEFT JOIN  departments d  ON   d.dept_no=m.dept_no
LEFT JOIN  salaries  s  ON     s.emp_no=e.emp_no
WHERE  e.emp_no='10001';
1 Like

优化后的sql确实能快挺多,棒

实践学的更多,总结的很好

关闭