第二次作业
1. SQL命令查询选修2号课的学生学号,成绩,并按照成绩降序输出;
select sno,grade
from SC
where cno='2'
order by grade desc
2. SQL命令查询各系院名称及学生人数;
select sdept,COUNT(*)
from student
group by sdept
3. SQL命令查询选修2号课程的学生学号,姓名及成绩;
select SC.Sno,sname, grade
from Student, SC
where Student.Sno=SC.Sno and cno='2';
4. SQL命令查询学生李勇选修的课程名称及成绩;
select cname, grade
from Student, SC,course
where Student.Sno=SC.Sno and course.cno=SC.cno and sname='李勇';
5. SQL命令查询每个学生学号及选修课程的总学分数。
select sno, SUM(ccredit)
from SC,course
where course.cno=SC.cno
group by sno;
6. SQL命令查询每个学生姓名及选修课程号及成绩(没选课的学生姓名也要输出)
SELECT *
FROM SC RIGHT JOIN Student
ON Student.Sno = SC.Sno