SQL 第二次作业

  1. 计算各个部门的平均工资并排序,给出平均工资、部门名称
  2. 给出一个员工的生日、收入、部门名字

作业-20200418

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

SELECT 
    AVG(salary), dept_name
FROM
    salaries t1
        INNER JOIN
    (dept_emp t2, departments t3) ON t1.emp_no = t2.emp_no
        AND t2.dept_no = t3.dept_no
GROUP BY t3.dept_no
ORDER BY AVG(salary);

image

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

SELECT 
    birth_date, SUM(salary), dept_name
FROM
    employees t1
        INNER JOIN
    (salaries t2, dept_emp t3, departments t4) ON t1.emp_no = t3.emp_no
        AND t3.dept_no = t4.dept_no
        AND t1.emp_no = t2.emp_no
        AND t1.emp_no = 10001
GROUP BY dept_name;

image

1 Like

##作业1.计算各个部门的平均工资并排序(给出平均工资、部门名称)
因为工资里有每个员工不同时期的工资,所以选择了当前的(9999-01-01)薪资进行平均

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


##作业二给出一个员工的生日、收入、部门名字
(选择员工编号为10001的员工,查询他的当前薪资)
作业二

  1. 计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
	avg(s.salary) '平均工资',
	d.dept_name '部门名称'
FROM
	salaries s
JOIN dept_emp de ON s.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
GROUP BY
	d.dept_no
ORDER BY
	avg(s.salary);

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

  • 这里查询emp_no = 10001,为了更好的查看该员工的收入详情,特意多展示了员工编号、起始日期、截至日期,并对截至日志进行了一个排序
SELECT
	e.emp_no,
	e.birth_date,
	s.salary,
	d.dept_name,
	s.from_date,
	s.to_date
FROM
	employees e
JOIN (
	salaries s,
	dept_emp de,
	departments d
) ON e.emp_no = s.emp_no
AND s.emp_no = de.emp_no
AND de.dept_no = d.dept_no
WHERE
	e.emp_no = 10001
ORDER BY
	s.to_date DESC;

image

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

SELECT
	AVG(salary) AS '平均成绩',
	dept_name AS '部门名称'
FROM
	departments a
JOIN dept_emp b ON a.dept_no = b.dept_no
JOIN salaries c ON b.emp_no = c.emp_no
GROUP BY
	dept_name
ORDER BY
	AVG(salary) DESC;

image
2、给出一个员工的生日、收入、部门名字
①求和形式:

SELECT
	birth_date AS '生日',
	SUM(salary) AS '收入总和',
	dept_name AS '部门名称'
FROM
	employees a
JOIN salaries b ON a.emp_no = b.emp_no
JOIN dept_emp C ON B.emp_no = C.emp_no
JOIN departments d ON c.dept_no = d.dept_no
WHERE
	a.emp_no = 11111
GROUP BY
	dept_name;

image
②工资信息全部列出来

SELECT
	a.birth_date AS '生日',
	b.salary AS '收入',
	b.from_date AS '收入起始时间',
	b.to_date AS '收入截止时间',
	d.dept_name AS '部门名称'
FROM
	employees a
JOIN salaries b ON a.emp_no = b.emp_no
JOIN dept_emp C ON B.emp_no = C.emp_no
JOIN departments d ON c.dept_no = d.dept_no
WHERE
	a.emp_no = 11111
ORDER BY
	b.from_date DESC;

image

1 Like

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

select avg(salary) 平均工资, dept_name 部门名称
from salaries s 
join dept_emp d on s.emp_no=d.emp_no
join departments de on d.dept_no=de.dept_no
group by dept_name
order by avg(salary) desc;

image

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

select e.emp_no 员工工号, birth_date 生日, sum(salary) 总收入, dept_name 部门名称
from employees e
join salaries s on e.emp_no=s.emp_no
join dept_emp d on s.emp_no=d.emp_no
join departments de on d.dept_no=de.dept_no
group by e.emp_no, birth_date, dept_name
order by e.emp_no;

1 Like

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

作业1

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

作业2

作业

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

SQL命令

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

查询结果

平均工资

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

SQL命令

SELECT
	birth_date,
	salary,
	dept_name 
FROM
	employees e
	JOIN salaries s ON e.emp_no = s.emp_no
	JOIN dept_emp de ON s.emp_no = de.emp_no
	JOIN departments d ON de.dept_no = d.dept_no 
WHERE
	first_name LIKE 'pan%' 
	LIMIT 1;

查询结果

pan

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

SELECT d2.dept_name,AVG(s.salary) AS 平均工资
FROM salaries s
JOIN (dept_emp d1,departments d2)
ON s.emp_no =d1.emp_no
AND d1.dept_no = d2.dept_no
GROUP BY d2.dept_no
ORDER BY AVG(s.salary)

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

– 计算员工编号为10013的员工的总收入、并列示出生日、所属部门和员工编号
SELECT e.birth_date AS 生日,sum(s.salary) as 总收入,d1.dept_name AS 部门名称,e.emp_no as 员工编号
FROM employees e
JOIN (salaries s,departments d1,dept_emp d2)
ON e.emp_no=s.emp_no
AND d1.dept_no = d2.dept_no
AND e.emp_no =d2.emp_no
AND s.emp_no =d2.emp_no
WHERE e.emp_no =10013
GROUP BY d1.dept_no;

– 查询developments部门的工资最高的前十位员工信息

-表连接方式查询

SELECT s.*
FROM salaries s
JOIN (dept_emp d1,departments d2)
ON s.emp_no= d1.emp_no
AND d1.dept_no =d2.dept_no
WHERE d2.dept_name= ‘development’
ORDER BY s.salary DESC
LIMIT 10;

