SQL常用查询语句

问题描述

数据库实验课课后习题的SQL语句，题目如下

代码实现

其中SC表数据我进行了补充：

1.查询成绩在 80~90分之间的记录。

USE jiaoxuedb
GO
/*第一问*/
select * from SC
	where Score>80 AND SCORE<90
go

2.查询至少有 4 个同学选修的课程名。

/*第二问*/
select Cname from Course
where Cno in (select Cno from SC
group by Cno having count(Sno)>=4)
go

3.查询其他系中比“信息系”所有学生年龄都大的学生名单及年龄, 并按年龄降序输出。

/*第三问*/
SELECT Sno,Sname,Age from Student
WHERE Sno IN(SELECT Sno FROM Student
WHERE  Age > (SELECT MAX (Age) FROM Student GROUP BY Dept HAVING Dept='信息'))
GO

4.查询与学生张建国同岁的所有学生的学号、姓名和系别。

/*第四问*/
select Sno,Sname,Dept from Student
WHERE Age IN (SELECT Age from Student where Sname='张建国')
go

5.查询选修了两门以上课程的学生名单。

/*第五问*/
SELECT * FROM Student
WHERE Sno IN
(SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)>2)
GO

6.查询至少有一门课程与“张建国”选课相同的学生的姓名、课程名和系别。

/*第六问*/
SELECT Cname,Sname,Dept FROM Course,Student,SC
WHERE Course.Cno = SC.Cno 
AND SC.Sno=Student.Sno 
AND SC.Cno IN(SELECT Cno FROM SC,Student WHERE SC.Sno=Student.Sno AND Sname='张建国')
GO

7.查询成绩比该课程平均成绩高的学生的成绩表。

/*第七问*/
SELECT Sno,Cno,Score
FROM SC x
WHERE Score>(SELECT AVG(Score)FROM SC y WHERE y.Cno=x.Cno)
GO

8.查询选修了课号为 01001 的课程且成绩高于课程号为 01002 的课程的学生的姓名、此两门课程的课程名和成绩。

/*第八问*/
SELECT Sname,C.Cname,C1.Cname,A.Score AS 课程01001的成绩,B.SCORE AS 课程01002成绩 
FROM SC A,SC B,Student,Course C,Course C1
WHERE  A.Cno='01001' 
AND B.Cno='01002' 
AND A.Score >B.Score AND A.Sno=B.Sno
AND A.Sno=Student.Sno
AND C.Cno=A.Cno AND C1.Cno=B.Cno
GO

 9.查询所有末修 01001 号课程的学生名单。

/*第九问*/
SELECT * FROM Student 
WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='01001')

10. 查询每个学生各门课程的平均成绩和最高成绩,按降序排列输出姓名、平均成绩和最高成绩。

/*第十问*/
SELECT Sname,AVG(Score) AS 平均成绩, MAX(Score) AS 最高成绩 FROM Student ,SC
WHERE Student.Sno=SC.Sno
GROUP BY Sname
ORDER BY Sname DESC
GO

11.查询所有学生都选修了的课程的课程号和课程名。

/*第十一问*/
SELECT Cno,Cname FROM Course
WHERE NOT EXISTS (SELECT * FROM Student 
WHERE NOT EXISTS ( SELECT * FROM SC 
                  WHERE SC.Sno= Student.Sno 
                  AND Cno=course.Cno))
GO

12.查询选修了 991102 号学生选修了的课程的学生的学号和姓名。

/*第十二问*/
SELECT Sname AS 姓名,Sno AS 学号 FROM Student
WHERE NOT EXISTS 
    (SELECT * 
    FROM SC AS sc_1
    WHERE SNO='991102' AND NOT EXISTS
　　　　(SELECT * 
　　　　FROM SC AS sc_2
　　　　WHERE sc_2.SNO=Student.SNO AND sc_2.CNO=sc_1.CNO))