记录五道笔试题遇到的SQL题

四张SQL表如下，下面是问题与答案。

题1：查询所有商品名称为“Java编程思想”且店铺名称为“天天出版社”的订单ID
SQL语句

select a.order_id
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
and b.goods_name = 'Java编程思想'
left join t_store c on b.store_id = c.store_id
and c.store_name = '天天出版社';

题2：查询购买商品类型最多的三位用户的昵称，类型数量相同则按照昵称升序排序
SQL语句

select d.nick_name
from (
select distinct c.nick_name, b.goods_type
from t_order a
inner join t_goods b
on a.goods_id = b.goods_id
inner join t_user c
on a.user_id = c.user_id
) d
group by d.nick_name
order by count(d.goods_type) desc, d.nick_name asc
limit 3

题3：为所有价格超过100元的商品打五折，如果打折后小于100则置为100
SQL语句

update t_goods
set goods_price = (
case when goods_price > 100
and goods_price < 200
then 100
when goods_price > 200
then goods_price * 0.5
else goods_price
end);

题4：查询所有订单中商品总额（商品数量*商品价格）都大于500的用户昵称
第一种SQL语句

select distinct c.nick_name
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
left join t_user c
on a.user_id = c.user_id
where a.goods_num * b.goods_price > 500;

第二种SQL语句

select distinct c.nick_name
from t_order a
inner join t_goods b
on a.goods_num * b.goods_price > 500
and a.goods_id = b.goods_id
left join t_user c
on a.user_id = c.user_id;

题5：哪些店铺的类型为“书籍”的商品平均价格高于系统中所有书籍商品的平均价格，查询这些店铺的名字，按平均价格高低排列
SQL语句

select distinct a.store_name
from t_store a
inner join t_goods b
on a.store_id = b.store_id
and b.goods_type = '书籍'
group by b.store_id
having avg(b.goods_price) > (
select avg(goods_price) as avg
from t_goods
where goods_type = '书籍')
order by avg(b.goods_price) asc;