-子查询 方式:

SELECT DISTINCT(emp_no),salary,from_date,to_date
FROM salaries
WHERE emp_no in (
SELECT emp_no
FROM dept_emp
WHERE dept_no = (
SELECT dept_no
FROM departments
WHERE dept_name = ‘development’))
ORDER BY salary DESC
LIMIT 10;

– 查询developments部门的所有员工的员工编号和入职日期(使用表连接和子查询两种方式)

-子查询方式:

SELECT emp_no,hire_date
FROM employees
WHERE emp_no in(
SELECT emp_no FROM dept_emp WHERE dept_no = (
SELECT dept_no FROM departments WHERE dept_name=‘Development’));

-表连接方式:

SELECT e.emp_no,e.hire_date
FROM employees e
JOIN (dept_emp d1,departments d2)
ON e.emp_no =d1.emp_no
AND d1.dept_no =d2.dept_no
WHERE d2.dept_name =‘Development’;

1 Like

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

SELECT
AVG(salary) AS 平均薪资,
d.dept_name AS 部门名称
FROM
salaries s
JOIN dept_emp de ON s.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
GROUP BY
d.dept_name
ORDER BY
AVG(salary);

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

SELECT
e.birth_date AS 生日,
s.salary AS 收入,
d.dept_name AS 部门名字
FROM
employees e
JOIN salaries s ON e.emp_no = s.emp_no
JOIN dept_emp de ON s.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
WHERE
e.first_name = ‘Saniya’ AND e.last_name =‘Kalloufi’;

1、 计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
AVG(salary),
dept_name
FROM
salaries t1
INNER JOIN dept_emp t2 ON t1.emp_no = t2.emp_no
INNER JOIN departments t3 ON t2.dept_no = t3.dept_no
GROUP BY
t3.dept_no
ORDER BY
AVG(salary)
2、 给出一个员工的生日、收入、部门名字
SELECT
birth_date, SUM(salary), dept_name
FROM
employees t1
INNER JOIN
(salaries t2, dept_emp t3, departments t4) ON t1.emp_no = t3.emp_no
AND t3.dept_no = t4.dept_no
AND t1.emp_no = t2.emp_no
AND t1.emp_no = 10001
GROUP BY dept_name;

1 Like

SQL第二次作业

  • 计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
	dept_name 部门名称,
	avg(s.salary) 平均工资
FROM
	departments d
JOIN (dept_emp de, salaries s) ON d.dept_no = de.dept_no
AND de.emp_no = s.emp_no
GROUP BY
	d.dept_no
ORDER BY
	avg(s.salary);

image

  • 给出一个员工的生日、收入、部门名字
-- 查询员工编号为15000的员工的生日、收入、部门名字
SELECT
	birth_date 生日,
	SUM(s.salary) 收入,
	d.dept_name 部门名字
FROM
	employees e
JOIN (
	salaries s,
	dept_emp de,
	departments d
) ON e.emp_no = s.emp_no
AND e.emp_no = de.emp_no
AND de.dept_no = d.dept_no
WHERE
	e.emp_no = '15000'
GROUP BY
	d.dept_no;

课堂练习题

  • 查询developments部的工资最高的前十位员工信息

  • 查询developments部的所有员工的员工编号和入职日期(使用表连接和子查询两种方式进行演练)

使用子查询方式
SELECT
	emp_no 员工编号,
	hire_date 入职日期
FROM
	employees
WHERE
	emp_no IN (
		SELECT
			emp_no
		FROM
			dept_emp
		WHERE
			dept_no IN (
				SELECT
					dept_no
				FROM
					departments
				WHERE
					dept_name = 'Development'
			)
	);
使用表连接方式查询
SELECT
	e.emp_no 员工编号,
	hire_date 入职日期
FROM
	employees e
JOIN (departments d, dept_emp de) ON d.dept_no = de.dept_no
AND e.emp_no = de.emp_no
WHERE
	d.dept_name = 'Development';
1 Like

第一题就是要查询员工在公司里的平均工资哈,所以这边就不用指定时间了,按照员工编号分组算平均值就可以

第一题没有问题
第二题其实是要把一个员工的薪资进行一个整合,让一个员工的所有信息在一条里面显示出来

第一题没有问题
第二题需要让一个员工的所有信息在一条里面显示出来

第一题没有问题
第二题需要把员工的薪资信息整合一下

第三题现在的方式有重复信息,咱们在群里也说了如果要去掉重复信息的话,可以考虑通过计算员工的总薪资然后再排名的方式,可以试一下

第一题没有问题
第二题需要把员工的信息整合到一条记录当中

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

SQL命令

SELECT
	birth_date,
	sum( salary ),
	dept_name 
FROM
	employees e
	JOIN salaries s ON e.emp_no = s.emp_no
	JOIN dept_emp de ON s.emp_no = de.emp_no
	JOIN departments d ON de.dept_no = d.dept_no 
WHERE
	first_name LIKE 'pan%' 
GROUP BY
	s.emp_no
	LIMIT 1;
# 使用该命令需要关闭 ONLY_FULL_GROUP_BY 模式,否则会出现报错

命令执行

zuoye

1 Like
  • 已整合
SELECT
	e.emp_no,
	e.birth_date,
	sum(s.salary),
	d.dept_name
FROM
	employees e
JOIN salaries s ON e.emp_no = s.emp_no
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
GROUP BY
	e.emp_no,
	d.dept_name
ORDER BY
	e.emp_no

image

1 Like
关闭