数据库程序设计-多表查询

1. 查询所有iPhone 6s的入库信息

select product.pname, recruit.* from recruit,product where recruit.pid=product.pid group by rid having pname='iPhone 6s';

2. 查询小米Note2 2016年12月份共买了多少台。显示产品名称，买的台数。

select p.pname,count(o.ocount) from orders o,product p where o.odate>'2016-12-01' and o.odate<'2016-12-31' and o.cid=p.pid;

3. 查看2016年12月份iPhone 7有多少盈利，显示产品名称，盈利

# 1)先查询iPhone 7产品的pid
select product.pid from product where product.pname='iPhone 7';
# 2).由于产品表中的pid和订单表中的pid相同，再根据订单中的pid查询订单中的oid
select orders.oid from orders where orders.pid=3;

select product.pname,sum(orders.ocount*(orders.oprice-product.pprice)) from product,orders where orders.pid=3 and orders.odate>'2016-12-01' and orders.odate<'2016-12-31' and product.pid=orders.pid;

4. 查询2016年12月份哪些顾客买了iPhone 6s,显示顾客姓名，订单号，产品名称，购买日期

select customer.cname,orders.oid,product.pname,orders.odate from customer,orders,product where orders.pid=4 and product.pid=orders.pid and customer.cid=orders.cid and orders.odate>'2016-12-01' and orders.odate<'2016-12-31';

5. 计算供应商雷军的所有产品的平均价格，显示供应商姓名，平均价格

# 1).先查出供货商雷军的vid
select vendor.vid from vendor where vendor.vname='雷军';
# 2).由于库存表recruit中的vid和vendor中的vid相同，可以根据其查询库存表中的所有产品的平均价格
select avg(recruit.rprice) from recruit where recruit.vid=1;

select vendor.vname,avg(recruit.rprice) from vendor,recruit where recruit.vid=(select vendor.vid from vendor where vendor.vname='雷军');

6. 使用左连接查询2016年11月份入库的所有商品名及入库情况
   

select product.*,recruit.rid,recruit.vid,recruit.rprice,recruit.rcount,recruit.rdecp from product left join recruit on product.pid=recruit.pid where rdate BETWEEN '2016-10-31' AND '2016-12-01';

7. 查询所有iPhone 7 在2016年12月10日前（不包括12月10日）的销售情况

select product.pname,orders.* from orders,product where orders.pid=(select product.pid from product where product.pname='iPhone 7') and orders.odate>'2016-12-01' and orders.odate<'2016-12-10' and product.pid=orders.pid;

8. 查询顾客马云的订单数量,显示订单号，顾客姓名，订单数量。

# 1).先查出顾客马云的cid
select customer.cid from customer where customer.cname='马云';
# 2).利用查出的cid，再查询订单号，订单数量
select orders.oid,orders.ocount from orders where orders.cid=1;

select orders.oid,customer.cname,orders.ocount from orders,customer where orders.cid=(select customer.cid from customer where customer.cname='马云') and orders.cid=customer.cid;

9. 查询提供过“iPhone 7”商品的所有供应商名及邮编。

# 1).先查出iPhone 7的pid
select product.pid from product where product.pname='iPhone 7';
# 2).再根据iPhone 7的pid，在recruit表中查出pid=3的供货商vid
select recruit.vid from recruit where pid=3;
# 3).在根据供货商的vid，在vendor表中查出供货商名及邮编
select vendor.vname,vendor.vzip from vendor where vendor.vid in(2,3)

select vendor.vname,vendor.vzip from vendor where vendor.vid in(select recruit.vid from recruit where pid=(select product.pid from product where product.pname='iPhone 7'));

10. 查询小米Note2共有几次订单，显示产品名称，订单数量。

select p.pname,count(o.oid) from product p,orders o where p.pname='小米Note2' and o.pid=p.pid;