数据库编程——Oracle SQL

在学习数据库编程总结了笔记,并分享出来。有问题请及时联系博主:Alliswell_WP,转载请注明出处。

09-数据库编程day01(oracle sql)

目录:
零、课程安排
一、学习目标
二、oracle
1、oracle的体系结构
2、oracle相关的概念
3、oracle的安装
4、登录和基本设置
5、基本查询
6、条件过滤1-where过滤
7、条件过滤2-逻辑运算符
8、条件过滤3-模糊匹配
9、排序
10、单行函数
(1)字符函数
(2)数值函数
(3)转换函数(重要)
(4)日期函数
11、条件表达式
12、多行处理
(1)分组函数
(2)分组数据
13、在redhat创建hr数据库用户

 

零、课程安排

什么是数据库?数据的库。

为什么学习数据库?管理数据,便于程序开发。

SQL语言与数据库的关系?

 

一、学习目标

1、oracle的体系结构(实例和内存的理解)
2、oracle安装的注意事项
3、基本的查询语法
4、条件过滤
5、oracle的函数
6、分组函数
7、分组数据


》扩展:设置cmd字体显示加粗

1)运行win+r,输入regedit打开注册表
2)然后找到以下路径:
HKEY_LOCAL_MACHINE/SOFTWARE/Microssft/Windows NT/CurrentVersion/Console/TrueTypeFont
3)新建一个字符串值(改名为“00000”,记住:每加一条,名字多加一个0)
修改值为系统已安装字体名字(如:Source Code Pro),但并不是每个字体都能用
运行cmd,输入chcp 437,然后右键窗口标题栏—>选择属性—>字体,然后配置字体大小“20”,字体“Consolas”确认并关闭cmd
4)重新打开cmd,可以惊喜的发现字体变好看了,很养眼

》注意:
1)有些字体是无法识别的,所以多试试其他字体
2)输入 chcp 936 回车,可以切换回来原来的样子


 

二、oracle

1、oracle的体系结构

》Oracle服务器 :是一个数据管理系统(RDBMS),它提供开放的, 全面的, 近乎完整的信息管理。由1个数据库和一个(或多个)实例组成。数据库位于硬盘上,实例位于内存中。

 

2、oracle相关的概念

》表空间+数据文件:

逻辑概念:表空间,表空间由多个数据文件组成。位于实例上,在内存中。

物理概念:数据文件,位于硬盘之上。(C:\app\Administrator\oradata\orcl目录内后缀为.DBF的文件)

                     一个表空间可以包含多个数据文件。1:n(表空间:数据文件)

》段、区、块

段存在于表空间中;  段是区的集合;  区是数据块的集合;  数据块会被映射到磁盘块。

 

3、oracle的安装

》服务器安装的注意事项:

目录不能有中文和空格

》客户端安装的注意事项:
目录不能有中文和空格 一定要选择管理员模式

 

》测试是否安装成功:

本机有服务器:

打开cmd命令窗口:>sqlplus / as sysdba(以管理员身份登录,如果不好使,使用>sqlplus sys/sys as sysdba)

SQL>quit

如果scott用户配置过,也可以>sqlplus scott/11

SQL>quit

 

》数据库如果登录不上,怎么排查?

启动服务:(win+r,然后输入)services.msc(或者:控制面板 → 管理工具  → 服务)
>实例主服务:OracleServiceORCL——d:\workspace\oracle11g\app\administrator\product\11.2.0\dbhome_1\bin\OraVSSW.exe ORCL

注意:Windows系统最好把OracleServiceORCL服务改为手动(这样开机不会占用大量资源),虚拟机不需要改。

>侦听服务:OracleOraDb11g_home1TNSListener主要提供网络服务——D:\WorkSpace\ORACLE11g\app\Administrator\product\11.2.0\dbhome_1\BIN\TNSLSNR

》其实主要看OracleServiceORCL和OracleOraDb11g_home1TNSListener两个服务是否启动,OracleServiceORCL启动,本机可以登录;OracleOraDb11g_home1TNSListener不启动,远程访问不了,本机可以登录。

 

4、登录和基本设置

》本机有服务器端:首先看服务器端是否可以登录

在命令行:>sqlplus scott/11      -- scott 用户,11 密码

》远程登录方式:
测试:>ping  ip (看网络是否连接成功)
          >tnsping ip (看是否秒回信息)
连接命令:>sqlplus scott/11@//192.168.137.100/orcl

另一种登录方式:在tnsnames.ora里配置了连接别名(可以不用输入那么长的命令)

 1 # tnsnames.ora Network Configuration File: D:\WorkSpace\ORACLE11g\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
 2 # Generated by Oracle configuration tools.
 3 
 4 ORCL100 =
 5   (DESCRIPTION =
 6     (ADDRESS_LIST =
 7         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.100)(PORT = 1521))
 8     )
 9     (CONNECT_DATA =
10       (SERVICE_NAME = orcl)
11     )
12   )
13 
14 ORCL =
15   (DESCRIPTION =
16     (ADDRESS_LIST =
17         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.111)(PORT = 1521))
18     )
19     (CONNECT_DATA =
20       (SERVICE_NAME = orcl)
21     )
22   )

如果配置了连接别名orcl100,可以这样登录:(注意:如果多台虚拟机,可配置多个,如:连接winxp,使用orcl100;连接Red Hat使用orcl)

>sqlplus scott/11@orcl

--退出指令:SQL>quit

--查看当前语言环境:SQL> select userenv('language') from dual

》登陆管理员用户: sqlplus sys/sys as sysdba   

sys用户是oracle超级用户,默认在安装的主机上登录可以不输入密码(安装的时候,oracle 的sys用户与当前操作用户进行了绑定)

--(在管理员用户下)修改密码

需要2步:(用户默认锁定)

SQL> alter user scott account unlock;
(会输出:用户已更改。)

SQL> alter user scott identified by 11;
(会输出:用户已更改。)

 

》tab 数据字典:存放的表的类型,名字相关的信息。

SQL>select * from tab;

最重要的是DEPT和EMP表。

》表:行和列的集合,是数据库存储的最基本单位。

 

》scott方案(即用户)

》hr方案(即用户)

 

》SQL指令:

--清屏:SQL>host cls

--查看当前用户:SQL>show user

--查看表结构:SQL>desc 表名(表名不区分大小写)

--设置行宽:set linesize 数值;(如:emp表一行显示不下,/ 执行前一条指令(SQL>select * from emp;))

