1 | 实验十二:T-SQL(10) |
#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
72
73
74
75
76create 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',2,89)
insert into SC values('19130203',3,79)
insert into SC values('19130204',4,69)
insert into SC values('19130205',5,59)
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
3
4
5
6
7
8create view Is_S1(sno,sname,grade1,grade2)
as
select a.sno,a.sname,b.Grade,c.Grade
from Student a
left outer join
(select sno,grade from sc,course where sc.cno=cource.cno and course.cname='数据结构') b on b.sno=a.sno
left outer join
(select sno,grade from sc,course where sc.cno=cource.cno and course.cname='数据库') c on c.sno=a.sno
#2. 查询选修了全部课程的同学的学号、姓名1
2
3
4
5
6
7
8
9
10
11
12select a.sname,a.sno
from Student a
where not exists
(select *
from Course
where not exists
(select *
from SC
where Sno= a.Sno
and Cno= Course.Cno
)
)
#3. 查询平均成绩在60分以上的同学的学号、姓名1
2
3
4
5
6
7
8
9select a.sno, b.sname,a.平均成绩
from Student b,
(
select sno,AVG(grade) as '平均成绩'
from sc
group by sno
having AVG(grade)>60
) a
where a.sno = b.sno
#4. 查询平均成绩在全系平均成绩之上的同学的学号、姓名1
2
3
4
5
6
7
8
9
10
11go
create view S_G(sno,sname,Gavg)
as
select student.sno,sname,AVG(grade)
from sc,student
where student.sno=sc.sno
group by student.sno,student.sname
go
select *
from S_G
where Gavg>(select AVG(grade) from sc)