1. 在表中插入符合主键
[sql]
CREATE
TABLE SC
(
Sid INT
REFERENCES Student(Sid),
Cid INT
REFERENCES Course(Cid),
Score INT
NOT NULL,
PRIMARY
KEY(Sid,Cid)
)
2. 查询各科成绩最高分，最低分以及平均分
[sql]
SELECT
c.Cname, MAX(s.Score) AS
Max, MIN(s.Score) AS
Min, AVG(s.Score) AS
Average
FROM
Course c JOIN
SC s ON c.Cid = s.Cid
GROUP
BY c.Cname
3. 查询平均成绩大于80分的学生姓名以及平均成绩
[sql]
SELECT
Sname, AVG(Score) AS
Average FROM
Student JOIN
SC
ON Student.Sid=SC.Sid
GROUP
BY Sname
HAVING
AVG(Score)>80
4. 查询各学生都选了多少门课
[sql]
SELECT
Sname, COUNT(Cid) AS
TOTAL_COURSE FROM
Student
LEFT JOIN SC ON Student.Sid=SC.Sid
GROUP
BY Sname
5. 查询没有选JANE老师课的学生信息
[sql]
SELECT
s.Sid,s.Sname,s.Sage,s.Sage FROM
Student s
WHERE
s.Sid NOT
IN
(SELECT
s.Sid FROM
SC s JOIN
Course c ON
s.Cid=c.Cid
JOIN Teacher t ON
c.Tid=t.Tid
WHERE
t.Tname=‘JANE‘)
6. 查询既选择了COMPUTER课程，又选择了MATH课程的学生信息
[sql]
SELECT
s.Sid,s.Sname,s.Sage,s.Ssex FROM
STUDENT s
JOIN SC ss ON
s.Sid=ss.Sid
JOIN Course c ON
ss.Cid=c.Cid WHERE
c.Cname=‘COMPUTER‘
INTERSECT
SELECT
s.Sid,s.Sname,s.Sage,s.Ssex FROM
STUDENT s
JOIN SC ss ON
s.Sid=ss.Sid
JOIN Course c ON
ss.Cid=c.Cid WHERE
c.Cname=‘MATH‘
7. 查询COMPUTER课程比MATH课程分数高的学生学号
[sql]
SELECT
a.Sid FROM
(SELECT
s.Sid,s.Score FROM
SC s JOIN
Course c ON
s.Cid=c.Cid WHERE
c.Cname=‘COMPUTER‘) a
JOIN
(SELECT
s.Sid,s.Score FROM
SC s JOIN
Course c ON
s.Cid=c.Cid WHERE
c.Cname=‘MATH‘) b
ON a.Sid=b.Sid
WHERE
a.Score>b.Score
8. 查询和JOHN选的课相同的学生信息
[sql]
SELECT
Student.Sname FROM
Student JOIN
SC ON Student.Sid=SC.Sid
WHERE
SC.Cid IN
(SELECT
SC.Cid FROM
SC JOIN Student ON
SC.Sid=Student.Sid WHERE
Student.Sname=‘JOHN‘)
AND Student.Sname<>‘JOHN‘
GROUP
BY Student.Sname
HAVING
COUNT(SC.Cid)=
(SELECT
COUNT(*) FROM
SC JOIN Student ON
SC.Sid=Student.Sid WHERE
Student.Sname=‘JOHN‘)
9. 按总分为学生排名，总分相同名次相同
[sql]
SELECT
RANK() OVER (ORDER
BY SUM(ss.Score) DESC) AS
Rank, s.Sname, ISNULL(SUM(ss.Score),0)
FROM
Student s LEFT
JOIN SC ss
ON s.Sid = ss.Sid
GROUP
BY s.Sname
ORDER
BY SUM(ss.Score) DESC
10. 查询总分在100至200之间的学生姓名及总分
[sql]
SELECT
s.Sname,SUM(ss.Score) FROM
Student s JOIN
SC ss ON
s.Sid=ss.Sid
GROUP
BY s.Sname HAVING
SUM(ss.Score) BETWEEN
100 AND 200
11. 查询总分第六到十名的学生姓名以及总分
[sql]
SELECT
* FROM
(SELECT
TOP(5) * FROM
(SELECT
TOP(10) SC.Sid,SUM(SC.Score) AS
SUM FROM SC GROUP
BY SC.Sid ORDER
BY SUM(SC.Score)) a
ORDER
BY a.SUM) b
ORDER
BY b.SUM DESC
12. 查询各科成绩的前三名以及分数
[sql]
SELECT
s.Sid,s.Cid,s.Score FROM
SC s
WHERE
s.Score IN
(SELECT
TOP(3) Score FROM
SC WHERE
s.Cid= Cid ORDER
BY score DESC)
ORDER
BY s.Cid;
13. 查询有不及格科目的学生的姓名，不及格科目以及不及格科目成绩
[sql]
SELECT
s.Sname,c.Cname,ss.Score FROM
Student s JOIN
SC ss ON
s.Sid=ss.Sid JOIN
Course c ON
ss.Cid=c.Cid
WHERE
ss.Score<60
14. 查询所有学生都选修的课程
[sql]
SELECT
c.Cname FROM
SC s JOIN
Course c ON
s.Cid=c.Cid
GROUP
BY c.Cname HAVING
COUNT(s.Sid)=(SELECT
COUNT(*) FROM
Student)
15. 查询选修了两门或以上的学生姓名及选修总科目
[sql]
SELECT
s.Sname,COUNT(ss.Cid) AS
TOTAL FROM
Student s JOIN
SC ss ON
s.Sid=ss.Sid
GROUP
BY s.Sname HAVING
COUNT(ss.Cid)>1