--设置页宽:set pagesize 数值;(如:emp表一页显示不下,/ 执行前一条指令(SQL>select * from emp;))

--永久性设置生效—glogin.sql:(我的是服务器,为:D:\WorkSpace\ORACLE11g\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql;如果是客户端,为:D:\WorkSpace\ORACLE11g\app\Administrator\product\11.2.0\client_1\sqlplus\admin\glogin.sql)

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set linesize 140;
set pagesize 120;
glogin.sql

 

--数值型字段宽度修改
col empno for 999999;

--字符型字段宽度修改
col ename for a20;

》SQL语句使用注意事项:

•SQL 语言大小写不敏感。
•SQL 可以写在一行或者多行
•关键字不能被缩写也不能分行
•各子句一般要分行写。
•使用缩进提高语句的可读性。

 

5、基本查询


》远程连接linux必要步骤:

linux启动oracle

启动实例:
>sqlplus / as sysdba
SQL>startup

启动侦听:在命令行
>lsnrctl start


写法:注意分号 可以换行写,sql关键字不能缩写,不能换行写!

基本查询 SELECT    *|{[DISTINCT] column | expression [ alias ],...} FROM    table;

结论:查询,可以全部列,部分列,表达式,别名

 

》练习:

--查询员工表信息
SQL>select * from emp;

--查询员工号,姓名,月薪,奖金,年薪
SQL>select empno as "员工编号",ename "姓名",sal 薪水,comm 奖金,sal*16 年薪from emp;
SQL>select empno as "员工编号",ename "姓名",sal 薪水,comm "奖 金",sal*16 年薪from emp;

注意:(1)as可以省略;(2)双引号能不能省略取决于起的别名里有没有空格这些特殊字符。

--select * 与select 列的区别 ?
select列更好?因为实例内部进行转换。

--查询员工号,姓名,月薪,奖金,年薪,年收入       -null 捣蛋鬼

——注意:null的第一个结论,null与任何值运算,结果为null
nvl(a,b) 如果a为null,返回结果为b,如果a不为null,返回a
SQL>select empno as "员工编号",ename "姓名",sal 薪水,comm "奖 金",sal*16 年薪,sal*16+nvl(comm,0) 年收入from emp;

--查看员工表不同的部门编号distinct 去除重复行 select distinct deptno from emp;

--输出计算表达式3+20*5,显示当前日期

SQL>select 3+20*5,sysdate from emp;(显示14行,因为emp有14行)

注意:伪表(dual,数学计算,取系统时间,函数使用)和伪列(sysdate,并不存在某张表,但是可以直接使用)
SQL> select 3+20*5,sysdate from dual;

--sqlplus 与sql命令的区别,哪个是sqlplus的命令,哪个是sql的命令?

sqlplus是oracle提供的客户端执行程序,本身并不会修改数据,只是本地化设置
sql是语言,可以实现对数据库数据的修改

set plus
col host
cls
select sql
insert  
update
delete

可以查看sqlplus的命令:SQL> ? topic 

SQL> ? topic

 Help is available on the following topics:

/
@
@@
ACCEPT
APPEND
ARCHIVE LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INDEX
INPUT
LIST
MENU
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
RECOVER
REMARK
REPFOOTER
REPHEADER
RESERVED WORDS (PL/SQL)
RESERVED WORDS (SQL)
RUN
SAVE
SET
SHOW
SHUTDOWN
SPOOL
SQLPLUS
START
STARTUP
STORE
TIMING
TOPICS
TTITLE
UNDEFINE
VARIABLE
WHENEVER OSERROR
WHENEVER SQLERROR
XQUERY
SQL> ? topic

》SQL
•一种语言
•ANSI 标准
•关键字不能缩写
•使用语句控制数据库中的表的定义信息和表中的数据

》SQL Plus
•一种环境
•Oracle 的特性之一
•关键字可以缩写
•命令不能改变数据库中的数据的值
•集中运行

 

6、条件过滤1-where过滤

》复杂sql的编写方法:

ed(it)打开记事本,编辑sql,sql不带“;”分号
SQL>/ (执行sql)

》where过滤:

○ 比较运算符  > < >= <= != (<>) (between...and...)

--查询10号部门的员工信息
SQL>select * from emp where deptno = 10;

--查询员工名字为king的员工信息

SQL> select * from EMP where ENAME ='KING';

——注意:字符串单引号包含,大小写敏感

--查找薪水不等于1250员工的信息
SQL>select * from emp where sal != 1250;
SQL>select * from emp where sal <> 1250;

--查询入职日期为1981年11月17日的员工信息

——注意:说明日期格式敏感。如何解决?

SQL>select * from v$nls_parameters;

SQL>select sysdate from dual;


SQL> select *from emp where hiredate ='17-11月-81';

——修改会话,日期格式yyyy-mm-dd
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';(会显示会话已更改。)

SQL>select sysdate from dual;

SQL> select *from emp where hiredate ='1981-11-17';

问题:如果别人更改了,自己查询表就不好使了,怎么办?留下一个疑问。

 

7、条件过滤2-逻辑运算符

○逻辑运算符:写多个条件

▪ and  并且
▪ or  或者
▪ not 非

--查询10号部门或者20部门的员工信息
SQL>select * from emp where deptno=10 or deptno=20;

--查询10号部门员工工资为1300的员工信息
SQL>select * from emp where deptno=10 and sal = 1300;

--查找工资介于1000-2000之间的员工信息
SQL>select * from emp where sal >=1000 and sal <= 2000;
SQL>select * from emp where sal between 1000 and 2000;

问题:between...and...从大到小?


SQL>select * from emp where sal between 1100 and 1600;

——注意:between and 闭区间,从小到大

--查询81年2月(含2月)至82年2月(不含2月)入职的员工信息

——分析:大于等于81年2月1日,小于等 于82年1月31日
SQL>select * from emp where hiredate between '1981-02-01' and '1982-01-31';

--查询奖金为空的员工信息-null

注意:不能使用= 或者 != 作为null的查询条件  column =null (永远不成立)
SQL>select * from emp where comm is NULL;
SQL>select * from emp where comm is not NULL;

--多个条件时怎么写更优?
▪ sql 执行的时候,先执行where最右面的条件:

▪ and的时候,将易假的条件写在右侧
▪ or的时候,将易真的条件写在右侧

--下面的语句是什么含义?
SQL>select * from emp where deptno=10 or deptno=30 and sal=1250;


SQL>select * from emp where (deptno=10 or deptno=30) and sal=1250;

