• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

就业培训学习记录-day002

开发技术 开发技术 2周前 (04-08) 8次浏览

课堂任务

了解Linux的部分常用命令

  1. ps
    英文:process status
    说明:显示当前进程的状态,类似于 Windows 的任务管理器。
    语法:ps [options] [–help]
    ps -ef 显示所有命令,连带命令行。
    ps -ef | grep [进程关键字] 查找指定进程格式。
    ps -A 列出所有的进程。
    ps aux 没有-号,可以查看系统中所有的进程。
    ps -le 可以查看系统中所有的进程,还能看到进程的父进程的PID和进程优先级。

进程管理命令:

  • 程序是静态概念,本身作为一种软件资源长期保存,而进程是程序的执行过程,它是动态概念,有一定的生命周期,是动态产生和消亡
  • 程序和进程无一一对应关系,一个程序可以由多个进程共用。

进程和线程的区别:
进程:进程就是正在执行的程序或命令
线程:轻量级的进程,进程有独立的地址空间,线程没有

  1. kill
    说明:关闭进程。
    语法:kill [选项] [PID]
    kill -9 1234 强制关闭PID为1234的进程

  2. useradd
    说明:用于建立用户帐号。
    语法:useradd [选项] [用户名]
    useradd -r tt 建立系统用户tt

  3. passwd
    英文:password
    说明:更改用户密码。
    语法:passwd [选项] [用户名]
    passwd tt 设置tt用户的密码,要输入两次密码,密码无回显
    passwd -d tt 删除tt用户的密码

  4. 三种基本权限
    r 读权限,w 写权限,x 执行权限,- 无权限
    文件权限说明:
    -rwxrw-r–
    第1位:文件类型(d目录,-普通文件,l连接文件)
    第2-4位:所属用户权限,用u(user)表示
    第5-7位:所属组权限,用g(group)表示
    第8-10位:其他用户权限,用o(other)表示
    第2-10位:表示所有的权限,用a(all)表示

字符 权限 对文件的含义 对目录的含义
r 读权限 可以查看这个文件的内容 可以列出目录的内容(ls)
w 写权限 可以修改文件内容 可以在目录中创建删除文件(mkdir,rm)
x 执行权限 可以执行文件 可以进入目录(cd)
  1. chmod
    英文:change mode
    说明:控制用户对文件的权限的命令。
    语法:chmod [{ugoa}{+-=}{rwx}] [文件或目录]
    chmod u u 表示该文件的拥有者,g 表示与该文件的拥有者属于同一个群体(group),o 表示其他以外的人,a 表示这三者皆是。
    chmod + + 表示增加权限、- 表示取消权限、= 表示唯一设定权限。
    chmod r r 表示可读取,w 表示可写入,x 表示可执行,X 表示只有当该文件是个子目录或者该文件已经被设定过为可执行。
    chmod a+r file1.txt 将文件file1.txt设为所有人皆可读取

补充:八进制语法
chmod命令可以使用八进制数来指定权限。文件或目录的权限位是由9个权限位来控制,每三位为一组,它们分别是文件所有者(User)的读、写、执行,用户组(Group)的读、写、执行以及其它用户(Other)的读、写、执行。

数字 权限 rwx 二进制
7 读 + 写 + 执行 rwx 111
6 读 + 写 rw- 110
5 读 + 执行 r-x 101
4 只读 r– 100
3 写 + 执行 -wx 011
2 只写 -w- 010
1 只执行 –x 001
0 000

chmod abc file 其中a,b,c各为一个数字,分别表示User、Group、及Other的权限,file为指定的文件。
r=4,w=2,x=1
若要 rwx 属性则 4+2+1=7;
若要 rw- 属性则 4+2=6;
若要 r-x 属性则 4+1=5。
chmod a=rwx filechmod 777 file效果相同

  1. chown
    英文:change owner
    说明:用于设置文件所有者和文件关联组的命令。
    语法:chown user[:group] [文件或目录]
    chown icecream:icecreamgroup file1.txt 将文件file1.txt的拥有者设为icecream,群体的使用者icecreamgroup。

Shell基础知识

Shell 是一个用 C 语言编写的程序,它是用户使用 Linux 的桥梁。Shell 既是一种命令语言,又是一种程序设计语言。
Shell 是指一种应用程序,这个应用程序提供了一个界面,用户通过这个界面访问操作系统内核的服务。

Shell变量定义

  1. 临时变量
    所谓临时变量是指在用户在当前登录环境生效的变量。用户登录系统后,直接在命令行上定义的变量只能在当前登录环境中使用。

  2. 环境变量
    通过将环境变量定义在配置文件中,用户每次登录时候系统自动定义,无需在到命令行中重新定义。
    /etc/profile针对系统所有用户生效,此文件应用于所有用户每次登录系统时的环境变量定义。

第一个Shell脚本

通过vi f1.sh来创建一个脚本,内容如下:

#!/bin/sh
echo "Hello Shell"

运行脚本的方式

./f1/sh
bash f1.sh
source f1.sh

变量设置规则

定义变量时应注意,变量名和等号之间不能有空格,这可能和你熟悉的所有编程语言都不一样。同时,变量名的命名须遵循如下规则:

  • 命名只能使用英文字母,数字和下划线,首个字符不能以数字开头
  • 中间不能有空格,可以使用下划线_
  • 不能使用标点符号
  • 不能使用bash里的关键字(可用help命令查看保留关键字)

占位变量

$n n为数字,$0代表命令本身,$1~$9代表第1到第9个参数

expr命令

expr命令是一个手工命令行计数器,用于在UNIX/LINUX下求表达式变量的值,一般用于整数值,也可用于字符串。
语法:expr 表达式
表达式说明:

  • 用空格隔开每个项
  • 用反斜杠放在Shell特定的字符前面,*表示转义符
  • 对包含空格和其他特殊字符的字符串要用引号括起来

test命令

使用test命令可以对文件、字符串进行测试,一般配合控制流程语句使用:
test -d x x是否是目录
test -f x x是否是文件
test int1 -eq int2 两个整数是否相等
test int1 –ge int2 整数1大于等于整数2
test int1 –gt int2 整数1大于整数2
test int1 –le int2 整数1小于等于整数2
test int1 –lt int2 整数1小于整数2
test int1 –ne int2 整数1不等于整数2
if test -d $1 then ... else ...fi可以使用简化test -d $1 等价于 -d $1

Oracle Database

Oracle是常用的关系型数据库。关系型数据库是什么?关系型数据库,是指采用了关系模型来组织数据的数据库,其以行和列的形式存储数据,称为表。一组表组成了数据库

安装Oracle 11g

Oracle 11g安装步骤详细图文教程
Oracle解锁SCOTT和HR账户

基本查询

Win+R,输入cmd,在命令行输入sqlplus回车,然后输入scott再回车,最后输入密码进入到系统。

  1. 显示当前用户
show user;
  1. 查询当前用户下的表
select * from tab;

就业培训学习记录-day002
就业培训学习记录-day002
就业培训学习记录-day002
就业培训学习记录-day002

  1. 查看员工表的结构
desc emp;
  1. 查询所有员工的信息
select * from emp;
  1. 查看行宽
show linesize;
  1. 设置行宽
set linesize 240;
  1. 通过列名查询
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
  1. 查询员工号 姓名 月薪
select empno,ename,sal from emp;
  1. 查询员工号 姓名 月薪 年薪
select empno,ename,sal,sal*12 from emp;
  1. 查询员工号 姓名 月薪 年薪 奖金 年收入
select empno,ename,sal,sal*12,comm,sal*12+comm from emp;

在查询结果中,会有一些行没有结果,原因是空值的影响

  1. 空值(null)是什么?
    空值是无效的,未指定的,未知的或不可预知的值,空值不是空格或者0。
    包含null的表达式的都为null,所以在第10点的查询结果中,有空值。

  2. 滤空函数nvl(a,b)
    如果a为null则返回b,通常b为0。使用滤空函数再执行第10点的查询语句。

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;
  1. null!=null
    查询奖金为null的员工
