笔记,仅此而已

 

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