Oracle sql语句练习
--1、选择在部门 30 中员工的所有信息 select * from emp where EMPNO=30; --2、列出职位为（MANAGER）的员工的编号，姓名 select EMPNO, ENAME from emp where job='MANAGER'; --3、找出奖金高于工资的员工 select * from emp where COMM>SAL; --4、找出每个员工奖金和工资的总和 select EMPNO,ENAME,COMM+SAL from emp; --5、找出部门 10 中的经理(MANAGER)和部门 20 中的普通员工(CLERK) select * from emp where (DEPTNO=10 and job='MANAGER') or (DEPTNO=20 and job='CLERK'); --6、找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工 select * from emp where job not in ('MANAGER','CLERK') and SAL>=2000; --7、找出有奖金的员工的不同工作 select distinct job from emp; --8、找出没有奖金或者奖金低于 500 的员工 select * from emp where COMM is null or COMM >500; --9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 select ename,HIREDATE from emp order by HIREDATE desc; --10、找出每个月倒数第三天受雇的员工（如：2009-5-29） select * from emp where EXTRACT(day FROM to_date(sysdate,'yyyy-mm-dd')) - EXTRACT(day FROM to_date(HIREDATE,'yyyy-mm-dd')) =3; --11、找出 25 年前雇的员工 select * from emp where EXTRACT(year FROM to_date(sysdate,'yyyy-mm-dd')) - EXTRACT(year FROM to_date(HIREDATE,'yyyy-mm-dd')) >25; --12、所有员工名字前加上 Dear ,并且名字首字母大写 select 'Dear'||initcap(ename) from emp; --13、找出姓名为 5 个字母的员工 select * from emp where ENAME like '_____'; --14、找出姓名中不带 R 这个字母的员工 select * from emp where ENAME not like '%R%'; --15、显示所有员工的姓名的第一个字 select substr(ename,0,1) as name from emp; --16、分组统计各部门下工资>500 的员工的平均工资、 select avg(SAL) from emp where SAL>500 ; --17、统计各部门下平均工资大于 500 的部门 select DEPTNO ,avg(SAL) from emp group by DEPTNO having avg(SAL)>500; --18、算出部门 30 中得到最多奖金的员工奖金 select max(COMM) from EMP where DEPTNO=30; --19、算出部门 30 中得到最多奖金的员工姓名 select ENAME from EMP where comm in (select max(COMM) from EMP where DEPTNO=30); --20、算出每个职位的员工数和最低工资 select job,count(*) as num,min(SAL) from emp group by job;
雇员表：记录了一个雇员的基本信息
EMP（雇员表）
NO
字段
类型
描述
1
EMPNO
NUMBER(4)
雇员编号
2
ENAME
VARCHAR2(10) 表示雇员姓名
3
JOB
VARCHAR2(9) 表示工作职位
4
MGR
NUMBER(4) 表示一个雇员的领导编号
5
HIREDATE
DATE
表示雇佣日期
6
SAL
NUMBER(7,2) 表示月薪，工资
7
COMM
NUMBER(7,2) 表示奖金或佣金
8
DEPTNO
NUMBER(2) 表示部门编号
部门表：表示一个部门的具体信息
DEPT（部门表）
NO
字段
类型
描述
1
DEPTNO
NUMBER(2)
部门编号
2
DNAME
VARCHAR2(14) 部门名称
3
LOC
VARCHAR2(13) 部门位置
奖金表：表示一个雇员的工资及奖金。
BONUS（奖金表）
NO
字段
类型
描述
1
ENAME VARCHAR2(10)
雇员姓名
2
JOB
VARCHAR2(9)
雇员工作
3
SAL
NUMBER
雇员工资
4
COMM
NUMBER
雇员奖金
一个公司是有等级制度，用此表表示一个工资的等级
SALGRADE（工资等级表）
NO
字段
类型
描述
1
GRADE
NUMBER
等级名称
2
LOSAL
NUMBER
此等级的最低工资
3
HISAL
NUMBER
此等级的最高工资
题目：
1、 选择在部门 30 中员工的所有信息
2、列出职位为（MANAGER）的员工的编号，姓名
3、找出奖金高于工资的员工
4、找出每个员工奖金和工资的总和
5、找出部门 10 中的经理(MANAGER)和部门 20 中的普通员工(CLERK)
6、找出部门 10 中既不是经理也不是普通员工，而且工资大于等于
2000 的员工
7、找出有奖金的员工的不同工作
8、找出没有奖金或者奖金低于 500 的员工
9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
10、找出每个月倒数第三天受雇的员工（如：2009-5-29）
11、找出 25 年前雇的员工
12、所有员工名字前加上 Dear ,并且名字首字母大写
13、找出姓名为 5 个字母的员工
14、找出姓名中不带 R 这个字母的员工
15、显示所有员工的姓名的第一个字
16、分组统计各部门下工资>500 的员工的平均工资、
17、统计各部门下平均工资大于 500 的部门
18、算出部门 30 中得到最多奖金的员工奖金
19、算出部门 30 中得到最多奖金的员工姓名
20、算出每个职位的员工数和最低工资
创建二张表
学生表和分数表
学生表字段：学号、姓名、年龄、奖学金、月实习工资、国家
分数表字段：学号、分数
1.要求二个表之间有主外键关系
2.查询出学生表中所有学生记录
3.查询出学生姓名是“张三”的学生的姓名、学号、奖学金，分数
4.查询出学生姓名是“李四”的学生一年能得多少钱
5.去重名查询出所有学生信息
6.查询出学生表中月实习工资在300到500之间的学生信息
7.查询出学生表中月实习工资是300，400，500，600的学生信息
8.查询出姓名为第三个字符为大写M的所有员工的姓名和工资和分数
9.查询出没有得到奖学金的同学的所有信息
10.查询出姓名是“张”开头的，且年龄>20的，有奖学金的学生，并且按升序排序
11.按国家查询，查询出总奖学金，要求只显示总奖学金>1000的信息，并且按降序排序
/*创建二张表 学生表和分数表 学生表字段：学号、姓名、年龄、奖学金、月实习工资、国家 分数表字段：学号、分数*/ create table stu--学生表 ( xh varchar2(8) not null, xm varchar2(64) not null, age integer, jj float ,--奖学金 sal float ,--月实习工资 city varchar2(10)--国家 ); create table soccer--分数表 ( xh varchar2(8) , fs number(2,3) ); --1.要求二个表之间有主外键关系 alter table stu add constraint pk_stu_xh primary key (xh); alter table soccer add constraint fk_soccer_stu_xh foreign key (xh) references stu(xh); --2.查询出学生表中所有学生记录 select xh,xm,age,jj,sal,city from stu; --3.查询出学生姓名是"张三"的学生的姓名、学号、奖学金，分数 select xm,stu.xh,jj,fs from stu,soccer where stu.xh=soccer.xh and stu.xm='张三'; select xm,stu.xh,jj,fs from soccer inner join (select * from stu where xm='张三') stu on stu.xh=soccer.xh; select a.*,fs from (select xm,xh,jj from stu where xm='张三') a left join soccer on a.xh=soccer.xh; --4.查询出学生姓名是“李四”的学生一年能得多少钱 select (sal*12 + nvl(jj,0)) as sum_money from stu where xm='李四' 5.去重名查询出所有学生信息 select distinct xm ,xh,age,jj,sal,city from stu; --6.查询出学生表中月实习工资在300到500之间的学生信息 select xh,xm,age,jj,sal,city from stu where sal between 300 and 500 ; select xh,xm,age,jj,sal,city from stu where sal >= 300 and sal <= 500; --7.查询出学生表中月实习工资是300，400，500，600的学生信息 select xh,xm,age,jj,sal,city from stu where sal in (300,400,500,600); select xh,xm,age,jj,sal,city from stu where sal = 300 or sal = 400 or sal = 500 or sal = 600; --8.查询出姓名为第三个字符为大写M的所有员工的姓名和工资和分数 select xm,sal,fs from stu,soccer where xm like '--M%' and stu.xh=soccer.xh; --9.查询出没有得到奖学金的同学的所有信息 select * from stu where jj is null; --10.查询出姓名是“张”开头的，且年龄>20的，有奖学金的学生，并且按升序排序 select * from stu where xm like '张%' and age>20 and jj is not null order by jj asc; --11.按国家查询，查询出总奖学金，要求只显示总奖学金>1000的信息，并且按降序排序 select city,sum(nvl(jj,0)) from stu group by city having sum(nvl(jj,0))>1000 order by sum(nvl(jj,0)) desc
