SQL练习题附重点函数说明--更新至21题

已于 2022-10-26 14:46:17 修改
阅读量460 收藏 1
点赞数
文章标签： sql 数据库 mysql
于 2022-10-25 14:01:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/momomuabc/article/details/127506557
版权

首先新建数据库，新建数据表，插入数据

CREATE DATABASE PRACTICE;
CREATE TABLE STUDENT(SNO varchar(10),SNAME nvarchar(10),SAGE datetime,SSEX nvarchar(10));
INSERT INTO STUDENT VALUES
('01' , N'赵雷' , '1990-01-01' , N'男'),
('02' , N'钱电' , '1990-12-21' , N'男'),
('03' , N'孙风' , '1990-05-20' , N'男'),
('04' , N'李云' , '1990-08-06' , N'男'),
('05' , N'周梅' , '1991-12-01' , N'女'),
('05' , N'周梅' , '1991-12-01' , N'女'),
('06' , N'吴兰' , '1992-03-01' , N'女'),
('07' , N'郑竹' , '1989-07-01' , N'女'),
('08' , N'王菊' , '1990-01-20' , N'女');
CREATE TABLE COURSE(CNO varchar(10),CNAME nvarchar(10),TNO varchar(10));
INSERT INTO COURSE VALUES
('01' , N'语文' , '02'),
('02' , N'数学' , '01'),
('03' , N'英语' , '03');
CREATE TABLE TEACHER(TNO varchar(10),TNAME nvarchar(10));
INSERT INTO TEACHER VALUES
('01' , N'张三'),
('02' , N'李四'),
('03' , N'王五');
CREATE TABLE SC(SNO varchar(10),CNO varchar(10),SCORE decimal(18,1));
INSERT INTO SC VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);
  1. 查询”01”课程比”02”课程成绩高的学生的信息及课程分数;
    答案一
select A.*,B.SCORE '01'，C.SCORE '02'
from student A inner join (sc B,sc C) on A.SNO=B.SNO and B.CNO='01'and A.SNO=C.SNO and C.CNO='02'
where C.SCORE> B.SCORE;
select A.*,B.SCORE '01',C.SCORE '02'
from (student A inner join sc B on A.SNO=B.SNO and B.CNO='01')
inner join sc C on A.SNO=C.SNO and C.CNO='02'
where C.SCORE> B.SCORE;

答案二
inner join可以分开写，但是前面这个需要加（），并且on也要分开写

select A.*,B.SCORE '01',C.SCORE '02'
from (student A inner join sc B on A.SNO=B.SNO and B.CNO='01')
inner join sc C on A.SNO=C.SNO and C.CNO='02'
where C.SCORE> B.SCORE;
  1. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
    答案一
SELECT A.SNO,B.SNAME,AVG(SCORE) AVG_SCORE
FROM SC A INNER JOIN STUDENT B
ON A.SNO=B.SNO
GROUP BY SNO 
HAVING AVG(SCORE)>=60;

无法运行，报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘practice.B.SNAME’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

答案二
嵌套式查询，效率会变低

select t.*,B.SNAME 
from (select SNO,avg(SCORE) '平均分'
from sc 
group by SNO
having avg(SCORE)>=60)t inner join student B on t.SNO=B.SNO;
  1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select A.SNO ,B.SNAME,count(CNO) '课程门数',SUM(SCORE) '总得分'
from sc A inner join student B on A.SNO=B.SNO 
group by A.SNO;
  1. 查询“李”姓老师的数量
select count(TNO)
from teacher
where left(TNAME,1)='李';
  1. 查询学过“张三”老师授课的同学的信息
select distinct A.*
from student A inner join (sc B,course C,teacher D)
on A.SNO=B.SNO and B.CNO=C.CNO and C.TNO=D.TNO 
where D.TNAME='张三';
  1. 查询没学过“张三”老师授课的同学的信息
    注意点： 没XX的内容如果不好判断，可以先查询符合条件的，然后使用 not in
select * from student where SNO not in
(select distinct A.SNO
from student A inner join (sc B,course C,teacher D)
on A.SNO=B.SNO and B.CNO=C.CNO and C.TNO=D.TNO 
where D.TNAME = '张三');
  1. 查询学过变化为“01"并且也学过编号为”02“的课程的同学的信息；
select distinct t.* from student t
where t.SNO in
(select distinct A.SNO from sc A inner join sc B on A.SNO=B.SNO
where A.CNO='01' and B.CNO='02'
)
  1. 查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息;
