笔记,仅此而已
1.查询所有同时订购了自行车和头盔的顾客。
----Exists的使用，Where 子查询
SELECT Customers.CustomerID, Customers.CustFirstName, Customers.CustLastName
FROM Customers
WHERE EXISTS
(SELECT *
FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber
WHERE Products.ProductName LIKE "*Helmet" AND Orders.CustomerID = Customers.CustomerID)
AND EXISTS
(SELECT *
FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber
WHERE Products.ProductName LIKE "*Bike" AND Orders.CustomerID = Customers.CustomerID);
2.列出厂商以及他们销售给我们的产品数目
----表达式中的子查询
SELECT VendName, (SELECT COUNT(*) FROM Product_Vendors WHERE Product_Vendors.VendorID = Vendors.VendorID) AS VendProductCount
FROM Vendors;
3.显示产品和该产品最后一次被订购的时日期
----三个表的关联+表达式子查询
----只要最后的FROM clause中有或者JOIN中含有就可以直接在子查询中使用没有前后顺序。
SELECT Products.ProductNumber, Products.ProductName, (SELECT MAX(Orders.OrderDate) FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber WHERE Order_Details.ProductNumber = Products.ProductNumber) AS LastOrder
FROM Products;
4．列出定购了自行车的所有顾客。
SELECT Customers.CustomerID, Customers.CustFirstName, Customers.CustLastName
FROM Customers
WHERE Customers.CustomerID IN
(SELECT Orders.CustomerID
FROM (Orders
INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber)
INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber
WHERE Products.ProductName LIKE '*Bike');
5.用自连接来代替”子查询”.
--SELECT S.SName, S.Sex, S.Age
--FROM S
--WHERE S.Age>(SELECT Sub.Age FROM S AS Sub WHERE Sub.SName LIKE '王华%')
SELECT S.SName, S.Sex, S.Age
FROM S, S AS Sub
WHERE S.Age>Sub.Age AND Sub.SName LIKE '王华%'
6.转换子查询-----集合函数
查询工资比其所在部门平均工资高的所有职工信息
--SELECT *
--FROM people INNER JOIN (SELECT JOB, AVG(WAGE) AS AVG_WAGE FROM PEOPLE GROUP BY JOB) AS AVG_P ON AVG_P.JOB=PEOPLE.JOB
--WHERE PEOPLE.WAGE>AVG_WAGE
SELECT People.PNO, People.PName, People.JOB, People.Wage, AVG(AVG_P.Wage) AS AVG_Wage
FROM People INNER JOIN People AS AVG_P ON AVG_P.Job=People.Job
GROUP BY AVG_P.JOB, People.PNO, People.Pname, People.Job, People.Wage
HAVING People.Wage > AVG(AVG_P.Wage)
7.查询有订单的城市的数目
SELECT COUNT(*) AS 有订单的城市数目
FROM(
SELECT DISTINCT 供应商.城市
FROM供应商
GROUP BY 供应商.城市) AS TMP
WHERE EXISTS(
SELECT订单.货主城市
FROM订单
WHERE TMP.城市=订单.货主城市
GROUP BY 货主城市)
