23、综合练习

于 2020-04-02 19:44:17 发布
阅读量657 收藏 2
点赞数 1
分类专栏： MySQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43007659/article/details/105274774
版权

1、 查询工资最低的员工信息：last_name，salary

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary = ( SELECT MIN( salary ) FROM employees )

2、查询平均工资最低的部门信息

SELECT
	d.* 
FROM
	departments d 
WHERE
	d.department_id = ( SELECT department_id 
	                    FROM employees 
	                    GROUP BY department_id 
	                    ORDER BY AVG( salary ) 
	                    LIMIT 1 )

3、查询平均工资最低的部门信息和该部门的平均工资

SELECT
	d.*,
	( SELECT AVG( salary ) FROM employees GROUP BY department_id ORDER BY AVG( salary ) LIMIT 1 ) ag 
FROM
	departments d 
WHERE
	d.department_id = ( SELECT department_id 
	                    FROM employees 
	                    GROUP BY department_id 
	                    ORDER BY AVG( salary ) 
	                    LIMIT 1 )

4、查询平均工资最高的job信息

SELECT
	* 
FROM
	jobs 
WHERE
	job_id = ( SELECT job_id 
	           FROM employees 
	           GROUP BY job_id 
	           ORDER BY AVG( salary ) DESC 
	           LIMIT 1
	         )

5、查询平均工资高于公司平均工资的部门有哪些？

