1 | 实验七:T-SQL(5) |
#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
2select Cno as '课程编号',Cname as '课程名称' from Course where Cno in
(select distinct Cno from SC);
##2. 查询年龄在19到21岁之间的同学的学号、姓名、性别,并按学号降序显示1
select sno,sname,ssex from Student where Sage>=19 and Sage<=21 order by sno desc
##3. 查询既不是英语系、数学系,也不是计算机科学系的学生的学号、姓名和性别1
2select sno,sname,ssex from Student where Sdept!='English' and
Sdept!='Math' and Sdept!='Computer'
##4. 查询所有姓王的学生的学号、姓名和性别,并按学号升序显示1
select sno,sname,ssex from Student where sname like '王%' order by sno
##5. 查询所有不姓刘的学生姓名1
select sname from Student where sname not like '刘%'
##6. 查询名字中第2个字为”敏”字的学生的学号和姓名1
select sno,sname from Student where sname like '_敏%'
##7. 查所有有成绩的学生学号和课程号1
select sno,cno from SC where Grade is not null