201606-数据库Sql Server题库带答案 (1).doc

2015级专科数据库sql server题库 题型知识点出题数量难度一、数据库设计创建数据库和表、操作数据（增、删、改）、数据库完整性6个大题（每个大题8-10个小题）3易2中1较难二、数据查询（以上课的scmdb数据库）单表查询、排序30易子查询105易3中1较难1难多表查询2010易5中3较难2难分组与聚合函数2010易5中3较难2难视图51060%以上易三、T-SQL编程存储过程（游标设计在存储过程里）2010易5中3较难2难 学生课程管理数据库（SCMDB）的表关系图一、数据库设计1、 现有一人事工资管理系统，有如下两个实体表结构设计如下：(易) 部门信息表deptinfo：字段名中文含义类型是否主外键是否为空备注Dept_id部门IDIntpkN标识列，初始为1，增长值为1Dept_code部门编号Varchar(20)YDept_name部门名称Varchar(30)Y员工信息表personinfo：字段名中文含义类型是否主外键是否为空备注Per_ID员工IDIntpkN标识列，初始为1，增长值为1Dept_id部门IDIntfkN 与deptinfo关联Per_name员工名称Varchar(20)YPer_age员工年龄IntY 要求学生用T-SQL完成如下内容：1） 创建人事工资管理数据库数据库名称为学生自己姓名的拼音全称。Create database wangguixin2） 按上面表设计要求，分别创建部门信息表（deptinfo）、员工信息表（personinfo）；Create table deptinfo( Dept_id int not null identity(1,1) primary key, Dept_code Varchar(20),Dept_name varchar(30)GoCreate table personinfo(Per_ID int not null identity(1,1) primary key,Dept_id int not null foreign key references deptinfo(Dept_id),Per_name Varchar(20) ,Per_age int)3） 分别部门信息表、员工信息表中，各插入两条数据，Insert into deptinfo values(001,软件工程)Insert into deptinfo values(002,xi2)Insert into personinfo values(1,wang,18)Insert into personinfo values(2,zhang,28)4） 删除部门信息表中所有数据。Delete from deptinfo 5） 在员工信息表中，新增一列，及员工的出生日期，默认时间为“2015/6/1”。Alter table personinfo add csrq datetime default 2015/6/16） 在员工信息表中，以部门id建立非聚集索引Create nonclustered index ix_id_test on personinfo(Dept_id)2、 现有商品信息管理系统，有如下两个实体表结构设计如下：(易)商品类别信息表shoptype：字段名中文含义类型是否主外键是否为空备注Ty_id类别IDIntpkN标识列，初始为1，增长值为1Ty_code类别编号Varchar(20)YTy_name类别名称Varchar(30)Y商品信息表shopinfo：字段名中文含义类型是否主外键是否为空备注Sp_id商品IDIntpkN标识列，初始为1，增长值为1Ty_id类别IDIntfkN 与shoptype关联Sp_code商品名称Varchar(20)YSp_name商品价格FloatY 要求学生用T-SQL完成如下内容：1） 创建商品信息管理数据库数据库名称为学生自己姓名的拼音全称。create database wangguixin2） 按上面表设计要求，分别创建商品类别信息表（shoptype）、商品信息表（shopinfo）；CREATE TABLE shoptype (ty_id int IDENTITY(1,1) NOT NULL primary key, ty_code varchar(20) NULL, ty_name varchar(30) NULL)GoCreate table shopinfo(Sp_id int not null identity(1,1) primary key,Ty_id int not null foreign key references shoptype(Ty_id),Sp_code Varchar(20) ,Sp_name Float)3） 分别商品信息表、商品类别信息表中，各插入两条数据，Insert into shoptype values(001,class1)Insert into shoptype values(002, class2)Insert into shopinfo values(1,name1,18)Insert into shopinfo values(2,name2,28)4） 在商品信息表中，新增一列，记录商品的计量单位。Alter table shopinfo add jldw varchar(10)5） 将商品信息表中的商品价格数据类型改为real类型。Alter table shopinfo alter column Sp_name real6） 将商品类别表中类别编号，设置默认值为“00001”，并建立非聚集索引。Alter table shoptype add constraint de_test default 00001 for Ty_codeCreate nonclustered index idx_ Ty_code on shoptype(Ty_code )3、 现有进销存管理系统，有如下两个实体表结构设计如下：(易)供应商信息表client：字段名中文含义类型是否主外键是否为空备注Cl_id供应商IDIntpkN标识列，初始为1，增长值为1Cl_ name供应商名称Varchar(20)YCl_address联系地址Varchar(30)Y商品信息表shopinfo：字段名中文含义类型是否主外键是否为空备注Sp_id商品IDIntpkN标识列，初始为1，增长值为1Cl_id供应商IDIntfkN 与client关联Sp_code商品名称Varchar(20)YSp_name商品价格FloatY 要求学生用T-SQL完成如下内容：1） 创建进销存管理数据库数据库名称为学生自己姓名的拼音全称。Create database wangguixin2） 按上面表设计要求，分别创建供应商信息表（client）、商品信息表（shopinfo）；Create table client (Cl_id int not null identity(1,1) primary key,Cl_name Varchar(20),Cl_address Varchar(30)GoCreate table shopinfo( Sp_id int not null identity(1,1) primary key,Cl_id int not null foreign key references client(Cl_id),Sp_code varchar(20),Sp_name float)3） 分别供应商信息表、商品信息表中，各插入两条数据Insert into client values(001,sup1)Insert into client values(002,sup2)Insert into shopinfo values(1,name1,18)Insert into shopinfo values(2,name2,28)4） 在供应商信息表中新增一列，供应商性别，类型为char（1），设默认值0，并创建检查约束0代表女，1代表男。Alter table client add gys_sex char(1) default 0 check (gys_sex=0 or gys_sex=1)5） 在商品信息表中，以供应商id建立非聚集索引。Create nonclustered index ix_shopinfo on shopinfo(Cl_id)6） 从数据库中，删除商品信息表（shopinfo）。Drop table shopinfo4、 现有车辆信息管理系统，有如下两个实体表结构设计如下：（中）部门信息表（deptinfo）：字段名中文含义类型是否主外键是否为空备注Dept_id部门编号IntpkNDept_name部门名称Varchar(200)Y 车辆信息表（carinfo）：字段名中文含义类型是否主外键是否为空备注car_ID车辆编号IntpkNDept_id部门编号IntFk与部门信息表关联car_num车牌号码Varchar(12)N car_style车辆类型Varchar(32)YPer_time购买日期datetimeY 要求学生用T-SQL完成如下内容：1）创建车辆信息管理系统数据库数据库名称为学生自己姓名的拼音全称。create database mingzi1go2）按上面表设计要求，分别创建部门信息表（deptinfo）、车辆信息表（carinfo）；create table deptinfo(dept_id int primary key,dept_name varchar(200)gocreate table carinfo(car_id int primary key, dept_id int, car_num varchar(12) not null, car_style varchar(32), per_time datetime constraint fk_dept foreign key(dept_id) references deptinfo(dept_id) )3）分别部门信息表、车辆信息表中，各插入两条数据，insert into deptinfo values(1,生产部)insert into deptinfo values(2,技术部)insert into carinfo values(1,1,渝ABD123,轿车,2013-12-1)insert into carinfo values(2,2,渝CLV332,货车,2014-3-5)4）删除车辆信息表中所有数据。delete from carinfo5）在车辆信息表中，新增一列购买日期，数据类型为DATETIME，默认时间为“-06-01”。alter table carinfo add purdate datetime default 2015-06-016）在车辆信息表中，以车牌号码列建立唯一性非聚集索引。create unique nonclustered index id_number on carinfo(car_id)5、 现有仓库管理系统，有如下两个实体表结构设计如下：（中）仓库信息表（storage）字段名中文含义类型是否主外键是否为空备注st_id仓库编号IntpkN标识列，初始为1，增长值为1st_name仓库名称Varchar(36)Yst_addr仓库地址Varchar(200)Y货物信息表goodsinfo：字段名中文含义类型是否主外键是否为空备注gs_id货物IDIntpkN标识列，初始为1，增长值为1st_id仓库编号IntfkN 与storagege表关联gs_name货物名称Varchar(36)Ygs_add货物位置Varchar(200)Y 要求学生用T-SQL完成如下内容：1）创建仓库管理系统数据库数据库名称为学生自己姓名的拼音全称。create database mingzi22）按上面表设计要求，分别创建仓库信息表（storage）、货物信息表(goodsinfo)；create table storage(st_id int identity(1,1) primary key, st_name varchar(36), st_addr varchar(200)create table goodsinfo( gs_id int identity(1,1) primary key, st_id int foreign key references storage(st_id), gs_name varchar(36), gs_add varchar(200)3）分别仓库信息表、货物信息表中，各插入两条数据，insert into storage values(宏远仓库,渝中区中山三路号)insert into storage values(志连仓库,江北区红叶路号)insert into goodsinfo values(2,电线,C区-24)insert into goodsinfo values(1,管道,F区-12)4）在货物信息表中，以货物名称建立非聚集索引create nonclustered index index_name on goodsinfo(gs_name)5）在仓库信息表中，新增一列联系电话alter table storage add phone char(11)6）从数据库中，删除货物信息表goodsinfo。drop table goosinfo6、现在商品销售系统，有如下三个表结构设计如下：（较难）（1）Members(用户表)字段名数据类型是否为空主键？外键约束缺省值备注M_accountChar(9)Not NullPk_eshopNo会员号M_nameChar(8)Not NullNoNo会员名M_datedatetimeYesNoNo出生年月M_sexChar(2)YesNoNo男性别（2）Products（产品信息表）字段名数据类型是否为空主键？外键约束缺省值备注P_noInt Not NullPk_ ProductsNo产品号P_nameVarchar(50)Not NullNoNo产品名（3）Orders（订单表）字段名数据类型是否为空主键约束 备注P_noIntNot NullPk_OrdersFk_P_no,参照Products中P_no产品号M_acountChar(9)Not NullPk_OrdersFK_M_account,参照Membe中M_account会员号O_countIntNull数量要求学生用T-SQL完成如下内容：1)创建商品销售系统数据库名称为学生自己姓名的拼音全称。create database mingzi32)按上面表设计要求，分别创建以上三张表；create table members(M_account char(9) constraint pk_eshop primary key, M_name char(8) not null, M_date datetime , M_sex char(2) default 男)create table products( P_no int constraint Pk_ products primary key, P_name varchar(50) not null)create table orders( P_no int, M_account char(9) not null, O_count int, constraint Pk_Orders primary key(P_no,M_account), constraint fk_p_no foreign key(P_no) references products(P_no), constraint fk_M_account foreign key(M_account) references members(M_account)3)为订单表的O_count列添加CHECK约束，要求数量在0到100之间alter table ordersadd constraint ck_oc check(O_count0 and O_count100) alter table orders add constraint ck_oc check(O_count between 0 and 100)4)在产品信息表中，新增一列产品价格p_price，类型为money。alter table products add p_price money5)分别在用户信息表、产品信息表和订单表三张表中，各插入两条数据，insert into members values(014072101,张于,1979-03-12,default)insert into members values(014072102,李晓妮,1986-02-18,女)insert into products values(1,轩尼XO)insert into products values(2,迪奥粉饼)insert into orders values(1,014072101,2)insert into orders values(2,014072101,6) 二、数据查询统计（以上课的scmdb数据库）单表查询、排序(易)1. 查询Studentinfo表中每个学生的所有信息。Select * From Studentinfo2. 查询Courseinfo表中所有课程情况。Select *From CourseInfo3. 查询“系部信息表”DeptInfo中的所有数据表中的所有数据。Select *From DeptInfo4. 查询“学生信息表”StudentInfo中的数据，只显示学号、姓名、性别、固定电话。SELECT Student_Code, Student_Name,Student_Sex,Student_Phone FROM StudentInfo5. 查询Courseinfo表中所有课程的课程编号，课程名。SELECT Course_Code,Course_Name FROM Courseinfo6. 查询“学生信息表”StudentInfo中所有女同学的学 号、姓 名、班级ID,要求列名以中文方式显示.Select Student_Code as 学号,姓名 = Student_Name,Class_ID 班级IDFrom StudentInfo where Student_Sex=07. 在“学生信息表”StudentInfo中以考生自己的学号查询自己的学号，姓名，手机号码，结果中各列的标题分别指定为我的学号，我的姓名，我的手机号码Select Student_Code as 我的学号,我的姓名 = Student_Name, Student_Mobile 我的手机号码 From StudentInfo where Student_Code=140011102 /这里与考生学号一致8. 查询课程表Courseinfo的每个课程的学分，将查询结果中的学分加3分作为新学分，并显示结果Select course_credit+3 新学分From courseinfo9. 查询课程表Courseinfo的每个课程的学分、课程编码和课程名称，将学分查询结果提高10%，并显示结果。Select course_credit*1.1 学分 , course_code,course_name From courseinfo10. 查询“学生信息表”StudentInfo中的学号、姓名、性别、固定电话，将学号和姓名组合在一起显示为“学号姓名”。SELECT Student_Code+Student_Name as 学号姓名,Student_Sex,Student_Phone FROM StudentInfo11. 查询Studentinfo表学生的入学时间，不显示重复的行，并注意观察结果。Select distinct student_indate from studentinfo12. 查询Studentinfo表中有哪些性别。select distinct Student_Sexfrom StudentInfo13. 查询Studentinfo表中前5行学生信息。select top 5 * from StudentInfo14. 查询课程表Courseinfo表中的前40%的数据。Select top 40 percent *From courseinfo15. 查询课程信息表”CourseInfo中课程类型为必修，并且学分在2和5之间（包含2和5）的课程编码、课程名称、学分。select Course_Code,Course_Name，Course_Creditfrom CourseInfowhere course_type=1 and Course_Credit=2或 Course_Credit between 2 and 516. 查询“学生信息表”StudentInfo中入学时间在2013年9月1日到2014年1月1日（包含两个端点）的学生的学号、姓名、性别。select Student_Code,Student_Name,Student_sexfrom StudentInfowhere Student_indate between 20130901 and 2014010117. 查询学生表StudentInfo中出生日期在1994年出生的女学生的姓名和手机号码 。select Student_Name,Student_Mobilefrom StudentInfowhere Student_Sex=0 and Student_BirthDay between 19940101 and 19941231(或where Student_Sex=0 and year(Student_BirthDay)=1994)18. 查询教师表teacherinfo中在2005年入职的教师的基本信息 。select *from teacherInfowhere Teacher_Hiredate between 20050101 and 2005123119. 查询“学生信息表”StudentInfo中姓“王”同学的学号和姓名。select Student_Code,Student_Namefrom StudentInfowhere Student_Name like 王%20. 查询“学生信息表”StudentInfo中姓“李”并且名字中带有“明”字在2013年入学的同学的学号和姓名、手机号码。select Student_Code,Student_Name, Student_Mobilefrom StudentInfowhere Student_Name like 李%明% and Student_indate between 20130101 and 2013120121. 查询“课程信息表”CourseInfo以“中”或者“国”开头的课程编码、课程名称。select Course_Code,Course_Namefrom CourseInfowhere Course_Name like 中国%22. 查找“学生信息表”StudentInfo中学号为单数的学生信息 。select *from StudentInfowhere Student_Code like %1357923. 查找“学生信息表”StudentInfo中学号的最后一位不是0到7的学生信息 。select *from StudentInfowhere Student_Code like %0-724. 查询“学生信息表”StudentInfo中学号最后两位是21的学生信息。select *from StudentInfowhere Student_Code like %2125. 查询学生表StudentInfo中未留手机号码的学生情况,并以学号进行排序。select *from StudentInfowhere Student_Mobile is nullorder by student_code26. 查询”课程信息表”courseinfo中没有人数限制的课程信息,以课程名称进行升序排列。select *from CourseInfowhere Course_limit is nullorder by Course_Name asc 27. 查询“学生信息表”StudentInfo中学号中有7且最后一位是3或4的学生,并按照学号进行降序排序。select *from StudentInfowhere Student_Code like %7%34order by Student_Code desc28. 查询“学生信息表”StudentInfo中学生的信息，根据入学时间排升序，学号排降序,显示前100个学生的基本信息。Select top 100 * from StudentInfoorder by student_indate asc ,student_name desc29. 查询学生信息表StudentInfo中学号在130016201到130016220之间（包括第一个和最后一个）学生所有基本信息,并按照姓名升序排序。select *from StudentInfowhere Student_Code between 130016201 and 130016220order by Student_Name asc30. 把课程表CourseInfo中课程类型排升序，学分排降序，但是要排除学分大于5的课程。select *from CourseInfowhere Course_Creditall(select Student_BirthDay from StudentInfo where Class_ID=(select Class_ID from ClassInfo where Class_Code=1300162) 7. 查询比李波所在班级学生年龄都大的学生信息。（中）select * from StudentInfo where Student_BirthDayall(select Course_Credit from CourseInfowhere Course_Name=SQL Server数据库应用)9. 查找不属于“软件工程系”所有女同学的学号、姓名、入属于学时间。（较难）Select Student_Code,Student_Name,Student_Indate From StudentInfo Where Student_Sex=0 And Class_ID IN ( Select Class_ID From ClassInfo Where Dept_ID not IN ( Select Dept_ID From DeptInfo Where Dept_Name =软件工程系 ) ) 10. 查找课程号Z10003的成绩不低于课程号Z10001的最低成绩的学生的学号和姓名。（难）select student_name,student_code from StudentInfowhere student_ID in(select student_ID from Student_Course where Course_ID in (select Course_ID from CourseInfo where Course_Code=Z10003)and course_grade any (select course_grade from Student_Course where Course_ID=(select Course_ID from CourseInfo where Course_Code=Z10001)或者select student_name,student_code from StudentInfowhere student_ID in(select student_ID from Student_Course where Course_ID in (select Course_ID from CourseInfo where Course_Code=Z10003)and course_grade (select min(course_grade) from Student_Course where Course_ID=(select Course_ID from CourseInfo where Course_Code=Z10001)多表查询多表查询1. 查询考试成绩在80分以上的学生的姓名. （易）select student_namefrom studentinfo,student_coursewhere studentinfo.Student_ID=Student_Course.Student_ID and Course_Grade=802. 查询选修了“程序设计基础”的学生的ID号. （易）select student_idfrom Student_Course,CourseInfowhere CourseInfo.Course_ID=Student_Course.Course_ID and Course_Name=程序设计基础 and CourseInfo.course_type=03. 查找“软件工程系”所开课程的课程编码、课程名称、学分。（易）select course_code,course_name,course_creditfrom CourseInfo,DeptInfowhere DeptInfo.Dept_ID=CourseInfo.Dept_ID and Dept_Name=软件工程系4. 查找班级编号为1400201的这学生的学号、姓名、性别。（易）select student_code,student_name,student_sexfrom StudentInfo,ClassInfowhere StudentInfo.Class_ID=ClassInfo.Class_ID and class_Code=14002015. 查找开设了课程号Z10003的系部信息。（易）select deptInfo.*from CourseInfo,deptInfowhere CourseInfo.Dept_ID=DeptInfo.Dept_ID and Course_Code=Z100036. 查询每个教师的信息，包括系部编码、系部名称、教师工号、教师姓名。（易）select dept_code,dept_name,teacher_code,teacher_namefrom DeptInfo,teacherInfowhere DeptInfo.Dept_ID=teacherInfo.Dept_ID7. 查询单科学分大于3课程的课程信息，包括系部名称、课程编码、课程名称。（易）select dept_name,course_code,course_namefrom courseinfo,deptinfowhere courseinfo.dept_id=deptinfo.dept_id and course_credit38. 查询“软件工程系”手机号码采用133号段的教师信息，信息包括系部名称、教师工号、教师名称、手机号码。（易）select dept_name,teacher_code,teacher_name,teacher_mobilefrom DeptInfo,teacherInfowhere teacherInfo.dept_id=deptinfo.dept_id and Dept_Name=软件工程系 and left(teacher_mobile,3) = 1339. 查询所有的课程情况及其被选修信息，如果有课程未被选修，也需要包含该课程的信息。（易）select * from CourseInfo left join Student_Course on CourseInfo.Course_ID=Student_Course.Course_ID10. 查询所有教师情况及教师课表情况，如果有教师未上课，也需要包含该教师的信息。（易）select * from teacherInfo left join Teacher_Class_Course on teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id11. 查询课程为“数据库系统原理”的成绩在前10名的学生信息，信息包括学号、姓名、成绩，成绩按降序排列（中）select top 10 student_code,student_name,course_gradefrom StudentInfo,Student_Course,CourseInfowhere StudentInfo.Student_ID=Student_Course.Student_ID and CourseInfo.Course_ID=Student_Course.Course_ID and Course_Name=数据库系统原理order by course_grade desc12. 查询教师工号为10003的教师姓名，所授课程代码，课程名称。（中3）select Teacher_Name,Course_Code,Course_Namefrom teacherInfo,Teacher_Class_Course,CourseInfowhere teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Idand Teacher_Code=1000313. 查询每个同学的信息，包括系部名称、班级名称、学号、姓名。（中3）select Dept_Name,Class_Name,Student_Code,Student_Namefrom studentinfo,ClassInfo,DeptInfowhere studentinfo.Class_ID=ClassInfo.Class_ID and ClassInfo.Dept_ID=DeptInfo.Dept_ID14. 查询学生代码为130016309的姓名，所选课程代码，课程名称，成绩。（中3）select student_name,course_code,course_name,course_gradefrom StudentInfo,Student_Course,CourseInfowhere StudentInfo.Student_ID=Student_Course.Student_ID and CourseInfo.Course_ID=Student_Course.Course_ID and Student_Code 查询1300161班“网页设计与制作”课程的前三名的学生学号、姓名和成绩。（较难4）select top 3 student_code,student_name,course_gra