oracle实战练习weixin44049484的博客-

甲骨文公司，全称甲骨文股份有限公司(甲骨文软件系统有限公司)，是全球最大的企业级软件公司，总部位于美国加利福尼亚州的红木滩。1989年正式进入中国市场。2013年，甲骨文已超越 IBM ，成为继 Microsoft 后全球第二大软件公司。
Oracle数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置)。

oracle的连接方式：5种方式

sqlplus

sql develop

oracle提供的企业web页面端

以上三种是oracle官方提供的：

第三方工具：plsql develop

navicat 也支持了 ，企业实际常用第三方提供的plsql develop.所以博主这里也是基于plsql develop 做一个简单的演示：

Oracle用户权限介绍：

oracle默认提供三个用户sys 、system、scott,但tiger是锁定的，需要通过sys 受权开启。

给scott授权：grant create session to scott;

当然你也可以自己创建用户：

语法[创建用户]： create user 用户名 identified by 口令[即密码]；

如：create user ceshi identified by 123456;

授权登录：grant create session to ceshi;

实战练习：首先创建表：

员工表建表语句:
create table EMP
(
ename VARCHAR2(30),
empno NUMBER(5),
deptno NUMBER(5),
job VARCHAR2(20),
hiredate DATE,
comm NUMBER(6,2),
sal NUMBER(6,2)
)
– Add comments to the columns
comment on column EMP.ename is ‘姓名’;
comment on column EMP.empno is ‘人员编号’;
comment on column EMP.deptno is ‘部门编号’;
comment on column EMP.job is ‘工作’;
comment on column EMP.hiredate is ‘入职时间’;
comment on column EMP.comm is ‘奖金’;
comment on column EMP.sal is ‘工资’;

部门表建表语句:
create table DEPT
(
dname VARCHAR2(30),
deptno NUMBER(5),
loc VARCHAR2(50)
)
– Add comments to the columns
comment on column DEPT.dname is ‘部门名称’;
comment on column DEPT.deptno is ‘部门编号’;

员工表插入数据:

INSERT INTO EMP VALUES (‘Zhou润发’, 10001, 10, ‘办事员’, TO_DATE(‘20161130131322’, ‘YYYYMMDDHH24MISS’), 2500, 2400);
INSERT INTO EMP VALUES (‘Liu德华’, 10002, 10, ‘办事员’, TO_DATE(‘20170105131334’, ‘YYYYMMDDHH24MISS’), 1800, 2250);
INSERT INTO EMP VALUES (‘Li连杰’, 20001, 20, ‘办事员’, TO_DATE(‘20170313131339’, ‘YYYYMMDDHH24MISS’), 2200, 2350);
INSERT INTO EMP VALUES (‘xiang华强’, 20000, 20, ‘经理’, TO_DATE(‘20160130131343’, ‘YYYYMMDDHH24MISS’), 3980, 3500);
INSERT INTO EMP VALUES (‘Zhang柏芝’, 30001, 30, ‘办事员’, TO_DATE(‘20170314131346’, ‘YYYYMMDDHH24MISS’), 1300, 2200);
INSERT INTO EMP VALUES (‘成long’, 10000, 10, ‘经理’, TO_DATE(‘20151031133724’, ‘YYYYMMDDHH24MISS’), 4800, 4000);
INSERT INTO EMP VALUES (‘zhang子怡’, 30000, 30, ‘经理’, TO_DATE(‘20161011133915’, ‘YYYYMMDDHH24MISS’), 3500, 3480);
INSERT INTO EMP VALUES (‘zhang家辉’, 20002, 20, ‘办事员’, TO_DATE(‘20170330134519’, ‘YYYYMMDDHH24MISS’), 1600, 2000);
INSERT INTO EMP VALUES (‘Liu亦菲’, 30002, 30, ‘驾驶员’, TO_DATE(‘20170330135457’, ‘YYYYMMDDHH24MISS’), NULL, 2500);
INSERT INTO EMP VALUES (‘peng于晏’, 20003, 20, ‘驾驶员’, TO_DATE(‘20170411135625’, ‘YYYYMMDDHH24MISS’), NULL, 2500);
INSERT INTO EMP VALUES (‘Zhang涵予’, 10003, 10, ‘驾驶员’, TO_DATE(‘20170408135706’, ‘YYYYMMDDHH24MISS’), NULL, 2500);
INSERT INTO EMP VALUES (‘yang幂’, 10004, 20, ‘秘书’, TO_DATE(‘20170109185706’, ‘YYYYMMDDHH24MISS’), 6000, 5800);
INSERT INTO EMP VALUES (‘jing甜’, 10005, 40, ‘秘书’, TO_DATE(‘20180909165706’, ‘YYYYMMDDHH24MISS’), 6800, 5600);
INSERT INTO EMP VALUES (‘liu诗诗’, 10007, 40, ‘秘书’, TO_DATE(‘20160409155706’, ‘YYYYMMDDHH24MISS’), 5000, 5000);
INSERT INTO EMP VALUES (‘tang嫣’, 10009, 20, ‘秘书’, TO_DATE(‘20170523102706’, ‘YYYYMMDDHH24MISS’), 5200, 4000);
COMMIT;

部门表插入数据:
INSERT INTO DEPT VALUES (‘市场部’, 10, ‘北京’);
INSERT INTO DEPT VALUES (‘公关部’, 20, ‘上海’);
INSERT INTO DEPT VALUES (‘研发部’, 30, ‘南京’);
INSERT INTO DEPT VALUES (‘人事部’, 40, ‘深圳’);
COMMIT;

两张表已全部建好,自己用查询语句查看一下表结构和数据是否没问题.

SELECT * FROM DEPT;

SELECT * FROM EMP;