select *
from emp
where comm=null;

查询结果为空,输入ed回车,会以记事本打开一个文件,在里面修改查询语句然后保存关闭,在查询中输入/执行上一条(指的是上一条保存或执行的)查询语句
就业培训学习记录-day002
就业培训学习记录-day002

  1. 设置别名的三种方式
select empno as "员工号",ename "姓名",sal 月薪 from emp;
  1. 基本查询语法
select * | {[distinct] column | expression [alias], ...}
from table;

column列,expression表达式,alias别名。

  1. distinct去掉重复记录
select deptno from emp;
select distinct deptno from emp;
select job from emp;
select distinct job from emp;
select distinct deptno,job from emp;

distinct作用于后面的所有的列,会显示组合起来不重复的结果

  1. concat函数
select concat('NiHao','Oracle');

SQL> select concat(‘NiHao’,’Oracle’);
select concat(‘NiHao’,’Oracle’)
                                               *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

Oracle当中有一个伪表dual

select concat('NiHao','Oracle') from dual;
select 3+2 from dual;
  1. 连接符 ||
    要查询员工信息:XXX的薪水是XXX,即显示结果如下

SMITH的薪水是800
War的薪水是1600

select ename||'的薪水是'||sal from emp;
  1. 基本查询语法,加上条件查询和排序
select * | {[distinct] column | expression [alias], ...}
from table
where condition(s);

condition(s)条件,可以有多个。

  1. 查询10号部门的员工
select *
from emp
where deptno=10;
  1. 日期和字符串
日期和字符只能在单引号中出现
字符大小写敏感,日期格式敏感
默认的日期格式是DD-MON-RR
  1. 查询名叫KING的员工(大小写敏感)
select * from emp where ename='King';
select * from emp where ename='KING';
  1. 查询入职日期是1981年11月17日的员工(日期格式敏感)
select * from emp where hiredate='1981-11-17';
select * from emp where hiredate='17-11月-81';
  1. 修改日期格式(仅当前窗口有效)
select * from v$nls_parameters;
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
  1. 其他比较运算符
  • between a and b在两个之间(包含边界,a<b)
  • in(set) 等于集合内的一个值
  • like 模糊查询(’%’,’_’),%代表任意个字符,_代表任意一个字符
  • is null 空值
  1. 查询薪水1000-2000之间的员工
select *
from emp
where sal between 1000 and 2000;
  1. 查询10和20号部门的员工
select *
from emp
where deptno in (10,20);
  1. 查询不是10和20号部门的员工
select *
from emp
where deptno not in (10,20);

如果集合中有null,不能使用not in (x,null),可以使用in (x,null),但是查不出对应列为null的结果,也就是说用in (x,null)和in (x)的效果是一样的。

select *
from emp
where deptno not in (10,20,null);
  1. 查询名字以S开头的员工
select *
from emp
where ename like 'S%';
  1. 查询名字是4个字的员工
select *
from emp
where ename like '____';
  1. 查询名中含有下划线的员工
insert into emp(empno,ename,sal,deptno) values(1001,'Tom_AB',3000,10);

因为表中没有名字含有下划线的员工,所以先插入一条,然后再做查询

select *
from emp
where ename like '%_%';

这样写查出来的结果不是我们想要的。下划线_被认为是任意一个字符,这里我们需要转义符才能查出正确的结果。

select *
from emp
where ename like '%_%' escape '';

escape后面可以指定某个字符为转义符,转义符后的第一个字符将失去特殊作用(例如下划线_代表任意一个字符)。如果你想,也可以指定下划线_当做转义符。

  1. 逻辑运算
    or 逻辑或
    and 逻辑并
    not 逻辑否
select * from emp where deptno=10 or job in ('MANAGER','PRESIDENT');
select * from emp where deptno=10 and job in ('MANAGER','PRESIDENT');
select * from emp where deptno=10 and job not in ('MANAGER','PRESIDENT');
  1. 查询员工信息,按照月薪排序
    asc 升序
    desc 降序
    如果不指定,order by默认是按升序来排
select * from emp order by sal;
select * from emp order by sal asc;
select * from emp order by sal desc;
  1. order by 后面+列、表达式、别名或者序号
select empno,ename,sal,sal*12 from emp order by sal*12 desc;
select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;
select empno,ename,sal,sal*12 年薪 From emp order by 4 desc;

序号指的是第n列,第三句查询中,n=4代表的就是sal*12,别名年薪

  1. 多个列的排序
select * from emp order by deptno,sal;
select * from emp order by deptno,sal desc;

order by作用域后面的所有列,先按照第一个列排序,再对后面的列进行排序desc只作用与离他最近的列。

  1. 查询员工信息,按照奖金排序
select * from emp order by comm;
select * from emp order by comm desc;

null值最大,这点在排序时有很好的体现出来。但是涉及到数值排序时,null值我们一般认为是最值(可以是最大值或最小值),可以通过nulls last或者nulls first来指定null的位置。

select * from emp order by comm desc nulls last;

单行函数

单行函数,简单来说就是只有一行/条输入,一行/条输出的函数。
就业培训学习记录-day002

字符函数

  1. 字符大小写转换
select lower('NiHao java'),upper('NiHao java') from dual;
  1. 字符串截取
    substr(a,b)从a中,第b位开始取
select substr('NIhao java',4) from dual;

substr(a,b,c)从a中,第b位开始取,取C位

select substr('NIhao java',4,5) from dual;
  1. 字符串字符数、字节数
    length()字符数,lengthb()字节数
select length('nihao'),lengthb('nihao') from dual;
select length('你好'),lengthb('你好') from dual;
  1. 字符串替换
    replace(a,b,c),把a中的b替换成c
select replace('nihao java','a','*') from dual;
  1. 字符串去除指定字符、去空格
    trim(‘单个字符’ from ‘待去字符串’),去掉字符串开头和末尾指定的字符
select trim('H' from 'HelHlo WordH') from dual;

trim(‘待去字符串’),去掉字符串开头和末尾的空格

select trim('  HelHlo WordH  ') from dual;

数值函数

  1. 四舍五入
    round(待处理数,到第n位小数)
select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2) from dual;

结果如下

45.93,45.9,46,50,0

  1. 截断
    trunc(待处理数,到第n位小数)
select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1),trunc(45.926,-2) from dual;

结果如下

45.92,45.9,45,40,0

查询本年的第一天

select trunc(sysdate,'year') from dual;

查询本季度的第一天

select trunc(sysdate,'q') from dual;

查询本月的第一天

select trunc(sysdate,'month') from dual;

日期函数

Oracl中的日期数据时间含有两个值:日期和时间
默认的日期格式是: DD-MON-RR

  1. 常用的日期函数
    months_between 两个日期的相差的月数
    add_months 向指定日期加上若干约束
    last_day 本月的最后一天
    next_day 指定日期的下一天

  2. 当前时间

select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
  1. 查询昨天、今天、还有明天
select (sysdate-1),sysdate,sysdate+1 from dual;
  1. 计算员工的工龄,天、周、月、年
select ename,hiredate,(sysdate-hiredate),(sysdate-hiredate)/7,(sysdate-hiredate)/30,(sysdate-hiredate)/365 from emp;

这种计算方式当然不是准确的,因为每个月的天数是不一样的,同理,每年的天数也不全是一样的(闰年的情况)。

  1. 计算员工的工龄,月
select ename,hiredate,(sysdate-hiredate)/30,months_between(sysdate,hiredate) from emp;

后者使用了函数months_between,计算结果更准确。

  1. 53个月后
select add_months(sysdate,53) from dual;
  1. 本月的最后一天
select last_day(sysdate) from dual;
  1. 下一个星期日
select next_day(sysdate,'星期日') from dual;
  1. to_char 对日期的转换
    格式如下
  • YYYY 2021
  • YEAR TWENTY TWENTY-ONE
  • MM 04
  • MONTH 4月
  • DAY 星期三
  • DD 07
