大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺

创建表并添加数据

-- 经典SQL练习题

CREATE TABLE STUDENT8
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5));

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL);

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL);

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL, 
TBIRTHDAY DATETIME NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);

INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(108,'曾华','男' ,'1977-09-01',95033);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(105,'匡明','男' ,'1975-10-02',95031);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(107 ,'王丽','女','1976-01-23',95033);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(101,'李军','男','1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(109 ,'王芳','女','1975-02-10',95031);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(103 ,'陆君','男','1974-06-03',95031);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105','计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245','操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166','数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888','高等数学',100);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

表数据

在这里插入图片描述

题目

1、查询Student表中的所有记录的Sname.Ssex和Class列。

2、查询教师所有的单位即不重复的Depart列。

3、查询Student表的所有记录。

4、查询Score表中成绩在60到80之间的所有记录。

5、查询Score表中成绩为85，86或88的记录。

6、查询Student表中“95031”班或性别为“女”的同学记录。

7、以Class降序查询Student表的所有记录。

8、以Cno升序、Degree降序查询Score表的所有记录。

9、查询”95031”班的学生人数。

10、查询Score表中的最高分的学生学号和课程号。

11、查询“3-105”号课程的平均分。

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

13、查询最低分大于70，最高分小于90的Sno列。

14、查询所有学生的Sname、Cno和Degree列。

15、查询所有学生的Sno、Cname和Degree列。

16、查询所有学生的Sname、Cname和Degree列。

17、查询“95033″班所选课程的平均分。

18、假设使用如下命令建立了一个grade表：

