在这篇文章中，我们将为您详细介绍SQL语句练习实例之四找出促销活动中销售额最高的职员的内容，并且讨论关于由sales表中查找出销售金额最高的订单的相关问题。此外，我们还会涉及一些关于-sql语句练习5
在这篇文章中，我们将为您详细介绍SQL语句练习实例之四 找出促销活动中销售额最高的职员的内容，并且讨论关于由sales表中查找出销售金额最高的订单的相关问题。此外，我们还会涉及一些关于-sql语句练习50题(Mysql学习练习版)、My SQL语句练习题、MySQL经典练习题及答案，常用SQL语句练习50题、MySQL语句练习的知识，以帮助您更全面地了解这个主题。
本文目录一览：- SQL语句练习实例之四 找出促销活动中销售额最高的职员（由sales表中查找出销售金额最高的订单）
- -sql语句练习50题(Mysql学习练习版)
- My SQL语句练习题
- MySQL经典练习题及答案，常用SQL语句练习50题
- MySQL语句练习
SQL语句练习实例之四 找出促销活动中销售额最高的职员（由sales表中查找出销售金额最高的订单）
复制代码 代码如下:
---找出促销活动中销售额最高的职员
---你刚在一家服装销售公司中找到了一份工作，此时经理要求你根据数据库中的两张表得到促销活动销售额最高的销售员
---1.一张是促销活动表
---2.一张是销售客列表
create table Promotions
(
activity nvarchar(30),
sdate datetime,
edate datetime
)
insert Promotions
select ''五一促销活动'',''2011-5-1'',''2011-5-7''
union
select ''十一促销活动'',''2011-10-1'',''2011-10-7''
union
select ''OA专场活动'',''2011-6-1'',''2011-6-7''
go
create table sales
(
id int not null,
name nvarchar(20),
saledate datetime,
price money
)
go
insert sales
select 1,''王五'',''2011-5-1'',1000 union
select 1,''王五'',''2011-5-2'',2000 union
select 1,''王五'',''2011-5-3'',3000 union
select 1,''王五'',''2011-5-4'',4000 union
select 1,''张三'',''2011-5-1'',1000 union
select 1,''张三'',''2011-5-3'',2000 union
select 1,''张三'',''2011-5-4'',4000 union
select 1,''李四'',''2011-5-6'',1000 union
select 1,''赵六'',''2011-5-5'',1000 union
select 1,''钱七'',''2011-5-8'',1000 union
select 1,''孙五'',''2011-6-1'',1000 union
select 1,''孙五'',''2011-6-2'',2000 union
select 1,''王五'',''2011-6-3'',3000 union
select 1,''孙五'',''2011-6-4'',4000 union
select 1,''张三'',''2011-6-1'',11000 union
select 1,''张三'',''2011-6-3'',20000 union
select 1,''张三'',''2011-6-4'',4000 union
select 1,''李四'',''2011-6-6'',1000 union
select 1,''赵六'',''2011-6-5'',1000 union
select 1,''钱七'',''2011-6-8'',1500 union
select 1,''孙五'',''2011-10-1'',11000 union
select 1,''孙五'',''2011-10-2'',12000 union
select 1,''王五'',''2011-10-3'',9000 union
select 1,''孙五'',''2011-10-4'',4000 union
select 1,''张三'',''2011-10-1'',11000 union
select 1,''张三'',''2011-10-3'',2000 union
select 1,''张三'',''2011-10-4'',4000 union
select 1,''李四'',''2011-10-6'',27000 union
select 1,''赵六'',''2011-10-5'',9000 union
select 1,''钱七'',''2011-10-8'',3000
go
-----我们需要找出在每次的促销活动中，其销售总额大于 等于
---所有其他职员销售额的职员及促销事件。
---说明：谓词a2.name<>a.name将其他职员从子查询合计中排除出去
---------谓词Between 中的子查询确保我们使用了正确的促销日期
--方法一：
select a.name,b.activity,SUM(a.price) as totalprice
from sales a ,Promotions as b
where a.saledate between b.sdate and b.edate
group by a.name,b.activity
having SUM(price)>= all(select SUM(price) from sales a2
where a2.name<>a.name and a2.saledate between
(
select sdate from Promotions as b2 where b2.activity=b.activity
)
and (select edate from Promotions b3
where b3.activity=b.activity)
group by a2.name)
-----------------
---方法二：
---说明： 如果促销活动时间是不重叠的，则promotions表中只有一个主键列，这样在group by
--子句中使用(activity,sdate,edate)将不会改变。但是它将使having子句可以使用sdate和edate
select a.name,b.activity,SUM(a.price) as totalprice
from sales a ,Promotions as b
where a.saledate between b.sdate and b.edate
group by b.activity,b.sdate,b.edate,a.name
having SUM(price)>= all(select SUM(price) from sales a2
where a2.name<>a.name and a2.saledate between
b.sdate
and b.edate
group by a2.name)
go
--方法三：
---使用cte(sql 2005以后的版本)
with clearksTotal(name,activity,totalprice) as
(
select a.name,b.activity,SUM(price)
from sales a ,Promotions b
where a.saledate between b.sdate and b.edate
group by a.name,b.activity
)
select c1.name,c1.activity,c1.totalprice
from clearksTotal c1
where totalprice=(select MAX(c2.totalprice) from clearksTotal c2
where c1.activity=c2.activity)
go
drop table Promotions
go
drop table sales
您可能感兴趣的文章:
- SQL语句练习实例之六 人事系统中的缺勤（休假）统计
- SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析
- SQL语句练习实例之一——找出最近的两次晋升日期与工资额
- SQL语句练习实例之二——找出销售冠军
- SQL语句练习实例之三——平均销售等待时间
- SQL语句练习实例之七 剔除不需要的记录行
-sql语句练习50题(Mysql学习练习版)
–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
测试数据
--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '''',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '''',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '''',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '''',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '''',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
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'' , ''女'');
--课程表测试数据
insert into Course values(''01'' , ''语文'' , ''02'');
insert into Course values(''02'' , ''数学'' , ''01'');
insert into Course values(''03'' , ''英语'' , ''03'');
--教师表测试数据
insert into Teacher values(''01'' , ''张三'');
insert into Teacher values(''02'' , ''李四'');
insert into Teacher values(''03'' , ''王五'');
--成绩表测试数据
insert into Score values(''01'' , ''01'' , 80);
insert into Score values(''01'' , ''02'' , 90);
insert into Score values(''01'' , ''03'' , 99);
insert into Score values(''02'' , ''01'' , 70);
insert into Score values(''02'' , ''02'' , 60);
insert into Score values(''02'' , ''03'' , 80);
insert into Score values(''03'' , ''01'' , 80);
insert into Score values(''03'' , ''02'' , 80);
insert into Score values(''03'' , ''03'' , 80);
insert into Score values(''04'' , ''01'' , 50);
insert into Score values(''04'' , ''02'' , 30);
insert into Score values(''04'' , ''03'' , 20);
insert into Score values(''05'' , ''01'' , 76);
insert into Score values(''05'' , ''02'' , 87);
insert into Score values(''06'' , ''01'' , 31);
insert into Score values(''06'' , ''03'' , 34);
insert into Score values(''07'' , ''02'' , 89);
insert into Score values(''07'' , ''03'' , 98);
练习题和sql语句
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a
join score b on a.s_id=b.s_id and b.c_id=''01''
left join score c on a.s_id=c.s_id and c.c_id=''02'' or c.c_id = NULL where b.s_score>c.s_score
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a left join score b on a.s_id=b.s_id and b.c_id=''01'' or b.c_id=NULL
join score c on a.s_id=c.s_id and c.c_id=''02'' where b.s_score<c.s_score
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)>=60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
left join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60
union
select a.s_id,a.s_name,0 as avg_score from
student a
where a.s_id not in (
select distinct s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from
student a
left join score b on a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;
-- 6、查询"李"姓老师的数量
select count(t_id) from teacher where t_name like ''李%'';
-- 7、查询学过"张三"老师授课的同学的信息
select a.* from
student a
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三''));
-- 8、查询没学过"张三"老师授课的同学的信息
select * from
student c
where c.s_id not in(
select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三'')));
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* from
student a,score b,score c
where a.s_id = b.s_id and a.s_id = c.s_id and b.c_id=''01'' and c.c_id=''02'';
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from
student a
where a.s_id in (select s_id from score where c_id=''01'' ) and a.s_id not in(select s_id from score where c_id=''02'')
-- 11、查询没有学全所有课程的同学的信息
select s.* from
student s where s.s_id in(
select s_id from score where s_id not in(
select a.s_id from score a
join score b on a.s_id = b.s_id and b.c_id=''02''
join score c on a.s_id = c.s_id and c.c_id=''03''
where a.c_id=''01''))
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in(
select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id=''01'')
);
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.* from student a where a.s_id in(
select distinct s_id from score where s_id!=''01'' and c_id in(select c_id from score where s_id=''01'')
group by s_id
having count(1)=(select count(1) from score where s_id=''01''));
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select a.s_name from student a where a.s_id not in (
select s_id from score where c_id =
(select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三''))
group by s_id);
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from
student a
left join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.*,b.c_id,b.s_score from
student a,score b
where a.s_id = b.s_id and b.c_id=''01'' and b.s_score<60 ORDER BY b.s_score DESC;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,(select s_score from score where s_id=a.s_id and c_id=''01'') as 语文,
(select s_score from score where s_id=a.s_id and c_id=''02'') as 数学,
(select s_score from score where s_id=a.s_id and c_id=''03'') as 英语,
round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
select a.s_id,a.c_id,
@i:=@i +1 as i保留排名,
@k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''01'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''02'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''03'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
-- 20、查询学生的总成绩并进行排名
select a.s_id,
@i:=@i+1 as i,
@k:=(case when @score=a.sum_score then @k else @i end) as rank,
@score:=a.sum_score as score
from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
(select @k:=0,@i:=0,@score:=0)s
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
left join score b on a.c_id=b.c_id
left join teacher c on a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id=''01''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@j:=@j+1 as 排名 from score a,(select @j:=0)s where a.c_id=''02''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@k:=@k+1 as 排名 from score a,(select @k:=0)s where a.c_id=''03''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select distinct f.c_name,a.c_id,b.`85-100`,b.百分比,c.`70-85`,c.百分比,d.`60-70`,d.百分比,e.`0-60`,e.百分比 from score a
left join (select c_id,SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)b on a.c_id=b.c_id
left join (select c_id,SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
ROUND(100*(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)c on a.c_id=c.c_id
left join (select c_id,SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70`,
ROUND(100*(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)d on a.c_id=d.c_id
left join (select c_id,SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60`,
ROUND(100*(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)e on a.c_id=e.c_id
left join course f on a.c_id = f.c_id
---也可以这么做
SELECT
c.c_id,
c.c_name,
a.`85-100`,
a.`85-100百分比`,
b.`70-85`,
b.`70-85百分比`,
ce.`60-70`,
ce.`60-70百分比`,
d.`0-60`,
d.`0-60百分比`
FROM course c
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "85-100",ROUND(100*SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END)/count(1),2) as "85-100百分比" FROM score s GROUP BY s.c_id) a ON a.c_id = c.c_id
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "70-85",ROUND(100*SUM(CASE WHEN s.s_score >70 AND s.s_score <= 85 THEN 1 ELSE 0 END)/count(1),2) as "70-85百分比" FROM score s GROUP BY s.c_id) b ON b.c_id = c.c_id
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "60-70",ROUND(100*SUM(CASE WHEN s.s_score >60 AND s.s_score <= 70 THEN 1 ELSE 0 END)/count(1),2) as "60-70百分比" FROM score s GROUP BY s.c_id) ce ON ce.c_id = c.c_id
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "0-60",ROUND(100*SUM(CASE WHEN s.s_score >0 AND s.s_score <= 60 THEN 1 ELSE 0 END)/count(1),2) as "0-60百分比" FROM score s GROUP BY s.c_id) d ON d.c_id = c.c_id
-- 24、查询学生平均成绩及其名次
select a.s_id,
@i:=@i+1 as ''不保留空缺排名'',
@k:=(case when @avg_score=a.avg_s then @k else @i end) as ''保留空缺排名'',
@avg_score:=avg_s as ''平均分''
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id)a,(select @avg_score:=0,@i:=0,@k:=0)b;
-- 25、查询各科成绩前三名的记录
-- 1.选出b表比a表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的
select a.s_id,a.c_id,a.s_score from score a
left join score b on a.c_id = b.c_id and a.s_score<b.s_score
group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
ORDER BY a.c_id,a.s_score DESC
-- 26、查询每门课程被选修的学生数
select c_id,count(s_id) from score a GROUP BY c_id
-- 27、查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name from student where s_id in(
select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);
---另外一种写法
SELECT
st.s_id,
st.s_name
FROM student st
INNER JOIN(
SELECT
s.s_id
FROM score s
GROUP BY s_id
HAVING COUNT(s.c_id) = 2
) a ON a.s_id = st.s_id;
-- 28、查询男生、女生人数
select s_sex,COUNT(s_sex) as 人数 from student GROUP BY s_sex
-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like ''%风%'';
-- 30、查询同名同性学生名单，并统计同名人数
select a.s_name,a.s_sex,count(*) from student a JOIN
student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex
-- 31、查询1990年出生的学生名单
select s_name from student where s_birth like ''1990%''
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_id=b.s_id where b.c_id=(
select c_id from course where c_name =''数学'') and b.s_score<60
----另外一种自己的写法
SELECT
st.s_name,
s.s_score
FROM student st
INNER JOIN score s ON s.s_id = st.s_id
INNER JOIN course c ON c.c_id = s.c_id AND c.c_name = ''数学''
WHERE s.s_score < 60;
-- 35、查询所有学生的课程及分数情况；
select a.s_id,a.s_name,
SUM(case c.c_name when ''语文'' then b.s_score else 0 end) as ''语文'',
SUM(case c.c_name when ''数学'' then b.s_score else 0 end) as ''数学'',
SUM(case c.c_name when ''英语'' then b.s_score else 0 end) as ''英语'',
SUM(b.s_score) as ''总分''
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name
--注意case when...和case...when的用法
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
left join student a on a.s_id=c.s_id where c.s_score>=70
----另外一种过滤方法
SELECT
st.s_name,
c.c_name,
s.s_score
FROM course c
LEFT JOIN score s on s.c_id = c.c_id AND s.s_score > 70
LEFT JOIN student st ON st.s_id = s.s_id
-- 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
where a.s_score<60
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
where a.c_id = ''01'' and a.s_score>80
-- 39、求每门课程的学生人数
select count(*) from score GROUP BY c_id;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 查询老师id
select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=''张三''
-- 查询最高分（可能有相同分数）
select MAX(s_score) from score where c_id=''02''
-- 查询信息
select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=''张三'')
and b.s_score in (select MAX(s_score) from score where c_id=''02'')
--可以将上面组织一下，一次查询出来如下：
SELECT
st.*
FROM student st
left JOIN score s on s.s_id = st.s_id
left JOIN course c on c.c_id = s.c_id
WHERE s.c_id = (SELECT c_id FROM course c,teacher t WHERE c.t_id = t.t_id AND t.t_name LIKE ''张三'')
AND s.s_score in (SELECT max(s_score) FROM score WHERE c_id = (SELECT c_id FROM course c,teacher t WHERE c.t_id = t.t_id AND t.t_name LIKE ''张三''))
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
-- 42、查询每门功成绩最好的前两名
-- 牛逼的写法
select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC
-- 44、检索至少选修两门课程的学生学号
select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2
-- 45、查询选修了全部课程的学生信息
select * from student where s_id in(
select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))
--46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_birth,(DATE_FORMAT(NOW(),''%Y'')-DATE_FORMAT(s_birth,''%Y'') -
(case when DATE_FORMAT(NOW(),''%m%d'')>DATE_FORMAT(s_birth,''%m%d'') then 0 else 1 end)) as age
from student;
-- 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))=WEEK(s_birth)
select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),''%Y%m%d''))
select WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))
-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))+1 =WEEK(s_birth)
-- 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),''%Y%m%d'')) =MONTH(s_birth)
-- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),''%Y%m%d''))+1 =MONTH(s_birth)
My SQL语句练习题
CREATE TABLE student
(sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5));
CREATE TABLE course
(cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL);
CREATE TABLE score
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL);
CREATE TABLE teacher
(tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL, tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL, prof VARCHAR(6),
depart VARCHAR(10) NOT NULL);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,''曾华'' ,''男'' ,''1977-09-01'',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,''匡明'' ,''男'' ,''1975-10-02'',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,''王丽'' ,''女'' ,''1976-01-23'',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,''李军'' ,''男'' ,''1976-02-20'',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,''王芳'' ,''女'' ,''1975-02-10'',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,''陆君'' ,''男'' ,''1974-06-03'',95031);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (''3-105'' ,''计算机导论'',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (''3-245'' ,''操作系统'' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (''6-166'' ,''数据电路'' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (''9-888'' ,''高等数学'' ,100);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,''3-245'',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,''3-245'',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,''3-245'',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,''3-105'',92);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,''3-105'',88);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,''3-105'',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,''3-105'',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,''3-105'',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,''3-105'',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,''6-166'',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,''6-106'',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,''6-166'',81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,''李诚'',''男'',''1958-12-02'',''副教授'',''计算机系'');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,''张旭'',''男'',''1969-03-12'',''讲师'',''电子工程系'');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,''王萍'',''女'',''1972-05-05'',''助教'',''计算机系'');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,''刘冰'',''女'',''1977-08-14'',''助教'',''电子工程系'');
# 11、查询‘3-105’号课程的平均分。
select AVG(score.degree),course.cno
from course join score
on score.cno = course.cno and course.cno=''3-105'';
# 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select course.cname,AVG(score.degree)
from student join score
on student.sno = score.sno
join course on score.cno=course.cno
and course.cno like ''3%''
group by course.cname
having count(score.cno)>= 5;
# 13、查询最低分大于70，最高分小于90的Sno列。
select score.sno from score
group by score.sno
having MIN(score.degree) >70 and MAX(score.degree)<90;
# 14、查询所有学生的Sname、Cno和Degree列。
select student.sname,score.cno,score.degree
from student join score
on student.sno = score.sno;# 15、查询所有学生的Sno、Cname和Degree列。
select score.sno,course.cname,score.degree
from score join course
on score.cno = course.cno;
# 16、查询所有学生的Sname、Cname和Degree列。
select student.sname,course.cname,score.degree
from student join score
on student.sno = score.sno
join course
on course.cno=score.cno;
# 17、查询“95033”班所选课程的平均分。
select AVG(score.degree)
from score join student
on score.sno=student.sno
and student.class = ''95033'';
# 18、假设使用如下命令建立了一个grade表：
create table grade(low INTEGER,upp INTEGER,rank VARCHAR(1));
insert into grade values(90,100,''A'');
insert into grade values(80,89,''B'');
insert into grade values(70,79,''C'');
insert into grade values(60,69,''D'');
insert into grade values(0,59,''E'');
# commit;
# 现查询所有同学的Sno、Cno和rank列。
select score.sno,score.cno,grade.rank
from grade join score
on score.degree>grade.low and score.degree<grade.upp;
# 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select student.* from student join score
on student.sno = score.sno
and score.cno =''3-105''and score.degree >
(select score.degree from score
where score.sno=''109''and score.cno=''3-105'');
# 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select score.sno,score.cno,score.degree,cmax
from score inner join(
select cno,max(degree) as cmax
from score group by cno
) as mc
on score.degree < cmax and score.cno = mc.cno
where sno in(
select sno from score
group by sno
having count(*) > 1
);# 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select score.* from student join score
on student.sno =score.sno and
score.cno=''3-105''and score.degree>
(select score.degree from score
where score.sno = ''109''and score.cno = ''3-105'');
# 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select student.sno,sname,sbirthday from student join(
select year(sbirthday),sno from student
where sno =''108''
) as sb
on student.sno = sb.sno and student.sno !=''108'';
# 23、查询“张旭“教师任课的学生成绩。
select score.degree
from score join course join teacher
on score.cno=course.cno and course.tno = teacher.tno
where teacher.tname =''张旭'';
# 24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher join course
on teacher.tno = course.tno join score
on score.cno = course.cno
group by tname
having count(score.sno)>5;
# 25、查询95033班和95031班全体学生的记录。
select * from student
where class = ''95033''or ''95031'';
# 26、查询存在有85分以上成绩的课程Cno.
select course.cno from course join score
on course.cno = score.cno
and score.degree>85;
# 27、查询出“计算机系“教师所教课程的成绩表。
select score.* from score join course
on score.cno = course.cno join teacher
on teacher.tno = course.tno and
teacher.depart=''计算机系'';
# 28、查询在“计算机系”中, 与“电子工程系“不同职称的教师的Tname和Prof。
select tname,prof from teacher
where depart =''计算机系'' and prof not in (
select teacher.prof
from teacher
where depart = ''电子工程系'');
# 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select score.cno,sno,degree from score join course
on course.cno=score.cno
where score.cno=''3-105'' and degree >=
any ( select score.degree
from score
where score.cno = ''3-245''
)
order by degree desc;
# 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select score.cno,sno,degree from score join course
on score.cno=course.cno
where score.cno=''3-105''and degree>= any (select score.degree from score
where score.cno=''3-245'');
# 31、查询所有教师和同学的name、sex和birthday.
select student.sname,student.ssex,student.sbirthday
from student union
select teacher.tname,teacher.tsex,teacher.tbirthday
from teacher;
# 32、查询所有“女”教师和“女”同学的name、sex和birthday.
select student.sname,student.ssex,student.sbirthday from studentwhere ssex=''女'' unionselect teacher.tname,teacher.tsex,teacher.tbirthday from teacherwhere tsex=''女'';
# 33、查询成绩比该课程平均成绩低的同学的成绩表。
select * from score as a
where a.degree <
(select AVG(score.degree) from score
where score.cno=a.cno );
# 34、查询所有任课教师的Tname和Depart.
select teacher.tname,depart from teacher join course
on teacher.tno= course.tno;
# 35 查询所有未讲课的教师的Tname和Depart.
select teacher.tname,depart from teacher
where tno not in (select course.tno from course
where course.tno=teacher.tno);
# 36、查询至少有2名男生的班号。
select student.class from student
where ssex=''男''
group by class
having count(*)>=2;
# 37、查询Student表中不姓“王”的同学记录。
select student.* from student
where sname not like ''王%'';
# 38、查询Student表中每个学生的姓名和年龄。
select student.sname,year(now())-year(sbirthday)
from student;
# 39、查询Student表中最大和最小的Sbirthday日期值。
select MAX(sbirthday),MIN(sbirthday) from student;
# 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select student.* from student
order by class desc,sbirthday ;
# 41、查询“男”教师及其所上的课程。
select teacher.*,course.cname from teacher join course
on course.tno=teacher.tno
and teacher.tsex=''男'';
# 42、查询最高分同学的Sno、Cno和Degree列。
select score.sno,cno,degree from score
where degree=(select MAX(degree) from score);
# 43、查询和“李军”同性别的所有同学的Sname.
select student.sname from student
where ssex = (select ssex from student
where sname=''李军'') and sname!=''李军'';
# 44、查询和“李军”同性别并同班的同学Sname.
select student.sname from student
where ssex=(select ssex from student
where sname=''李军'') and class=(select class from student
where sname=''李军'')and sname!=''李军'';
# 45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select score.* from score join course
on score.cno = course.cno and course.cname=''计算机导论''
join student
on score.sno = student.sno and student.ssex=''男'';
MySQL经典练习题及答案，常用SQL语句练习50题
表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
SQL文件
链接：https://pan.baidu.com/s/1Ss0bnRY5ylWFnJEzcQ9ckA
提取码：pjnb
练习题和sql语句
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a
join score b on a.s_id=b.s_id and b.c_id=''01''
left join score c on a.s_id=c.s_id and c.c_id=''02'' or c.c_id = NULL where b.s_score>c.s_score
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a left join score b on a.s_id=b.s_id and b.c_id=''01'' or b.c_id=NULL
join score c on a.s_id=c.s_id and c.c_id=''02'' where b.s_score<c.s_score
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)>=60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
left join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60
union
select a.s_id,a.s_name,0 as avg_score from
student a
where a.s_id not in (
select distinct s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from
student a
left join score b on a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;
-- 6、查询"李"姓老师的数量
select count(t_id) from teacher where t_name like ''李%'';
-- 7、查询学过"张三"老师授课的同学的信息
select a.* from
student a
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三''));
-- 8、查询没学过"张三"老师授课的同学的信息
select * from
student c
where c.s_id not in(
select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三'')));
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* from
student a,score b,score c
where a.s_id = b.s_id and a.s_id = c.s_id and b.c_id=''01'' and c.c_id=''02'';
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from
student a
where a.s_id in (select s_id from score where c_id=''01'' ) and a.s_id not in(select s_id from score where c_id=''02'')
-- 11、查询没有学全所有课程的同学的信息
select s.* from
student s where s.s_id in(
select s_id from score where s_id not in(
select a.s_id from score a
join score b on a.s_id = b.s_id and b.c_id=''02''
join score c on a.s_id = c.s_id and c.c_id=''03''
where a.c_id=''01''))
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in(
select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id=''01'')
);
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.* from student a where a.s_id in(
select distinct s_id from score where s_id!=''01'' and c_id in(select c_id from score where s_id=''01'')
group by s_id
having count(1)=(select count(1) from score where s_id=''01''));
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select a.s_name from student a where a.s_id not in (
select s_id from score where c_id =
(select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三''))
group by s_id);
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from
student a
left join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.*,b.c_id,b.s_score from
student a,score b
where a.s_id = b.s_id and b.c_id=''01'' and b.s_score<60 ORDER BY b.s_score DESC;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,(select s_score from score where s_id=a.s_id and c_id=''01'') as 语文,
(select s_score from score where s_id=a.s_id and c_id=''02'') as 数学,
(select s_score from score where s_id=a.s_id and c_id=''03'') as 英语,
round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
select a.s_id,a.c_id,
@i:=@i +1 as i保留排名,
@k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''01'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''02'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''03'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
-- 20、查询学生的总成绩并进行排名
select a.s_id,
@i:=@i+1 as i,
@k:=(case when @score=a.sum_score then @k else @i end) as rank,
@score:=a.sum_score as score
from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
(select @k:=0,@i:=0,@score:=0)s
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
left join score b on a.c_id=b.c_id
left join teacher c on a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id=''01''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@j:=@j+1 as 排名 from score a,(select @j:=0)s where a.c_id=''02''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@k:=@k+1 as 排名 from score a,(select @k:=0)s where a.c_id=''03''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select distinct f.c_name,a.c_id,b.`85-100`,b.百分比,c.`70-85`,c.百分比,d.`60-70`,d.百分比,e.`0-60`,e.百分比 from score a
left join (select c_id,SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)b on a.c_id=b.c_id
left join (select c_id,SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
ROUND(100*(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)c on a.c_id=c.c_id
left join (select c_id,SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70`,
ROUND(100*(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)d on a.c_id=d.c_id
left join (select c_id,SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60`,
ROUND(100*(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)e on a.c_id=e.c_id
left join course f on a.c_id = f.c_id
-- 24、查询学生平均成绩及其名次
select a.s_id,
@i:=@i+1 as ''不保留空缺排名'',
@k:=(case when @avg_score=a.avg_s then @k else @i end) as ''保留空缺排名'',
@avg_score:=avg_s as ''平均分''
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id)a,(select @avg_score:=0,@i:=0,@k:=0)b;
-- 25、查询各科成绩前三名的记录
-- 1.选出b表比a表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的
select a.s_id,a.c_id,a.s_score from score a
left join score b on a.c_id = b.c_id and a.s_score<b.s_score
group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
ORDER BY a.c_id,a.s_score DESC
-- 26、查询每门课程被选修的学生数
select c_id,count(s_id) from score a GROUP BY c_id
-- 27、查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name from student where s_id in(
select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);
-- 28、查询男生、女生人数
select s_sex,COUNT(s_sex) as 人数 from student GROUP BY s_sex
-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like ''%风%'';
-- 30、查询同名同性学生名单，并统计同名人数
select a.s_name,a.s_sex,count(*) from student a JOIN
student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex
-- 31、查询1990年出生的学生名单
select s_name from student where s_birth like ''1990%''
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_id=b.s_id where b.c_id=(
select c_id from course where c_name =''数学'') and b.s_score<60
-- 35、查询所有学生的课程及分数情况；
select a.s_id,a.s_name,
SUM(case c.c_name when ''语文'' then b.s_score else 0 end) as ''语文'',
SUM(case c.c_name when ''数学'' then b.s_score else 0 end) as ''数学'',
SUM(case c.c_name when ''英语'' then b.s_score else 0 end) as ''英语'',
SUM(b.s_score) as ''总分''
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
left join student a on a.s_id=c.s_id where c.s_score>=70
-- 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
where a.s_score<60
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
where a.c_id = ''01'' and a.s_score>80
-- 39、求每门课程的学生人数
select count(*) from score GROUP BY c_id;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 查询老师id
select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=''张三''
-- 查询最高分（可能有相同分数）
select MAX(s_score) from score where c_id=''02''
-- 查询信息
select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=''张三'')
and b.s_score in (select MAX(s_score) from score where c_id=''02'')
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
-- 42、查询每门功成绩最好的前两名
-- 牛逼的写法
select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC
-- 44、检索至少选修两门课程的学生学号
select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2
-- 45、查询选修了全部课程的学生信息
select * from student where s_id in(
select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))
--46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_birth,(DATE_FORMAT(NOW(),''%Y'')-DATE_FORMAT(s_birth,''%Y'') -
(case when DATE_FORMAT(NOW(),''%m%d'')>DATE_FORMAT(s_birth,''%m%d'') then 0 else 1 end)) as age
from student;
-- 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))=WEEK(s_birth)
select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),''%Y%m%d''))
select WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))
-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))+1 =WEEK(s_birth)
-- 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),''%Y%m%d'')) =MONTH(s_birth)
-- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),''%Y%m%d''))+1 =MONTH(s_birth)https://blog.csdn.net/qq_41936662/article/details/80393172
MySQL语句练习
#创建表
CREATE TABLE student(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20),
sex TINYINT(1) DEFAULT 1,
phone VARCHAR(50),
role INT(4),
score INT(10)
)
#查询表结构
DESC student;
#插入表数据
INSERT INTO student(`name`,sex,phone,role,score)
VALUES(''tom'',1,''15999999999'',1,54),(''rose'',2,''158888888888'',2,90),
(''jack'',1,''15666666666'',3,94),(''make'',1,''15555555555'',3,82);
#删除表中不需要的数据
DELETE FROM student WHERE id=2;
#查看表内容
SELECT * FROM student;
#修改rose的电话号码
UPDATE student SET phone = ''158000000000'' WHERE `name`=''rose'';
#查询不及格学员信息
SELECT * FROM student
WHERE score<60;
#查询name 是j开头的人数据
SELECT `name` FROM student
WHERE `name` LIKE ''%j%'';
#查询为1和3的数据
SELECT * FROM student WHERE role IN(1,3);
#删除role为1的数据
DELETE FROM student WHERE role=1;
#删除表数据
DELETE FROM student;
TRUNCATE student;
#删除表
DROP TABLE student;
今天的关于SQL语句练习实例之四 找出促销活动中销售额最高的职员和由sales表中查找出销售金额最高的订单的分享已经结束，谢谢您的关注，如果想了解更多关于-sql语句练习50题(Mysql学习练习版)、My SQL语句练习题、MySQL经典练习题及答案，常用SQL语句练习50题、MySQL语句练习的相关知识，请在本站进行查询。
本文标签：
