1 | 实验十一:T-SQL(9) |
#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
select Sdept,ssex,count(ssex)as '人数' from Student group by Sdept,ssex
#2. 查询选修了课程名为“信息系统”的学生学号和姓名1
2
3
4
5select b.cname,a.sname,a.sno
from Student a,course b,sc c
where a.sno=c.sno
and b.cno=c.cno
and b.cname='数据库'
#3. 删除“王兰”所选的全部课程;1
2
3delete from sc
where sno in
(select sno from Student where sname='丁鹏')
#4. 将“计算机”系的学生成绩全部清零;1
2
3
4update sc
set grade = null
where sno in
(select sno from student where sdept ='software')
#5. 查询没有选修1号课程的学生姓名。1
2
3select sname from Student
where sno not in
(select sno from sc where cno =1)
#6. 查询选修了课程1或者选修了课程2的学生姓名。1
2
3
4
5
6
7select sname from Student
where sno in
(select sno from sc where cno ='1')
union
select sname from Student
where sno in
(select sno from sc where cno ='2')
#7. 查询既选修了课程1又选修了课程2的学生姓名1
2
3
4
5
6
7select sname from Student
where sno in
(select sno from sc where cno ='1')
intersect
select sname from Student
where sno in
(select sno from sc where cno ='2')
#8. 查询选修了课程1但没有又选修了课程2的学生姓名1
2
3
4
5
6
7select sname from Student
where sno in
(select sno from sc where cno ='1')
except
select sname from Student
where sno in
(select sno from sc where cno ='2')