数据库实验二
实验二 连接查询
- 连接查询
- 查询“国皓科技有限公司”的订单信息
SELECT *
FROM Customer C JOIN Sell_Order S
ON C.CustomerID=S.CustomerID
WHERE CompanyName='国皓科技有限公司'
- 查询“三川实业有限公司”订购的商品信息,输出字段为:CompanyName,ProductName,Price,SellOrderNumber。
SELECT C.CompanyName,P.ProductName,P.Price,S.SellOrderNumber
FROM
Customer C JOIN Sell_Order S
ON S.CustomerID=C.CustomerID
JOIN Product P
ON P.ProductID=S.ProductID
WHERE Companyname='三川实业有限公司'
- 查询各公司各种商品的订单数量,输出字段为:CompanyName,ProductName,订单数量,按照订单数量升序排列。
SELECT C.Companyname,P.ProductName,COUNT(S.SellOrderID) 订单数量
FROM Sell_Order S JOIN Customer C
ON C.CustomerID=S.CustomerID
JOIN Product P
ON P.ProductID=S.ProductID
GROUP BY P.ProductName,C.CompanyName
ORDER BY C.CompanyName,COUNT(S.SellOrderID) ASC
- 查询各公司各种商品的订货总量、总金额。输出字段为:CompanyName,ProductName,订货总量,总金额。按照总金额降序排列。
SELECT C.Companyname,P.ProductName,SUM(S.SellOrderNumber) 订货总量 ,SUM(P.Price * S.SellOrderNumber) 总金额
FROM Sell_Order S JOIN Customer C
ON C.CustomerID=S.CustomerID
JOIN Product P
ON P.ProductID=S.ProductID
GROUP BY C.CompanyName,P.ProductName
ORDER BY C.CompanyName,总金额 DESC
- 自身连接
- 查询和“章宏”同一部门的员工号,员工姓名。
SELECT E2.EmployeeID,E2.EmployeeName
FROM Employee E1,Employee E2
WHERE E1.EmployeeName='章宏' AND E1.DepartmentID=E2.DepartmentID
- 查询既订购过3号产品,又订购过4号产品的客户号。
SELECT C.CustomerID
From Customer C,Sell_Order S1,Sell_Order S2
WHERE C.CustomerID=S1.CustomerID AND C.CustomerID=S2.CustomerID AND S1.ProductID='3' AND S2.ProductID='4'
GROUP BY C.CustomerID
- 外连接
- 查询没有接收到订单的员工姓名。
SELECT E.EmployeeName FROM Employee E
WHERE EmployeeID NOT IN(
SELECT EmployeeID
FROM Sell_Order)
- 查询没有任何订购信息的客户公司名。
SELECT C.CompanyName,C.CustomerID FROM Customer C
WHERE CustomerID NOT IN(
SELECT CustomerID
FROM Sell_Order)
- 查询没有被订购的商品名称。
SELECT P.ProductName FROM Product P
WHERE ProductID NOT IN(
SELECT ProductID
FROM Sell_Order)