1 | 实验十:T-SQL(8) |
#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;
#1.查询每一门课的间接先修课(即先修课的先修课)1
2
3select a.cno as '课程号' , a.cname as '课程名', b.cpno as '间接先修课'
from course a,course b
where b.cno = a.cpno
#2. 查询每个学生的学号、姓名、选修的课程名及成绩1
2
3select a.sno as '学号',a.sname as '姓名',cno as '选修科目',grade as '成绩'
from Student a,sc b
where a.sno=b.sno
#3. 查询与“刘晨”在同一个系学习的学生。1
2
3
4select a.*
from Student a,Student b
where b.sname='丁鹏'
and a.sdept=b.sdept
#4. 查询选修了课程名为“信息系统”的学生学号和姓名1
2
3
4
5select a.sno,a.sname
from Student a,course b,sc c
where b.cname='数据库'
and c.cno=b.cno
and a.sno=c.sno
#5. 找出每个学生超过他选修课程平均成绩的课程号与课程名。1
2
3
4
5
6
7select b.sno,a.cno ,a.cname from
course a,
sc b,
(select sno,AVG(grade) as 'avge' from sc group by sno)c
where a.cno=b.cno
and b.sno=c.sno
and b.grade >= c.avge