SQL查询练习——实验06
| November 10, 2008 19:15 | timmy | Via Original
1. 用不相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数
SQL语句：
2. 用相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数
SQL语句：
select distinct count(ProductID) from orderdetails where exists
(select * from orders where OrderID=orderdetails.OrderID and exists
(select * from customers where CustomerID=orders.CustomerID and CompanyName like 'L%'))
SQL语句：
select distinct count(ProductID) from orderdetails where OrderID in
(select OrderID from orders where CustomerID in
(select CustomerID from customers where CompanyName like 'L%'))
(select OrderID from orders where CustomerID in
(select CustomerID from customers where CompanyName like 'L%'))
2. 用相关子查询，查出公司名首字母为’L’的顾客所下订单的产品总数
SQL语句：
select distinct count(ProductID) from orderdetails where exists
(select * from orders where OrderID=orderdetails.OrderID and exists
(select * from customers where CustomerID=orders.CustomerID and CompanyName like 'L%'))
SQL查询练习——实验05
| November 10, 2008 19:12 | timmy | Via Original
1. 查询顾客表(Customer)中没有设定区域的顾客编号和公司名
SQL语句：
2. 统计职工表(Employees)中头衔的数量
SQL语句：
3. 查找订单表(Orders)中顾客编号为’VICTE’和’WELLI’的的订单号和运费，并按照运费的降序排列
SQL语句：
4. 查找产品表(Products)中的平均库存总价(库存总价=单价×库存数)
SQL语句：
SQL语句：
select CustomerID,CompanyName from customers where Region is NULL
2. 统计职工表(Employees)中头衔的数量
SQL语句：
select distinct Title from Employees
3. 查找订单表(Orders)中顾客编号为’VICTE’和’WELLI’的的订单号和运费，并按照运费的降序排列
SQL语句：
select OrderID,Freight from orders where CustomerID in ('VICTE','WELLI') order by Freight DESC
4. 查找产品表(Products)中的平均库存总价(库存总价=单价×库存数)
SQL语句：
select avg(UnitPrice*UnitsInStock) from products
