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

更新时间：2024-06-15 09:21:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

201606英语四级答案推荐度：
相关推荐

2015级专科数据库sql server题库

题型一、数据库设计知识点创建数据库和表、操作数据（增、删、改）、数据库完整性单表查询、排序子查询出题数量 6个大题（每个大题8-10个小题）难度 3易 2中 1较难易 5易 3中 1较难 1难 10易 5中 3较难 2难 10易 5中 3较难 2难 60%以上易 10易 5中 3较难 2难二、数据查询（以上课的scmdb数据库） 30 10 多表查询 20 分组与聚合函数 20 视图存储过程（游标设计在存储过程里） 5~10 20 三、T-SQL编程

学生课程管理数据库（SCMDB）的表关系图

一、数据库设计

1、现有一人事工资管理系统，有如下两个实体表结构设计如下：(易)

部门信息表deptinfo：字段名 Dept_id Dept_code Dept_name 字段名 Per_ID Dept_id Per_name Per_age 要求学生用T-SQL完成如下内容：

1）创建人事工资管理数据库—数据库名称为学生自己姓名的拼音全称。

Create database wangguixin

2）按上面表设计要求，分别创建部门信息表（deptinfo）、员工信息表（personinfo）；

Create table deptinfo(

Dept_id int not null identity(1,1) primary key, Dept_code Varchar(20), Dept_name varchar(30) ) Go

Create 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)

中文含义部门ID 部门编号部门名称中文含义员工ID 部门ID 员工名称员工年龄类型 Int Varchar(20) Varchar(30) 类型 Int Int Varchar(20) Int 是否主外键 pk 是否主外键 pk fk 是否为空 N Y Y 是否为空 N N Y Y 备注标识列，初始为1，增长值为1 备注标识列，初始为1，增长值为1 与deptinfo关联员工信息表personinfo：

4）删除部门信息表中所有数据。

Delete from deptinfo

5）在员工信息表中，新增一列，及员工的出生日期，默认时间为“2015/6/1”。

Alter table personinfo add csrq datetime default '2015/6/1'

6）在员工信息表中，以部门id建立非聚集索引

Create nonclustered index ix_id_test on personinfo(Dept_id)

2、现有商品信息管理系统，有如下两个实体表结构设计如下：(易)

商品类别信息表shoptype：字段名 Ty_id Ty_code Ty_name 字段名 Sp_id Ty_id Sp_code Sp_name

要求学生用T-SQL完成如下内容：

1）创建商品信息管理数据库—数据库名称为学生自己姓名的拼音全称。

create database wangguixin

2）按上面表设计要求，分别创建商品类别信息表（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 ) Go