——注意:在or的时候,一般 使用小括号

 

8、条件过滤3-模糊匹配

○in:在集合中

--查询部门号是10或者20的员工信息
SQL>select * from emp where deptno = 10 or deptno = 20;
SQL>select * from emp where deptno in (10,20);

--查询不是10和20号部门的员工信息    
——注意:null
SQL>select * from emp where deptno not in (10,20);

——注意:null的又一个结论:不能在not in的集合中使用null
原因分析:not in (10,20,null) == > if(deptno != 10 && deptno != 20 && deptno != null),最后一个 表达式永远不会成立,所以整个表达式为假

○like:模糊查询

知识点:‘%’匹配任意多个字符。‘_’匹配一个字符

--查询员工名字首字母是S的员工信息
SQL>select * from emp where ename like 'S%';

--查询名字四个字母的员工信息
SQL>select * from emp where ename like '____';

--查询名字带有下划线的员工信息
因为名字中没有带下划线的,先增加一条:

SQL>insert into emp(empno,ename,job,sal,deptno) values(1001,' Tom_abc','SALESMAN',2000,10);

--escape指定转义字符 like:模糊查询
SQL>select * from emp where ename like '%\_%' escape '\';
SQL>select * from emp where ename like '%/_%' escape '/';

 

》总结:

select …取出的内容
from …从哪取
where … 有哪些条件

 

9、排序

如何设计排序?
按列? 表达式---按什么排序
如何识别按什么排序?设计关键字order by
按多列排序?排序顺序的问题
升序还是降序?asc 升序(默认),desc 降序

》oracle支持排序:
order by column|expr|alias|number(序号)

--员工信息按入职日期排序
SQL>select * from emp order by hiredate;

--员工薪水按从大到小排序(序号)
SQL>select * from emp order by sal desc;

--员工信息按部门、薪水排列

如果要按照多列进行排序,则规则是先按照第一列排序,如果相同,则按照第二列排序;以此类推。

SQL>select * from emp order by deptno desc,sal desc;

——注意:asc和desc作用的它之前的最近的字段

--员工信息按奖金逆序nulls last

——注意:null

——注意:null的又一个结论:null既不是空,也不是某个数,无穷大,默认排序在最后。
SQL>select * from emp order by comm desc nulls last;
SQL>select * from emp order by nvl(comm,-1) desc ;

》按序号排序

——注意:序号是查询结果集的列的序号并非是表的序号
SQL>select empno,ename,sal from emp order by 3 ;

》按照别名来排序

SQL>select empno,ename,sal sal1 from emp order by sal1 ;

 

--回滚操作
rollback;

 

10、单行函数:

单行函数:只对一行进行变换,产生一个结果。函数可以没有参数,但必须要有返回值。如:concat、nvl    
▪操作数据对象
▪接受参数返回一个结果
▪只对一行进行变换
▪每行返回一个结果
▪可以转换数据类型
▪可以嵌套
▪参数可以是一列或一个值

(1)字符函数

--lower小写,upper 大写,initcap 首字母大写
SQL>select lower('Hello wOrld') 一,upper('Hello wOrld') 二,initcap('Hello wOrld') 三from dual;

》concat(连接符||),substr,length,lengthb,instr:

--concat
SQL> select concat('hello','world') from dual;

——注意:contact连接字符串,只支持2个参数

--||支持拼接多个参数

--substr(str,pos,len)——截取字符串,从pos位置开始,截取len长度,如果len未填写,代表直接到末尾
SQL>select substr('helloworld',1,3) 一,substr('helloworld',1) 二,substr('helloworld',-3) 三from dual;

--instr(str,str2)——判断str2是否在str中存在,如果存在,返回位置,如果不存在返回0

SQL>selectinstr('helloworld','owo') 一,instr('helloworld','owow') 二from dual;

--lpad,rpad——l(r)pad(str,len,ch) 返回len长度的字符串,如果str不够len的话,在左(右)填充ch这 个字符

--trim

SQL>select 'aaa'||trim('  hello world  ')||'bbb' from dual;——去首尾空格

SQL>select trim('H' from 'HHHHHelloHHHworldHHHHH') from dual;——trim(C from str) 去掉str首尾为C的字符

--replace

SQL>select replace('helloworld','llo','kk') from dual;

SQL>select replace('helloworld','llo','') from dual;

 

(2)数值函数

--round四舍五入 trunc截断 mod取模
SQL>select round(45.926, 2) 一, round(45.926, 1) 二, round(45.926, 0) 三,  round(45.926, -1) 四, round(45.926, -2) 五from dual;

SQL>select trunc(45.926, 2) 一, trunc(45.926, 1) 二, trunc(45.926, 0) 三,  trunc(45.926, -1) 四, trunc(45.926, -2) 五 from dual;

SQL>select mod(1000,600), mod(600, 1000) from dual;

--ceil函数-向上取整,floor函数-向下取整

SQL> select ceil(181/60),floor(181/60) from dual;

 

(3)转换函数(重要)

在不同的数据类型之间完成转换。将“123” 转换为 123。有隐式转换和显示转换之分。                

》隐式转换:

隐式转换,前提条件是:被转换的对象是可以转换的。

SQL> select * from emp where hiredate = '17-11月-81'; 由Oracle数据库来做

》显示转换:
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;通过转换函数来完成。
——借助to_char(数据,格式)、to_number、to_date函数来完成转换

--to_char,to_numer,to_date

如果隐式转换和显示转换都可以使用,应该首选哪个呢?

       ※SQL优化:如果隐式、显示都可以使用,应该首选显式,这样可以省去Oracle的解析过程。

》to_char 与to_number互相转换

--把薪水转换为本地货币字符型


SQL>select empno,sal,to_char(sal,'L9,999') from emp;

--把上述某个结果转回数值型
SQL>select to_number('¥2,975','L9,999') from dual;

》to_char 与to_date

--显示"yyyy-mm-dd hh24:mi:ss 今天是 星期几"
SQL>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;

