第四次作业
1.
select 学号,姓名,性别,出生日期,家庭住址
from student_info
order by 性别 asc,学号 desc;
2.
select 学号,AVG（分数）AS 平均成绩
from grade
group by 学号;
3.
select a.学号,姓名,分数
from student_info a,grade b
where a.学号=b.学号 and b.分数 between 80 and 90;
4.
select a.学号,分数,姓名
from student_info a INNER JOIN grade b ON a.学号=b.学号
where b.课程名称='0003';
5.
select a.学号,MAX(分数) AS 最高成绩 ,姓名
from student_info a,grade b
where a.学号=b.学号
group by a.学号;
6.
select 姓名,SUM(分数) AS 总成绩,a.学号
from student_info a LEFT OUTER JOIN grade b ON a.学号=b.学号
group by 学号;
7.
insert into grade values('0004','0006','76');
8.
select b.课程编号,课程名称,count(学号) AS 选修人数
from curriculum c RIGHT OUTER JOIN grade b ON c.课程编号=b.课程编号
group by b.课程编号;
9.
select a.学号,姓名
from student_info a left outer join grade b on a.学号=b.学号
where b.学号 is null;
10.
select count(*) AS 选修人数
from student_info
where 学号 in(select 学号 from grade);
11.
select b.课程编号,课程名称,count(*) AS 人数
from curriculum c, grade b
where c.课程编号=b.课程编号
group by b.课程编号
having 人数>=3;
12.实现表与表之间的连接有5种方式，有相等连接、自身连接、不等连接、左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)