Create table shopinfo(

中文含义类别ID 类别编号类别名称中文含义商品ID 类别ID 商品名称商品价格类型 Int Varchar(20) Varchar(30) 类型 Int Int Varchar(20) Float 是否主外键 pk 是否主外键 pk fk 是否为空 N Y Y 是否为空 N N Y Y 备注标识列，初始为1，增长值为1 备注标识列，初始为1，增长值为1 与shoptype关联商品信息表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 real

6）将商品类别表中类别编号，设置默认值为“00001”，并建立非聚集索引。

Alter table shoptype add constraint de_test default ?00001? for Ty_code Create nonclustered index idx_ Ty_code on shoptype(Ty_code )

3、现有进销存管理系统，有如下两个实体表结构设计如下：(易)

供应商信息表client：字段名 Cl_id Cl_ name Cl_address 字段名 Sp_id Cl_id Sp_code Sp_name

要求学生用T-SQL完成如下内容：

1）创建进销存管理数据库—数据库名称为学生自己姓名的拼音全称。

Create database wangguixin

2）按上面表设计要求，分别创建供应商信息表（client）、商品信息表（shopinfo）；

Create table client (

Cl_id int not null identity(1,1) primary key, Cl_name Varchar(20), Cl_address Varchar(30) ) Go

Create 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）分别供应商信息表、商品信息表中，各插入两条数据

中文含义供应商ID 供应商名称联系地址中文含义商品ID 供应商ID 商品名称商品价格类型 Int Varchar(20) Varchar(30) 类型 Int Int Varchar(20) Float 是否主外键 pk 是否主外键 pk fk 是否为空 N Y Y 是否为空 N N Y Y 备注标识列，初始为1，增长值为1 备注标识列，初始为1，增长值为1 与client关联商品信息表shopinfo：

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 shopinfo

4、现有车辆信息管理系统，有如下两个实体表结构设计如下：（中）部门信息表（deptinfo）：字段名中文含义类型 Dept_id Dept_name 部门编号部门名称 Int Varchar(200) 是否主外键 pk 是否主外键 pk Fk 是否为空 N Y 是否为空 N N Y Y 备注备注与部门信息表关联车辆信息表（carinfo）：字段名中文含义类型 car_ID Dept_id car_num car_style Per_time 要求学生用T-SQL完成如下内容：

车辆编号部门编号车牌号码车辆类型购买日期 Int Int Varchar(12) Varchar(32) datetime 1）创建车辆信息管理系统数据库—数据库名称为学生自己姓名的拼音全称。

create database mingzi1 go

2）按上面表设计要求，分别创建部门信息表（deptinfo）、车辆信息表（carinfo）； create table deptinfo (

dept_id int primary key, dept_name varchar(200) ) go

create 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 carinfo

5）在车辆信息表中，新增一列购买日期，数据类型为DATETIME，默认时间为“-06-01”。 alter table carinfo add purdate datetime default '2015-06-01' 6）在车辆信息表中，以车牌号码列建立唯一性非聚集索引。

create unique nonclustered index id_number on carinfo(car_id)

5、现有仓库管理系统，有如下两个实体表结构设计如下：（中）

仓库信息表（storage）字段名 st_id st_name st_addr 字段名 gs_id st_id gs_name gs_add 要求学生用T-SQL完成如下内容：

1）创建仓库管理系统数据库—数据库名称为学生自己姓名的拼音全称。 create database mingzi2

2）按上面表设计要求，分别创建仓库信息表（storage）、货物信息表(goodsinfo)； create table storage

(st_id int identity(1,1) primary key, st_name varchar(36), st_addr varchar(200) )

中文含义仓库编号仓库名称仓库地址中文含义货物ID 仓库编号货物名称货物位置类型 Int Varchar(36) Varchar(200) 类型 Int Int Varchar(36) Varchar(200) 是否主外键 pk 是否主外键 pk fk 是否为空 N Y Y 是否为空 N N Y Y 备注标识列，初始为1，增长值为1 备注标识列，初始为1，增长值为1 与storagege表关联货物信息表goodsinfo： 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 goosinfo

6、现在商品销售系统，有如下三个表结构设计如下：（较难）

（1）Members(用户表) 字段名数据类型 M_account Char(9) M_name Char(8) M_date datetime M_sex Char(2) 是否为空 Not Null Not Null Yes Yes 主键？ Pk_eshop No No No 外键约束 No No No No 缺省值 ‘男’ 备注会员号会员名出生年月性别（2）Products（产品信息表）

字段名数据类型是否为空主键？外键约束缺省值 P_no Int Not Null Pk_ Products No P_name Varchar(50) Not Null No No （3）Orders（订单表）字段名数据类型是否为空主键约束 P_no Int Not Null Pk_Orders Fk_P_no,参照Products中P_no M_acount Char(9) Not Null Pk_Orders FK_M_account,参照Membe中M_account O_count Int Null 要求学生用T-SQL完成如下内容：

1)创建商品销售系统—数据库名称为学生自己姓名的拼音全称。 create database mingzi3

2)按上面表设计要求，分别创建以上三张表； 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 orders

add constraint ck_oc check(O_count>0 and O_count<100)

alter table orders add constraint ck_oc check(O_count between 0 and 100))

4)在产品信息表中，新增一列产品价格p_price，类型为money。 alter table products add p_price money

5)分别在用户信息表、产品信息表和订单表三张表中，各插入两条数据，

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 Studentinfo 2. 查询Courseinfo表中所有课程情况。

Select *From CourseInfo

3. 查询“系部信息表”DeptInfo中的所有数据表中的所有数据。

Select *From DeptInfo

4. 查询“学生信息表”StudentInfo中的数据，只显示学号、姓名、性别、固定电话。

SELECT Student_Code, Student_Name,Student_Sex,Student_Phone FROM StudentInfo

5. 查询Courseinfo表中所有课程的课程编号，课程名。

SELECT Course_Code,Course_Name FROM Courseinfo

6. 查询“学生信息表”StudentInfo中所有女同学的学号、姓名、班级ID,要求列名以中

文方式显示.

Select Student_Code as '学号','姓名' = Student_Name,Class_ID 班级ID From StudentInfo where Student_Sex='0'

7. 在“学生信息表”StudentInfo中以考生自己的学号查询自己的学号，姓名，手机号码，

结果中各列的标题分别指定为我的学号，我的姓名，我的手机号码

Select Student_Code as '我的学号','我的姓名' = Student_Name,

Student_Mobile '我的手机号码' From StudentInfo

where Student_Code='140011102' //这里与考生学号一致

8. 查询课程表Courseinfo的每个课程的学分，将查询结果中的学分加3分作为新学分，并

显示结果

Select course_credit+3 '新学分' From courseinfo

9. 查询课程表Courseinfo的每个课程的学分、课程编码和课程名称，将学分查询结果提高

10%，并显示结果。

Select course_credit*1.1 '学分' , course_code,course_name From courseinfo

10. 查询“学生信息表”StudentInfo中的学号、姓名、性别、固定电话，将学号和姓名组合

