温故而知新之SQLServer 经典50题重写

以下为学生表、课程表、成绩表

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'
	)
)