刚做完实验,小伙伴们可以参考
(1) 在产品表(Products)中找出库存大于50的产品的所有信息,按产品编号升序排序。
SELECT *
FROM Products
WHERE UnitsInStock>=50
ORDER BY ProductID
(2) 查询顾客表(Customers)中所有不重复的所在城市,并升序排序。
SELECT DISTINCT City
FROM Customers
ORDER BY City
(3) 在订单表(Orders)中找出运费在10到50(含10和50)之间的订单编号、顾客编号和职员编号,并按订单号升序排序。
SELECT OrderID,CustomerID,EmployeeID
FROM Orders
WHERE Freight >=10 AND Freight<=50
ORDER BY OrderID
(4) 在顾客表(Customers)中找出所在城市为London的联系人名和公司
SELECT ContactName,CompanyName
FROM Customers
WHERE City='London'
(5) 在顾客表(Customers)中找出所在城市为CLondon、Madrid、Torino和Paris的顾客编号及电话
SELECT CustomerID,Phone
FROM Customers
WHERE City IN ('CLondon','Madrid','Torino','Paris')
(6) 在订单表(Orders)中找出国籍不是Brazil、Spain和Mexico的订单编号和订货日期
SELECT OrderID,OrderDate
FROM Orders
WHERE ShipCity NOT IN ('Brazil','Spain','Mexico')
(7) 在产品表(Products)中找出单位数量中有box的产品名和产品编号
SELECT ProductName,ProductID
FROM Products
WHERE QuantityPerUnit LIKE '%box%'
(8) 在顾客表(Customers)中找出公司名的首字母为F的顾客编号和联系人名
SELECT CustomerID,ContactName
FROM Customers
WHERE CompanyName LIKE 'F%'
(9) 在顾客表(Customers)中找出公司名的首字母为F,第5位为k的顾客编号和联系人名
SELECT CustomerID,ContactName
FROM Customers
WHERE CompanyName LIKE 'F___k%'
(10) 统计在'1997-10-1' 到'1997-10-7'期间,订单中每个员工的订单数,并按订单数降序排序。
SELECT EmployeeID"员工编号",COUNT(ORDERID)"订单数"
FROM Orders
WHERE OrderDate BETWEEN '1997-10-1'ANd'1997-10-7'
GROUP BY EmployeeID
ORDER BY COUNT(OrderID) DESC
(11) 请查询平均价格在30元及以上的产品类型
SELECT CategoryID,AVG(UnitPrice)'平均价格'
FROM Products
GROUP BY CategoryID
Having AVG(UnitPrice)>=30
(12) 查询在'1997-10-1' 到'1997-10-7'期间订货的订单编号、客户联系人名称、城市、地址、联系电话,并按城市和订货日期升序排序。
SELECT OrderID,ContactName,City,Address
FROM Orders,Customers
WHERE Orders.CustomerID=Customers.CustomerID AND OrderDate BETWEEN '1997-10-1' AND '1997-10-7'
ORDER BY City,OrderDate
(13) 查询在'1997-10-1' 到'1997-10-7'期间的员工销售业绩情况,包括员工编号、订单量,并按员工编号升序排序。
SELECT Employees.EmployeeID,count(OrderID)'订单量'
FROM Employees,Orders
WHERE Employees.EmployeeID=Orders.EmployeeID AND OrderDate BETWEEN '1997-10-1' AND '1997-10-7'
GROUP BY Employees.EmployeeID
ORDER BY EmployeeID,count(OrderID)
(14) 查询'1997-10-7'当天所售商品的库存情况,包括商品编号、名称、库存。
SELECT Products.ProductID,ProductName,UnitsInStock
FROM Products,Orders,[Order Details]
WHERE Products.ProductID=[Order Details].ProductID AND [Order Details].OrderID=Orders.OrderID AND OrderDate='1997-10-7'
(15) 查询客户编号“BSBEV”在“1997-5-16”订购的每一笔订单明细(包括订单号、订货日期、交货日期、产品名称、单价、数量、折扣后金额)。
SELECT Orders.OrderID,OrderDate,RequiredDate,ProductName,products.UnitPrice,Quantity,products.UnitPrice*Quantity*(1-Discount) '折扣后金额'
FROM Products,Orders,[Order Details]
WHERE Products.ProductID=[Order Details].ProductID AND [Order Details].OrderID=Orders.OrderID AND OrderDate='1997-5-16'AND CustomerID='BSBEV'