SQL查询语句习题

1. 检索“商品信息”类别名称=玩具并且零售价在100和300之间的商品的平均零售价，最大零售价，最小零售价，以及零售价之和Select 类别名称,零售价, min(零售价)最小零售价, max(零售价)最大零售价,avg(零售价)平均零售价,sum(零售价)零售价之和 from 商品信息 a inner join 商品类别 b on a.类别ID=b.类别ID where 类别名称=玩具 group by 类别名称,零售价 2.检索“商品入出库”中审核日期大于2009年3月1日，并且入库类型编号为“采购入库”的商品的总金额（入出库数量*进价）返回数据：入出库编号、总金额(商品入出库.入库类型编号=入出库类型.入库类型编号) select a.入出库编号,类型名称,总金额=入出库数量*进价 from 商品入出库 a inner join 入出库类型 b on a.入出库类型编号=b.入出库类型编号inner join 入出库明细 c on c.入出库编号 = a.入出库编号 where 审核日期 2009-03-01 and 类型名称=采购入库3. 统计每个仓库中库存的除去食品之外的总金额 (【仓库】.仓库ID=【库存.仓库IDselect 仓库名称,类别名称, sum(数量*零售价)总金额 from 仓库 a inner join 库存 b on a.仓库ID=b.仓库IDinner join 商品信息 c on b.商品ID=c.商品ID inner join 商品类别 d on c.类别ID=d.类别ID where 类别名称 not like 食品 group by 仓库名称,类别名称4、统计“商品信息”中商品名称相同的信息，要求商品名称的重复次数大于3，返回数据 商品名称，次数select 商品名称 ,count(商品名称)次数 from 商品信息 group by 商品名称 having count(商品名称)35、统计“库存”中汇总库存数量小于100的商品编码、商品名称、汇总库存数量select 商品编码,商品名称,count (数量)汇总库存数量 from 库存 a inner join 商品信息 b on a.商品ID=b.商品ID group by 商品编码,商品名称 having count (数量)100实验11 SQL Server 2000的存储过程实验目的掌握使用向导创建存储过程并更新相应数据掌握使用T-SQL编程的方法掌握使用T-SQL语句创建一个存储过程并验证掌握创建和执行带参数的存储过程熟练使用系统存储过程、系统函数掌握用在企业管理器中管理存储过程实验要求创建一个不带参数的存储过程。创建一个带参数的存储过程p_count。保存并上交实验结果。实验步骤用print语句显示“重庆正大软件职业技术学院软件技术系对日开发专业”写一个程序，计算9到999的和，SQL自已查找：如果职工信息表中的平均工龄大于5，就显示“平均工龄大于5”，否则显示“平均工龄不大于5”。（if-else）在商品类别表中使用CASE语句来判断商品的类别，例如类别ID为1则显示食品，如果类别ID为2则显示副食品。显示存在“大渡口库房”所有的商品名称。1、创建一个存储过程查看所有商品的商品名称、类型、单价、数量，并调用该过程。2、创建一个存储过程查看名称为豆浆晶的商品的商品名称、类型、单价、数量。3、创建一个存储过程查看某个商品的商品名称、类型、单价、数量，并调用该过程。4、创建一个存储过程求两个数之积，要求用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 a5 select 平均工龄大于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 类别ID=9 then 文具 when 类别ID=10 then 饮料 else 无end from 商品类别5显示存在“大渡口库房”所有的商品名称。select 商品名称 from 商品信息 a inner join 库存 b on a.商品ID=b.商品ID join 仓库 c on c.仓库ID=b.仓库ID where 仓库名称=大渡口库房1、创建一个存储过程查看所有商品的商品名称、类别名称、进价、数量，并调用该过程。create proc waasselect 商品名称,类别名称,进价,数量 from 商品信息 a inner join 库存 b on a.商品ID=b.商品ID join 商品类别c on a.类别ID=c.类别ID execute wa2、创建一个存储过程查看名称为豆浆晶的商品的商品名称、类型、单价、数量。create proc aaas select 商品名称,类别名称,进价,数量 from 商品信息 a inner join 库存 b on a.商品ID=b.商品ID join 商品类别c on a.类别ID=c.类别ID where 商品名称=豆浆晶execute aa3、创建一个存储过程查看某个商品的商品名称、类型、单价、数量，并调用该过程。create proc abas select 商品名称,类别名称,进价,数量 from 商品信息 a inner join 库存 b on a.商品ID=b.商品ID join 商品类别c on a.类别ID=c.类别ID where 商品名称=豆浆晶drop proc 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 ,avg outputselect c+convert(char (20) ,avg)6、查询4题创建存储过程的源代码 。sp_helptext qq7、删除1题创建的存储过程。drop proc wa8创建一个存储过程，输入整数a，b，计算a，b之和Create Proc wwwwAsdeclare a int ,b Intset a=1set b=0beginwhile a100set a=a+1set b=a+bend select bexecute wwww1. 检索“职工信息”中的职工ID(Employee ID)、姓名（Employee Name）2.Select Employee ID=职工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之下和100之上的商品信息Select商品名称 from商品信息 where 零售价 1005. 检索“商品信息”进价大于50的商品名称，进价，零售价，并按进价从低到高的顺序排列Select 商品名称，进价，零售价 from商品信息 where进价50 order by进价6. 检索“商品信息”类别ID=2或者零售价在100和200之间的商品的商品ID、商品编码、商品名称、零售价Select商品ID,商品编码,商品名称,零售价 from商品信息 where类别 ID=2 or (零售价100and 零售价20检索“职工信息”的年龄最小的5名职工的职工ID、姓名、性别、出生日期Select top 5 职工ID,姓名,性别, 出生日期 from 职工信息 order by出生日期检索商品信息中各种商品的销售利润总额在100和500之间的前三名商品的类别名称，利润总额Select top 3 类别名称,sum(零售价-进价) 利润总额from 商品信息 a inner join 商品类别 b on a. 类别ID=b. 类别ID group by 类别名称having sum(零售价-进价) between 100 and 500 order by sum(零售价-进价) desc统计“库存”中汇总库存数量大于1000的商品编码、商品名称、汇总库存数量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,10 from 商品信息 where 商品名称 like %康明%无库存商品初始化，将商品信息中存在，库存中不存在的数据插入到库存中，仓库id=3,数量=100Insert into 库存(商品ID,仓库ID,数量) select商品id,3,100 from商品信息 a left join库存 b on a. 商品ID=b. 商品ID where a. 商品ID is null一、使用T-SQL语句创建数据库book1、将数据库文件保存在d:book目录下2、数据文件book_data，初始大小20M,增长方式20%3、日志文件book_log,初始大小1M,文件增长无限制二、使用T-SQL创建表（20分）1、图书字段名数据类型是否为空主键？外键约束缺省值图书IDIntNot NullPk_bookNo图书名Char(8)Not NullNoNo库存数量intYesNoNo2、用户字段名数据类型是否为空主键？外键约束缺省值用户idInt Not NullPk_userNo用户名char(20)Not NullNoNo用户地址char(20)Not NullNoNo用户电话char(20)Not NullNoNo3、订单字段名数据类型是否为空主键？外键约束 订单编号IntNot NullPk_buy序号Int (自增长)Not NullPk_buy图书IDInt NullFk_book,参照图书中图书ID用户idInt Not NullFK_user,参照用户中用户id数量IntYes单价Decimal(5,2)Yes三、使用T-SQL插入数据1、图书图书ID图书名库存数量单价1111巴黎圣母院2045.201112三国演义1500180.501113红与黑80029.802、用户用户id用户名用户地址用户电话1001张三重庆市巴南区13678785491002李四重庆市大渡口区13839396381003王五重庆市巴南区13678785491004赵六重庆市江北订单订单编号用户id图书ID数量单价11200110011111211200110011112100112002100211129112003100311125112004100411146题目向图书表中添加一个字段单价，数据类型Decimal(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是否能在订单中存在，如果存在，提示“该图书已经在你的购物车中”，并将订单中的数量+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 图书(图书IDIntNot Null primary key ,图书名Char(20)NOT NULL,库存数量intnull,)DROP Table 图书create table 用户(用户idInt Not Null primary key,用户名char(20)Not Null,用户地址char(20)Not Null,用户电话char(20)Not Null,)create table 订单(订单编号 int not null ,序号Int Not Null,图书IDint null,用户idint not null,数量Intnull,单价Decimal(5,2)null,constraint Pk_buy primary key (订单编号,序号),constraint FK_book foreign key (图书ID) references 图书(图书ID),constraint FK_user foreign key(用户id) references 用户(用户id)drop table 订单alter table 图书 drop column 单价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,图书名,库存数量,单价) 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)insert 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，检索图书中销量最后三名的图书名称和单价create view uv_bookasselect top 3 a.图书名,a.单价,数量 from 图书 a join 订单 b on a.图书ID=b.图书ID order by 数量 3.查询购买书的种类最多的用户名称和书的种类数select top 1 用户名,图书ID from 订单 a join 用户 b on a.用户id=b.用户id order by 图书ID1.使用T-SQL语句创建数据库，以自己的名字的拼音命名在d盘建立一个以自己名字拼音命名的文件夹，将建立的数据库文件保存在改文件夹下，数据文件*_data，初始大小15M,增长方式20%日志文件*_log,初始大小10m,增长方式50m2.数据库下面建立几张表：（表用T-SQL的方式创建）课程学生成绩作业学生.学号作业明细create database zhanglijuanon (name= zhanglijuan_data,filename=d:zhanglijuanzhanglijuan_data.mdf,size=15mb,filegrowth=20%)log on (name= zhanglijuan_log,file