【MySQL】经典数据库SQL语句编写练习题—

【MySQL】数据库原理复习——SQL语言

对基本的SQL语句编写的练习题，其中的题目的答案可能会有多种书写方式。

1、题目1

1.1 关系模式

学生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

1.2 要求一

用SQL语言完成表的创建以及数据的插入：

[sql] view plain copy

use test;
create table if not exists student
(
SNO varchar(20) primary key,
SNAME varchar(20) character set gbk,
AGE int,
SEX char(2) character set gbk CHECK(SEX IN(\’男\’,\’女\’))
);
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.3 要求二

用SQL语言完成如下要求：

（1）检索至少选修＂程军＂老师所授全部课程的学生姓名（SNAME）；

本题的另外一种说法就是，检索选修了“程军”老师所授全部课程的学生姓名

[sql] view plain copy

select sname from student
where not exists
(
select * from course
where teacher=\’程军\’ and not exists
(
select * from sc
where sc.sno=student.sno and sc.cno=course.cno
)
);

这里的检索结果是：

+——-+
| sname |
+——-+
| 张友 |
+——-+
1 row in set (0.00 sec)

（2）检索＂李强＂同学不学课程的课程号(CNO)；

[sql] view plain copy

select course.cno from course
where course.cno not in
(
select sc.cno from sc,student
where student.sname=\’李强\’ and student.sno=sc.sno
);

+—–+
| cno |
+—–+
| K5 |
| K8 |
+—–+
2 rows in set (0.22 sec)

（3）检索选修不少于3门课程的学生学号(SNO)；

[sql] view plain copy

SELECT sc.sno
FROM sc
GROUP BY sc.sno HAVING count(*)>=3
;

+—–+
| sno |
+—–+
| 5 |
+—–+
1 row in set (0.09 sec)

（4）检索选修全部课程的学生姓名(SNAME)。

[sql] view plain copy

SELECT sname FROM student
WHERE NOT EXISTS
(
SELECT * FROM course
WHERE NOT EXISTS
(
SELECT * FROM sc
WHERE course.cno=sc.cno and student.sno=sc.sno
)
);

+——-+
| sname |
+——-+
| 张友 |
+——-+
1 row in set (0.02 sec)

（5）检索不学”C语言”的学生信息

[sql] view plain copy

SELECT * FROM student
WHERE sno not in
(
SELECT sc.sno FROM sc,course
WHERE course.cno=sc.cno
);

1.4 要求三

请用SQL语言完成如下查询：

（1）查询“程军”老师所教授的所有课程；

[sql] view plain copy

SELECT * FROM course
WHERE teacher=\’程军\’;

（2）查询“李强”同学所有课程的成绩；

[sql] view plain copy

SELECT score FROM student,sc
WHERE student.sname=\’李强\’ AND student.sno=sc.sno

（3）查询课程名为“C语言”的平均成绩；

[sql] view plain copy

SELECT AVG(score) FROM sc,course
WHERE course.cname=\’C语言\’ AND course.cno=sc.cno

（4）查询选修了所有课程的同学信息。

[sql] view plain copy

SELECT * FROM student
WHERE NOT EXISTS
(
SELECT * FROM course
WHERE NOT EXISTS
(
SELECT * FROM sc
WHERE course.cno=sc.cno AND student.sno=sc.sno
)
);

1.5 要求四

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

[sql] view plain copy

SELECT cno,cname FROM course WHERE teacher LIKE \’王%\’;

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

[sql] view plain copy

SELECT sno,sname FROM student
WHERE age>23;

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

[sql] view plain copy

SELECT sname FROM student
WHERE sex=\’女\’ AND sno IN
(
SELECT distinct sno FROM sc,course
WHERE teacher LIKE \’王%\’ AND sc.cno=course.cno
);

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

[sql] view plain copy

SELECT course.cno FROM course
WHERE course.cno NOT IN
(
SELECT sc.cno FROM sc,student
WHERE student.sname LIKE \’李%\’ AND sc.sno=student.sno
);

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

[sql] view plain copy

SELECT sno FROM sc
GROUP BY sno HAVING (count(*)>=2);

（6）检索全部学生都选修的课程的课程号与课程名。

[sql] view plain copy

SELECT course.cno,course.cname FROM course
WHERE course.cno IN
(
SELECT cno FROM sc
GROUP BY cno HAVing count(*)=(SELECT count(*) FROM student)
);

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

[sql] view plain copy

SELECT DISTINCT sno FROM sc
WHERE cno IN
(
SELECT cno FROM course
WHERE teacher LIKE \’王%\’
);

（8）统计有学生选修的课程门数。

[sql] view plain copy

SELECT count(*) NumberOfCourse
FROM
(
SELECT DISTINCT cno FROM sc
GROUP BY cno
) AS testTab;

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

[sql] view plain copy

SELECT AVG(age) AverageOfAge
FROM student
WHERE sno IN
(
SELECT sno FROM sc
WHERE cno=\’K1\’
);

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

[sql] view plain copy

SELECT sc.cno,AVG(sc.score)
FROM sc
WHERE sc.cno IN
(
SELECT course.cno FROM course
WHERE course.teacher LIKE \’王%\’
)
GROUP BY sc.cno

（11）统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

[sql] view plain copy

SELECT cno,Num
FROM
(
SELECT cno,count(*) as Num
FROM sc
GROUP BY cno HAVING (count(*) >= 2)
) tb_temp
ORDER BY Num DESC,cno ASC
;

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

[sql] view plain copy

SELECT stu1.sname
FROM student stu1,
(
SELECT max(sno) snoLi,min(age) ageLi FROM student
WHERE sname LIKE \’李%\’
) AS stuLi
WHERE (stu1.age < stuLi.ageLi) AND (stu1.sno>stuLi.snoLi)
;

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

[sql] view plain copy

SELECT sname,age FROM student
WHERE sname LIKE \’李%\’
;

（14）在SC中检索成绩为空值的学生学号和课程号。

[sql] view plain copy

SELECT sno,cno FROM sc
WHERE score IS NULL;

（15）求年龄大于女同学平均年龄的男学生姓名和年龄。

[sql] view plain copy

SELECT sname,age FROM student
WHERE
sex=\’男\’ AND age > (
SELECT AVG(age)
FROM student
WHERE sex=\’女\’
)
;

（16）求年龄大于所有女同学年龄的男学生姓名和年龄。

[sql] view plain copy

SELECT sname,age
FROM student
WHERE sex=\’男\’ AND age > (
SELECT max(age)
FROM student
WHERE sex=\’女\’
)
;

本文链接：https://www.cnblogs.com/linjiaxin/p/7747944.html

【MySQL】经典数据库SQL语句编写练习题——SQL语句扫盲

1、题目1

1.1 关系模式

1.2 要求一

1.3 要求二

1.4 要求三

1.5 要求四

【MySQL】经典数据库SQL语句编写练习题——SQL语句扫盲的更多相关文章

随机推荐

热门专题

目录导航