SELECT
	department_id,
	AVG( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	AVG( salary ) > ( SELECT AVG( salary ) FROM employees )

6、查询出公司中所有manager的详细信息

SELECT
	* 
FROM
	employees 
WHERE
	employee_id IN ( SELECT manager_id FROM employees )

7、各个部门中最高工资中最低的那个部门的最低工资是多少


SELECT
	min( salary ) 
FROM
	employees 
WHERE
	department_id = ( SELECT e.department_id 
	                  FROM employees e 
	                  GROUP BY e.department_id 
	                  ORDER BY MAX( salary ) 
	                  LIMIT 1 )

8、查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary

SELECT e.last_name,e.department_id,e.email,e.salary
FROM employees e
WHERE employee_id in (SELECT DISTINCT manager_id 
                      FROM employees 
                      WHERE department_id = ( SELECT department_id 
                                              FROM employees 
                                              GROUP BY department_id 
                                              ORDER BY AVG( salary ) DESC 
                                              LIMIT 1 ) 
                      ) 
--方法2
SELECT
		e.last_name,
		e.department_id,
		e.email,
		e.salary 
FROM
	employees e
INNER JOIN departments d ON d.manager_id = e.employee_id 
WHERE
	d.department_id = ( SELECT department_id 
		                FROM employees 
		                GROUP BY department_id 
		                ORDER BY AVG( salary ) DESC 
		                LIMIT 1 
		                )

数据源

DROP DATABASE IF EXISTS student;
CREATE DATABASE student;
USE student;

CREATE TABLE student(
	studentno VARCHAR(10) NOT NULL PRIMARY KEY,
	studentname VARCHAR(20) NOT NULL,
	loginpwd VARCHAR(8) NOT NULL,
	sex CHAR(1) ,
	majorid INT NOT NULL REFERENCES grade(majorid),
	phone VARCHAR(11),
	email VARCHAR(20) ,
	borndate DATETIME
);

CREATE TABLE major(
	majorid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	majorname VARCHAR(20) NOT  NULL

);
CREATE TABLE result(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	studentno VARCHAR(10) NOT NULL REFERENCES student(studentno),
	score DOUBLE
);


INSERT INTO major VALUES(NULL,'javaee');
INSERT INTO major VALUES(NULL,'html5');
INSERT INTO major VALUES(NULL,'android');


INSERT INTO student VALUES('S001','张三封','8888','男',1,'13288886666','zhangsanfeng@126.com','1966-9-1');
INSERT INTO student VALUES('S002','殷天正','8888','男',1,'13888881234','yintianzheng@qq.com','1976-9-2');
INSERT INTO student VALUES('S003','周伯通','8888','男',2,'13288886666','zhoubotong@126.com','1986-9-3');
INSERT INTO student VALUES('S004','张翠山','8888','男',1,'13288886666',NULL,'1995-9-4');
INSERT INTO student VALUES('S005','小小张','8888','女',3,'13288885678','xiaozhang@126.com','1990-9-5');

INSERT INTO student VALUES('S006','张无忌','8888','男',2,'13288886666','zhangwuji@126.com','1998-8-9');
INSERT INTO student VALUES('S007','赵敏','0000','女',1,'13288880987','zhaomin@126.com','1998-6-9');
INSERT INTO student VALUES('S008','周芷若','6666','女',1,'13288883456','zhouzhiruo@126.com','1996-7-9');
INSERT INTO student VALUES('S009','殷素素','8888','女',1,'13288886666','yinsusu@163.com','1986-1-9');
INSERT INTO student VALUES('S010','宋远桥','6666','男',3,'1328888890','songyuanqiao@qq.com','1996-2-9');


INSERT INTO student VALUES('S011','杨不悔','6666','女',2,'13288882345',NULL,'1995-9-9');
INSERT INTO student VALUES('S012','杨逍','9999','男',1,'13288885432',NULL,'1976-9-9');
INSERT INTO student VALUES('S013','纪晓芙','9999','女',3,'13288888765',NULL,'1976-9-9');
INSERT INTO student VALUES('S014','谢逊','9999','男',1,'13288882211',NULL,'1946-9-9');
INSERT INTO student VALUES('S015','宋青书','9999','男',3,'13288889900',NULL,'1976-6-8');



INSERT INTO result VALUES(NULL,'s001',100);
INSERT INTO result VALUES(NULL,'s002',90);
INSERT INTO result VALUES(NULL,'s003',80);

INSERT INTO result VALUES(NULL,'s004',70);
INSERT INTO result VALUES(NULL,'s005',60);
INSERT INTO result VALUES(NULL,'s006',50);
INSERT INTO result VALUES(NULL,'s006',40);
INSERT INTO result VALUES(NULL,'s005',95);
INSERT INTO result VALUES(NULL,'s006',88);

一、查询每个专业的学生人数

SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid

二、查询参加考试的学生中，每个学生的平均分、最高分

SELECT
	r.studentno,
	avg( r.score ),
	max( r.score ) 
FROM
	result r 
GROUP BY
	r.studentno

三、查询姓张的每个学生的最低分大于60的学号、姓名

SELECT
	s.studentno,s.studentname 
FROM
	result r
	INNER JOIN student s ON r.studentno = s.studentno 
WHERE
	s.studentname LIKE '张%' 
GROUP BY
	s.studentno
HAVING MIN( r.score )> 60
-- 若分组后某个字段的每个分组只有唯一值，那么该字段也可以出现在select后面

四、查询生日在“1988-1-1”后的学生姓名、专业名称

SELECT studentname,majorname
FROM student s 
JOIN major m 
on s.majorid = m.majorid
WHERE  DATEDIFF(borndate,'1988-1-1')>0

五、查询每个专业的男生人数和女生人数分别是多少

SELECT majorid,sex,COUNT(*)
FROM student
GROUP BY majorid,sex
-- 
SELECT
	majorid,
	( SELECT COUNT( * ) FROM student s WHERE s.sex = '男' AND st.majorid = s.majorid ) 男,
	( SELECT COUNT( * ) FROM student s WHERE s.sex = '女' AND st.majorid = s.majorid ) 女 
FROM
	student st 
GROUP BY
	majorid

六、查询张翠山所学专业的最低分

SELECT
	min( r.score ) 
FROM
	student s
	JOIN result r ON r.studentno = s.studentno 
WHERE
	s.majorid = ( SELECT majorid 
	              FROM student 
	              WHERE studentname = '张翠山' )

七、查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m
on s.majorid = m.majorid
join result r
on s.studentno = r.studentno
WHERE r.score > 60;

八、按邮箱位数分组，查询每组的学生个数

SELECT
	COUNT( * ),
	LENGTH( email ) 
FROM
	student 
GROUP BY
	LENGTH( email )

九、查询学生名、专业名、分数

SELECT studentname,score,majorname
FROM student s
JOIN major m
on s.majorid = m.majorid
LEFT JOIN result r
on s.studentno = r.studentno

十、查询哪个专业没有学生，分别用左连接和右连接实现

SELECT
	m.majorid,
	m.majorname 
FROM
	major m
	LEFT JOIN student s ON m.majorid = s.majorid 
WHERE
	s.majorid IS NULL

十一、查询没有成绩的学生人数

SELECT
	COUNT( * ) 
FROM
	student s
	LEFT JOIN result r ON s.studentno = r.studentno 
WHERE
	r.studentno IS NULL;
Mr. Code178
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值