实验6SQL练习

实验6SQL练习一、实验目的：熟悉SQLSERVER数据库的恢复技术。练习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查询语句用不相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数；用相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数；用连接查询，查出公司名首字母为’L’的顾客所下订单的产品总数；用带有ANY或ALL谓词的子查询，找出所签订的订单运费最高的顾客编号，和公司名；用带有聚集函数的子查询，找出所签订的订单运费最高的顾客编号，和公司名；用相关子查询，找出没有下过订单的客户编号和公司名；用集合查询，找出单价小于20的及库存大于50的产品编号、产品名称，要求保留重复的元组；用集合查询，找出订货数量大于0的，及折扣大于0的产品编号，要求去除重复的元组；找出在同一个地区，且存在订货关系的客户编号，公司名称和供应商编号，供应商名称；找出完成定单数量最多的职工姓、名、编号；四、作业上交1写出相应的SQL2截图3上传BB平台用不相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数；selectsum(Quantity)fromOrderDetailswhereOrderIDin(selectOrderIDfromOrderswhereCustomerIDin(selectCustomerIDfromCustomerswhereCompanyNamelike'L%'))用相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数；selectsum(Quantity)fromOrderDetailswhereexists(select*fromOrderswhereOrders.OrderID=OrderDetails.OrderIDandexists(select*fromCustomerswhereCustomers.CustomerID=Orders.CustomerIDandCompanyNamelike'L%'))用连接查询，查出公司名首字母为’L’的顾客所下订单的产品总数；selectsum(Quantity)fromOrderDetailsLEFTOUTERJOIN(OrdersLEFTOUTERJOINCustomersON(Orders.CustomerID=Customers.CustomerID))ON(OrderDetails.OrderID=Orders.OrderID)whereCustomers.CompanyNamelike'L%'用带有ANY或ALL谓词的子查询，找出所签订的订单运费最高的顾客编号，和公司名；selectCustomers.CustomerID,CompanyNamefromOrders,CustomerswhereOrders.CustomerID=Customers.CustomerIDandFreight=ALL(selectFreightfromOrders)用带有聚集函数的子查询，找出所签订的订单运费最高的顾客编号，和公司名；selectCustomers.CustomerID,CompanyNamefromOrders,CustomerswhereOrders.CustomerID=Customers.CustomerIDandFreight=(selectMax(Freight)fromOrders)用相关子查询，找出没有下过订单的客户编号和公司名；selectCustomerID,CompanyNamefromCustomerswherenotexists(select*fromOrderswhereOrders.CustomerID=Customers.CustomerID)用集合查询，找出单价小于20的及库存大于50的产品编号、产品名称，要求保留重复的元组；selectProductID,ProductNamefromProductswhereUnitPrice20UNIONALLselectProductID,ProductNamefromProductswhereUnitsInStock50用集合查询，找出订货数量大于0的，及折扣大于0的产品编号，要求去除重复的元组；selectProductIDfromOrderDetailswhereQuantity0UNIONselectProductIDfromOrderDetailswhereDiscount0找出在同一个地区，且存在订货关系的客户编号，公司名称和供应商编号，供应商名称；SelectCustomers.CustomerID,Customers.CompanyName,Suppliers.SupplierID,Suppliers.CompanyNamefromCustomers,SupplierswhereCustomers.Region=Suppliers.Regionandexists(select*fromOrders,OrderDetails,Products,SupplierswhereOrders.CustomerID=Customers.CustomerIDandOrders.OrderID=OrderDetails.OrderIDandOrderDetails.ProductID=Products.ProductIDandProducts.SupplierID=Suppliers.SupplierID)找出完成定单数量最多的职工姓、名、编号；selectLastName,FirstName,EmployeeIDfromEmployeeswhereEmployeeID=(selectEmployeeIDfromOrdersgroupbyEmployeeIDhavingcount(*)=ALL(selectcount(*)fromOrdersgroupbyEmployeeID))