实验7--SQL练习

实验7SQL练习一、实验目的：练习SQL数据更新语句。二、实验内容：1、视图2、SQL数据更新语言练习三、表结构描述3.1Suppliers(供货厂商)代码描述数据类型长度约束条件SupplierID供货厂商编号INT4主码CompanyName厂名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人职位VARCHAR30Address地址VARCHAR60City城市名VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国家VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR24HomePage主页VARCHAR163.2Region（地区）代码描述数据类型长度约束条件RegionID地区编号INT4主码RegionDescription地区描述VARCHAR503.3Products（产品）代码描述数据类型长度约束条件ProductID产品编号INT4主码ProductName品名VARCHAR40SupplierID供货厂商编号INT4CategoryID所属种类号INT4QuantityPerUnit单位数量VARCHAR20UnitPrice单价FLOAT8UnitsInStock库存INT2UnitsOnOrder定货数INT2ReorderLevel修订量INT2Discontinued是否进行BIT13.4Orders（定单）代码描述数据类型长度约束条件OrderID定单编号INT4主码CustomerID顾客编号VARCHAR5EmployeeID职员编号INT4OrderDate定货日期DATETIME8RequiredDate交货日期DATETIME8ShippedDate载运日期DATETIME8ShipVia经由数INT4Freight运费FLOAT8ShipName船名VARCHAR40ShipAddress地址VARCHAR60ShipCity城市VARCHAR15ShipRegion地区VARCHAR15PostalCode邮政编码VARCHAR10ShipCountry国籍VARCHAR153.5OrderDetails（定单详细信息）代码描述数据类型长度约束条件OrderID定单编号INT4主码ProductID产品编号INT4主码UnitPrice单价FLOAT8Quantity数量INT2Discount折扣FLOAT43.6Employees（职工）代码描述数据类型长度约束条件EmployeeID职工编号INT4主码LastName姓VARCHAR20FirstName名VARCHAR10Title头衔VARCHAR30TitleOfCourtesy性别VARCHAR25BirthDate生日DATETIME8HireDate受聘日期DATETIME8Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15HomePhone住宅电话VARCHAR24Extension分机号VARCHAR4Photo照片IMAGE16Notes备注VARCHAR16ReportsTo直接上级号INT4Photopath职工照片路径VARCHAR2553.7Customers（顾客）代码描述数据类型长度约束条件CustomerID顾客编号VARCHAR5主码CompanyName公司名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人头衔VARCHAR30Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR243.8OldSuppliers（供应厂商备份表）结构与Suppliers表相同四、实验步骤1、运行SQLSERVER服务管理器，确认数据库服务器开始运行。2、运行企业管理器，以图示方式点击“附加数据库”，恢复db目录下的数据库文件3、打开查询分析器，选择刚才恢复的数据库exampleDB，输入SQL指令，获得运行结果。4、完成以下SQL数据更新语句(1)创建视图V_SupplyCount,显示供应商编号，以及该供应商供应的产品的品种数(非CategoryID)，该视图包含两个字段：SupplierID、ProductCount。createviewV_SupplyCount(SupplierID,ProductCount)asselectSupplierID,count(ProductID)fromproductsgroupbySupplierID(2)创建视图V_OrderCount，显示顾客编号，顾客所下订单的产品总金额(金额=单价×数量×折扣)，该视图包含两个字段：CustomerID，TotalFee。createviewV_OrderCount(CustomerID,TotalFee)asselectCustomerID,sum(UnitPrice*Quantity*Discount)fromOrders,OrderDetailswhereOrders.OrderID=OrderDetails.OrderIDgroupbyCustomerID(3)通过视图V_SupplyCount，查出供应产品的品种数最少的供应商编号和供应商名称。selectSupplierID,CompanyNamefromSupplierswhereSupplierIDin(selectSupplierIDfromV_SupplyCountwhereProductCount=(selectmin(ProductCount)fromV_SupplyCount))(4)通过视图V_OrderCount，查出所下订单的产品总金额最多的顾客编号和地址。selectCustomerID,AddressfromCustomerswhereCustomerIDin(selectCustomerIDfromV_OrderCountwhereTotalFee=(selectmax(TotalFee)fromV_OrderCount))(5)再Region表中，添加一个新的地区：地区编号为5，地区描述为CentralinsertintoRegionvalues(5,’Central’)(6)将Suppliers表中的所有国家为法国的供应商信息内容添加到OldSuppliers中createtableOldSuppliers(SupplierIDintNOTNULLprimarykey,CompanyNamevarchar(50),ContactNamevarchar(50),ContactTitlevarchar(50),Addressvarchar(60),Cityvarchar(50),Regionvarchar(50),PostalCodevarchar(50),Countryvarchar(50),Phonevarchar(50),Faxvarchar(50),HomePagevarchar(50))insertintoOldSuppliersselect*fromSupplierswhereCountry='France'(7)将所有由职员Fuller(LastName)签订的订单运费降低10%updateOrderssetFreight=Freight*0.9whereEmployeeID=(selectEmployeeIDfromEmployeeswhereLastName='Fuller')(8)将所有美国顾客购买的订单单价调高20%updateOrderdetailssetUnitPrice=UnitPrice*1.2whereOrderIDin(selectdistinctOrderdetails.OrderIDfromOrderdetails,Orders,CustomerswhereOrderdetails.OrderID=Orders.OrderIDandOrders.CustomerID=Customers.CustomerIDandCustomers.Country='USA')(9)将订货数量最多的产品的单价上调5元updateProductssetUnitPrice=UnitPrice+5whereProductIDin(selectProductIDfromOrderdetailsgroupbyProductIDhavingsum(Quantity)=ALL(selectsum(Quantity)fromOrderdetailsgroupbyProductID))(10)删除订单个数最少的职员的信息deletefromEmployeeswhereEmployeeIDin(selectEmployeeIDfromOrdersgroupbyEmployeeIDhavingcount(OrderID)=ALL(selectcount(OrderID)fromOrdersgroupbyEmployeeID))(11)删除所有没有下订单的顾客信息deletefromCustomerswherenotexists(select*fromOrderswhereCustomers.CustomerID=Orders.CustomerID)