编程题(4_1)
CREATE table "EMP" (
"EMPNO" V ARCHAR2(20),
"ENAME" V ARCHAR2(20),
"HIREDATE" DATE,
"SAL" BINARY_FLOAT,
"COMM" BINARY_FLOAT,
"DEPTNO" V ARCHAR2(20),
"MRG" V ARCHAR2(20),
"JOB" V ARCHAR2(20),
constraint "EMP_PK" primary key ("EMPNO") )
/
CREATE table "SALGRADE" (
"GRADENO" V ARCHAR2(20),
"GRADE" V ARCHAR2(20),
"LOSAL" BINARY_FLOAT,
"HISAL" BINARY_FLOAT,
constraint "SALGRADE_PK" primary key ("GRADENO") )
/
CREATE table "DEPT" (
"DEPTNO" V ARCHAR2(20),
"DNAME" V ARCHAR2(20),
"LOC" V ARCHAR2(20),
constraint "DEPT_PK" primary key ("DEPTNO")
)
/
insert into dept values('1','市场部','北京');
insert into dept values('2','研发部','北京');
insert into dept values('3','管理部','上海');
insert into dept values('4','客户部','南京');
insert into salgrade values('1','一等',10000,20000);
insert into salgrade values('1','二等',5000,10000);
insert into salgrade values('1','三等',2000,5000);
insert into emp values('1','莉莉',to_date(‘2010-2-9’,'yyyy-mm-dd'),20000,10000,1,null,'经理');
insert into emp values('2','张立志',to_date(‘2011-2-9’,'yyyy-mm-dd'),3000,3000,1,1,'职员');
insert into emp values('3','吴淑玲',to_date(‘1981-2-9’,'yyyy-mm-dd'),5300,5000,1,1,'助理');
insert into emp values('5','张聪',to_date(‘1982-2-9’,'yyyy-mm-dd'),3400,2000,2,null,'经理');
insert into emp values('4','晨晨',to_date(‘1970-2-9’,'yyyy-mm-dd'),3400,null,2,5,'职员');
1、构造SQL语句，列出在1981年入职的职员的姓名、入职时间和月收入（薪
水和奖金之和）别名为salary，并将入职时间显示为:：“1981-10-10”这种形式，月收入显示为”$12,345.67”这种形式。
select sysdate from dual
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as 日期from dual
select floor( sysdate - to_date('2016-3-17','yyyy/mm/dd') ) from dual
select to_char(112345,'$999,999.99') from dual
Select ename,to_char(hiredate,'yyyy-mm-dd') ,to_char(sal+nvl(comm,0),'$99,999.99') as salary
From emp
Where to_char(hiredate,'yyyy') = '1981'
select ename,nvl(comm,0) from emp
select nvl(null,0) from dual
2、列出职员表中员工的姓名、薪水和薪水等级，并按薪水由高到低排序。等级
数据来自于表salgrade。
select ename,sal ,grade
from emp,salgrade
where sal between losal and hisal
order by sal desc
select ename,sal,grade
from emp join salgrade
on sal between losal and hisal
order by sal desc
order by (desc 降序asc 升序)
（1）Order by 子句可以指定多个列
（2）Order规定空值（null）大于所有的非空值
Desc空值在最前面
Asc空值在最后面
（3）desc必须把空值放在后面：
order by ** desc nulls last
asc必须把空值放在前面：
order by ** asc nulls first
between and 包含等于
select * from salgrade
select ename,sal ,grade
from emp,salgrade
where sal< hisal and sal>losal
3、列出职员表中的员工姓名和他们所在的部门名称及所在城市，按部门编码升
序排列。暂未分配部门的员工及没有员工的部门不予考虑。
select ename,dname,loc
from emp,dept
where emp.deptno = dept.deptno
order by emp.deptno asc
select ename,dname,loc,e.deptno
from emp e,dept d
where e.deptno = d.deptno
全连接：full join
select ename,dname,loc
from emp e full join dept d
on e.deptno = d.deptno
要求：暂未分配部门的员工不予考虑（没有员工的部门要求显示）
右连：right join
select ename,dname,loc
from emp e right join dept d
on e.deptno = d.deptno
把右表中所有的行都显示，左表中符合条件加到右表中，不符合条件就不用加入结果集
select ename,dname,loc
from emp e,dept d
where e.deptno(+) = d.deptno
（+）放在没有匹配记录，值就不显示的表的一端
技巧：右连等于（+）=
要求：没有员工的部门不予考虑（暂未分配部门的员工要求显示）select ename,dname,loc
from emp e left join dept d
on e.deptno = d.deptno
左连：left join
把左表中所有的行都显示，右表中符合条件加到结果中，不符合条件就不用加入结果集
select ename,dname,loc
from emp e,dept d
where e.deptno = d.deptno(+)
（+）放在没有匹配记录，值就不显示的表的一端
技巧：左连等于= （+）
4、列出每个员工的名字(别名为EMPLOYEE)及他们的直接领导的名字（别名为
MANAGER），如果某些员工没有领导，则在其领导的位置上显示“boss”。
nvl(mrg,’boss’)
e:员工表m:领导表
select e.ename as EMPLOYEE , nvl(m.ename , 'boss' ) as MANAGER
from emp e left join emp m
on e.mrg = m.empno
5、查询员工分布在哪些不同的部门，列出这些部门的部门编码。去掉重复值显
示。
select distinct deptno from emp
查询员工分布在哪些不同的部门，列出这些部门的部门编码和部门名称。去掉重复值显示。
select distinct e.deptno,dname
from emp e,dept d
where e.deptno = d.deptno
6、
select count(*) from emp
select deptno , count(*) from emp
group by deptno
select deptno,job ,count(*) from emp
group by deptno,job
聚合函数：
Count 计算个数
Sum 求和
Avg 平均
Min 最小
Max 最大
7、查询哪些职位的薪水总和大于5000元，列出这些职位和其薪水总和，并按
薪水总和升序排列。
每个职位的薪水总和
select job,sum(sal) from emp
group by job having sum(sal)>6000 order by sum(sal) asc
查询时执行顺序
1、where
2、group by
3、having
4、order by
8、查询哪个部门的平均薪水比部门2的平均薪水低，列出这些部门的编码和
平均薪水值。
（1）查询出每个部门的平均薪水，列出这些部门的编码和平均薪水值。
select deptno,avg(sal) from emp group by deptno
（2）查询出部门2的平均薪水的值
select avg(sal) from emp where deptno='2'
select deptno,avg(sal) from emp group by deptno
having avg(sal) <
( select avg(sal) from emp where deptno='2' )
9、查询机构中薪水最低的3名员工，列出员工名字和薪水值。
select rownum , a.* from (
select * from emp order by sal
)a
where rownum<=3
10、按薪水从高到低排序，列出第2位到第4位员工的姓名、薪水和职位。
Rownum是一个总是从1开始的伪列。如果想找到从第二条记录以后的数据，是查不出来的。
select * from (
select rownum rn,a.* from(
select ename,sal,job from emp order by sal desc) a
)
where rn>=2 and rn<=4
11、查询职员表中工资大于1600的员工姓名和工资
12、查询职员表中员工号为7369的员工的姓名和部门号码
13、选择职员表中工资不在4000到5000的员工的姓名和工资
14、选择职员表中在20和30号部门工作的员工姓名和部门号
15、选择职员表中没有管理者的员工姓名及职位, 按职位排序
16、选择职员表中有奖金的员工姓名，工资和奖金，按工资倒序排列
17、选择职员表中员工姓名的第三个字母是A的员工姓名
18、列出部门表中的部门名字和所在城市；
19、显示出职员表中的岗位job,去掉重复的部分。
20、连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成OUT_PUT(提示:使用连接符||、别名)
21、查询职员表emp中员工号、姓名、工资，以及工资提高百分之20%后的结果
22、查询员工的姓名和工资数,条件限定为工资数必须大于1200，并对查询结果按入职时间进行排列，早入职排在前面，晚入职排在后面。
23、列出除了ACCOUNT部门还有哪些部门。
24、将员工的姓名按首字母排序，并列出姓名的长度（length）
25、做查询显示下面形式的结果
例如：
Dream Salary
King earns $5000 monthly but wants $15000
1.使用decode函数，按照下面的条件：
JOB GRADE
PRESIDENT A
MANAGER B
ANALYST C
SALESMAN D
CLERK E
产生类似下面形式的结果
ENAME JOB GRADE
SMITH CLERK E
26、查询各员工的姓名ename，并显示出各员工在公司工作的月份数(即:与当前日期比较,该员工已经工作了几个月, 用整数表示)。
27、现有数据表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语句，列出客户帐户的余额总数。
28、按照”2009-4-11 20:35:10 ”格式显示系统时间。
29、构造SQL语句查询员工表emp中员工编码empno，姓名ename，以及月
收入(薪水+ 奖金)，注意有的员工暂时没有奖金。
30、查找员工姓名的长度是5个字符的员工信息。
31、查询员工的姓名和工资，按下面的形式显示：（提示：使用lpad函数）
NAME SALARY
-----------------------------------------------------
SMITH $$$$$$$$$$24000
32、查询薪水大于2000元的员工的姓名和薪水，薪水值显示为’RMB5000.00’这种形式，并对查询结果按薪水的降序方式进行排列；
33、构造查询语句，产生类似于下面形式的结果：
NAME HIREDATE REVIEW
----------------------------------------------------------------------------------------- SMITH 1980-12-17 1980年12月17日
34、显示所有员工的姓名ename，部门号deptno和部门名称dname。
35、选择在DALLAS工作的员工的员工姓名、职位、部门编码、部门名字
36、选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno，结果类似于下面的格式
38、查询公司员工工资的最大值，最小值，平均值，总和
39、列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四舍五入
40、查询出JONES的领导是谁（JONES向谁报告）。
41、查询各职位的员工工资的最大值，最小值，平均值，总和
42、选择具有各个job的员工人数(提示:对job进行分组)
43、查询员工最高工资和最低工资的差距,列名为DIFFERENCE；
44、查询各个管理者属下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内
45、查询所有部门的部门名字dname，所在位置loc，员工数量和工资平均值；
46、查询和scott相同部门的员工姓名ename和雇用日期hiredate
47、查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal。
48、查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名
49、查询在部门的loc为newYork的部门工作的员工的员工姓名ename，部门名称dname和岗位名称job
50、查询管理者是king的员工姓名ename和工资sal
51、显示Operations部门有哪些职位
52、各个部门中工资大于1500的员工人数
53、哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
54、所在部门平均工资高于1500的员工名字
55、列出各个部门中工资最高的员工的信息：名字、部门号、工资
56、哪个部门的平均工资是最高的，列出部门号、平均工资
57、创建表employee，字段为：
Id number
First_Name varchar2（20），
last_Name varchar2（20），
mgrid NUMBER,
Job varchar2（20），
Salary number(7,2)
58、向表中插入下列数据，并提交，查询数据；
59．将3号员工的last_name修改为“Tate”，并提交，查询数据；
60．将所有工资少于2000的员工的工资修改为2000 (不提交)，并设置保存点，查询数据；
61．删除employee表中所有数据(不提交)，查询数据；
62．回滚到60题中的设置的保存点，查询数据；
63．删除表employee中所有数据，并提交，查询数据；
64、现有数据表Customer，其结构如下所示：
cust_id NUMBER(4) Primary Key, --客户编码
cname VARCHAR2(25) Not Null, --客户姓名
csex CHAR(6) --客户性别
birthday DATE, --客户生日
account NUMBER. --客户账户余额
1)、创建表，客户编码为主键，姓名不能为空，性别只能选择”男”或”女”；
2)．构造SQL语句，向Customer数据表中插入一条记录，其信息如下：客户编码使用第一步创建的序列获得，客户姓名为sean，性别为”男”，生日为1987-11-17，帐户余额为12345元。
65、创建一个序列，初始值是1000，步进是10；
66、创建一个视图v_emp，内容是按部门分组，各个部门的薪水总和和员工人数；视图字段定义为deptno, total_sal, total_count;
67、查询视图，列出全部记录；
68、列出薪水最低的三名员工的名字；
69、列出按员工编码排序，第6到第10条员工记录。
70、有员工表empinfo
(
Fempno varchar2(10) not null pk,
Fempname varchar2(20) not null,
Fage number not null,
Fsalary number not null
);
假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人：
fsalary>9999 and fage > 35
fsalary>9999 and fage < 35
fsalary <9999 and fage > 35
fsalary <9999 and fage < 35
每种员工的数量；
71、aa，bb表都有20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引，请用SQL列出aa表里面存在的X在bb表不存在的X的值，请写出认为最快的语句，并解译原因。