在一起显示为“学号姓名”。

SELECT Student_Code+Student_Name as ?学号姓名?,Student_Sex,Student_Phone FROM StudentInfo 11. 查询Studentinfo表学生的入学时间，不显示重复的行，并注意观察结果。

Select distinct student_indate from studentinfo

12. 查询Studentinfo表中有哪些性别。

select distinct Student_Sex from StudentInfo

13. 查询Studentinfo表中前5行学生信息。

select top 5 * from StudentInfo

14. 查询课程表Courseinfo表中的前40%的数据。

Select top 40 percent * From courseinfo

15. 查询课程信息表”CourseInfo中课程类型为必修，并且学分在2和5之间（包含2和5）

的课程编码、课程名称、学分。

select Course_Code,Course_Name，Course_Credit from CourseInfo

where course_type=1 and Course_Credit<=5 and Course_Credit>=2 或 Course_Credit between 2 and 5

16. 查询“学生信息表”StudentInfo中入学时间在2013年9月1日到2014年1月1日（包

含两个端点）的学生的学号、姓名、性别。

select Student_Code,Student_Name,Student_sex from StudentInfo

where Student_indate between '20130901' and '20140101'

17. 查询学生表StudentInfo中出生日期在1994年出生的女学生的姓名和手机号码。

select Student_Name,Student_Mobile from StudentInfo

where Student_Sex='0' and Student_BirthDay between '19940101' and '19941231'

(或where Student_Sex='0' and year(Student_BirthDay)=1994)

18. 查询教师表teacherinfo中在2005年入职的教师的基本信息。

select *

from teacherInfo

where Teacher_Hiredate between '20050101' and '20051231'

19. 查询“学生信息表”StudentInfo中姓“王”同学的学号和姓名。

select Student_Code,Student_Name from StudentInfo

where Student_Name like '王%'

20. 查询“学生信息表”StudentInfo中姓“李”并且名字中带有“明”字在2013年入学的

同学的学号和姓名、手机号码。

select Student_Code,Student_Name, Student_Mobile from StudentInfo

where Student_Name like '李%明%' and Student_indate between '20130101 'and '20131201'

21. 查询“课程信息表”CourseInfo以“中”或者“国”开头的课程编码、课程名称。

select Course_Code,Course_Name from CourseInfo

where Course_Name like '[中国]%'

22. 查找“学生信息表”StudentInfo中学号为单数的学生信息。

select *

from StudentInfo

where Student_Code like '%[13579]'

23. 查找“学生信息表”StudentInfo中学号的最后一位不是0到7的学生信息。

select *

from StudentInfo

where Student_Code like '%[^0-7]'

24. 查询“学生信息表”StudentInfo中学号最后两位是21的学生信息。

select *

from StudentInfo

where Student_Code like '!'

25. 查询学生表StudentInfo中未留手机号码的学生情况,并以学号进行排序。

select *

from StudentInfo

where Student_Mobile is null order by student_code

26. 查询”课程信息表”courseinfo中没有人数限制的课程信息,以课程名称进行升序排列。

select *

from CourseInfo

where Course_limit is null order by Course_Name asc

27. 查询“学生信息表”StudentInfo中学号中有7且最后一位是3或4的学生,并按照学号

进行降序排序。 select *

from StudentInfo

where Student_Code like '%7%[34]' order by Student_Code desc

28. 查询“学生信息表”StudentInfo中学生的信息，根据入学时间排升序，学号排降序,显

示前100个学生的基本信息。 Select top 100 * from StudentInfo

order by student_indate asc ,student_name desc

29. 查询学生信息表StudentInfo中学号在130016201到130016220之间（包括第一个和最

后一个）学生所有基本信息,并按照姓名升序排序。 select *

from StudentInfo

where Student_Code between '130016201' and '130016220' order by Student_Name asc

30. 把课程表CourseInfo中课程类型排升序，学分排降序，但是要排除学分大于5的课程。

select *

from CourseInfo

where Course_Credit<=5

order by Course_Type asc,Course_Credit desc

子查询

1. 查询“课程信息表”CourseInfo中课程编号为Z10001、Z10007、Z10010、Z10025的课

程名称、学分。（易）

select Course_Name,Course_Credit From courseInfo

where Course_Code in('Z10001','Z10007','Z10010','Z10025')

2. 查找与陈欣老师在同一个系的所有教师的基本信息。（易）

Select * from teacherinfo

Where dept_id in (select dept_id from teacherinfo where Teacher_Name='陈欣')

3. 将学生信息表studentinfo中1400111班学生的学号和姓名数据复制到新表newStudent

中. （易）

select Student_Code,Student_Name into newStudent from StudentInfo

where class_id in(select class_id from classinfo where class_code='1400111')

4. 将所有学生的“计算机基础”成绩置为70 。（易）

Update student_course Set course_grade=70

