资源描述：
《数据库电子版作业.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、1、select*fromProductswhereProductNamelike'%en'orProductNamelike'%ton'2、selecttop5*fromProductswhereUnitPricebetween15and25orderbyNEWID()3、selectCustomerID,CityfromCustomerswhereCountry='USA'4、select*fromSupplierswherePostalCodelike'[A-Z]%'andFaxisnotnull5、selectReportsTofro
2、mEmployeeswhereTitle='SalesRepresentative'6、select*fromOrderswhereShippedDate<'2009-6-30'andRequiredDate208、selectSupplierID,CategoryID,AVG(UnitPrice)fromProductsgroupbySuppl
3、ierID,CategoryIDorderbySupplierID,CategoryID9、selectSupplierID,COUNT(UnitPrice)fromProductswhereUnitPrice<20groupbySupplierID10、selectCustomerID,month(OrderDate)asmonth,COUNT(OrderID)fromOrderswhereOrderDatebetween'2009-1-1'and'2009-12-31'groupbyCustomerID,month(OrderDate)1
4、1、selecta.productid,count(a.OrderID),SUM(amount)fromOrderItemsasainnerjoinOrdersasbona.OrderID=b.OrderIDwhereyear(b.OrderDate)='2009'groupbya.ProductIDorderbya.ProductID12、selecta.employeeid,a.lastname+''+a.FirstNameasfullname,SUM(c.Amount)assumfromEmployeesasainnerjoinOrde
5、rsasbona.EmployeeID=b.EmployeeIDinnerjoinOrderItemsasconb.OrderID=c.OrderIDgroupbya.EmployeeID,a.lastname+''+a.FirstNamehavingSUM(c.Amount)>150000013、select*fromProductswhereCategoryID=(selectCategoryIDfromProductswhereProductName='tofu')andUnitPrice>(select2*UnitPricefromP
6、roductswhereProductName='tofu')14、selectCategoryID,AVG(UnitPrice)fromProductsgroupbyCategoryIDhavingAVG(UnitPrice)>(selectAVG(a.UnitPrice)fromProductsasainnerjoinCategoriesasbona.CategoryID=b.CategoryIDwhereb.CategoryName='Beverages')15、selectCustomerID,COUNT(OrderID)fromOr
7、dersgroupbyCustomerIDhavingCOUNT(OrderID)>20orderbycustomerid16、selectCustomerID,COUNT(OrderID)asnumintotembfromOrdersgroupbyCustomerIDselect*fromtembwherenum=(selectMAX(num)fromtemb)17、selectOrderID,COUNT(ProductID)asnumintotembfromOrderItemsgroupbyOrderIDorderbyOrderIDsel
8、ectorderidfromtembwherenum=(selectMAX(num)fromtemb)18、selectCategoryID,COUNT(Produ
