数据库题目是这样的，如下图
CREATE TABLE [dbo].[ClassInfo]( [ClassId] [int] NULL, [ClassName] [varchar](50) NULL, [Manager] [varchar](50) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[CourseInfo]( [CourseID] [varchar](50) NULL, [CourseName] [varchar](50) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[ScoreInfo]( [StudentId] [varchar](50) NULL, [CourseId] [varchar](50) NULL, [Score] [int] NULL ) ON [PRIMARY] CREATE TABLE [dbo].[StudentInfo]( [StudentID] [varchar](50) NULL, [ClassID] [int] NULL, [StudentName] [varchar](50) NULL, [BirthDate] [date] NULL, [CardID] [varchar](50) NULL ) ON [PRIMARY]
INSERT [dbo].[ClassInfo] ([ClassId], [ClassName], [Manager]) VALUES (14, N‘初一1班‘, N‘张三‘) INSERT [dbo].[ClassInfo] ([ClassId], [ClassName], [Manager]) VALUES (15, N‘初二1班‘, N‘李四‘) INSERT [dbo].[ClassInfo] ([ClassId], [ClassName], [Manager]) VALUES (16, N‘初三1班‘, N‘王五‘) INSERT [dbo].[ClassInfo] ([ClassId], [ClassName], [Manager]) VALUES (17, N‘初四1班‘, N‘赵五‘) INSERT [dbo].[CourseInfo] ([CourseID], [CourseName]) VALUES (N‘1001‘, N‘语文‘) INSERT [dbo].[CourseInfo] ([CourseID], [CourseName]) VALUES (N‘1002‘, N‘数学‘) INSERT [dbo].[CourseInfo] ([CourseID], [CourseName]) VALUES (N‘1003‘, N‘物理‘) INSERT [dbo].[CourseInfo] ([CourseID], [CourseName]) VALUES (N‘1004‘, N‘化学‘) INSERT [dbo].[ScoreInfo] ([StudentId], [CourseId], [Score]) VALUES (N‘071096120‘, N‘1001‘, 80) INSERT [dbo].[ScoreInfo] ([StudentId], [CourseId], [Score]) VALUES (N‘071096121‘, N‘1001‘, 86) INSERT [dbo].[ScoreInfo] ([StudentId], [CourseId], [Score]) VALUES (N‘071096122‘, N‘1002‘, 87) INSERT [dbo].[ScoreInfo] ([StudentId], [CourseId], [Score]) VALUES (N‘071096123‘, N‘1003‘, 90) INSERT [dbo].[StudentInfo] ([StudentID], [ClassID], [StudentName], [BirthDate], [CardID]) VALUES (N‘071096120‘, 14, N‘刘备‘, CAST(0xD4160B00 AS Date), N‘410898199010018907‘) INSERT [dbo].[StudentInfo] ([StudentID], [ClassID], [StudentName], [BirthDate], [CardID]) VALUES (N‘071096121‘, 15, N‘关羽‘, CAST(0xC2190B00 AS Date), N‘410898199210208907‘) INSERT [dbo].[StudentInfo] ([StudentID], [ClassID], [StudentName], [BirthDate], [CardID]) VALUES (N‘071096122‘, 16, N‘张飞‘, CAST(0x551C0B00 AS Date), N‘410898199210208907‘) INSERT [dbo].[StudentInfo] ([StudentID], [ClassID], [StudentName], [BirthDate], [CardID]) VALUES (N‘071096123‘, 17, N‘吕布‘, CAST(0xFE1D0B00 AS Date), N‘410898199210208907‘) INSERT [dbo].[StudentInfo] ([StudentID], [ClassID], [StudentName], [BirthDate], [CardID]) VALUES (N‘071096124‘, 15, N‘夏侯惇‘, CAST(0xBA200B00 AS Date), N‘410898199210208907‘)
select ClassInfo.ClassName as 班级名称,ClassInfo.Manager as 班级负责人,StudentInfo.StudentName as 学生姓名, StudentInfo.BirthDate as 出生年月日,StudentInfo.CardID as 身份证号 from ClassInfo inner join StudentInfo on StudentInfo.ClassID=ClassInfo.ClassId where classINfo.ClassId=15 order by BirthDate asc
2.统计每个班级的学生个数，并按照学生数量从大到小排列，要求显示字段：班级名称，班级人数，班级负责人
select b.classname as 班级名称,b.manager as 班级负责人 ,count(1) as 班级人数 from StudentInfo a inner join ClassInfo b on a.ClassID=b.ClassId group by b.classname,b.manager order by 班级人数 desc
select a.ClassName as 班级名称,c.CourseName as 课程名称,count(1) as 学生个数 from ClassInfo a inner join StudentInfo b on a.ClassId=b.ClassID inner join ScoreInfo d on d.StudentId=b.StudentID inner join CourseInfo c on c.CourseID=d.CourseId where d.Score>80 and c.CourseID=‘1001‘ group by a.ClassName ,c.CourseName
delete from StudentInfo where StudentID=‘071096120‘ delete from ScoreInfo where StudentID=‘071096120‘
update ScoreInfo set Score=100 where CourseId=‘1001‘ and exists(select StudentInfo.StudentID from StudentInfo where StudentInfo.ClassID=15)
6.sql查询学生名称重复
select studentname,count(*) from StudentInfo group by StudentName having count(*)>1
原文：https://www.cnblogs.com/652769324qq/p/12070725.html
