每道题提供一种方法。

第一题：

select name from tablename where name not in (select distinct name from tablename where fenshu<=80)

第二题：

delete a from 学生表 as a,(select  学号,姓名,课程编号,课程名称,分数,min(自动编号) as 自动编号  from 学生表 group by 学号,姓名,课程编号,课程名称,分数) as b where a.学号=b.学号 and a.姓名=b.姓名 and a.课程编号=b.课程编号 and a.课程名称=b.课程名称 and a.分数=b.分数 and a.自动编号!=b.自动编号

第三题：

select * from (

select a.name as Aname,b.name as Bname from department as a,department as b

) as c where Aname<Bname

第四题：

select 名称 from 商品表 as a group by 名称 having avg(进价)<2

第五题：

select 准考证号 from 高考信息表  group by 准考证号 having sum(成绩)>600

第六题：

select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学)>600

第七题：

select year,

sum(case when month=1 then amount else 0 end) as m1,

sum(case when month=2 then amount else 0 end) as m2,

sum(case when month=3 then amount else 0 end) as m3,

sum(case when month=4 then amount else 0 end) as m4

 from tablename group by year order by year