一、建表的结构和数据，在sqlserver直接用就行了
USE [test] GO/****** Object: Table [dbo].[TEACHER] Script Date: 05/16/2018 16:27:24 ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TEACHER]( [T#] [int] NOT NULL, [TNAME] [nvarchar](50) NOT NULL, PRIMARY KEY CLUSTERED ( [T#] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (50, N'李冰冰') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (51, N'黄一山') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (52, N'陈小春') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (53, N'赵铁柱') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (54, N'钱中书') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (55, N'孙悟空') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (56, N'李连杰') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (57, N'杨千嬅') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (58, N'泽日生') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (59, N'林夕') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (60, N'陈奕迅') INSERT [dbo].[TEACHER] ([T#], [TNAME]) VALUES (61, N'刘备')/****** Object: Table [dbo].[STUDENT] Script Date: 05/16/2018 16:27:24 ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[STUDENT]( [S#] [int] NOT NULL, [SNAME] [nvarchar](50) NOT NULL, [SAGE] [int] NOT NULL, [SSEX] [char](2) NOT NULL, PRIMARY KEY CLUSTERED ( [S#] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (1, N'小红', 10, N'女') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (2, N'小明', 12, N'男') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (3, N'小绿', 13, N'男') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (4, N'小蓝', 15, N'男') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (5, N'小青', 11, N'男') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (6, N'小白', 10, N'女') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (7, N'小紫', 14, N'女') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (8, N'小黄', 13, N'男') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (9, N'小粉', 12, N'女') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (10, N'小橙', 11, N'女') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (11, N'小黑', 9, N'男') INSERT [dbo].[STUDENT] ([S#], [SNAME], [SAGE], [SSEX]) VALUES (12, N'小粉', 14, N'男')/****** Object: Table [dbo].[SC] Script Date: 05/16/2018 16:27:24 ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SC]( [S#] [int] NULL, [C#] [nvarchar](50) NULL, [SCORE] [int] NOT NULL ) ON [PRIMARY] GO INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'111', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'112', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'113', 75) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'114', 79) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'115', 63) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'116', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'117', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'118', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'119', 60) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'120', 83) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'121', 88) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'122', 75) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'123', 50) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'124', 44) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'125', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'127', 79) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (1, N'128', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'111', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'112', 68) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'113', 76) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'114', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'115', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'116', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'117', 99) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'118', 86) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'120', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'121', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'122', 82) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'123', 67) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'124', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'125', 53) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'126', 64) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'127', 75) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (2, N'128', 71) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'111', 82) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'112', 67) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'113', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'114', 44) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'115', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'116', 98) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'117', 79) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'118', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'119', 68) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'120', 76) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'121', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'122', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'123', 82) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'124', 63) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'125', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'126', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'127', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (3, N'128', 60) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'111', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'112', 68) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'113', 76) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'114', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'115', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'116', 82) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'117', 76) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'118', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'119', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'120', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'121', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'122', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'123', 75) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'124', 79) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'125', 68) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'126', 54) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'127', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (4, N'128', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'111', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'112', 98) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'113', 79) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'115', 68) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'116', 85) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'117', 83) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'118', 81) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'119', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'120', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'121', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'122', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'124', 75) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'125', 75) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'126', 70) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'127', 53) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (5, N'128', 50) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'113', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'114', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'115', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'116', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'117', 60) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'118', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'120', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'121', 76) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'122', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'123', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'124', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'125', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'126', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (6, N'128', 74) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'111', 57) GO print 'Processed 100 total records'INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'112', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'114', 60) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'115', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'116', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'124', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'125', 43) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'126', 65) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'127', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (7, N'128', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'111', 71) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'112', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'113', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'114', 81) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'115', 95) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'116', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'117', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'119', 65) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'120', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'121', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'122', 66) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'123', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'124', 60) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'125', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'126', 90) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'127', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (8, N'128', 76) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'111', 68) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'112', 72) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'114', 45) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'115', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'116', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'117', 66) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'118', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'121', 92) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'122', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'123', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'124', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'125', 60) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'126', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (9, N'127', 79) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'111', 71) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'112', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'113', 77) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'114', 81) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'116', 96) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'117', 50) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'118', 40) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'119', 63) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'120', 92) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'121', 57) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'122', 69) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'123', 80) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'124', 62) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'125', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'127', 92) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (10, N'128', 73) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (11, N'124', 99) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (12, N'122', 100) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (12, N'126', 99) INSERT [dbo].[SC] ([S#], [C#], [SCORE]) VALUES (12, N'124', 97)/****** Object: Table [dbo].[COURSE] Script Date: 05/16/2018 16:27:24 ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[COURSE]( [C#] [int] NOT NULL, [CNAME] [nvarchar](50) NOT NULL, [T#] [nvarchar](50) NOT NULL, PRIMARY KEY CLUSTERED ( [C#] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (111, N'语文1', N'50') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (112, N'语文2', N'51') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (113, N'数学1', N'52') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (114, N'数学2', N'53') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (115, N'英语1', N'54') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (116, N'英语2', N'55') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (117, N'生物1', N'56') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (118, N'生物2', N'57') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (119, N'物理1', N'58') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (120, N'物理2', N'59') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (121, N'化学1', N'60') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (122, N'化学2', N'61') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (123, N'政治1', N'50') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (124, N'政治2', N'54') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (125, N'历史1', N'58') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (126, N'历史2', N'53') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (127, N'地理1', N'56') INSERT [dbo].[COURSE] ([C#], [CNAME], [T#]) VALUES (128, N'地理2', N'60')
View Code
二、表的数据截图
1、学生表 ：学号、姓名、姓别
2、教师表：编号，名称
3、课程表：课程编号、课程、老师编号
4、分数表：学号、课程号、分数
5、数据准备好后正式开始题目
--1、查询“111”课程比“112”课程成绩高的所有学生的学号；/* EXISTS 版本 */SELECT t1.s#FROM sc t1WHERE t1.c# = 111 AND EXISTS ( SELECT 1 FROM sc t2 WHERE t2.c# = 112 AND t1.s# = t2.s# --要求同一个学生 所以有t1.s# = t2.s# AND t1.score > t2.score );/* 子查询版本 */SELECT a.s#FROM (SELECT t1.s#,t1.score FROM sc t1 WHERE t1.c# = 111) aINNER JOIN (SELECT t2.s#,t2.score FROM sc t2 WHERE t2.c# = 112) b ON a.s# = b.s#WHERE a.score > b.score;--2、查询平均成绩大于60分的同学的学号和平均成绩；SELECT t1.s# AS ST_CODE, AVG(t1.score) AS CU_AVGFROM sc t1GROUP BY t1.s# HAVING AVG(t1.score) > 60 --3、查询所有同学的学号、姓名、选课数、总成绩；/* group by 的时候要注意选出来的字段是不是都是聚合函数或者分组的字段 */SELECT t1.s# AS ST_DOCE, MIN(t1.sname) AS ST_NAME, COUNT(t2.c#) AS CU_NUM, SUM(t2.score) AS CU_SUMFROM STUDENT t1LEFT JOIN SC t2 ON t1.s# = t2.s#GROUP BY t1.s#--4、查询姓“李”的老师的个数；SELECT COUNT(t1.t#) AS 李_NUMFROM teacher t1WHERE t1.tname LIKE '李%'--5、查询没学过'陈奕迅'老师课的同学的学号、姓名；/* 一对多关系的就会出现比较坑爹的情况，记得用distinct并且要考虑是不是要做一个子查询 *//* 当正面来可能比较困难的时候就要反方面来进行 *//* EXISTS版 */SELECT t5.s# AS ST_CODE, t5.sname AS ST_NAMEFROM student t5WHERE NOT EXISTS ( SELECT 1 FROM ( SELECT DISTINCT t1.s# AS ST_CODE, t1.sname AS ST_NAME, t4.tname FROM student t1 LEFT JOIN sc t2 ON t1.s# = t1.s# LEFT JOIN course t3 ON t2.c# = t3.c# LEFT JOIN teacher t4 ON t3.t# = t4.t# WHERE t4.tname = '陈奕迅' ) WHERE s# = t5.s# );/* 子查询版 */SELECT t5.s# AS ST_CODE, t5.sname AS ST_NAMEFROM student t5WHERE t5. EXISTS ( SELECT 1 FROM ( SELECT DISTINCT t1.s# AS ST_CODE, t1.sname AS ST_NAME, t4.tname FROM student t1 LEFT JOIN sc t2 ON t1.s# = t1.s# LEFT JOIN course t3 ON t2.c# = t3.c# LEFT JOIN teacher t4 ON t3.t# = t4.t# WHERE t4.tname = '陈奕迅' ) WHERE s# = t5.s# ); --6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；--不能在where条件下面写WHERE t2.c# = 111 and t2.c# = 112 因为是对应同一条记录的SELECT DISTINCT t1.s#,t1.snameFROM student t1LEFT JOIN sc t2 ON t1.s# = t2.s#WHERE t2.c# = 111INTERSECT