Where course_id in(select course_id from courseinfo where course_name='计算机基础')

5. 删除李波的 “程序设计基础”课程成绩记录。（中）

Delete from student_course

Where course_id in(select course_id from courseinfo where

course_name='程序设计基础')

and student_id in (select student_id from studentinfo where student_name='李波')

6. 查询比1300162班的所有学生年龄都小的学生的学号、姓名和出生日期。（中）

select Student_Code,Student_Name,Student_BirthDay from StudentInfo where Student_BirthDay>all

(select Student_BirthDay from StudentInfo

where Class_ID=(select Class_ID from ClassInfo where Class_Code='1300162') )

7. 查询比李波所在班级学生年龄都大的学生信息。（中）

select * from StudentInfo where Student_BirthDay

select Student_BirthDay from StudentInfo where Class_ID=( select Class_ID from StudentInfo where student_name='李波') )

8. 查询课程表中比“SQL Server数据库应用”的学分都大的课程信息。（易）

select * from CourseInfo where Course_Credit>all(

select Course_Credit from CourseInfo where 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 StudentInfo where 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 StudentInfo where 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_name

from studentinfo,student_course where

studentinfo.Student_ID=Student_Course.Student_ID

and

Course_Grade>=80

2. 查询选修了“程序设计基础”的学生的ID号. （易）

select student_id

from Student_Course,CourseInfo

where CourseInfo.Course_ID=Student_Course.Course_ID and Course_Name='程序设计基础' and CourseInfo.course_type=0

3. 查找“软件工程系”所开课程的课程编码、课程名称、学分。（易）

select course_code,course_name,course_credit from CourseInfo,DeptInfo

where DeptInfo.Dept_ID=CourseInfo.Dept_ID and Dept_Name='软件工程系'

4. 查找班级编号为1400201的这学生的学号、姓名、性别。（易）

select student_code,student_name,student_sex from StudentInfo,ClassInfo

where StudentInfo.Class_ID=ClassInfo.Class_ID and class_Code='1400201'

5. 查找开设了课程号Z10003的系部信息。（易）

select deptInfo.*

from CourseInfo,deptInfo

where CourseInfo.Dept_ID=DeptInfo.Dept_ID and Course_Code='Z10003'

6. 查询每个教师的信息，包括系部编码、系部名称、教师工号、教师姓名。（易）

select dept_code,dept_name,teacher_code,teacher_name from DeptInfo,teacherInfo

where DeptInfo.Dept_ID=teacherInfo.Dept_ID

7. 查询单科学分大于3课程的课程信息，包括系部名称、课程编码、课程名称。（易）

select dept_name,course_code,course_name from courseinfo,deptinfo

where courseinfo.dept_id=deptinfo.dept_id and course_credit>3

8. 查询“软件工程系”手机号码采用133号段的教师信息，信息包括系部名称、教师工号、

教师名称、手机号码。（易）

select dept_name,teacher_code,teacher_name,teacher_mobile from DeptInfo,teacherInfo

where teacherInfo.dept_id=deptinfo.dept_id and Dept_Name='软件工程系' and left(teacher_mobile,3) = '133'

9. 查询所有的课程情况及其被选修信息，如果有课程未被选修，也需要包含该课程的信息。

（易）

select * from CourseInfo left join Student_Course on CourseInfo.Course_ID=Student_Course.Course_ID

10. 查询所有教师情况及教师课表情况，如果有教师未上课，也需要包含该教师的信息。（易）

select * from teacherInfo left join Teacher_Class_Course on teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id

11. 查询课程为“数据库系统原理”的成绩在前10名的学生信息，信息包括学号、姓名、

成绩，成绩按降序排列（中）

select top 10 student_code,student_name,course_grade from StudentInfo,Student_Course,CourseInfo

where StudentInfo.Student_ID=Student_Course.Student_ID and CourseInfo.Course_ID=Student_Course.Course_ID and Course_Name='数据库系统原理' order by course_grade desc

12. 查询教师工号为'10003'的教师姓名，所授课程代码，课程名称。（中3）

select Teacher_Name,Course_Code,Course_Name from teacherInfo,Teacher_Class_Course,CourseInfo

where teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Id and Teacher_Code='10003'

13. 查询每个同学的信息，包括系部名称、班级名称、学号、姓名。（中3）

select Dept_Name,Class_Name,Student_Code,Student_Name from studentinfo,ClassInfo,DeptInfo

where studentinfo.Class_ID=ClassInfo.Class_ID and ClassInfo.Dept_ID=DeptInfo.Dept_ID

14. 查询学生代码为130016309'的姓名，所选课程代码，课程名称，成绩。（中3）

select student_name,course_code,course_name,course_grade from StudentInfo,Student_Course,CourseInfo

where StudentInfo.Student_ID=Student_Course.Student_ID and CourseInfo.Course_ID=Student_Course.Course_ID and Student_Code='130016309'

15. 查询1300161班“网页设计与制作”课程的前三名的学生学号、姓名和成绩。（较难4）

select top 3 student_code,student_name,course_grade from StudentInfo,Student_Course,CourseInfo,ClassInfo where StudentInfo.Student_ID=Student_Course.Student_ID and CourseInfo.Course_ID=Student_Course.Course_ID and ClassInfo.Class_ID=StudentInfo.Class_ID

and Course_Name='网页设计与制作' and Class_Code='1300161' order by course_grade desc

16. 查询课程成绩大于等于85分的学生信息，包括班级名称、学号、学生姓名、课程名称、

成绩（较难4）

select class_name,student_code,student_name,course_name,course_grade

from StudentInfo,Student_Course,CourseInfo,ClassInfo where StudentInfo.Student_ID=Student_Course.Student_ID and CourseInfo.Course_ID=Student_Course.Course_ID and

ClassInfo.Class_ID=StudentInfo.Class_ID and course_grade>85

17. 查询陈欣老师的课表，信息包括授课班级编号、课程编号和课程名称（较难4）

select class_code,Course_Code,Course_Name

from teacherInfo,Teacher_Class_Course,CourseInfo,ClassInfo where teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Id and

classinfo.Class_ID=Teacher_Class_Course.Class_Id

and

Teacher_name='陈欣'

18. 查询1300162班的班级课表，信息包括授课班级编号、教师姓名和课程名称（较难4）

select class_code,Course_Name,Teacher_Name

from teacherInfo,Teacher_Class_Course,CourseInfo,ClassInfo where teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Id and

classinfo.Class_ID=Teacher_Class_Course.Class_Id

and

Class_Code='1300162'

19. 查询李波同学的上课课表，信息要包括授课老师姓名，课程名称（难5）

select Course_Name,Teacher_Name from

teacherInfo,Teacher_Class_Course,CourseInfo,ClassInfo,StudentInfo where teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Id and classinfo.Class_ID=Teacher_Class_Course.Class_Id and StudentInfo.Class_ID=ClassInfo.Class_ID and Student_Name='李波'

select Course_Name,Teacher_Name

from teacherInfo,Teacher_Class_Course,CourseInfo,StudentInfo where teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Id and studentinfo.Class_ID=Teacher_Class_Course.Class_Id and Student_Name='李波'

20. 查询陈欣老师所授课课程的学生学号、姓名、课程名和成绩（难5）

select student_code,student_name,course_name,course_grade from

teacherInfo,Teacher_Class_Course,CourseInfo,StudentInfo,student_course

where teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_Id and CourseInfo.Course_ID=Teacher_Class_Course.Course_Id

and StudentInfo.Class_ID=Teacher_Class_Course.Class_Id and student_course.student_id=studentinfo.student_id and student_course.course_id=courseinfo.course_id and Teacher_name='陈欣'

分组与聚合函数

10易

1、查询每个系部ID所拥有的班级个数。

select Dept_ID 系部ID,count(*) 班级个数 from ClassInfo group by Dept_ID

2、查询每个系部ID所拥有的教师人数。

select Dept_ID 系部ID,count(*) 教师人数 from teacherInfo group by Dept_ID

3、查询每个系部ID所开设的课程门数。

select Dept_ID 系部ID,count(*) 课程门数 from CourseInfo group by Dept_ID

4、查询每个系部ID所开设的课程的学时总数。

select Dept_ID 系部ID,sum(Course_period) 学时总数 from CourseInfo group by Dept_ID

5、查询每个学生ID所学课程的最高成绩。

select Student_ID 学生ID,max(Course_Grade) 最高成绩 from Student_Course group by Student_ID

6、查询每个学生ID所学课程的总成绩。

select Student_ID 学生ID,sum(Course_Grade) 总成绩 from Student_Course group by Student_ID

7、分性别查询学生总人数。

select Student_Sex 性别,count(*) 总人数 from StudentInfo group by Student_Sex

8、分课程性质查询各性质课程的课程门数。

select Course_Type 课程性质,count(*) 课程门数 from CourseInfo group by Course_Type

9、按课程性质查询各性质课程的最高学分与最低学分。

select Course_Type 课程性质,max(Course_Credit) 最高学

分,min(Course_Credit) 最低学分 from CourseInfo group by Course_Type

10、查询每个学生ID所学课程的平均成绩。

select Student_ID 学生ID,avg(Course_Grade) 平均成绩 from Student_Course group by Student_ID 5中

--1、查询各门课程的名称，及其对应的平均成绩、最高成绩。

select Course_Name 课程名称,avg(Course_Grade) 平均成绩 ,max(Course_Grade)

最高成绩

from CourseInfo inner join Student_Course on CourseInfo.Course_ID=Student_Course.Course_ID group by Course_Name

2、查询所有学生的姓名，及其所有课程的总成绩。

select Student_Name 学生姓名,sum(Course_Grade) 课程总成绩 from StudentInfo inner join Student_Course on StudentInfo.Student_ID=Student_Course.Student_ID group by Student_Name

--3、查询所有教师的姓名，及其所教课程的门数。

select Teacher_Name 教师姓名,count(*) 所教课程门数

from teacherInfo inner join Teacher_Class_Course on teacherInfo.Teacher_ID=Teacher_Class_Course.Teacher_ID group by Teacher_Name

4、查询所有班级的名称，及其开设课程的门数。

select Class_Name 班级名称,count(*) 开设课程门数

from ClassInfo inner join Teacher_Class_Course on ClassInfo.Class_ID=Teacher_Class_Course.Class_ID group by Class_Name

5、查询每个学院的名称，及其拥有班级的个数。

select Dept_Desc 学院名称,count(*) 班级个数 from DeptInfo inner join ClassInfo on DeptInfo.Dept_ID=ClassInfo.Dept_ID group by Dept_Desc 3较难

1、统计各班“SQL Server数据库应用”课程的平均分，返回班级名称，课程名称，平均分。 select Class_Name 班级名称,Course_Name 课程名称,avg(Course_Grade) 平均分 from ClassInfo inner join StudentInfo on ClassInfo.Class_ID=StudentInfo.Class_ID inner join Student_Course on

StudentInfo.Student_ID=Student_Course.Student_ID

inner join CourseInfo on Student_Course.Course_ID=CourseInfo.Course_ID where Course_Name='SQL Server数据库应用' group by Class_Name,Course_Name

2、统计班各门课程的最高分，返回班级名称，课程名称，最高分。

select Class_Name 班级名称,Course_Name 课程名称,max(Course_Grade)最高分 from ClassInfo inner join StudentInfo on ClassInfo.Class_ID=StudentInfo.Class_ID inner join Student_Course on

StudentInfo.Student_ID=Student_Course.Student_ID

inner join CourseInfo on Student_Course.Course_ID=CourseInfo.Course_ID where Class_Name='1300161班'

group by Class_Name,Course_Name

3、统计软件学院各系部各门课程的平均分，返回系部名称，课程名称，平均分。

select Dept_Name 系部名称,Course_Name 课程名称,avg(Course_Grade)平均分 from DeptInfo inner join CourseInfo on DeptInfo.Dept_ID=CourseInfo.Dept_ID inner join Student_Course on

CourseInfo.Course_ID=Student_Course.Course_ID where Dept_Desc='软件学院'

group by Dept_Name,Course_Name

select Dept_Name 系部名称,Course_Name 课程名称,avg(Course_Grade)平均分 from DeptInfo inner join ClassInfo on DeptInfo.Dept_ID=ClassInfo.Dept_ID

inner join StudentInfo on ClassInfo.Class_ID=StudentInfo.Class_ID inner join Student_Course on

StudentInfo.Student_ID=Student_Course.Student_ID

inner join CourseInfo on Student_Course.Course_ID=CourseInfo.Course_ID where Dept_Desc='软件学院'

group by Dept_Name,Course_Name 2难

1、统计课程平均分达到分的系部信息，返回学院名称，系部名称，课程名称，平均分>80。并按平均分降序显示。

select Dept_Desc,Dept_Name,Course_Name,avg(Course_Grade)平均分 from DeptInfo inner join CourseInfo on DeptInfo.Dept_ID=CourseInfo.Dept_ID inner join Student_Course on

CourseInfo.Course_ID=Student_Course.Course_ID group by Dept_Desc,Dept_Name,Course_Name having avg(Course_Grade)>80 order by avg(Course_Grade) desc

select Dept_Desc,Dept_Name,Course_Name,avg(Course_Grade)平均分 from DeptInfo inner join ClassInfo on DeptInfo.Dept_ID=ClassInfo.Dept_ID

inner join StudentInfo on ClassInfo.Class_ID=StudentInfo.Class_ID inner join Student_Course on

StudentInfo.Student_ID=Student_Course.Student_ID

inner join CourseInfo on Student_Course.Course_ID=CourseInfo.Course_ID group by Dept_Desc,Dept_Name,Course_Name having avg(Course_Grade)>80 order by avg(Course_Grade) desc

2、统计课程平均分达到分的学生信息，返回学号，姓名，平均分。并按平均分降序显示。 select Student_Code,Student_Name,avg(Course_Grade)平均分 from StudentInfo inner join Student_Course on StudentInfo.Student_ID=Student_Course.Student_ID

inner join CourseInfo on Student_Course.Course_ID=CourseInfo.Course_ID group by Student_Code,Student_Name having avg(Course_Grade)>80 order by avg(Course_Grade) desc

视图5~10

60%以上易

1、创建视图，用于显示学生的学号，姓名，课程ID，成绩。 create view v_xscj as begin

select Student_Code,Student_Name,Course_ID,Course_Grade from StudentInfo inner join Student_Course on StudentInfo.Student_ID=Student_Course.Student_ID end

2、创建视图，用于显示教师姓名，承担的课程ID。 create view v_jskc as

select Teacher_Name,Course_Id

from teacherInfo inner join Teacher_Class_Course on teacherInfo.Teacher_Id=Teacher_Class_Course.Teacher_Id

3、创建视图，用于显示学院名称，系部名称，所拥有的教师的编号及姓名。 create view v_xyjs as

select Dept_Desc,Dept_Name,Teacher_Code,Teacher_Name from DeptInfo inner join teacherInfo on DeptInfo.Dept_ID=teacherInfo.Dept_ID

4、创建视图，用于显示系部名称，所拥有的班级的编号及班级名称。

create view v_xbbj as

select Dept_Name,Class_Code,Class_Name from DeptInfo inner join ClassInfo on DeptInfo.Dept_ID=ClassInfo.Dept_ID

5、创建视图，用于显示班级名称，所拥有学生的学号及姓名。 create view v_bjxs as

select Class_Name,Student_Code,Student_Name from ClassInfo inner join StudentInfo on ClassInfo.Class_ID=StudentInfo.Class_ID

6、创建视图，用于显示班级名称，及各班级所开设的课程ID。 create view v_bjkc as

select Class_Name,Course_Id

from ClassInfo inner join Teacher_Class_Course on ClassInfo.Class_ID=Teacher_Class_Course.Class_ID

7、创建视图并加密，用于显示班学生的学号，姓名，课程名，成绩。 create view v_xscj_jm with ENCRYPTION as

select Student_Code,Student_Name,Course_Name,Course_Grade from StudentInfo inner join Student_Course on StudentInfo.Student_ID=Student_Course.Student_ID

inner join CourseInfo on Student_Course.Course_ID=CourseInfo.Course_ID

8、创建视图并加密，用于显示系部名称，教师姓名，承担的课程数。 create view v_jskc_jm with ENCRYPTION as

select Dept_Name,Teacher_Name,count(*) kcs from DeptInfo inner join teacherInfo on DeptInfo.Dept_ID=teacherInfo.Dept_ID inner join Teacher_Class_Course on

teacherInfo.Teacher_Id=Teacher_Class_Course.Teacher_Id group by Dept_Name,Teacher_Name

9、创建视图并加密，用于显示班级名称，教师姓名，课程名称。 create view v_bjjskc_jm with ENCRYPTION as

select Class_Name,Teacher_Name,Course_Name

from ClassInfo inner join Teacher_Class_Course on ClassInfo.Class_Id=Teacher_Class_Course.Class_Id

inner join teacherInfo on

Teacher_Class_Course.Teacher_Id=teacherInfo.Teacher_Id

inner

join

CourseInfo

Teacher_Class_Course.Course_ID=CourseInfo.Course_ID

三、T-SQL编程

存储过程

1、在scmdb数据库中新建存储过程：Proc_AddDept，用于向表DeptInfo中新增一条数据，数据内容如下：(易) Dept_Code 900 Dept_Name 软件学院Test Dept_Desc 是学院最大的二级学院 CREATE PROC Proc_AddDept AS

BEGIN

INSERT INTO deptInfo

( dept_code, dept_name, dept_desc )

VALUES ( '900', '软件学院Test', '是学院最大的二级学院' ) END GO

2、在scmdb数据库中新建存储过程：Proc_AddClass，用于向表ClassInfo中新增一条数据，数据内容如下：(易) Class_Code 1400165 Class_Name 1400165班 Dept_ID 1 CREATE PROC Proc_AddClass AS

BEGIN

INSERT INTO ClassInfo

( Class_Code, Class_Name, Dept_ID ) VALUES ( '1400165', '1400165班', 1) END GO

3、在scmdb数据库中新建存储过程：Proc_AddStudent，用于向表StudentInfo中新增一条数据，数据内容如下：(易) Student_Code 140016508 Student_Name 张三 Student_Sex 1 Class_ID 1 CREATE PROC Proc_AddStudent AS

BEGIN

INSERT INTO StudentInfo

( Student_Code, Student_Name, Student_Sex,Class_ID ) VALUES ( '140016508', '张三', 1,1) END

4、在scmdb数据库中新建存储过程：Proc_AddCourse，用于向表CourseInfo中新增一条数据，数据内容如下：(易) Dept_ID 1 Course_Code 201301031 Course_Name Course_Credit Course_Type 数据库SQL 2 Server 1 Course_limit 300 CREATE PROC Proc_AddCourse AS

BEGIN

INSERT INTO CourseInfo ( Dept_ID ,

Course_Code , Course_Name , Course_Credit , Course_Type , Course_limit )

VALUES ( 1 ,

'201301031' ,

'数据库SQL Server' , 2 , '1' , 300 ) END GO

5、在scmdb数据库中新建存储过程：Proc_AddTeacher，用于向表TeacherInfo中新增一条数据，数据内容如下：(易) Teacher_Code 00201 Teacher_Name 张浩然 Teacher_Mobile 15999999999 CREATE PROC Proc_AddTeacher AS

BEGIN

INSERT INTO TeacherInfo ( Teacher_Code , Teacher_Name , Teacher_Mobile )

VALUES ( '00201' , '张浩然' , '15999999999' ) END GO

6、在scmdb数据库中新建存储过程：Proc_GetDept，从表DeptInfo中查询出所有数据。(易) CREATE PROC Proc_GetDept AS

BEGIN

SELECT *

FROM DeptInfo END GO 7、在scmdb数据库中新建存储过程：Proc_GetClass，从表ClassInfo中查询出所有数据。(易) CREATE PROC Proc_GetClass AS

BEGIN

SELECT *

FROM ClassInfo END GO

8、在scmdb数据库中新建存储过程：Proc_GetStudent，从表StudentInfo中查询出所有数据。(易)

CREATE PROC Proc_GetStudent AS

BEGIN

SELECT *

FROM StudentInfo END GO

9、在scmdb数据库中新建存储过程：Proc_GetCourse，从表CourseInfo中查询出所有数据。(易)

CREATE PROC Proc_GetCourse AS

BEGIN

SELECT *

FROM CourseInfo END GO

10、在scmdb数据库中新建存储过程：Proc_GetTeacher，从表TeacherInfo中查询出所有数据。(易)

CREATE PROC Proc_GetTeacher AS

BEGIN

SELECT *

FROM TeacherInfo END GO

11、在scmdb数据库中新建存储过程：Proc_InsertDept，向表DeptInfo中新增数据，要求使用参数：Dept_Code、Dept_Name、Dept_Desc，并执行存储过程Proc_InsertDept 。(中) CREATE PROC Proc_InsertDept @Dept_Code VARCHAR(50) , @Dept_Name VARCHAR(50) , @Dept_Desc VARCHAR(100) AS

BEGIN

INSERT INTO DeptInfo ( Dept_Code , Dept_Name , Dept_Desc )

VALUES ( @Dept_Code , @Dept_Name , @Dept_Desc ) END GO

--执行存储过程

EXEC Proc_InsertDept '110', '软件学院test', '软件学院描述test'

12、在scmdb数据库中新建存储过程：Proc_GetStudent，从表StudentInfo中通过学生姓名模糊查询查询出学生信息，要求使用参数：Student_Name，并执行存储过程Proc_ GetStudent。(中)

CREATE PROC Proc_GetStudent @Student_Name VARCHAR(32) AS

BEGIN

SELECT *

FROM StudentInfo

WHERE Student_Name LIKE '%' + @Student_Name + '%' END GO

--执行存储过程

EXEC Proc_GetStudent '李'

13、在scmdb数据库中新建存储过程：Proc_UpdateDept，按条件修改表DeptInfo中的数据，要求使用参数：Dept_ID 、Dept_Code、Dept_Name、Dept_Desc，并执行存储过程Proc_UpdateDept 。(中)

CREATE PROC Proc_UpdateDept @Dept_ID INT ,

@Dept_Code VARCHAR(50) , @Dept_Name VARCHAR(50) , @Dept_Desc VARCHAR(100) AS

BEGIN

UPDATE DeptInfo

SET Dept_Code = @Dept_Code , Dept_Name = @Dept_Name , Dept_Desc = @Dept_Desc WHERE Dept_ID = @Dept_ID END GO

--执行存储过程

EXEC Proc_UpdateDept 1,'TestCode','TestName','TestDesc'

14、在scmdb数据库中新建存储过程：Proc_ DeleteStudent，按条件删除表StudentInfo中的数据，要求使用参数Student_ID，并执行存储过程Proc_ DeleteStudent。(中) CREATE PROC Proc_DeleteStudent @Student_ID INT AS

BEGIN

DELETE FROM StudentInfo

WHERE Student_ID = @Student_ID END GO

--执行存储过程

EXEC Proc_DeleteStudent 10

15、在scmdb数据库中新建存储过程：Proc_InsertStudent，向表StudentInfo中新增数据，要求使用参数：Student_Code、Student_Name、Student_Sex、Class_ID，并执行存储过程Proc_ InsertStudent。(中)

CREATE PROC Proc_InsertStudent @Student_Code VARCHAR(50) , @Student_Name VARCHAR(50) , @Student_Sex INT , @Class_ID INT AS

BEGIN

INSERT INTO StudentInfo ( Student_Code , Student_Name , Student_Sex , Class_ID )

VALUES ( @Student_Code , @Student_Name , @Student_Sex , @Class_ID ) END

--执行存储过程

EXEC Proc_InsertStudent '140016509','李四',1,1