select distinct t.* from student t
where t.SNO in (select SNO from sc  where CNO='01' )
and t.SNO not in (select SNO from sc  where CNO='02' )
  1. 查询没有学全所有课程的同学的信息;
select distinct t.* from student t
where t.SNO not in(select SNO from sc  group by SNO having count(distinct CNO)=3 )
  1. 查询至少有一门课与学号为”01”的同学所学相同的同学的信息;
select distinct t.* from student t
where SNO in (select distinct SNO from sc
where  CNO in (select CNO from sc A where SNO='01' ) and SNO <>'01')
  1. 查询和”01”号的同学学习的课程完全相同的其他同学的信息 ;
SELECT A.SNO FROM STUDENT A  INNER JOIN SC B ON A.SNO=B.SNO
WHERE A.SNO<>01 AND B.CNO IN (SELECT CNO FROM SC WHERE SNO=01)
GROUP BY A.SNO HAVING COUNT(B.CNO)=(SELECT count(*) FROM SC WHERE SNO=01)
  1. 查询没学过”张三”老师讲授的任一门课程的学生姓名；
select SNAME from student
where SNO not in (select A.SNO from course B inner join sc A on A.CNO=B.CNO 
inner join teacher C on C.TNO=B.TNO where C.TNAME='张三')
  1. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；
select A.SNO,B.SNAME,avg(SCORE)
from sc A inner join student B on A.SNO=B.SNO and A.SCORE <60
group by SNO
having count(CNO)>=2
  1. 检索”01”课程分数小于60，按分数降序排列的学生信息；
select A.* from student A inner join sc B on A.SNO=B.SNO 
where  B.CNO='01' and B.SCORE <60 
order by B.SCORE desc
  1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩;

答案一
使用group_concat()函数，将groupby以外的字段拼接起来

SELECT SNO,GROUP_CONCAT(CNO),GROUP_CONCAT(SCORE),AVG(SCORE) FROM SC
GROUP BY SNO ORDER BY AVG(SCORE) desc

答案二
此种方式只能显示groupby的第一条数据

select *,avg(SCORE)
from sc
group by SNO order by avg(SCORE) desc
  1. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率;
    注意点： 在select后面可以直接写公式，要注意和下面的衔接，groupby要放在最下面，而不是公式内
select A.CNO '课程ID',CNAME '课程名称',max(SCORE) '最高分',min(SCORE) '最低分',avg(SCORE) '平均分',(select COUNT(*) FROM SC WHERE CNO=A.CNO AND SCORE>=60)/(SELECT COUNT(*) FROM SC WHERE CNO=A.CNO)*100 '及格率%'
from sc A inner join course B on A.CNO=B.CNO
group by A.CNO
  1. 按各科成绩进行排序，并显示排名

答案一：使用 row_number() OVER(partition by 分组字段 order by 排序字段 DESC) 函数

select CNO,SNO,SCORE,row_number() OVER(partition by CNO order by SCORE DESC) '排名' from sc

答案二：

SELECT SNO,CNO,SCORE,(SELECT COUNT(1)+1 FROM SC WHERE SCORE> A.SCORE AND CNO=A.CNO) '排名'
FROM SC A
ORDER BY CNO,SCORE DESC;
  1. 查询学生的总成绩并进行排名

答案一：

select SNO,sum(SCORE),row_number() over( order by sum(SCORE) desc)'排名'
from sc group by SNO

答案二：

SELECT SNO,SUM(SCORE)
FROM SC
GROUP BY SNO 
ORDER BY SUM(SCORE) DESC
  1. 查询不同老师所教不同课程平均分从高到低显示
select TNAME,a.CNO,avg(SCORE)
from sc a inner join (course b,teacher c) on a.CNO=B.CNO and b.TNO=C.TNO
group by TNAME
order by avg(SCORE) desc
  1. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
    注意点： 有单独筛选时可选择嵌套查询
select distinct t.*, b.*
from (select CNO,SNO,SCORE,row_number() OVER(partition by CNO order by SCORE desc) as num
from sc )t inner join student b on t.SNO=b.SNO where t.num in(2,3)
  1. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

注意点： 使用case when统计不同情况下的数据
答案一：
1.使用case when为数据打上标签，然后利用group by分组统计不同标签下的数据。
2.因为有两层分组统计（课程和分值区间），因此要统计在第一层分组下的第二层占比时，可以使用join表的方式。也就是说另建表2，只计算第一层分组的总值，和具有两层分组的表1进行连接后计算占比。

