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