sql查询习题集及答案

练习1： 学生成绩示例

sid	name	lesson	score
1	张三	数学	89
2	张三	语文	90
3	张三	英语	70
4	李四	语文	80
5	李四	数学	82
6	王二	英语	79

1.求各科目成绩不低于80分的学生?(每门课成绩都大于等于80) 《=》 最低分不小于80，

每个学生科目成绩最低分大于等于80分

SELECT name 
 FROM course
 GROUP BY name
 HAVING MIN(score)>=80;

2.求各科目平均成绩不低于80分的学生?

SELECT name
 FROM course
 GROUP BY name
 HAVING AVG(score) >= 80;

创表代码

create database grade;
create table course(
	id int(10) not null,
	name varchar(20) not null,
	lesson varchar(50) not null,
	score int(20) not null 
);
insert into course value(1,'张三','数学',89),
						(2,'张三','语文',90),
						(3,'张三','英语',70),
						(4,'李四','语文',80),
						(5,'李四','数学',82),
						(6,'王二','英语',79);

练习二

已知员工表和部门表结构如下:

部门表结构：
±----------±------------±-----±----±--------±-----------+
| 字段名 | 数据类型 | Null | Key | Default | 字段说明|
±----------±------------±-----±----±--------±-----------+
| d_no | int(11) | NO | PRI | NULL | 部门编号
| d_name | varchar(50) | NO | | NULL | 部门名称
| d_location| varchar(100)| NO | | NULL | 部门地址
±----------±------------±-----±----±--------±-----------+

员工表结构：
±-------±---------------±-----±----±--------±-----------+
| 字段名 | 数据类型 | Null | Key | Default | 字段说明|
±----------±------------±-----±----±--------±-----------+
| e_no | int(11) | NO | PRI | NULL | 员工编号
| e_name | varchar(50) | NO | | NULL | 员工名称
| e_sex | char(2) | NO | | NULL | 员工性别
| dept_no | int(11) | NO | MUL | NULL | 部门编号
| e_job | varchar(50) | NO | | NULL | 职位
| e_salary | int(11) | NO | | NULL | 薪水
| hire_date | date | NO | | NULL | 入职日期
±----------±------------±-----±----±--------±-----------+

部门记录:

d_no	d_name	d_location
10	业务部	上海
20	财务部	北京
30	人事部	广州
40	销售部	成都

员工记录

e_no	e_name	e_sex	dept_no	e_job	e_salary	hire_date
1001	李强	1	20	算账	800	2015-9-9
1002	王刚	1	30	招聘	1600	2014-10-9
1003	珊珊	0	30	招聘	1250	2014-11-7
1004	张阳	1	20	算账	2975	2015-4-9
1005	小丽	0	40	销售	2850	2015-2-10
1006	王二	1	10	业务	2450	2015-4-23
1007	小冬	1	40	销售	2750	2015-3-10

1.在employee表中,查询dept_no等于10和20的所有记录

分析: 查询结果: 员工信息; 条件: dept_no=10 OR dept_no=20
SELECT e_no, e_name , e_sex, dept_no, e_job, e_salary , hire_date FROM employee WHERE dept_no=10 OR dept_no=20;
±-----±-------±------±--------±-------------±---------±----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
±-----±-------±------±--------±-------------±---------±----------+
| 1001 | 李强 | 男 | 20 | 会计 | 800 | 2015-9-9 |
| 1004 | 张阳 | 男 | 20 | 出纳 | 2975 | 2015-4-9 |
| 1006 | 王二 | 男 | 10 | 产品策划 | 2450 | 2015-4-23 |
±-----±-------±------±--------±-------------±---------±----------+

2.在employee表中,查询工资范围在800-2500的员工信息

SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;
±-----±-------±------±--------±-------------±---------±----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
±-----±-------±------±--------±-------------±---------±----------+
| 1001 | 李强 | 男 | 20 | 会计 | 800 | 2015-9-9 |
| 1002 | 王刚 | 男 | 30 | HR | 1600 | 2014-10-9 |
| 1003 | 珊珊 | 女 | 30 | HR | 1250 | 2014-11-7 |
| 1006 | 王二 | 男 | 10 | 产品策划 | 2450 | 2015-4-23 |
±-----±-------±------±--------±-------------±---------±----------+

