50道sql练习题

最新推荐文章于 2020-09-15 18:39:30 发布
最新推荐文章于 2020-09-15 18:39:30 发布
阅读量118 收藏 1
点赞数
分类专栏： mysql数据库学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pk5478/article/details/102641535
版权

从网上找来的50道sql题目

表结构

create table Student(SId varchar(10),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-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

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 varchar(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);

题目

  • 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
  select Student.*,Course.*  from 
  	(select * from  SC where CId = '01') as a
  INNER JOIN
  	(select * from  SC where CId = '02') as b
  ON a.SId = b.SId and a.score > b.score
  INNER JOIN
  	Student on Student.SId = a.SId
  INNER JOIN
  	Course on Course.CId = a.CId
  	
  select Student.*,Course.*  from 
  	SC as a
  INNER JOIN
  	SC as b
  	ON a.SId = b.SId and a.score > b.score and b.CId='02'
  INNER JOIN
  	Student ON Student.SId = a.SId
  INNER JOIN
  	Course ON Course.CId = a.CId
  where a.CId = '01'
  • 1.1 查询同时存在" 01 “课程和” 02 "课程的情况
select * from SC as a INNER JOIN SC as b on a.SId=b.SId and b.CId='02' where a.CId = '01'
  • 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select * from SC as a LEFT JOIN SC as b on a.SId=b.SId and b.CId='02' where a.CId = '01'
  • 1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
select * from SC as a LEFT JOIN SC as b on a.SId = b.SId and b.CId='01'  where a.CId="02" and b.SId is null
  • 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select SC.SId,AVG(SC.score),Student.Sname from SC INNER JOIN Student ON SC.SId = Student.SId group by SC.SId HAVING AVG(SC.score) >= 60
  • 查询在 SC 表存在成绩的学生信息
select DISTINCT Student.*  from SC INNER JOIN Student ON SC.SId = Student.SId
  • 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT Student.*, COUNT(SC.SId) as coursenumber, SUM(SC.score) as scoresum FROM Student LEFT JOIN SC on Student.SId = SC.SId GROUP by Student.SId
  • 4.1 查有成绩的学生信息
select DISTINCT Student.* from Student INNER JOIN SC on Student.SId = SC.SId
  • 查询「李」姓老师的数量
select count(*) from Teacher where Tname like "李%"
  • 查询学过「张三」老师授课的同学的信息
select Student.* from Teacher inner JOIN Course ON Course.TId= Teacher.TId inner JOIN SC on SC.CId = Course.CId inner JOIN Student ON Student.SId = SC.SId where Tname = "张三"
  • 查询没有学全所有课程的同学的信息
select * from Student  LEFT JOIN SC on  SC.SId = Student.SId group by Student.SId HAVING count(SC.CId) < (select count(*) FROM Course)
  • 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select DISTINCT Student.* FROM SC as a inner JOIN SC as b on a.CId = b.CId inner JOIN Student ON Student.SId = b.SId  where a.SId="02"
  • 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select
	Student.*
from
	SC as a
INNER JOIN SC as b on
	a.CId = b.CId
INNER JOIN Student on
	Student.SId = b.SId
where
	a.SId = '01'
group by
	b.SId
HAVING
	COUNT(*) = (select COUNT(*) FROM SC where SId = '01')
  • 查询没学过"张三"老师讲授的任一门课程的学生姓名
select
	Student.*
from
	SC
INNER JOIN Course on
	SC.CId = Course.CId
INNER JOIN Teacher on
	Course.TId = Teacher.TId
	and Teacher.Tname = '张三'
RIGHT JOIN Student ON
	Student.SId = SC.SId
where
	SC.SId is NULL
  • 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select
	Student.SId,
	Student.Sname,
	AVG(SC.score)
from
	SC
INNER JOIN Student on
	SC.SId = Student.SId
where
	SC.SId in (select SId from SC where score <60 GROUP by SId HAVING COUNT(*) >= 2)
group by
	SC.SId
  • 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select  * from SC INNER JOIN Student on SC.SId = Student.SId where score < 60 and CId = '01' order by score DESC
  • 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
	a.SId,
	SC.CId,
	SC.score ,
	a.avg_score
from
	(select SC.SId ,AVG(SC.score) as avg_score from SC GROUP BY SC.SId order by AVG(SC.score) desc) as a
INNER JOIN SC on
	SC.SId = a.SId
  • 查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 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(if(SC.score >= 60,1,0))/COUNT(*) as 及格率,
	SUM(if(SC.score >= 70 AND SC.score<80,1,0))/COUNT(*) as 中等率,
	SUM(if(SC.score >= 80 AND SC.score<90,1,0))/COUNT(*) as 优良率,
	SUM(if(SC.score >= 90, 1,0))/COUNT(*) as 优秀率
from SC GROUP by SC.CId
ORDER BY 人数 DESC, SC.CId asc
  • 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select
	a.*,
	COUNT(b.score) + 1 as rank
from
	SC as a
LEFT join SC as b on
	a.CId = b.CId
	and a.score < b.score
group by 
	a.SId,a.CId
order by
	a.CId asc ,
	a.score desc
  • 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select *,COUNT(tmp.h_score) + 1 as rank from 
	(select
		DISTINCT
		a.*,
		b.score as h_score
	from
		SC as a
	LEFT join SC as b on
		a.CId = b.CId
		and a.score < b.score) as tmp
group by tmp.SId,tmp.CId
order by tmp.CId asc,tmp.score desc
  • 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
set @pre_score  = 0
set @pre_rank  = 0
set @mrank = 0
select
	*,
	if(@pre_score = tmp.total,@pre_rank, @pre_rank := @mrank + 1) as rank,
	@pre_score := tmp.total,
	@mrank := @mrank + 1
from
	(
		select SId,
		sum(score) as total
	from
		SC
	group by
		SId
	order by
		sum(score)) as tmp
  • 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
set @pre_score  = 0
set @mrank = 0
select
	*,
	if(@pre_score = tmp.total,@mrank, @mrank := @mrank + 1) as rank,
	@pre_score := tmp.total
from
	(
		select SId,
		sum(score) as total
	from
		SC
	group by
		SId
	order by
		sum(score)) as tmp
  • 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select 
	SC.CId ,
	Course.Cname,
	count(*) as total, 
	SUM(IF(SC.score <60,1,0))/count(*) as `[60-0]`, 
	SUM(IF(SC.score >=60 and SC.score <70,1,0))/count(*) as `[70-60]` , 
	SUM(IF(SC.score >=70 and SC.score <85,1,0))/count(*) as `[85-70]` , 
	SUM(IF(SC.score >=85,1,0))/count(*) as `[100-85]`
from SC
INNER JOIN Course on  Course.CId = SC.CId
group by SC.CId
  • 查询各科成绩前三名的记录
select
	a.*
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.score) < 3
  • 查询每门课程被选修的学生数
