MySQL子查询练习

没有数据库的先执行50q数据库

1. 检索没有供应商品的所有供应商信息
   select * from vendors where vend_id not in (select distinct vend_id from products)
2. 检索提供价格最高商品的供应商信息
   select * from vendors where vend_id=(select vend_id from products order by prod_price desc limit 1)
3. 检索价格大于平均价格的所有产品信息
   select * from products where prod_price>(select avg(prod_price) from products)
4. 检索价格大于‘ANV01’、‘ANV03’、‘ANV03’这3样产品价格的所有产品信息
   SELECT * FROM products where prod_price >all (select prod_price from products where prod_id in('ANV01','ANV02','ANV03'));
5. 检索价格大于‘ANV01’、‘ANV03’、‘ANV03’这3样产品任意一个价格的所有产品信息
   SELECT * FROM products where prod_price >any (select prod_price from products where prod_id in('ANV01','ANV02','ANV03'));
6. 检索所有客户的cust_name,cust_state和其在订单表中的订单数
   select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as count from customers;