题目：
–1.选择30部门的职员
–2.列出所有办事员的姓名、编号和部门
–3.找出奖金高于工资的雇员
–4.找出佣金高于工资60%的雇员
–5.找出部门10中所有经理和部门20中的所有办事员的详细资料
–6.找出既不是经理又不是办事员但其工资>=2000的所有雇员的详细资料
–7.找出收取佣金的雇员的不同工作
–8.找出不收取佣金或收取的佣金低于2000的雇员
–9.找出各月最后一天受雇的所有雇员
–10.找出早于1年之前受雇的雇员
–11.显示只有首字母大写的所有雇员的姓名
–12.显示正好为7个字符的雇员姓名
–13.显示不带有’Z’的雇员姓名
–14.显示所有雇员的姓名的前三个字符
–15.显示所有雇员的姓名，用小写 z 替换所有大写 Z
–16.显示所有雇员的姓名以及满1年服务年限的日期
–17.显示雇员的详细资料，按姓名排序
–18.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
–19.显示所有雇员的姓名、工作和，按工作的降序顺序排序，而工作相同时按工资升序
–20.显示所有雇员的姓名和加入公司的年份和月份，
– 按雇员受雇日所在月排序，将最早年份的项目排在最前面
–21.显示在一个月为30天的情况下所有雇员的日工资
–22.找出在（任何年份的）1月受聘的所有雇员
–23.对于每个雇员，显示其加入公司的天数
–24.显示姓名字段的任何位置，包含 “i” 的所有雇员的姓名
–25.以年、月和日显示所有雇员的服务年限

答案:

–1.选择30部门的职员
SELECT * FROM EMP WHERE deptno=30
–2.列出所有办事员的姓名、编号和部门
SELECT E.ENAME 姓名, E.EMPNO 编号, D.DNAME 部门
FROM EMP E
LEFT JOIN DEPT D
ON D.DEPTNO = E.DEPTNO
WHERE E.JOB = ‘办事员’
–3.找出奖金高于工资的雇员
SELECT * FROM EMP WHERE SAL < COMM ORDER BY COMM DESC
–4.找出佣金高于工资60%的雇员
SELECT * FROM EMP WHERE COMM > SAL * 0.6
–5.找出部门10中所有经理和部门20中的所有办事员的详细资料
SELECT *
FROM EMP
WHERE (DEPTNO = 10 AND JOB = ‘经理’)
OR (DEPTNO = 20 AND JOB = ‘办事员’)
–6.找出既不是经理又不是办事员但其工资>=2000的所有雇员的详细资料
SELECT *
FROM EMP
WHERE JOB NOT IN (‘经理’, ‘办事员’)
AND SAL >= 2000
–7.找出收取佣金的雇员的不同工作
SELECT DISTINCT JOB FROM EMP WHERE COMM > 0
–8.找出不收取佣金或收取的佣金低于2000的雇员
SELECT ENAME, NVL(COMM, 0)
FROM EMP
WHERE COMM = 0
OR COMM < 2000
–9.找出各月最后一天受雇的所有雇员
SELECT * FROM EMP WHERE HIREDATE = LAST_DAY(HIREDATE)
–10.找出早于1年之前受雇的雇员
SELECT * FROM EMP WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) > 12;
–11.显示只有首字母大写的所有雇员的姓名
SELECT * FROM EMP WHERE ENAME = INITCAP(ENAME);
–12.显示正好为7个字符的雇员姓名
SELECT * FROM EMP WHERE LENGTH(ENAME) = 7;
–13.显示不带有’Z’的雇员姓名
SELECT * FROM EMP WHERE ENAME NOT LIKE ‘%Z%’
–14.显示所有雇员的姓名的前三个字符
SELECT SUBSTR(ENAME, 0, 3) FROM EMP
–15.显示所有雇员的姓名，用小写 z 替换所有大写 Z .
SELECT REPLACE(ENAME, ‘z’, ‘Z’) FROM EMP
–16.显示所有雇员的姓名以及满1年服务年限的日期
SELECT ENAME, ADD_MONTHS(HIREDATE, 12) FROM EMP
–17.显示雇员的详细资料，按姓名排序
SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY E.ENAME
–18.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
SELECT ENAME, HIREDATE FROM EMP ORDER BY HIREDATE
–19.显示所有雇员的姓名、工作和，按工作的降序顺序排序，而工作相同时按工资升序
SELECT ENAME, JOB, SAL FROM EMP ORDER BY JOB DESC, SAL ASC
–20.显示所有雇员的姓名和加入公司的年份和月份，
– 按雇员受雇日所在月排序，将最早年份的项目排在最前面
SELECT ENAME, TO_CHAR(HIREDATE, ‘yyyy’) 年份, TO_CHAR(HIREDATE, ‘mm’) 月份
FROM EMP
ORDER BY HIREDATE
–21.显示在一个月为30天的情况下所有雇员的日工资
SELECT ENAME, SAL / 30, TRUNC(SAL / 30, 1), FLOOR(SAL / 30), CEIL(SAL / 30)
FROM EMP
–22.找出在（任何年份的）1月受聘的所有雇员
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, ‘mm’) = ‘01’
–23.对于每个雇员，显示其加入公司的天数
SELECT ENAME, FLOOR(SYSDATE – HIREDATE) 入职天数
FROM EMP
ORDER BY 入职天数 DESC
–24.显示姓名字段的任何位置，包含 “i” 的所有雇员的姓名
SELECT ENAME FROM EMP WHERE INSTR(ENAME, ‘i’) > 0;
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%i%’;
–25.以年、月和日显示所有雇员的服务年限
SELECT ENAME,
FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12) 服务总年数,
FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)) 服务总月数,
FLOOR(SYSDATE – HIREDATE) 服务总天数
FROM EMP
ORDER BY 服务总天数;