Oracle SQL练习题，从小白到入门 - 上
从事DBA以来，越来越认识到自己SQL水平不足，想想sql语句还停留在大二寒假学习的黑马的Mysql《Mysql 十天精通》基础篇+进阶篇，将近100集一天就学完了，黑马yyds。
再后来，做项目用Spring的MyBatis是真的香，练习的环境也是单表的增删改查居多，什么是JDBC一边去。加上力扣算法还刷不过来，根本没时间或者不屑于练习sql，环境能跑出来就行了（DBA兄弟别打我啊），自此sql编写算是荒废了。
工作这几个月一直想练习SQL，但是一直找不到很好的场景和案例，不知道大家都是怎么练习SQL，有好的方法可以分享在评论区，我开始实在力扣上刷，但是效果不是很显著，每次都要去翻笔记，我就想有没有更加体系化一点的，由浅入深的，今天分享给大家。
下面练习题是基于Oracle scott用户下的表，层层深入，很全面，做完下面练习相信你的SQL水平可以更上一层楼
1、SQL练习
练习 1 基本select语句
1.做一个查询显示dept表的所有数据
2.做一个查询显示emp表中ename,job,sal几列,其中sal列在结果集中做salary显示
3.做一个查询显示emp表中deptno,ename,年工资,并将年工资命名别名annual salary
4.得到emp表的结构
5.做一个查询显示emp表中雇员编号,雇员的名字,工作,雇用日期,并将查询保存为脚本,脚本名字p1q7.sql
6.运行p1q7.sql
7.创建一个查询显示雇员表中部门编号,重复的部门编号只显示一次
8.在结果集中雇员名和工作相连用"空格逗号空格"分隔,并将列头显示为Employee and Title
练习 2 限制和排列数据
1.显示收入大于2850的雇员名称和工资
2.查看雇员编号7566的雇员名字和所在部门号
3.查看BLAKE的工资
4.显示收入在2000和5000之间的人
5.显示收入为1000,2000,3000,5000的人
6.显示奖金不为空的人
7.显示管理者编号为空的人
8.找到名字当中第二个字母为"L"的员工
9.找到工资大于1000的职员
10.找到工资大于1000的或者工作为职员的人
11.找到工作为管理员或者是分析员,并且工资大于2500的人
12.查看雇员名称,工资,将工资按照降序排列
13.查看雇员名称,部门编号,工资,将部门编号按照升序排列,工资按照降序排列
14.做一个用别名排序的例子(随意)
练习 3 单行函数
1.将字符串"HELLO World"全变小写输出
2.将字符串"HELLO World"全变大写输出
3.将字符串"HELLO World"单词字首字母变大写其余变小写输出
4.将单词"Hello"和"World"拼接为"HelloWorld"
5.从字符串"HelloWorld"中截取出单词"World"
6.计算字符串"HelloWorld"的长度
7.字符串"HelloWorld"中字母"W"在第几个位置
8.将1234在左面用"*"号填满10位
9.将1234在右面用"*"号填满10位
10.截掉字符串"HelloWorldh"中首尾的"H"和"h"
11.求出45.926四舍五入到个位,十位,十分位的结果
12.求出45.926截尾取整到个位,十位,十分位的结果
13.求出2100除以500的余数
14.查看当前系统时间
15.计算1977年9月1号和当前系统时间相差多少个月
16.计算当前系统时间加半年后的日期是哪一天
17.计算当前系统时间的下一个星期五是哪一天
sunday
monday
tuesday
wednesday
thursday
friday
saturday
18.计算当前系统时间当月最后一天是哪一天
19.当前系统时间四舍五入精确到月份是哪一天
20.当前系统时间四舍五入精确到年是哪一天
21.当前系统时间解尾取整到月份是哪一天
22.当前系统时间解尾取整到年是哪一天
23.将当前系统时间变成类似格式"2006-10月-星期三"的字符串
24.将当前系统时间变成类似格式"10月-25"的字符串
25.将当前系统时间变成类似格式"2006-10-25 08: 20: 17 下午"的字符串
26.将emp 表sal列转换成以本地货币符号为前缀的字符串
练习 4 多表连接
1.查看每个员工都工作在哪个城市
2.查看每个员工都工作在哪个城市并在结果集中显示40部门信息
3.查看每个员工的工资等级
4.查看每个员工的直属管理者
练习 5 组函数
1.求emp表中工资总和
2.求emp表中工资平均值
3.求emp表中最小工资
4.求emp表中最大工资
5.求emp表的总行数,再求出部门编号去重后的总行数
6.求有奖金的员工的行数
7.求所有员工的平均奖金
8.求每个部门的工资总和
9.求每个部门的平均工资
10.求每个部门的最小工资
11.求每个部门的最大工资
12.求每个部门中从事不同工作的员工的工资总和
13.求每个部门中从事不同工作的员工的平均工资
14.求每个部门中从事不同工作的员工的最小工资
15.求每个部门中从事不同工作的员工的最大工资
16.查出平均工资大于2000的部门
17.计算不同年份参加工作的员工数量
练习 6 子查询
1.部门编号与7934相同的人有哪些?
2.参加工作比7788晚的人有哪些?
3.每个部门拿最少工资的人?
4.姓名中第二个字母为L的人的工资是多少?
5.81年2月参加工作的人工资是多少?
6.工资前10名的人? rownum<=10
7.大于30部门最大工资的人?
8.工资高于本部门平均工资的人(拿上游工资的人)?
9.工资相同的人有哪些？
teach用户下做:
10.管理者编号与部门编号与144或149的管理者编号与部门编号相同的人有那些?
练习 7 替换变量
1.在select子句中用替换变量取两列名称
2.在select子句中用替换变量取两列名称,在from子句中用替换变量取表名
3.在where子句中用替换变量取ename的名称
4.在select子句中用替换变量取列名并在order by子句中用替换变量取相同的列名排序
5.修改sqlplus环境变量,使ename列显示长度为10字符,sal列显示长度为5位数字
6.修改sqlplus环境变量,使ename列在结果集中显示为"雇员名称"
7.修改sqlplus环境变量,使sal列在结果集中显示为本地货币符号前缀五位整数两位小数
8.为结果集创建标题和脚注
练习 8 DML语句
1.向emp表插入一新行,empno=7935,ename='张建中',sal=1000,deptno=20
2.向emp表插入一新行奖金列为空其他列都插入有效值
3.向emp表插入一新行奖金列为空其他列都插入有效值,其中雇用日期为"2006-10-01"
4.create table e as select * from emp where 1=0;
将emp表中7839的所有列拷贝到e表中
5.将emp表中7369的雇员编号,雇员名称,雇用日期,拷贝到e表中
6.将e表中7369的管理者编号修改为7902
7.将e表中7369的工资修改为800,部门编号修改为20
8.将e表中7369的工资修改为与"ALLEN"相同,并将此人调动到7782所在部门
9.将"KING"从e表中删除
10.将与"MILLER"同部门的人从e表删除
11.描述事务的概念
12.提交对数据的修改(如何实现自动提交)
13.回退对数据的修改
14.创建一个事务,并在事务中的每条DML后手工设置savepoint点
15.将事务回退到指定的savepoint点
16.何时产生隐式savepoint点
17.何时回产生隐式提交
18.何时会产生隐式回退
19.什么叫做读一致性
20.锁的作用
练习 9 创建和管理表
1.创建一张表d,结构如下:
SQL> desc d
名称 是否为空? 类型
------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
2.查询数据字典得到当前用户所拥有的表的信息
3.创建一张表e拷贝emp表的结构
4.修改表e,在表e中添加一个新列loc varchar2(10)
5.修改表e的loc列数据长度为varchar2(20)
6.删除loc列
7.将e表comm列置为未使用状态并删除该列
8.删除表e
9.将表d重命名为dept_temp
10.说明truncate操作和delete操作的区别
11.为emp表添加注释信息"雇员信息表"
12.查看表的注释信息
练习 10 约束
1.创建一张表tt包含数字型列id和可变长度字符串型列ename并使id列为主键,名称为pk_tt_id
2.修改表tt,为ename列添加唯一键约束uu_ename
3.为tt表添加一新列deptno number(2),并在deptno列添加外键约束fk_tt使其参照dept表的主键
4.为tt表添加一新列sal数字型7精度2刻度,并在sal列添加check约束ck_tt_sal使sal列的取值大于1000
5.删除约束uu_ename
6.禁止约束ck_tt_sal
7.启用约束ck_tt_sal
8.查看约束信息
练习 11 创建视图
1创建一个视图v_emp使其能够访问emp表的empno,ename,sal,deptno列的数据
2.修改视图v_emp使其能够访问emp表30部门的empno,ename,sal,deptno列的数据
3.向视图v_emp插入一新行数据其中部门号为20
4.修改视图v_emp使其不能向非30部门插入数据
5.修改视图v_emp使其不能做任何DML操作
7.查看视图v_emp的源代码
8.删除视图v_emp
练习 12 序列.索引.同义词
1.创建一个序列s1,使步长增长幅度为10,起始值为-120,最大值为10000,不循环,不缓存
2.查看s1的信息
3.查看s1的值
4.使用s1向表中插如值
5.修改s1使步长增长幅度为20,最大值为100000,缓存尺寸为20
6.删除序列
7.在emp表ename列创建一个索引ind_emp_ename
8.删除ind_emp_ename
9.在system用户下为scott.emp创建一个私有同义词emp并使用
10.删除私有同义词emp
11.在system用户下为scott.emp创建一个公有同义词emp并使用
12.删除公有同义词emp
13.私有同义词和公有同义词有和区别?
练习 13 管理用户和权限
1.在管理员下创建一个用户test,初始口令为test
2.在管理员下对test用户授予create session和create table权限
3.连接到test用户
4.在管理员下撤消test用户的create session和create table权限
5.在管理员下创建一个角色r1
6.在管理员下对角色r1授予create session和create table权限
7.在管理员下将角色r1授予test用户
8.连接到test用户
9.修改test用户口令
10.在test用户下创建test表(id int),插入一行数据,并使scott能够查看test用户下的test表数据
11.使所有人都能够查看test用户下的test表
2、环境推荐
- Oracle 10G+都可
- 墨天轮的 SQLRUN 工具：SQL 工作台 - 墨天轮
- SQL Fiddle
SQL Fiddle 提供了 MySQL、PostgreSQL、Oracle、SQLite 以及 SQL Server 数据库的运行环境，如图所示。
运行网址：
SQL Fiddle - Online SQL Compiler for learning & practice
4、DB Fiddle
DB Fiddle 提供了 MySQL、PostgreSQL 以及 SQLite 数据库的运行环境，部分截图如下。
运行网址：
DB Fiddle - SQL Database Playground (db-fiddle.com)
DB Fiddle 还有一个亮点是支持在线协作，点击Collaborate生成一个邀请链接，其他人点击即可加入协作，同时支持语音和文字聊天，这样非常方便 SQL 学习探讨。
3、项目推荐
最好的学习方式就是在游戏过程中学习，可以把这个过程游戏化，这里推荐鱼总的两个SQL项目可以参考。
1）SQL 之母 - 闯关式 SQL 自学网”
一个Vue3 + Ant-Design-Vue 的纯前端的项目，开源透明，一分钟本地环境启动，支持挑战模式和自定义关卡，在游戏中学习。不过项目重点是实现这个功能，而不于练习SQL，有兴趣大家可以制作一个专属DBA的SQL闯关游戏，我第一个卡会员😜。
项目源码：
GitHub - liyupi/sql-mother: 免费的闯关式 SQL 自学教程网站，从 0 到 1 带大家掌握常用 SQL 语法，纯前端实现，简单易学~
在线体验：http://sqlmother.yupi.icu
2）SQL Father - 模拟数据生成器
这是我学习后端时练手的一个项目，一个JAVA的前后端分离项目，不仅仅是基础的增删改查，项目中还会涉及到单例模式、工厂模式、门面模式等多种设计模式，另外自己在本地环境测试项目时如果没有假数据利用此项目可以一键生成SQL代码，也支持JSON、JAVA、前端等格式。
项目源码：liyupi/sql-father-frontend-public: 新项目：快速生成 SQL 和模拟数据的网站（React 前端），大幅提高开发测试效率！by 程序员鱼皮 (github.com)
在线体验：http://sqlfather.yupi.icu