1 | 实验六:T-SQL(4) |
#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 sno,Ssex,Sage,Sdept from Student where Sname='丁鹏';
#2.查询选修课程名为“数据库”课程的同学的学号、姓名、院系;1
2
3select sno,Sname,Sdept from Student where sno in
(select Sno from SC where Cno in
(select Cno from Course where Cname = '数据库'));
#3.查询没被任何同学选的课程号及课程名;1
2select Cno,Cname from Course where Cno not in
(select distinct Cno from SC);
#4查询与“丁鹏”在同一院系的同学的学号、姓名、性别、院系;1
2select sno,Sname,Ssex,Sdept from Student where Sdept in(
select Sdept from Student where Sname='丁鹏');