实验3 连接查询和嵌套查询

实验目的：
1．熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。
2．熟练掌握简单表的数据连接查询和嵌套查询的操作方法。
实验内容：
创建教学管理数据库“JXGL”，在“JXGL”数据库中创建3-2中的三张表并添加数据，实现数据的单表查询操作。
实验步骤：
写出下列操作的SQL语句
在教学管理“JXGL”数据库中进行如下操作：
（1）查询每个同学的的学号、姓名、所在系、选修课程的课程号及成绩。

SELECT DISTINCT student.Sno, Sname,Grade,Sdept,course.Cno
FROM Student join sc
on student.Sno =SC.Sno
join course
on SC.Cno = course.Cno

（2）查询选课成绩在90分以上的学生的学号、姓名和所在系。

SELECT student.Sno, Sname,Sdept
FROM Student join sc
on student.Sno =SC.Sno
join course
on SC.Cno = course.Cno
where grade>90;

（3）查询"CS"系且选课成绩在90分以上的学生的学号、姓名和所在系。

SELECT student.Sno, Sname,Sdept
FROM Student join sc
on student.Sno =SC.Sno
join course
on SC.Cno = course.Cno
where Sdept='cs'and grade>90;

（4）查询每一门课的间接先修课（即先修课的先修课）。

select c1.cno,c2.cpno
from course c1,course c2
where c1.cpno =c2.cno

（5）查询有选课记录的同学的学号、姓名、系名、选修课程的课程号及成绩。

SELECT DISTINCT student.Sno, Sname,Sdept
FROM Student join sc
on student.Sno =SC.Sno
join course
on SC.Cno = course.Cno
where Ccredit is not null

（6）查询所有同学的学号、姓名、系名、选修课程的课程号及成绩。（左外连接）。

SELECT  student.sno,student.sname,Sdept,grade,course.Cno
FROM student 
LEFT  JOIN sc 
on student.Sno=SC.Sno
left join course
on SC.Cno=course.Cno

（7）查询每个学生的学号、姓名、所在系、选修的课程名及成绩。（与（1）题有何不同）

SELECT  student.Sno, Sname,Cname,Grade,Sdept
FROM Student join sc
on student.Sno =SC.Sno
join course
on SC.Cno = course.Cno

（8）查询选修了课程名为“数据库”的学生学号和姓名。

 SELECT student.Sno, Sname
FROM Student,Course
where course.Cname='数据库';

（9）查询选修了课程名为“数据库”的男学生学号和姓名。

 SELECT student.Sno, Sname
FROM Student,Course
where course.Cname='数据库'and student.Ssex='男';

--------------------------------------------------------------（10-19题要求用嵌套查询实现）

（10）查询与“李勇”年龄相同的学生的学号、姓名、年龄和所在系。

 select * from student 
where sage=(
select sage from student 
where sname='李勇') and sname!='李勇';

（11）查询选修了课程名为“数据库”的学生学号和姓名。

 select s.sno,s.sname 
from SC 
left join student s 
on SC.sno=s.sno 
left join course c 
on c.cno=SC.cno
 where c.cname='数据库';

（12）查询年龄大于“李勇”年龄的同学的学号、姓名、年龄和所在系。

  select * 
 from student 
 where sage>(select sage from student
  where sname='李勇') ;

（13）查询年龄大于计算机系平均年龄的同学的学号、姓名、年龄和所在系。

 select *
from student
where sage>(
select avg(sage)
from student) 

（14）查询 年龄大于计算机系年龄最大者的同学的学号、姓名、年龄和所在系。
select *

from student 
where sage>(
select max(sage) 
from student 
where sdept='CS' 
);

（15）查询其他系中年龄大于计算机系年龄最大者的同学的学号、姓名、年龄和所在系。

 select * 
from student 
where sage>(
select max(sage) from student 
where sdept='CS' ) and sdept!= 'SC';

（16）查询每个学生超过他选修课程平均成绩的学号和课程号。

 SELECT sno,cno
FROM sc x
WHERE grade > (
SELECT AVG(grade)
FROM sc y
WHERE x.sno = y.sno)

（17）查询每门课程超过它平均分的那些选修记录的学号和课程号。

 select Sno,Cno,Grade
from SC x
WHERE Grade>(select avg(Grade)
from SC y
where y.Sno=x.Sno);

（18）查询大于所在系平均年龄的同学的学号、姓名、所在系和年龄。

 select x.sno,x.sname,x.sdept
from student  x
where x.sage>(select avg(sage)
from student  y
where x.sdept=y.sdept);

--------------------------------------------------------------（用所学知识实现以下语句）

（19）求选修了数学课的学生姓名。

SELECT STUDENT.Sname 
FROM STUDENT,sc
WHERE sc.cno=2 AND STUDENT.sno=Sc .sno

（20） 求没有选修数学课的学生姓名。

SELECT DISTINCT Sname FROM STUDENT 
WHERE ('2'NOT IN(SELECT SC.Cno FROM Sc
  WHERE STUDENT.Sno=SC.Sno))

（21）求选修各门课的人数及平均成绩。

SELECT count(*) as num,avg(Grade) as avg_grade FROM SC
group by Cno

（22）求选修课程在2门及以上且都及格的学生号及总平均分。

select sno, AVG(grade) as avescore
from SC
where exists (select * from student
where not exists (select *
from SC
where Sno=student.Sno
and Cno <60))
GROUP BY Sno
  HAVING (COUNT(*)>=2)

（23）求2012级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。（学号前四位为年级）

select sno, AVG(grade) as avescore
from SC
where exists (select * from student
where not exists (select *
from SC
where Sno=student.Sno
and Cno <60))
   AND Sno LIKE '2012%'
   GROUP BY Sno
   HAVING (COUNT(*)>=2)

（24）统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT Sno, COUNT(*) AS C_Num, AVG(Grade) AS AveScore
from sc
where grade >=60
group by sno
order by avescore desc, c_num desc

（25）统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT Sno, COUNT(*) AS C_Num, AVG(Grade) AS AveScore
FROM SC
WHERE EXISTS (
SELECT * FROM STUDENT
WHERE NOT EXISTS (
SELECT *FROM SC
WHERE Sno=STUDENT.Sno
AND Cno < 60))
GROUP BY Sno
ORDER BY AveScore DESC, C_Num DESC