Oracle经典作业与答案.doc

-----------------------day1-----------------------------------

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 ename 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 || ', ' || OUT_PUT(将列名大写) 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
using (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
using (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. 显示Operations部门有哪些职位
select distinct e.job from emp e , dept d where e.deptno = d.deptno and d.dname = 'Operations';
12. 各个部门中工资大于1500的员工人数
select dept_id , count(*) cnt from s_emp where salary > 1500 group by dept_id;
13. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
select first_name , salary from s_emp where salary > (select avg(salary) from s_emp) order by salary desc;
14. 所在部门平均工资高于1500的员工名字
select first_name , salary from s_emp where dept_id in (select dept_id from s_emp group by dpet_id having avg(salary) > 1500);
15. 列出各个部门中工资最高的员工的信息：名字、部门号、工资
select first_name , salary , dept_id from s_emp where (dept_id , salary) in (select dept_id , max(salary) from s_emp group by dept_id);
16. 哪个部门的平均工资是最高的，列出部门号、平均工资
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary) = (select max(avg(salary)) from s_emp);

-----------------------day4-----------------------------------
1. 创建表employee，字段为：
Id number
First_Name varchar2（20），
last_Name varchar2（20），
mgrid NUMBER,
Job varchar2（20），
Salary number(7,2)

CREATE TABLE employee(
id NUMBER,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
mgrid NUMBER,
salary NUMBER(7,2));
2. 向表中插入下列数据，并提交，查询数据；
ID
FIRSTNAME
LAST_NAME
MGRID
SALARY
1
Rose
Tyler
4
1500
2
Matha
Jones
4
2200
3
Donna
Noble
4
1300
4
Doctor
Who

3500
5
Jack
Harkness
1
3000
SQL> INSERT INTO employee
VALUES (1, 'Rose', 'Tyler', 4, 1500);

SQL> INSERT INTO employee
VALUES (2, 'Martha', 'Jones', 4, 2200);

SQL> INSERT INTO employee
VALUES (3, 'Donna', 'Noble', 4, 1300);

SQL> INSERT INTO employee
VALUES (4, 'Doctor', 'Who', NULL,3500);

SQL> INSERT INTO employee
VALUES (5, 'Jack', 'Harkness', 1, 3000);

SQL> COMMIT;

4．将3号员工的last_name修改为“Tate”，并提交，查询数据；

SQL> UPDATE employee SET lastname = 'Tate'
WHERE id = 3;
5．将所有工资少于2000的员工的工资修改为2000 (不提交)，并设置保存点，查询数据；
SQL> UPDATE employee SET salary = 2000 WHERE salary < 2000;
SQL> SAVEPOINT A;
6．删除employee表中所有数据(不提交)，查询数据；
SQL> DELETE FROM employee;
SQL> SELECT * FROM employee;
7．回滚到第五题中的设置的保存点，查询数据；
SQL> Rollback to A;
SQL> SELECT * FROM employee;
8．删除表employee中所有数据，并提交，查询数据；
SQL> DELETE FROM employee;
SQL> COMMIT;
SQL> SELECT * FROM employee;
9. 现有数据表Customer，其结构如下所示：
cust_id NUMBER(4) Primary Key, --客户编码
cname VARCHAR2(25) Not Null, --客户姓名
csex CHAR(6) --客户性别
birthday DATE, --客户生日
account NUMBER. --客户账户余额
1)、创建表，客户编码为主键，姓名不能为空，性别只能选择”男”或”女”；
CREATE TABLE customer(
cust_id NUMBER PRIMARY KEY,
cname VARCHAR2(25) NOT NULL,
csex CHAR(6),
birthday DATE,
account NUMBER,
CONSTRAINT cust_csex_chk CHECK (csex IN ‘男’, ‘女’),));
2)．构造SQL语句，向Customer数据表中插入一条记录，其信息如下：客户编码使用第一步创建的序列获得，客户姓名为sean，性别为”男”，生日为1987-11-17，帐户余额为12345元。
INSERT INTO customer(cust_id, cname, birthday, account) VALUES(cust_seq.NEXTVAL, 'sean',
TO_DATE('1987-11-17','yyyy-mm-dd'), 12345);

-----------------------day5--------------------
1. 创建一个序列，初始值是1000，步进是10；
Create sequence start with 1000 increment by 10;
2. 创建一个视图v_emp，内容是按部门分组，各个部门的薪水总和和员工人数；视图字段定义为deptno, total_sal, total_count;
Create or replace view
Is
Select deptno, sum(sal) total_sal, count(*) total_count
From emp group by deptno;
3. 查询视图，列出全部记录
Select * from v_emp;
4. 删除视图；
Drop view v_emp;
5. 列出薪水最低的三名员工的名字；
SELECT ename, sal, ROWNUM
FROM (SELECT ename, sal
FROM emp
ORDER BY sal)
WHERE ROWNUM <= 3;
6. 列出按员工编码排序，第6到第10条员工记录。
SELECT ename, sal, rn
FROM (SELECT ROWNUM rn, ename, sal
FROM emp
ORDER BY empno )
WHERE rn BETWEEN 6 AND 10;