《SQL查询语句习题.pdf》由会员分享，可在线阅读，更多相关《SQL查询语句习题.pdf（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1.检索“商品信息”类别名称=玩具并且零售价在 100 和 300 之间的商品的平均零售价，最大零售价，最小零售价，以及零售价之和Select 类别名称,零售价, min(零售价)最小零售价, max(零售价)最大零售价,avg(零售价)平均零售价,sum(零售价)零售价之和 from 商品信息 a inner join商品类别 b ona.类别 ID=b.类别 ID where 类别名称=玩具 group by 类别名称,零售价2.检索“商品入出库”中审核日期大于2009 年 3 月 1 日，并且入库类型编号为“采购入库”的商品的总金额（入出库数量*进价）返回数据：入出库编号、总金额(商品入
2、出库.入库类型编号=入出库类型.入库类型编号)select a.入出库编号,类型名称,总金额=入出库数量*进价 from 商品入出库 a inner join 入出库类型 b on a.入出库类型编号=b.入出库类型编号inner join 入出库明细 c on c.入出库编号 = a.入出库编号where 审核日期 2009-03-01and 类型名称=采购入库3. 统计每个仓库中库存的除去食品之外的总金额(【仓库】.仓库 ID=【库存.仓库 IDselect 仓库名称,类别名称, sum(数量*零售价)总金额 from 仓库 a inner join 库存 b on a.仓库 ID=b.仓
3、库 IDinner join 商品信息 c on b.商品 ID=c.商品 ID inner join 商品类别 d on c.类别 ID=d.类别 IDwhere 类别名称 notlike 食品 group by仓库名称,类别名称4、 统计 “商品信息” 中商品名称相同的信息， 要求商品名称的重复次数大于3， 返回数据 商品名称，次数select 商品名称 ,count(商品名称)次数 from 商品信息 group by商品名称 having count(商品名称)35、统计“库存”中汇总库存数量小于100 的商品编码、商品名称、汇总库存数量select 商品编码,商品名称,count (
4、数量)汇总库存数量 from 库存 a inner join 商品信息 bon a.商品 ID=b.商品 ID group by商品编码,商品名称 having count (数量)100实验 11SQL Server 2000的存储过程实验目的掌握使用向导创建存储过程并更新相应数据掌握使用 T-SQL编程的方法掌握使用 T-SQL语句创建一个存储过程并验证掌握创建和执行带参数的存储过程熟练使用系统存储过程、系统函数掌握用在企业管理器中管理存储过程实验要求创建一个不带参数的存储过程。创建一个带参数的存储过程p_count。保存并上交实验结果。实验步骤用 print 语句显示“重庆正大软件职业技
5、术学院软件技术系对日开发专业”写一个程序，计算 9 到 999 的和，SQL 自已查找：如果职工信息表中的平均工龄大于5，就显示“平均工龄大于5” ，否则显示“平均工龄不大于 5” 。 （if-else）在商品类别表中使用 CASE 语句来判断商品的类别，例如类别ID 为 1 则显示食品，如果类别 ID 为 2 则显示副食品。显示存在“大渡口库房”所有的商品名称。1、 创建一个存储过程查看所有商品的商品名称、类型、单价、数量，并调用该过程。2、 创建一个存储过程查看名称为豆浆晶的商品的商品名称、类型、单价、数量。3、 创建一个存储过程查看某个商品的商品名称、类型、单价、数量，并调用该过程。4、
6、 创建一个存储过程求两个数之积，要求用OUTPUT 输出参数。5、 调用该过程计算 20*30 的积，输出20*30 的和为：600的效果。6、 查询 4 题创建存储过程的源代码 。7、 删除 1 题创建的存储过程。8创建一个存储过程，输入整数a，b，计算 a，b 之和1用 print 语句显示“重庆正大软件职业技术学院软件技术系对日开发专业”declare a char(50)set a=重庆正大软件职业技术学院软件技术系对日开发专业print a2写一个程序，计算 9 到 999 的和，SQL 自已查找：declare a int ,sum intset a=9set sum=0while
7、 a5select 平均工龄大于 5elseselect 平均工龄不大于 54在商品类别表中使用 CASE 语句来判断商品的类别，例如类别 ID 为 1 则显示食品，如果类别 ID 为 2 则显示副食品。select 类别 ID, 类别 ID=case when 类别 ID=1 then 食品when 类别 ID=2 then 副食品when 类别 ID=3 then 日常用品when 类别 ID=4 then 粮油类when 类别 ID=5 then 电器类when 类别 ID=6 then 厨具when 类别 ID=7 then 玩具when 类别 ID=8 then 茶类when 类别
8、ID=9 then 文具when 类别 ID=10 then 饮料else 无endfrom 商品类别5显示存在“大渡口库房”所有的商品名称。select 商品名称 from 商品信息 a innerjoin 库存 b on a.商品 ID=b.商品 ID join仓库 c on c.仓库 ID=b.仓库 ID where 仓库名称=大渡口库房1、创建一个存储过程查看所有商品的商品名称、类别名称、进价、数量，并调用该过程。create proc waasselect 商品名称,类别名称,进价,数量 from商品信息 ainner join 库存 b on a.商品 ID=b.商品 ID joi
9、n 商品类别c on a.类别 ID=c.类别 IDexecute wa2、创建一个存储过程查看名称为豆浆晶的商品的商品名称、类型、单价、数量。createproc aaasselect 商品名称,类别名称,进价,数量 from商品信息 ainner join 库存 b on a.商品 ID=b.商品 ID join 商品类别c on a.类别 ID=c.类别 IDwhere 商品名称=豆浆晶execute aa3、创建一个存储过程查看某个商品的商品名称、类型、单价、数量，并调用该过程。createproc abasselect 商品名称,类别名称,进价,数量 from商品信息 ainner
10、join 库存 b on a.商品ID=b.商品 ID join 商品类别c on a.类别 ID=c.类别 ID where 商品名称=豆浆晶dropproc abexecute ab4、创建一个存储过程求两个数之积，要求用OUTPUT 输出参数。create procedure qqa int,b int,avg int outputasset avg=b*adrop proc qq5、调用该过程计算 20*30 的积，输出20*30 的和为：600的效果。declare avg int ,c char(20)set c=20*30 的和为:exec qq 20,30 ,avgoutput
11、select c+convert(char (20) ,avg)6、查询 4 题创建存储过程的源代码 。sp_helptext qq7、删除 1 题创建的存储过程。dropproc wa8创建一个存储过程，输入整数a，b，计算 a，b 之和Create Proc wwwwAsdeclare a int ,b Intset a=1set b=0beginwhile a100set a=a+1set b=a+bendselect bexecute wwww1.检索“职工信息”中的职工 ID(Employee ID)、姓名（Employee Name）2.Select Employee ID=职工
12、ID，Employee Name=姓名 from 职工信息3.检索“商品信息”中商品编码、商品名称、进价、零售价、利润（零售价-进价）Select 商品编码,商品名称,进价,零售价,利润=零售价-进价 from 商品信息4.检索“商品信息”中类别 ID、商品名称，要求类别 ID 和商品名称相同的数据只显示一次Select distinct 类别 ID,商品名称 from 商品信息5.检索“商品信息”进价在 20 和 100 之间的商品编码、商品名称Select 商品编码,商品名称 from 商品信息 where 进价 between 20 and 1006.检索“商品信息”零售价在 2 之下和
13、 100 之上的商品信息Select 商品名称 from 商品信息 where 零售价 1005. 检索“商品信息”进价大于 50 的商品名称，进价，零售价，并按进价从低到高的顺序排列Select 商品名称，进价，零售价 from 商品信息 where 进价50 orderby 进价6. 检索“商品信息”类别 ID=2 或者零售价在 100 和 200 之间的商品的商品 ID、商品编码、商品名称、零售价Select 商品 ID,商品编码 ,商品名称 ,零售价 from 商品信息where 类别 ID=2 or (零售价100and 零售价20检索“职工信息”的年龄最小的 5 名职工的职工 ID
14、、姓名、性别、出生日期Selecttop 5职工 ID,姓名,性别, 出生日期from 职工信息 order by 出生日期检索商品信息中各种商品的销售利润总额在100 和 500 之间的前三名商品的类别名称， 利润总额Select top 3 类别名称,sum(零售价-进价) 利润总额from 商品信息 ainner join 商品类别 b on a. 类别 ID=b. 类别 IDgroup by 类别名称havingsum(零售价-进价) between 100 and 500order by sum(零售价-进价)desc统计“库存”中汇总库存数量大于 1000 的商品编码、商品名称、汇
15、总库存数量Select 商品编码,商品名称,sum(数量) from 库存 a inner join 商品信息 b on a.商品 ID =b. 商品 ID group by 商品编码,商品名称 having sum(数量)1000将“商品信息”中商品名称包含“康明”的商品插入到“库存”中，仓库 id=2,数量=10insert into 库存(商品 ID,仓库 ID,数量)select 商品 id,2,10from 商品信息 where 商品名称 like %康明%无库存商品初始化，将商品信息中存在，库存中不存在的数据插入到库存中，仓库 id=3,数量=100Insert into 库存(商
16、品ID,仓库ID,数量) select商品id,3,100 from商品信息 a left join库存 b ona. 商品 ID=b. 商品 IDwhere a. 商品 ID is null一、使用 T-SQL语句创建数据库 book1、将数据库文件保存在d:book 目录下2、数据文件 book_data，初始大小 20M,增长方式 20%3、日志文件 book_log,初始大小 1M,文件增长无限制二、使用 T-SQL创建表（20 分）1、图书字段名图书 ID图书名数据类型IntChar(8)是否为空Not NullNot Null主键？Pk_bookNo外键约束NoNo缺省值库存数量2
17、、用户字段名用户 id用户名用户地址用户电话3、订单字段名订单编号序号图书 ID用户 id数量单价int数据类型Intchar(20)char(20)char(20)Yes是否为空Not NullNot NullNot NullNot NullNo主键？Pk_userNoNoNoNo外键约束NoNoNoNo缺省值数据类型IntInt (自增长)IntIntIntDecimal(5,2)是否为空Not NullNot NullNullNot NullYesYes主键？Pk_buyPk_buy外键约束Fk_book,参照图书中图书 IDFK_user,参照用户中用户 id三、使用 T-SQL插入数
18、据1、图书图书 ID1111111211132、用户用户 id10011002100310043、订单订单编号112001112001112002用户 id100110011002图书 ID111111121112数量21009单价用户名张三李四王五赵六用户地址重庆市巴南区重庆市大渡口区重庆市巴南区重庆市江北区用户电话1367878549138393963813678785491335858588图书名巴黎圣母院三国演义红与黑库存数量201500800单价45.20180.5029.80112003112004100310041112111456题目向图书表中添加一个字段单价，数据类型Deci
19、mal(5,2)用 t-sql 创建一个视图 uv_book，检索图书中销量最后三名的图书名称和单价查询购买书的种类最多的用户名称和书的种类数4.存储过程名称“UP_book_Insert”功能：将图书添加到订单参数:buyno char(9),user_id int ,num int，price decimal(10,2)业务逻辑判断插入的buyno 在图书是否存在，如果不存在，提示“该图书不存在” 。 退出存储过程判断插入的,user_id 在用户中是否存在，如不存在提示“该用户不存在” 。 退出存储过程判断插入的buyno, user_id 是否能在订单中存在， 如果存在，提示“该图书已
20、经在你的购物车中” ，并将订单中的数量+num；如果不存在，将记录插入到订单中create database bookon(name=book_data,filename=d:bookbook_data.mdf,size=20mb,filegrowth=20%)log on(name=book_log,filename=d:bookbook_log.ldf,size=1mb)create table 图书(图书 ID IntNot Null primary key ,图书名Char(20)NOT NULL,库存数量intnull,)DROP Table 图书create table 用户(用户
21、 idIntNot Null primary key,用户名char(20) Not Null,用户地址char(20) Not Null,用户电话char(20) Not Null,)create table 订单(订单编号 intnot null ,序号Int Not Null,图书 ID intnull,用户 idintnot null,数量Intnull,单价Decimal(5,2) null,constraint Pk_buy primary key (订单编号,序号),constraint FK_book foreign key (图书 ID) references 图书(图书 I
22、D),constraint FK_user foreign key(用户 id) references 用户(用户 id)drop table 订单alter table 图书 dropcolumn 单价alter table 图书 add 单价 decimal(5,2)insert into 图书 (图书 ID,图书名,库存数量,单价) values (1111,巴黎圣母院,20,45.20)insert into 图书 (图书 ID,图书名,库存数量,单价) values (1112,三国演义,1500 ,180.50)insert into 图书 (图书 ID,图书名,库存数量,单价)
23、values (1113,红与黑,800,29.80)insert into 用户 values (1001,张三,重庆市巴南区,1367878549)insert into 用户 values (1002,李四,重庆市大渡口区,1383939638)insert into 用户 values (1003,王五, 重庆市巴南区,1367878549)insert into 用户 values (1004,赵六,重庆市江北区,1335858588)insert into 订单(订单编号,序号, 用户 id,图书 ID,数量) values (112001, 1,1001,1111,2)inser
24、t into 订单(订单编号,序号, 用户 id,图书 ID,数量) values (112001,2,1001,1112,100)insert into 订单(订单编号,序号, 用户 id,图书 ID,数量) values (112002,3,1002,1112,9)insert into 订单(订单编号,序号, 用户 id,图书 ID,数量) values (112003,4,1003,1112,5)insert into 订单(订单编号,序号, 用户 id,图书 ID,数量) values (112004,4,1002,1112,6)2.用 t-sql 创建一个视图 uv_book，检索
25、图书中销量最后三名的图书名称和单价create view uv_bookasselect top 3a.图书名,a.单价,数量 from 图书 a join 订单 b on a.图书 ID=b.图书 ID order by数量3.查询购买书的种类最多的用户名称和书的种类数selecttop 1 用户名,图书 ID from 订单 a join 用户 b on a.用户 id=b.用户 idorder by 图书 ID1.使用 T-SQL语句创建数据库，以自己的名字的拼音命名在 d 盘建立一个以自己名字拼音命名的文件夹， 将建立的数据库文件保存在改文件夹下， 数据文件*_data，初始大小 15
26、M,增长方式 20%日志文件*_log,初始大小 10m,增长方式 50m2.数据库下面建立几张表： （表用 T-SQL的方式创建）课程学生成绩作业学生.学号作业明细create database zhanglijuanon (name= zhanglijuan_data,filename=d:zhanglijuanzhanglijuan_data.mdf,size=15mb,filegrowth=20%)logon (name= zhanglijuan_log,filename=d:zhanglijuanzhanglijuan_data.ldf,size=10mb,filegrowth=50
27、mb)drop database zhanglijuancreate table 课程(课程编号 int primary key,课程名称 nvarchar (50) ,类型 nvarchar (4),学分 smallint ,学期 nvarchar (10)create table 学生(学号 varchar (10) primary key ,姓名 nvarchar (10),性别char (2),联系方式 varchar (20),职务 nvarchar (20)create table 成绩(课程编号 int,学号 varchar (10),成绩decimalconstraint qq
28、 primary key(课程编号,学号)constraint ss foreign key (课程编号) references 课程(课程编号),constraint saforeign key (学号) references 学生(学号),)drop table成绩create table 作业(作业 IDintprimary key ,安排日期 datetime,上交日期 datetime,上交数量smallint,内容nvarchar (4000),平均分 decimal)drop table作业create table 作业明细(作业 IDint,学号 varchar (10),成绩decimalconstraint bb primary key (作业 ID,学号 )constraint ab foreign key (作业 ID) references 作业(作业 ID),constraint ac foreign key (学号) references 学生(学号)drop table 作业明细