转载 https://blog.csdn.net/qq_23994787/article/details/77161741
一、设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。用SQL语句创建四个表并完成相关题目。
表（一）Student (学生表)
| 属性名 | 数据类型 | 可否为空 | 含 义 |
| Sno | varchar (20) | 否 | 学号（主码） |
| Sname | varchar (20) | 否 | 学生姓名 |
| Ssex | varchar (20) | 否 | 学生性别 |
| Sbirthday | datetime | 可 | 学生出生年月 |
| Class | varchar (20) | 可 | 学生所在班级 |
表（二）Course（课程表）
| 属性名 | 数据类型 | 可否为空 | 含 义 |
| Cno | varchar (20) | 否 | 课程号（主码） |
| Cname | varchar (20) | 否 | 课程名称 |
| Tno | varchar (20) | 否 | 教工编号（外码） |
表（三）Score(成绩表)
| 属性名 | 数据类型 | 可否为空 | 含 义 |
| Sno | varchar (20) | 否 | 学号（外码） |
| Cno | varchar (20) | 否 | 课程号（外码） |
| Degree | Decimal(4,1) | 可 | 成绩 |
| 主码：Sno+ Cno | |||
表（四）Teacher(教师表)
| 属性名 | 数据类型 | 可否为空 | 含 义 |
| Tno | varchar (20) | 否 | 教工编号（主码） |
| Tname | varchar (20) | 否 | 教工姓名 |
| Tsex | varchar (20) | 否 | 教工性别 |
| Tbirthday | datetime | 可 | 教工出生年月 |
| Prof | varchar (20) | 可 | 职称 |
| Depart | varchar (20) | 否 | 教工所在部门 |
表1-2数据库中的数据
表（一）Student
| Sno | Sname | Ssex | Sbirthday | class |
| 108 | 曾华 | 男 | 1977-09-01 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 |
表（二）Course
| Cno | Cname | Tno |
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
表（三）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 |
表（四）Teacher
| Tno | Tname | Tsex | Tbirthday | Prof | Depart |
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
-
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
-
-
select Sname,Ssex,
Class
from student
-
2、 查询教师所有的单位即不重复的Depart列。
-
-
select
distinct Depart fromteacher
-
3、 查询Student表的所有记录。
-
-
select *
from student
-
4、 查询Score表中成绩在
60到
80之间的所有记录。
-
-
select *
from Score
where Degree between60and
80
-
5、 查询Score表中成绩为
85，
86或
88的记录。
-
-
select *
from Score
where Degree
in(
85,
86,
88)
-
6、 查询Student表中“
95031”班或性别为“女”的同学记录。
-
-
select *
from Student
where
class=
'95031'orSsex=
'女'
-
7、 以
Class降序查询Student表的所有记录。
-
-
select *
from student
order
by
class
desc
-
8、 以Cno升序、Degree降序查询Score表的所有记录。
-
-
select *
from Score
order
by cno
asc,degree
desc
-
9、 查询“
95031”班的学生人数。
-
-
select
count(*) fromstudentwhere
class=
'95031'
-
10、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
-
-
select Sno,Cno
from ScorewhereDegree=(selectmax(Degree)fromScore)
-
-
select Sno,Cno
from Scoreorderby Degree desclimit
0,
1
-
11、查询每门课的平均成绩。
-
-
select Cno,
avg(degree)
from Score groupbyCno
-
12、查询Score表中至少有
5名学生选修的并以
3开头的课程的平均分数。
-
-
select
avg(Degree) fromscorewhere Cno
like
'3%'andCno
in(selectCno
from score
group byCno
having
count(*)>=
5) 用
in不用= 是因为可能会有多个
-
-
简单写法：selectavg(Degree)fromscore
where Cno
like
'3%'
and
group
by Cno havingcount(*)>=
5
-
13、查询分数大于
70，小于
90的Sno列。
-
-
select Sno
from Score
where degree>
70 anddegree<
90
-
14、查询所有学生的Sname、Cno和Degree列。
-
-
select Sname, Cno,Degree
from Score , student
where Score.Sno=student.Sno
-
15、查询所有学生的Sno、Cname和Degree列。
-
-
select Sno,Cname,Degree
from Score , Course
where Score.Cno=Course.Cno
-
16、查询所有学生的Sname、Cname和Degree列。
-
-
select Sname,Cname,Degree
from student,course,score
where student.Sno=score.Sno
and course.Cno=score.Cno
join ..
on
-
-
写法：selectSname,Cname,Degreefromstudent
join score onstudent.Sno=score.Snojoincourse
on course.Cno=score.Cno
-
17、查询“
95033”班学生的平均分。
-
-
select
avg(degree)
as
'class=95033'fromScore whereSno
in (selectSno
from Student whereClass=
'95033')
-
18、 假设使用如下命令建立了一个grade表：
-
create
table grade(
low
int(
3),upp
int(
3),
rank
char(
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’)
-
现查询所有同学的Sno、Cno和
rank列。
-
-
select Sno,Cno,
rank
from Score,grade
where degree
between lowandupp
-
19、查询选修“
3
-105”课程的成绩高于“
109”号同学成绩的所有同学的记录。
-
-
109同学，选修是
3
-105课的
-
-
select *
from score
where Cno=
'3-105'anddegree>(selectmax(degree )fromScore
where Sno=
'109'andCno=
'3-105')
-
-
109同学，没有选修
3
-105课
-
-
select *
from score
where Cno=
'3-105'anddegree>(selectmax(degree )fromScore
where Sno=
'109')
-
and degree<( selectmax(degree )fromScore
where sno
in(selectSnofrom score
group bySnohaving
count(*)>
1))
-
-
选了多门课程并且是这个课程下不是最高分的
-
-
select *
from score a
where Sno
in(selectSno
from score
group bySno
having
count(*)>
1)anddegree<(
select
max(degree )fromScore b
where b.cno = a.cno)
-
21、查询成绩高于学号为“
109”、课程号为“
3
-105”的成绩的所有记录。
-
-
Select *
from score
where degree>(selectdegreefrom Score
where Sno=
'109'andCno=
'3-105')
-
22、查询和学号为
108、
101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
-
-
select sno,sname,sbirthday
from student
where
year(sbirthday) = (selectyear(sbirthday)fromstudent
where sno=
'108')
-
-
select sno,sname,sbirthday
from student
where
year(sbirthday) = (selectyear(sbirthday)fromstudent
where sno=
'101')
-
23、查询“张旭“教师任课的学生成绩。
-
-
select Sno,degree
from score,Course wherescore.Cno=Course.CnoandCourse.Tno= (selectTnofrom Teacher
where Tname=
'张旭')
-
-
select degree
from scorewhereCno
in (selectcno
from course whereTno= (selectTnofrom Teacher
where Tname=
'张旭' ) )
-
24、查询选修某课程的同学人数多于
5人的教师姓名。
-
-
select Tname
from Teacher, CoursewhereTeacher.Tno=Course.TnoandCourse.Cno =(selectCnofrom Score
group byCnohaving
count(*)>
5)
-
-
select Tname
from Teacherwheretno=(
select Tno fromCourse
where cno=( selectCno
from Score groupbyCno havingcount(*)>
5 ))
-
25、查询
95033班和
95031班全体学生的记录。
-
-
select *
from student
where
class
in(
'95033',
'95031')
-
26、查询存在有
85分以上成绩的课程Cno.
-
-
select Cno
from score
where degree>
85
-
27、查询出“计算机系“教师所教课程的成绩表。
-
-
select *
from course
where cno
in(selectcno
from course
where tnoin(selecttno
from teacher
where Depart=
'计算机系'))
-
28、查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。
-
-
select Tname,Prof
from TeacherwhereDepart =
'计算机系'andProf notin(selectProf
from Teacher whereDepart =
'电子工程系')<br>
union<br>selectTname,Prof
from Teacherwhere Depart =
'电子工程系'andProf notin(selectProf
from Teacher whereDepart =
'计算机系')
-
29、查询选修编号为“
3
-105“课程且成绩至少高于选修编号为“
3
-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
-
any:代表括号中任意一个成绩就可以
-
-
select Cno,Sno,Degree
from score
where cno=
'3-105'anddegree >
any(selectdegreefrom score
where cno=
'3-245')
order bydegree
desc
-
30、查询选修编号为“
3
-105”且成绩高于选修编号为“
3
-245”课程的同学的Cno、Sno和Degree.
-
-
all:代表括号中的所有成绩
-
select Cno,Sno,Degree
from score
where cno=
'3-105'anddegree >all(selectdegreefrom score
where cno=
'3-245')
order bydegree
desc
-
31、查询所有教师和同学的
name、sex和birthday.
-
-
select tname,tsex,tbirthday
from Teacher
union selectsname,ssex,sbirthdayfromStudent
-
32、查询所有“女”教师和“女”同学的
name、sex和birthday.
-
-
select Tname,Tsex,Tbirthday
from Teacher
where Tsex=
'女'unionselect Sname,Ssex,Sbirthdayfrom Student
where Ssex=
'女'
-
33、查询成绩比该课程平均成绩低的同学的成绩表。
-
-
select *
from score a
where degree < (selectavg(degree)fromscore b
where b.cno=a.cno)
-
34、查询所有任课教师的Tname和Depart.
-
-
select Tname,Depart
from Teacher
where tnoin(selecttnofrom course )
-
35、查询所有未讲课的教师的Tname和Depart.
-
-
select Tname,Depart
from Teacher
where Tnonotin (selectTno
from Course wherecno
in (selectcno
from score ))
-
36、查询至少有
2名男生的班号。
-
-
select
class
from studentwheressex=
'男'groupby
class havingcount(*)>
1
-
37、查询Student表中不姓“王”的同学记录。
-
-
select *
from Student
where Sname
not
like
'王%%'
-
38、查询Student表中每个学生的姓名和年龄。
-
-
select Sname,
year(
now())-
year(sbirthday) fromStudent
-
39、查询Student表中最大和最小的Sbirthday日期值。
-
-
select
Max(Sbirthday ),
Min(Sbirthday )fromStudent
-
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
-
-
select *
from Student
order
by
class
desc, Sbirthday
-
41、查询“男”教师及其所上的课程。
-
-
select Tname,Cname
from course,teacher
where course.tno= teacher.tno
and teacher.Tsex=
'男'
-
42、查询最高分同学的Sno、Cno和Degree列。
-
-
select Sno,Cno,Degree
from score
where degree=(selectmax(degree)fromscore)
-
-
排序写法：
select Sno,Cno,Degree
from score
order bydegreedesc
limit
0,
1
-
-
43、查询和“李军”同性别的所有同学的Sname.
-
-
select Sname
from StudentwhereSsex = (selectSsexfrom Student
where Sname=
'李军')
-
44、查询和“李军”同性别并同班的同学Sname.
-
-
select Sname
from StudentwhereSsex = (selectSsexfrom Student
where Sname=
'李军')
and
class=(
select classfromstudent
where Sname=
'李军')
-
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
-
-
select Sno,Cno,degree
from score
where Cno=(selectCno
from course whereCname=
'计算机导论')andSno
in(selectSno
from student
where Ssex=
'男')
扫一扫
794
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
