Sql 语法练习

select * from Student
select * from Class
select * from Score
select * from Subject


--1、查询出和张三住在同一个地方的学生信息
select * from Student where StuAddress=
(select StuAddress from Student where StuName='张三')

--2、查询年龄大于李四的学生信息
select * from Student where StuAge>
(select StuAge from Student where StuName='李四')

--3、查询和张三在同一个班级的学生信息
select * from Student where ClassID=
(select ClassID from Student where StuName='张三')

--4、查询出所有脱产班的学生信息
select * from Student where ClassID in
(select ClassID from Class where ClassType='脱产')

--5、查询出没有参加过任何考试的学生信息 
select *  from Student where StuID not in
(select StuID from score)

--6、把和张三在同一个班级的所有学生的JAVA成绩加10分
update Score set  Marks+=10 where StuID in 
(select StuID from Student where ClassID=
    (select ClassID from Student where StuName='张三')
)
and SubId=(select SubId from Subject where SubName='JAVA')

--7、查询比张三的JAVA成绩好的学生信息
select * from Student where StuID in
(select StuID from Score where Marks>
    (select Marks from Score where StuID=(select StuID from Student where StuName='张三')
    and SubId=(select SubId from Subject where SubName='JAVA')
    )
)

--8、查询比张三的JAVA成绩好的学生所在的班级的信息
select * from Class where ClassID in 
(select ClassID from Student where StuID in
    (select StuID from Score where Marks>
        (select Marks from Score where StuID=(select StuID from Student where StuName='张三')
        and SubId=(select SubId from Subject where SubName='JAVA')
        )
    )
)

--9、查询比S1T21班所有学生年龄大的“周末班”的学员信息
select * from Student where StuID in
(select StuID from Student where StuAge>
    (select max(StuAge) from Student where ClassID in
        (select ClassID from Class where ClassType='周末'
        ) 
    )
)


--10、查询统计出每个脱产班的班级编号、班级类型、开班日期、JAVA平均分、C#平均分、SQL平均分、HTML平均分
select c.ClassID 班级编号,c.ClassType 班级类型,c.ClassDate 班级日期 ,
avg(case SubName when 'JAVA' then Marks end) JAVA平均分,
avg(case SubName when 'C#' then Marks end) C#平均分,
avg(case SubName when 'SQL' then Marks end) SQL平均分,
avg(case SubName when 'HTML' then Marks end) HTML平均分,
avg(Marks) 平均分
from Student st
inner join Class c on st.ClassID=c.ClassID
inner join Score sc on sc.StuID=st.StuID
inner join Subject su  on su.SubId=sc.SubId
where c.ClassType='脱产'
group by c.ClassID,c.ClassType,c.ClassDate 


--11、查询出JAVA成绩比S1T21班JAVA成绩最高分要高的学员信息
select * from Student where StuID in(
select StuID from Score where Marks>(
  select max(Marks) from Score where SubId=(select SubId from Subject where SubName='JAVA')
  and StuID in(select StuID from Student where ClassID='S1T21')
  )
  and SubId=(select SubId from Subject where SubName='JAVA')
)

--12、查询出JAVA成绩没有及格的学员信息
select * from Student where StuID in(
  select StuID from Score where Marks<60
  and  SubId=(select SubId from Subject where SubName='JAVA')
)
--13、查询出姓“王”的学生的JAVA成绩和C#成绩
select * from Score where StuID in (
  select StuID from Student where StuName like '王%'
)

select st.StuName 姓名,
(case SubName when 'JAVA' then Marks end) JAVA成绩,
(case SubName when 'C#' then Marks end) C#成绩
from Score sc
inner join Student st on sc.StuID=st.StuID
inner join Subject su on sc.SubId=su.SubId
where st.StuID in (
  select StuID from Student where StuName like '王%'
)
group by st.StuName,(case SubName when 'JAVA' then Marks end),(case SubName when 'C#' then Marks end)