3.在employee表中,查询部门编号为20的部门中的员工信息

SELECT * FROM employee WHERE dept_no = 20;
±-----±-------±------±--------±-------±---------±----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
±-----±-------±------±--------±-------±---------±----------+
| 1001 | 李强 | 男 | 20 | 会计 | 800 | 2015-9-9 |
| 1004 | 张阳 | 男 | 20 | 出纳 | 2975 | 2015-4-9 |
±-----±-------±------±--------±-------±---------±----------+

4.在employee表中,查询每个部门最高工资的员工信息

第一步:每个部门最高工资
SELECT MAX(e_salary) AS 工资 FROM employee GROUP BY dept_no;
第二步:

SELECT e_no, e_name , e_sex, dept_no, e_job, e_salary , hire_date FROM employee WHERE e_salary IN( SELECT MAX(e_salary) AS 工资 FROM employee GROUP BY dept_no );

5.查询员工李强所在部门和部门所在地

分析: 查询结果: d_name d_location
条件: e_name = ‘李强’
内连接
SELECT d_name ,d_location,e_name FROM employee INNER JOIN department ON dept_no = d_no WHERE e_name = '李强'

普通多表连接
SELECT d_name ,d_location,e_name FROM employee,department WHERE dept_no = d_no AND e_name = '李强'

子查询
SELECT d_name,d_location FROM department WHERE d_no = ( SELECT dept_no FROM employee WHERE e_name = '李强' );

6.使用连接查询，查询所有员工的部门和部门信息

`SELECT e_no,e_name,e_job,   e_salary ,   hire_date, d_name ,d_location
FROM employee INNER JOIN department
ON  dept_no = d_no`

7.在employee表中，计算每个部门各有多少员工

SELECT COUNT(*) as 个数 ,dept_no FROM employee GROUP BY dept_no;
或者加个笔名
SELECT COUNT(*) as 员工个数 ,dept_no, d_name FROM employee,department WHERE d_no = dept_no GROUP BY dept_no;
±-------------±--------±----------+
| 员工个数 | dept_no | d_name |
±-------------±--------±----------+
| 1 | 10 | 产品部 |
| 2 | 20 | 财务部 |
| 2 | 30 | 人事部 |
| 2 | 40 | 销售部 |
±-------------±--------±----------+

8.在employee表中，计算不同工种职工的总工资数

SELECT SUM(e_salary) AS 总工资, e_job FROM employee GROUP BY e_job;
±----------±-------------+
| 总工资 | e_job |
±----------±-------------+
| 2850 | HR |
| 2450 | 产品策划 |
| 800 | 会计 |
| 2975 | 出纳 |
| 5600 | 销售经理 |
±----------±-------------+

9.在employee表中,计算不同部门的平均工资

SELECT ROUND(AVG(e_salary),0) AS 平均工资, dept_no FROM employee GROUP BY dept_no

SELECT ROUND(AVG(e_salary),0) AS 平均工资, d_name FROM employee,department WHERE d_no = dept_no GROUP BY dept_no
±-------------±----------+
| 平均工资 | d_name |
±-------------±----------+
| 2450 | 产品部 |
| 1888 | 财务部 |
| 1425 | 人事部 |
| 2800 | 销售部 |
±-------------±----------+

10.在employee表中，查询工资低于1500的员工信息

SELECT * FROM employee WHERE e_salary < 1500;

11.在employee表中,将查询记录先按部门编号由高到低排序，再按员工工资由高到低排序

