实验四 SQL连接查询

一、实验目的：
掌握SQL连接查询语句

二、实验内容和主要步骤：

1. 查询每个学生及其选修成绩的情况
   select sno,cno,Grade
   from sc;
2. 分别用左外连接和右外连接实现查询所有学生信息及其选修成绩的情况（包括没有选课的学生信息）
   select sname,cno,Grade
   from student left outer join sc on(student.sno=sc.sno);

select sname,cno,Grade
from student left outer join sc on(student.sno=sc.sno);

3. 查询选修2号课程且成绩在90分以上的学生姓名和系别信息
   select sname,cno,sdept,Grade
   from student,sc
   where student.sno=sc.sno
   having cno=‘002’ and Grade>90;

4. 查询每个学生的学号、姓名、选修的课程名及成绩
   SELECT student.sno,sname,cname,Grade
   FROM student,sc,course
   WHERE student.sno=sc.sno AND sc.cno=course.cno;

5. 查询计算机系的学生所学课程的课程名。
   select student.sno,sname,cname,sdept
   from student,sc,course
   where student.sno=sc.sno and sc.cno=course.cno
   having sdept=‘cs’;

6. 查询姓张的同学学的课程的课程名称。
   SELECT sname,cname
   FROM student,sc,course
   WHERE student.sno=sc.sno AND sc.cno=course.cno
   HAVING sname LIKE ‘张%’;

7. 查询至少选修两门课程的学生姓名（*）
   SELECT sname,COUNT(sc.sno)
   FROM student,sc
   WHERE student.sno=sc.sno
   GROUP BY sc.sno
   HAVING COUNT(sc.sno)>=2;

8. 查询课程平均成绩大于80的课程名称（*）
   SELECT sname,COUNT(sc.sno)
   FROM student,sc
   WHERE student.sno=sc.sno
   GROUP BY sc.sno
   HAVING COUNT(sc.sno)>=2;

9. 查询选修人数最多的课程名称（*）
   select cname,count(sc.sno) as 人数
   from course,sc
   where course.cno=sc.cno
   group by sc.cno
   having count(sc.sno)>=all(
   select count(sc.sno)
   from sc
   group by sc.cno
   );

10. 查询计算机系的学生选修课程中选修人数前三名的课程名称（）
    select cname,count()
    from student,course,sc
    where student.sno=sc.sno and course.cno=sc.cno
    group by cname
    order by count(*) desc
    limit 3;

11. 查询每门课程的先修课程名称和学分信息
    SELECT cname,credit
    FROM course;

12. 分别用左外连接和右外连接实现查询所有课程信息和其先修课程信息（包括没有先修课程的课程信息）
    SELECT sc.,course.
    FROM sc LEFT JOIN course ON sc.cno=course.cno;

select sc.,course.
from sc right join course on sc.cno=course.cno;
13. 查询每门课程的间接先修课的信息（先修课程的先修课程）
select a1.cno,a2.cno
from course
a1 left join course a2 on a1.cpno=a2.cno;

14. 查询有不及格的学生姓名和所在系
    select distinct sname,sdept
    from student,sc
    where student.sno=sc.sno
    and(Grade<60 or Grade is null);
15. 查询所有成绩为优秀（大于90分）的学生姓名（*）
    SELECT sname,sno
    FROM student
    WHERE sno IN(
    SELECT sno FROM sc WHERE Grade>90);
16. 增加一个表格成绩等级表，表里记录成绩等级编号和每个等级对应的最低成绩和最高成绩
    create table glevel(
    id tinyint primary key,
    lowgrade int not null,
    highgrade int not null
    )

insert into glevel values(1,90,100),(2,80,89),(3,70,79),(4,60,69),(5,0,59);
17. 查询每个成绩等级有多少计数（）
select id,count()
from sc,glevel
where Grade between lowgrade and highgrade
group by id;
18. 查询每个学生每个成绩等级有多少计数（）
select sno,id,count()
from sc,glevel
where Grade between lowgrade and highgrade
group by sno,id;
19. 查询每门课程每个成绩等级有多少计数（）
select cno,id,count()
from sc,glevel
where Grade between lowgrade and highgrade
group by cno,id;
20. 查询每个学生选修成绩的对应等级
SELECT cno,id
FROM sc,glevel
WHERE Grade BETWEEN lowgrade AND highgrade
GROUP BY cno,id;
21. 查询选修成绩等级是5的课程名称和学生姓名
SELECT cname,sname
FROM student,sc,course,glevel
WHERE student.sno=sc.sno AND sc.cno=course.cno
AND id=‘5’;
三、实验要求：

1. 将上述任务中完整的SQL语句调试并使之运行正确；
2. 写出实验报告（在实验系统完成）
   注意：（*）为进阶题，这部分属于选做部分，且有些题目可以使用多种方法进行表示