SQL再理解

--create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
--insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
--insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
--insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
--insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
--insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
--insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
--insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
--insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
--create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
--insert into Course values('01' , N'语文' , '02')
--insert into Course values('02' , N'数学' , '01')
--insert into Course values('03' , N'英语' , '03')
--create table Teacher(T# varchar(10),Tname nvarchar(10))
--insert into Teacher values('01' , N'张三')
--insert into Teacher values('02' , N'李四')
--insert into Teacher values('03' , N'王五')
--create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
--insert into SC values('01' , '01' , 80)
--insert into SC values('01' , '02' , 90)
--insert into SC values('01' , '03' , 99)
--insert into SC values('02' , '01' , 70)
--insert into SC values('02' , '02' , 60)
--insert into SC values('02' , '03' , 80)
--insert into SC values('03' , '01' , 80)
--insert into SC values('03' , '02' , 80)
--insert into SC values('03' , '03' , 80)
--insert into SC values('04' , '01' , 50)
--insert into SC values('04' , '02' , 30)
--insert into SC values('04' , '03' , 20)
--insert into SC values('05' , '01' , 76)
--insert into SC values('05' , '02' , 87)
--insert into SC values('06' , '01' , 31)
--insert into SC values('06' , '03' , 34)
--insert into SC values('07' , '02' , 89)
--insert into SC values('07' , '03' , 98)

1、通过外连接把不在一行的数据，规整到一行。

select s1.s#, s1.c#, s1.score, s2.s#, s2.c#, s2.score from sc s1, sc s2 where s1.s# = s2.s#(+) and s1.c#='01' and s2.c#='02'and s1.score < s2.score

 

2.group by having 

select s#, avg(score) from sc where s#='06' group by s# having avg(score) <=60

3. 学过01课程，没有学02课程的数据

 select * from sc s1 where s1.c# ='01' and s1.s# not in (select s2.s# from sc s2 where s2.c#='02')

4.查询参加所有课程的学生(group by having)

select s1.s# from sc s1 group by s1.s# having(count(s1.c#) >= (select count(1) from course))

5.查询至少有一门课与学号为"01"的同学所学相同的同学的信息 

select distinct s2.s# from sc s2 where s2.c# in (select s1.c# from sc s1 where s1.s#='01') and s2.s# !='01'

6.查询和"01"号的同学学习的课程完全相同的其他同学的信息 

select s1.s# from sc s1 group by s1.s# having count(1) = (select count(1) from sc s2 where s2.s#= '01' group by s2.s#) and s1.s# != '01'

7.查询没学过"张三"老师讲授的任一门课程的学生姓名 

select a.Sname naem
from student as a 
 where a.s# not in (
 select distinct(b.s#) from sc as b,teacher as c,course as d
 where c.t#=d.t# and c.tname='张三' and b.c#=d.c# )

8.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 

select s1.s#,avg(s1.score) from sc s1, sc s2 where s1.c# = s2.c# and s1.s# = s2.s# and s1.score < 60 and s2.score < 60 group by s1.s#

9.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from (
select s2.s# s##, avg(s2.score) avgscore from sc s2 group by s2.s# order by sum(s2.score)
) s1, sc s3 where s1.s##(+) = s3.s# 

10.查询各科成绩最高分、最低分和平均分及及格率

select c##, a a1 , rs rs1 , sum(rs) over(partition by c##) totalc#,rs/sum(rs) over(partition by c##) percent from(

select s1.c# c##, (CASE
            WHEN s1.score < 60 then
             '及格一下'
            WHEN s1.score >= 60 THEN
             '及格以上' END) a,COUNT(1) rs from sc s1
   group by  (CASE
            WHEN s1.score < 60 then
             '及格一下'
            WHEN s1.score >= 60 THEN
             '及格以上' END) , s1.c#
)

 

8.按各科成绩进行排序，并显示排名

select s1.s#, s1.c#,s1.score,row_number()over(partition by s1.c# order by s1.score) rn from sc s1
 

9.查询学生的总成绩并进行排名

select s1.s#, s1.c#,s1.score,sum(s1.score) over(order by s1.s#)  from sc s1 order by sum(s1.score) over(order by s1.s#) 
select s1.s#, s1.c#,s1.score,avg(s1.score) over(order by s1.s# desc)  from sc s1 order by avg(s1.score) over(order by s1.s# desc) 
 

10.查询各科成绩前三名的记录

select s#, c#, score, rn from(
select s1.s#, s1.c#,s1.score,row_number() over(partition by s1.c# order by s1.score desc) rn from sc s1
) where rn <4

11.查询出只有两门课程的全部学生的学号和姓名
select * from (
select s1.s#, s1.c#,s1.score, count(s1.s# ) over(partition by s1.s# order by s1.s#) num from sc s1 
) where num = 2

12.查询男生和女生的人数

select 
(case when ssex='男' then 'nan'
when ssex='女' then 'nv' end ) sex,
count(1) rs
from student
group by 
(case when ssex='男' then 'nan'
when ssex='女' then 'nv' end )

13.查询两门以上不及格课程的同学的学号及其平均成绩 

select distinct s# from ( 
select s1.s#, s1.c#, max(s1.score) score,count(s1.s#) over(partition by s1.s# order by s1.s#) sn from sc s1 where s1.score < 60 group by s1.s#, s1.c#
) where sn > 1