--将上述输出字符串反转回日期
SQL>select to_date('2017-06-16 16:10:20 今天是星期五','yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;

 

》上午遗留的问题:


SQL>select * from emp where hiredate = to_date('1981-11-17','yyyy-mm-dd');
SQL>select * from emp where to_char(hiredate,'yyyy-mm-dd') = '1981-11-17';

 

》隐式转换的问题:当你没有明确转换函数的时候,系统如果判断类型不一致,会进行隐式转换,隐式有一 个前提,它必须能转换。尽量避免隐式转换。

(4)日期函数

--显示昨天,今天,明天——oracle日期型+1代表加一天
SQL> select sysdate-1 昨天,sysdate 今天,sysdate+1 明天from dual;

--计算员工工龄 可以按日,周,月,年 日期差减方法

注意:日期和日期可以相减,但是不允许相加。  日期只能和数字相加!
SQL> select empno,ename,sysdate-hiredate 日,(sysdate-hiredate)/7 周,(sysdate-hiredate)/30 月,(sysdatehiredate)/365 年from emp;

上述结果不够准确,计算月份和年的时候

--日期函数months_between、add_months、last_day、next_day

--months_between——计算月份差 精确值
SQL>select months_between(sysdate,to_date('2017-02-17','yyyy-mm-dd')) from dual;

——这样更准确
SQL>select empno,ename,months_between(sysdate,hiredate),(sysdate-hiredate)/30 月from emp;

--add_months——增加月份
SQL>select add_months(sysdate,2) from dual;

--求明年的今天?
SQL>select add_months(sysdate,12) from dual;

--last_day——最后一天,指定日期所在月份的最后一天

SQL>select last_day(sysdate) from dual;

--next_day——求指定日期的下一个星期几

SQL>select next_day(sysdate,'星期一') from dual;

 

11、条件表达式

--老板打算给涨工资,总裁1000,经理800,普通员工400,将涨前、涨后薪水列出

》C语言格式:
if(job == 'PRESIDENT') sal+1000; else if(job =='MANAGER') sal+800; else sal+400;
语法:
CASE expr  WHEN comparison_expr1 THEN return_expr1
            [WHEN comparison_expr2 THEN return_expr2
            WHEN comparison_exprn THEN return_exprn
            ELSE else_expr ]
END

》sql99标准:
SQL>select empno,ename,sal 涨前薪水,case job when 'PRESIDENT' then sal+1000
                                         when 'MANAGER' then sal+800
                                         else sal+400
                                end  涨后薪水  from emp;

》oracle特有的decode函数:

decode(expr|column,search1,res1,search2,res2,…,defalut)
SQL>select empno,ename,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,'MANAGER' ,sal+800,sal+400)  涨后薪水  from emp;

 

12、多行处理

(1)分组函数

分组函数(组函数,聚合函数),对多行进行处理。
oracle提供的分组函
数: avg,sum,max,min,count
》来源需求:统计平均工资?


--求员工的工资总额
SQL> select sum(sal) from emp;

--员工人数
SQL> select count(empno),count(*) from emp;

--求emp表中的工种数
SQL> select count(distinct job) from emp;

--求员工的平均工资(两种方式)
SQL> select avg(sal),sum(sal)/count(*) from emp;

--求员工的平均奖金(三种方式)
SQL> select avg(comm),sum(comm)/count(*),sum(comm)/count(comm) from emp;

分析:上述结果不一致的原因是分母不一样导致
究竟哪个对?具体看需求。

》null结论:组函数自动过滤null

 

(2)分组数据

》来源需求:统计各个部门的平均工资?

分组数据语法设计:
关键字group by
按照什么分组?
数据如何显示?

SQL> select deptno,avg(sal) from emp group by deptno;

》语法总结:
select a,avg(X) from tablename group by a;===> 一列

select a,b,avg(X) from tablename group by a,b;==> 两列

select a,b,c,avg(X) from tablename group by a,b,c;=>三列

结论:在SELECT 列表中所有没有包含在组函数中的列,都必须在group by的后面出现。所以上面应该写成group by a, b;没有b语法就会出错,不会执行SQL语句。但,反之可以。Group by a,b,c;  c可以不出现在select语句中。 

--查询各部门各工种平均工资
SQL>select deptno,job,avg(sal) from emp group by deptno,job;

--查询平均薪水大于2000的部门

——注意:where字句后不能使用分组函数。

可以使用having对分组数据进行过滤

SQL>select deptno,avg(SAL) from emp  group by deptno having avg(sal) > 2000;

--求10号部门员工的平均薪水


SQL>select avg(sal),deptno from emp group by deptno having deptno=10;
SQL>select avg(sal),deptno from emp where deptno=10 group by deptno ;

上述结果2个好?使用where的好,当where和having都可以使用的时 候,优先使用where


--常见的非法使用组函数的情况,主要出现在缺少group by 子句。如hr用户下执行查询语句:

SELECT department_id, COUNT(last_name) FROM  employees;

会显示如下错误:

SELECT department_id, COUNT(last_name)

       *

ERROR at line 1:

ORA-00937: not a single-group group function

意为:GROUP BY 子句中缺少列

记住结论:在select中出现的非组函数的列必须在group by中出现


 

13、在redhat创建hr数据库用户

》使用PL/SQL Developer工具,登录管理员,登录成功后,创建hr用户

注意:只能远程登录Red Hat虚拟机需要配置!(本机Windows,远程Win XP不需要配置!!!)

1)登录管理员选项,截图:

2)登录成功截图:

3)选择command窗口

可以查看数据:

SQL> select * from scott.emp;

然后执行redhat_add_hr(登陆管理员,客户端执行).sql 语句:(clear清屏后;Ctrl+a;Ctrl+c;Ctrl+v)

-- 01 创建表空间
-- 注意表空间的路径 根据实际安装环境进行调整
drop user hr cascade;
DROP TABLESPACE ts_hr INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE ts_hr2 INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE ts_hr 
    LOGGING 
    DATAFILE '/home/oracle_11/app/oradata/orcl/ts_hr.dbf' SIZE 10M 
    EXTENT MANAGEMENT LOCAL;
    
CREATE TABLESPACE ts_hr2 
    LOGGING 
    DATAFILE '/home/oracle_11/app/oradata/orcl/ts_hr2.dbf' SIZE 20M
    EXTENT MANAGEMENT LOCAL;
    
ALTER DATABASE DATAFILE '/home/oracle_11/app/oradata/orcl/ts_hr.dbf' AUTOEXTEND ON NEXT   10M MAXSIZE UNLIMITED;
ALTER DATABASE DATAFILE '/home/oracle_11/app/oradata/orcl/ts_hr2.dbf' AUTOEXTEND ON NEXT  20M MAXSIZE UNLIMITED; 


-- 02 创建方案 (创建用户)


CREATE USER hr  PROFILE DEFAULT 
    IDENTIFIED BY hr DEFAULT TABLESPACE USERS 
    ACCOUNT UNLOCK;

-- 资源和登录权限
GRANT RESOURCE TO hr;
GRANT create session TO hr;


