某仪表上市公司.net-————Sql面试题

数据库题目是这样的，如下图

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

1.查询班级代码为15的学生信息，按照年龄从小到大的顺序排列，要求显示字段：班级名称，班级负责人，学生姓名，出生年月日，身份证号

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

3.统计每个班级课程代码为1001的成绩大于80分的学生个数，要求显示字段:班级名称，课程名称，学生个数

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 

4.删除学号为‘071096120’的学生及其相关所有数据

delete from StudentInfo where StudentID='071096120'
delete from ScoreInfo where StudentID='071096120'

5.班级代码为15的所有学生的课程编码为1001的成绩统一改为100分

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