第四次作业2

select s.学号 ,s.姓名,g.分数 from grade as g ,student_info as s where g.学号 =s.学号 and g.分数 between 80 and 90;

 select grade.学号 ,student_info.姓名,grade.分数 from student_info inner join grade on student_info.学号 = grade.学号

select grade.学号 ,student_info.姓名 ,max(grade.分数) from student_info ,grade where student_info.学号 =grade.学号 group by grade.学号 ;

 select student_info.学号 ,student_info.姓名,sum(grade.分数)as 总成绩 from student_info left outer join grade on student_info.学号 =grade.学号 group by student_info.学号 ;

insert into grade (学号,课程编号,分数) values (0004,0006,76);

 select c.课程编号 ,c.课程名称 ,count(学号)as 选修人数 from curriculum c right outer join grade g on c.课程编号 = g.课程编号 group by g.课程编号 ;

 select 学号,姓名 from student_info where 学号 not in (select 学号 from grade);

 select sum(学号)as 选课人数 from grade group by 课程编号;

on c.课程编号=g.课程编号

group by c.课程编号

having 人数 >=3；

 

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行