实验4：使用子查询实现复杂查询 实验报告

GDOU-B-11-112广东海洋大学学生实验报告书实验名称实验四：使用子查询课程名称 数据库原理与应用成绩学院（系）专业班级学生姓名 学号实验地点实验日期、实验目的1. 掌握带谓词IN的子查询；2. 掌握带存在量词的子查询；3. 掌握使用比较运算符的子查询；4. 掌握使用限量谓词的子查询；5. 综合运用所学知识实现查询；6. 掌握 SELECT INTO 语句；二、实验内容完成在，GlobalToyz数据库基础上的操作，按要求完成给出的下列题目，要求写出相应数据 库的T-SQL语句。1. 将'Texas'州的订购者（Shopper）信息拷贝到一张全局临时表中（表名自定义）。2. 查询截止日期（dExpiryDate）在2001年5月的订购者（Shopper）的姓名和所在城市。3. 根据玩具品牌统计每种品牌的平均价格，输出其中平均价格最高的品牌ID以及平均价格。4. 检索每一类（类别用cCategoryId表示）玩具里价格最高的玩具的名称。5. 检索单价最高的玩具的品牌（品牌为Brand）名称。6. 要求必须用带EXISTS量词的嵌套查询实现，检索和'David Cooper'住在同一个州的订购者（Shopper）的姓和名。7. 检索订购玩具数量在3个或3个以上的订单号°（Orderdetail表的siQty表示数量）&检索和'Lisa Lee'使用同一种类型的信用卡的订购者的人数。9. 检索订购了玩具品牌为'Largo'的订购者的姓和名。（多层嵌套查询实现）10. 列出价格不低于所有品牌ID为'005'的玩具（要求显示玩具ID和名称）。11. 检索曾订购过玩具表中所有玩具的订购者名单。12. 创建数据库Employee，其中包含一个主文件，一个辅文件，两个日志文件组成。三、实验过程1.SELECT *INTO #TexasShopperFROM ShopperWHERE cState='Texas'2.SELECT vFirstName+' '+vLastName AS ClientName,cCityFROM ShopperWHERE dExpiryDate<='2001-5-31' AND dExpiryDate>='2001-5-13.SELECT cBrandId,AVG(mToyRate) AS AvgToyRateFROM ToysGROUP BY cBrandIdHAVING AVG(mToyRate)>= ALL(SELECT AVG(mToyRate)FROM ToysGROUP BY cBrandId)4-自身连接SELECT vToyNameFROM Toys XWHERE mToyRate=(SELECT MAX(mToyRate)FROM Toys YWHERE X.cCategoryId=Y.cCategoryld)5.1SELECT cBrandNameFROM ToyBrandWHERE cBrandId IN(SELECT cBrandIdFROM ToysWHERE mToyRate IN(SELECT MAX(mToyRate)FROM Toys)5.2SELECT cBrandNameFROM Toys t1, ToyBrandWHERE ToyBrand.cBrandId = t1.cBrandIdGROUP BY cBrandNameHAVING MAX(mToyRate)>=ALL(SELECT MAX(mToyRate)FROM Toys t2GROUP BY cBrandId)6.SELECT vFirstName,vLastNameFROM Shopper XWHERE EXISTS(SELECT *FROM Shopper YWHERE Y.vFirstName='David'AND Y.vLastName='Cooper' AND X.cState=Y.cState)7.SELECT cOrderNoFROM OrderDetailGROUP BY cOrderNoHAVING SUM(siQty)>='3'8.SELECT COUNT(cShopperId) AS SumShopperFROM ShopperWHERE vCreditCardType IN(SELECT vCreditCardTypeFROM ShopperWHERE vFirstName='Lisa'AND vLastName='Lee')9.SELECT vFirstname,vLastNameFROM ShopperWHERE cShopperId IN(SELECT cShopperIdFROM OrdersWHERE cOrderNo IN(SELECT cOrderNoFROM OrderDetailWHERE cToyId IN(SELECT cToyIdFROM ToysWHERE cBrandId IN(SELECT cBrandIdFROM ToyBrandWHERE cBrandName='Largo')10.SELECT cToyId,vToyNameFROM ToysWHERE mToyRate>=(SELECT MAX(mToyRate)FROM ToysWHERE cBrandId='OO5')11.1SELECT cOrderNo,Toys.cToyIdINTO #NEW1FROM OrderDetail,ToysWHERE OrderDetail.cToyId=Toys.cToyIdSELECT cOrderNo,COUNT(cToyId) AS SumToyIdINTO #NEW2FROM #NEW1GROUP BY cOrderNoSELECT vFirstName,vLastNameFROM ShopperWHERE cShopperld IN(SELECT cShopperldFROM Orders,#NEW2WHERE Orders.cOrderNo=#NEW2.cOrderNoAND #NEW2.SumToyId =(SELECT COUNT (DISTINCT cToyId)FROM TOYS)11.2SELECT vFirstName, vLastNameFROM ShopperWHERE cShopperId IN(SELECT cShopperIdFROM OrdersWHERE cOrderNo =(SELECT cOrderNoFROM OrderDetailGROUP BY cOrderNoHAVING COUNT (DISTINCT cToyId) = (SELECT COUNT(DISTINCT cToyId) FROM Toys)12.CREATE DATABASE EmployeeONPRIMARY(NAME = Employee,FILENAME = 'D:课程数据库新建文件夹Emplpyee1.mdf', SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),(NAME = Employee2 ,FILENAME = 'D:课程数据库新建文件夹Employee2.ndf'),(NAME = Employee3,FILENAME = 'D:课程数据库新建文件夹Employee3.ndf'),(NAME = Employee4,FILENAME = 'D:课程数据库新建文件夹Employee4.ndf') LOG ON(NAME = Employlog1,FILENAME = 'D:课程数据库新建文件夹Employeelog1.ldf',SIZE = 100MB,MAXSIZE = 2OO,FILEGROWTH = 20),(NAME = Employeelog2,FILENAME = 'D:课程数据库新建文件夹Employeelog2.ldf',SIZE100MB,MAXSIZE = 200,FILEGROWTH = 20);四、实验总结通过这次试验，我基本掌握了 SQL Server的带谓词IN的子查询，带存在量词的子查询,使用比 较运算符的子查询，掌握使用限量谓词的子查询，对数据库的认识有了更深刻的理解，值得注意的 是，第5题和第11题，第一种方法是我自己写的，第二种是网上的参考资料。5.1是利用多层嵌 套查询实现，5.2是利用多表连接盒多层嵌套查询实现；11.1建立的两个临时表，比较麻烦，11.2 则利用多层嵌套循环实现，思路一样，但11.2比较简明。这次实验对我以后的学习提供了很大的 帮助，让我熟悉了数据库实现简单查询。指导教师日期注：请用A4纸书写，不够另附纸。第页，共页