上面是图片
--下面是代码
--查询商品信息表
--添加数据
insert Goods
select '从心选择的智慧(李开复)','书籍','18.5' union
select 'Java面向对象编程(孙卫琴)','书籍','52.6' union
select '漫谈中国文化(南怀瑾)','书籍','13.00' union
select '艾美特FSW65R-5落地风扇','生活电器','199.00' union
select '飞利浦HD3035/05电饭煲','生活电器','269.00' union
select '美的FD302电饭煲','生活电器','248.00' union
select '格力KYT-2503台式转页扇','生活电器','88.00' union
select '尤尼克斯Yonex羽毛球拍','体育用品','209.00' union
select 'NIKE篮球BB0361-823','体育品牌','135.00'
--查询客户信息表
--添加数据
insert Customer
select 'abc111', '111', '刘一鸣', '110227198001050134', '北京市朝阳区新源南路14号',' 13901212345'union
select 'abc222', '222', '王华传', '110227198001059876', '北京市东城区香河园路16号',' 01062111234'union
select 'abc333', '333', '张晓静', '110227198001059999', '北京市东城区直门大街2号 ',' 13501229678'union
select 'abc444', '444', '张洪涛', '310104198407062657', '上海市徐汇区漕溪路126号 ',' 13819819999'union
select 'abc555', '555', '王勇强', '310110197507098123', '上海市杨浦区大连路1548 ',' 13671648888'union
select 'abc666', '666', '张鹏飞', '310110619841023426', '上海市静安区大沽路347 ',' 13917067777'union
select 'abc777', '777', '刘亚其', '420102197209022678', '武汉市江岸区洞庭街67 ',' 18674060972'union
select 'abc888', '888', '张允辉', '420101119851191278', '武汉市洪山区关山一路45号',' 18672791254'union
select 'abc999', '999', '王怀东', '420103197909111892', '武汉市江汉区唐家墩路5号 ',' 15926353456'
--删除数据
delete from Customer where CustomerID<10
--查询订单表
select * from Orders
--添加数据
insert Orders
select '2013-04-10','78.60 ',11 ,'2013-04-12' union
select '2013-05-16','702.20 ',14 ,'2013-05-19' union
select '2013-03-18','773.00 ',10 ,'2013-03-19' union
select '2013-04-12','1416.00',12 ,'2013-04-12' union
select '2013-04-10','246.00 ',13 ,'2013-04-12' union
select '2013-05-16','411.00 ',17 ,'2013-05-17' union
select '2013-03-18','509.00 ',16 ,'2013-03-21' union
select '2013-06-19','535.00 ',15 ,'2013-06-20' union
select '2013-04-12','290.00 ',18 ,'2013-04-13' union
select '2013-05-28','484.00 ',14 ,'2013-05-30' union
select '2013-03-08','656.20 ',11 ,'2013-03-09' union
select '2013-03-08','551.50 ',13 ,'2013-03-10' union
select '2013-03-08','431.80 ',11 ,'2013-03-09' union
select '2013-03-18','646.00 ',10 ,'2013-03-19' union
select '2013-04-10','435.00 ',13 ,'2013-04-13'
--查询订单详细表
select * from OrdersDetail
--添加数据
insert OrdersDetail
select 7,8,2,NULL union
select 8,7,3,NULL union
select 8,8,2,NULL union
select 9,7,2,NULL union
select 9,6,3,NULL union
select 10,7,3,NULL union
select 10,5,1,NULL union
select 11,6,2,NULL union
select 11,1,2,NULL union
select 11,7,1,NULL union
select 12,5,2,NULL union
select 12,4,3,NULL union
select 12,5,2,NULL union
select 13,1,3,NULL union
select 13,7,2,NULL union
select 13,5,1,NULL union
select 14,3,2,NULL union
select 14,5,1,NULL union
select 15,4,2,NULL
--查询商品信息表
select * from Goods
(问题一)
--显示客户“张洪涛”所购商品信息,显示商品信息,显示客户名,商品名,商品单价
select *from Customer
select *from Goods
select *from Orders
select *from OrdersDetail
select Customer.CustomerName 客户名称,Goods.GoodsName 商品名,Goods.UnitPrice 商品单价
from Customer, Goods ,OrdersDetail,Orders
where Customer.CustomerID=Orders.CustomerID
and Goods.GoodsID=OrdersDetail.GoodsID
and Customer.CustomerName='张洪涛'
group by Customer.CustomerName ,Goods.GoodsName ,Goods.UnitPrice
(问题二)
--显示客户“张洪涛”所购不同种类的商品及其件数,显示商品名和件数
select Customer.CustomerName 客户名称,Goods.GoodsName 商品名,Goods.UnitPrice 商品单价 ,count(*)商品数
from Customer, Goods ,OrdersDetail,Orders
where Customer.CustomerName='张洪涛'
and Goods.GoodsID=OrdersDetail.GoodsID
and Customer.CustomerID=Orders.CustomerID
group by Customer.CustomerName ,Goods.GoodsName ,Goods.UnitPrice