select to_char(sysdate,'YYYY/MM/DD DAY') from dual;
  1. 2021年4月7日 14:53:39 今天是星期三
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是"day') from dual;

转换函数

  1. to_char 对数字的转换
    格式如下
  • 9 数字
  • 0
  • $ 美元符号
  • L 本地货币符号
  • . 小数点
  • , 千位符
  1. 查询员工的薪水,两位小数,千位符,本地货币代码
select to_char(sal,'L9,999.99') from emp;   
  1. 在SQL中类似if-then-else的用法
    CASE SQL99的语法:
CASE expr WHEN comparison_exp1 THEN return_expr1[
  WHEN comparison_exp2 THEN return_expr2
  WHEN comparison_expn THEN return_exprn
  ELSE else_expr]
END
  1. 给员工涨工资,总裁1000,经理800,其他400(用case)
select ename,job,sal 涨前,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后
from emp;
  1. decode函数
    类似java,是Oracle自己的语法:
    DECODE (col | expression,search1,resut1
    [,search2,result2,…,]
    [,default]
    )

  2. 给员工涨工资,总裁1000,经理800,其他400(用decode)

select ename,job,sal 涨前,
decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后
from emp;

通用函数

  1. 滤空函数nvl(a,b)
    如果a为null则返回b,通常b为0。使用滤空函数再执行第10点的查询语句。
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;

多行函数

多行函数也叫组函数,简单来说就是可以有多行/条输入,一行/条输出的函数。组函数会自动滤空。
就业培训学习记录-day002

  1. 工资总额
select sum(sal) from emp;
  1. 查询雇员总数
select count(*) from emp;
  1. 平均工资
select sum(sal)/count(*),avg(sal) from emp;
  1. 平均奖金
select sum(comm)/count(*),sum(comm)/count(comm),avg(comm) from emp;
  1. 组函数会自动滤空
select count(*),count(comm) from emp;
select count(*),count(nvl(comm,0)) from emp;
  1. 完整的基本查询语法
select * | {[distinct] column | expression [alias], ...}
from table
[where condition(s)]
[group by group_by_expression]
[having group_condition]
[order by column];
  1. 每个部门的平均工资
select deptno,avg(sal)
from emp
group by deptno;

使用grouo by子句,可以将表中的数据分成若干组。句式可抽象成下面的形式。

select a,组函数(x)
from emp
group by a;

在select列表中,所有未包含在组函数中的列都应该包含在group by子句中。

select a,b,c,组函数(x)
from emp
group by a,b,c;

上面的话倒过来也可以。包含在group by子句中的列不必包含在select列表中。

select a,b,c,组函数(x)
from emp
group by a,b,c,d,e;

  1. 非法使用组函数
select deptno,count(ename)
from emp;

执行结果如下

SQL> select deptno,count(ename)
  2  from emp;
select deptno,count(ename)
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数
  1. 多个列的分组
select deptno,job,sum(sal)
from emp
group by deptno,job
order by 1;

多个列的分组:先按照第一个列分组,如果相同,再第二个列分组,以此类推。

  1. 查询平均工资大于2000的部门
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
  1. 查询10号部门的平均工资
select deptno,avg(sal)
from emp
group by deptno
having deptno=10;

select deptno,avg(sal)
from emp
where deptno=10
group by deptno;
  1. group by增强(group by rollup)
select deptno,job,sum(sal) from emp group by rollup(deptno,job);

SQL优化原则

  1. 查询时用 * 效率高还是用列名效率高?
    解答:用列名的查询效率高。先记结论,后续再讲为什么。
    例子:select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;select * from emp;

  2. where true and false 和 where false and true 哪个效率更高?
    解答:where true and false效率更高。where解析条件的顺序是从右到左,因此尽量把可能为假条件的放在右边。
    例子:where 2>1 and 2<1 和 where 2<1 and 2>1

  3. select deptno,avg(sal) from emp group by deptno having deptno=10;select deptno,avg(sal) from emp where deptno=10 group by deptno;,查出来的结果相同,哪种更好?
    解答:尽量用where。

null值需要注意的地方

  1. 包含null的表达式的都为null
  2. null!=null
  3. 如果集合中有null,不能使用not in (x,null)
  4. null值最大
  5. 组函数会自动滤空

课后任务

Oracle_HR.sql

下面的练习用Oracle的HR用户来做。HR用户里的表我导出如下,如果是安装了Oracle可以直接在里面做;如果没装,可以拿下面这些数据导入到数据库中练习。

点击查看详细内容
/*
Navicat Oracle Data Transfer
Oracle Client Version : 11.2.0.1.0

Source Server         : Oracle_HR
Source Server Version : 110200
Source Host           : localhost:1521
Source Schema         : HR

Target Server Type    : ORACLE
Target Server Version : 110200
File Encoding         : 65001

Date: 2021-04-08 18:32:20
*/


-- ----------------------------
-- Table structure for COUNTRIES
-- ----------------------------
DROP TABLE "HR"."COUNTRIES";
CREATE TABLE "HR"."COUNTRIES" (
"COUNTRY_ID" CHAR(2 BYTE) NOT NULL ,
"COUNTRY_NAME" VARCHAR2(40 BYTE) NULL ,
"REGION_ID" NUMBER NULL ,
PRIMARY KEY ("COUNTRY_ID")
)
ORGANIZATION INDEX NOLOGGING NOCOMPRESS PCTTHRESHOLD 50 

;
COMMENT ON TABLE "HR"."COUNTRIES" IS 'country table. Contains 25 rows. References with locations table.';
COMMENT ON COLUMN "HR"."COUNTRIES"."COUNTRY_ID" IS 'Primary key of countries table.';
COMMENT ON COLUMN "HR"."COUNTRIES"."COUNTRY_NAME" IS 'Country name';
COMMENT ON COLUMN "HR"."COUNTRIES"."REGION_ID" IS 'Region ID for the country. Foreign key to region_id column in the departments table.';

