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
货主城市
)