SQL实验与练习题参考答案

1、实验1 数据库操作1创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10mb，最大尺寸为无限大，增长速度1mb；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1mb，最大尺寸为5mb，增长速度为10%。参考答案：create database teston primary(name = test_data,filename = d:testtest_data.mdf,size = 5mb,maxsize = unlimited,filegrowth = 1mb)log

2、 on(name = test_log,filename = d:testtest_log.ldf,size = 1mb,maxsize = 5mb,filegrowth = 10%)go2查看数据库属性：操作1.2：使用t-sql语句查看数据库test属性参考答案：exec sp_helpdb test3删除数据库：操作1.3：使用t-sql语句删除数据库test参考答案：drop database test实验2 表操作1创建表：操作2.1：创建学生表：表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnvarchar9not nullpk学生学号st_nm

3、nvarchar8not null学生姓名st_sexnvarchar2null学生性别st_birthdatetimenull出生日期st_scoreintnull入学成绩st_datedatetimenull入学日期st_fromnchar20null学生来源st_dpidnvarchar2null所在系编号st_mnttinyintnull学生职务参考答案：use testgocreate table student(st_id nvarchar(9) primary key not null ,st_nm nvarchar(8) not null ,st_sex nvarchar(2)

4、 null ,st_birth datetime null ,st_score int null ,st_date datetime null ,st_ from nvarchar(20)null ,st_dpid nvarchar(2) null ,st_ mnt tinyint null)go操作2.2：创建课程信息表：表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnvarchar4not nullpk课程编号cs_nmnvarchar20not null课程名称cs_tmintnull课程学时cs_scintnull课程学分参考答案：use testgocre

5、ate table couse(cs_id nvarchar(4) primary key not null ,cs_nm nvarchar(20) not null ,cs_tm int null ,cs_sc int null)go操作2.3：创建选课表：表名：slt_couse说明：选课表属性列数据类型长度空值列约束说明cs_idnvarchar4not nullfk课程编号st_idnvarchar9not nullfk学生编号scoreintnull课程成绩sltdatedatetimenull选课日期参考答案：use testgocreate table couse(cs_id n

6、varchar(4) not null contraint fk_xxx reference 课程信息表（cs.id）,st_id nvarchar(9) not null ,score int null ,sltdate datetime null)go操作2.4：创建院系信息表：表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnvarchar2not null系编号dp_nmnvarchar20not null院系名称dp_drtnvarchar8null院系主任dt_telnvarchar12null联系电话参考答案：use testgocreate table

7、dept(dp_id nvarchar(2) not null ,dp_nm nvarchar(20)not null ,dp_drtnvarchar(8) null ,dp_telnvarchar(12)null)go2修改表结构：（1）向表中添加列：操作2.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）参考答案：alter table dept add dp_count nvarchar(3) null（2）修改列数据类型：操作2.6：修改“dept”表的“dp_count”列数据类型为int参考答案：alter table dept al

8、ter column dp_count int null（3）删除表中指定列：操作2.7：删除“dept”表的“dp_count”列参考答案：alter table dept drop column dp_count3删除表操作2.8：删除“dept”表参考答案：drop table student4向表中输入数据记录操作2.9：分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录实验3 数据完整性1空值约束( null )操作3.1：将student表中的st_sex列属性更改为not null参考答案：alter table student

9、alter colume st_nm nvarchar(8) not null2默认值约束( default )操作3.2：将student表中的st_from列默认值设置为“陕西省”参考答案：alter table student add default 陕西省 for st_from3默认值对象操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。参考答案：create default df_today as getdate( )goexec sp_bindefault df_today

10、, slt_couse.sltdategoexec sp_unbindefault slt_couse.sltdategodrop default df_todaygo4检查约束( check )操作3.4：将slt_couse表中的score列的检查约束设置为=0且=0 and score= 2008操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩select st_id, st_nm, st_sex, st_score from studentwhere left(st_id,6)=0808082使用逻辑表达式表示查询条件操作6.5：查询student表中非

