oracle数据库题

day 01

1.查询员工表中工资大于1600的员工的姓名的姓名和工资?
select ename,sal from emp where sal>1600;

2.查询员工工号为8369的员工的姓名和部门号码
select ename, deptno from emp where empno = '8369';

3.选择员工表中工资不在4000到5000的员工的姓名和工资
select ename,sal from emp where sal not between 4000 and 5000;

4.选择员工表中在20号和30号部门工作的员工姓名和部门号
select ename,deptno from emp where deptno in (20,30);

5.选择职员表中没有管理者的员工姓名和职位 按照职位排序
select ename,job from emp where mgr is null order by job;

6.选择职员表中有奖金的员工姓名 工资 和奖金 按工资倒叙排练
select ename, sal, comm from emp where comm is not null order by sal desc;

7.选择职员表中员工姓名的第三个字母是A的员工姓名
select ename from emp where ename like '%__A%';

8 列出部门表中的部门名字和所在城市
select dname,loc from dept;

9. 显示出职员表中的不重复的岗位job
select distinct job from emp;

10 连接职员表中的职员名字 职位 薪水 列之间用逗号连接 (提示：使用连接符 ||）
select ename || ',' || job || ','|| sal from emp;

11 查询职员表中emp中的员工号 姓名 工资 以及工资提高百分之20后的结果
select deptno,ename,sal,sal*1.2 salary from emp;

12查询员工的姓名和工资数 条件限定为工资数必须大于1200 并对查询结果按入职时间进行排列 早入职的排在前面
select ename,sal from emp where sal>1200 order by hiredate;

13列出除了Account部门还有哪些部门。

select deptno,dname,loc from dept where dname <> 'Account';



day 02

1. 查询出 JONES 的领导是谁（JONES 向谁报告）。
select ename from emp where empno in (select mgr from emp where ename='JONES');

2. JONES 领导谁。（谁向 JONES 报告）
select ename from emp where mgr in (select empno from emp where ename='JONES');

3. 查询各职位的员工工资的最大值，最小值，平均值，总和
select job,max(sal),min(sal),avg(sal),sum(sal) from emp group by job;

4. 选择具有各个 job 的员工人数(提示:对 job 进行分组
select job,count(*) from emp group by job;

5.查询员工最高工资和最低工资的差距,列名为 DIFFERENCE
select max(sal)-min(sal) DEFERENCE FROM emp;

6. 查询各个管理者属下员工的最低工资，其中最低工资不能低于 800，没有管理者的员工
不计算在内
select mgr,min(sal) from emp where mgr is not null group by mgr having min(sal) >=800;

7. 查询所有部门的部门名字 dname，所在位置 loc，员工数量和工资平均值；

select e.cs,e.aas, d.dname,d.loc from dept d left outer join
(select deptno, count(empno) cs,avg(nvl(sal,0)) aas from emp group by deptno) e
on d.deptno = e.deptno

8.查询和 scott 相同部门的员工姓名 ename

和雇用日期 hiredate

select ename,hiredate from emp where deptno in (select deptno from emp where ename='SCOTT');

9. 查询工资比公司平均工资高的所有员工的员工号 empno，姓名 ename 和工资 sal

select ename,empno,sal from emp where sal > (select avg(sal) from emp);

10. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号 empno 和姓名 ename
select ename,empno from emp where deptno in (select deptno from emp where lower(ename) like '%u%');

11.查询在部门的 loc 为 newYork 的部门工作的员工的员工姓名 ename，部门名称 dname 和
岗位名称 job
select e.ename,d.dname,e.job from emp e join (select dname,deptno from dept where loc = 'NEW YORK') d on e.deptno = d.deptno;

12. 查询管理者是 king 的员工姓名 ename 和工资 sal

select ename,sal from emp where mgr in (select empno from emp where ename='KING');

13. 显示 SALES 部门有哪些职位
select DISTINCT job from emp where deptno = (select deptno from dept where dname='SALES');

14. 各个部门中工资大于 1500 的员工人数

select deptno,count(*) from emp where sal > 1500 group by deptno;

15.哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）

select ename,sal from emp where sal > (select avg(sal) from emp ) order by sal desc;

16. 所在部门平均工资高于 1500 的员工名字
select ename from emp where deptno in (select deptno from emp group by deptno having avg(nvl(sal,0)) >1500);

17. 列出各个部门中工资最高的员工的信息：名字、部门号、工资

select ename,deptno,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

18. 哪个部门的平均工资是最高的，列出部门号、平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno );


--------------------------------------------------------------与上边雷同-----------------------------------------------------


1. 查询职员表中工资大于1600的员工姓名和工资
Select ename, sal from emp where sal > 1600;

2. 查询职员表中员工号为7369的员工的姓名和部门号码
Select ename, deptno from emp where empno = 7369;

3. 选择职员表中工资不在4000到5000的员工的姓名和工资
Select ename, sal from emp where sal not between 4000 and 5000;

4. 选择职员表中在20和30号部门工作的员工姓名和部门号
Select ename, deptno from emp where deptno in (20, 30);

5. 选择职员表中没有管理者的员工姓名及职位, 按职位排序
Select ename, job from emp where mgr is null order by job;

6. 选择职员表中有奖金的员工姓名，工资和奖金，按工资倒序排列
Select ename, sal, comm From emp where comm is not null order by sal desc;

7. 选择职员表中员工姓名的第三个字母是A的员工姓名
Select en

ame from emp where ename like ‘__A%’;

8. 列出部门表中的部门名字和所在城市；
select dname, loc from dept;

9. 显示出职员表中的不重复的岗位
job select distinct job from emp;

10. 连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成 OUT_PUT(提示:使用连接符||、别名)
select ename || ', ' || job || ', ' || sal from emp;

11. 查询职员表emp中员工号、姓名、工资，以及工资提高百分之20%后的结 果

select empno, ename, sal, sal * 1.2 salary from emp;

12. 查询员工的姓名和工资数,条件限定为工资数必须大于1200，并对查询结果 按入职时间进行排列，早入职排在前面，晚入职排在后面。
select ename, sal from emp where sal > 1200 order by hiredate;

13. 列出除了ACCOUNT部门还有哪些部门。
select deptno, dname, loc from dept where dname <> 'ACCOUNT';

-----------------------day2-----------------------------------




1. 将员工的姓名按首字母排序，并列出姓名的长度（length）
select ename, length(ename) from emp order by ename;

2. 做查询显示下面形式的结果
earns monthly but wants 例如： Dream Salary King earns $5000 monthly but wants $15000
select ename || ' earns $' || sal ||' monthly but wants $' || sal * 3 “Dream Salary” from emp;
3. 使用decode函数，按照下面的条件：
JOB GRADE PRESIDENT A MANAGER B ANALYST C SALESMAN D CLERK E 产生类似下面形式的结果 ENAME JOB GRADE SMITH CLERK E
SELECT ename, job, DECODE(job,'PRESIDENT','A', 'MANAGER','B', 'ANALYST','C', 'SALESMAN','D', 'CLERK','E' ) AS "Grade" FROM EMP;
4. 查询各员工的姓名ename，并显示出各员工在公司工作的月份数(即:与当前 日期比较,该员工已经工作了几个月, 用整数表示)。
select ename, round(months_between(sysdate, hiredate)) hire_months from emp;

5. 现有数据表Customer，其结构如下所示： cust_id NUMBER(4) Primary Key, --客户编码 cname VARCHAR2(25) Not Null, --客户姓名 birthday DATE, --客户生日 account NUMBER. --客户账户余额 (1).构造SQL语句，列出Customer数据表中每个客户的信息。如果客户生日未提供，则该列值显示“not available” 。如果没有余额信息，则显示“no account”。 (2).构造SQL语句，列出生日在1987年的客户的全部信息。 (3).构造SQL语句，列出客户帐户的余额总数。
1）select cust_id, cname, nvl(to_char(birthday, 'yyyy-mm-dd'), 'not available'), nvl(to_char(account, '9999'), 'no account') from Customer;
2）select * from Customer where extract(year from birthday) = '1987';
3) select sum(account) from Customer;
6. 按照”2009-4-11 20:35:10 ”格式显示系