select SC.CId,count(*) from SC group by SC.CId
  • 查询出只选修两门课程的学生学号和姓名
select Student.* from SC INNER JOIN Student on Student.SId = SC.SId group by SC.SId HAVING COUNT(SC.SId) = 2
  • 查询男生、女生人数
select Ssex,COUNT(*) from Student GROUP BY Ssex
  • 查询名字中含有「风」字的学生信息
select *FROM Student where Sname like "%风%"
  • 查询同名同性学生名单，并统计同名人数
select *,count(*) from Student GROUP by Sname HAVING COUNT(*) > 1
  • 查询 1990 年出生的学生名单
select * from Student where Student.Sage = DATE_FORMAT('1990-1-1','%Y%-%m-%d %H:%i:%s')
  • 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select Course.Cname,tmp.* from 
	(select SC.CId,AVG(score) as avg_score from SC GROUP by SC.CId) as tmp
	INNER JOIN Course on  tmp.CId =Course.CId
GROUP by 
	tmp.avg_score desc,
	tmp.CId asc
  • 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select Student.*,AVG(SC.score) from SC INNER JOIN Student on  Student.SId = SC.SId GROUP by SC.SId HAVING AVG(SC.score) >= 85
  • 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select Student.*,SC.score,Course.Cname from Course INNER JOIN SC on SC.CId = Course.CId and SC.score <60 INNER JOIN Student on Student.SId = SC.SId where Course.Cname = '数学'
  • 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select
	tmp.*,SC.score 
from 
	(select * from Student,Course) as tmp
LEFT JOIN
	SC on  SC.CId = tmp.Cid and tmp.Sid = SC.SId
  • 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Student.Sname, Course.Cname,SC.score from SC INNER JOIN Student on Student.SId = SC.SId inner JOIN Course on Course.CId = SC.CId where SC.score >=70
  • 查询不及格的课程
select DISTINCT Course.* FROM Course INNER JOIN SC on SC.CId = Course.CId and SC.score < 60
  • 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select Student.*,SC.score from SC INNER JOIN Student on Student.SId = SC.SId where SC.CId = '01' and SC.score >= 80
  • 求每门课程的学生人数
select SC.CId, count(*) from SC GROUP by SC.CId
  • 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select
	Teacher.Tname,
	Course.Cname,
	Student.Sname,
	SC.score
from
	Teacher
INNER JOIN Course on
	Course.TId = Teacher.TId
INNER JOIN SC on
	SC.CId = Course.CId
INNER JOIN Student on
	Student.SId = SC.SId
WHERE
	Teacher.Tname = "张三"
order by
	SC.score desc