11、11系的学生信息select * from student where not (st_dpid = 11)操作6.6：查询选修了1002号课程且成绩在60以下的学生学号select st_id from slt_cousewhere (cs_id=1002) and (score75操作8.11：查询选修了2门以上课程的学生学号select st_id from slt_cousegroup by st_id having count(*)2操作8.12：明细汇总年龄20的学生，并汇总学生数量、平均年龄select st_nm,datepart(yy,getdate( )-datepart(

12、yy,st_birth) as 年龄from studentwhere datepart(yy,getdate()-datepart(yy,st_birth)20compute count(st_nm),avg(datepart(yy,getdate()-datepart(yy,st_birth)操作8.13：按班级明细汇总成绩85分的学生，汇总学生数、均分select st_nm, left(st_id,6) as 班级, st_scorefrom studentwhere st_score85order by 班级compute count(st_nm), avg(st_score) by

13、 班级实验9 数据查询（5）连接查询操作9.1：用sql server形式连接查询学生学号、姓名、性别及其所选课程编号select a.st_id, st_nm, st_sex, cs_idfrom student a, slt_couse bwhere a.st_id = b.st_idorder by a.st_id操作9.2：用ansi形式连接查询学生学号、姓名、性别及其所选课程编号select a.st_id, st_nm, st_sex, cs_idfrom student a inner join slt_couse bon a.st_id = b.st_idorder by a.

14、st_id操作9.3：用sql server形式连接查询学生学号、姓名及其所选课程名称及成绩select a.st_id, st_nm, cs_nm, scorefrom student a, slt_couse b, couse cwhere a.st_id = b.st_id and b.cs_id = c.cs_idorder by a.st_id操作9.4：用ansi形式连接查询学生学号、姓名及其所选课程名称及成绩select a.st_id, st_nm, cs_nm, scorefrom slt_couse a inner join student b on a.st_id = b

15、.st_idinner join couse c on a.cs_id = c.cs_idorder by b.st_id操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩select a.st_id, st_nm, scorefrom student a，slt_couse bwhere a.st_id = b.st_id and b.cs_id = 1002order by b.st_id操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩select a.st_id, st_nm, scorefrom student a, slt_couse b, cous

16、e cwhere a.st_id=b.st_id and b.cs_id=c.cs_id and c.cs_nm=数据结构order by a.st_id操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名select a.st_id, st_nm, scorefrom student a left outer join slt_couse b on a.st_id = b.st_idwhere b.cs_id is nullorder by b.st_id操作9.8：用右外连接查询选修各个课程的学生学号select b.cs_id, a.st_idfrom slt_couse a ri

17、ght outer join couse b on a.cs_id = b.cs_idorder by b.cs_id实验10 数据查询（6）子查询操作10.1：用子查询对各班人数进行查询（新增列）select distinct left(a.st_id,6) as 班级, 人数 = ( select count(st_id) from student b where left(a.st_id,6) = left(b.st_id,6)from student a order by left(a.st_id,6) asc操作10.2：用子查询对各课程的选课人数进行查询（新增列）select di

18、stinct a.cs_id, 人数 = ( select count(st_id) from slt_couse b where a.cs_id = b.cs_id)from slt_couse a order by a.cs_id asc操作10.3：查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序通过子查询实现：使用in关键字select st_id, st_nm, st_sex from studentwhere st_id in( select st_id from slt_couse where cs_id=1002 and score 60)order

19、by st_nm通过子查询实现：使用比较运算符select st_id, st_nm, st_sexfrom student awhere ( select score from slt_couse b where a.st_id = b.st_id and cs_id = 1002 ) any(select score from slt_couse where cs_id = 1002 and left(st_id,6)=070511)and left(st_id,6) 070511 and cs_id = 1002操作10.6：查询其它班比070511班任一学生的1002号课程成绩高的学生

20、信息（any/all）select * from slt_cousewhere score all(select score from slt_couse where cs_id = 1002 and left(st_id,6)=070511)and left(st_id,6) 070511 and cs_id = 1002操作10.7：查询大于等于60分且且比1003课程平均成绩低的学生课程信息（betweenand）select * from slt_couse awhere a.score between 60 and ( select avg(b.score) from slt_couse bwhere b.cs_id=1003 )操作10.8：查询系主任为“赵虎”的系的所有学生信息通过子查询实现：in运算符select * from student where exists( select * from dept where st_dpid = dp_id and dp_drt=赵虎 )通过子查询实现：=运算符select * from student where st_dpid =( select dp_id from dept where dp_drt=赵虎 )实验11 数据查询（7）数据更新与子查询操作11.1：将070511班所有学生信息插入到表student0