数据库电子版作业.docVIP

1、select * from Products where ProductName like %en or ProductName like %ton 2、select top 5 * from Products where UnitPrice between 15 and 25 order by NEWID() 3、select CustomerID,City from Customers where Country=USA 4、select * from Suppliers where PostalCode like [A-Z]% and Fax is not null 5、select ReportsTo from Employees where Title=Sales Representative 6、select * from Orders where ShippedDate2009-6-30 and RequiredDateShippedDate 7、select CategoryID,AVG(UnitPrice) as avg from Products group by CategoryID having AVG(UnitPrice)20 8、select SupplierID,CategoryID,AVG(UnitPrice) from Products group by SupplierID,CategoryID order by SupplierID,CategoryID 9、select SupplierID,COUNT(UnitPrice) from Products where UnitPrice 20 group by SupplierID 10、select CustomerID,month(OrderDate) as month,COUNT(OrderID) from Orders where OrderDate between 2009-1-1 and 2009-12-31 group by CustomerID,month(OrderDate) 11、select a.productid,count(a.OrderID),SUM(amount) from OrderItems as a inner join Orders as b on a.OrderID=b.OrderID where year(b.OrderDate)=2009 group by a.ProductID order by a.ProductID 12、select a.employeeid,a.lastname+ +a.FirstName as fullname,SUM(c.Amount) as sum from Employees as a inner join Orders as b on a.EmployeeID=b.EmployeeID inner join OrderItems as c on b.OrderID=c.OrderID group by a.EmployeeID,a.lastname+ +a.FirstName having SUM(c.Amount)1500000 13、select * from Products where CategoryID=(select CategoryID from Products where ProductName=tofu) and UnitPrice(select 2*UnitPrice from Products where ProductName=tofu) 14、select CategoryID,AVG(UnitPrice) from Products group by CategoryID having AVG(UnitPrice) (select AVG(a.UnitPrice) from Products as a inner join Categories as b on a.CategoryID=b.CategoryID where b.CategoryName=Beverages) 15、select CustomerID,COUNT(OrderID) from Orders group by CustomerID having COUNT(OrderID)20 order by customerid 16、select CustomerID,COUNT(OrderID) as num into temb from Orders group by CustomerID select * from temb where num=(sel