问题及描述：
--1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
--3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
--4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
*/
--创建测试数据
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID 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、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a , SC b , SC c
where a.SID = b.SID and a.SID = c.SID and b.CID = '01' and c.CID = '02' and b.score > c.score
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where b.score > isnull(c.score,0)
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score 课程01的分数 ,c.score 课程02的分数 from Student a , SC b , SC c
where a.SID = b.SID and a.SID = c.SID and b.CID = '01' and c.CID = '02' and b.score < c.score
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.* , b.score 课程01的分数 ,c.score 课程02的分数 from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where isnull(b.score,0) < c.score
--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.SID
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) < 60
order by a.SID
--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select a.SID , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score
from Student a left join sc b
on a.SID = b.SID
group by a.SID , a.Sname
having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.SID
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。
select a.SID 学生编号 , a.Sname 学生姓名 , count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a , SC b
where a.SID = b.SID
group by a.SID,a.Sname
order by a.SID
--5.2、查询所有(包括有成绩和无成绩)的SQL。
select a.SID 学生编号 , a.Sname 学生姓名 , count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a left join SC b
on a.SID = b.SID
group by a.SID,a.Sname
order by a.SID
--6、查询"李"姓老师的数量
--方法1
select count(Tname) 李姓老师的数量 from Teacher where Tname like '李%'
--方法2
select count(Tname) 李姓老师的数量 from Teacher where left(Tname,1) = '李'
--7、查询学过"张三"老师授课的同学的信息
select distinct Student.* from Student , SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三'
order by Student.SID
--8、查询没学过"张三"老师授课的同学的信息
select m.* from Student m where SID not in (select distinct SC.SID from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三') order by m.SID
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '01' and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') order by Student.SID
--方法2
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '02' and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '01') order by Student.SID
--方法3
select m.* from Student m where SID in
(
select SID from
(
select distinct SID from SC where CID = '01'
union all
select distinct SID from SC where CID = '02'
) t group by SID having count(1) = 2
)
order by m.SID
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '01' and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') order by Student.SID
--方法2
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '01' and Student.SID not in (Select SC_2.SID from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') order by Student.SID
--11、查询没有学全所有课程的同学的信息
--11.1、
select Student.*
from Student , SC
where Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)
--11.2
select Student.*
from Student left join SC
on Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct Student.* from Student , SC where Student.SID = SC.SID and SC.CID in (select CID from SC where SID = '01') and Student.SID <> '01'
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select Student.* from Student where SID in
(select distinct SC.SID from SC where SID <> '01' and SC.CID in (select distinct CID from SC where SID = '01')
group by SC.SID having count(1) = (select count(1) from SC where SID='01'))
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select student.* from student where student.SID not in
(select distinct sc.SID from sc , course , teacher where sc.CID = course.CID and course.TID = teacher.TID and teacher.tname = '张三')
order by student.SID
--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.SID , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc
where student.SID = SC.SID and student.SID in (select SID from SC where score < 60 group by SID having count(1) >= 2)
group by student.SID , student.sname
--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select student.* , sc.CID , sc.score from student , sc
where student.SID = SC.SID and sc.score < 60 and sc.CID = '01'
order by sc.score desc
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--17.1 SQL 2000 静态
select a.SID 学生编号 , a.Sname 学生姓名 ,
max(case c.Cname when '语文' then b.score else null end) 语文 ,
max(case c.Cname when '数学' then b.score else null end) 数学 ,
max(case c.Cname when '英语' then b.score else null end) 英语 ,
cast(avg(b.score) as decimal(18,2)) 平均分
from Student a
left join SC b on a.SID = b.SID
left join Course c on b.CID = c.CID
group by a.SID , a.Sname
order by 平均分 desc
--17.2 SQL 2000 动态
declare @sql nvarchar(4000)
set @sql = 'select a.SID ' + '学生编号' + ' , a.Sname ' + '学生姓名'
select @sql = @sql + ',max(case c.Cname when '''+Cname+''' then b.score else null end) '+Cname+' '
from (select distinct Cname from Course) as t
set @sql = @sql + ' , cast(avg(b.score) as decimal(18,2)) ' + '平均分' + ' from Student a left join SC b on a.SID = b.SID left join Course c on b.CID = c.CID
group by a.SID , a.Sname order by ' + '平均分' + ' desc'
exec(@sql)
--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--方法1
select m.CID 课程编号 , m.Cname 课程名称 ,
max(n.score) 最高分 ,
min(n.score) 最低分 ,
cast(avg(n.score) as decimal(18,2)) 平均分 ,
cast((select count(1) from SC where CID = m.CID and score >= 60)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 及格率 ,
cast((select count(1) from SC where CID = m.CID and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 中等率 ,
cast((select count(1) from SC where CID = m.CID and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优良率 ,
cast((select count(1) from SC where CID = m.CID and score >= 90)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优秀率
from Course m , SC n
where m.CID = n.CID
group by m.CID , m.Cname
order by m.CID
--方法2
select m.CID 课程编号 , m.Cname 课程名称 ,
(select max(score) from SC where CID = m.CID) 最高分 ,
(select min(score) from SC where CID = m.CID) 最低分 ,
(select cast(avg(score) as decimal(18,2)) from SC where CID = m.CID) 平均分 ,
cast((select count(1) from SC where CID = m.CID and score >= 60)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 及格率,
cast((select count(1) from SC where CID = m.CID and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 中等率 ,
cast((select count(1) from SC where CID = m.CID and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优良率 ,
cast((select count(1) from SC where CID = m.CID and score >= 90)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优秀率
from Course m
order by m.CID
--19、按各科成绩进行排序，并显示排名
--19.1 sql 2000用子查询完成
--Score重复时保留名次空缺
select t.* , px = (select count(1) from SC where CID = t.CID and score > t.score) + 1 from sc t order by t.cid , px
--Score重复时合并名次
select t.* , px = (select count(distinct score) from SC where CID = t.CID and score >= t.score) from sc t order by t.cid , px
--19.2 sql 2005用rank,DENSE_RANK完成
--Score重复时保留名次空缺(rank完成)
select t.* , px = rank() over(partition by cid order by score desc) from sc t order by t.CID , px
--Score重复时合并名次(DENSE_RANK完成)
select t.* , px = DENSE_RANK() over(partition by cid order by score desc) from sc t order by t.CID , px
--20、查询学生的总成绩并进行排名
--20.1 查询学生的总成绩
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
order by 总成绩 desc
--20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (select count(1) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t2 where 总成绩 > t1.总成绩) + 1 from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t1
order by px
select t1.* , px = (select count(distinct 总成绩) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t2 where 总成绩 >= t1.总成绩) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t1
order by px
--20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(order by 总成绩 desc) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t
order by px
select t.* , px = DENSE_RANK() over(order by 总成绩 desc) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(sum(score),0) 总成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t
order by px
--21、查询不同老师所教不同课程平均分从高到低显示
select m.TID , m.Tname , cast(avg(o.score) as decimal(18,2)) avg_score
from Teacher m , Course n , SC o
where m.TID = n.TID and n.CID = o.CID
group by m.TID , m.Tname
order by avg_score desc
--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--22.1 sql 2000用子查询完成
--Score重复时保留名次空缺
select * from (select t.* , px = (select count(1) from SC where CID = t.CID and score > t.score) + 1 from sc t) m where px between 2 and 3 order by m.cid , m.px
--Score重复时合并名次
select * from (select t.* , px = (select count(distinct score) from SC where CID = t.CID and score >= t.score) from sc t) m where px between 2 and 3 order by m.cid , m.px
--22.2 sql 2005用rank,DENSE_RANK完成
--Score重复时保留名次空缺(rank完成)
select * from (select t.* , px = rank() over(partition by cid order by score desc) from sc t) m where px between 2 and 3 order by m.CID , m.px
--Score重复时合并名次(DENSE_RANK完成)
select * from (select t.* , px = DENSE_RANK() over(partition by cid order by score desc) from sc t) m where px between 2 and 3 order by m.CID , m.px
--23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
--23.1 统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60
--横向显示
select Course.CID 课程编号 , Cname as 课程名称 ,
sum(case when score >= 85 then 1 else 0 end) 85-100 ,
sum(case when score >= 70 and score < 85 then 1 else 0 end) 70-85 ,
sum(case when score >= 60 and score < 70 then 1 else 0 end) 60-70 ,
sum(case when score < 60 then 1 else 0 end) 0-60
from sc , Course
where SC.CID = Course.CID
group by Course.CID , Course.Cname
order by Course.CID
--纵向显示1(显示存在的分数段)
select m.CID 课程编号 , m.Cname 课程名称 , 分数段 = (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) ,
count(1) 数量
from Course m , sc n
where m.CID = n.CID
group by m.CID , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.CID , m.Cname , 分数段
--纵向显示2(显示存在的分数段，不存在的分数段用0显示)
select m.CID 课程编号 , m.Cname 课程名称 , 分数段 = (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) ,
count(1) 数量
from Course m , sc n
where m.CID = n.CID
group by all m.CID , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.CID , m.Cname , 分数段
--23.2 统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , <60 及所占百分比
--横向显示
select m.CID 课程编号, m.Cname 课程名称,
(select count(1) from SC where CID = m.CID and score < 60) 0-60 ,
cast((select count(1) from SC where CID = m.CID and score < 60)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比 ,
(select count(1) from SC where CID = m.CID and score >= 60 and score < 70) 60-70 ,
cast((select count(1) from SC where CID = m.CID and score >= 60 and score < 70)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比 ,
(select count(1) from SC where CID = m.CID and score >= 70 and score < 85) 70-85 ,
cast((select count(1) from SC where CID = m.CID and score >= 70 and score < 85)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比 ,
(select count(1) from SC where CID = m.CID and score >= 85) 85-100 ,
cast((select count(1) from SC where CID = m.CID and score >= 85)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比
from Course m
order by m.CID
--纵向显示1(显示存在的分数段)
select m.CID 课程编号 , m.Cname 课程名称 , 分数段 = (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) ,
count(1) 数量 ,
cast(count(1) * 100.0 / (select count(1) from sc where CID = m.CID) as decimal(18,2)) 百分比
from Course m , sc n
where m.CID = n.CID
group by m.CID , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.CID , m.Cname , 分数段
--纵向显示2(显示存在的分数段，不存在的分数段用0显示)
select m.CID 课程编号 , m.Cname 课程名称 , 分数段 = (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) ,
count(1) 数量 ,
cast(count(1) * 100.0 / (select count(1) from sc where CID = m.CID) as decimal(18,2)) 百分比
from Course m , sc n
where m.CID = n.CID
group by all m.CID , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.CID , m.Cname , 分数段
--24、查询学生平均成绩及其名次
--24.1 查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (select count(1) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(cast(avg(score) as decimal(18,2)),0) 平均成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t2 where 平均成绩 > t1.平均成绩) + 1 from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(cast(avg(score) as decimal(18,2)),0) 平均成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t1
order by px
select t1.* , px = (select count(distinct 平均成绩) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(cast(avg(score) as decimal(18,2)),0) 平均成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t2 where 平均成绩 >= t1.平均成绩) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(cast(avg(score) as decimal(18,2)),0) 平均成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t1
order by px
--24.2 查询学生的平均成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(order by 平均成绩 desc) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(cast(avg(score) as decimal(18,2)),0) 平均成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t
order by px
select t.* , px = DENSE_RANK() over(order by 平均成绩 desc) from
(
select m.SID 学生编号 ,
m.Sname 学生姓名 ,
isnull(cast(avg(score) as decimal(18,2)),0) 平均成绩
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t
order by px
--25、查询各科成绩前三名的记录
--25.1 分数重复时保留名次空缺
select m.* , n.CID , n.score from Student m, SC n where m.SID = n.SID and n.score in
(select top 3 score from sc where CID = n.CID order by score desc) order by n.CID , n.score desc
--25.2 分数重复时不保留名次空缺，合并名次
--sql 2000用子查询实现
select * from (select t.* , px = (select count(distinct score) from SC where CID = t.CID and score >= t.score) from sc t) m where px between 1 and 3 order by m.Cid , m.px
--sql 2005用DENSE_RANK实现
select * from (select t.* , px = DENSE_RANK() over(partition by Cid order by score desc) from sc t) m where px between 1 and 3 order by m.CID , m.px
--26、查询每门课程被选修的学生数
select Cid , count(SID) 学生数 from sc group by CID
--27、查询出只有两门课程的全部学生的学号和姓名
select Student.SID , Student.Sname
from Student , SC
where Student.SID = SC.SID
group by Student.SID , Student.Sname
having count(SC.CID) = 2
order by Student.SID
--28、查询男生、女生人数
select count(Ssex) as 男生人数 from Student where Ssex = N'男'
select count(Ssex) as 女生人数 from Student where Ssex = N'女'
select sum(case when Ssex = N'男' then 1 else 0 end) 男生人数 ,sum(case when Ssex = N'女' then 1 else 0 end) 女生人数 from student
select case when Ssex = N'男' then N'男生人数' else N'女生人数' end 男女情况 , count(1) 人数 from student group by case when Ssex = N'男' then N'男生人数' else N'女生人数' end
--29、查询名字中含有"风"字的学生信息
select * from student where sname like N'%风%'
select * from student where charindex(N'风' , sname) > 0
--30、查询同名同性学生名单，并统计同名人数
select Sname 学生姓名 , count(*) 人数 from Student group by Sname having count(*) > 1
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,'1990-01-01') = 0
select * from Student where datepart(yy,sage) = 1990
select * from Student where convert(varchar(4),sage,120) = '1990'
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select m.CID , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score
from Course m, SC n
where m.CID = n.CID
group by m.CID , m.Cname
order by avg_score desc, m.CID asc
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 85
order by a.SID
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select sname , score
from Student , SC , Course
where SC.SID = Student.SID and SC.CID = Course.CID and Course.Cname = N'数学' and score < 60
--35、查询所有学生的课程及分数情况；
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID
order by Student.SID , SC.CID
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and SC.score >= 70
order by Student.SID , SC.CID
--37、查询不及格的课程
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and SC.score < 60
order by Student.SID , SC.CID
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and SC.CID = '01' and SC.score >= 80
order by Student.SID , SC.CID
--39、求每门课程的学生人数
select Course.CID , Course.Cname , count(*) 学生人数
from Course , SC
where Course.CID = SC.CID
group by Course.CID , Course.Cname
order by Course.CID , Course.Cname
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
--40.1 当最高分只有一个时
select top 1 Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N'张三'
order by SC.score desc
--40.2 当最高分出现多个时
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N'张三' and
SC.score = (select max(SC.score) from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N'张三')
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--方法1
select m.* from SC m ,(select CID , score from SC group by CID , score having count(1) > 1) n
where m.CID= n.CID and m.score = n.score order by m.CID , m.score , m.SID
--方法2
select m.* from SC m where exists (select 1 from (select CID , score from SC group by CID , score having count(1) > 1) n
where m.CID= n.CID and m.score = n.score) order by m.CID , m.score , m.SID
--42、查询每门功成绩最好的前两名
select t.* from sc t where score in (select top 2 score from sc where CID = T.CID order by score desc) order by t.CID , t.score desc
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select Course.CID , Course.Cname , count(*) 学生人数
from Course , SC
where Course.CID = SC.CID
group by Course.CID , Course.Cname
having count(*) >= 5
order by 学生人数 desc , Course.CID
--44、检索至少选修两门课程的学生学号
select student.SID , student.Sname
from student , SC
where student.SID = SC.SID
group by student.SID , student.Sname
having count(1) >= 2
order by student.SID
--45、查询选修了全部课程的学生信息
--方法1 根据数量来完成
select student.* from student where SID in
(select SID from sc group by SID having count(1) = (select count(1) from course))
--方法2 使用双重否定来完成
select t.* from student t where t.SID not in
(
select distinct m.SID from
(
select SID , CID from student , course
) m where not exists (select 1 from sc n where n.SID = m.SID and n.CID = m.CID)
)
--方法3 使用双重否定来完成
select t.* from student t where not exists(select 1 from
(
select distinct m.SID from
(
select SID , CID from student , course
) m where not exists (select 1 from sc n where n.SID = m.SID and n.CID = m.CID)
) k where k.SID = t.SID
)
--46、查询各学生的年龄
--46.1 只按照年份来算
select * , datediff(yy , sage , getdate()) 年龄 from student
--46.2 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end 年龄 from student
--47、查询本周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--48、查询下周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
--49、查询本月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--50、查询下月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
drop table Student,Course,Teacher,SC
1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名
name course grade
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
select name from table group by name having min(grade) > 80
2. 现有学生表如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息
delete tablename where 自动编号 not in (
select min( 自动编号)
from tablename
group by 学号, 姓名, 课程编号, 课程名称, 分数
)
3. 一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合
select a.name, b.name
from team a, team b
where a.name < b.name
4. 请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。
请注意：TestDB 中有很多科目，都有1~12月份的发生额。
AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。
数据库名：JcyAudit ，数据集：Select * from TestDB
select a.*
from TestDB a,
(select Occmonth, max(DebitOccur) as Debit101ccur
from TestDB
where AccID='101'
group by Occmonth) b
where a.Occmonth = b.Occmonth and a.DebitOccur > b.Debit101ccur
5. 怎么把这样一个数据表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果？
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
select year,
(select amount from table m where month=1 and m.year=table.year) as m1,
(select amount from table m where month=2 and m.year=table.year) as m2,
(select amount from table m where month=3 and m.year=table.year) as m3,
(select amount from table m where month=4 and m.year=table.year) as m4
from table group by year
6. 有表A，结构如下：
p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。
请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。
select p_id,
sum(case when s_id=1 then p_num else 0 end) as s1_id,
sum(case when s_id=2 then p_num else 0 end) as s2_id,
sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id
下面进入正题。首先创建数据表：
学生表 Student
create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女')
成绩表 SC
create table SC(Sid varchar(10), Cid 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)
课程表 Course
create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03')
教师表 Teacher
create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五')
四张表之间的关联很简单：
表格关联
（以下题目的顺序和原文相对应）
MYSQL 实现：
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select s.*, a.score as score_01, b.score as score_02
from student s,
(select sid, score from sc where cid=01) a,
(select sid, score from sc where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid
+------+--------+---------------------+------+----------+----------+
| Sid | Sname | Sage | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
+------+--------+---------------------+------+----------+----------+
2 rows in set (0.00 sec)
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid, sname, avg(score) as avg_score
from student as s, sc
where s.sid = sc.sid
group by s.sid
having avg_score > 60
+------+--------+-----------+
| sid | sname | avg_score |
+------+--------+-----------+
| 01 | 赵雷 | 89.66667 |
| 02 | 钱电 | 70.00000 |
| 03 | 孙风 | 80.00000 |
| 05 | 周梅 | 81.50000 |
| 07 | 郑竹 | 93.50000 |
+------+--------+-----------+
5 rows in set (0.00 sec)
3. 查询在 SC 表存在成绩的学生信息
select * from student where sid in (select sid from sc where score is not null)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
这道题得用到left join或者right join，不能用where连接，因为题目说了要求有显示为null的，where是inner join，不会出现null，在这道题里会查不出第08号学生。
select s.sid, s.sname, count(cid) as 选课总数, sum(score) as 总成绩
from student as s left join sc
on s.sid = sc.sid
group by s.sid
+------+--------+--------------+-----------+
| sid | sname | 选课总数 | 总成绩 |
+------+--------+--------------+-----------+
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
| 08 | 王菊 | 0 | NULL |
+------+--------+--------------+-----------+
8 rows in set (0.00 sec)
4.1 查有成绩的学生信息
select s.sid, s.sname, count(*) as 选课总数, sum(score) as 总成绩,
sum(case when cid = 01 then score else null end) as score_01,
sum(case when cid = 02 then score else null end) as score_02,
sum(case when cid = 03 then score else null end) as score_03
from student as s, sc
where s.sid = sc.sid
group by s.sid
+------+--------+--------------+-----------+----------+----------+----------+
| sid | sname | 选课总数 | 总成绩 | score_01 | score_02 | score_03 |
+------+--------+--------------+-----------+----------+----------+----------+
| 01 | 赵雷 | 3 | 269.0 | 80.0 | 90.0 | 99.0 |
| 02 | 钱电 | 3 | 210.0 | 70.0 | 60.0 | 80.0 |
| 03 | 孙风 | 3 | 240.0 | 80.0 | 80.0 | 80.0 |
| 04 | 李云 | 3 | 100.0 | 50.0 | 30.0 | 20.0 |
| 05 | 周梅 | 2 | 163.0 | 76.0 | 87.0 | NULL |
| 06 | 吴兰 | 2 | 65.0 | 31.0 | NULL | 34.0 |
| 07 | 郑竹 | 2 | 187.0 | NULL | 89.0 | 98.0 |
+------+--------+--------------+-----------+----------+----------+----------+
7 rows in set (0.00 sec)
5. 查询「李」姓老师的数量
select count(tname) from teacher where tname like '李%'
+--------------+
| count(tname) |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
6. 查询学过「张三」老师授课的同学的信息
select * from student where sid in (
select sid from sc, course, teacher
where sc.cid = course.cid
and course.tid = teacher.tid
and tname = '张三'
)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
6 rows in set (0.00 sec)
原作者的写法里面用到了等号 =，虽然得到同样的结果，但是这样写不太好，因为不确定张三老师是不是只教授一门课（只不过现在的数据量太小了而已），in 适用于一个或多个返回结果的情况，适应性比等号更广。
select * from Student
where sid in(select distinct Sid from SC
where cid=(select Cid from Course
where Tid=(select Tid from Teacher where Tname='张三')))
7. 查询没有学全所有课程的同学的信息
select * from student where sid in (select sid from sc group by sid having count(cid) < 3)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
3 rows in set (0.00 sec)
9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
这道题号称是所有题目里最难的一道，我虽然做了出来，但是写法很麻烦，不必要。原作者写的很简洁：
select * from Student
where Sid in(
select Sid from SC
where Cid in (select Cid from SC where Sid = '01') and Sid <>'01'
group by Sid
having COUNT(Cid)>=3
)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
+------+--------+---------------------+------+
3 rows in set (0.00 sec)
我写的就太麻烦啦。。
select * from student where sid in (
select B.sid
from
(select sid,
sum(case when cid=01 then 1 else 0 end) as course_01,
sum(case when cid=02 then 1 else 0 end) as course_02,
sum(case when cid=03 then 1 else 0 end) as course_03
from sc where sid = 01 group by sid) as A,
(select sid,
sum(case when cid=01 then 1 else 0 end) as course_01,
sum(case when cid=02 then 1 else 0 end) as course_02,
sum(case when cid=03 then 1 else 0 end) as course_03
from sc where sid != 01 group by sid) as B
where A.course_01=B.course_01 and A.course_02=B.course_02 and A.course_03=B.course_03
)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
+------+--------+---------------------+------+
3 rows in set (0.00 sec)
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
和第9题基本一致，还是原作者写的好一些
select * from Student where Sid in(
select distinct Sid from SC where Cid in(
select Cid from SC where Sid='01'
)
)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
一般涉及到"任意"的都会用到not in这样的取反的结构：
select sname from student
where sname not in (
select s.sname
from student as s, course as c, teacher as t, sc
where s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid
and t.tname = '张三'
)
+--------+
| sname |
+--------+
| 吴兰 |
| 王菊 |
+--------+
2 rows in set (0.00 sec)
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.sid, s.sname, avg(score)
from student as s, sc
where s.sid = sc.sid and score<60
group by s.sid
having count(score)>=2
+------+--------+------------+
| sid | sname | avg(score) |
+------+--------+------------+
| 04 | 李云 | 33.33333 |
| 06 | 吴兰 | 32.50000 |
+------+--------+------------+
2 rows in set (0.00 sec)
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s.* ,score
from student as s, sc
where cid = 01
and score < 60
and s.sid=sc.sid
order by score desc
+------+--------+---------------------+------+-------+
| Sid | Sname | Sage | Ssex | score |
+------+--------+---------------------+------+-------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 |
+------+--------+---------------------+------+-------+
2 rows in set (0.00 sec)
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sid,
sum(case when cid=01 then score else null end) as score_01,
sum(case when cid=02 then score else null end) as score_02,
sum(case when cid=03 then score else null end) as score_03,
avg(score)
from sc group by sid
order by avg(score) desc
+------+----------+----------+----------+------------+
| sid | score_01 | score_02 | score_03 | avg(score) |
+------+----------+----------+----------+------------+
| 07 | NULL | 89.0 | 98.0 | 93.50000 |
| 01 | 80.0 | 90.0 | 99.0 | 89.66667 |
| 05 | 76.0 | 87.0 | NULL | 81.50000 |
| 03 | 80.0 | 80.0 | 80.0 | 80.00000 |
| 02 | 70.0 | 60.0 | 80.0 | 70.00000 |
| 04 | 50.0 | 30.0 | 20.0 | 33.33333 |
| 06 | 31.0 | NULL | 34.0 | 32.50000 |
+------+----------+----------+----------+------------+
7 rows in set (0.00 sec)
14. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
这道题熟练掌握case和sum的用法就没什么问题
select c.cid as 课程号, c.cname as 课程名称, count(*) as 选修人数,
max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分,
sum(case when score >= 60 then 1 else 0 end)/count(*) as 及格率,
sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as 中等率,
sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as 优良率,
sum(case when score >= 90 then 1 else 0 end)/count(*) as 优秀率
from sc, course c
where c.cid = sc.cid
group by c.cid
order by count(*) desc, c.cid asc
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 课程号 | 课程名称 | 选修人数 | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01 | 语文 | 6 | 80.0 | 31.0 | 64.50000 | 0.6667 | 0.3333 | 0.3333 | 0.0000 |
| 02 | 数学 | 6 | 90.0 | 30.0 | 72.66667 | 0.8333 | 0.0000 | 0.5000 | 0.1667 |
| 03 | 英语 | 6 | 99.0 | 20.0 | 68.50000 | 0.6667 | 0.0000 | 0.3333 | 0.3333 |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
3 rows in set (0.00 sec)
原作者的写法本质上和我是相同的，但是用了很多left join看起来有些冗余
select distinct A.Cid,Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 from SC A
left join Course on A.Cid=Course.Cid
left join (select Cid,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分 from SC group by Cid)B on A.Cid=B.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score>=60 then 1 else 0 end)*1.00)/COUNT(*))*100)及格率 from SC group by Cid)C on A.Cid=C.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score >=70 and score<80 then 1 else 0 end)*1.00)/COUNT(*))*100)中等率 from SC group by Cid)D on A.Cid=D.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score >=80 and score<90 then 1 else 0 end)*1.00)/COUNT(*))*100)优良率 from SC group by Cid)E on A.Cid=E.Cid
left join (select Cid,(convert(decimal(5,2),(sum(case when score >=90 then 1 else 0 end)*1.00)/COUNT(*))*100)优秀率
from SC group by Cid)F on A.Cid=F.Cid
15. 按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺
原题目是按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺。但是我没看明白什么意思，各科成绩如何排序？语文分数和数学分数有可比性吗？作者的写法是select *,RANK()over(order by score desc)排名 from SC，把所有的成绩都放到一块儿排序了，这没有意义，不可比。于是我修改了一下题目。
select s.*, rank_01, rank_02, rank_03, rank_total
from student s
left join (select sid, rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc
+------+--------+---------------------+------+---------+---------+---------+------------+
| Sid | Sname | Sage | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
+------+--------+---------------------+------+---------+---------+---------+------------+
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 | NULL | NULL | NULL | NULL |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | NULL | 2 | 2 | 1 |
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 1 | 1 | 1 | 2 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 3 | 3 | NULL | 3 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 1 | 4 | 3 | 4 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 4 | 5 | 3 | 5 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 5 | 6 | 6 | 6 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 6 | NULL | 5 | 7 |
+------+--------+---------------------+------+---------+---------+---------+------------+
8 rows in set (0.00 sec)
15.1 按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次
同样修改了一下题目。15题和15.1题的指向很明确了，就是rank()和dense_rank()的区别，也就是两个并列第一名之后的那个人是第三名(rank)还是第二名(dense_rank)的区别。
select s.*, rank_01, rank_02, rank_03, rank_total
from student s
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, dense_rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc
+------+--------+---------------------+------+---------+---------+---------+------------+
| Sid | Sname | Sage | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
+------+--------+---------------------+------+---------+---------+---------+------------+
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 | NULL | NULL | NULL | NULL |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | NULL | 2 | 2 | 1 |
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 1 | 1 | 1 | 2 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 2 | 3 | NULL | 3 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 1 | 4 | 3 | 4 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 3 | 5 | 3 | 5 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 4 | 6 | 5 | 6 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 5 | NULL | 4 | 7 |
+------+--------+---------------------+------+---------+---------+---------+------------+
8 rows in set (0.00 sec)
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select c.cid as 课程编号, c.cname as 课程名称, A.*
from course as c,
(select cid,
sum(case when score >= 85 then 1 else 0 end)/count(*) as 100_85,
sum(case when score >= 70 and score < 85 then 1 else 0 end)/count(*) as 85_70,
sum(case when score >= 60 and score < 70 then 1 else 0 end)/count(*) as 70_60,
sum(case when score < 60 then 1 else 0 end)/count(*) as 60_0
from sc group by cid) as A
where c.cid = A.cid
+--------------+--------------+------+--------+--------+--------+--------+
| 课程编号 | 课程名称 | cid | 100_85 | 85_70 | 70_60 | 60_0 |
+--------------+--------------+------+--------+--------+--------+--------+
| 01 | 语文 | 01 | 0.0000 | 0.6667 | 0.0000 | 0.3333 |
| 02 | 数学 | 02 | 0.5000 | 0.1667 | 0.1667 | 0.1667 |
| 03 | 英语 | 03 | 0.3333 | 0.3333 | 0.0000 | 0.3333 |
+--------------+--------------+------+--------+--------+--------+--------+
3 rows in set (0.00 sec)
18. 查询各科成绩前三名的记录
这是我比较喜欢的一道题目，非常经典。
select * from (select *, rank() over(partition by cid order by score desc) as graderank from sc) A
where A.graderank <= 3
| Sid | Cid | score | graderank |
+------+------+-------+-----------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 3 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
+------+------+-------+-----------+
10 rows in set (0.00 sec)
20. 查询出只选修两门课程的学生学号和姓名
select s.sid, s.sname, count(cid)
from student s, sc
where s.sid = sc.sid
group by s.sid
having count(cid)=2
+------+--------+------------+
| sid | sname | count(cid) |
+------+--------+------------+
| 05 | 周梅 | 2 |
| 06 | 吴兰 | 2 |
| 07 | 郑竹 | 2 |
+------+--------+------------+
3 rows in set (0.00 sec)
22. 查询名字中含有「风」字的学生信息
select * from student where sname like '%风%'
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
+------+--------+---------------------+------+
1 row in set (0.00 sec)
24. 查询 1990 年出生的学生名单
select * from student where year(sage) = 1990
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+
5 rows in set (0.00 sec)
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*, max(score)
from student s, teacher t, course c, sc
where s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid
and t.tname = '张三'
+------+--------+---------------------+------+------------+
| Sid | Sname | Sage | Ssex | max(score) |
+------+--------+---------------------+------+------------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 90.0 |
+------+--------+---------------------+------+------------+
1 row in set (0.00 sec)
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select * from (
select *, DENSE_RANK() over (order by score desc) A
from SC
where Cid = (select Cid from Course where Tid=(select Tid from Teacher where Tname='张三'))
) B
where B.A=1
+------+------+-------+---+
| Sid | Cid | score | A |
+------+------+-------+---+
| 01 | 02 | 90.0 | 1 |
+------+------+-------+---+
1 row in set (0.00 sec)
40. 查询各学生的年龄，只按年份来算
select sname, year(now())-year(sage) as age from student
+--------+------+
| sname | age |
+--------+------+
| 赵雷 | 28 |
| 钱电 | 28 |
| 孙风 | 28 |
| 李云 | 28 |
| 周梅 | 27 |
| 吴兰 | 26 |
| 郑竹 | 29 |
| 王菊 | 28 |
+--------+------+
8 rows in set (0.00 sec)
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select sname, timestampdiff(year, sage, now()) as age from student
+--------+------+
| sname | age |
+--------+------+
| 赵雷 | 28 |
| 钱电 | 27 |
| 孙风 | 28 |
| 李云 | 27 |
| 周梅 | 26 |
| 吴兰 | 26 |
| 郑竹 | 29 |
| 王菊 | 28 |
+--------+------+
8 rows in set (0.00 sec)
42. 查询本周过生日的学生
select * from student where week(now()) = week(sage)
Empty set (0.00 sec)
43. 查询下周过生日的学生
select * from student where (week(now())+1) = week(sage)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
+------+--------+---------------------+------+
1 row in set (0.00 sec)
44. 查询本月过生日的学生
select * from student where month(now()) = month(sage)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
1 row in set (0.00 sec)
45. 查询下月过生日的学生
select * from student where (month(now())+1) = month(sage)
+------+--------+---------------------+------+
| Sid | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
+------+--------+---------------------+------+
1 row in set (0.00 sec)
练习题目
- 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 1.1 查询同时存在" 01 "课程和" 02 "课程的情况 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
- 查询在 SC 表存在成绩的学生信息
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 4.1 查有成绩的学生信息
- 查询「李」姓老师的数量
- 查询学过「张三」老师授课的同学的信息
- 查询没有学全所有课程的同学的信息
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
- 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
- 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
- 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
- 查询学生的总成绩，并进行排名，总分重复时保留名次空缺 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
- 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
- 查询各科成绩前三名的记录
- 查询每门课程被选修的学生数
- 查询出只选修两门课程的学生学号和姓名
- 查询男生、女生人数
- 查询名字中含有「风」字的学生信息
- 查询同名同性学生名单，并统计同名人数
- 查询 1990 年出生的学生名单
- 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
- 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
- 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
- 查询不及格的课程
- 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
- 求每门课程的学生人数
- 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
- 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 查询每门功成绩最好的前两名
- 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
- 检索至少选修两门课程的学生学号
- 查询选修了全部课程的学生信息
- 查询各学生的年龄，只按年份来算
- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
- 查询本周过生日的学生
- 查询下周过生日的学生
- 查询本月过生日的学生
- 查询下月过生日的学生
参考答案
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select *
from (select SId ,score from sc where sc.CId='01')as t1 , (select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId
and t1.score>t2.score
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select *
from (select SId ,score from sc where sc.CId='01')as t1 , (select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select *
from (select SId ,score from sc where sc.CId='01')as t1 left join (select SId ,score from sc where sc.CId='02') as t2
on t1.SId=t2.SId
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select *
from sc
where sc.SId not in (select SId from sc where sc.CId='01')
and sc.CId='02'
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select student.*,t1.avgscore
from student inner JOIN(
select sc.SId ,AVG(sc.score)as avgscore
from sc
GROUP BY sc.SId
HAVING AVG(sc.score)>=60)as t1 on student.SId=t1.SId
- 查询在 SC 表存在成绩的学生信息
select DISTINCT student.*
from student ,sc
where student.SId=sc.SId
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
select student.SId,student.Sname,t1.sumscore,t1.coursecount
from student ,(
select SC.SId,sum(sc.score)as sumscore ,count(sc.CId) as coursecount
from sc
GROUP BY sc.SId) as t1
where student.SId =t1.SId
4.1 查有成绩的学生信息
select *
from student
where EXISTS(select * from sc where student.SId=sc.SId)
- 查询「李」姓老师的数量
select count(*)
from teacher
where teacher.Tname like '李%
- 查询学过「张三」老师授课的同学的信息
select student.*
from teacher ,course ,student,sc
where teacher.Tname='张三'
and teacher.TId=course.TId
and course.CId=sc.CId
and sc.SId=student.SId
- 查询没有学全所有课程的同学的信息
- 解法1
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
Having count(*)<(select count(*) from course)
但这种解法得出来的结果不包括什么课都没选的同学。
- 解法2
select DISTINCT student.*
from
(select student.SId,course.CId
from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and t1.SId=student.SId
利用笛卡尔积可以把什么课都没选的同学查询出来
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select DISTINCT student.*
from sc ,student
where sc.CId in (select CId from sc where sc.SId='01')
and sc.SId=student.SId
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select DISTINCT student.*
from (
select student.SId,t.CId
from student ,(select sc.CId from sc where sc.SId='01') as t) as t1 LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student
where sc.SId is null
and t1.SId=student.SId
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select *
from student
where student.SId not in
(
select student.SId
from student left join sc on student.SId=sc.SId
where EXISTS
(select *
from teacher ,course
where teacher.Tname='张三'
and teacher.TId=course.TId
and course.CId=sc.CId))
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.SId,student.Sname,avg(sc.score)
from student ,sc
where student.SId=sc.SId
and sc.score<60
GROUP BY sc.SId
HAVING count(*)>=2
- 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*
from student,sc
where sc.CId ='01'
and sc.score<60
and student.SId=sc.SId
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
sc.SId,sc.CId,sc.score,t1.avgscore
from sc left join (select sc.SId,avg(sc.score) as avgscore
from sc
GROUP BY sc.SId) as t1 on sc.SId =t1.SId
ORDER BY t1.avgscore DESC
- 查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.CId ,max(sc.score)as 最高分,min(sc.score)as 最低分,AVG(sc.score)as 平均分,count(*)as 选修人数,sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,sum(case when sc.score>=80 and sc.score<90 and sc.score<80 then 1 else 0 end )/count(*)as 优良率,sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC,sc.CId asc
- 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select sc.CId ,@curRank:=@curRank+1 as rank,sc.score
from (select @curRank:=0) as t ,sc
ORDER BY sc.score desc
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select sc.CId , case when @fontscore=score then @curRank when @fontscore:=score then @curRank:=@curRank+1 end as rank,sc.score
from (select @curRank:=0 ,@fontage:=null) as t ,sc
ORDER BY sc.score desc
- 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select t1.*,@currank:= @currank+1 as rank
from (select sc.SId, sum(score)
from sc
GROUP BY sc.SId
ORDER BY sum(score) desc) as t1,(select @currank:=0) as t
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select t1.*, case when @fontscore=t1.sumscore then @currank when @fontscore:=t1.sumscore then @currank:=@currank+1 end as rank
from (select sc.SId, sum(score) as sumscore
from sc
GROUP BY sc.SId
ORDER BY sum(score) desc) as t1,(select @currank:=0,@fontscore:=null) as t
- 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select course.CId,course.Cname,t1.*
from course LEFT JOIN (
select sc.CId,CONCAT(sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end )/count(*)*100,'%') as '[85-100]',
CONCAT(sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end )/count(*)*100,'%') as '[70-85)',
CONCAT(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end )/count(*)*100,'%') as '[60-70)',
CONCAT(sum(case when sc.score>=0 and sc.score<60 then 1 else 0 end )/count(*)*100,'%') as '[0-60)'
from sc
GROUP BY sc.CId) as t1 on course.CId=t1.CId
- 查询各科成绩前三名的记录
思路：前三名转化为若大于此成绩的数量少于3即为前三名。
select *
from sc
where (select count(*) from sc as a where sc.CId =a.CId and sc.score <a.score )<3
ORDER BY CId asc,sc.score desc
- 查询每门课程被选修的学生数
select sc.CId,count(*)
from sc
GROUP BY sc.CId
- 查询出只选修两门课程的学生学号和姓名
select student.SId,student.Sname
from sc,student
where student.SId=sc.SId
GROUP BY sc.SId
HAVING count(*)=2
21.查询男生、女生人数
select student.Ssex ,count(*) as 人数
from student
GROUP BY student.Ssex
- 查询名字中含有「风」字的学生信息
select *
from student
where student.Sname like '%风%'
23.查询同名同性学生名单，并统计同名人数
select *
from student LEFT JOIN (select Sname,Ssex,COUNT(*)同名人数 from Student group by Sname,Ssex) as t1
on student.Sname =t1.Sname and student.Ssex=t1.Ssex
where t1.同名人数>1
24.查询 1990 年出生的学生名单
select *
from student
where YEAR(student.Sage)=1990
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sc.CId,AVG(sc.score)
from sc
GROUP BY sc.CId
ORDER BY AVG(sc.score) desc ,sc.CId asc
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select student.SId,student.Sname,t1.avgscore
from student INNER JOIN (select sc.SId ,AVG(sc.score) as avgscore from sc GROUP BY sc.SId HAVING AVG(sc.score)>85) as t1 on
student.SId=t1.SId
- 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select student.Sname ,t1.score
from student INNER JOIN (select sc.SId,sc.score
from sc,course
where sc.CId=course.CId
and course.Cname='数学'
and sc.score<60)as t1 on student.SId=t1.SId
- 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select student.SId,sc.CId,sc.score from Student left join sc on student.SId=sc.SId
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select student.Sname,course.Cname,sc.score
from student , sc ,course
where sc.score>=70
and student.SId=sc.SId
and sc.CId=course.CId
30.查询存在不及格的课程
select DISTINCT sc.CId
from sc
where sc.score <60
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select student.SId,student.Sname
from student ,sc
where sc.CId='01'
and student.SId=sc.SId
and sc.score>80
- 求每门课程的学生人数
select sc.CId,count(*) as 学生人数
from sc
GROUP BY sc.CId
- 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select student.*,sc.score
from student ,course ,teacher ,sc
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname='张三'
and student.SId =sc.SId
LIMIT 1
- 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select student.*,t1.score
from student INNER JOIN (select sc.SId,sc.score, case when @fontage=sc.score then @rank when @fontage:=sc.score then @rank:=@rank+1 end as rank
from course ,teacher ,sc,(select @fontage:=null,@rank:=0) as t
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname='张三'
ORDER BY sc.score DESC) as t1 on student.SId=t1.SId
where t1.rank=1
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from sc as t1
where exists(select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score )
36.查询每门功成绩最好的前两名
select *
from sc as t1
where (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2
ORDER BY t1.CId
37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select sc.CId as 课程编号,count(*) as 选修人数
from sc
GROUP BY sc.CId
HAVING count(*)>5
38.检索至少选修两门课程的学生学号
select DISTINCT t1.SId
from sc as t1
where (select count(* )from sc where t1.SId=sc.SId)>=3
- 查询选修了全部课程的学生信息
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )
40.查询各学生的年龄，只按年份来算
select student.SId as 学生编号,student.Sname as 学生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student
- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student.SId as 学生编号,student.Sname as 学生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student
42.查询本周过生日的学生
select *
from student
where YEARWEEK(student.Sage)=YEARWEEK(CURDATE())
- 查询下周过生日的学生
select *
from student
where YEARWEEK(student.Sage)=CONCAT(YEAR(CURDATE()),week(CURDATE())+1)
44.查询本月过生日的学生
select *
from student
where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM CURDATE())
45.查询下月过生日的学生
select *
from student
where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
练习题目
- 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-
查询在 SC 表存在成绩的学生信息
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
4.1 查有成绩的学生信息
-
查询「李」姓老师的数量
-
查询学过「张三」老师授课的同学的信息
-
查询没有学全所有课程的同学的信息
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-
查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
-
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-
检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-
查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
- 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
- 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
-
统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-
查询各科成绩前三名的记录
-
查询每门课程被选修的学生数
-
查询出只选修两门课程的学生学号和姓名
-
查询男生、女生人数
-
查询名字中含有「风」字的学生信息
-
查询同名同性学生名单，并统计同名人数
-
查询 1990 年出生的学生名单
-
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-
查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
-
查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-
查询不及格的课程
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-
求每门课程的学生人数
-
成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-
成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数（超过 5 人的课程才统计）。
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
-
查询各学生的年龄，只按年份来算
-
按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-
查询本周过生日的学生
-
查询下周过生日的学生
-
查询本月过生日的学生
-
查询下月过生日的学生
答案
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
因为需要全部的学生信息，则需要在sc表中得到符合条件的SId后与student表进行join，可以left join 也可以 right join
select * from Student RIGHT JOIN (
select t1.SId, class1, class2 from
(select SId, score as class1 from sc where sc.CId = '01')as t1,
(select SId, score as class2 from sc where sc.CId = '02')as t2
where t1.SId = t2.SId AND t1.class1 > t2.class2
)r
on Student.SId = r.SId;
select * from (
select t1.SId, class1, class2
from
(SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1,
(SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2
where t1.SId = t2.SId and t1.class1 > t2.class2
) r
LEFT JOIN Student
ON Student.SId = r.SId;
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select * from
(select * from sc where sc.CId = '01') as t1,
(select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
这一道就是明显需要使用join的情况了，02可能不存在，即为left join的右侧或right join 的左侧即可.
select * from
(select * from sc where sc.CId = '01') as t1
left join
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;
select * from
(select * from sc where sc.CId = '02') as t2
right join
(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc
where sc.SId not in (
select SId from sc
where sc.CId = '01'
)
AND sc.CId= '02';
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注意，这里必须要给计算得到的AVG结果一个alias.（AS ss）
得到学生信息的时候既可以用join也可以用一般的联合搜索
select student.SId,sname,ss from student,(
select SId, AVG(score) as ss from sc
GROUP BY SId
HAVING AVG(score)> 60
)r
where student.sid = r.sid;
select Student.SId, Student.Sname, r.ss from Student right join(
select SId, AVG(score) AS ss from sc
GROUP BY SId
HAVING AVG(score)> 60
)r on Student.SId = r.SId;
select s.SId,ss,Sname from(
select SId, AVG(score) as ss from sc
GROUP BY SId
HAVING AVG(score)> 60
)r left join
(select Student.SId, Student.Sname from
Student)s on s.SId = r.SId;
- 查询在 SC 表存在成绩的学生信息
select DISTINCT student.*
from student,sc
where student.SId=sc.SId
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
联合查询不会显示没选课的学生：
select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc
group by sc.sid)r
where student.sid = r.sid;
如要显示没选课的学生(显示为NULL)，需要使用join:
select s.sid, s.sname,r.coursenumber,r.scoresum
from (
(select student.sid,student.sname
from student
)s
left join
(select
sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
from sc
group by sc.sid
)r
on s.sid = r.sid
);
4.1 查有成绩的学生信息
这一题涉及到in和exists的用法，在这种小表中，两种方法的效率都差不多，但是请参考SQL查询中in和exists的区别分析
当表2的记录数量非常大的时候，选用exists比in要高效很多.
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False.
结论：IN()适合B表比A表数据小的情况
结论：EXISTS()适合B表比A表数据大的情况
select * from student
where exists (select sc.sid from sc where student.sid = sc.sid);
select * from student
where student.sid in (select sc.sid from sc);
- 查询「李」姓老师的数量
select count(*)
from teacher
where tname like '李%';
- 查询学过「张三」老师授课的同学的信息
多表联合查询
select student.* from student,teacher,course,sc
where
student.sid = sc.sid
and course.cid=sc.cid
and course.tid = teacher.tid
and tname = '张三';
- 查询没有学全所有课程的同学的信息
因为有学生什么课都没有选，反向思考，先查询选了所有课的学生，再选择这些人之外的学生.
select * from student
where student.sid not in (
select sc.sid from sc
group by sc.sid
having count(sc.cid)= (select count(cid) from course)
);
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
这个用联合查询也可以，但是逻辑不清楚，我觉得较为清楚的逻辑是这样的：从sc表查询01同学的所有选课cid--从sc表查询所有同学的sid如果其cid在前面的结果中--从student表查询所有学生信息如果sid在前面的结果中
select * from student
where student.sid in (
select sc.sid from sc
where sc.cid in(
select sc.cid from sc
where sc.sid = '01'
)
);
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
不会做。
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
仍然还是嵌套，三层嵌套， 或者多表联合查询
select * from student
where student.sid not in(
select sc.sid from sc where sc.cid in(
select course.cid from course where course.tid in(
select teacher.tid from teacher where tname = "张三"
)
)
);
select * from student
where student.sid not in(
select sc.sid from sc,course,teacher
where
sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname= "张三"
);
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
从SC表中选取score小于60的，并group by sid，having count 大于1
select student.sid, student.sname, AVG(sc.score) from student,sc
where
student.sid = sc.sid and sc.score<60
group by sc.sid
having count(*)>1;
- 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
双表联合查询，在查询最后可以设置排序方式，语法为ORDER BY ***** DESC\ASC;
select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc
left join (
select sid,avg(score) as avscore from sc
group by sid
)r
on sc.sid = r.sid
order by avscore desc;
- 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC
- 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
这一道题有点tricky，可以用变量，但也有更为简单的方法，即自交（左交）
用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”。
select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc as a
left join sc as b
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;
- 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
这里主要学习一下使用变量。在SQL里面变量用@来标识。
set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from sc
group by sc.sid
order by total desc)q;
- 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
有时候觉得自己真是死脑筋。group by以后的查询结果无法使用别名，所以不要想着先单表group by计算出结果再从第二张表里添上课程信息，而应该先将两张表join在一起得到所有想要的属性再对这张总表进行统计计算。这里就不算百分比了，道理相同。
注意一下，用case when 返回1 以后的统计不是用count而是sum
select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;
- 查询各科成绩前三名的记录
大坑比。mysql不能group by 了以后取limit，所以不要想着讨巧了，我快被这一题气死了。思路有两种，第一种比较暴力，计算比自己分数大的记录有几条，如果小于3 就select，因为对前三名来说不会有3个及以上的分数比自己大了，最后再对所有select到的结果按照分数和课程编号排名即可。
select * from sc
where (
select count(*) from sc as a
where sc.cid = a.cid and sc.score<a.score
)< 3
order by cid asc, sc.score desc;
第二种比较灵巧一些，用自身左交，但是有点难以理解。
先用自己交自己，条件为a.cid = b.cid and a.score<b.score，其实就是列出同一门课内所有分数比较的情况。
想要查看完整的表可以
select * from sc a
left join sc b on a.cid = b.cid and a.score<b.score
order by a.cid,a.score;
结果
查看，发现结果是47行的一个表，列出了类似 01号课里“30分小于50，也小于70，也小于80，也小于90”“50分小于70，小于80，小于90”.....
所以理论上，对任何一门课来说，分数最高的那三个记录，在这张大表里，通过a.sid和a.cid可以联合确定这个同学的这门课的这个分数究竟比多少个其他记录高/低，
如果这个特定的a.sid和a.cid组合出现在这张表里的次数少于3个，那就意味着这个组合（学号+课号+分数）是这门课里排名前三的。
所以下面这个计算中having count 部分其实count()或者任意其他列都可以，这里制定了一个列只是因为比count()运行速度上更快。
select a.sid,a.cid,a.score from sc a
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<3
order by a.cid;
- 查询每门课程被选修的学生数
select cid, count(sid) from sc
group by cid;
- 查询出只选修两门课程的学生学号和姓名
嵌套查询
select student.sid, student.sname from student
where student.sid in
(select sc.sid from sc
group by sc.sid
having count(sc.cid)=2
);
联合查询
select student.SId,student.Sname
from sc,student
where student.SId=sc.SId
GROUP BY sc.SId
HAVING count(*)=2；
21.查询男生、女生人数
select ssex, count(*) from student
group by ssex;
- 查询名字中含有「风」字的学生信息
select *
from student
where student.Sname like '%风%'
23.查询同名学生名单，并统计同名人数
找到同名的名字并统计个数
select sname, count(*) from student
group by sname
having count(*)>1;
嵌套查询列出同名的全部学生的信息
select * from student
where sname in (
select sname from student
group by sname
having count(*)>1
);
24.查询 1990 年出生的学生名单
select *
from student
where YEAR(student.Sage)=1990;
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course
where sc.cid = course.cid
group by sc.cid
order by average desc,cid asc;
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
having也可以用来截取结果表，在这里就先得到平均成绩总表，再截取AVG大于85的即可.
select student.sid, student.sname, AVG(sc.score) as aver from student, sc
where student.sid = sc.sid
group by sc.sid
having aver > 85;
- 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select student.sname, sc.score from student, sc, course
where student.sid = sc.sid
and course.cid = sc.cid
and course.cname = "数学"
and sc.score < 60;
- 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select student.sname, cid, score from student
left join sc
on student.sid = sc.sid;
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70
and student.sid = sc.sid
and sc.cid = course.cid;
30.查询存在不及格的课程
可以用group by 来取唯一，也可以用distinct
select cid from sc
where score< 60
group by cid;
select DISTINCT sc.CId
from sc
where sc.score <60;
31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
select student.sid,student.sname
from student,sc
where cid="01"
and score>=80
and student.sid = sc.sid;
- 求每门课程的学生人数
select sc.CId,count(*) as 学生人数
from sc
GROUP BY sc.CId;
- 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
用having max()理论上也是对的，但是下面那种按分数排序然后取limit 1的更直观可靠
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
having max(sc.score);
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
order by score desc
limit 1;
- 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
为了验证这一题，先修改原始数据
UPDATE sc SET score=90
where sid = "07"
and cid ="02";
这样张三老师教的02号课就有两个学生同时获得90的最高分了。
这道题的思路继续上一题，我们已经查询到了符合限定条件的最高分了，这个时候只用比较这张表，找到全部score等于这个最高分的记录就可，看起来有点繁复。
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
and sc.score = (
select Max(sc.score)
from sc,student, teacher, course
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
);
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
同上，在这里用了inner join后会有概念是重复的记录：“01 课与 03课”=“03 课与 01 课”，所以这里取唯一可以直接用group by
select a.cid, a.sid, a.score from sc as a
inner join
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;
36.查询每门功成绩最好的前两名
同上19题
select a.sid,a.cid,a.score from sc as a
left join sc as b
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;
37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select sc.cid, count(sid) as cc from sc
group by cid
having cc >5;
38.检索至少选修两门课程的学生学号
select sid, count(cid) as cc from sc
group by sid
having cc>=2;
- 查询选修了全部课程的学生信息
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )
40.查询各学生的年龄，只按年份来算
不想做，一般都用41题的方法精确到天
- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student.SId as 学生编号,student.Sname as 学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student
42.查询本周过生日的学生
select *
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());
- 查询下周过生日的学生
select *
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;
44.查询本月过生日的学生
select *
from student
where MONTH(student.Sage)=MONTH(CURDATE());
45.查询下月过生日的学生
select *
from student
where MONTH(student.Sage)=MONTH(CURDATE())+1;
链接：https://www.jianshu.com/p/476b52ee4f1b
练习SQL语句大全（关键看思路）
该数据有三张表：
部门表dept（deptno部门编号、dname部门名称、loc位置）
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50718
Source Host : localhost:3306
Source Database : sql_demo
Target Server Type : MYSQL
Target Server Version : 50718
File Encoding : 65001
Date: 2019-02-23 21:54:13
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(10) NOT NULL COMMENT '部门编号',
`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) DEFAULT NULL COMMENT '部门位置',
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', '后勤部门', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', '开发部门', 'DALLAS');
INSERT INTO `dept` VALUES ('30', '销售部门', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', '推广部门', 'BOSTON');员工表emp（empno工号、ename员工姓名、job职位、mgr直属领导工号、hiredate入职日期、sal月薪、comm补贴、deptno部门编号）
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50718
Source Host : localhost:3306
Source Database : sql_demo
Target Server Type : MYSQL
Target Server Version : 50718
File Encoding : 65001
Date: 2019-02-23 21:54:29
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`job` varchar(9) DEFAULT NULL,
`mgr` int(4) DEFAULT NULL COMMENT '直属领导工号',
`hiredate` date DEFAULT NULL COMMENT '入职日期',
`sal` double(7,2) DEFAULT NULL,
`comm` double(7,2) DEFAULT NULL COMMENT '补贴',
`deptno` int(2) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', '张三', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', '李四', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', '王二', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', '李武', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', '周六', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', '刘振', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', '曹阳', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', '爱丽丝', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', '哪吒', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', '赵云', 'SALESMAN', '7698', '1981-09-08', '1500.00', null, '30');
INSERT INTO `emp` VALUES ('7876', '妲己', 'CLERK', '7788', '1987-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', '刘备', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', '猴子', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', '安其拉', 'CLERK', '7782', '1982-01-23', '950.00', null, '10');薪水等级表salgrade（grade等级、losal区间下限、hisal区间上限）
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50718
Source Host : localhost:3306
Source Database : sql_demo
Target Server Type : MYSQL
Target Server Version : 50718
File Encoding : 65001
Date: 2019-02-23 21:54:39
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL COMMENT '薪水等级',
`losal` int(11) DEFAULT NULL COMMENT '薪水最低',
`hisal` int(11) DEFAULT NULL COMMENT '薪水最高'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '5000');1、 取得每个部门最高薪水人员的名称
#1.先查每个部门最高薪水的(按照部门编号分组)
SELECT deptno ,MAX(sal) AS maxsal FROM emp GROUP BY deptno
#2.把这张表当做临时表，跟emp表进行连接通过员工表的部门编号和最高的薪水
SELECT emp.ename , t.*
FROM emp
JOIN (SELECT deptno ,MAX(sal) AS maxsal FROM emp GROUP BY deptno) as t
ON t.deptno = emp.deptno AND t.maxsal = emp.sal
2、 哪些人的薪水在部门平均薪水之上？
#第一步需要先按照部门编号分组选出每个部门的平均薪水
SELECT deptno , AVG(sal) FROM emp GROUP BY deptno
#第二步需要把刚才的那个表当做临时表通过临时表的部门id且当部门表中的薪水比临时表中的薪水高的时候查出所有的数据
SELECT emp.ename,emp.sal , t.*
FROM emp
JOIN (SELECT deptno , AVG(sal) AS avgsal FROM emp GROUP BY deptno)t
ON emp.deptno = t.deptno AND emp.sal > t.avgsal
±-------±--------±-------±------------+
| ename | sal | deptno | avgsal |
±-------±--------±-------±------------+
| 李四 | 1600.00 | 30 | 1566.666667 |
| 李武 | 2975.00 | 20 | 2175.000000 |
| 刘振 | 2850.00 | 30 | 1566.666667 |
| 爱丽丝 | 3000.00 | 20 | 2175.000000 |
| 哪吒 | 5000.00 | 10 | 2916.666667 |
| 猴子 | 3000.00 | 20 | 2175.000000 |
±-------±--------±-------±------------+
3、（1）取得部门中（所有人的）平均薪水的等级
#思路：其实就是让你把部门的平均薪水等级而且是按部门分组得到临时表，在用临时表的平均薪水放到薪水等级表中用between完成
#第一步所以我们的目的是要求平均薪水的等级，也就是求部门的平均薪水(按部门分)
SELECT deptno ,AVG(sal) FROM emp GROUP BY deptno
#第二步是用得到的上表进行在薪水等级表中进行用betwee和and关联一起
SELECT t.* , salgrade.grade
FROM (SELECT deptno ,AVG(sal) AS avgsal FROM emp GROUP BY deptno) AS t
JOIN salgrade
ON avgsal BETWEEN salgrade.losal AND salgrade.hisal
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±-------±------------±------+
3、 (2)取得部门中（所有人的）平均的薪水等级
#思路：其实说白了就是求出每一个员工的薪水等级然后在按部门进行排序，然后再对部门编号进行分组在分组内进行求平均成绩的函数
#第一步先求出每一个员工的薪水等级
SELECT emp.ename,emp.sal,emp.deptno,salgrade.grade FROM emp JOIN salgrade ON
emp.sal BETWEEN salgrade.losal AND salgrade.hisal ORDER BY emp.deptno
#第二步就是在上一步的基础上进行进行按部门分组，并且对成绩等级进行求平均函数
SELECT emp.deptno AS deptno,AVG( salgrade.grade) AS avgsal
FROM emp
JOIN salgrade ON
emp.sal BETWEEN salgrade.losal AND salgrade.hisal
GROUP BY emp.deptno
±-------±-------+
| deptno | avgsal |
±-------±-------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±-------±-------+
5、 取得平均薪水最高的部门和部门编号（至少给出两种解决方案）
（1）思路：（这种是使用最容易想到的两表关联）其实就是查询平均薪水最高的部门和编号，这里没什么好解释的
#第一步需要先求出部门平均薪水最高的部门，然后当成一个临时表
SELECT ename,deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno
#第二步把你的临时表跟你想要拿到的部门名称和编号都关联起来，这里可以使用当部门编号相同的时候来进行关联
SELECT dept.dname,dept.deptno , MAX(t.avgsal)
FROM dept
JOIN (SELECT ename,deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno)AS t
ON dept.deptno = t.deptno
±-------------±-------±--------------+
| dname | deptno | MAX(t.avgsal) |
±-------------±-------±--------------+
| 后勤部门 | 10 | 2916.666667 |
±-------------±-------±--------------+
（2）思路：（这种是使用临时表和目的表可以使用where条件关联）也是通过部门编号，这里思路和上面那个俩表关联一样的思路
#第一步需要先求出部门平均薪水最高的部门，然后当成一个临时表
SELECT ename,deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno
#第二步把你的临时表跟你想要拿到的部门名称和编号都关联起来，这里还可以使用where来进行关联
SELECT dept.dname,dept.deptno , MAX(t.avgsal)
FROM dept ,(SELECT ename,deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno)AS t
WHERE dept.deptno = t.deptno
±-------------±-------±--------------+
| dname | deptno | MAX(t.avgsal) |
±-------------±-------±--------------+
| 后勤部门 | 10 | 2916.666667 |
±-------------±-------±--------------+
（3）方案实现就是按照平均薪水降序排列取第一个
#首先第一步你得先取到每一个部门的平均薪水
SELECT AVG( sal) ,deptno FROM emp GROUP BY deptno
#然后第二步取出平均薪水最高的那个部门（把它当成一个）
SELECT AVG( sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal desc LIMIT 1
#第三步其实就是在第一步的基础上用having取到平均薪水最高的那个部门
SELECT AVG( sal) ,deptno FROM emp
GROUP BY deptno
HAVING AVG( sal) = (SELECT AVG( sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal desc LIMIT 1)
±------------±-------+
| AVG( sal) | deptno |
±------------±-------+
| 2916.666667 | 10 |
±------------±-------+
6、 取得平均薪水最高的部门和部门名称
#第一步先拿到每个部门的平均薪水
SELECT AVG(sal),deptno FROM emp GROUP BY deptno
#第二步先按照部门分组得出每个组的平均薪水，然后在排序取出最高的薪水
SELECT AVG(sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1
#第三步把取到的平均薪水最高的部门表当做临时表，和部门表进行关联,然后取出平均薪水最高的那个
SELECT dept.dname,avgsal
FROM (SELECT AVG(sal) AS avgsal ,deptno FROM emp GROUP BY deptno) AS t
JOIN dept
ON dept.deptno = t.deptno
HAVING avgsal =
(SELECT AVG(sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1)
±-------------±------------+
| dname | avgsal |
±-------------±------------+
| 后勤部门 | 2916.666667 |
±-------------±------------+
7、 求平均薪水的等级最高的部门的部门名称
#第一步先拿到各个部门的平均薪水的等级并且跟部门名称也进行关联一起
SELECT dept.dname,AVG(sal),emp.deptno FROM emp JOIN dept ON dept.deptno=emp.deptno GROUP BY deptno
#第二步通过第一步的临时表和薪水等级表进行关联，用临时表的平均薪水用between进行范围查询
SELECT t.dname,t.avgsal,s.grade
FROM (SELECT dept.dname AS dname,AVG(sal)AS avgsal,emp.deptno FROM emp JOIN dept ON dept.deptno=emp.deptno GROUP BY deptno) AS t
JOIN salgrade AS s
ON t.avgsal BETWEEN s.losal AND s.hisal
#第三步获取最高等级的值(首先你的想办法把平均薪水临时表和最高等级表进行关联起来)
SELECT MAX(grade)
FROM (SELECT AVG(sal) AS avgsal,deptno FROM emp GROUP BY deptno) AS t
JOIN salgrade AS s
ON t.avgsal BETWEEN s.losal AND s.hisal
#第四步其实就是把第二步和第三步进行联合一起进行查询，因为这里涉及到部门名称，
#所以需要在第一步的时候也要把部门名称和平均薪水关联起来
SELECT t.dname,t.avgsal,s.grade AS grade
FROM
(SELECT dept.dname AS dname,AVG(sal)AS avgsal,emp.deptno FROM emp JOIN dept ON dept.deptno=emp.deptno GROUP BY deptno) AS t
JOIN salgrade AS s
ON t.avgsal
BETWEEN s.losal AND s.hisal
WHERE grade = (SELECT MAX(grade)
FROM
(SELECT AVG(sal) AS avgsal,deptno FROM emp GROUP BY deptno) AS t
JOIN salgrade AS s
ON t.avgsal
BETWEEN s.losal AND s.hisal)
±-------------±------------±------+
| dname | avgsal | grade |
±-------------±------------±------+
| 后勤部门 | 2916.666667 | 4 |
| 开发部门 | 2175.000000 | 4 |
±-------------±------------±------+
8、 取得比普通员工（院代码没有在mgr字段出现的）的最高薪水还要高的领导人姓名
思路就是先取出全部的普通员工，然后用限定条件若不在直接领导人号的列表中就说明是普通员工，然后在取出员工最高的那个值当做一个字段和取出所有薪水的领导人进行where限定筛选
总结：在使用not in 的时候需要手动去除为NULL的字段的值，如果是使用in 的话可以自动忽略为NULL的字段值
#第一步首先是取出普通的员工(这里需要注意的是not in不会自动忽略空值，但是in可以)
SELECT * FROM emp WHERE empno not in
(SELECT DISTINCT mgr FROM emp WHERE mgr is NOT NULL)
#第二步找出普通员工的最高的薪水
SELECT MAX(sal) FROM emp WHERE empno not in
(SELECT DISTINCT mgr FROM emp WHERE mgr is NOT NULL)
#第三步需要找到比普通员工最高的薪水的领导人的姓名
SELECT emp.ename,sal FROM emp
WHERE sal >(SELECT MAX(sal) FROM emp WHERE empno not in
(SELECT DISTINCT mgr FROM emp WHERE mgr is NOT NULL))
±----------±--------+
| ename | sal |
±----------±--------+
| 李武 | 2975.00 |
| 刘振 | 2850.00 |
| 曹阳 | 2450.00 |
| 爱丽丝 | 3000.00 |
| 哪吒 | 5000.00 |
| 猴子 | 3000.00 |
±----------±--------+
9、 取得薪水最高的前五名员工
#这个没啥好说的就是直接查询然后按照薪水降序排序然后取到前5条数据
SELECT emp.ename,emp.sal FROM emp ORDER BY sal DESC LIMIT 5
±------±----------±----------±-----±-----------±--------±-----±-------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
±------±----------±----------±-----±-----------±--------±-----±-------+
| 7839 | 哪吒 | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | 猴子 | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7788 | 爱丽丝 | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7566 | 李武 | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | 刘振 | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
±------±----------±----------±-----±-----------±--------±-----±-------+
10、取得薪水最高的第六到第十名员工
#这个也没啥好说的主要是利用limit，也就是我们实现分页所需要用到的，
#limit的第一个数表示从第几条数据开始截取，第二个参数是从刚刚第一个参数开始截取多少条数据
SELECT * FROM emp ORDER BY sal DESC LIMIT 5, 5
±------±----------±---------±-----±-----------±--------±-------±-------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
±------±----------±---------±-----±-----------±--------±-------±-------+
| 7782 | 曹阳 | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7499 | 李四 | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7844 | 赵云 | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7934 | 安其拉 | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7521 | 王二 | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
±------±----------±---------±-----±-----------±--------±-------±-------+
12、取得每个薪水等级有多少员工
思路：首先先按照薪水等级进行分组（这里需要考虑把员工表和薪水等级表进行关联），然后进行分组中的查询每个组的员工的个数
SELECT emp.*,COUNT(emp.empno)AS 每个等级的员工个数 FROM emp JOIN salgrade
ON sal BETWEEN salgrade.losal AND salgrade.hisal
GROUP BY salgrade.grade
±------±-------±----------±-----±-----------±--------±-------±-------±-----------------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | COUNT(emp.empno) |
±------±-------±----------±-----±-----------±--------±-------±-------±-----------------+
| 7369 | 张三 | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 3 |
| 7521 | 王二 | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 3 |
| 7499 | 李四 | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 2 |
| 7566 | 李武 | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 5 |
| 7839 | 哪吒 | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 1 |
±------±-------±----------±-----±-----------±--------±-------±-------±-----------------+
14、列出所有员工及领导的姓名
#思路：就是因为员工和领导都在员工表中，但是他们的对应关系是员工表的上级是领导的员工号（因为领导也是员工啊）
SELECT a.ename AS ‘员工姓名’,b.ename AS ‘领导姓名’ FROM
emp AS a JOIN emp AS b
ON a.empno = b.mgr
±-------------±-------------+
| 员工姓名 | 领导姓名 |
±-------------±-------------+
| 猴子 | 张三 |
| 刘振 | 李四 |
| 刘振 | 王二 |
| 哪吒 | 李武 |
| 刘振 | 周六 |
| 哪吒 | 刘振 |
| 哪吒 | 曹阳 |
| 李武 | 爱丽丝 |
| 刘振 | 赵云 |
| 爱丽丝 | 妲己 |
| 刘振 | 刘备 |
| 李武 | 猴子 |
| 曹阳 | 安其拉 |
±-------------±-------------+
15、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
#第一步因为员工表中既有员工又有领导，那就让领导和员工表结合
#然后用where条件限定日期大小
#第二步是把部门表也给关联进去
SELECT a.empno AS ‘员工编号’,a.ename AS ‘员工姓名’,a.hiredate,
b.hiredate,b.empno AS ‘领导编号’,b.ename AS ‘领导姓名’,dept.dname AS ‘部门名称’
FROM emp AS a
JOIN emp AS b
ON a.empno = b.mgr
JOIN dept
ON a.deptno = dept.deptno
WHERE a.hiredate < b.hiredate
±-------------±-------------±-----------±-----------±-------------±-------------±-------------+
| 员工编号 | 员工姓名 | hiredate | hiredate | 领导编号 | 领导姓名 | 部门名称 |
±-------------±-------------±-----------±-----------±-------------±-------------±-------------+
| 7782 | 曹阳 | 1981-06-09 | 1982-01-23 | 7934 | 安其拉 | 后勤部门 |
| 7566 | 李武 | 1981-04-02 | 1987-04-19 | 7788 | 爱丽丝 | 开发部门 |
| 7788 | 爱丽丝 | 1987-04-19 | 1987-05-23 | 7876 | 妲己 | 开发部门 |
| 7566 | 李武 | 1981-04-02 | 1981-12-03 | 7902 | 猴子 | 开发部门 |
| 7698 | 刘振 | 1981-05-01 | 1981-09-28 | 7654 | 周六 | 销售部门 |
| 7698 | 刘振 | 1981-05-01 | 1981-09-08 | 7844 | 赵云 | 销售部门 |
| 7698 | 刘振 | 1981-05-01 | 1981-12-03 | 7900 | 刘备 | 销售部门 |
±-------------±-------------±-----------±-----------±-------------±-------------±-------------+
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
#思路:因为题目说了不仅要简单的列出部门名称和员工信息，而且还要列出部门里面没有员工的情况
#所以很容易能想到是用左联接，或者是右连接都可以实现
#（1）使用左连接实现
SELECT dname AS ‘部门名称’ , emp.* FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno ORDER BY dname
#(2)使用右连接实现
SELECT dept.dname , emp.* FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
±-------------±------±----------±----------±-----±-----------±--------±--------±-------+
| 部门名称 | empno | ename | job | mgr | hiredate | sal | comm | deptno |
±-------------±------±----------±----------±-----±-----------±--------±--------±-------+
| 后勤部门 | 7782 | 曹阳 | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 后勤部门 | 7839 | 哪吒 | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 后勤部门 | 7934 | 安其拉 | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 开发部门 | 7876 | 妲己 | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 开发部门 | 7902 | 猴子 | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 开发部门 | 7566 | 李武 | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 开发部门 | 7788 | 爱丽丝 | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 开发部门 | 7369 | 张三 | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 推广部门 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 销售部门 | 7499 | 李四 | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 销售部门 | 7698 | 刘振 | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 销售部门 | 7844 | 赵云 | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 销售部门 | 7900 | 刘备 | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 销售部门 | 7521 | 王二 | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 销售部门 | 7654 | 周六 | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
±-------------±------±----------±----------±-----±-----------±--------±--------±-------+
17、列出至少有5个员工的所有部门的详细信息
#思路就是先把员工表和部门表进行关联，然后分组的时候之后只能用having，而且在使用分组的时候还可以是多个字段
SELECT emp.ename,dept.dname,dept.loc,dept.deptno
FROM emp
JOIN dept
ON emp.deptno = dept.deptno
GROUP BY dept.dname,dept.loc,dept.deptno
HAVING COUNT(emp.empno)>=5
±-------±-------------±--------±-------+
| ename | dname | loc | deptno |
±-------±-------------±--------±-------+
| 张三 | 开发部门 | DALLAS | 20 |
| 李四 | 销售部门 | CHICAGO | 30 |
±-------±-------------±--------±-------+
18、列出薪金比‘simith’多的所有员工信息
#先找出妲己的薪水，当做一个字段
SELECT emp.sal FROM emp WHERE ename=‘曹阳’
#然后找到全部的员工信息加个where条件语句把第一步找到的薪水还大的给找出来
SELECT emp.* FROM emp WHERE sal >=(SELECT emp.sal FROM emp WHERE ename=‘曹阳’)
±------±----------±----------±-----±-----------±--------±-----±-------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
±------±----------±----------±-----±-----------±--------±-----±-------+
| 7566 | 李武 | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | 刘振 | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | 曹阳 | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | 爱丽丝 | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | 哪吒 | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | 猴子 | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
±------±----------±----------±-----±-----------±--------±-----±-------+
19、列出所有’clerk’（办事员）的姓名及部门名称，部门人数
#第一步是先找出所有的“clerk”的姓名和部门名称作为一个表
SELECT e.ename,d.dname FROM emp AS e
JOIN dept AS d
ON d.deptno = e.deptno
WHERE job = ‘clerk’
#第二步是找出部门人数而且是按照部门编号进行分组
SELECT deptno , COUNT() AS total FROM emp GROUP BY deptno
#第三步是把第一步的表和第二步的表进行连接通过部门编号
SELECT e.ename,d.dname,t.total FROM emp AS e
JOIN dept AS d
ON d.deptno = e.deptno
JOIN (SELECT deptno , COUNT() AS total FROM emp GROUP BY deptno)AS t
ON t.deptno = e.deptno
WHERE job = ‘clerk’
±----------±-------------±------+
| ename | dname | total |
±----------±-------------±------+
| 张三 | 开发部门 | 5 |
| 妲己 | 开发部门 | 5 |
| 刘备 | 销售部门 | 6 |
| 安其拉 | 后勤部门 | 3 |
±----------±-------------±------+
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
（1）第一种方式答案
#第一步先按工作进行分组
SELECT job ,sal FROM emp GROUP BY job
#第二步然后找出工资最低的然后限定条件是大于1500
SELECT job, MIN(sal)AS minsal,COUNT()
FROM emp GROUP BY job HAVING minsal>1500
±----------±--------±---------+
| job | minsal | COUNT() |
±----------±--------±---------+
| ANALYST | 3000.00 | 2 |
| MANAGER | 2450.00 | 3 |
| PRESIDENT | 5000.00 | 1 |
±----------±--------±---------+
21、列出在部门‘开发部门’工作的员工的姓名，假定不知道开发部的部门编号
#第一步如果不知道开发部门的部门编号，但是我根据部门名称不是也可以查询部门编号吗
SELECT deptno FROM dept WHERE dname = ‘开发部门’
#第二步然后把查到的部门编号当做是一个字段，在进行去员工表中去查询
SELECT ename FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname = ‘开发部门’)
±----------+
| ename |
±----------+
| 张三 |
| 李武 |
| 爱丽丝 |
| 妲己 |
| 猴子 |
±----------+
22、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导的姓名，雇员的工资等级
#第一步是查出公司所有员工的平均工资
SELECT AVG(sal) FROM emp
#第二步还需要去查询上一级领导
SELECT a.ename AS ‘员工名称’,b.ename AS ‘领导的名字’
FROM emp AS a JOIN emp AS b ON a.empno = b.mgr
#第三步是需要把部门表和员工表和薪水等级表进行关联起来
SELECT a.sal ,b.ename,dept.dname ,salgrade.grade ,a.ename AS ‘上级领导’
FROM emp AS a JOIN dept ON a.deptno =dept.deptno
JOIN emp AS b ON a.empno = b.mgr
JOIN salgrade ON a.sal
BETWEEN salgrade.losal AND salgrade.hisal
WHERE a.sal > (SELECT AVG(emp.sal) FROM emp )
±--------±----------±-------------±------±-------------+
| sal | ename | dname | grade | 上级领导 |
±--------±----------±-------------±------±-------------+
| 3000.00 | 张三 | 开发部门 | 4 | 猴子 |
| 2850.00 | 李四 | 销售部门 | 4 | 刘振 |
| 2850.00 | 王二 | 销售部门 | 4 | 刘振 |
| 5000.00 | 李武 | 后勤部门 | 5 | 哪吒 |
| 2850.00 | 周六 | 销售部门 | 4 | 刘振 |
| 5000.00 | 刘振 | 后勤部门 | 5 | 哪吒 |
| 5000.00 | 曹阳 | 后勤部门 | 5 | 哪吒 |
| 2975.00 | 爱丽丝 | 开发部门 | 4 | 李武 |
| 2850.00 | 赵云 | 销售部门 | 4 | 刘振 |
| 3000.00 | 妲己 | 开发部门 | 4 | 爱丽丝 |
| 2850.00 | 刘备 | 销售部门 | 4 | 刘振 |
| 2975.00 | 猴子 | 开发部门 | 4 | 李武 |
| 2450.00 | 安其拉 | 后勤部门 | 4 | 曹阳 |
±--------±----------±-------------±------±-------------+
23、列出与‘妲己’从事相同工作的所有员工及部门名称
#第一步先找到和妲己同一个部门的部门编号，然后通过找到的这个部门编号再去查询素有的员工信息
SELECT dept.deptno FROM dept JOIN emp ON emp.deptno = dept.deptno WHERE emp.ename=‘妲己’
#第二步通过部门编号去找所有的员工信息
SELECT emp.ename, dept.dname
FROM emp JOIN dept ON emp.deptno=dept.deptno
WHERE dept.deptno=(SELECT dept.deptno FROM dept JOIN emp
ON emp.deptno = dept.deptno WHERE emp.ename=‘妲己’)
±----------±-------------+
| ename | dname |
±----------±-------------+
| 张三 | 开发部门 |
| 李武 | 开发部门 |
| 爱丽丝 | 开发部门 |
| 妲己 | 开发部门 |
| 猴子 | 开发部门 |
±----------±-------------+
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
#第一步首先是先查出部们编号等于30的员工
SELECT DISTINCT sal FROM emp WHERE deptno = 30
#第二步是查询所有部门的员工的薪水在上一个SQL中，且部门编号不等于30的
SELECT ename,sal FROM emp WHERE sal
in(SELECT DISTINCT sal FROM emp WHERE deptno = 30) AND deptno != 30
±----------±-------+
| ename | sal |
±----------±-------+
| 安其拉 | 950.00 |
±----------±-------+
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
#第一步其实就是找出部门编号为30的员工的最高薪水
SELECT MAX(sal) FROM emp WHERE deptno = 30
#第二步就是把员工表和部门表联合然后薪水的条件大于上面的SQL语句的薪水
SELECT emp.ename,emp.sal,dept.dname FROM emp JOIN
dept ON emp.deptno=dept.deptno
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)
±----------±--------±-------------+
| ename | sal | dname |
±----------±--------±-------------+
| 李武 | 2975.00 | 开发部门 |
| 爱丽丝 | 3000.00 | 开发部门 |
| 哪吒 | 5000.00 | 后勤部门 |
| 猴子 | 3000.00 | 开发部门 |
±----------±--------±-------------+
26、列出在每个部门工作的员工数量，平均工资和平均服务期限
#第一步先按部门编号分组去找出平均工资和员工数量，因为有的部门是没有员工的
SELECT dept.deptno,COUNT(emp.ename),IFNULL(AVG(sal),0)AS ‘平均工资’ ,
IFNULL(((TO_DAYS(now())-TO_DAYS(emp.hiredate))/365),0) AS ‘平均服务期限’
FROM emp
RIGHT JOIN dept
ON emp.deptno=dept.deptno
GROUP BY dept.deptno
#总结：这里用到了ifNull(A,B)是用来判断如果A为空，就会把值设置为B
#TO_days(A)是把时间转换为天数的函数
±-------±-----------------±-------------±-------------------+
| deptno | COUNT(emp.ename) | 平均工资 | 平均服务期限 |
±-------±-----------------±-------------±-------------------+
| 10 | 3 | 2800.000000 | 37.4192 |
| 20 | 5 | 2175.000000 | 37.8959 |
| 30 | 6 | 1566.666667 | 37.7178 |
| 40 | 0 | 0.000000 | 0.0000 |
±-------±-----------------±-------------±-------------------+
27、列出所有员工的姓名、部门名称、工资
#这个有点太简单了直接就能写出来了
SELECT emp.ename,emp.sal ,dept.dname FROM emp JOIN dept ON emp.deptno=dept.deptno
±----------±--------±-------------+
| ename | sal | dname |
±----------±--------±-------------+
| 张三 | 800.00 | 开发部门 |
| 李四 | 1600.00 | 销售部门 |
| 王二 | 1250.00 | 销售部门 |
| 李武 | 2975.00 | 开发部门 |
| 周六 | 1250.00 | 销售部门 |
| 刘振 | 2850.00 | 销售部门 |
| 曹阳 | 2450.00 | 后勤部门 |
| 爱丽丝 | 3000.00 | 开发部门 |
| 哪吒 | 5000.00 | 后勤部门 |
| 赵云 | 1500.00 | 销售部门 |
| 妲己 | 1100.00 | 开发部门 |
| 刘备 | 950.00 | 销售部门 |
| 猴子 | 3000.00 | 开发部门 |
| 安其拉 | 950.00 | 后勤部门 |
±----------±--------±-------------+
28、列出所有部门的详细信息和人数
#首先思路就是直接按照部门编号进行分组，然后对需要考虑到左联或者右联接
SELECT dept.* ,COUNT(emp.ename)AS ‘部门人数’ FROM dept LEFT JOIN emp ON emp.deptno=dept.deptno
GROUP BY dept.deptno
±-------±-------------±---------±-------------+
| deptno | dname | loc | 部门人数 |
±-------±-------------±---------±-------------+
| 10 | 后勤部门 | NEW YORK | 3 |
| 20 | 开发部门 | DALLAS | 5 |
| 30 | 销售部门 | CHICAGO | 6 |
| 40 | 推广部门 | BOSTON | 0 |
±-------±-------------±---------±-------------+
29、列出各种工作的最低工资以及从事此工作的雇员姓名
SELECT MIN(emp.sal),ename,job FROM emp GROUP BY job ORDER BY sal
±-------------±----------±----------+
| MIN(emp.sal) | ename | job |
±-------------±----------±----------+
| 800.00 | 张三 | CLERK |
| 1250.00 | 李四 | SALESMAN |
| 2450.00 | 李武 | MANAGER |
| 3000.00 | 爱丽丝 | ANALYST |
| 5000.00 | 哪吒 | PRESIDENT |
±-------------±----------±----------+
30、列出各个部门的manager的最低薪金
SELECT deptno,MIN(sal ) FROM emp WHERE job = ‘MANAGER’ GROUP BY deptno
±-------±----------+
| deptno | MIN(sal ) |
±-------±----------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
±-------±----------+
31、列出员工的年工资，按年薪从低到高排序
SELECT (sal*12) AS total ,emp.ename FROM emp ORDER BY total
±---------±----------+
| total | ename |
±---------±----------+
| 9600.00 | 张三 |
| 11400.00 | 刘备 |
| 11400.00 | 安其拉 |
| 13200.00 | 妲己 |
| 15000.00 | 王二 |
| 15000.00 | 周六 |
| 18000.00 | 赵云 |
| 19200.00 | 李四 |
| 29400.00 | 曹阳 |
| 34200.00 | 刘振 |
| 35700.00 | 李武 |
| 36000.00 | 爱丽丝 |
| 36000.00 | 猴子 |
| 60000.00 | 哪吒 |
±---------±----------+
32、求出员工领导的薪水超过3000的员工名称与领导姓名
SELECT a.ename AS ‘员工名称’,b.ename AS ‘领导姓名’ ,b.sal
FROM emp AS a JOIN emp AS b ON a.mgr=b.empno WHERE b.sal>3000
±-------------±-------------±--------+
| 员工名称 | 领导姓名 | sal |
±-------------±-------------±--------+
| 李武 | 哪吒 | 5000.00 |
| 刘振 | 哪吒 | 5000.00 |
| 曹阳 | 哪吒 | 5000.00 |
±-------------±-------------±--------+
33、求出部门名称中，带有‘s’字符的部门员工的工资合计、部门人数
#第一步先把部门表和员工表进行连接，需要注意当部门编号为40的时候是没有员工的，
#然后按照员工编号进行分组
SELECT IFNULL(SUM(emp.sal),0),COUNT(emp.empno) FROM dept LEFT JOIN emp
ON dept.deptno=emp.deptno GROUP BY dept.deptno
#第二步因为说把含有“s”字符的部门员工都统计到，需要在上面的基础上加上like限定
SELECT dept.deptno, IFNULL(SUM(emp.sal),0),COUNT(emp.ename)
FROM dept LEFT JOIN emp
ON dept.deptno=emp.deptno
WHERE dept.dname LIKE ‘%部%’
GROUP BY dept.deptno
±-------±-----------------------±-----------------+
| deptno | IFNULL(SUM(emp.sal),0) | COUNT(emp.ename) |
±-------±-----------------------±-----------------+
| 10 | 8400.00 | 3 |
| 20 | 10875.00 | 5 |
| 30 | 9400.00 | 6 |
| 40 | 0.00 | 0 |
±-------±-----------------------±-----------------+
34、给任职日期超过35年的员工加薪10%
CREATE TABLE emp_bak AS SELECT * FROM emp
UPDATE emp_bak SET sal = sal*1.1
WHERE (TO_DAYS(NOW())-(TO_DAYS(hiredate)))/365 > 30
扫一扫
2066
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
