sql练习题

1.已知商品表订单表，求今年三月份每个商品的售卖情况，结果需包含：商品名称、订单数、订单总金额，最近购买时间。

select goods.name, sum(orders.price) as total_price,count(orders.id) as frequency, max(orders.update_time) as update_time 
from orders left join goods on orders.gid = goods.id  
where orders.update_time between time1 and time2
group by orders.gid;

 

 

2.用一条SQL 语句 查询出每门课都大于80 分的学生姓名。

方法一：使用自连接。如果有多门课程就要使用多次自连接，多张表连接非常耗费时间。而且SQL语句也比较复杂，需要注意事项很多（不推荐）。

select distinct test1.student_name as name
from test as test1 left join test as test2
on test1.student_name = test2.student_name and test1.lesson_name != test2.lesson_name
where test1.score >= 80 and test2.score >= 80

 

方法二：逆向思维，排除没有达到80分的学生。

select distinct student_name from test where student_name
not in(select distinct student_name from test where score < 80);

 

方法三：

select student_name from test 
group by student_name having min(score) >= 80;

 

3.已知数据库中的city如下左侧表，请编写sql输出右侧表。

 

select city1.name as '一级地名',
    city2.name as '二级地名', 
    city3.name as '三级地名' 
from city as city1 inner join city as city2 on city1.parentid = 0 and city1.id = city2.parentid
inner join city as city3 on  city2.id = city3.parentid