select B.CNAME, A.CNO,A.num'分值',A.a'人数',A.a/B.total'占比'
from(
select CNO,num,count(*) as a
from(
select *,
case
    when score<=100 and score>=85 then '[100-85]'
	 when score< 85 and score>=70 then '[85-70]'
	  when score<70 and score>=60 then '[70-60]'
	  else'[0-60]' end as num
	  from SC
	  order by score desc)t1
	  group by CNO,num)A left join
	  (select sc.CNO,course.CNAME,count(*) as total from sc inner join course on sc.CNO=course.CNO group by CNO)B on A.CNO=B.CNO

答案二
1.使用case when 筛选符合条件的数据，同时使用SUM函数统计数量。
2.占比则根据数量/count（*）计算，最外层利用group by分组统计CNO的数据。

select  sc.CNO,course.CNAME,
sum(case when score<=100 and score>=85 then 1 else 0 end)'100-85人数',
sum(case when score<=100 and score>=85 then 1 else 0 end)/count(*)'100-85占比',
sum(case when SCORE<85 AND SCORE>=70 then 1 else 0 end)'100-85人数',
sum(case when SCORE<85 AND SCORE>=70 then 1 else 0 end)/count(*)'100-85占比',
sum(case when SCORE<70 AND SCORE>=60 then 1 else 0 end)'100-85人数',
sum(case when SCORE<70 AND SCORE>=60 then 1 else 0 end)/count(*)'100-85占比',
sum(case when SCORE<60 then 1 else 0 end)'100-85人数',
sum(case when SCORE<60 then 1 else 0 end)/count(*)'100-85占比'
from sc left join course on sc.CNO =course.CNO
group by sc.CNO
小成小成小成
关注
常用sql 函数练习示例
软践的专栏
04-04 710
select ascii(U)select char(85)select str(44.4545545)select lower(ASDFSDAdfsdfsd)select upper(ASDFSDAdfsdfsd)select ltrim( sdfsdf  )select rtrim( sdfsdf       )select left(fdsa
SQL练习题 SQL练习题 SQL练习题
最新发布
09-06
以下是一些SQL练习题，涵盖了SELECT、INSERT、UPDATE、DELETE、JOIN、SUBQUERY、AGGREGATE、GROUP BY等语句和函数： 选择数据 从 employees 表中选择所有列： SELECT * FROM employees; 从 employees 表中选择 ...
Sql经典练习题库（附答案）
02-17
SQL是高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同底层结构的不同数据库系统，可以使用相同的SQL语言作为数据输入与管理的SQL接口。 它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使它具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。 　　 结构化查询语言(Structured Query Language）最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。 　 　美国国家标准局(ANSI）与国际标准化组织（ISO）已经制定了SQL标准。ANSI是一个美国工业和商业集团组织，负责开发美国的商务和通讯标准。ANSI同时也是ISO和International Electrotechnical Commission(IEC）的成员之一。ANSI 发布与国际标准组织相应的美国标准。1992年，ISO和IEC发布了SQL国际标准，称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。ANSI SQL-92有时被称为ANSI SQL。尽管不同的关系数据库使用的SQL版本有一些差异，但大多数都遵循 ANSI SQL 标准。SQL Server使用ANSI SQL-92的扩展集，称为T-SQL，其遵循ANSI制定的 SQL-92标准。 　　 SQL语言包含4个部分： 　　 数据定义语言(DDL），例如：CREATE、DROP、ALTER等语句。 　　 数据操作语言(DML），例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 　　 数据查询语言（DQL），例如：SELECT语句。 　　 数据控制语言（DCL），例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。 　　 SQL语言包括三种主要程序设计语言类别的语句：数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。
sql 函数练习
lcawen的专栏
12-04 1079
通过表c 想实现如下表的效果，，如题： 利用sqlserver内部自定义函数，并调用函数实现，实现如下： if object_id('f_hb') is not null drop function dbo.f_hb go create function dbo.f_hb(@id char(10)) returns varchar(8000) as  begin  declar
【SQL刷题】Day3----SQL必会的常用函数专项练习
你在看屏幕的同时，屏幕也在注视着你
08-10 4539
Day3----SQL必会的常用函数专项练习
数据库技术复习题1---选择题.zip_300_sql_sql 习题_sql选择练习题_数据库选择题
09-23
通过这些选择题的练习，学习者不仅能加深对SQL语法的理解，还能锻炼在实际场景中解决问题的能力。同时，填空题等形式可以进一步检验对SQL命令的精确理解和记忆。因此，无论是在准备认证考试还是日常工作中，这个练习...
习题参考答案 -《关系数据库与SQL Server (2019版)》.pdf
06-28
它允许用户通过SQL语句来定义数据结构（如CREATE TABLE）、操作数据（如SELECT, INSERT, UPDATE, DELETE）、实现数据的完整性约束（如FOREIGN KEY, PRIMARY KEY, UNIQUE）以及进行权限管理（如GRANT, REVOKE）。...
Oracle SQL：经典练习题（附答案）
01-09
1. **基本查询操作**：练习题中包含了查询员工表（emp）和部门表（dept）的基础信息，例如查询所有员工的详细信息。这涉及到SELECT语句的基本用法，包括选择列（如`SELECT * FROM emp`）和指定表（如`FROM emp`）。 ...
SQL：SQL-92，SQL-99和SQL-2003的BNF语法
01-30
为了进一步学习和实践这些SQL标准，你可以参考"SQL-master"这个压缩包中的文件，它可能包含了一系列关于SQL-92、SQL-99和SQL-2003的教程、示例代码或练习题，帮助你深入理解每个版本的新特性和BNF语法。通过不断实践...
sql语句练习(函数)
Keith_Walker的博客
12-15 1346
--1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk’); --3、找出佣金高于薪金的雇员 selec
SQL语句练习-使用函数
朱铭雷的日记本
04-05 1244
SQL支持利用函数来处理数据，这些函数也都是非常高效的。不过，对于不同的DBMS，它们可能支持不同的函数，这会给SQL代码的可移植性带来问题。
SQL面试练习题二(常用函数)
我的学习成长日记
04-09 1964
使用的表和数据与《PL/SQL练习题一》是一样的。 对日期函数的使用： Count(*) of orders last week SELECT COUNT(*) FROM order_list WHERE order_date < TRUNC(SYSDATE,'DAY') AND order_date >= TRUNC(SYSDATE,'DAY')-7 Count(*)...
SQL函数例题
m0_54487641的博客
01-18 2638
创建一个自定义函数F2：完成通过系部名称查询系部学生人数的功能 create function F2(@dname varchar(20)) returns int as begin declare @num int select @num=count(StudentID)from Department join Class on Class.DepartmentID=Department.DepartmentID join Student on Student.ClassID=Class
SQL函数
zhangmary的博客
04-15 521
聚集函数通常和GROUP BY联合起来使用()必选，[]可选COUNT(*) 统计元组个数，返回被选行数COUNT([DISTINCT|ALL]&lt;列名&gt;) 统计一列中值的个数SUM([DISTINCT|ALL]&lt;列名&gt;) 统计一列值的总和  此列必须为数值型AVG([DISTINCT|ALL]&lt;列名&gt;)计算一列值的平均值  此列必须为数值型MAX([DISTIN...
sql过程、函数、触发器语句练习
vgfvgf的博客
04-04 206
sql语句练习
sql刷题-计算用户的平均次日留存率
qq_40420214的博客
12-19 1371
计算用户的平均次日留存率 关键字和函数 关键词 distinct 用于返回唯一不同的值。 DATE_ADD() 函数向日期添加指定的时间间隔。 问题描述 题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。 根据示例，你的查询应返回以下结果： 示例 drop table if exists `user_profile`; drop table if exists `question_practice_detail`; drop table if exists `q
教学思路SQL之入门习题《学生成绩》 六.使用SQL函数
weixin_34247155的博客
11-02 86
本节课我们来学习使用sql自带的系统函数，完成查询作业。sql系统函数中较常用的，包括字符串函数、日期函数、聚合函数以及其他函数框架下的数据类型转换、自增长函数、空值函数等。后面所指出的几种函数，我们都在前几节的学习中使用过，今天我们着重来学习前两种函数。 字符串函数： 1.charindex（）： 返回字符串中指...
SQL常用函数复习
q951674899的博客
01-05 781
一、组合查询       UNION      定义：组合多个SELECT语句的结果，不包含重复的记录。在使用UNION时，注意每个SELECT语句里必须选择相同数量的字段、相同数量的字段表达式、同样的数据类型、同样的次序----长度可以不同。       UNION ALL      定义：同UNION区别就是可以返回重复的记录，而UNION不返回。       INTERSECT
全面总结数据库SQL操作及知识点，附练习题代码
资源摘要信息:"数据库SQL知识点总结及代码和练习题代码" 1.数据库的创建、修改、批量插入等基本操作： - 数据库的创建：主要用到的是create database语句，可以创建一个新的数据库。例如：create database mydb; ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值