实验7SQL练习

《实验7SQL练习》由会员分享，可在线阅读，更多相关《实验7SQL练习（15页珍藏版）》请在装配图网上搜索。

1、实验7 SQL练习一、实验目的：练习SQL数据更新语句。二、实验内容：1、 视图2、 SQL数据更新语言练习三、表结构描述3.1 Suppliers(供货厂商)代码描述数据类型长度约束条件SupplierID供货厂商编号INT4主码CompanyName厂名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人职位VARCHAR30Address地址VARCHAR60City城市名VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国家VARCHAR15Phone电话VARCHAR24Fax传

2、真VARCHAR24HomePage主页VARCHAR163.2 Region（地区）代码描述数据类型长度约束条件RegionID地区编号INT4主码RegionDescription地区描述VARCHAR503.3 Products（产品）代码描述数据类型长度约束条件ProductID产品编号INT4主码ProductName品名VARCHAR40SupplierID供货厂商编号INT4CategoryID所属种类号INT4QuantityPerUnit单位数量VARCHAR20UnitPrice单价FLOAT8UnitsInStock库存INT2UnitsOnOrder定货数INT2Reo

3、rderLevel修订量INT2Discontinued是否进行BIT13.4 Orders（定单）代码描述数据类型长度约束条件OrderID定单编号INT4主码CustomerID顾客编号VARCHAR5EmployeeID职员编号INT4OrderDate定货日期DATETIME8RequiredDate交货日期DATETIME8ShippedDate载运日期DATETIME8ShipVia经由数INT4Freight运费FLOAT8ShipName船名VARCHAR40ShipAddress地址VARCHAR60ShipCity城市VARCHAR15ShipRegion地区VARCHAR

4、15PostalCode邮政编码VARCHAR10ShipCountry国籍VARCHAR153.5 Order Details（定单详细信息）代码描述数据类型长度约束条件OrderID定单编号INT4主码ProductID产品编号INT4主码UnitPrice单价FLOAT8Quantity数量INT2Discount折扣FLOAT43.6 Employees（职工）代码描述数据类型长度约束条件EmployeeID职工编号INT4主码LastName姓VARCHAR20FirstName名VARCHAR10Title头衔VARCHAR30TitleOfCourtesy性别VARCHAR25B

5、irthDate生日DATETIME8HireDate受聘日期DATETIME8Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15HomePhone住宅电话VARCHAR24Extension分机号VARCHAR4Photo照片IMAGE16Notes备注VARCHAR16ReportsTo直接上级号INT4Photopath职工照片路径VARCHAR2553.7 Customers（顾客）代码描述数据类型长度约束条件CustomerID顾客编号VARCHAR5主

6、码CompanyName公司名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人头衔VARCHAR30Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR243.8 OldSuppliers（供应厂商备份表）结构与Suppliers表相同四、实验步骤1、 运行SQL SERVER服务管理器，确认数据库服务器开始运行。2、 运行企业管理器，以图示方式点击“附加数据库”

7、，恢复db目录下的数据库文件3、 打开查询分析器，选择刚才恢复的数据库exampleDB，输入SQL指令，获得运行结果。4、 完成以下SQL数据更新语句(1) 创建视图V_SupplyCount,显示供应商编号，以及该供应商供应的产品的品种数(非CategoryID)，该视图包含两个字段：SupplierID、ProductCount。create view V_SupplyCount(SupplierID,ProductCount)asselect SupplierID,count(ProductID)from productsgroup by SupplierID(2) 创建视图V_Ord

8、erCount，显示顾客编号，顾客所下订单的产品总金额(金额=单价数量折扣)，该视图包含两个字段：CustomerID，TotalFee。create view V_OrderCount(CustomerID, TotalFee)asselect CustomerID,sum(UnitPrice*Quantity*Discount)from Orders,OrderDetails where Orders.OrderID=OrderDetails.OrderID group by CustomerID(3) 通过视图V_SupplyCount，查出供应产品的品种数最少的供应商编号和供应商名称。

9、select SupplierID,CompanyNamefrom Supplierswhere SupplierID in(select SupplierIDfrom V_SupplyCountwhere ProductCount=(select min(ProductCount)from V_SupplyCount) (4) 通过视图V_OrderCount，查出所下订单的产品总金额最多的顾客编号和地址。select CustomerID,Address from Customers where CustomerID in(select CustomerID from V_OrderCou

10、nt where TotalFee=(select max(TotalFee) from V_OrderCount)(5) 再Region表中，添加一个新的地区：地区编号为5，地区描述为Centralinsertinto Regionvalues (5,Central)(6) 将Suppliers表中的所有国家为法国的供应商信息内容添加到OldSuppliers中create table OldSuppliers( SupplierID int NOT NULL primary key, CompanyName varchar(50) , ContactName varchar(50) , C

11、ontactTitle varchar(50) , Address varchar(60) , City varchar(50) , Region varchar(50) , PostalCode varchar(50) , Country varchar(50) , Phone varchar(50) , Fax varchar(50) , HomePage varchar(50) )insertinto OldSuppliersselect * from Suppliers where Country=France(7) 将所有由职员Fuller(LastName)签订的订单运费降低10%

12、update Ordersset Freight=Freight*0.9where EmployeeID=(select EmployeeID from Employees where LastName=Fuller)(8) 将所有美国顾客购买的订单单价调高20%update Orderdetailsset UnitPrice=UnitPrice*1.2where OrderID in (select distinct Orderdetails.OrderID from Orderdetails,Orders,Customers where Orderdetails.OrderID=Order

13、s.OrderID and Orders.CustomerID=Customers.CustomerID and Customers.Country=USA)(9) 将订货数量最多的产品的单价上调5元update Productsset UnitPrice=UnitPrice+5where ProductID in (select ProductID from Orderdetails group by ProductID having sum(Quantity) =ALL (select sum(Quantity) from Orderdetails group by ProductID)(

14、10) 删除订单个数最少的职员的信息delete from Employeeswhere EmployeeID in (select EmployeeID from Orders group by EmployeeID having count(OrderID) =ALL (select count(OrderID) from Orders group by EmployeeID)(11) 删除所有没有下订单的顾客信息delete from Customerswhere not exists (select * from Orders where Customers.CustomerID=Orders.CustomerID)