SQL语句课后问题修改

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for staff
-- ----------------------------
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `NAME` varchar(50) NOT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `job` varchar(20) DEFAULT NULL COMMENT '职位',
  `salary` int(11) DEFAULT NULL COMMENT '薪资',
  `entrydate` date DEFAULT NULL COMMENT '入职时间',
  `managerid` int(11) DEFAULT NULL COMMENT '直属领导ID',
  `dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='员工表';

-- ----------------------------
-- Records of staff
-- ----------------------------
INSERT INTO `staff` VALUES ('1', '金庸', '66', '总裁', '20000', '2000-01-01', null, '5');
INSERT INTO `staff` VALUES ('2', '张无忌', '20', '项目经理', '12500', '2005-12-05', '1', '1');
INSERT INTO `staff` VALUES ('3', '杨逍', '33', '开发', '8400', '2000-11-03', '2', '1');
INSERT INTO `staff` VALUES ('4', '韦一笑', '48', '开发', '11000', '2002-02-05', '2', '1');
INSERT INTO `staff` VALUES ('5', '常遇春', '43', '开发', '10500', '2004-09-07', '3', '1');
INSERT INTO `staff` VALUES ('6', '小昭', '19', '程序员鼓励师', '6600', '2004-10-12', '2', '1');
INSERT INTO `staff` VALUES ('7', '灭绝', '60', '财务总监', '8500', '2002-09-12', '1', '3');
INSERT INTO `staff` VALUES ('8', '周芷若', '19', '会计', '48000', '2006-06-02', '7', '3');
INSERT INTO `staff` VALUES ('9', '丁敏君', '23', '出纳', '5250', '2009-05-13', '7', '3');
INSERT INTO `staff` VALUES ('10', '赵敏', '20', '市场部总监', '12500', '2004-10-12', '1', '2');
INSERT INTO `staff` VALUES ('11', '鹿杖客', '56', '职员', '3750', '2006-10-03', '10', '2');
INSERT INTO `staff` VALUES ('12', '鹤笔翁', '19', '职员', '3750', '2007-05-09', '10', '2');
INSERT INTO `staff` VALUES ('13', '方东白', '19', '职员', '5500', '2009-02-12', '10', '2');
INSERT INTO `staff` VALUES ('14', '张三丰', '88', '销售总监', '14000', '2004-10-12', '1', '4');
INSERT INTO `staff` VALUES ('15', '俞莲舟', '38', '销售', '4600', '2004-10-12', '14', '4');
INSERT INTO `staff` VALUES ('16', '宋远桥', '40', '销售', '4600', '2004-10-12', '14', '4');
INSERT INTO `staff` VALUES ('17', '陈友谅', '42', null, '2000', '2011-10-12', '1', null);

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `NAME` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COMMENT='部门表';

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '研发部');
INSERT INTO `dept` VALUES ('2', '市场部');
INSERT INTO `dept` VALUES ('3', '财务部');
INSERT INTO `dept` VALUES ('4', '销售部');
INSERT INTO `dept` VALUES ('5', '总经办');
INSERT INTO `dept` VALUES ('6', '人事部');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '工资等级',
  `losal` int(11) DEFAULT NULL COMMENT '最低薪资',
  `hisal` int(11) DEFAULT NULL COMMENT '最高薪资'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='薪资等级表';

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '0', '3000');
INSERT INTO `salgrade` VALUES ('2', '3001', '5000');
INSERT INTO `salgrade` VALUES ('3', '5001', '8000');
INSERT INTO `salgrade` VALUES ('4', '8001', '10000');
INSERT INTO `salgrade` VALUES ('5', '10001', '15000');
INSERT INTO `salgrade` VALUES ('6', '15001', '20000');
INSERT INTO `salgrade` VALUES ('7', '20001', '25000');
INSERT INTO `salgrade` VALUES ('8', '25001', '50000');

一共9个问题，问题3/4/7/9有问题，不能得到想要的答案


-- 1.查询工资小于7000的员工的姓名、年龄、职位、所属部门。
SELECT s.name '姓名', s.age '年龄', s.job '职位', d.name '所属部门'
FROM  staff s join dept d on s.dept_id =d.id where s.salary<7000;
-- 2.查询所有工资大于7000岁的员工的姓名、年龄、入职时间、所属部门; 如果员工没有分配部门, 也需要展示出来；并按入职时间升序排序。
SELECT s.name '姓名', s.age '年龄', s.entrydate '入职时间', d.name '所属部门'
FROM  staff s left join dept d  on s.dept_id =d.id where s.salary>7000 ORDER BY s.entrydate;
-- 3.查询所有员工的工资等级及所属部门,显示所属部门、员工姓名、入职时间、薪资、工资等级,并按工资进行升序排序
SELECT d.name '所属部门', s.name '员工姓名', s.entrydate '入职时间', s.salary '薪资',sa.grade'工资等级'
FROM  staff s  join dept d  on s.dept_id =d.id ,salgrade sa 
where s.salary>sa.losal and s.salary<sa.hisal ORDER BY s.salary ;
-- 4.查询 "财务部" 所有员工的信息及工资等级，显示直属领导姓名、员工姓名、薪资、工资等级    。
SELECT s.managerid '直属领导姓名' ,s.name '员工姓名',  s.salary '薪资',d.name '所属部门',sa.grade'工资等级'
FROM  staff s  join dept d  on s.dept_id =d.id,salgrade sa 
where d.NAME='研发部'and s.salary>sa.losal and s.salary<sa.hisal;
-- 5.查询各部门员工的平均工资及平均工资所在的工资等级，显示部门名称、平均工资、工资等级。
SELECT d.name '部门名称',(select avg (salary) from staff GROUP BY dept_id) '平均工资',sa.grade'工资等级'
FROM  staff s  join dept d  on s.dept_id =d.id, salgrade sa 
where  s.salary>sa.losal and s.salary<sa.hisal ;
-- 6.查询入职时间比 "灭绝" 早的员工信息。
SELECT * from staff where entrydate<(select entrydate from staff where name='灭绝');
-- 7.查询比平均薪资低800的员工信息。
SELECT * from staff where salary =(select avg(salary)-800 from staff );
-- 8.查询高于本部门平均工资的员工信息。
SELECT * from staff where salary >(select avg(s.salary) from staff s , dept d where s.dept_id =d.id);
-- 9.查询所有的部门信息, 并统计部门的员工人数，员工工资总额，员工平均工资。
SELECT d.*, COUNT(s.dept_id) '人数',SUM(s.salary)  '工资总额', AVG(s.salary)  '平均工资'
FROM dept d
LEFT JOIN staff s ON d.id = s.dept_id;

img

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
问题3和问题4中都有查询条件错误，具体解答如下：

问题3：查询所有员工的工资等级及所属部门，显示所属部门、员工姓名、入职时间、薪资、工资等级，并按工资进行升序排序

解答：

SELECT d.name '所属部门', s.name '员工姓名', s.entrydate '入职时间', s.salary '薪资', sa.grade '工资等级'
FROM staff s 
JOIN dept d ON s.dept_id = d.id
JOIN salgrade sa ON s.salary BETWEEN sa.losal AND sa.hisal
ORDER BY s.salary ASC;

问题4：查询 "财务部" 所有员工的信息及工资等级，显示直属领导姓名、员工姓名、薪资、工资等级。

解答：

SELECT sm.name '直属领导姓名', s.name '员工姓名', s.salary '薪资', sa.grade '工资等级'
FROM staff s
JOIN dept d ON s.dept_id = d.id
JOIN staff sm ON sm.id = s.managerid
JOIN salgrade sa ON s.salary BETWEEN sa.losal and sa.hisal
WHERE d.name = '财务部';

以上是正确的SQL代码，可以直接复制黏贴运行。

另附上其他问题的SQL代码：

问题1：

SELECT s.name '姓名', s.age '年龄', s.job '职位', d.name '所属部门'
FROM staff s 
JOIN dept d ON s.dept_id = d.id
WHERE s.salary < 7000;

问题2：

SELECT s.name '姓名', s.age '年龄', s.entrydate '入职时间', d.name '所属部门'
FROM staff s
LEFT JOIN dept d ON s.dept_id = d.id
WHERE s.salary > 7000
ORDER BY s.entrydate ASC;

问题5：

SELECT d.name '部门名称', AVG(s.salary) '平均工资', sa.grade '工资等级'
FROM staff s
JOIN dept d ON s.dept_id = d.id
JOIN salgrade sa ON s.salary BETWEEN sa.losal AND sa.hisal
GROUP BY d.name;

问题6：

SELECT * FROM staff 
WHERE entrydate < (SELECT entrydate FROM staff WHERE name = '灭绝');

问题7：

SELECT * FROM staff 
WHERE salary < (SELECT AVG(salary) - 800 FROM staff);

问题8：

SELECT * FROM staff 
WHERE salary > (SELECT AVG(s.salary) FROM staff s JOIN dept d ON s.dept_id = d.id WHERE d.name = '研发部');

问题9：

SELECT d.*, COUNT(s.dept_id) '人数', SUM(s.salary) '工资总额', AVG(s.salary) '平均工资'
FROM dept d
LEFT JOIN staff s ON d.id = s.dept_id
GROUP BY d.id;

如果我的回答解决了您的问题，请采纳！

第三个好像没问题

img

-- 9.查询所有的部门信息, 并统计部门的员工人数，员工工资总额，员工平均工资。

SELECT d.name, COUNT(s.dept_id) '人数',SUM(s.salary)  '工资总额', AVG(s.salary)  '平均工资'
FROM dept d
LEFT JOIN staff s ON d.id = s.dept_id GROUP BY name;

img


SELECT d.name '所属部门', s.name '员工姓名', s.entrydate '入职时间', s.salary '薪资',sa.grade'工资等级'
FROM  staff s  join dept d  on s.dept_id =d.id ,salgrade sa 
where s.salary>sa.losal and s.salary<sa.hisal 
ORDER BY s.salary asc;
-- 4.查询 "财务部" 所有员工的信息及工资等级，显示直属领导姓名、员工姓名、薪资、工资等级
SELECT s1.`NAME` '直属领导姓名' ,s.name '员工姓名',  s.salary '薪资',d.name '所属部门',sa.grade'工资等级'
FROM  staff s  
left join dept d  on s.dept_id =d.id 
left join staff s1 on s1.id = s.managerid,
salgrade sa 
where d.NAME='研发部'and s.salary>sa.losal and s.salary<sa.hisal;
-- 9.查询所有的部门信息, 并统计部门的员工人数，员工工资总额，员工平均工资。
SELECT d.*, COUNT(s.dept_id) '人数',SUM(s.salary)  '工资总额', AVG(s.salary)  '平均工资'
FROM dept d
LEFT JOIN staff s ON d.id = s.dept_id 
group by s.dept_id;

img


第三题目前来看没什么问题，可以加一下order by 字段 asc

问题3的SQL语句存在问题，因为在查询工资等级时，需要计算员工的工资落在哪个工资等级范围内，但是当前的查询条件只是限制了员工的工资在某个范围内，而没有计算出员工实际所属的工资等级。正确的SQL语句应该使用子查询来计算员工的工资等级，如下所示：

SELECT d.name '所属部门', s.name '员工姓名', s.entrydate '入职时间', s.salary '薪资', sa.grade '工资等级'
FROM staff s
JOIN dept d ON s.dept_id = d.id
JOIN salgrade sa ON s.salary BETWEEN sa.losal AND sa.hisal
ORDER BY s.salary;

问题4的SQL语句存在问题，因为查询条件限制了部门名称为“研发部”，而实际需要查询的是“财务部”的员工信息。正确的SQL语句如下所示：

SELECT sm.name AS '直属领导姓名', s.name AS '员工姓名', s.salary AS '薪资', d.name AS '所属部门', sg.grade AS '工资等级'
FROM staff s
JOIN dept d ON s.dept_id = d.id
LEFT JOIN staff sm ON s.managerid = sm.id
JOIN salgrade sg ON s.salary BETWEEN sg.losal AND sg.hisal
WHERE d.name = '财务部'
ORDER BY s.salary;

问题7的SQL语句存在问题，因为它只查询了工资等于平均薪资减去800的员工信息，而没有查询工资小于平均薪资减去800的员工信息。正确的SQL语句如下所示：

SELECT * FROM staff WHERE salary < (SELECT AVG(salary) - 800 FROM staff);

问题9的SQL语句存在问题，因为它只统计了部门中存在员工的情况，并没有统计部门中不存在员工的情况。正确的SQL语句应该使用左连接来统计部门的员工人数、工资总额和平均工资，如下所示：

SELECT d.*, COUNT(s.id) AS '人数', SUM(s.salary) AS '工资总额', AVG(s.salary) AS '平均工资'
FROM dept d
LEFT JOIN staff s ON d.id = s.dept_id
GROUP BY d.id;

给几条数据，不然很难写，看问题都不难，如果是作业还是建议自己做

回答不易，请关注采纳：

以下是修改后的SQL语句：

SELECT d.dept_name, COUNT(e.emp_id) AS emp_count, SUM(e.emp_salary) AS total_salary, AVG(e.emp_salary) AS avg_salary
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

解释：

  • 使用LEFT JOIN将部门表和员工表连接起来，以便查询每个部门的员工信息。
  • 使用COUNT函数统计每个部门的员工人数。
  • 使用SUM函数统计每个部门的员工工资总额。
  • 使用AVG函数计算每个部门的员工平均工资。
  • 使用GROUP BY语句按照部门名称分组，以便对每个部门进行统计。

2.查询所有员工的姓名、部门、工资和入职日期。

SELECT name, department, salary, hire_date  
FROM employees;

3.查询所有员工的姓名、部门、工资和入职日期，并按入职日期升序排序。

SELECT name, department, salary, hire_date  
FROM employees  
ORDER BY hire_date ASC;

4.查询所有员工的姓名、部门、工资和入职日期，并去掉重复的记录。

SELECT DISTINCT name, department, salary, hire_date  
FROM employees;

5.查询所有员工的姓名、部门、工资和入职日期，并统计员工人数。

SELECT name, department, COUNT(*) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary  
FROM employees  
GROUP BY name, department;

6.查询所有员工的姓名、部门、工资和入职日期，并去掉重复的记录，并按入职日期升序排序。

SELECT name, department, salary, hire_date  
FROM (  
 SELECT DISTINCT name, department, salary, hire_date  
 FROM employees  
) AS tmp  
ORDER BY hire_date ASC;

7.查询所有员工的姓名、部门、工资和入职日期，并去掉重复的记录，并按入职日期升序排序，同时统计员工人数和工资总额。

SELECT name, department, COUNT(*) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary, hire_date  
FROM (  
 SELECT DISTINCT name, department, salary, hire_date  
 FROM employees  
) AS tmp  
ORDER BY hire_date ASC;

8.查询所有员工的姓名、部门、工资和入职日期，并去掉重复的记录，并按入职日期升序排序，同时统计员工人数、工资总额和平均工资。

SELECT name, department, COUNT(*) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary, hire_date  
FROM (  
 SELECT DISTINCT name, department, salary, hire_date  
 FROM employees  
) AS tmp  
ORDER BY hire_date ASC;

9.查询所有员工的姓名、部门、工资和入职日期，并去掉重复的记录，并按入职日期升序排序，同时统计员工人数、工资总额、平均工资和所在城市的人数。

SELECT name, department, COUNT(*) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary, hire_date, city, COUNT(*) AS city_count  
FROM (  
 SELECT DISTINCT name, department, salary, hire_date, city  
 FROM employees  
) AS tmp  
GROUP BY name, department, city, hire_date  
ORDER BY hire_date ASC;

你把sql数据和题目用代码的形式贴出来。
再来提问排查会好一些。

你图中的报错。建议尝试直接用原表名试试，不用别名

很好解决的，主要是分组和子查询来处理

该回答引用GPT与博主@晓码自在合作编写：

根据提供的SQL查询语句,我的理解如下:

  1. 您要查询staff、dept、salgrade三张表,获取员工、部门、工资等级相关信息。
  2. 第10行的表名d.name应改为dept.name,否则会报Unknown table 'd'错误。
  3. 除第10行错误外,其他语句语法正确。
  4. 您查询的信息主要包括:员工姓名、年龄、入职时间、部门名称;员工工资级别及部门;财务部员工信息及工资级别;各部门平均工资及工资级别;入职时间早于“灭绝”的员工信息;工资低于平均工资800的员工信息;高于部门平均工资的员工信息;所有部门信息及人数、工资总额、平均工资统计。

基于此,我的建议如下:

  1. 查看并修改第10行错误,将d.name改为dept.name。
  2. 根据查询要实现的具体信息,拆分复杂的查询语句为多个简单语句,逐条执行,便于检查语法错误与结果。
  3. 添加语法检查,如检查表名、字段名是否正确,条件判断语句是否匹配等。这可以避免一条复杂语句的多个错误叠加,导致结果混乱。
  4. 进行联表查询时,注意on条件判断语句,将关联键指定正确。如s.dept_id = dept.id,而不是s.dept id = d.id。
  5. 使用表别名(如s、d、sa)可以简化语句,提高可读性。但在第一次引用时,要使用完整表名与别名,如:staff s、dept d、salgrade sa。
  6. 添加适当注释,简要描述每个查询要实现的功能。这有助于自己在编写或检查语句时的理解与思维连贯性。
  7. 如果出现混淆,最好重新构建查询逻辑。从最简单查询开始,逐步添加联表及条件,达到最终查询效果。这避免已有错误 continuar影响所写语句。