1 | 实验十三:T-SQL(11) |
#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. 列出每门课程成绩都在90分以上的学生学号、姓名1
2
3
4
5
6
7
8
9//拿出每条记录,若存在一门课小于90分,则不要
select sno,sname
from Student a
where not exists
(
select grade from sc
where a.sno=sc.sno
and sc.grade<=90
)
#2. 查询没有选修数据结构课程的学生学号与姓名1
2
3
4
5
6
7
8
9
10//拿出每一条记录,对比是否选择了数据结构
select a.sno,a.sname
from Student a
where not exists
(
select a.* from course b,sc c
where a.sno=c.sno
and b.cname='数据结构'
and c.cno=b.cno
)
#3. 查询既没有选修数据结构又没有选修数据库课程的学生学号与姓名1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20//利用交集,结合上题
select a.sno,a.sname
from Student a
where not exists
(
select a.* from course b,sc c
where a.sno=c.sno
and b.cname='数据结构'
and c.cno=b.cno
)
intersect
select a.sno,a.sname
from Student a
where not exists
(
select a.* from course b,sc c
where a.sno=c.sno
and b.cname='数据库'
and c.cno=b.cno
)
#4. 将所有选择“数据结构”课程的“计算机系”的学生成绩置为0分1
2
3
4
5
6
7
8
9
10
11//结合第二题
update sc
set grade = 98
where sno not in
(
select a.sno from student a ,course b,sc c
where a.sno=c.sno
and b.cname='数据结构'
and c.cno=b.cno
and a.sdept='software'
)