SELECT * FROM employee ORDER BY dept_no DESC , e_salary DESC;
±-----±-------±------±--------±-------------±---------±----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
±-----±-------±------±--------±-------------±---------±----------+
| 1005 | 小丽 | 女 | 40 | 销售经理 | 2850 | 2015-2-10 |
| 1007 | 小冬 | 男 | 40 | 销售经理 | 2750 | 2015-3-10 |
| 1002 | 王刚 | 男 | 30 | HR | 1600 | 2014-10-9 |
| 1003 | 珊珊 | 女 | 30 | HR | 1250 | 2014-11-7 |
| 1004 | 张阳 | 男 | 20 | 出纳 | 2975 | 2015-4-9 |
| 1001 | 李强 | 男 | 20 | 会计 | 800 | 2015-9-9 |
| 1006 | 王二 | 男 | 10 | 产品策划 | 2450 | 2015-4-23 |
±-----±-------±------±--------±-------------±---------±----------+

12.使用limit,查询第二条记录开始到第五条记录结束的所有记录

SELECT * FROM employee LIMIT 1,4

练习三

±-------±----------±-----±------+
| number | name | age | score |
±-------±----------±-----±------+
| 1 | 小明 | 20 | 96 |
| 2 | 张三 | 26 | 86 |
| 3 | 王二 | 25 | 76 |
| 5 | 李四 | 20 | 98 |
| 6 | 王小二 | 25 | 88 |
±-------±----------±-----±------+

1.查询高于平均成绩的学生？

SELECT avg(score) FROM student;

SELECT number,name,age,score FROM student WHERE score > ( SELECT avg(score) FROM student )

±-------±-------±-----±------+
| number | name | age | score |
±-------±-------±-----±------+
| 1 | 小明 | 20 | 96 |
| 5 | 李四 | 20 | 98 |
±-------±-------±-----±------+

2.添加学生，添加学生成绩与平均成绩相等?

INSERT INTOstudent(number,name,age,score)VALUES(8,'小丽',24 ,(SELECT ROUND(avg(score),0) FROMstudentas s ));

练习四

学生student：SNO：学号，SNAME：姓名，AGE：年龄 SEX：性别

SNO	SNAME	AGE	SEX
1	李强	23	男
2	刘丽	22	女
5	张友	22	男

课程course：CNO：课程代码，CNAME：课程名称，TEACHER：教师

CNO	CNAME	TEACHER
K1	C语言	王华
K5	数据库原理	程军
K8	编译原理	程军

学生成绩SC：SNO：学号，CNO：课程代码，SCORE：成绩

SNO	CNO	SCORE
1	K1	83
2	K1	85
5	K1	92
2	K5	90
5	K5	84
5	K8	80

创表代码

  create table if not exists student  