统时间。
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now from dual;

7. 构造SQL语句查询员工表emp中员工编码empno，姓名ename，以及月收入 (薪水 + 奖金)，注意有的员工暂时没有奖金。
select empno, ename, sal + nvl(comm, 0) month_salary from emp;

8. 查找员工姓名的长度是5个字符的员工信息。
select * from emp where length(ename) = 5;

9. 查询员工的姓名和工资，按下面的形式显示：（提示：使用lpad函数） NAME SALARY ----------------------------------------------------- SMITH $$$$$$$$$$24000
select ename name, lpad(sal, 15, '$') salary from emp;

10. 查询薪水大于2000元的员工的姓名和薪水，薪水值显示为’RMB5000.00’这 种形式，并对查询结果按薪水的降序方式进行排列；
select ename, to_char(sal, 'L9999.00') salary from emp where sal > 2000 order by sal desc;

11. 构造查询语句，产生类似于下面形式的结果： NAME HIREDATE REVIEW ----------------------------------------------------------------------------------------- SMITH 1980-12-17 1980年12月17日
select ename name, to_char(hiredate, 'yyyy-mm-dd') hiredate, to_char(hiredate, 'yyyy"年"mm"月"dd"日"') review from emp;

12. 显示所有员工的姓名ename，部门号deptno和部门名称dname。
Select e.ename, d.deptno, d.dname From emp e join dept d on e.deptno = d.deptno;