--14、SQLSERVER分页查询：
--A、查询出学生信息表中第1-3条信息（第一页）；
select top 3 * from Student where  StuID not in(
 select top ((1-1)*3) StuID from Student
)
--B、查询出学生信息表中第4-6条信息（第二页）；
select top 3 * from Student where StuID not in(
 select top ((2-1)*3) StuID from Student
)
--15、查询没有参加过JAVA考试的学生所在的班级编号、班级类型、开班日期
select st.StuName ,c.ClassID,c.ClassType,c.ClassDate from Student st
inner join Class c on c.ClassID= st.ClassID
where StuID not in (
 select StuID from Score where SubId=(select SubId from Subject where SubName='JAVA')
)

--16、查询出和“张三”在同一个班的班级人数，平均年龄
select ClassID,count(*) 班级人数,avg(StuAge) 平均年龄 from Student where ClassID=(
  select ClassID from student where StuName='张三'
)
group by ClassID

--17、查询出学生姓名、班级编号、考试科目名称、考试成绩（使用连接查询）
select st.StuName,st.ClassID,su.SubName,sc.Marks from Score sc
inner join Student st on sc.StuID=st.StuID
inner join Subject su on sc.SubId=su.SubId

--18、查询出班级人数最多的班级编号、班级类型、开班日期
select * from Class where ClassID in (
 select ClassID from Student group by ClassID having
 count(*)=(select top 1 count(*) num from Student group by ClassID order by num desc)
 )

--19、由于张三、李四、王五三名同学JAVA考试作弊，现将三名同学的成绩作废（删除）
delete from Score where StuID in (select StuID from Student where StuName in ('张三','李四','王五')) 
and SubId=(select SubId from Subject where SubName='JAVA')

--20、由于S1T23班参加的C#考试难度大，现将该班每个学生的C#成绩加10分
update Score set Marks+=10 where StuID in (select StuID from Student where ClassID='S1T23') 
and SubId=(select SubId from Subject where SubName='C#') 

--21、查询出年龄大于25并且家住湖北襄樊的学生的编号、姓名、班级编号、班级类型、开班日期
select st.StuID,st.StuName,st.ClassID,c.ClassType,c.ClassDate from Student st
inner join Class c on st.ClassID=c.ClassID
where StuID in (
 select StuID from Student where StuAge>25 and StuAddress='湖北襄樊'
)

--22、查询统计出2010-1-1以前开班的学生人数和平均年龄
select count(*) 人数,avg(StuAge) 平均年龄 from Student where ClassID in (
 select ClassID from  Class  where ClassDate<'2010-1-1'
)

--23、查询出每个班级JAVA成绩不及格的人数
select ClassID,count(StuID) JAVA成绩不及格的人数 from Student where StuID in (
 select StuID from Score where Marks<60
 and SubId=(select SubId from Subject where SubName='JAVA')
)
group by ClassID

select c.ClassID,
count(case when SubName='JAVA' and Marks<60 then Marks end) JAVA成绩不及格的人数
from Student st
inner join Class c on st.ClassID=c.ClassID
inner join Score sc on sc.StuID=st.StuID
inner join Subject su  on su.SubId=sc.SubId
group by c.ClassID



--24、查询出每个班级没有参加JAVA考试的学生人数
select ClassID,count(*) 没有参加JAVA考试的学生人数 from Student where StuID not in (
 select StuID from Score where SubId=(select SubId from Subject where SubName='JAVA')
)
group by ClassID

--25、根据学生的JAVA课程的分数，来评定级别:
--成绩<60 :★
--60<=成绩<70:★★
--70<=成绩<80:★★★
--80<=成绩<90:★★★★
--90<=成绩<=100:★★★★★
--并且显示评定的等级信息

select st.StuName 学生姓名,sc.Marks 学生JAVA成绩,
(case when Marks<60 then           '★'
when Marks>=60 and Marks<70 then   '★★'
when Marks>=80 and Marks<90 then   '★★★★'
when Marks>=90 and Marks<=100 then '★★★★★'
 else '×' end) JAVA课程的分数评级
from Score sc 
inner join Student st on sc.StuID=st.StuID
where SubId=(select SubId from Subject where SubName='JAVA')