-- 03 创建表

create table hr.REGIONS
(
  region_id   NUMBER,
  region_name VARCHAR2(25)
) tablespace ts_hr;

alter table hr.REGIONS  add constraint REG_ID_PK primary key (REGION_ID);

--hr.COUNTRIES
create table hr.COUNTRIES
(
  country_id   CHAR(2),
  country_name VARCHAR2(40),
  region_id    NUMBER,
  constraint COUNTRY_C_ID_PK primary key (COUNTRY_ID)
)
organization index;
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.';
alter table hr.COUNTRIES
  add constraint COUNTR_REG_FK foreign key (REGION_ID)
  references hr.REGIONS (REGION_ID);
alter table hr.COUNTRIES
  add constraint COUNTRY_ID_NN
  check ("COUNTRY_ID" IS NOT NULL);


--jobs
create table hr.JOBS
(
  job_id     VARCHAR2(10) not null,
  job_title  VARCHAR2(35),
  min_salary NUMBER(6),
  max_salary NUMBER(6)
) tablespace ts_hr;

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';
alter table hr.JOBS
  add constraint JOB_ID_PK primary key (JOB_ID)
  using index 
  tablespace ts_hr;
  
--hr.DEPARTMENTS
create table hr.DEPARTMENTS
(
  department_id   NUMBER(4) not null,
  department_name VARCHAR2(30),
  manager_id      NUMBER(6),
  location_id     NUMBER(4)
)
tablespace ts_hr;

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.';
create index DEPT_LOCATION_IX on hr.DEPARTMENTS (LOCATION_ID)
  tablespace ts_hr;
  
alter table hr.DEPARTMENTS
  add constraint DEPT_ID_PK primary key (DEPARTMENT_ID)
  using index 
  tablespace ts_hr;



alter table hr.DEPARTMENTS
  add constraint DEPT_NAME_NN
  check ("DEPARTMENT_NAME" IS NOT NULL);


create table hr.EMPLOYEES
(
  employee_id    NUMBER(6) not null,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  email          VARCHAR2(25),
  phone_number   VARCHAR2(20),
  hire_date      DATE,
  job_id         VARCHAR2(10),
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4)
)
tablespace ts_hr;

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';
create index EMP_DEPARTMENT_IX on hr.EMPLOYEES (DEPARTMENT_ID);

create index EMP_JOB_IX on hr.EMPLOYEES (JOB_ID);
create index EMP_MANAGER_IX on hr.EMPLOYEES (MANAGER_ID);

create index EMP_NAME_IX on hr.EMPLOYEES (LAST_NAME, FIRST_NAME);

alter table hr.EMPLOYEES
  add constraint EMP_EMP_ID_PK primary key (EMPLOYEE_ID);

alter table hr.EMPLOYEES
  add constraint EMP_DEPT_FK foreign key (DEPARTMENT_ID)
  references hr.DEPARTMENTS (DEPARTMENT_ID);
alter table hr.EMPLOYEES
  add constraint EMP_JOB_FK foreign key (JOB_ID)
  references hr.JOBS (JOB_ID);
--alter table hr.EMPLOYEES
--  add constraint EMP_MANAGER_FK foreign key (MANAGER_ID)
--  references hr.EMPLOYEES (EMPLOYEE_ID);
alter table hr.EMPLOYEES
  add constraint EMP_EMAIL_NN
  check ("EMAIL" IS NOT NULL);
alter table hr.EMPLOYEES
  add constraint EMP_HIRE_DATE_NN
  check ("HIRE_DATE" IS NOT NULL);
alter table hr.EMPLOYEES
  add constraint EMP_JOB_NN
  check ("JOB_ID" IS NOT NULL);
alter table hr.EMPLOYEES
  add constraint EMP_LAST_NAME_NN
  check ("LAST_NAME" IS NOT NULL);
alter table hr.EMPLOYEES
  add constraint EMP_SALARY_MIN
  check (salary > 0);



--hr.LOCATIONS 
create table hr.LOCATIONS
(
  location_id    NUMBER(4) not null,
  street_address VARCHAR2(40),
  postal_code    VARCHAR2(12),
  city           VARCHAR2(30),
  state_province VARCHAR2(25),
  country_id     CHAR(2)
)
tablespace ts_hr;

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.';
create index LOC_CITY_IX on hr.LOCATIONS (CITY);


create index LOC_COUNTRY_IX on hr.LOCATIONS (COUNTRY_ID);


create index LOC_STATE_PROVINCE_IX on hr.LOCATIONS (STATE_PROVINCE);

alter table hr.LOCATIONS
  add constraint LOC_ID_PK primary key (LOCATION_ID);
  
alter table hr.LOCATIONS
  add constraint LOC_C_ID_FK foreign key (COUNTRY_ID)
  references hr.COUNTRIES (COUNTRY_ID);
alter table hr.LOCATIONS
  add constraint LOC_CITY_NN
  check ("CITY" IS NOT NULL);


create table hr.JOB_HISTORY
(
  employee_id   NUMBER(6),
  start_date    DATE,
  end_date      DATE,
  job_id        VARCHAR2(10),
  department_id NUMBER(4)
)
tablespace ts_hr;


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';
create index JHIST_DEPARTMENT_IX on hr.JOB_HISTORY (DEPARTMENT_ID);

create index JHIST_EMPLOYEE_IX on hr.JOB_HISTORY (EMPLOYEE_ID);
create index JHIST_JOB_IX on hr.JOB_HISTORY (JOB_ID);

alter table hr.JOB_HISTORY
  add constraint JHIST_EMP_ID_ST_DATE_PK primary key (EMPLOYEE_ID, START_DATE);



alter table hr.JOB_HISTORY
  add constraint JHIST_DEPT_FK foreign key (DEPARTMENT_ID)
  references hr.DEPARTMENTS (DEPARTMENT_ID);
alter table hr.JOB_HISTORY
  add constraint JHIST_EMP_FK foreign key (EMPLOYEE_ID)
  references hr.EMPLOYEES (EMPLOYEE_ID);
alter table hr.JOB_HISTORY
  add constraint JHIST_JOB_FK foreign key (JOB_ID)
  references hr.JOBS (JOB_ID);
alter table hr.JOB_HISTORY
  add constraint JHIST_DATE_INTERVAL
  check (end_date > start_date);
alter table hr.JOB_HISTORY
  add constraint JHIST_EMPLOYEE_NN
  check ("EMPLOYEE_ID" IS NOT NULL);