13. 选择在DALLAS工作的员工的员工姓名、职位、部门编码、部门名字
Select e.ename, d.deptno, d.dname From emp e join dept d on e.deptno = d.deptno and d.loc = ‘DALLAS’;

14. 选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名
ename和员工号deptno，结果类似于下面的格式 employees Emp# manager Mgr# SMITH 7369 FORD 7902
select wor.ename "employees", wor.empno "Emp#", mag.ename "manager", mag.empno "Mgr#" from emp wor, emp mag where wor.mgr = mag.empno;

15. 查询各部门员工姓名和他们所在位置，结果类似于下面的格式 Deptno Ename Loc 20 SMITH DALLAS
select deptno,e.ename, d.loc from emp e join dept d on e.deptno = d.deptno;

16. 查询公司员工工资的最大值，最小值，平均值，总和
select max(sal), min(sal), avg(sal), sum(sal) from emp;

17. 列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四 舍五入
Select ename , sal , round(sal*1.08) from emp;

18. 查询出JONES的领导是谁（JONES向谁报告）。
select e1.ename from emp e1 , emp e2 where e2.mgr = e1.empno and e2.ename = 'JONES';

19. JONES领导谁。（谁向JONES报告）。
select e1.ename from emp e1 , emp e2 where e1.mgr = e2.empno and e2.ename = 'JONES';


-----------------------day3-----------------------------------
1. 查询各职位的员工工资的最大值，最小值，

平均值，总和
select job, max(sal), min(sal), avg(sal), sum(sal) from emp group by job;

2. 选择具有各个job的员工人数(提示:对job进行分组)
select job, count(*) from emp group by job;

3. 查询员工最高工资和最低工资的差距,列名为DIFFERENCE；
select max(sal)-min(sal) "DIFFERENCE" from emp;

4. 查询各个管理者属下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内
select mgr, min(sal) from emp where mgr is not null group by mgr having min(sal) >= 800;

5. 查询所有部门的部门名字dname，所在位置loc，员工数量和工资平均值；
select dept.dname, dept.loc, COUNT, AVG from dept join( select deptno, count(*) as "COUNT", avg(sal) as "AVG" from emp group by deptno ) using(deptno);

6. 查询和scott相同部门的员工姓名ename和雇用日期hiredate
select ename, hiredate from emp where deptno = (select deptno from emp where emp.ename = 'SCOTT');

7. 查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工字sal；
select empno, ename, sal from emp where sal > (select avg(sal) from emp);

8. 查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名 ename
select empno, ename from emp where deptno in (select deptno from emp where ename like '%U%');

9. 查询在部门的loc为newYork的部门工作的员工的员工姓名ename，部门名 称dname和岗位名称job
select e.ename, d.dname, e.job from emp e join dept d on e.deptno = d.deptno where deptno = (select deptno from dept where loc = 'NEW YORK');

10. 查询管理者是king的员工姓名ename和工资sal
select ename, sal from emp where mgr = (select empno from emp where ename = 'KING');

11. 显示SALES部门有哪些职位
select distinct e.job from emp e , dept d where e.deptno = d.deptno and d.dname = 'SALES';

12. 各个部门中工资大于1500的员工人数
select deptno , count(*) from emp where sal > 1500 group by deptno;

13. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
select ename , sal from emp where sal > (select avg(sal) from emp) order by sal desc;

14. 所在部门平均工资高于1600的员工名字
select ename,deptno from emp where deptno in (select deptno from emp group by deptno having avg(sal) >1600);