以下为学生表、课程表、成绩表
Student(Sid,Sname,Sage,Ssex)学生表
Sid:学号
Sname:学生姓名
Sage:学生年龄
Ssex:学生性别
Course(Cid,Cname,T#)课程表
Cid:课程编号
Cname:课程名称
Tid:教师编号
SC(Sid,Cid,score)成绩表
Sid:学号
Cid:课程编号
score:成绩
Teacher(Tid,Tname)教师表
Tid:教师编号:
Tname:教师名字
/****** Object: Table [dbo].[Teacher] Script Date: 05/29/2019 16:31:30 ******/
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T001', N'章永华')
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T002', N'徐明长')
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T003', N'吴海华')
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T004', N'章守理')
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T005', N'汤小海')
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T006', N'吴英英')
INSERT [dbo].[Teacher] ([Tid], [Tname]) VALUES (N'T007', N'倪康来')
/****** Object: Table [dbo].[Student] Script Date: 05/29/2019 16:31:30 ******/
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S001', N'张三', 17, N'男')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S002', N'李思', 18, N'男')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S003', N'王二', 18, N'男')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S004', N'杜丽', 17, N'女')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S005', N'蒋燕', 18, N'女')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S006', N'周玉', 19, N'女')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S007', N'刘瑶', 20, N'女')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S008', N'黄民', 19, N'男')
INSERT [dbo].[Student] ([Sid], [Sname], [Sage], [Ssex]) VALUES (N'S009', N'邹海', 17, N'男')
/****** Object: Table [dbo].[Score] Script Date: 05/29/2019 16:31:30 ******/
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C001', CAST(94.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C002', CAST(92.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C003', CAST(91.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C004', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C005', CAST(66.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C006', CAST(67.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S001', N'C007', CAST(88.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C001', CAST(60.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C002', CAST(61.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C003', CAST(56.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C004', CAST(54.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C005', CAST(59.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C006', CAST(56.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S002', N'C007', CAST(51.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C001', CAST(87.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C002', CAST(56.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C003', CAST(96.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C004', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C005', CAST(66.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C006', CAST(67.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S003', N'C007', CAST(89.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C001', CAST(86.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C002', CAST(46.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C003', CAST(99.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C004', CAST(79.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C005', CAST(68.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C006', CAST(67.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S004', N'C007', CAST(88.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C001', CAST(50.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C002', CAST(91.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C003', CAST(89.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C004', CAST(66.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C005', CAST(76.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C006', CAST(80.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S005', N'C007', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C001', CAST(94.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C002', CAST(92.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C003', CAST(91.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C004', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C005', CAST(67.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C006', CAST(87.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S006', N'C007', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C001', CAST(80.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C002', CAST(99.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C003', CAST(95.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C004', CAST(58.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C005', CAST(60.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C006', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S007', N'C007', CAST(98.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C001', CAST(80.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C002', CAST(93.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C003', CAST(94.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C004', CAST(90.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C005', CAST(82.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C006', CAST(67.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S008', N'C007', CAST(89.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S009', N'C003', CAST(66.00 AS Numeric(4, 2)))
INSERT [dbo].[Score] ([Sid], [Cid], [score]) VALUES (N'S009', N'C004', CAST(88.00 AS Numeric(4, 2)))
/****** Object: Table [dbo].[Course] Script Date: 05/29/2019 16:31:30 ******/
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C001', N'T002', N'语文')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C002', N'T003', N'数学')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C003', N'T007', N'英语')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C004', N'T006', N'化学')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C005', N'T002', N'生物')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C005', N'T004', N'生物')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C006', N'T001', N'地理')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C007', N'T002', N'历史')
INSERT [dbo].[Course] ([Cid], [Tid], [Cname]) VALUES (N'C007', N'T005', N'历史')
1、查询“C001”课程比“C002”课程成绩高的所有学生的学号、姓名
SELECT
s.sid,
s.sname
FROM student s
LEFT JOIN
(
SELECT
a.sid
FROM
(
SELECT
sid,
score
FROM Score
WHERE cid='C001'
) a,
(
SELECT
sid,
score
FROM score
WHERE cid='C002'
) b
WHERE a.sid = b.sid
AND a.score>b.score
) meg
ON s.sid=meg.sid
2、查询平均成绩大于60分的同学的学号和平均成绩,成绩保留两位小数
SELECT
sid,
CONVERT(decimal(5, 2), AVG(score)) AS score
FROM Score
GROUP BY sid
HAVING AVG(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩
SELECT
s.sid,
s.sname,
sc.count_cid AS 选课数,
sc.sum_score AS 总成绩
FROM student s
LEFT JOIN
(
SELECT
sid,
COUNT(cid) AS count_cid,
SUM(score) AS sum_score
FROM score
GROUP BY sid
) sc
ON s.sid = sc.sid;
4、查询姓‘章’的老师的个数:
SELECT
COUNT(*)
FROM dbo.Teacher
WHERE TNAME LIKE '章%'
5、查询没有学过“徐明长”老师课的同学的学号、姓名:
--学生中没有学过徐明长老师课的学生
SELECT
s.Sid,
s.Sname
FROM Student s
WHERE s.Sid NOT IN
(
--学过徐明长老师课的学生
SELECT
DISTINCT(Sid) AS Sid
FROM Score sc
WHERE sc.Cid IN
(
--徐明长老师教授的课Cid
SELECT
DISTINCT(c.Cid) as Cid
FROM Course c
LEFT JOIN Teacher t
ON c.Tid=t.Tid
WHERE t.Tname='徐明长'
)
)
6、查询学过“叶平”老师所教的所有课的同学的学号、姓名:
SELECT s.sid,s.sname
FROM student AS s
WHERE s.sid IN
(
SELECT DISTINCT sc.sid
FROM Score AS sc
WHERE sc.cid IN
(
SELECT cid
FROM course AS c
LEFT JOIN teacher AS t ON c.tid = t.tid
WHERE t.tname = '徐明长'
)
GROUP BY sc.sid
HAVING COUNT(cid)=
(
SELECT COUNT(cid)
FROM course AS c
LEFT JOIN teacher AS t
ON c.tid = t.tid
WHERE t.tname = '徐明长'
)
)
7、查询学过“C001”并且也学过编号“C002”课程的同学的学号、姓名:
SELECT
s.Sid,
s.Sname
FROM Student s
WHERE S.Sid IN
(
SELECT
DISTINCT(sc.Sid) AS Sid
FROM Score sc
WHERE Cid IN('C001','C002')
)
8、查询课程编号“C002”的成绩比课程编号“C001”课程低的所有同学的学号、姓名:
SELECT
s.Sid,
s.Sname
FROM Student s
WHERE s.Sid IN
(
SELECT
r.sid FROM
(
SELECT
t1.sid,
t1.score AS t1score,
t2.score as t2score
FROM
(
SELECT
sc1.sid as sid,
sc1.Score as score
FROM dbo.Score sc1
WHERE Cid='C001'
) as t1
LEFT JOIN
(
SELECT
sc2.Sid as sid,
sc2.Score as score
FROM dbo.Score sc2
WHERE Cid='C002'
) as t2
ON T1.sid=T2.sid
WHERE t2.score<t1.score
) as r
)
9、查询所有课程成绩小于60的同学的学号、姓名:
SELECT
s.Sid,
s.Sname
FROM Student s
WHERE s.Sid IN
(
SELECT
DISTINCT(sc.Sid) AS Sid
FROM dbo.Score sc
WHERE Score<60
)
10、查询没有学全所有课的同学的学号、姓名:
SELECT
s.Sid,
s.Sname
FROM Student s
WHERE s.Sid IN
(
SELECT
sc.Sid
FROM Score sc
GROUP BY sc.Sid
HAVING COUNT(sc.Cid)<
(
SELECT
COUNT(DISTINCT c.Cid) as allcnt
FROM Course c
)
)
11、查询至少有一门课与学号为“S009”同学所学相同的同学的学号和姓名:
SELECT
st.Sid,
st.Sname
FROM Student st
WHERE st.Sid IN
(
SELECT
DISTINCT(sc1.sid) AS Sid
FROM Score sc1
LEFT JOIN student s
ON sc1.sid = s.sid
WHERE
EXISTS
(
SELECT
sc2.cid
FROM Score AS sc2
WHERE sc1.cid = sc2.cid
AND sc2.sid = 'S009'
)
)