-- ----------------------------
-- Records of COUNTRIES
-- ----------------------------
INSERT INTO "HR"."COUNTRIES" VALUES ('AR', 'Argentina', '2');
INSERT INTO "HR"."COUNTRIES" VALUES ('AU', 'Australia', '3');
INSERT INTO "HR"."COUNTRIES" VALUES ('BE', 'Belgium', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('BR', 'Brazil', '2');
INSERT INTO "HR"."COUNTRIES" VALUES ('CA', 'Canada', '2');
INSERT INTO "HR"."COUNTRIES" VALUES ('CH', 'Switzerland', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('CN', 'China', '3');
INSERT INTO "HR"."COUNTRIES" VALUES ('DE', 'Germany', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('DK', 'Denmark', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('EG', 'Egypt', '4');
INSERT INTO "HR"."COUNTRIES" VALUES ('FR', 'France', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('IL', 'Israel', '4');
INSERT INTO "HR"."COUNTRIES" VALUES ('IN', 'India', '3');
INSERT INTO "HR"."COUNTRIES" VALUES ('IT', 'Italy', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('JP', 'Japan', '3');
INSERT INTO "HR"."COUNTRIES" VALUES ('KW', 'Kuwait', '4');
INSERT INTO "HR"."COUNTRIES" VALUES ('ML', 'Malaysia', '3');
INSERT INTO "HR"."COUNTRIES" VALUES ('MX', 'Mexico', '2');
INSERT INTO "HR"."COUNTRIES" VALUES ('NG', 'Nigeria', '4');
INSERT INTO "HR"."COUNTRIES" VALUES ('NL', 'Netherlands', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('SG', 'Singapore', '3');
INSERT INTO "HR"."COUNTRIES" VALUES ('UK', 'United Kingdom', '1');
INSERT INTO "HR"."COUNTRIES" VALUES ('US', 'United States of America', '2');
INSERT INTO "HR"."COUNTRIES" VALUES ('ZM', 'Zambia', '4');
INSERT INTO "HR"."COUNTRIES" VALUES ('ZW', 'Zimbabwe', '4');

-- ----------------------------
-- Table structure for DEPARTMENTS
-- ----------------------------
DROP TABLE "HR"."DEPARTMENTS";
CREATE TABLE "HR"."DEPARTMENTS" (
"DEPARTMENT_ID" NUMBER(4) NOT NULL ,
"DEPARTMENT_NAME" VARCHAR2(30 BYTE) NOT NULL ,
"MANAGER_ID" NUMBER(6) NULL ,
"LOCATION_ID" NUMBER(4) NULL 
)
NOLOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "HR"."DEPARTMENTS" IS 'Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.';
COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_ID" IS 'Primary key column of departments table.';
COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_NAME" IS 'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ';
COMMENT ON COLUMN "HR"."DEPARTMENTS"."MANAGER_ID" IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';
COMMENT ON COLUMN "HR"."DEPARTMENTS"."LOCATION_ID" IS 'Location id where a department is located. Foreign key to location_id column of locations table.';

-- ----------------------------
-- Records of DEPARTMENTS
-- ----------------------------
INSERT INTO "HR"."DEPARTMENTS" VALUES ('10', 'Administration', '200', '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('20', 'Marketing', '201', '1800');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('30', 'Purchasing', '114', '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('40', 'Human Resources', '203', '2400');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('50', 'Shipping', '121', '1500');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('60', 'IT', '103', '1400');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('70', 'Public Relations', '204', '2700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('80', 'Sales', '145', '2500');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('90', 'Executive', '100', '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('100', 'Finance', '108', '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('110', 'Accounting', '205', '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('120', 'Treasury', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('130', 'Corporate Tax', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('140', 'Control And Credit', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('150', 'Shareholder Services', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('160', 'Benefits', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('170', 'Manufacturing', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('180', 'Construction', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('190', 'Contracting', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('200', 'Operations', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('210', 'IT Support', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('220', 'NOC', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('230', 'IT Helpdesk', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('240', 'Government Sales', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('250', 'Retail Sales', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('260', 'Recruiting', null, '1700');
INSERT INTO "HR"."DEPARTMENTS" VALUES ('270', 'Payroll', null, '1700');

-- ----------------------------
-- Table structure for EMPLOYEES
-- ----------------------------
DROP TABLE "HR"."EMPLOYEES";
CREATE TABLE "HR"."EMPLOYEES" (
"EMPLOYEE_ID" NUMBER(6) NOT NULL ,
"FIRST_NAME" VARCHAR2(20 BYTE) NULL ,
"LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ,
"EMAIL" VARCHAR2(25 BYTE) NOT NULL ,
"PHONE_NUMBER" VARCHAR2(20 BYTE) NULL ,
"HIRE_DATE" DATE NOT NULL ,
"JOB_ID" VARCHAR2(10 BYTE) NOT NULL ,
"SALARY" NUMBER(8,2) NULL ,
"COMMISSION_PCT" NUMBER(2,2) NULL ,
"MANAGER_ID" NUMBER(6) NULL ,
"DEPARTMENT_ID" NUMBER(4) NULL 
)
NOLOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "HR"."EMPLOYEES" IS 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.';
COMMENT ON COLUMN "HR"."EMPLOYEES"."EMPLOYEE_ID" IS 'Primary key of employees table.';
COMMENT ON COLUMN "HR"."EMPLOYEES"."FIRST_NAME" IS 'First name of the employee. A not null column.';
COMMENT ON COLUMN "HR"."EMPLOYEES"."LAST_NAME" IS 'Last name of the employee. A not null column.';
COMMENT ON COLUMN "HR"."EMPLOYEES"."EMAIL" IS 'Email id of the employee';
COMMENT ON COLUMN "HR"."EMPLOYEES"."PHONE_NUMBER" IS 'Phone number of the employee; includes country code and area code';
COMMENT ON COLUMN "HR"."EMPLOYEES"."HIRE_DATE" IS 'Date when the employee started on this job. A not null column.';
COMMENT ON COLUMN "HR"."EMPLOYEES"."JOB_ID" IS 'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.';
COMMENT ON COLUMN "HR"."EMPLOYEES"."SALARY" IS 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)';
COMMENT ON COLUMN "HR"."EMPLOYEES"."COMMISSION_PCT" IS 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage';
COMMENT ON COLUMN "HR"."EMPLOYEES"."MANAGER_ID" IS 'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)';
COMMENT ON COLUMN "HR"."EMPLOYEES"."DEPARTMENT_ID" IS 'Department id where employee works; foreign key to department_id
column of the departments table';

-- ----------------------------
-- Records of EMPLOYEES
-- ----------------------------
INSERT INTO "HR"."EMPLOYEES" VALUES ('100', 'Steven', 'King', 'SKING', '515.123.4567', TO_DATE('2003-06-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AD_PRES', '24000', null, null, '90');
INSERT INTO "HR"."EMPLOYEES" VALUES ('101', 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', TO_DATE('2005-09-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AD_VP', '17000', null, '100', '90');
INSERT INTO "HR"."EMPLOYEES" VALUES ('102', 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', TO_DATE('2001-01-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AD_VP', '17000', null, '100', '90');
INSERT INTO "HR"."EMPLOYEES" VALUES ('103', 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', TO_DATE('2006-01-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT_PROG', '9000', null, '102', '60');
INSERT INTO "HR"."EMPLOYEES" VALUES ('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', TO_DATE('2007-05-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT_PROG', '6000', null, '103', '60');
INSERT INTO "HR"."EMPLOYEES" VALUES ('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', TO_DATE('2005-06-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT_PROG', '4800', null, '103', '60');
INSERT INTO "HR"."EMPLOYEES" VALUES ('106', 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', TO_DATE('2006-02-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT_PROG', '4800', null, '103', '60');
INSERT INTO "HR"."EMPLOYEES" VALUES ('107', 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', TO_DATE('2007-02-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT_PROG', '4200', null, '103', '60');
INSERT INTO "HR"."EMPLOYEES" VALUES ('108', 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', TO_DATE('2002-08-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'FI_MGR', '12008', null, '101', '100');
INSERT INTO "HR"."EMPLOYEES" VALUES ('109', 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', TO_DATE('2002-08-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'FI_ACCOUNT', '9000', null, '108', '100');
INSERT INTO "HR"."EMPLOYEES" VALUES ('110', 'John', 'Chen', 'JCHEN', '515.124.4269', TO_DATE('2005-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'FI_ACCOUNT', '8200', null, '108', '100');
INSERT INTO "HR"."EMPLOYEES" VALUES ('111', 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', TO_DATE('2005-09-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'FI_ACCOUNT', '7700', null, '108', '100');
INSERT INTO "HR"."EMPLOYEES" VALUES ('112', 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', TO_DATE('2006-03-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'FI_ACCOUNT', '7800', null, '108', '100');
INSERT INTO "HR"."EMPLOYEES" VALUES ('113', 'Luis', 'Popp', 'LPOPP', '515.124.4567', TO_DATE('2007-12-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'FI_ACCOUNT', '6900', null, '108', '100');
INSERT INTO "HR"."EMPLOYEES" VALUES ('114', 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', TO_DATE('2002-12-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PU_MAN', '11000', null, '100', '30');
INSERT INTO "HR"."EMPLOYEES" VALUES ('115', 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', TO_DATE('2003-05-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PU_CLERK', '3100', null, '114', '30');
INSERT INTO "HR"."EMPLOYEES" VALUES ('116', 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', TO_DATE('2005-12-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PU_CLERK', '2900', null, '114', '30');
INSERT INTO "HR"."EMPLOYEES" VALUES ('117', 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', TO_DATE('2005-07-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PU_CLERK', '2800', null, '114', '30');
INSERT INTO "HR"."EMPLOYEES" VALUES ('118', 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', TO_DATE('2006-11-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PU_CLERK', '2600', null, '114', '30');
INSERT INTO "HR"."EMPLOYEES" VALUES ('119', 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', TO_DATE('2007-08-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PU_CLERK', '2500', null, '114', '30');
INSERT INTO "HR"."EMPLOYEES" VALUES ('120', 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', TO_DATE('2004-07-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_MAN', '8000', null, '100', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('121', 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', TO_DATE('2005-04-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_MAN', '8200', null, '100', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('122', 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', TO_DATE('2003-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_MAN', '7900', null, '100', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('123', 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', TO_DATE('2005-10-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_MAN', '6500', null, '100', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('124', 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', TO_DATE('2007-11-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_MAN', '5800', null, '100', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('125', 'Julia', 'Nayer', 'JNAYER', '650.124.1214', TO_DATE('2005-07-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3200', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('126', 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', TO_DATE('2006-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2700', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('127', 'James', 'Landry', 'JLANDRY', '650.124.1334', TO_DATE('2007-01-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2400', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('128', 'Steven', 'Markle', 'SMARKLE', '650.124.1434', TO_DATE('2008-03-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2200', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('129', 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', TO_DATE('2005-08-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3300', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('130', 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', TO_DATE('2005-10-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2800', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('131', 'James', 'Marlow', 'JAMRLOW', '650.124.7234', TO_DATE('2005-02-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2500', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('132', 'TJ', 'Olson', 'TJOLSON', '650.124.8234', TO_DATE('2007-04-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2100', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('133', 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', TO_DATE('2004-06-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3300', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('134', 'Michael', 'Rogers', 'MROGERS', '650.127.1834', TO_DATE('2006-08-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2900', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('135', 'Ki', 'Gee', 'KGEE', '650.127.1734', TO_DATE('2007-12-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2400', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('136', 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', TO_DATE('2008-02-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2200', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('137', 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', TO_DATE('2003-07-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3600', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('138', 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', TO_DATE('2005-10-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3200', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('139', 'John', 'Seo', 'JSEO', '650.121.2019', TO_DATE('2006-02-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2700', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('140', 'Joshua', 'Patel', 'JPATEL', '650.121.1834', TO_DATE('2006-04-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2500', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('141', 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', TO_DATE('2003-10-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3500', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('142', 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', TO_DATE('2005-01-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '3100', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('143', 'Randall', 'Matos', 'RMATOS', '650.121.2874', TO_DATE('2006-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2600', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('144', 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', TO_DATE('2006-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '2500', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('145', 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', TO_DATE('2004-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_MAN', '14000', '0.40', '100', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('146', 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', TO_DATE('2005-01-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_MAN', '13500', '0.30', '100', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('147', 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', TO_DATE('2005-03-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_MAN', '12000', '0.30', '100', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('148', 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', TO_DATE('2007-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_MAN', '11000', '0.30', '100', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('149', 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', TO_DATE('2008-01-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_MAN', '10500', '0.20', '100', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('150', 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', TO_DATE('2005-01-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '10000', '0.30', '145', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('151', 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', TO_DATE('2005-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '9500', '0.25', '145', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('152', 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', TO_DATE('2005-08-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '9000', '0.25', '145', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('153', 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', TO_DATE('2006-03-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '8000', '0.20', '145', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('154', 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', TO_DATE('2006-12-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7500', '0.20', '145', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('155', 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', TO_DATE('2007-11-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7000', '0.15', '145', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('156', 'Janette', 'King', 'JKING', '011.44.1345.429268', TO_DATE('2004-01-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '10000', '0.35', '146', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('157', 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', TO_DATE('2004-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '9500', '0.35', '146', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('158', 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', TO_DATE('2004-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '9000', '0.35', '146', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('159', 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', TO_DATE('2005-03-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '8000', '0.30', '146', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('160', 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', TO_DATE('2005-12-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7500', '0.30', '146', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('161', 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', TO_DATE('2006-11-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7000', '0.25', '146', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('162', 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', TO_DATE('2005-11-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '10500', '0.25', '147', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('163', 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', TO_DATE('2007-03-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '9500', '0.15', '147', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('164', 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', TO_DATE('2008-01-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7200', '0.10', '147', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('165', 'David', 'Lee', 'DLEE', '011.44.1346.529268', TO_DATE('2008-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '6800', '0.10', '147', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('166', 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', TO_DATE('2008-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '6400', '0.10', '147', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('167', 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', TO_DATE('2008-04-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '6200', '0.10', '147', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('168', 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', TO_DATE('2005-03-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '11500', '0.25', '148', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('169', 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', TO_DATE('2006-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '10000', '0.20', '148', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('170', 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', TO_DATE('2006-01-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '9600', '0.20', '148', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('171', 'William', 'Smith', 'WSMITH', '011.44.1343.629268', TO_DATE('2007-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7400', '0.15', '148', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('172', 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', TO_DATE('2007-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7300', '0.15', '148', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('173', 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', TO_DATE('2008-04-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '6100', '0.10', '148', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('174', 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', TO_DATE('2004-05-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '11000', '0.30', '149', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('175', 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', TO_DATE('2005-03-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '8800', '0.25', '149', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('176', 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', TO_DATE('2006-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '8600', '0.20', '149', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('177', 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', TO_DATE('2006-04-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '8400', '0.20', '149', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('178', 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', TO_DATE('2007-05-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '7000', '0.15', '149', null);
INSERT INTO "HR"."EMPLOYEES" VALUES ('179', 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', TO_DATE('2008-01-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '6200', '0.10', '149', '80');
INSERT INTO "HR"."EMPLOYEES" VALUES ('180', 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', TO_DATE('2006-01-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3200', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('181', 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', TO_DATE('2006-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3100', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('182', 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', TO_DATE('2007-06-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2500', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('183', 'Girard', 'Geoni', 'GGEONI', '650.507.9879', TO_DATE('2008-02-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2800', null, '120', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('184', 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', TO_DATE('2004-01-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '4200', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('185', 'Alexis', 'Bull', 'ABULL', '650.509.2876', TO_DATE('2005-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '4100', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('186', 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', TO_DATE('2006-06-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3400', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('187', 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', TO_DATE('2007-02-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3000', null, '121', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('188', 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', TO_DATE('2005-06-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3800', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('189', 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', TO_DATE('2005-08-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3600', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('190', 'Timothy', 'Gates', 'TGATES', '650.505.3876', TO_DATE('2006-07-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2900', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('191', 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', TO_DATE('2007-12-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2500', null, '122', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('192', 'Sarah', 'Bell', 'SBELL', '650.501.1876', TO_DATE('2004-02-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '4000', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('193', 'Britney', 'Everett', 'BEVERETT', '650.501.2876', TO_DATE('2005-03-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3900', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('194', 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', TO_DATE('2006-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3200', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('195', 'Vance', 'Jones', 'VJONES', '650.501.4876', TO_DATE('2007-03-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2800', null, '123', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('196', 'Alana', 'Walsh', 'AWALSH', '650.507.9811', TO_DATE('2006-04-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3100', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('197', 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', TO_DATE('2006-05-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '3000', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('198', 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', TO_DATE('2007-06-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2600', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('199', 'Douglas', 'Grant', 'DGRANT', '650.507.9844', TO_DATE('2008-01-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SH_CLERK', '2600', null, '124', '50');
INSERT INTO "HR"."EMPLOYEES" VALUES ('200', 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', TO_DATE('2003-09-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AD_ASST', '4400', null, '101', '10');
INSERT INTO "HR"."EMPLOYEES" VALUES ('201', 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', TO_DATE('2004-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'MK_MAN', '13000', null, '100', '20');
INSERT INTO "HR"."EMPLOYEES" VALUES ('202', 'Pat', 'Fay', 'PFAY', '603.123.6666', TO_DATE('2005-08-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'MK_REP', '6000', null, '201', '20');
INSERT INTO "HR"."EMPLOYEES" VALUES ('203', 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', TO_DATE('2002-06-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'HR_REP', '6500', null, '101', '40');
INSERT INTO "HR"."EMPLOYEES" VALUES ('204', 'Hermann', 'Baer', 'HBAER', '515.123.8888', TO_DATE('2002-06-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PR_REP', '10000', null, '101', '70');
INSERT INTO "HR"."EMPLOYEES" VALUES ('205', 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', TO_DATE('2002-06-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AC_MGR', '12008', null, '101', '110');
INSERT INTO "HR"."EMPLOYEES" VALUES ('206', 'William', 'Gietz', 'WGIETZ', '515.123.8181', TO_DATE('2002-06-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AC_ACCOUNT', '8300', null, '205', '110');

-- ----------------------------
-- Table structure for JOB_HISTORY
-- ----------------------------
DROP TABLE "HR"."JOB_HISTORY";
CREATE TABLE "HR"."JOB_HISTORY" (
"EMPLOYEE_ID" NUMBER(6) NOT NULL ,
"START_DATE" DATE NOT NULL ,
"END_DATE" DATE NOT NULL ,
"JOB_ID" VARCHAR2(10 BYTE) NOT NULL ,
"DEPARTMENT_ID" NUMBER(4) NULL 
)
NOLOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "HR"."JOB_HISTORY" IS 'Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.';
COMMENT ON COLUMN "HR"."JOB_HISTORY"."EMPLOYEE_ID" IS 'A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table';
COMMENT ON COLUMN "HR"."JOB_HISTORY"."START_DATE" IS 'A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)';
COMMENT ON COLUMN "HR"."JOB_HISTORY"."END_DATE" IS 'Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)';
COMMENT ON COLUMN "HR"."JOB_HISTORY"."JOB_ID" IS 'Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.';
COMMENT ON COLUMN "HR"."JOB_HISTORY"."DEPARTMENT_ID" IS 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table';

-- ----------------------------
-- Records of JOB_HISTORY
-- ----------------------------
INSERT INTO "HR"."JOB_HISTORY" VALUES ('102', TO_DATE('2001-01-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2006-07-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT_PROG', '60');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('101', TO_DATE('1997-09-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2001-10-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AC_ACCOUNT', '110');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('101', TO_DATE('2001-10-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2005-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AC_MGR', '110');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('201', TO_DATE('2004-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-12-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'MK_REP', '20');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('114', TO_DATE('2006-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '50');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('122', TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ST_CLERK', '50');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('200', TO_DATE('1995-09-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2001-06-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AD_ASST', '90');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('176', TO_DATE('2006-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2006-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_REP', '80');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('176', TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'SA_MAN', '80');
INSERT INTO "HR"."JOB_HISTORY" VALUES ('200', TO_DATE('2002-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2006-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'AC_ACCOUNT', '90');

-- ----------------------------
-- Table structure for JOBS
-- ----------------------------
DROP TABLE "HR"."JOBS";
CREATE TABLE "HR"."JOBS" (
"JOB_ID" VARCHAR2(10 BYTE) NOT NULL ,
"JOB_TITLE" VARCHAR2(35 BYTE) NOT NULL ,
"MIN_SALARY" NUMBER(6) NULL ,
"MAX_SALARY" NUMBER(6) NULL 
)
NOLOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "HR"."JOBS" IS 'jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.';
COMMENT ON COLUMN "HR"."JOBS"."JOB_ID" IS 'Primary key of jobs table.';
COMMENT ON COLUMN "HR"."JOBS"."JOB_TITLE" IS 'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';
COMMENT ON COLUMN "HR"."JOBS"."MIN_SALARY" IS 'Minimum salary for a job title.';
COMMENT ON COLUMN "HR"."JOBS"."MAX_SALARY" IS 'Maximum salary for a job title';

-- ----------------------------
-- Records of JOBS
-- ----------------------------
INSERT INTO "HR"."JOBS" VALUES ('AD_PRES', 'President', '20080', '40000');
INSERT INTO "HR"."JOBS" VALUES ('AD_VP', 'Administration Vice President', '15000', '30000');
INSERT INTO "HR"."JOBS" VALUES ('AD_ASST', 'Administration Assistant', '3000', '6000');
INSERT INTO "HR"."JOBS" VALUES ('FI_MGR', 'Finance Manager', '8200', '16000');
INSERT INTO "HR"."JOBS" VALUES ('FI_ACCOUNT', 'Accountant', '4200', '9000');
INSERT INTO "HR"."JOBS" VALUES ('AC_MGR', 'Accounting Manager', '8200', '16000');
INSERT INTO "HR"."JOBS" VALUES ('AC_ACCOUNT', 'Public Accountant', '4200', '9000');
INSERT INTO "HR"."JOBS" VALUES ('SA_MAN', 'Sales Manager', '10000', '20080');
INSERT INTO "HR"."JOBS" VALUES ('SA_REP', 'Sales Representative', '6000', '12008');
INSERT INTO "HR"."JOBS" VALUES ('PU_MAN', 'Purchasing Manager', '8000', '15000');
INSERT INTO "HR"."JOBS" VALUES ('PU_CLERK', 'Purchasing Clerk', '2500', '5500');
INSERT INTO "HR"."JOBS" VALUES ('ST_MAN', 'Stock Manager', '5500', '8500');
INSERT INTO "HR"."JOBS" VALUES ('ST_CLERK', 'Stock Clerk', '2008', '5000');
INSERT INTO "HR"."JOBS" VALUES ('SH_CLERK', 'Shipping Clerk', '2500', '5500');
INSERT INTO "HR"."JOBS" VALUES ('IT_PROG', 'Programmer', '4000', '10000');
INSERT INTO "HR"."JOBS" VALUES ('MK_MAN', 'Marketing Manager', '9000', '15000');
INSERT INTO "HR"."JOBS" VALUES ('MK_REP', 'Marketing Representative', '4000', '9000');
INSERT INTO "HR"."JOBS" VALUES ('HR_REP', 'Human Resources Representative', '4000', '9000');
INSERT INTO "HR"."JOBS" VALUES ('PR_REP', 'Public Relations Representative', '4500', '10500');

-- ----------------------------
-- Table structure for LOCATIONS
-- ----------------------------
DROP TABLE "HR"."LOCATIONS";
CREATE TABLE "HR"."LOCATIONS" (
"LOCATION_ID" NUMBER(4) NOT NULL ,
"STREET_ADDRESS" VARCHAR2(40 BYTE) NULL ,
"POSTAL_CODE" VARCHAR2(12 BYTE) NULL ,
"CITY" VARCHAR2(30 BYTE) NOT NULL ,
"STATE_PROVINCE" VARCHAR2(25 BYTE) NULL ,
"COUNTRY_ID" CHAR(2 BYTE) NULL 
)
NOLOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "HR"."LOCATIONS" IS 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables. ';
COMMENT ON COLUMN "HR"."LOCATIONS"."LOCATION_ID" IS 'Primary key of locations table';
COMMENT ON COLUMN "HR"."LOCATIONS"."STREET_ADDRESS" IS 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';
COMMENT ON COLUMN "HR"."LOCATIONS"."POSTAL_CODE" IS 'Postal code of the location of an office, warehouse, or production site
of a company. ';
COMMENT ON COLUMN "HR"."LOCATIONS"."CITY" IS 'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';
COMMENT ON COLUMN "HR"."LOCATIONS"."STATE_PROVINCE" IS 'State or Province where an office, warehouse, or production site of a
company is located.';
COMMENT ON COLUMN "HR"."LOCATIONS"."COUNTRY_ID" IS 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';

-- ----------------------------
-- Records of LOCATIONS
-- ----------------------------
INSERT INTO "HR"."LOCATIONS" VALUES ('1000', '1297 Via Cola di Rie', '00989', 'Roma', null, 'IT');
INSERT INTO "HR"."LOCATIONS" VALUES ('1100', '93091 Calle della Testa', '10934', 'Venice', null, 'IT');
INSERT INTO "HR"."LOCATIONS" VALUES ('1200', '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
INSERT INTO "HR"."LOCATIONS" VALUES ('1300', '9450 Kamiya-cho', '6823', 'Hiroshima', null, 'JP');
INSERT INTO "HR"."LOCATIONS" VALUES ('1400', '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO "HR"."LOCATIONS" VALUES ('1500', '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO "HR"."LOCATIONS" VALUES ('1600', '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
INSERT INTO "HR"."LOCATIONS" VALUES ('1700', '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO "HR"."LOCATIONS" VALUES ('1800', '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO "HR"."LOCATIONS" VALUES ('1900', '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
INSERT INTO "HR"."LOCATIONS" VALUES ('2000', '40-5-12 Laogianggen', '190518', 'Beijing', null, 'CN');
INSERT INTO "HR"."LOCATIONS" VALUES ('2100', '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
INSERT INTO "HR"."LOCATIONS" VALUES ('2200', '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
INSERT INTO "HR"."LOCATIONS" VALUES ('2300', '198 Clementi North', '540198', 'Singapore', null, 'SG');
INSERT INTO "HR"."LOCATIONS" VALUES ('2400', '8204 Arthur St', null, 'London', null, 'UK');
INSERT INTO "HR"."LOCATIONS" VALUES ('2500', 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO "HR"."LOCATIONS" VALUES ('2600', '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
INSERT INTO "HR"."LOCATIONS" VALUES ('2700', 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
INSERT INTO "HR"."LOCATIONS" VALUES ('2800', 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
INSERT INTO "HR"."LOCATIONS" VALUES ('2900', '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
INSERT INTO "HR"."LOCATIONS" VALUES ('3000', 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
INSERT INTO "HR"."LOCATIONS" VALUES ('3100', 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
INSERT INTO "HR"."LOCATIONS" VALUES ('3200', 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');

-- ----------------------------
-- Table structure for REGIONS
-- ----------------------------
DROP TABLE "HR"."REGIONS";
CREATE TABLE "HR"."REGIONS" (
"REGION_ID" NUMBER NOT NULL ,
"REGION_NAME" VARCHAR2(25 BYTE) NULL 
)
NOLOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of REGIONS
-- ----------------------------
INSERT INTO "HR"."REGIONS" VALUES ('1', 'Europe');
INSERT INTO "HR"."REGIONS" VALUES ('2', 'Americas');
INSERT INTO "HR"."REGIONS" VALUES ('3', 'Asia');
INSERT INTO "HR"."REGIONS" VALUES ('4', 'Middle East and Africa');

-- ----------------------------
-- View structure for EMP_DETAILS_VIEW
-- ----------------------------
CREATE OR REPLACE FORCE VIEW "HR"."EMP_DETAILS_VIEW" AS 
SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY 
CONSTRAINTS "SYS_C0011032";

-- ----------------------------
-- Procedure structure for ADD_JOB_HISTORY
-- ----------------------------
CREATE OR REPLACE PROCEDURE "HR"."ADD_JOB_HISTORY"
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/

-- ----------------------------
-- Procedure structure for SECURE_DML
-- ----------------------------
CREATE OR REPLACE PROCEDURE "HR"."SECURE_DML"
IS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
	RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END secure_dml;
/

-- ----------------------------
-- Sequence structure for DEPARTMENTS_SEQ
-- ----------------------------
DROP SEQUENCE "HR"."DEPARTMENTS_SEQ";
CREATE SEQUENCE "HR"."DEPARTMENTS_SEQ"
 INCREMENT BY 10
 MINVALUE 1
 MAXVALUE 9990
 START WITH 280
 NOCACHE ;

-- ----------------------------
-- Sequence structure for EMPLOYEES_SEQ
-- ----------------------------
DROP SEQUENCE "HR"."EMPLOYEES_SEQ";
CREATE SEQUENCE "HR"."EMPLOYEES_SEQ"
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 9999999999999999999999999999
 START WITH 207
 NOCACHE ;

-- ----------------------------
-- Sequence structure for LOCATIONS_SEQ
-- ----------------------------
DROP SEQUENCE "HR"."LOCATIONS_SEQ";
CREATE SEQUENCE "HR"."LOCATIONS_SEQ"
 INCREMENT BY 100
 MINVALUE 1
 MAXVALUE 9900
 START WITH 3300
 NOCACHE ;

-- ----------------------------
-- Indexes structure for table COUNTRIES
-- ----------------------------

-- ----------------------------
-- Checks structure for table COUNTRIES
-- ----------------------------
ALTER TABLE "HR"."COUNTRIES" ADD CHECK ("COUNTRY_ID" IS NOT NULL);

-- ----------------------------
-- Indexes structure for table DEPARTMENTS
-- ----------------------------
CREATE INDEX "HR"."DEPT_LOCATION_IX"
ON "HR"."DEPARTMENTS" ("LOCATION_ID" ASC)
NOLOGGING
VISIBLE;

-- ----------------------------
-- Checks structure for table DEPARTMENTS
-- ----------------------------
ALTER TABLE "HR"."DEPARTMENTS" ADD CHECK ("DEPARTMENT_NAME" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table DEPARTMENTS
-- ----------------------------
ALTER TABLE "HR"."DEPARTMENTS" ADD PRIMARY KEY ("DEPARTMENT_ID");

-- ----------------------------
-- Indexes structure for table EMPLOYEES
-- ----------------------------
CREATE INDEX "HR"."EMP_DEPARTMENT_IX"
ON "HR"."EMPLOYEES" ("DEPARTMENT_ID" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."EMP_JOB_IX"
ON "HR"."EMPLOYEES" ("JOB_ID" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."EMP_MANAGER_IX"
ON "HR"."EMPLOYEES" ("MANAGER_ID" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."EMP_NAME_IX"
ON "HR"."EMPLOYEES" ("LAST_NAME" ASC, "FIRST_NAME" ASC)
NOLOGGING
VISIBLE;

-- ----------------------------
-- Triggers structure for table EMPLOYEES
-- ----------------------------
CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES" BEFORE INSERT OR DELETE OR UPDATE ON "HR"."EMPLOYEES" REFERENCING OLD AS "OLD" NEW AS "NEW" DISABLE
BEGIN
  secure_dml;
END secure_employees;CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY" AFTER UPDATE OF "DEPARTMENT_ID","JOB_ID" ON "HR"."EMPLOYEES" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW ENABLE
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
-- ----------------------------
-- Uniques structure for table EMPLOYEES
-- ----------------------------
ALTER TABLE "HR"."EMPLOYEES" ADD UNIQUE ("EMAIL");

-- ----------------------------
-- Checks structure for table EMPLOYEES
-- ----------------------------
ALTER TABLE "HR"."EMPLOYEES" ADD CHECK ("EMAIL" IS NOT NULL);
ALTER TABLE "HR"."EMPLOYEES" ADD CHECK ("HIRE_DATE" IS NOT NULL);
ALTER TABLE "HR"."EMPLOYEES" ADD CHECK ("JOB_ID" IS NOT NULL);
ALTER TABLE "HR"."EMPLOYEES" ADD CHECK ("LAST_NAME" IS NOT NULL);
ALTER TABLE "HR"."EMPLOYEES" ADD CHECK (salary > 0);

-- ----------------------------
-- Primary Key structure for table EMPLOYEES
-- ----------------------------
ALTER TABLE "HR"."EMPLOYEES" ADD PRIMARY KEY ("EMPLOYEE_ID");

-- ----------------------------
-- Indexes structure for table JOB_HISTORY
-- ----------------------------
CREATE INDEX "HR"."JHIST_DEPARTMENT_IX"
ON "HR"."JOB_HISTORY" ("DEPARTMENT_ID" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."JHIST_EMPLOYEE_IX"
ON "HR"."JOB_HISTORY" ("EMPLOYEE_ID" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."JHIST_JOB_IX"
ON "HR"."JOB_HISTORY" ("JOB_ID" ASC)
NOLOGGING
VISIBLE;

-- ----------------------------
-- Checks structure for table JOB_HISTORY
-- ----------------------------
ALTER TABLE "HR"."JOB_HISTORY" ADD CHECK (end_date > start_date);
ALTER TABLE "HR"."JOB_HISTORY" ADD CHECK ("EMPLOYEE_ID" IS NOT NULL);
ALTER TABLE "HR"."JOB_HISTORY" ADD CHECK ("END_DATE" IS NOT NULL);
ALTER TABLE "HR"."JOB_HISTORY" ADD CHECK ("JOB_ID" IS NOT NULL);
ALTER TABLE "HR"."JOB_HISTORY" ADD CHECK ("START_DATE" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table JOB_HISTORY
-- ----------------------------
ALTER TABLE "HR"."JOB_HISTORY" ADD PRIMARY KEY ("EMPLOYEE_ID", "START_DATE");

-- ----------------------------
-- Indexes structure for table JOBS
-- ----------------------------

-- ----------------------------
-- Checks structure for table JOBS
-- ----------------------------
ALTER TABLE "HR"."JOBS" ADD CHECK ("JOB_TITLE" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table JOBS
-- ----------------------------
ALTER TABLE "HR"."JOBS" ADD PRIMARY KEY ("JOB_ID");

-- ----------------------------
-- Indexes structure for table LOCATIONS
-- ----------------------------
CREATE INDEX "HR"."LOC_CITY_IX"
ON "HR"."LOCATIONS" ("CITY" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."LOC_COUNTRY_IX"
ON "HR"."LOCATIONS" ("COUNTRY_ID" ASC)
NOLOGGING
VISIBLE;
CREATE INDEX "HR"."LOC_STATE_PROVINCE_IX"
ON "HR"."LOCATIONS" ("STATE_PROVINCE" ASC)
NOLOGGING
VISIBLE;

-- ----------------------------
-- Checks structure for table LOCATIONS
-- ----------------------------
ALTER TABLE "HR"."LOCATIONS" ADD CHECK ("CITY" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table LOCATIONS
-- ----------------------------
ALTER TABLE "HR"."LOCATIONS" ADD PRIMARY KEY ("LOCATION_ID");

-- ----------------------------
-- Indexes structure for table REGIONS
-- ----------------------------

-- ----------------------------
-- Checks structure for table REGIONS
-- ----------------------------
ALTER TABLE "HR"."REGIONS" ADD CHECK ("REGION_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table REGIONS
-- ----------------------------
ALTER TABLE "HR"."REGIONS" ADD PRIMARY KEY ("REGION_ID");

-- ----------------------------
-- Foreign Key structure for table "HR"."COUNTRIES"
-- ----------------------------
ALTER TABLE "HR"."COUNTRIES" ADD FOREIGN KEY ("REGION_ID") REFERENCES "HR"."REGIONS" ("REGION_ID");

-- ----------------------------
-- Foreign Key structure for table "HR"."DEPARTMENTS"
-- ----------------------------
ALTER TABLE "HR"."DEPARTMENTS" ADD FOREIGN KEY ("LOCATION_ID") REFERENCES "HR"."LOCATIONS" ("LOCATION_ID");
ALTER TABLE "HR"."DEPARTMENTS" ADD FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID");

-- ----------------------------
-- Foreign Key structure for table "HR"."EMPLOYEES"
-- ----------------------------
ALTER TABLE "HR"."EMPLOYEES" ADD FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID");
ALTER TABLE "HR"."EMPLOYEES" ADD FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID");
ALTER TABLE "HR"."EMPLOYEES" ADD FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID");

-- ----------------------------
-- Foreign Key structure for table "HR"."JOB_HISTORY"
-- ----------------------------
ALTER TABLE "HR"."JOB_HISTORY" ADD FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID");
ALTER TABLE "HR"."JOB_HISTORY" ADD FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID");
ALTER TABLE "HR"."JOB_HISTORY" ADD FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID");

-- ----------------------------
-- Foreign Key structure for table "HR"."LOCATIONS"
-- ----------------------------
ALTER TABLE "HR"."LOCATIONS" ADD FOREIGN KEY ("COUNTRY_ID") REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID");

使用HR用户

如果是在cmd里新打开sqlplus,那直接使用HR用户即可。如果是正在使用其他的用户(比如Scott),可以使用CONN HR来切换到HR用户。

练习题

  1. 查询工资大于12000的员工姓名和工资
select last_name||' '||first_name,salary
from employees
where salary>12000;
  1. 查询员工号为176的员工姓名和部门号
select last_name||' '||first_name,department_id
from employees
where employee_id=176;
  1. 选择工资不在5000到12000的员工姓名和工资
select last_name||' '||first_name,salary
from employees
where salary not between 5000 and 12000;
  1. 选择雇用时间在2008-02-01到2008-05-01之间的员工姓名,job_id和雇用时间
select last_name||' '||first_name,job_id,employee_id,hire_date
from employees
where hire_date between to_date('2008-02-01','YYYY-MM-DD') and to_date('2008-05-01','YYYY-MM-DD');
  1. 选择在20或50号部门工作的员工姓名和部门号
select last_name||' '||first_name,department_id
from employees
where department_id in(20,50);
  1. 选择在2004年雇用的员工的姓名和雇用时间
select last_name||' '||first_name,hire_date
from employees
where hire_date between to_date('2004-01-01','YYYY-MM-DD') and to_date('2004-12-31','YYYY-MM-DD');
  1. 选择公司中没有管理者的员工姓名及job_id
select last_name||' '||first_name,job_id
from employees
where manager_id is null;
  1. 选择公司中有奖金的员工姓名,工资和奖金级别
select last_name||' '||first_name,salary,commission_pct
from employees
where commission_pct is not null;
  1. 选择员工姓名的第三个字母是a的员工姓名
select last_name||' '||first_name
from employees
where last_name||' '||first_name like '__a%';
  1. 选择姓名中有字母a和e的员工姓名
select last_name||' '||first_name
from employees
where last_name||' '||first_name like '%a%' and last_name||' '||first_name like '%e%';
  1. 显示系统时间
select sysdate from dual;
  1. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)
select employee_id,last_name||' '||first_name,salary,salary*1.2 "new salary"
from employees;
  1. 将员工的姓名按首字母排序,并写出姓名的长度(length)
select last_name||' '||first_name,length(last_name||first_name)
from employees
order by substr(last_name||first_name,1,1);
  1. 查询各员工的姓名,并显示出各员工在公司工作的月份数(worked_month)
select last_name||' '||first_name,trunc(months_between(sysdate,hire_date),0)
from employees;
  1. 查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列
select last_name||' '||first_name,trunc(months_between(sysdate,hire_date),0)
from employees
order by 2 desc;
  1. 做一个查询,产生下面的结果
  • <last_name> earns monthly but wants <salary*3>
Dream Salary
King earns $24000 monthly but wants $72000
select last_name||' earns'||to_char(salary,'$99999')||'but wants'||to_char(salary*3,'$99999') "Dream Salary"
from employees;
  1. 使用decode函数,按照下面的条件做一个查询
Job_id Grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
Others F

查询的结果形式如下

Last_name Job_id Grade
king AD_PRES A
select last_name,job_id,decode(job_id,
'AD_PRES','A',
'ST_MAN','B',
'IT_PROG','C',
'SA_REP ','D',
'ST_CLERK','E',
'F') Grade
from employees;
  1. 将第17题的查询用case函数再写一遍
select last_name,job_id,case job_id
when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP ' then 'D'
when 'ST_CLERK' then 'E'
else 'F'
end Grade
from employees;
  1. 查询公司员工工资的最大值,最小值,平均值,总和
select max(salary),min(salary),avg(salary),sum(salary)
from employees;
  1. 查询各job_id的员工工资的最大值,最小值,平均值,总和
select job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id;
  1. 查询各个job_id的员工人数
select job_id,count(employee_id)
from employees
group by job_id;
  1. 查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(salary)-min(salary) "DIFFERENCE"
from employees;
  1. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>=6000;
  1. 查询所有部门的名字,location_id,员工数量和工资平均值
select department_name,location_id,count(employee_id),avg(salary)
from employees e join departments d on e.department_id = d.department_id 
group by department_name,location_id;

程序员灯塔
转载请注明原文链接:就业培训学习记录-day002
喜欢 (0)