SQLServer实例教程课堂练习部分含答案doc.docx
《SQLServer实例教程课堂练习部分含答案doc.docx》由会员分享，可在线阅读，更多相关《SQLServer实例教程课堂练习部分含答案doc.docx（25页珍藏版）》请在冰豆网上搜索。
SQLServer实例教程课堂练习部分含答案doc
任务一：
基本信息：
医院：
编号，院长号，名称，地点
医生：
职工号，姓名，年龄，性别，职称
病人：
床号，姓名，病症，主治医师，住院费病房：
房号，楼层
联系：
一个医院有多名医生，一个医生就在一个医院上班。
（工作）一个医院有一个院长，院长本身是医生・（管理）
一个医生负责多个病人，一个病人被多名医生治疗。
（治疗）一个医院有多个病人，一个病人只在一个医院治疗（照顾）一个医院有多了病房，（建有）
一个病房有多个病人（入住）
应该记录每个病人住进病房的时间（入住时间）
（1:
1的关系向大级［向上］和并）
任务二、
基本信息：
公司：
公司名，经理号,人数
员工：
员工号,姓名，年龄，性别
客户：
客户号,姓名，对应办公室编号，业务
办公室：
编号,名称,人数，
联系：
一个公司有多名员工，一个员工只能在一个公司工作。
（工作）一个公司有一个经理，经理本身是员工.（领导）
一个员工负责多个客户，一个客户被多名员工接待。
（负责）
一个公司有多个办公室（拥有）
一个办公室接待多个客户，一个客户对应一个办公室（接待）一个办公室管理多个员工，一个员工只能在一个办公室（管理）应该记录每个办公室接待客户的次数（接待次数）
1.创建“实验1”数据库，要求保存在D盘下自己学号的文件夹中。
数据文件名为“实验1”，日志文件为''实验1日志”。
数据文件要求初始为3,自动增长1。
日志文件要求，初始1,最多IO-
2.创建“实验2”数据库，要求保存在D盘下自己学号的文件夹屮。
各种文件名称使用默认名称。
数据文件要求初始为5,自动增长2,最多50。
日志文件要求，初始1,自动增长10%,最多10o
3.要建立一个图书管理数据库，信息如下：
目前图书馆有图书20万，每本书的信息占用1Kb。
以后，每年的图书要增加1万册。
目前学校有学生5000人，每年新来学生2000人，也毕业2000人。
每个学生的信息占用200bo
每年会发生借还书事件1万次，每次信息占用空间50bo
根据上述情况，创建数据库方案。
初始大小？
？
增长方式？
？
增长多少？
？
1.新建基本表“实验表”，表中有4个字段：
字段loooo字段4。
字段1字符型长度10
字段2数值型
字段3和4同字段lo
2.为上表添加约束：
字段1为主键
字段2要求在0-100Z间
字段3具有非空性
字段4的默认值为“缺省”
1.商品中的名称，品牌和进价是公司经常要查询的三个信息，通过视图来简化检索工作。
2.将所有高价商品（4000元以上）的信息保存到视图屮
3.个人类型的用户经常查询购买信息，所以需要通过视图保存个人购买商品的名称和时间。
4.将购买了笔记本的买家名称和电话存为一个视图
5.将所有一级买家购买的计算机类商品名称存到视图中，并保存购买的时间
SELECT+FROM练习
1.查询所有商品的信息
2.查询所有买家信息
3.查询买家的名称和电话
4.查询我们的系统中，都是那些品牌的商品
WHERE练习
5.查询所有A品牌商品的信息
6.查询所库存小于50的商品信息
7.查询所有进价大于3000的商品名称和品牌
8.查询所有A品牌中售价大于5000的商品名称和进价
9.查询售价介于3000-5000之间的商品信息
10.查询级別为一级或二级的漓品类別名称
11.查询买家中，有哪些大学
12.查询J02级别的买家中，电话是以“23”开头的
ORDERBY练习
13.查询商品的信息，并按照进价多少排序
14.查询所有商品的名称和进价，并按照售价多少排序，如果售价相同，按照进价多少排序
LIMIT练习（mysql练习，SQL没有）
15.查询谕品表屮的前5个谢品信息
16.查询商品表中的后5个商品信息
17.查询商品表中第5到第10个商品信息
GROUPBY练习
18.查询各个品牌商品的平均进价
19.查询各个品牌有多少种商品
20.查询各个品牌共有多少库存
21.查询商品种类在3种以上的各个品牌商品的平均进价
22.查询销售价格在2000元以上的商品中，各个品牌的库存总数，只显示库存总数在200个以上的。
综合
查询A品牌中，售价在3000元以上的商品中，进价最贵的3件商品信息
1.查询所有买家的信息
2.查询进价大于2000的商品信息，包括名称，品牌
3.查询售价排在前5名（比较贵）的商品信息
4.查询所有A品牌商品信息
5.查询B品牌中最贵的两件商品
6.查询2013年1月1日的销售信息
7.查询所有型号以A开头的商品信息
8.查询所有商品的平均价格
9.查询A品牌商品的数量
10.查询超过30（）（）元的商品数量
11.查询各类型商品的平均价格
12.查询各个品牌商品库存的总数，只显示总数在100个以上的品牌，并按照数量排序
13.查询进价超过1000的商品中中，各个品牌商品库存的总数，只显示总数在100个以上的品牌，并按照总数排序
1.查询售价大于5000的商品名称和类型名称。
2.查询A品牌笔记本的类别名称
3.查询一级买家有哪些，显示名称和电话
4.查询A大学可以享受的折扣
5.查询B医院购买商品的时间和商品名称
6.查询购买了A品牌笔记本的买家名称和电话
7.查询购买了计算机类产品的买家名称。
8.查询一级买家购买商品的信息，包括购买谢品的名称、品牌和时间。
9.查询各个买家购买筒品的总额，显不买家名称和总额。
10.查询各个买家购买A牌商品的数竝，要求只显示购买数量在50个以上的买家姓名和
购买数量。
最后按照购买数量的降序排序。
1.查询一级买家的名称。
2.查询售价大于5000元的数码类商品的名称和品牌。
3.查询二级买家的购买情况，显示他们的名称以及购买商品的名称、购买时间
4.查询2014年以前的销售信息，包括购买者是谁，买家的级别是什么
5.查询所有一级买家购买二级商品的信息，显示名称和购买时间。
6.查询二级买家中，哪些买家购买了名称中含有“机”的相关商品，显示买家的名称和电话。
7.查询各级别买家的数量，显示级别名称和数量
8.查询各级别商品的平均价格，显示级别和价格
9.查询A品牌的各类商品的数量，要求显示类型名称和数量
10.查询各个买家购买商品的数量，显示买家名称和数量
11.查询各品牌的商品中价格超过100元的商品被购买的数量。
12.查询各个一级买家购买商品的数暈，只显示数暈超过100的买家信息。
难题：
1.查询购买了名称中含有“机”字，产品型号的第二个字为“T”的商品，并且购买这类商品数量大于20,购买总额大于8000的买家屮，哪个买家的购买额度最大，显示其名称和购买总额度
2.查询哪些商品没有按照应有的折扣价销售。
外连接
1.查询所有买家的购买信息，不论是否购买商品都显示其名称及购买信息。
2.查询所有进价小于100的商品信息及其销售信息，即不论是否被购买，商品信息都要显75。
3.查询所有谢品的信息及其购买它的买家信息，显示商品的名称和购买它买家的姓名。
不论商品是否被购买，商品信息都要显示。
4.查询C品牌商品的购买情况（所有C品牌商品都要显示，还包括买家姓名和购买时间）
子查询
1.查询计算机类商品的名称（子查询）。
2.查询一级买家的信息（子查询）。
3.所有被购买过的商品的名称（子查询）。
4.所有没有被购买过的商品的名称（子查询）。
5.查询比C品牌所有商品都要贵的商品名称和品牌（子查询）。
6.查询比C品牌中任意一个商品贵的商品名称和品牌（子查询）。
外连接+子查询
7.查询所有买家的购买数量
select买家表.买家编号，买家名称，数量
from买家表leftjoin（select买家编号，sum（销售数量）as数量from销售表groupby买家编号）bon买家表.买家编号二b.买家编号
1.向商品表表中添加一条新数据，编号为S40,名称为显卡，品牌为D牌，型号为xkl,类型为L04,进价500,销售价700,库存50。
2.向买家表中添加一条新数据，编号为M08,级别为J03,名称为F公司
1.将编号为S40的显卡销售价改为650
2.将所有商品的售价上调10元。
3.将所有进价小于500的商品的销售价下调10%。
4.将所有计算机类的商品销售价上调5%o
1.删除刚创建的数据
2.删除销售时间在2014年以前，且销售总额在1万以下的销售记录
1、使用语句完成销售管理数据库的创建。
数据文件初始值为5,自动增长1,没有上限。
日志文件初始值2,自动增长10%,上限10。
两个文件都保存到D盘自己学号文件夹中。
2、使用语句完成销售管理数据库屮各表的创建，表结构参见导论屮的介绍
3、为各表添加相应的主键、外键、约束和缺省
（1）各表主键的设置
（2）商品的进价和销售价人于零；库存人于等于0；品牌的最后一个字为牌；
（3）商品名称的默认值为笔记本
（4）将5个基本表建立正确的关系
视图
1、创建一个保存商品基本信息的视图（商品编号，名称，品牌，销售价和库存）
2、创建一个保存个人用户购买商品的视图（商品名称，购买时间）
规则
1、创建一个大于零的规则，绑定到销售表的实际销售价格字段和销售数暈字段
2、解除上面的绑定，并删除规则默认
1、创建一个值为4000的默认，绑定到商品表的进价字段上
2、解除上面绑定，并删除默认
1、两种身份验证模式的转换
2、为销售管理数据库分别创建两个登陆账号，一个是win登陆账号“wxs”；一个是SQL登陆账号“sxs”
3、将上述两个账号映射为销售管理数据库的用户
4、设置“wxs”对买家表具有查看和添加的功能；设置“sxs”对商品表有查看和修改的功能
5、在销售管理数据库中创建一个“xgjs”角色，该角色具有查看和修改所有表格的权限,将上述两个用户添加到这个角色中
1、创建一个销售管理数据库的备份设备
2、为销售管理数据库实施一次完全备份
3、为销售管理数据库实施一次差界备份，删除英屮的买家表，再进行一次差异备份，删除其中的商品表
4、恢复到有商品表，没买家表的状态
5、恢复到所有表齐全的状态
1、
1.创建存储过程，查询所有买家资料
2.创建存储过程，查询购买了笔记本的买家名称
3.创建存储过程，根据买家名称查询买家资料
4.创建存储过程，根据品牌情况查询商品资料
5.创建存储过程，根据品牌和名称查询商品资料
6.创建存储过程，根据销售时间和买家编号查询销售情况（包括•买家名称和商品名称）
7.创建存储过程，根据品牌情况查询商品资料，品牌默认为A牌
8.创建存储过程，根据销售时间和买家编号查询销售情况，编号默认为M01
1.创建高价商品存储过程，要求保存价格在5000元以上商品的信息。
2.创建存储过程，将购买次数超过10次的买家编号查询出来。
3.创建存储过程，根据商品名称查询商品的信息。
创建好后，查询笔记本的信息
4.创建公司存储过程，根据用户输入的公司名称显示该公司的信息。
创建好后，查询C局的信息
5.创建销售情况存储过程，根据用户输入的买家名称和商品名称，查询其购买情况。
创建好后，查询A大学购买笔记本的信息。
6.创建存储过程，查询某品牌某商品的销售信息（包括谁买的，什么时候买的，多钱买的），查询A牌台式机的次数比较多。
7.创建某品牌高价商品存储过程，根据用户输入的品牌及进价信息，查询该品牌大于
用户输入进价的商品信息。
其中，用户经常查询A品牌的商品。
创建好后，调用该存储过程查询A品牌屮大于3000元的商品信息。
任务一：
基本信息：
医院：
编号，院长号，名称，地点
医生：
职工号，姓名，年龄，性别，职称
病人：
床号，姓名，病症，主治医师，住院费病房：
房号，楼层
联系：
一个医院有多名医生，一个医生就在一个医院上班。
（工作）一个医院有一个院长，院长本身是医生・（管理）
一个医生负责多个病人，一个病人被多名医生治疗。
（治疗）一个医院有多个病人，一个病人只在一个医院治疗（照顾）一个医院有多了病房，（建有）
一个病房有多个病人（入住）应该记录每个病人住进病房的时间（入住时间）
（1：
1的关系向大级［向上］和并）
答案：
实体
病房
房号,楼层，编号
l：
n联系
编号，职工号
l：
n联系
房号，编号
l：
n联系
入住/X|_lT
床号，房号，入住时间
l:
n联系
W
床号，编号
1:
1联系
职工号，编号
m:
n联系
治疗
职工号，床号
任务二、
基本信息：
公司：
公司名，经理号,人数
员工：
员工号,姓名，年龄，性别
客户：
客户号,姓名，对应办公室编号，业务
办公室：
编号,名称,人数，
联系：
一个公司有多名员工，一个员工只能在一个公司工作。
（工作）一个公司有一个经理，经理本身是员工・（领导）
一个员工负责多个客户，一个客户被多名员工接待。
（负责）
一个公司有多个办公室（拥有）
一个办公室接待多个客户，一个客户对应一个办公室（接待）一个办公室管理多个员工，一个员工只能在一个办公室（管理）应该记录每个办公室接待客户的次数（接待次数）
答案：
实体
公司
公司名，经理号，人数，员工号
实体
员工
员工号，姓名，年龄，性别，名称，编号
实体
客户
客户号，姓名，对应办公室编号，业务，编号，接待次数
实体
办公室
编号，名称，人数，公司名
l：
n联系
公司名，员工号
l：
n联系
管理
员工号，编号
l：
n联系
编号，公司名
l:
n联系
晞
客户号，编号，接待次数
1:
1联系
员工号，公司名
m:
n联系
负责
员工号，客户号
（1:
1的关系向大级［向上］和并）
—SELECT+FROM练习
--1.查询所有商品的信息
Select*from商品表
—2.查询所有买家信息
Select*from买家表
-3.查询买家的名称和电话
Select买家名称，电话from买家衣
-4•查询我们的系统中，都是那些品牌的商品
Selectdistinct品牌from涪j品表
--WHERE练习
--5.查询所有A品牌商品的信息
Select*from商品表where品牌='A牌'
--6・查询所库存小于的商品信息
Select*from商晶表where库存＜50
-7・查询所有进价大于的商品名称和品牌
Select商品名称，品牌from商品农where进价＞3000
—8.查询所有A品牌中售价大于的商品名称和进价
Select商品名称，进价from商品表where品牌=，A牌，and销售价＞5000
--9・查询售价介于-5000Z间的商品信息
Select*from商品农where销售价between3000and5000
--10.查询级别为一级或二级的商品类别名称
Select类型名称from商品类型表where级别in（*一级',*二级'）
—11.查询买家中，有哪些大学
Select*from买家表where买家名称like，%大学％，
—12.查询J02级别的买家中，电话是以''〃开头的
Select*from买家表where电话like123%1and级別=‘J02‘
-13.查询商品的信息，并按照进价多少排序
Select*from商品表orderby进价desc
-14.查询所有商品的名称和进价，并按照售价多少排序，如果售价相同，按照进价多少排序
Select商品名称，进价from商品表orderby销售价desc，进价desc
--GROUPBY练习
—15.查询各个品牌商品的平均进价
Select品牌，avg（进价）from商品表groupby品牌
■■16.查询各个品牌有多少种商晶
Select品牌，count严）from商品表groupby品牌
—17.查询各个品牌共有多少库存
Select品牌，sum（库存）from商品表groupby品牌
-18.查询商品种类在种以上的各个品牌商品的平均进价
Select品牌“vg（进价）from商品表groupby品牌havingcount（*）＞3
-19.查询销售价格在元以上的商品中，各个品牌的库存总数，只显示库存总数在个以上的。
Select品牌，sum（库存）from商品表where销售价＞2000groupby品牌havingsum（库存）＞200
一综合
--查询A品牌中，售价在元以上的商品中，进价最贵的件商品信息
Selecttop3*from商品表where品牌牌，and销售价＞3000orderby进价desc
--丄・查询所有买家的信息
Select*from买家表
—2.查询进价大于的商品信息，包括幺称，品牌
Select商品名称，品牌from商品表where进价＞2000
-3.查询售价排在前名（比较贵）的商品信息
Selecttop5*fromj筍品表orderby销售价desc
—4.查询所有A品牌商品信息
Select*from商品表where品牌=7牌，
__5.查询B品牌中最贵的两件商品
Selecttop2*from商品表where品牌=，B牌，orderby销售价desc
—6.查询年月日的销售信息
Select*from销售表where销售口期='2013-01-01'
—7•查询所有型号以A开头的商品信息
Select*from商品表where型号like1A%'
-8.查询所有商品的平均价格
Selectavg（销售价）from商品表
—9.查询A品牌商品的数量
Selectcount（*）fromj筍品表where品牌=，A牌，
-10.查询超过元的商品数量
Selectcount（*）fromI筍品表where销售价＞3000
-11.查询各类型商品的平均价格
Select类型，avg（销售价）from商品表groupby类型
—12・查询各个品牌商品库存的总数，只显示总数在个以上的品牌
Select品牌，sum（库存）from商品表groupby品牌havingsum（库存）＞100-13.查询进价超过的商品中中，各个品牌商品库存的总数，只显示总数在个以上的品牌
Select品牌，sum（库存）from商品衣where进价＞1000groupby品牌havingsum（库存）＞100
—1.查询售价大于的商品名称和类型名称。
select商品名称，类型名称
from商品表，商品类型表
where商品表.类型=商品类型表.类型编号and销售价＞5000
—2.查询A品牌笔记本的类别名称
select类型名称
from商品表,商品类型表
where商品表.类型=商品类型表.类型编号and品牌=2牌，and商品名称笔记本，
—3・查询一级买家有哪些，显示名称和电话
select买家名称，电话from买家衣，买家级别衣where买家衣.级别=买家级别农•级别编号and级别名称》—级，
—4・查询A大学可以亨受的折扣
select享受折扣from买家表，买家级别表where买家表.级别=买家级别表.级别编号and买家名称=7大学，
—5.查询B陕院购买商品的时间和商品名称
Select商品名称，销售「I期from商品表，销售表，买家表where商品表.商品编号=销售表.商品编号and买家表•买家编号=销售表.买家编号and买家名称='B医院，
--6・查询购买了A品牌笔记本的买家名称和电话
Select买家名称，电话from商品表，销售表，买家表where商品表.商品编号=销售表.商品编号and买家表.买家编号二销售表.买家编号and品牌》A牌，and商品需称笔记本，
--7・查询购买了计算机类产品的买家名称。
Selectdistinct买家名称from商品表，销售表，买家表，商品类型表where商品类型表.类型编号=商品表.类型and商品表.商品编号=销售表.商品编号and买家表.买家编号=销售表.买家编号and类别名称》计算机，
—8.查询一级买家购买商品的信息，包括购买商品的名称、品牌和时间。
Select商品名称，品牌，销售口期from商品表，销售表，买家表，买家级别表where商品表•商品编号=销售表•商品编号and买家表.买家编号=销售表•买家编号and买家表.级别=买家级别表.级别编号and级别名称一级，
—9.查询各个买家购买商品的总额，显示买家名称和总额。
Select买家名称，sum（实际销售价格★销售数量）
from买家表，销售表
where买家表.买家编号=销售表.买家编号
groupby买家名称一10.查询各个买家购买A牌商品的数量，要求只显示购买数量在个以上的买家姓名和购买数量。
最后按照购买数量的降序排序。
Select买家名称，sum（销售数量）from买家表，销售表，商品表where买家表.买家编号=销售表.买家编号and销售表.商品编号=商品表.商品编号and品牌=*A牌，groupby买家名称havingsum（销售数量）＞50orderbysum（销售数量）desc
--1・查询一级买家的名称。
Select买家名称from买家表，买家级别表where买家表.级别=买家级别表.级別编号and级别名称=，一级，
—2•查询售价大于元的数码类商品的名称和品牌。
Select商品名称，品牌from商品表，商品类型表where商品表.类型二商品类型表.类型编号and销售价＞5000and类型名称数码产品，
—3.查询二级买家的购买情况，显示他们的名称以及购买商品的名称、购买时间
Select买家名称，商品名称，销售日期from买家级别表，买家表，销售表，商品表where买家级别表.级别编号二买家表.级别and买家表.买家编号=销售表.买家编号and销售表.商品编号=商品表.商品编号and级别名称='二级，
__4・查询年以前的销售信息，包括购买者是谁，买家的级别是什么
Select买家名称，级别名称from买家表，买家级别表，销售表where买家级别表.级别编号=买家表.级别and买家表.买家编号=销售表.买家编号and销售日期
＜f2014-01-011
--5・查询所有一级买家购买二级商品的信息，显示名称和购买时间。
Select买家名称，销售日期from买家级别表，买家表，销售表，商品表，商品类型表where买家级別表.级别编号=买家表.级别and买家表.买家编号=销售表.买家编号and销售表.商品编号=商品表.商品编号and商品表.类型=商品类型表.类型编号and买家级别表.级别名称》—级，and商品类型表.级别》二级，
—6.查询二级买家中，哪些买家购买了名称中含有'、机〃的相关商品，显示买家的名称和
电话。
Select买家名
升级会员 