alter table hr.JOB_HISTORY
  add constraint JHIST_END_DATE_NN
  check ("END_DATE" IS NOT NULL);
alter table hr.JOB_HISTORY
  add constraint JHIST_JOB_NN
  check ("JOB_ID" IS NOT NULL);
alter table hr.JOB_HISTORY
  add constraint JHIST_START_DATE_NN
  check ("START_DATE" IS NOT NULL);



prompt Loading hr.REGIONS...
insert into hr.REGIONS (region_id, region_name)
values (1, 'Europe');
insert into hr.REGIONS (region_id, region_name)
values (2, 'Americas');
insert into hr.REGIONS (region_id, region_name)
values (3, 'Asia');
insert into hr.REGIONS (region_id, region_name)
values (4, 'Middle East and Africa');
commit;


prompt Loading hr.COUNTRIES...
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('AR', 'Argentina', 2);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('AU', 'Australia', 3);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('BE', 'Belgium', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('BR', 'Brazil', 2);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('CA', 'Canada', 2);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('CH', 'Switzerland', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('CN', 'China', 3);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('DE', 'Germany', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('DK', 'Denmark', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('EG', 'Egypt', 4);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('FR', 'France', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('IL', 'Israel', 4);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('IN', 'India', 3);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('IT', 'Italy', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('JP', 'Japan', 3);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('KW', 'Kuwait', 4);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('ML', 'Malaysia', 3);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('MX', 'Mexico', 2);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('NG', 'Nigeria', 4);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('NL', 'Netherlands', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('SG', 'Singapore', 3);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('UK', 'United Kingdom', 1);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('US', 'United States of America', 2);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('ZM', 'Zambia', 4);
insert into hr.COUNTRIES (country_id, country_name, region_id)
values ('ZW', 'Zimbabwe', 4);
commit;
prompt 25 records loaded
prompt Loading hr.JOBS...
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('AD_PRES', 'President', 20080, 40000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('AD_VP', 'Administration Vice President', 15000, 30000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('AD_ASST', 'Administration Assistant', 3000, 6000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('FI_MGR', 'Finance Manager', 8200, 16000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('FI_ACCOUNT', 'Accountant', 4200, 9000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('AC_MGR', 'Accounting Manager', 8200, 16000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('SA_MAN', 'Sales Manager', 10000, 20080);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('SA_REP', 'Sales Representative', 6000, 12008);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('PU_MAN', 'Purchasing Manager', 8000, 15000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('ST_MAN', 'Stock Manager', 5500, 8500);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('ST_CLERK', 'Stock Clerk', 2008, 5000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('IT_PROG', 'Programmer', 4000, 10000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('MK_MAN', 'Marketing Manager', 9000, 15000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('MK_REP', 'Marketing Representative', 4000, 9000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('HR_REP', 'Human Resources Representative', 4000, 9000);
insert into hr.JOBS (job_id, job_title, min_salary, max_salary)
values ('PR_REP', 'Public Relations Representative', 4500, 10500);
commit;
prompt 19 records loaded
prompt Loading hr.DEPARTMENTS...
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (10, 'Administration', 200, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (20, 'Marketing', 201, 1800);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (30, 'Purchasing', 114, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (40, 'Human Resources', 203, 2400);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (50, 'Shipping', 121, 1500);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (60, 'IT', 103, 1400);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (70, 'Public Relations', 204, 2700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (80, 'Sales', 145, 2500);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (90, 'Executive', 100, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (100, 'Finance', 108, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (110, 'Accounting', 205, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (120, 'Treasury', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (130, 'Corporate Tax', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (140, 'Control And Credit', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (150, 'Shareholder Services', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (160, 'Benefits', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (170, 'Manufacturing', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (180, 'Construction', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (190, 'Contracting', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (200, 'Operations', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (210, 'IT Support', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (220, 'NOC', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (230, 'IT Helpdesk', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (240, 'Government Sales', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (250, 'Retail Sales', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (260, 'Recruiting', null, 1700);
insert into hr.DEPARTMENTS (department_id, department_name, manager_id, location_id)
values (270, 'Payroll', null, 1700);
commit;
prompt 27 records loaded
prompt Loading hr.EMPLOYEES...
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-2007', 'dd-mm-yyyy'), 'SH_CLERK', 2600, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2008', 'dd-mm-yyyy'), 'SH_CLERK', 2600, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-2003', 'dd-mm-yyyy'), 'AD_ASST', 4400, null, 101, 10);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', to_date('17-02-2004', 'dd-mm-yyyy'), 'MK_MAN', 13000, null, 100, 20);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', to_date('17-08-2005', 'dd-mm-yyyy'), 'MK_REP', 6000, null, 201, 20);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', to_date('07-06-2002', 'dd-mm-yyyy'), 'HR_REP', 6500, null, 101, 40);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', to_date('07-06-2002', 'dd-mm-yyyy'), 'PR_REP', 10000, null, 101, 70);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', to_date('07-06-2002', 'dd-mm-yyyy'), 'AC_MGR', 12008, null, 101, 110);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', to_date('07-06-2002', 'dd-mm-yyyy'), 'AC_ACCOUNT', 8300, null, 205, 110);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'Steven', 'King', 'SKING', '515.123.4567', to_date('17-06-2003', 'dd-mm-yyyy'), 'AD_PRES', 24000, null, null, 90);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', to_date('21-09-2005', 'dd-mm-yyyy'), 'AD_VP', 17000, null, 100, 90);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', to_date('13-01-2001', 'dd-mm-yyyy'), 'AD_VP', 17000, null, 100, 90);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', to_date('03-01-2006', 'dd-mm-yyyy'), 'IT_PROG', 9000, null, 102, 60);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', to_date('21-05-2007', 'dd-mm-yyyy'), 'IT_PROG', 6000, null, 103, 60);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', to_date('25-06-2005', 'dd-mm-yyyy'), 'IT_PROG', 4800, null, 103, 60);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', to_date('05-02-2006', 'dd-mm-yyyy'), 'IT_PROG', 4800, null, 103, 60);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', to_date('07-02-2007', 'dd-mm-yyyy'), 'IT_PROG', 4200, null, 103, 60);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', to_date('17-08-2002', 'dd-mm-yyyy'), 'FI_MGR', 12008, null, 101, 100);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', to_date('16-08-2002', 'dd-mm-yyyy'), 'FI_ACCOUNT', 9000, null, 108, 100);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (110, 'John', 'Chen', 'JCHEN', '515.124.4269', to_date('28-09-2005', 'dd-mm-yyyy'), 'FI_ACCOUNT', 8200, null, 108, 100);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', to_date('30-09-2005', 'dd-mm-yyyy'), 'FI_ACCOUNT', 7700, null, 108, 100);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', to_date('07-03-2006', 'dd-mm-yyyy'), 'FI_ACCOUNT', 7800, null, 108, 100);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', to_date('07-12-2007', 'dd-mm-yyyy'), 'FI_ACCOUNT', 6900, null, 108, 100);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', to_date('07-12-2002', 'dd-mm-yyyy'), 'PU_MAN', 11000, null, 100, 30);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', to_date('18-05-2003', 'dd-mm-yyyy'), 'PU_CLERK', 3100, null, 114, 30);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', to_date('24-12-2005', 'dd-mm-yyyy'), 'PU_CLERK', 2900, null, 114, 30);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', to_date('24-07-2005', 'dd-mm-yyyy'), 'PU_CLERK', 2800, null, 114, 30);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', to_date('15-11-2006', 'dd-mm-yyyy'), 'PU_CLERK', 2600, null, 114, 30);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', to_date('10-08-2007', 'dd-mm-yyyy'), 'PU_CLERK', 2500, null, 114, 30);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', to_date('18-07-2004', 'dd-mm-yyyy'), 'ST_MAN', 8000, null, 100, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', to_date('10-04-2005', 'dd-mm-yyyy'), 'ST_MAN', 8200, null, 100, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', to_date('01-05-2003', 'dd-mm-yyyy'), 'ST_MAN', 7900, null, 100, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', to_date('10-10-2005', 'dd-mm-yyyy'), 'ST_MAN', 6500, null, 100, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', to_date('16-11-2007', 'dd-mm-yyyy'), 'ST_MAN', 5800, null, 100, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', to_date('16-07-2005', 'dd-mm-yyyy'), 'ST_CLERK', 3200, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', to_date('28-09-2006', 'dd-mm-yyyy'), 'ST_CLERK', 2700, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', to_date('14-01-2007', 'dd-mm-yyyy'), 'ST_CLERK', 2400, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', to_date('08-03-2008', 'dd-mm-yyyy'), 'ST_CLERK', 2200, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', to_date('20-08-2005', 'dd-mm-yyyy'), 'ST_CLERK', 3300, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', to_date('30-10-2005', 'dd-mm-yyyy'), 'ST_CLERK', 2800, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', to_date('16-02-2005', 'dd-mm-yyyy'), 'ST_CLERK', 2500, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', to_date('10-04-2007', 'dd-mm-yyyy'), 'ST_CLERK', 2100, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', to_date('14-06-2004', 'dd-mm-yyyy'), 'ST_CLERK', 3300, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', to_date('26-08-2006', 'dd-mm-yyyy'), 'ST_CLERK', 2900, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', to_date('12-12-2007', 'dd-mm-yyyy'), 'ST_CLERK', 2400, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', to_date('06-02-2008', 'dd-mm-yyyy'), 'ST_CLERK', 2200, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', to_date('14-07-2003', 'dd-mm-yyyy'), 'ST_CLERK', 3600, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', to_date('26-10-2005', 'dd-mm-yyyy'), 'ST_CLERK', 3200, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (139, 'John', 'Seo', 'JSEO', '650.121.2019', to_date('12-02-2006', 'dd-mm-yyyy'), 'ST_CLERK', 2700, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', to_date('06-04-2006', 'dd-mm-yyyy'), 'ST_CLERK', 2500, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', to_date('17-10-2003', 'dd-mm-yyyy'), 'ST_CLERK', 3500, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', to_date('29-01-2005', 'dd-mm-yyyy'), 'ST_CLERK', 3100, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', to_date('15-03-2006', 'dd-mm-yyyy'), 'ST_CLERK', 2600, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', to_date('09-07-2006', 'dd-mm-yyyy'), 'ST_CLERK', 2500, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', to_date('01-10-2004', 'dd-mm-yyyy'), 'SA_MAN', 14000, .4, 100, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', to_date('05-01-2005', 'dd-mm-yyyy'), 'SA_MAN', 13500, .3, 100, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', to_date('10-03-2005', 'dd-mm-yyyy'), 'SA_MAN', 12000, .3, 100, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', to_date('15-10-2007', 'dd-mm-yyyy'), 'SA_MAN', 11000, .3, 100, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', to_date('29-01-2008', 'dd-mm-yyyy'), 'SA_MAN', 10500, .2, 100, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', to_date('30-01-2005', 'dd-mm-yyyy'), 'SA_REP', 10000, .3, 145, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', to_date('24-03-2005', 'dd-mm-yyyy'), 'SA_REP', 9500, .25, 145, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', to_date('20-08-2005', 'dd-mm-yyyy'), 'SA_REP', 9000, .25, 145, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', to_date('30-03-2006', 'dd-mm-yyyy'), 'SA_REP', 8000, .2, 145, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', to_date('09-12-2006', 'dd-mm-yyyy'), 'SA_REP', 7500, .2, 145, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', to_date('23-11-2007', 'dd-mm-yyyy'), 'SA_REP', 7000, .15, 145, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (156, 'Janette', 'King', 'JKING', '011.44.1345.429268', to_date('30-01-2004', 'dd-mm-yyyy'), 'SA_REP', 10000, .35, 146, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', to_date('04-03-2004', 'dd-mm-yyyy'), 'SA_REP', 9500, .35, 146, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', to_date('01-08-2004', 'dd-mm-yyyy'), 'SA_REP', 9000, .35, 146, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', to_date('10-03-2005', 'dd-mm-yyyy'), 'SA_REP', 8000, .3, 146, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', to_date('15-12-2005', 'dd-mm-yyyy'), 'SA_REP', 7500, .3, 146, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', to_date('03-11-2006', 'dd-mm-yyyy'), 'SA_REP', 7000, .25, 146, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', to_date('11-11-2005', 'dd-mm-yyyy'), 'SA_REP', 10500, .25, 147, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', to_date('19-03-2007', 'dd-mm-yyyy'), 'SA_REP', 9500, .15, 147, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', to_date('24-01-2008', 'dd-mm-yyyy'), 'SA_REP', 7200, .1, 147, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', to_date('23-02-2008', 'dd-mm-yyyy'), 'SA_REP', 6800, .1, 147, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', to_date('24-03-2008', 'dd-mm-yyyy'), 'SA_REP', 6400, .1, 147, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', to_date('21-04-2008', 'dd-mm-yyyy'), 'SA_REP', 6200, .1, 147, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', to_date('11-03-2005', 'dd-mm-yyyy'), 'SA_REP', 11500, .25, 148, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', to_date('23-03-2006', 'dd-mm-yyyy'), 'SA_REP', 10000, .2, 148, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', to_date('24-01-2006', 'dd-mm-yyyy'), 'SA_REP', 9600, .2, 148, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', to_date('23-02-2007', 'dd-mm-yyyy'), 'SA_REP', 7400, .15, 148, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', to_date('24-03-2007', 'dd-mm-yyyy'), 'SA_REP', 7300, .15, 148, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', to_date('21-04-2008', 'dd-mm-yyyy'), 'SA_REP', 6100, .1, 148, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', to_date('11-05-2004', 'dd-mm-yyyy'), 'SA_REP', 11000, .3, 149, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', to_date('19-03-2005', 'dd-mm-yyyy'), 'SA_REP', 8800, .25, 149, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', to_date('24-03-2006', 'dd-mm-yyyy'), 'SA_REP', 8600, .2, 149, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', to_date('23-04-2006', 'dd-mm-yyyy'), 'SA_REP', 8400, .2, 149, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', to_date('24-05-2007', 'dd-mm-yyyy'), 'SA_REP', 7000, .15, 149, null);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', to_date('04-01-2008', 'dd-mm-yyyy'), 'SA_REP', 6200, .1, 149, 80);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', to_date('24-01-2006', 'dd-mm-yyyy'), 'SH_CLERK', 3200, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', to_date('23-02-2006', 'dd-mm-yyyy'), 'SH_CLERK', 3100, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', to_date('21-06-2007', 'dd-mm-yyyy'), 'SH_CLERK', 2500, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', to_date('03-02-2008', 'dd-mm-yyyy'), 'SH_CLERK', 2800, null, 120, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', to_date('27-01-2004', 'dd-mm-yyyy'), 'SH_CLERK', 4200, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', to_date('20-02-2005', 'dd-mm-yyyy'), 'SH_CLERK', 4100, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', to_date('24-06-2006', 'dd-mm-yyyy'), 'SH_CLERK', 3400, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', to_date('07-02-2007', 'dd-mm-yyyy'), 'SH_CLERK', 3000, null, 121, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', to_date('14-06-2005', 'dd-mm-yyyy'), 'SH_CLERK', 3800, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', to_date('13-08-2005', 'dd-mm-yyyy'), 'SH_CLERK', 3600, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', to_date('11-07-2006', 'dd-mm-yyyy'), 'SH_CLERK', 2900, null, 122, 50);
commit;
prompt 100 records committed...
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', to_date('19-12-2007', 'dd-mm-yyyy'), 'SH_CLERK', 2500, null, 122, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', to_date('04-02-2004', 'dd-mm-yyyy'), 'SH_CLERK', 4000, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', to_date('03-03-2005', 'dd-mm-yyyy'), 'SH_CLERK', 3900, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', to_date('01-07-2006', 'dd-mm-yyyy'), 'SH_CLERK', 3200, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', to_date('17-03-2007', 'dd-mm-yyyy'), 'SH_CLERK', 2800, null, 123, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', to_date('24-04-2006', 'dd-mm-yyyy'), 'SH_CLERK', 3100, null, 124, 50);
insert into hr.EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', to_date('23-05-2006', 'dd-mm-yyyy'), 'SH_CLERK', 3000, null, 124, 50);
commit;
prompt 107 records loaded
prompt Loading hr.LOCATIONS...
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1000, '1297 Via Cola di Rie', '00989', 'Roma', null, 'IT');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1100, '93091 Calle della Testa', '10934', 'Venice', null, 'IT');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', null, 'JP');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2000, '40-5-12 Laogianggen', '190518', 'Beijing', null, 'CN');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2300, '198 Clementi North', '540198', 'Singapore', null, 'SG');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2400, '8204 Arthur St', null, 'London', null, 'UK');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
insert into hr.LOCATIONS (location_id, street_address, postal_code, city, state_province, country_id)
values (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
commit;
prompt 23 records loaded
prompt Loading hr.JOB_HISTORY...
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (102, to_date('13-01-2001', 'dd-mm-yyyy'), to_date('24-07-2006', 'dd-mm-yyyy'), 'IT_PROG', 60);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (101, to_date('21-09-1997', 'dd-mm-yyyy'), to_date('27-10-2001', 'dd-mm-yyyy'), 'AC_ACCOUNT', 110);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (101, to_date('28-10-2001', 'dd-mm-yyyy'), to_date('15-03-2005', 'dd-mm-yyyy'), 'AC_MGR', 110);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (201, to_date('17-02-2004', 'dd-mm-yyyy'), to_date('19-12-2007', 'dd-mm-yyyy'), 'MK_REP', 20);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (114, to_date('24-03-2006', 'dd-mm-yyyy'), to_date('31-12-2007', 'dd-mm-yyyy'), 'ST_CLERK', 50);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (122, to_date('01-01-2007', 'dd-mm-yyyy'), to_date('31-12-2007', 'dd-mm-yyyy'), 'ST_CLERK', 50);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (200, to_date('17-09-1995', 'dd-mm-yyyy'), to_date('17-06-2001', 'dd-mm-yyyy'), 'AD_ASST', 90);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (176, to_date('24-03-2006', 'dd-mm-yyyy'), to_date('31-12-2006', 'dd-mm-yyyy'), 'SA_REP', 80);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (176, to_date('01-01-2007', 'dd-mm-yyyy'), to_date('31-12-2007', 'dd-mm-yyyy'), 'SA_MAN', 80);
insert into hr.JOB_HISTORY (employee_id, start_date, end_date, job_id, department_id)
values (200, to_date('01-07-2002', 'dd-mm-yyyy'), to_date('31-12-2006', 'dd-mm-yyyy'), 'AC_ACCOUNT', 90);
commit;
redhat_add_hr(登陆管理员,客户端执行).sql

如下图,添加hr用户成功。

 

4)本机Win 10可以远程登陆hr用户

打开命令窗口,然后执行>sqlplus hr/hr@//192.168.137.111/orcl
SQL>select * from tab;

 

在学习数据库编程总结了笔记,并分享出来。有问题请及时联系博主:Alliswell_WP,转载请注明出处。

posted on 2020-07-16 17:50  Alliswell_WP  阅读(360)  评论(0编辑  收藏  举报

导航