2．使用UPDATE命令修改基本表中的数据
（1）将编号为‘F70006’的商品的单价改为12.00元
update commodity set avgprice=12.00 where cid=’ F70006’
（2）将名称为‘家乐福’的客户的等级改为VIP客户。
update customer
set culevel=2
where cuname=’家乐福’
（3）将编号为‘A102’ 供应商供应商品的价格全部打八折
update supplying
set price=price 0.8
where sid=’A102’
3．使用DELETE命令删除基本表中的数据
（1）删除采购明细表中的所有记录
delect from dbo.stockDetail
（2）从采购单表中删除2005年以前签订的采购单
delect from dbo.stock where year(cgdate)<’2005’
4．使用SELECT…INTO语句向表中成批插入记录
（1）将供应商‘A102’供应的商品和供应价格存到一个名字为‘A102_commodity’的表中
SELECT cgid,eid,cgmoney,cgdate
INTO A102_commodity
from stock where sid=’A102’
（2）将所有的VIP客户信息存储到一个名字为‘VIP_Customer’的表中
select cuid,cuname,cutelephone
into VIP_Customer
from customer
where culevel=2
5．使用INSERT…SELECT语句向表中成批插入记录
（1）新建一个名称为‘achievement’的表，其属性包括销售单ID，销售单总金额，销售日期
CREATE TABLE achievement(
SAid char(12) PRIMARY KEY, –**销售单**ID
SAmoney float, –**销售金额**
SAdate DateTIme, –**签订日期**
)
（2）将编号为‘S0002’的销售员签订的销售单信息存储到achievement表中。
insert
into achievement(SAid,SAmoney,SAdate)
select SAid,SAmoney,SAdate from sale where eid=’S0002’
三、自我测试
1．从采购明细表中删除所有从供应商‘A153’处采购的编号为‘F70006’商品的明细信息
DELETE from stockdetail where cgid in
(select cgid from stock where sid=’A153’) and cid=’F70006’
2．从销售明细表中删除2005年以前签订的销售单的销售明细记录
delete from saledetail where said in
(select said from sale where year(sadate)<’2005’)
实验三 单表查询
一、实验目的与要求
1．掌握基本的SELECT…FROM语句的用法
2．能应用SELECT语句及其子句解决常见的查询问题
3．掌握模糊查询的使用方法
4．能应用聚集函数和GROUP BY子句进行统计查询
二、实验内容
1． 查找所有商品的名称和库存信息
select cname,cstorage from commodity
2． 查找‘北京’地区的供应商
select * from supplier where sarea = ‘**北京’**
3． 查找所有销售人员的基本信息
Select * from employee where etype=3
4． 查找签订日期在‘2005-5-1’和‘2006-3-1’之间签订的采购单的基本信息
select * from stock where cgdate>’2005-5-1’ and cgdate<’2006-3-1’
5． 查找2006年签订的所有销售单，要求显示销售单ID、客户ID和销售单总金额
select said,cuid,samoney from sale where year(sadate)= ‘2006’
6． 查找每个采购员所签订的采购单的数目，显示结果按采购单数目降序排序
select eid,count(cgid) myc from stock group by eid order by myc desc
7． 查询每个销售员签订的销售单总金额
select eid,sum(samoney) from sale group by eid
8． 查找2005年与每个客户签订的销售单的总金额，只显示总金额大于500000的客户ID和总金额
select cuid,samoney from sale where samoney>500000 and year(sadate)=2005
9． 查询2005年签订的金额最大的一笔销售单，要求显示销售员ID、客户ID和销售金额
select top 1 eid,cuid,samoney from sale where year(sadate)=2005
10． 查询2005年与编号为‘A002’的供应商签订的所有采购单的总金额
select sum(cgmoney) from stock where year(cgdate)=2005 and sid=’ A002’
11． 查询名称中含有‘机’字的所有商品的ID、名称和库存量
select cid,cname,cstorage from commodity where cname like ‘%**机%’**
12． 查找第二个汉字是‘翔’的商品名称和单价。
select cname,avgprice from commodity where cname like ‘_**翔%’**
三、自我测试
1．查询2005年签订的销售合同总额排名前5名的销售员的ID和合同总额，要求结果按照合同总额降序排列
select top 5 eid,samoney from sale order by samoney desc
2．查询至少供应了3种商品的供应商的ID
select sid from supplying group by sid having count(*)>=3
3．查询在2006年至少已经与2名销售员签订了合同的客户ID
select cuid,count(distinct eid)
from sale where year(sadate)=2006 group by cuid having count(distinct eid)>=2
实验四 表连接查询
一、实验目的与要求
1．掌握表连接查询的使用方法
2．掌握子查询的使用方法
3．能使用内连接、左外连接、右外连接以及交叉连接解决相关问题
4．熟悉系统函数的使用
二、实验内容
1．查找每一个供应商供应的商品的名称，要求显示供应商名称和商品名称。
select sname,cname
from supplying sg,commodity c,supplier sr
where sg.cid=c.cid and sr.sid=sg.sid
2．使用连接查询查找与姓名为‘李云’的采购员签订采购单的供应商的名称。
select distinct sname
from stock s,supplier sr,employee e
where s.sid=sr.sid and e.eid=s.eid and ename=’**李云’**
3．使用关键字IN完成查询，要求查找与姓名为‘李云’的采购员签订采购单的供应商的名称。
select sname from supplier where sid in(
select sid from stock sk,employee e where sk.eid=e.eid and e.ename=’**李云’)**
4．使用左外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息。
select * from dbo.Employee e left join dbo.stock s on e.eid=s.eid
5．使用左外连接完成如下查询：查找所有客户购买的商品详细信息，要求显示客户名称，
商品名称，销售数量，商品单价，没有购买商品的客户也要显示。
select cuname,cname,sdnumber,sdprice from dbo.customer cu
left join dbo.sale s on cu.cuid=s.cuid
left join dbo.saleDetail sd on sd.said=s.said
left join commodity c on sd.cid=c.cid
6．请使用内连接完成如下查询：查找每一个供应商供应的商品的种类，要求显示供应商名称，供应的商品的种类。
select sname,count(cname) from dbo.supplier s
inner join dbo.supplying sp on s.sid=sp.sid
inner join dbo.commodity c on sp.cid=c.cid
group by sname
7．查找购买了编号为‘A001’的供应商供应的商品的客户名称
select cuname from customer cu,sale s,saledetail sd,supplying sp,supplier sr
Where cu.cuid=s.cuid and s.said=sd.said and sd.cid=sp.cid
and sr.sid=sp.sid and sp.sid=’ A001’
8．查找销售员‘王良’在2005年签订的销售合同的详细信息。
select sd.*,s.* from saledetail sd,sale s,employee e
where sd.said=s.said and e.eid=s.eid
三、自我测试
1．使用右外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息，没有签订采购单的采购员也要显示
select st.*,std.*,e.* from stockdetail std right join stock st on std.cgid=st.cgid
right join employee e on e.eid= st.eid
2．查找购买了名称为‘联想集团’的供应商供应的商品的客户名称
select distinct cuname
from customer cu,sale s,saledetail sd,commodity c,supplying sp,supplier sr
Where cu.cuid=s.cuid and s.said=sd.said and sd.cid=c.cid and sp.cid=c.cid and sp.sid=sr.sid and sr.sname=’**联想集团’**
实验五 表综合查询使用
一、实验目的与要求
1．能熟练应用SELECT语句及其相关字句
2．能将SELECT与系统函数、IF ELSE、WHILE等语句进行综合应用并解决相关问题
二、实验内容
1．查询所有供应‘鲜橙多’的供应商的最低报价。
select min(price) from supplying
where cid in(select cid from commodity where cname=’**鲜橙多’)**
2．查找销售员‘王良’在2005年签订的所有销售合同中每一类商品的总金额。
select cid,sum(sdprice*sdnumber)
from saledetail sd,sale sa
where sd.said=sa.said and year(sadate)=2005
and eid in(select eid from employee where ename=’**王良**’)
group by cid
3．汇总由姓名为‘刘明’的采购员在2005年采购的‘数码相机’的总金额。
select sum(sdnumber*sdprice)
from stockdetail sd,stock s
where sd.cgid=s.cgid and eid =(select eid from employee where ename=’**刘明’)**
and cid=(select cid from commodity where cname=’**数码相机’)**
1． 汇总由姓名为‘刘明’的采购员在2005年采购的各类商品的数量。
select cid,sum(sdnumber)
from dbo.stockDetail
where cgid in(select cgid from stock where year(cgdate)=2005
and eid=(select eid from employee where ename=’**刘明’)**
)
group by cid
2． 查找没有供应任何一类商品的供应商的名字。
select sname from supplier where sid not in(select sid from supplying)
3． 查找在2006年各个客户购买商品的总金额，要求结果按照购买商品的总金额降序排序
select cuid,sum(samoney) from sale where year(sadate)=2006 group by cuid
4． 查找编号为‘A002’的供应商没有供应的商品的名称
select cname from commodity
where cid not in(select cid from supplying where sid=’A002’)
5． 查询由‘三高计算机公司’和‘联想集团’供应商的所有商品的商品名称。
select cname from commodity where cid in(
select cid from supplying where sid in(
select sid from supplier where sname in(‘**三高计算机公司’,’联想集团’)))**
三、自我测试
\1. 请使用左连接完成以下查询：查找每一个销售人员销售的商品的详细信息，要求显示销售人员姓名、销售单ID、客户姓名、商品名称、销售数量、和销售单价。
select ename,s.said,cuname,sdnumber,sdprice from employee e
left join sale s on s.eid = e.eid
left join saledetail sd on s.said=sd.said
left join customer cu on s.cuid=cu.cuid
left join commodity c on sd.cid=c.cid
where e.etype=3
\2. 查找每个采购员和每个供应商签订的合同的总金额，要求显示采购员姓名、供应商名称、和签订合同的总金额。（可以假设采购员姓名唯一，供应商名称唯一）
select ename,sname,sum(cgmoney) from stock st,employee e,supplier sr
Where e.eid=st.eid and sr.sid=st.sid
Group by ename,sname
实验六 视图与索引
一、实验目的与要求
1．熟悉视图的概念，以及视图与表的联系和区别
2．掌握视图的定义方法
3．掌握对视图的查询操作
1． 掌握对视图的更新操作，并了解视图的更新条件
2． 掌握同时对视图和表进行操作的方法
3． 熟悉索引的概念与作用
4． 掌握索引的建立与删除操作
二、实验内容
1．定义一个名称为‘supplying_view’的视图，要求其对应的数据是由‘天天食品公司’供应的商品ID、商品单价。
create view supplying_view as
select cid,price
from supplying
where sid = (select sid from supplier where sname=’**天天食品公司’)**
2．查询采购员‘李云’和供应商‘天天食品公司’签订的所有采购单的采购单ID、采购金额、签订日期，并将其建立为一个名称为‘stock_view_1’的视图。
create view stock_view_1 as
select st.cgid,cgmoney,cgdate from stock st
where eid =(select eid from employee where ename=’**李云’)**
and sid =(select sid from supplier where sname=’**天天食品公司’)**
3．建立一个名称为‘commodity_100’的视图，其对应所有的库存量小于100的商品的信息。
create view commodity_100 as
select * from commodity where cstorage<100
4．使用视图supplying_view和相关基本表查找由供应商‘天天食品公司’供应的商品ID、商品名称和商品单价。
select sv.*,cname from supplying_view sv,commodity c where sv.cid=c.cid
5．使用视图‘stock_view_1’和相关基本表查询‘李云’和供应商‘天天食品公司’签订的所有采购单的采购明细信息，要求显示采购单ID、商品ID、商品名称、采购数量、和商品单价。
select sv1.cgid,c.cid,cname,sdnumber,avgprice
from stock_view_1 sv1,commodity c,stockdetail std
where sv1.cgid=std.cgid and c.cid=std.cid
6．为supermarket数据库中的任意一个表建立索引（按升序建唯一索引号）。
create index id
on commodity(cid asc)
7．使用DROP INDEX语句删除新建的索引
drop index id
三、自我测试
1．建立一个视图，其对应的数据是在2006年签订的销售单总额超过100万的销售员的I D和姓名。
Create view myview as
Select s.**eid,ename from sale s,employee e**
where year(SAdate)=2006 and s.eid=e.eid
2．使用上述建立的视图和NOT IN关键字查询在2006年签订的销售单总额没有超过100万的销售员的I D和姓名。
Select s.eid,ename from sale s,employee e
where s.eid=e.eid and year(SAdate)=2006 and s.eid not in(select eid from myview)
