S (SNO,SNAME) 学生关系。SNO 为学号, SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
1. 找出没有选修过“CC”老师讲授课程的所有学生姓名
分析:
--C和SC连表
--查连表后CName==cc的老师的学生编号SNO
--查S表 SNO不在上面查到的SNO
SELECT * FROM S WHERE SNO NOT IN( select SNO from SC WHERE CNO in( select CNO from C where CTEACHER = '王老师' ) )
下面把数据库和记录贴出来
USE [StudentCourse] GO /****** Object: Table [dbo].[SC] Script Date: 06/02/2019 12:45:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SC]( [ID] [int] IDENTITY(1,1) NOT NULL, [SNO] [nvarchar](50) NULL, [CNO] [nvarchar](50) NULL, [SCGRADE] [int] NULL, CONSTRAINT [PK_SC] PRIMARY KEY CLUSTERED ( [ID] 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 IDENTITY_INSERT [dbo].[SC] ON INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (1, N'1', N'5', 23) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (2, N'2', N'6', 4) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (3, N'4', N'3', 12) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (4, N'3', N'5', 53) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (5, N'5', N'7', 67) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (6, N'1', N'2', 87) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (7, N'6', N'4', 97) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (8, N'7', N'6', 56) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (9, N'9', N'3', 87) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (10, N'1', N'2', 98) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (11, N'3', N'6', 78) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (12, N'5', N'7', 93) INSERT [dbo].[SC] ([ID], [SNO], [CNO], [SCGRADE]) VALUES (13, N'7', N'8', 94) SET IDENTITY_INSERT [dbo].[SC] OFF /****** Object: Table [dbo].[S] Script Date: 06/02/2019 12:45:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[S]( [ID] [int] IDENTITY(1,1) NOT NULL, [SNO] [nvarchar](50) NULL, [SNAME] [nvarchar](50) NULL, CONSTRAINT [PK_S] PRIMARY KEY CLUSTERED ( [ID] 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 IDENTITY_INSERT [dbo].[S] ON INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (1, N'1', N'张三') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (2, N'2', N'李四') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (3, N'3', N'王五') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (4, N'4', N'赵六') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (5, N'5', N'陈七') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (6, N'6', N'孙八') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (7, N'7', N'周九') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (8, N'8', N'田十') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (9, N'9', N'李白') INSERT [dbo].[S] ([ID], [SNO], [SNAME]) VALUES (10, N'10', N'黄飞') SET IDENTITY_INSERT [dbo].[S] OFF /****** Object: Table [dbo].[C] Script Date: 06/02/2019 12:45:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[C]( [ID] [int] IDENTITY(1,1) NOT NULL, [CNO] [nvarchar](50) NULL, [CNAME] [nvarchar](50) NULL, [CTEACHER] [nvarchar](50) NULL, CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED ( [ID] 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 IDENTITY_INSERT [dbo].[C] ON INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (1, N'1', N'语文', N'张老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (2, N'2', N'数学', N'万老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (3, N'3', N'外语', N'王老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (4, N'4', N'物理', N'唐老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (5, N'5', N'化学', N'赵老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (6, N'6', N'生物', N'宋老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (7, N'7', N'政治', N'葛老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (8, N'8', N'历史', N'郭老师') INSERT [dbo].[C] ([ID], [CNO], [CNAME], [CTEACHER]) VALUES (9, N'9', N'地理', N'尹老师') SET IDENTITY_INSERT [dbo].[C] OFF