limit 1
  • 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select
	Student.Sname ,
	Course.Cname,
	a.score,
	Teacher.Tname
from
	Teacher
INNER JOIN Course on
	Course.TId = Teacher.TId
INNER JOIN SC as a on
	a.CId = Course.CId
LEFT JOIN SC as b on
	b.CId = a.CId
	and b.score > a.score
INNER JOIN Student on
	Student.SId = a.SId
WHERE
	Teacher.Tname = '张三'
group by
	a.SId,a.CId
HAVING
	COUNT(b.score) = 0
  • 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select * from SC  as a INNER JOIN SC as b on a.SId = b.SId  and a.score = b.score and a.CId != b.CId GROUP by a.CId
  • 查询每门功成绩最好的前两名
select * 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(a.score) < 2

select * from 
 (select DISTINCT a.*,b.score as hscore from SC as a LEFT JOIN SC as b on a.CId = b.CId and a.score < b.score) as tmp
 GROUP by tmp.SId, tmp.CId
 HAVING COUNT(tmp.hscore) < 2
order by tmp.Cid asc , tmp.score desc

  • 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

  • 检索至少选修两门课程的学生学号

  • 查询选修了全部课程的学生信息

  • 查询各学生的年龄，只按年份来算

  • 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

  • 查询本周过生日的学生

  • 查询下周过生日的学生

  • 查询本月过生日的学生

  • 查询下月过生日的学生

js-ye
关注
专栏目录
SQL练习
Arcgo的博客
04-05 315
题目及数据来源 https://blog.csdn.net/qq_45574891/article/details/102559547 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息 #根据返回的子查询中的SId来返回学生信息 select * from student where SId in( select SId from ( #将课程01的成绩和课程02的成绩单独做成...
sql练习
qq_41609957的博客
12-11 469
use master create database Test go -------------- use Test --学生表 if exists (select *from sysobjects where name='Students') drop table Students,Score go create table Students( stuId int primar...
MySQL数据库学习
Json111的博客
02-16 783
建表 学生表 create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))DEFAULT charset = utf8; insert into Student values('01' , '赵雷' , '1990-01-01' , '男') insert into Student...
sql 练习
09-28 415
--建表 --学生表 create table student ( --学号 sno varchar2(3) not null primary key, --姓名 sname varchar2(4) not null, --性别 ssex varchar2(2)
经典SQL练习题
热门推荐
qaz13177_58_的专栏
05-10 27万+
今天在网上找了几道经典的SQL练习题做了一下，虽然都不难，但是对打基础是很有好处的，在明白的基础上可以进一步做分析，来研究一下各种解法的优劣，甚至进行简单的优化。。现在将题目和答案分享一下。我使用的是MYSQL 5.0，但是绝大部分都是标准SQL。表结构：CREATE TABLE STUDENT(SNO VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL,
50道SQL练习题及答案与详细分析
12-14
通过这50道练习题，我们可以深入理解和掌握SQL的基础语法以及高级特性。以下是一些关键知识点的讲解： 1. **创建表**：在SQL中，我们使用`CREATE TABLE`语句来创建新的表格。例如，`CREATE TABLE Student`定义了...
50道SQL练习题
03-01
50道SQL练习题，还附带有详细答案，练完后大概就能掌握SQL基本查询语句了
50道SQL练习题及答案与详细分析（01~05）
12-14
50道SQL练习题及答案与详细分析（题目和数据初始化） 50道SQL练习题及答案与详细分析（01~05） 50道SQL练习题及答案与详细分析（06~10） 1.查询”01″课程比”02″课程成绩高的学生的信息及课程分数. SELECT ...
02 50道SQL练习题及答案与详细分析 - 简书
最新发布
11-21
1
经典50道SQL练习题
wbc199685的博客
09-15 2万+
前言 作为一个SQL小白，在一个月的努力后终于把这50道练习题做完了，分享一下我的答案给大家，说不定能给你提供一些思路。 我使用的是在线SQL，个人认为十分方便，使用的版本是“My SQL Server 2017”。 一、数据表 学生表 Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 create table Student(SId varchar(10),Sname varchar(10),Sage dateti
mysql练习题
05-25
mysql简单练习题，这是 非常简单的练习题mysql简单练习题，这是 非常简单的练习题mysql简单练习题，这是 非常简单的练习题mysql简单练习题，这是 非常简单的练习题
mysql经典练习题
10-07
经常练习有好处，很好用的mysql基础练习题，非常建议拿来练手！
SQL入门必备：50道实战练习题及难度分级
本资源是一份包含50道SQL语句练习题的文档，旨在帮助数据库学习者特别是初学者提升SQL技能。SQL，即结构化查询语言，是用于管理关系型数据库的标准语言。这份文档涵盖了多种难度级别的题目，适合不同程度的学习者...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值