create table grade(
	low decimal(3,0),
	upp int(3),
	rankk varchar(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
commit;

在这里插入图片描述
现查询所有同学的Sno、Cno和rank列。

19、查询选修“3-105″课程的成绩高于“109″号同学成绩的所有同学的记录。

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

23、查询“张旭“教师任课的学生成绩。

24、查询选修某课程的同学人数多于5人的教师姓名。

25、查询95O33班和95031班全体学生的记录。

26、查询存在有85分以上成绩的课程Cno.

27、查询出“计算机系“教师所教课程的成绩表。

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

29、查询选修编号为”3-105“课程且成绩至少高于选修编号为”3-245”的同学的Cno、Sno和Degree，并按Degree从高到低次序排序。

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245″课程的同学的Cno、Sno和Degree。

31、查询所有教师和同学的name、sex和birthday。

32、查询所有“女”教师和“女”同学的name、sex和birthday。

33、查询成绩比该课程平均成绩低的同学的成绩表。

34、查询所有任课教师的Tname和Depart。

35查询所有未讲课的教师的Tname和Depart。

36、查询至少有2名男生的班号。

37、查询Student表中不姓””王”的同学记录。

38、查询Student表中每个学生的姓名和年龄。

39、查询Student表中年龄最大和最小学生的Sbirthday日期值。

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

41、查询“男”教师及其所上的课程。

42、查询最高分同学的Sno、Cno和Degree列。

43、查询和“李军”同性别的所有同学的Sname。

44、查询和“李军”同性别并同班的同学Sname。

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

参考答案

-- 1
select sname, ssex, class from student;

-- 2
select distinct depart from teacher;

-- 3
select * from student;

-- 4
select * from score where degree between 60 and 80;

-- 5
select * from score where degree in(85, 86, 88);

-- 6
select * from student where class = '95031' or ssex = '女';

-- 7 降序desc
select * from student
order by class desc;

-- 8 
select * from score
order by cno, degree desc;

-- 9
select count(*) from student
where class = '95031';

-- 10
select sno, cno from score
where degree = (select max(degree) from score);

select sno, cno from score order by degree desc limit 1; -- 方法二

-- 11
select avg(degree) from score where cno = '3-105';

-- 12
select cno, avg(degree) 
from score 
group by cno
having cno like '3%' and count(cno) >= 5;

select cno, avg(degree) 
from score
where  cno like '3%' -- 或者
group by cno
having count(cno) >= 5;

-- 13
select sno from score
group by sno
having max(degree) < 90 and min(degree) > 70;

-- 14
select sname, cno, degree
from student stu, score sco
where stu.sno = sco.sno;

select sname, cno, degree -- 方法2
from student left join score
on student.sno = score.sno;

-- 15
select sno, cname, degree 
from score s, course c
where s.cno = c.cno;

select sno, cname, degree -- 方法2
from score join course
on score.cno = course.cno;

-- 16
select sname, cname, degree
from student s join score sc
on s.sno = sc.sno join course c
on sc.cno = c.cno;

select sname, cname, degree
from student s join (score sc, course c) -- 或者
on s.sno = sc.sno and sc.cno = c.cno;

-- 17 
select cno, avg(degree)
from score
where sno in(select sno from student where class = '95033')
group by cno;

select cno, avg(degree) -- 方法2
from score join student
on score.sno = student.sno
where student.class = '95033'
group by cno;

-- 18
create table grade(
	low decimal(3,0),
	upp int(3),
	rankk varchar(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
commit;

select sno, cno, degree, rankk
from score, grade
where degree between low and upp; -- between ... and ...

-- 19
select *
from score
where cno = '3-105' and degree > (select degree from score where sno = '109' and cno = '3-105');

select * -- 方法2
from score a join score b
on a.cno = '3-105' and a.degree > b.degree and b.sno = '109' and b.cno = '3-105';

-- 20 
select *
from score
where degree < (select max(degree) from score) -- 所有科目所有成绩的最高分（only one）
group by sno
having count(*) > 1
order by degree;

-- 21
select *
from score
where degree > (select degree from score where sno = '109' and cno = '3-105');

select * -- 方法2
from score a join score b
on a.degree > b.degree and b.sno = '109' and b.cno = '3-105';

-- 22
select sno, sname, sbirthday
from student
where year(sbirthday) = (select year(sbirthday) from student where sno = '108'); -- 获取年份year()

-- 23
select * 
from score
where cno in (select cno
	from teacher, course
	where teacher.tno = course.tno and tname = '张旭');
	
select * -- 方法2，比方法1的扫描次数更少
from score join (teacher, course) -- 注意此处需加括号()
on score.cno = course.cno and teacher.tno = course.tno
where tname = '张旭';

-- 24
select tname
from teacher
where teacher.tno in (
	select tno
	from course, score
	where course.cno = score.cno
	group by score.cno
	having count(*) > 5);

select tname -- 方法2，优于方法1
from teacher join (course, score)
on teacher.tno = course.tno and course.cno = score.cno
group by score.cno
having count(*) > 5;

-- 25
select *
from student
where class = '95033' or class = '95031';

-- 26
select distinct cno
from score
where degree > 85;

select cno -- 方法2
from score
group by cno
having max(degree) > 85;

-- 27 
select *
from score
where score.cno in (
	select cno
	from course
	where course.tno in(
		select teacher.tno
		from teacher
		where depart = '计算机系'));

select * -- 方法2
from score join (course, teacher)
on score.cno = course.cno and course.tno = teacher.tno
where depart = '计算机系';

select * -- 方法3，略好于方法2，在多连接情况下性能会迅速下降
from score 
where score.cno in(select cno from course join teacher on course.tno = teacher.tno where depart = '计算机系');

-- 28
select tname, prof
from teacher
where depart = '计算机系' and prof not in (select prof from teacher where depart = '电子工程系');

-- 29
select *
from score sc
where cno = '3-105' and degree > (select degree from score sc2 where cno = '3-245' and sc.sno = sc2.sno) 
order by degree desc;

-- 30
select *
from score sc
where cno = '3-105' and degree > (select degree from score sc2 where cno = '3-245' and sc.sno = sc2.sno);

-- 31 
select sname name, ssex sex, sbirthday birthday
from student
union -- 用于合并两个或多个 SELECT 语句的结果集
select tname name, tsex sex, tbirthday birthday
from teacher;

-- 32
select sname name, ssex sex, sbirthday birthday
from student
where ssex = '女'
union -- 同上
select tname name, tsex sex, tbirthday birthday
from teacher
where tsex = '女';

-- 33
select *
from score sc
where degree < (select avg(degree) from score sc2 where sc.cno = sc2.cno);

-- 34
select tname, depart
from teacher, course
where teacher.tno = course.tno;

select tname, depart -- 方法2
from teacher join course
on teacher.tno = course.tno;

select tname, depart -- 方法3
from teacher
where tno in(select tno from course);

-- 35
select tname, depart
from teacher
where teacher.tno not in (select tno from course);  -- not in 方法效率最差

select tname, depart -- 方法2
from teacher left join course
using(tno) -- using 必须等值连接
where isnull(course.tno); -- 判断tno是否为空，为空返回1，否则返回0

select tname, depart  -- 方法3，同方法2效率差不多
from teacher 
where not exists ( -- exists 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False
	select * from course
	where teacher.tno = course.tno
);

-- 36 -- where → group by → having
select class, count(*)
from student
where ssex = '男'
group by class
having count(ssex) > 1;

-- 37 
select *
from student
where sname not like '王%';

-- 38
select sname, year(now()) - year(SBIRTHDAY) age -- now() 表示现在的时间
from student;

-- 39 DATE_FORMAT(日期,'%m-%d') 月-日，该函数用于以不同的格式显示日期/时间数据。
select sname, sbirthday max
from student
where sbirthday = (select max(sbirthday) from student)
union 
select sname, sbirthday min
from student
where sbirthday = (select min(sbirthday) from student);

-- 40
select *
from student
order by class desc, date(SBIRTHDAY);

-- 41
select tname, cname
from teacher, course
where tsex = '男' and teacher.tno = course.tno;

select tname, cname -- 方法2
from teacher join course
on teacher.tno = course.tno
where tsex = '男';

-- 42
select sno, cno, degree
from score
where degree = (select max(degree) from score);

-- 43
select sname
from student
where ssex = (select ssex from student where sname = '李军');

-- 44
select sname
from student
where ssex = (select ssex from student where sname = '李军')
	and class = (select class from student where sname = '李军');

-- 45
select *
from student, score, course
where student.ssex = '男' and score.sno = student.sno 
	and score.cno = course.cno and cname = '计算机导论';

select *  -- 方法2
from score join (student, course)
using (sno, cno)
where ssex = '男' and cname = '计算机导论';