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