Sql Server数据库查询语句练习题含答案.docx
《Sql Server数据库查询语句练习题含答案.docx》由会员分享，可在线阅读，更多相关《Sql Server数据库查询语句练习题含答案.docx（52页珍藏版）》请在冰点文库上搜索。
SqlServer数据库查询语句练习题含答案
第三章综合练习
1.创建一个数据库，数据库名字edu，数据文件逻辑名字edu_data，初始大小10M，最大为1024M，增长比例为每次1M;日志文件逻辑名字为edu_log,初始大小5M，最大为100M,按10%比例增长，这些文件都存储到E盘根目录下，并且物理文件名与逻辑文件名全部相同。
CREATEDATABASEedu
ONPRIMARY
（NAME=N'educ_data',FILENAME=N'e:
\educ_data.mdf',SIZE=10240KB,MAXSIZE=1024M,FILEGROWTH=1M）
LOGON
（NAME=N'educ_log',FILENAME=N'e:
\educ_log.ldf',SIZE=5M,MAXSIZE=100M,FILEGROWTH=10%）
2.在edu数据库里创建一个架构，架构名字为自己名字的汉语拼音。
createschemazhangsan;--自己名字的汉语拼音
3.在创建的架构中，创建如下表：
表1student表（学生信息表）
字段名称
类型
宽度
允许空值
主键
说明
sno
char
8
NOTNULL
是
学生学号
sname
char
8
NOTNULL
学生姓名
sex
char
2
NULL
学生性别
native
char
20
NULL
籍贯
birthday
datetime
NULL
学生出生日期
pno
char
4
NULL
专业号
dno
char
6
NULL
学生所在院系（外键）
classno
char
4
NULL
班级号
entime
datetime
NULL
学生入校时间
home
varchar
40
NULL
学生家庭住址
tel
varchar
40
NULL
学生联系电话
表2course表（课程信息表）
字段名称
类型
宽度
允许空值
主键
说明
cno
char
10
NOTNULL
是
课程编号
cname
char
20
NOTNULL
课程名称
cpno
char
10
NULL
先修课程（外键）
experiment
tinyint
NULL
实验时数
lecture
tinyint
NULL
授课学时
semester
tinyint
NULL
开课学期
credit
tinyint
NULL
课程学分
表3student_course表（学生选课成绩表）
字段名称
类型
宽度
允许空值
主键
说明
sno
char
8
NOTNULL
是
学生学号
cno
char
10
NOTNULL
是
课程编号
score
tinyint
NULL
学生成绩,0-100之间
表4teacher表（教师信息表）
字段名称
类型
宽度
允许空值
主键
说明
tno
char
8
NOTNULL
是
教师编号
tname
char
8
NOTNULL
教师姓名
sex
char
2
NULL
教师性别
birthday
datetime
NULL
教师出生日期
dno
char
6
NULL
教师所在院系（外键）
pno
varchar
20
NULL
教师职务
home
varchar
40
NULL
教师家庭住址
zipcode
char
6
NULL
邮政编码
tel
varchar
40
NULL
联系电话
email
varchar
40
NULL
电子邮件
表5teacher_course表（教师上课课表）
字段名称
类型
宽度
允许空值
主键
说明
tcid
smallint
NOTNULL
是
上课编号
tno
char
8
NULL
教师编号（外键）
classno
char
4
NULL
班级号
cno
char
10
NOTNULL
课程编号（外键）
semester
char
6
NULL
学期
schoolyear
char
10
NULL
学年
classtime
varchar
40
NULL
上课时间
classroom
varchar
40
NULL
上课地点
weektime
tinyint
NULL
每周课时数
表6department表（院系息表）
字段名称
类型
宽度
允许空值
主键
说明
dno
char
6
NOTNULL
是
学院编号
dname
char
8
NOTNULL
学院名称
dhome
varchar
40
NULL
学院地址
dzipcode
char
6
NULL
学院邮政编码
dtel
varchar
40
NULL
学院联系电话
创建表的时候必须建立主、外键关系。
createtablezhangsan.department（
dnochar（6）primarykey,
dnamechar（8）notnull,
dhomevarchar（40）,
dzipcodechar（6）,
dtelvarchar（40）
）
createtablezhangsan.student（
snochar（8）notnullprimarykey,
snamechar（8）notnull,
sexchar
（2）,
[native]char（20）,
birthdaydatetime,
pnochar（4）,
dnochar（6）,
classnochar（4）,
entimedatetime,
homevarchar（40）,
telvarchar（40）
foreignkey（dno）referencesdepartment（dno）
）
createtablezhangsan.course（
cnochar（10）primarykey,
cnamechar（20）notnull,
cpnochar（10）,
experimenttinyint,
lecturetinyint,
semestertinyint,
credittinyint,
foreignkey（cpno）referencescourse（cno）
）
createtablezhangsan.student_course（
snochar（8）,
cnochar（10）,
scoretinyint,
primarykey（sno,cno）,
foreignkey（sno）referencesstudent（sno）,
foreignkey（cno）referencescourse（cno）
）
createtablezhangsan.teacher（
tnochar（8）primarykey,
tnamechar（8）notnull,
sexchar
（2）,
birthdaydatetime,
dnochar（6）,
pnotinyint,
homevarchar（40）,
zipcodechar（6）,
telvarchar（40）,
emailvarchar（40）,
foreignkey（dno）referencesdepartment（dno）
）
createtablezhangsan.teacher_course（
tcidsmallintprimarykey,
tnochar（8）,
classnochar（4）,
cnochar（10）notnull,
semesterchar（6）,
schoolyearchar（10）,
classtimevarchar（40）,
classroomvarchar（40）,
weektimetinyint,
foreignkey（tno）referencesteacher（tno）,
foreignkey（cno）referencescourse（cno）
）
4.建立、删除索引
（1）在student表的sname列建立唯一降序索引；
createuniqueindexIX_STUDENT_SNAMEonzhangsan.student（snamedesc）
（2）在course表的credit列上建立升序索引；
createindexIX_COURSE_CREDITonzhangsan.course（credit）
（3）在student_course表的sno列上建立聚集索引。
createclusteredindexIX_STUDENT_COURSE_SNOonzhangsan.student_course（sno）
（4）在student_course表上，以学号升序，学号相同按课程号降序建立索引；
createindexIX_STUDENT_COURSE_SNOCNOonzhangsan.student_course（sno,cnodesc）
（5）在teacher表的tno列上建立聚集升序索引。
createclusteredindexIX_TEACHER_TNOONteacher（tno）
（6）将student_course表的sno列上的聚集索引删掉。
dropindexIX_STUDENT_COURSE_SNO
5.将给出的数据插入到相应的表里，必须使用insert语句。
student表
学号
姓名
性别
籍贯
出生日期
专业号
院系编号
班级号
入学日期
家庭住址
电话
20101001
张三
男
河北沧州
1989-03-15
2
1
2
2010-9-1
河北省沧州市
NULL
20101002
张强
男
河北邢台
1990-05-01
2
1
2
2010-9-1
河北省邢台市
131********
20101003
张小兵
男
山东济南
1991-05-02
1
2
4
2010-9-1
河北省唐山市
NULL
20101004
李燕
女
山东济南
1991-05-02
NULL
2
1
2010-9-1
山东省济南市
151********
20101005
李晓
女
山东德州
1991-05-02
1
2
4
2010-9-1
山东省德州市
153********
20101006
上官青
女
陕西西安
1993-05-02
3
3
1
2010-9-1
陕西省西安市
NULL
20101007
李晓芳
女
陕西西安
1993-05-02
3
1
1
2010-9-1
陕西省西安市
158********
20101008
上官文宏
女
北京市
1988-05-02
NULL
3
3
2010-9-1
北京市
158********
20101009
上官文宏
女
河北保定
1988-05-02
3
3
1
2010-9-1
山东省青岛市
158********
20101010
张倩
女
河北保定
1988-11-02
1
2
3
2010-9-1
河北省保定市
138********
20101011
刘英伟
男
河北保定
1988-11-02
NULL
3
2
2010-9-1
河北省保定市
151********
20101012
刘伟
男
河北保定
1989-11-02
1
2
2
2010-9-1
河北省保定市
153********
20101013
翟南
男
上海市
1989-11-21
3
4
3
2010-9-1
上海市
137********2
20101014
窦士厝
男
云南昆明
1988-10-02
3
4
1
2010-9-1
云南省昆明市
131********0
course表
课程编号
课程名称
先修课程
实验时数
授课学时
开课学期
课程学分
1
数据库系统概论
3
20
72
1
3
2
100%掌握财务报表
7
30
54
1
2
3
数据结构
5
30
72
2
4
4
c++程序设计
5
15
72
2
4
5
c语言程序设计
NULL
18
72
2
3
6
WEB开发
NULL
28
54
1
3
7
计算机导论
NULL
10
32
1
1
student_course表
学生学号
课程编号
学生成绩
20101001
1
90
20101001
2
70
20101001
3
95
20101001
4
90
20101001
5
88
20101001
6
NULL
20101001
7
NULL
20101002
2
65
20101002
4
89
20101002
6
NULL
20101003
2
90
20101003
3
89
20101003
4
90
20101004
2
65
20101004
4
87
20101004
5
90
20101004
6
NULL
20101005
4
92
20101005
6
NULL
20101007
4
90
20101007
5
88
20101007
6
NULL
20101009
2
80
20101010
3
75
20101010
5
88
20101009
7
80
20101004
7
70
20101003
7
86
teacher表
教师编号
姓名
性别
出生日期
院系编号
职务
家庭住址
邮政编码
联系电话
email
836001
董青
男
1975-04-26
1
讲师
河北省石家庄市
50000
137********
dong@
836002
李亮
男
1975-04-27
1
副教授
河北省石秦皇岛市
66000
151********
liliang@
836003
李晓亮
男
1975-04-28
1
讲师
河北省石家庄市
50000
138********
lixl@
836004
项天
男
1975-04-29
2
副教授
河北省石家庄市
50000
131********
xy23@
836005
余孝天
男
1975-04-30
2
教授
北京市
100000
138********
yugong@
836006
鲁婵娟
女
1975-05-01
3
副教授
河北省石家庄市
50000
151********
chanjuan@
836007
李冉冉
女
1975-05-02
3
讲师
河北省唐山市
63000
151********
ranran@
836008
庄文
男
1975-05-03
2
教授
河北省唐山市
63000
138********
zw1965@
836009
李晓璐
女
1975-05-04
3
讲师
河北省石家庄市
50000
158********
lxl@
836010
范娜
女
1975-05-05
3
讲师
山东省济南市
250000
136********
fn@
836011
萧不语
男
1975-05-06
2
讲师
新疆自治区乌鲁木齐市
830000
130********
xby@
teacher_course表
上课编号
教师编号
班级号
课程编号
学期
学年
上课时间
上课地点
每周课时数
1
836001
1
1
1
2010
8:
00-11:
00
公教楼305
3
2
836001
1
2
1
2010
8:
00-11:
00
公教楼305
3
3
836002
2
2
1
2010
8:
00-11:
00
c301
3
4
836002
2
3
1
2010
8:
00-10:
00
c302
2
5
836003
2
1
1
2010
8:
00-10:
00
c302
2
6
836003
1
3
1
2010
14:
00-16:
00
c302
2
7
836003
3
4
1
2010
14:
00-16:
00
c304
2
8
836004
2
3
3
2010
14:
00-17:
00
c204
3
9
836005
2
3
2
2010
14:
00-17:
00
c204
3
10
836006
2
3
1
2010
14:
00-17:
00
c301
3
11
836007
2
3
2
2010
14:
00-17:
00
c201
3
12
836007
3
4
2
2010
14:
00-17:
00
c204
3
13
836007
3
5
2
2010
14:
00-17:
00
c201
3
14
836006
3
4
8
2010
14:
00-17:
00
c201
3
15
836006
3
5
9
2010
14:
00-17:
00
c201
3
16
836006
3
6
8
2010
14:
00-17:
00
c201
3
17
836005
3
5
7
2010
14:
00-17:
00
c302
3
18
836004
3
4
6
2010
14:
00-17:
00
c201
3
19
836003
3
5
5
2010
14:
00-17:
00
c201
3
20
836003
2
5
4
2010
14:
00-17:
00
c201
3
department表
学院编号
学院名称
学院地址
邮政编码
学院联系电话
1
法政学院
河北省石家庄市裕华区南二环东路20号
50024
80788100
2
教育学院
河北省石家庄市裕华区南二环东路20号
50024
80788101
3
软件学院
河北省石家庄市裕华区南二环东路20号
50024
80788102
4
数信学院
河北省石家庄市裕华区南二环东路20号
50024
80788102
注意插入数据时注意满足参照关系，否则插入数据会出错。
例子：
往学生表插入一条记录
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）
VALUES（'108','曾华','男','1977-09-01',95033）;
向教师表插入一条记录
insertintoteacher
values（'101','赵旭东',,,'副教授','数学系'）
6.查询
--简单查询部分练习
1）查询全体学生的学号、姓名、籍贯
selectsno,sname,native
fromstudent
2）查询所有课程的名称及学分
selectcname,credit
fromcourse
3）查询教师的姓名、性别、联系电话
selecttname,sex,tel
fromteacher
4）查询每门课程的课程编号、课程名称及理论学时
selectcno,cname,lecture-experiment理论课时
fromcourse
5）查询全体学生的姓名、联系电话，并在前面加上字符串‘联系方式’
selectsname,'联系方式',tel
fromstudent
6）查询全体教师的人数
selectcount（tno）教师人数
fromteacher
7）查询全体教师的姓名、家庭住址、邮政编码（使用列别名）
selecttname'姓名',home'家庭住址',zipcode'邮政编码'
fromteacher
8）查询全体学生的姓名
selectsname
fromstudent
9）查询本学期有课的教师编号
selectdistincttno有课的教师编号
fromteacher_course
wheresemester='1'
10）查询所有选课的学生学号
selectdistinctsno
fromstudent_course
11）查询籍贯为河北保定的学生的学号和姓名
selectsno,sname
fromstudent
wherenative='河北保定'
12）查询课程学分小于3分的课程名称
selectcname
fromcourse
wherecredit<3
13）查询在c201教室上课的教师编号
selectdistincttno
fromteacher_course
whereclassroom='c201'
14）查询软件学院的办公电话
selectdtel
fromdepartment
wheredname='软件学院'
15）查询学生成绩在60-90分的学生的学号和课程号
升级会员 