sql语句

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

name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

select name from table group by name having min(fenshu)>80;

2.怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year, 
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

3、查询平均成绩大于60分的学生的学号和平均成绩

-- 考察点：聚集函数avg()，数据分组groupby，过滤分组
select s_id,avg(s_score) as  "平均成绩" from score group by s_id having 平均成绩 > 60

4.查询姓“猴”的老师的个数

-- 考察点：操作符 like，通配符 % 的使用
select count(*) from teacher where t_name like '猴%'

5.检索“004”课程分数小于60，按分数降序排列的同学学号

select sid,score from sc where cid='004' and score<60
order by score desc;

6.删除“002”同学的“001”课程的成绩

delete from sc where sid = '002' and cid = '001';

7.查询出只有两门课程的全部学生的学号和姓名

select s_id,s_name 
from student where s_id in(
 select s_id from score group by s_id 
 having count(*) = 2)

8、查询男生、女生人数

select (select count(*) from student where s_sex = '男') as "男生数",
(select count(*) from student where s_sex = '女') as "女生数"