1 | 实验八:T-SQL(6) |
#0.建表代码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
72create database Students
on
primary(name=Students,
filename='D:\test\test.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
)
log on
(name=Studentslog,
filename='D:\test\testlog.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use Students;
create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno char(6),
Ccredit int null,
primary key(cno),
)
go
create table SC
(
Sno char(8) not null foreign key references Student(sno),
Cno char(6) not null foreign key references course(cno),
primary key(Sno,Cno),
Grade int null
)
insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')
insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)
insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)
alter table course add foreign key(cpno) references course(cno)
select * from Course;
select * from SC;
select * from Student;
建表示例图
![DQQPAUM`X~V~S4D@T1O_1P.png-14.4kB
#1. 查询选修了课程的学生人数1
select count(distinct Sno) from SC
#2. 计算1号课程的学生平均成绩1
select AVG(Grade) as 平均成绩 from SC where Cno='1'
#3. 查询选修1号课程的学生最高分数、最低分数1
select MAX(Grade)as 最高成绩,MIN(Grade)as 最低成绩 from SC where Cno='1'
#4. 列出每个课程号及相应的选课人数1
select Cno ,COUNT(Sno) as 选课人数 from SC group by Cno
#5. 查询选修了3门以上课程的学生学号1
select Sno from SC group by Sno having COUNT(*)>3
#6. 列出每门课程的最高分数、最低分数1
select cno,MAX(Grade)as 最高成绩,MIN(Grade)as 最低成绩 from SC group by Cno