Catalog
数据库实验一
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
-- 一:对给定的xsgl数据库完成以下查询要求 :
--对xs表增加身份证号码属性列,要求是18位的字符类型
alter table xs add 身份证号 char(18)

--1.查询有直接先行课的课程的课号,课名和先行课号。
select kc.课程号,kc.先行课号
from kc
where kc.先行课号 !=''

--2.查询先行课号是“J001”号课程的课号和课名
select kc.课程号,kc.课程名
from kc
where kc.课程号 = 'J001'

--3.查询所有的网络工程系姓李,张,王的同学的学号和姓名
select xs.姓名,xs.学号
from xs
where xs.专业 = '网络工程'and xs.姓名 like '[李,张,王]%'

--4.查询不在网络工程和信息管理专业学习的学生的学号和姓名,系别,并对查询结果按照专业的升序和学号的降序排序
select xs.学号,xs.姓名,xs.专业
from xs
where xs.专业 not in ('网络工程','信息管理')
order by xs.专业,xs.学号 desc

--5.查询每门课不及格的学生的人数,显示课号和人数
select cj.课程号,count(*) 人数
from cj
where cj.成绩 < 60
group by cj.课程号

--6.查询年龄不在30-35之间的网络工程系的学生的学号,姓名和年龄
select xs.学号,xs.姓名,datediff(dd,出生时间,getdate())/365 as 年龄
from xs
where datediff(dd,出生时间,getdate())/365 not between 30 and 35

--7.查询没有选修‘J001’号课程的学生的学号(注意去掉重复的元组)
select distinct 学号
from xs
where not exists(
select *
from cj
where xs.学号 = cj.学号 and 课程号 = 'J001'
)

--8.查询每个学生的学号,姓名,出生年份,并给出生年份起别名为chusheng
select xs.学号,xs.姓名,datename(year,xs.出生时间) chusheng
from xs

--9. 查询每个学生的学号,姓名和出生日期(出生日期根据身份证号码查询)
select xs.学号,xs.姓名,datediff(yy,convert(varchar(10),cast(substring(xs.身份证号,7,8)as datetime),120),getdate()) 出生日期
from xs

--10.查询选修J001课程成绩排名第一的同学的学号和成绩
select top 1 cj.学号,cj.成绩
from cj
where cj.课程号 = 'J001'
order by cj.成绩 desc

--11. 查询所有名字中含有’明’或者’丽’的同学的学号,姓名
select xs.学号,xs.姓名
from xs
where xs.姓名 like '%[明,丽]%'

--12. 查询信息管理专业年龄超过20岁的学生的人数
select count(*)
from xs
where xs.专业 = '信息管理' and datediff(dd,出生时间,getdate())/365 > 20

--13. 查询平均成绩超过80分的课程的课程号和平均成绩
select cj.课程号,avg(cj.成绩)
from cj
group by cj.课程号
having avg(cj.成绩) > 80

--14. 查询每个专业所有姓张的人数
select xs.专业,count(*) 人数
from xs
where xs.姓名 like '张%'
group by xs.专业

--15. 查询各种姓氏的人数(假设没有复姓)
select left(xs.姓名,1),count(left(xs.姓名,1)) 人数
from xs
group by left(xs.姓名,1)

--16.查询选修课程超过5门的学生的学号和选课门数,以及平均成绩
select cj.学号,count(cj.课程号) 选课门数,avg(cj.成绩) 平均成绩
from cj
group by cj.学号
having count(cj.课程号) > 5

--17. 查询选修‘J001’课程的成绩排名前五的学生的学号和成绩
select top 5 cj.学号,cj.成绩
from cj
where cj.课程号 = 'J001'
order by cj.成绩 desc

--18.查询每个学生的最低分和选课门数
select cj.学号,min(cj.成绩) 最低分,count(cj.课程号) 选课门数
from cj
group by cj.学号

--19. 查询各个专业各种性别的人数
select xs.专业,xs.性别,count(xs.性别) 人数
from xs
group by xs.专业,xs.性别

--20.查询各个专业男生的人数
select xs.专业,count(xs.性别) 人数
from xs
where xs.性别 = '男'
group by xs.专业

--21. 列出有二门以上课程(含两门)不及格的学生的学号及该学生的平均成绩;
/*select cj.学号,avg(cj.成绩) 平均成绩
from cj
where cj.成绩 < 60
group by cj.学号
having count(cj.成绩) >= 2
单表无法实现,因为where执行之后只剩不及格科目成绩求平均值*/

--22. 显示学号第五位或者第六位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及专业;
select xs.学号,xs.姓名,xs.性别,datediff(yy,xs.出生时间,getdate()) 年龄,xs.专业
from xs
where xs.学号 like '____[1,2,3,4,9]%' or xs.学号 like '_____[1,2,3,4,9]%'

--23. 显示选修课程数最多的学号及选修课程数最少的学号;
select cj.学号
from cj
group by cj.学号
having count(cj.课程号)>=all(
select count(cj.课程号)
from cj
group by cj.学号
)

select cj.学号
from cj
group by cj.学号
having count(cj.课程号)<=all(
select count(cj.课程号)
from cj
group by cj.学号
)

--24. 查询选修了A001或者A002或者J001或者J002课程的学生的学号和课程号
select cj.学号,cj.课程号
from cj
where cj.课程号 in ('A001','A002','J001','J002')

--25. 查询姓名为两个字的不同姓氏的人数,输出姓氏,人数。
select left(xs.姓名,1) 姓氏,count(left(xs.姓名,1)) 人数
from xs
where len(xs.姓名) = 2
group by left(xs.姓名,1)

--二:对书上第二章课后习题的4的SPJ数据库各表查询:
--1.求供应工程J1零件的供应商号码SNO
select SPJ.sno
from SPJ
where SPJ.jno='J1'

--2.求查询每个工程使用不同供应商的零件的个数
select SPJ.jno,SPJ.pno,sum(SPJ.qty) 个数
from SPJ
group by SPJ.jno,SPJ.pno

--3.求供应工程使用零件P3数量超过200的工程号JNO
select SPJ.jno
from SPJ
where SPJ.pno = 'P3' and SPJ.qit > 200

--4.求颜色为红色和蓝色的零件的零件号和名称
select distinct pno, pname
from p
where color in ('蓝','红')

--5.求使用零件数量在200-400之间的工程号
select SPJ.jno
from SPJ
group by SPJ.jno
having sum(SPJ.qty) between 200 and 400

--6.查询每种零件的零件号,以及使用该零件的工程数。
select SPJ.pno,count(DISTINCT SPJ.jno) 工程数
from SPJ
group by SPJ.pno
Author: Christopher Shen
Link: https://www.pasxsenger.com/2019/10/11/数据库实验一/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Donate
  • 微信
  • 支付寶