SQL实验七—多表查询（二）

实验目的：

1. 掌握子查询语句的一般格式。

2. 熟练掌握子查询的各种方法。

   实验内容：

   对JXGL数据库进行查询，用子查询完成下列题。

3. 查询未被选修的课程的课程名和学分。

   select * from course
   left join sc on course.cno=sc.cno
   where sno is null

4. 查询“CS”系学生的选课信息。

   select * from student
   join sc on student.sno=sc.sno
   where sdept = 'CS'
   

5. 查询不选1号课程的学生名字。

   select sname from student 
   where sno not in(select sno from sc where cno='1')
   

6. 查询至少被2个学生选修的课程的课程名。

   select cname from course 
   where cno in (select cno from sc 
   group by cno 
   having count(*)>=2)
   

7. 查询计算机系（CS）选修了2门及以上课程的学生的学号。

   select sno from sc 
   where sno in(select sno from student where sdept='CS') 
   group by sno 
   having count(*)>=2
   

8. 查询与“钱横”在同一个系学习的学生信息。

   select * 
   from student 
   where sname!='钱横' and sdept= (select sdept from student where sname='钱横')
   

9. 查询选修了课程名为“数学分析”的学生学号、姓名和所在系。

   select sno,sname,sdept 
   from student 
   where sno in(select sno from sc 
    where cno in(select cno from course  
   where cname='数学分析'))
   

10. 查询同时选修了“1”课程和“2”课程的学生的姓名。

    select sname from student 
    where sno in  (select s1.sno from sc s1,sc s2 
    where s1.cno='1' and s2.cno='2' and s1.sno=s2.sno)
    

11. 查询至少选修了3门课程的学生的姓名。

    select sname from student 
    where sno in (select sno from sc 
    group by sno 
    having count(*)>=3)
    

12. 查询平均成绩在80分以上的学生的姓名。

    select sname from student 
    where sno in (select sno from sc 
    group by sno 
    having avg(grade)>80)