写完作业五一就可以玩了
话说在文章前面写东西的习惯怎么改不掉，qaq

---

一、题目

设如下四个表，先创建表， 插入数据， 然后做后面的查询：
student (学生信息表）
sno sname sex birthday class
108 曾华男09/01/77 95033
105 匡明男10/02/75 95031
107 王丽女01/23/76 95033
101 李军男02/20/76 95033
109 王芳女02/10/75 95031
103 陆军男06/03/74 95031
teacher(老师信息表）
tno tname sex birthday prof depart
804 李诚男12/02/58 副教授计算机系
856 李旭男03/12/69 讲师电子工程系
825 王萍女05/05/72 助教计算机系
831 刘冰女08/14/77 助教电子工程系
course(课程表）
cno cname tno
3-105 计算机导论825
3-245 操作系统804
6-166 数字电路856
9-888 高等数学825
score(成绩表）
sno cno degree
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
101 3-105 64
107 3-105 91
108 3-105 78
101 6-166 85
107 6-166 79
108 6-166 81
请写出下列查询语句并给出结果
1、列出student表中所有记录的sname、sex和class列。
2、显示教师所有的单位即不重复的depart列。
3、显示学生表的所有记录。
4、显示score表中成绩在60到80之间的所有记录。
5、显示score表中成绩为85，86或88的记录。
6、显示student表中“95031”班或性别为“女”的同学记录。
7、以class降序显示student表的所有记录。
8、以cno升序、degree降序显示score表的所有记录。
9、显示“98031”班的学生人数。
10、显示score表中的最高分的学生学号和课程号。
11、显示“3-105”号课程的平均分。
12、显示score表中至少有5名学生选修的并以3开头的课程号的平均分数。
13、显示最低分大于70，最高分小于90 的sno列。
14、显示所有学生的 sname、 cno和degree列。
15、显示所有学生的 sname、 cname和degree列。
16、列出“95033”班所选课程的平均分。
17、显示选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
18、显示score中选修多门课程的同学中分数为非最高分成绩的记录。
19、显示成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
20、显示出和学号为“108”的同学同年出生的所有学生的sno、sname和 birthday列。
21、显示“张旭”老师任课的学生成绩。
22、显示选修某课程的同学人数多于5人的老师姓名。
23、显示“95033”班和“95031”班全体学生的记录。
24、显示存在有85分以上成绩的课程cno。
25、显示“计算机系”老师所教课程的成绩表。
26、显示“计算机系”和“电子工程系”不同职称的老师的tname和prof。
27、显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。
28、显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。
29、列出所有任课老师的tname和depart。
30、列出所有未讲课老师的tname和depart。
31、列出所有老师和同学的 姓名、性别和生日。
*32、检索所学课程包含学生“103”所学课程的学生学号。
*33、检索选修所有课程的学生姓名。

二、我的垃圾代码

2.1 创建表并插入数据

--在这里删除创建的表，以便后续可以重新新建，上机演示
drop table score;drop table course;drop table student;drop table teacher;--创建学生信息表，命名student表
create table student (  sno			char(7)		primary key,	--学号，是主码sname		char(10)	not null,		--姓名sex			char(2),					--性别birthday	char(20),					--年龄class		int							--所在班级
)  ;--下面创建老师信息表，表名为teacher
create table teacher (tno			char(10)	primary key,	--老师编号是主健tname		char(20),					--老师姓名sex			char(4),					--性别birthday	char(20),					--生日prof		char(20),					--职称depart		char(20)					--所在系);--创建课程表，表名是course
create table course (cno		char(10)	not null ,					--课程编号cname	char(20)	not null,					--课程名称tno		char(10)	references teacher(tno),	--上面是声明sno是外健，参照student表的tnoprimary key (cno)								--指明cno是主码
);--创建成绩表，表名是score
create table score(sno		char(7)		not null,				--学生编号cno		char(10)	not null,				--课程号degree	smallint ,							--成绩primary key(sno,cno),						--声明sno,cno是主码foreign key(sno) references student(sno),	--上面是声明sno是外健，参照student表的snoforeign key(cno) references course(cno)		--上面是声明cno是外健，参照course表的cno
);--插入所有表的数据insert into student(sno, sname, sex,birthday,class) values ('108', '曾华', '男','09/01/77',95033 );
insert into student(sno, sname, sex,birthday,class) values ('105', '匡明', '男','10/02/75',95031 );
insert into student(sno, sname, sex,birthday,class) values ('107', '王丽', '女','01/23/76',95033 );
insert into student(sno, sname, sex,birthday,class) values ('101', '李军', '男','02/20/76',95033 );
insert into student(sno, sname, sex,birthday,class) values ('109', '王芳', '女','02/10/75',95031 );
insert into student(sno, sname, sex,birthday,class) values ('103', '陆军', '男','06/03/74',95031 );insert into teacher(tno, tname, sex,birthday,prof,depart) values ('804', '李诚', '男','12/02/58','副教授','计算机系' );
insert into teacher(tno, tname, sex,birthday,prof,depart) values ('856', '李旭', '男','03/12/69','讲师','电子工程系' );
insert into teacher(tno, tname, sex,birthday,prof,depart) values ('825', '王萍', '女','05/05/72','助教','计算机系' );
insert into teacher(tno, tname, sex,birthday,prof,depart) values ('831', '刘冰', '女','08/14/77','助教','电子工程系' );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', '高等数学', '825' );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-166', '79' );
insert into score(sno, cno,degree) values ('108', '6-166', '81' );--查看一下数据select  * from student
select  * from teacher
select  * from course
select  * from score

2.2 开始作业

1、列出student表中所有记录的sname、sex和class列。

select sname, sex, class from student;

2、显示教师所有的单位即不重复的depart列。

select distinct depart from teacher;

DISTINCT 用于返回唯一不同的值。

3、显示学生表的所有记录。

select *from student
--或者
select sno as 学号, sname as 姓名, sex as 性别 ,birthday as 出生日期  from student;--as别名

4、显示score表中成绩在60到80之间的所有记录。

select * from score where degree between 60 and 80

5、显示score表中成绩为85，86或88的记录。

select * from score where degree=85 or degree=86 or degree=88
--或者select * from score where degree in (85, 86, 88);

6、显示student表中“95031”班或性别为“女”的同学记录。

select * from student where class = 95031 or sex = '女'

7、以class降序显示student表的所有记录。

select * from student order by class desc;

升序是asc，降序是desc

8、以cno升序、degree降序显示score表的所有记录。

select * from score by cno asc, degree desc

9、显示“98031”班的学生人数。

select count(*) as 学生人数 from student where class = 

10、显示score表中的最高分的学生学号和课程号。

select sno,cno ,degree as 最高分 from score
where degree = (select max(degree) from score)

11、显示“3-105”号课程的平均分。

select avg(degree)  as 课程平均分 from score where cno='3-105'

12、显示score表中至少有5名学生选修的并以3开头的课程号的平均分数。

select cno,avg(degree) from score where cno like '3%'--3开头的
group by cno 
having count (*)>=5

考察group

13、显示最低分大于70，最高分小于90 的sno列。

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

14、显示所有学生的 sname、 cno和degree列。

select sname,cno,degree from score,student
where student.sno=score.sno;

这里考察多个表相连

15、显示所有学生的 sname、 cname和degree列。

select sname, cname, degree from course,student,score
where student.sno=score.sno and course.cno=score.cno;
--三个表相连

16、列出“95033”班所选课程的平均分。

select cno,avg(degree) as 平均分  from student , score 
where student.sno=score.sno and student.class='95033'   
group by  cno;

17、显示选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

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

18、显示score中选修多门课程的同学中分数为非最高分成绩的记录。

select a.sno, a.degree, a.cno from score a, score b
where a.sno=b.sno and a.degree<b.degree ;

19、显示成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

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

20、显示出和学号为“108”的同学同年出生的所有学生的sno、sname和 birthday列。

select sno,sname,birthday from student
where birthday =(select birthday from student where sno ='108')

21、显示“李旭”老师任课的学生成绩。

select cno,sno, degree from score 
where cno=(select cno from course where tno =(select tno from teacher where tname='李旭'))

22、显示选修某课程的同学人数多于5人的老师姓名。

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

23、显示“95033”班和“95031”班全体学生的记录。

select * from student where class in (21033,21031);

24、显示存在有85分以上成绩的课程cno。

select cno from score
where degree>85 
group by cno;

25、显示“计算机系”老师所教课程的成绩表。

select sno,cno,degree from score 
where cno in
(select cno from course where tno in
(select tno from teacher where depart like '计算机系')
)

26、显示“计算机系”和“电子工程系”不同职称的老师的tname和prof。

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

27、显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。

select cno sno,degree from score
where cno='3-105' and degree>any(select degree from score where cno='3-245')
order by degree desc;

28、显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。

select cno,sno,degree from score 
where cno='3-105'and degree > all
(select degree from score where cno = '3-245')

29、列出所有任课老师的tname和depart。

select tname ,depart from teacher

30、列出所有未讲课老师的tname和depart。

select tname ,depart from teacher 
where  tno  not in 
(select tno from course)

31、列出所有老师和同学的 姓名、性别和生日。

 select tname,sex,birthday  from teacherunion select sname,sex,birthday from student

*32、检索所学课程包含学生“103”所学课程的学生学号。

Select distinct sno  from score x
Where not exists(select * from score ywhere y.sno=103 and not exists(select * from score zwhere z.sno=x.sno and z.cno=y.cno) ) 

*33、检索选修所有课程的学生姓名。

select student.sname from student where not exists     (select *  from  coursewhere not exists ( select * from  score wherestudent.sno=score.sno and course.cno=score.cno))

2.3 完整代码

--select  子句指定 要显示的属性列 
--from子句指定查询对象
--where子句指定查询条件 
--group by子句对查询结果按指定列的值分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数
--having短语筛选出只有满足指定条件的组
--order by子句对查询结果按指定列值升序或降序排序  ASC DESC 
--小写字母表示系名lower（stept）
--distinct不显示重复数值，all
--where子句作用于基表或视图，从中选择满足条件的元组；having子句作用于组，从中选择满足条件的组--在这里删除创建的表，以便后续可以重新新建，上机演示
drop table score;drop table course;drop table student;drop table teacher;--创建学生信息表，命名student表
create table student (  sno			char(7)		primary key,	--学号，是主码sname		char(10)	not null,		--姓名sex			char(2),					--性别birthday	char(20),					--年龄class		int							--所在班级
)  ;--下面创建老师信息表，表名为teacher
create table teacher (tno			char(10)	primary key,	--老师编号是主健tname		char(20),					--老师姓名sex			char(4),					--性别birthday	char(20),					--生日prof		char(20),					--职称depart		char(20)					--所在系);--创建课程表，表名是course
create table course (cno		char(10)	not null ,					--课程编号cname	char(20)	not null,					--课程名称tno		char(10)	references teacher(tno),	--上面是声明sno是外健，参照student表的tnoprimary key (cno)								--指明cno是主码
);--创建成绩表，表名是score
create table score(sno		char(7)		not null,				--学生编号cno		char(10)	not null,				--课程号degree	smallint ,							--成绩primary key(sno,cno),						--声明sno,cno是主码foreign key(sno) references student(sno),	--上面是声明sno是外健，参照student表的snoforeign key(cno) references course(cno)		--上面是声明cno是外健，参照course表的cno
);--插入所有表的数据insert into student(sno, sname, sex,birthday,class) values ('108', '曾华', '男','09/01/77',95033 );
insert into student(sno, sname, sex,birthday,class) values ('105', '匡明', '男','10/02/75',95031 );
insert into student(sno, sname, sex,birthday,class) values ('107', '王丽', '女','01/23/76',95033 );
insert into student(sno, sname, sex,birthday,class) values ('101', '李军', '男','02/20/76',95033 );
insert into student(sno, sname, sex,birthday,class) values ('109', '王芳', '女','02/10/75',95031 );
insert into student(sno, sname, sex,birthday,class) values ('103', '陆军', '男','06/03/74',95031 );insert into teacher(tno, tname, sex,birthday,prof,depart) values ('804', '李诚', '男','12/02/58','副教授','计算机系' );
insert into teacher(tno, tname, sex,birthday,prof,depart) values ('856', '李旭', '男','03/12/69','讲师','电子工程系' );
insert into teacher(tno, tname, sex,birthday,prof,depart) values ('825', '王萍', '女','05/05/72','助教','计算机系' );
insert into teacher(tno, tname, sex,birthday,prof,depart) values ('831', '刘冰', '女','08/14/77','助教','电子工程系' );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', '高等数学', '825' );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-166', '79' );
insert into score(sno, cno,degree) values ('108', '6-166', '81' );--查看一下数据select  * from student
select  * from teacher
select  * from course
select  * from score--开始作业--1、列出student表中所有记录的sname、sex和class列。
select sname, sex, class from student;--2、显示教师所有的单位即不重复（distinct）的depart列。  
select distinct depart from teacher;--3.显示学生表的所有记录
select *from student
--或者
select sno as 学号, sname as 姓名, sex as 性别 ,birthday as 出生日期  from student;--as别名--4、显示score表中成绩在60到80之间的所有记录select * from score where degree between 60 and 80; --所有记录就是select*--5、显示score表中成绩为85，86或88的记录。select * from score where degree in (85, 86, 88);--或者select * from score where degree=85 or degree=86 or degree=88--6、显示student表中“95031”班或性别为“女”的同学记录。select * from student where class=98031 or sex='女';--7、以class降序显示student表的所有记录。
select * from student order by class desc;--8、以cno升序、degree降序显示score表的所有记录。asc为缺省
select * from score order by cno asc, degree  desc;
--升序是asc，降序是desc--9、显示“98031”班的学生人数。
select count(*) as 学生人数 from student where class=98031;--10、显示score表中的最高分的学生学号和课程号。max
select sno ,cno,degree as 最高分 from score 
where degree =(select max(degree) from score)--11、显示“3-105”号课程的平均分。avg
select avg(degree)  as 课程平均分 from score where cno='3-105'--12、显示score表中至少有5名学生选修的并以3开头的课程号的平均分数。
select cno,avg(degree) from score
where cno like '3%'--3开头的
group by cno 
having count (*)>=5--13、显示最低分大于70，最高分小于90 的sno列。
select sno from score 
group by sno
having min(degree)>70 and max(degree)<90;--14、显示所有学生的 sname、 cno和degree列。
select sname,cno,degree from score,student
where student.sno=score.sno;--15、显示所有学生的 sname、 cname和degree列。
select sname, cname, degree from course,student,score
where student.sno=score.sno and course.cno=score.cno;
--三个表相连--16、列出“21033”班所选课程的平均分。
select cno,avg(degree) as 平均分  from student , score 
where student.sno=score.sno and student.class='95033'   
group by  cno;--17、显示选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select  sno, cno, degree from score 
where cno='3-105' and degree>(select degree from score where  cno='3-105'   and sno=109)--***18、显示score中选修多门课程的同学中分数为非最高分成绩的记录。
select a.sno, a.degree, a.cno from score a, score b
where a.sno=b.sno and a.degree<b.degree ;--19、显示成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select cno,sno,degree from score
where degree>(select degree from score  where sno=109 and cno ='3-105' )
--Select x.cno, x.sno, x.degree
--from sc x, sc y
--Where x.degree>y.degree and y.sno=109 and y.cno='3-105';--20、显示出和学号为“108”的同学同年出生的所有学生的sno、sname和 birthday列。
select sno,sname,birthday from student
where birthday =(select birthday from student where sno ='108')--select x.sno,x.sname,x.birthday
--from student x,student y
--Where x.sno=y.sno and y.sno='108'--21、显示“李旭”老师任课的学生成绩。
select cno,sno, degree from score 
where cno=(select cno from course where tno =(select tno from teacher where tname='李旭'))
--Select cno, sno, degree from sc
--Where cno=(select x.cno from course x, teacher y
--                     where x.tno=y.tno and y.tname='李旭')--***22、显示选修某课程的同学人数多于5人的老师姓名。
select tname from teacher 
where tno in (
select tno from score,course 
where score.cno =course.cno
group by tno
having count(tno )>5)--select tname from teacher 
--where tno in 
--(select tno from sc,course
--where sc.cno=course.cno
--group by tno
--having count(*)>5)--select tname from teacher 
--where tno in (select x.tno from course x,sc y
--					where x.cno=y.cno
--					group by x.tno
--					having count(x.tno)>5)--23、显示“95033”班和“95031”班全体学生的记录。
--select * from student 
--where class=21033 or class=21031
select * from student where class in (21033,21031);select * from score--24、显示存在有85分以上成绩的课程cno。
select cno from score
where degree>85 
group by cno;--select distinct cno from sc
--where degree>85 --25、显示“计算机系”老师所教课程的成绩表。
select sno,cno,degree from score 
where cno in
(select cno from course where tno in
(select tno from teacher where depart like '计算机系')
)--26、显示“计算机系”和“电子工程系”不同职称的老师的 tname和prof。select tname, prof from teacherwhere depart='计算机系' and prof  not in(select prof from teacher where depart='电子工程系')--27、显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。select cno sno,degree from score
where cno='3-105' and degree>any(select degree from score where cno='3-245')
order by degree desc;--28、显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。
select cno,sno,degree from score 
where cno='3-105'and degree > all
(select degree from score where cno = '3-245')--29、列出所有任课老师的tname和depart。
select tname ,depart from teacher--30、列出所有未讲课老师的tname和depart。
select tname ,depart from teacher 
where  tno  not in 
(select tno from course)--Select tname,dept  from  teacher  a
--  where tno NOT in
--      ( select tno  from course b
--            where a.tno=b.tno)--31、列出所有老师和同学的 姓名、性别和生日。select tname,sex,birthday  from teacherunion select sname,sex,birthday from student--not exists  全称量词
--exists 存在量词--*32、检索所学课程包含学生“103”所学课程的学生学号。Select distinct sno  from score x
Where not exists(select * from score ywhere y.sno=103 and not exists(select * from score zwhere z.sno=x.sno and z.cno=y.cno) ) --*33、检索选修所有课程的学生姓名。
select student.sname from student where not exists     (select *  from  coursewhere not exists ( select * from  score wherestudent.sno=score.sno and course.cno=score.cno))--插入两条记录
insert into score(sno, cno,degree) values ('103', '9-888', '81' );
insert into score(sno, cno,degree) values ('103', '6-166', '81' );
--删除两条记录
delete from score where cno ='9-888'
delete from score where sno ='103' and cno = '6-166' 
select * from score