(  
    SNO varchar(20) primary key,  
    SNAME varchar(20),  
    AGE int,  
    SEX  char(2)   
);  
insert into student values('1','李强',23,'男');  
insert into student values('2','刘丽',22,'女');  
insert into student values('5','张友',22,'男');  ```
  
create table if not exists course  
(  
    CNO varchar(20) primary key,  
    CNAME varchar(20) character set gbk,  
    TEACHER varchar(20) character set gbk  
);  
insert into course values('K1','C语言','王华');  
insert into course values('K5','数据库原理','程军');  
insert into course values('K8','编译原理','程军');  
  
create table if not exists sc  
(  
    SNO varchar(20) NOT NULL,  
    CNO varchar(20) NOT NULL,  
    SCORE int NOT NULL,  
    primary key (SNO,CNO),  
    foreign key (SNO) references student(SNO),  
    foreign key (CNO) references course(CNO)  
);  
insert into sc values('1','K1',83);  
insert into sc values('2','K1',85);  
insert into sc values('5','K1',92);  
insert into sc values('2','K5',90);  
insert into sc values('5','K5',84);  
insert into sc values('5','K8',80);

题目

（1）检索王老师所授课程的课程号和课程名

mysql>·SELECT CNAME,CNO -> FROM course -> WHERE TEACHER LIKE '王%';
±--------±----+
| CNAME | CNO |
±--------±----+
| C语言 | K1 |
±--------±----+

（2） 检索年龄大于或等于23岁的男学生的学号和姓名。

mysql>SELECT SNO,SNAME -> FROM student -> WHERE age >= 23 AND sex = '男';
±----±-------+
| SNO | SNAME |
±----±-------+
| 1 | 李强 |
±----±-------+

（3） 检索至少选修王老师所授课程中一门课程的女学生姓名。

1.王老师所授课程号
SELECT CNO FROM course WHERE TEACHER LIKE '王%';
2.选学王老师所授课程的学生学号
SELECT SNO FROM sc WHERE CNO IN ( SELECT CNO FROM course WHERE TEACHER LIKE '王%' )
3.
SELECT SNAME FROM student WHERE sex = '女' AND sno IN ( SELECT SNO FROM sc WHERE CNO IN ( SELECT CNO FROM course WHERE TEACHER LIKE '王%' ) )

普通多表边接
SELECT DISTINCT SNAME FROM student,course,sc WHERE student.sno = sc.sno AND course.cno=sc.cno AND sex = '女' AND TEACHER LIKE '王%';

内连接

SELECT SNAME FROM sc INNER JOIN student ON sc.sno=student.sno INNER JOIN course ON course.cno=sc.cno WHERE sex = '女' AND TEACHER LIKE '王%';

（4） 检索李同学不学的课程的课程号。

检索李同学学的课程的课程号。

SELECT CNO FROM sc WHERE sno IN ( SELECT SNO FROM student WHERE sname LIKE '李%' )
±----+
| CNO |
±----+
| K1 |
±----+

李同学不学的课程的课程号。
SELECT distinct cno FROM sc WHERE cno <> ( SELECT CNO FROM sc WHERE sno IN ( SELECT SNO FROM student WHERE sname LIKE '李%' ) )
±----+
| cno |
±----+
| K5 |
| K8 |
±----+

（5）检索至少选修两门课程的学生学号。

SELECT SNO FROM SC GROUP BY sno HAVING count(cno) >= 2;
±----+
| SNO |
±----+
| 2 |
| 5 |
±----+

（6）检索选修课程包含王老师所授课的学生学号。

(等价于:选修了王老师所授课的学生学号)

SELECT SNO FROM sc WHERE cno IN ( SELECT CNO FROM course WHERE teacher LIKE '王%');
±----+
| SNO |
±----+
| 1 |
| 2 |
| 5 |
±----+

（7）统计每个学生选修的课程门数。

COUNT()
GROUP BY

SELECT COUNT(CNO) AS 课程门数,SNAME FROM SC,student WHERE sc.sno=student.sno GROUP BY sc.SNO;

（8）求选修K1课程的学生的平均年龄。

AVG(age)
cno = k1

SELECT ROUND(AVg(age),0) AS 平均年龄 FROM student WHERE sno IN ( SELECT SNO FROM sc WHERE cno = 'k1' );

（9）求程老师所授课程的每门课程的学生平均成绩。

王老师所授课程的学生平均成绩

AVG(score)
teacher LIKE ‘王%’

SELECT AVG(score) FROM sc WHERE cno IN ( SELECT cno FROM course WHERE teacher LIKE '程%')

SELECT AVG(SCORE) FROM sc WHERE cno IN ( SELECT cno FROM course WHERE teacher LIKE '程%') GROUP BY cno
±-----------+
| AVG(SCORE) |
±-----------+
| 87.0000 |
| 80.0000 |
±-----------+

（10）检索学号比李同学大，而年龄比他小的学生姓名。

SELECT SNAME FROM student WHERE sno > ALL (SELECT SNO from student WHERE sname LIKE '李%') AND age < ALL (SELECT AGE FROM student WHERE sname LIKE '李%')
±-------+
| SNAME |
±-------+
| 张友 |
±-------+

若子查询结果有多个，在进行条件判断时，要加修饰符: ANY 或ALL

例如 >ANY ： 比子查询结果任一结果大则满足条件
如: WHERE sno >ANY (2,5)
sno 只要比2大或者5大都可以
大于最小值

例如>ALL : 比子查询结果所有结果大则满足条件
如: WHERE sno >ALL (2,5)
sno 大于最大值5

<ANY : 比子查询结果任一结果小则满足条件
小于最大值

<ALL 小于最小值

（11）检索姓名以李打头的所有学生的姓名和年龄。

SELECT sname,age FROM student WHERE sname LIKE '李%';