ORACLE数据库练习

ORACLE数据库练习

创建新的用户

CREATE USER oracle_train identified by oracle_train;
grant connect to oracle_train;
grant dba to oracle_train;
grant create any materialized view to oracle_train;
grant create any synonym to oracle_train;
grant create any table to oracle_train;
grant create any view to oracle_train;
grant create job to oracle_train;
grant select any dictionary to oracle_train;
grant unlimited tablespace to oracle_train;

–单行函数查询
SELECT LAST_NAME,salary
from EMPLOYEES
where lower(LAST_NAME)=‘king’;
–concat dual为oracle数据库的虚表
select CONCAT(FIRST_NAME,LAST_NAME)
from EMPLOYEES;
–SUBSTR(ch, pos, length 位置从1开始，length不写则为截取到最后
SELECT SUBSTR(‘helloworld’,1) from dual;
–length函数
SELECT LENGTH(‘helloworld’) from dual;
–INSTR(str, substr) str 中第一个出现strsub字符串的索引，从1开始，无则返回0
select LAST_NAME,INSTR(LAST_NAME, ‘a’) “contains a ?”
from EMPLOYEES;
–LPAD(expr1, n, expr2) 对齐函数
select LPAD(SALARY, 10, ‘*’)
from EMPLOYEES;
–字符串拼接
SELECT ‘$’ ||RPAD(SALARY, 7, ’ ‘)
from EMPLOYEES;
–trim 函数的使用 去掉首位指定的字符
select trim(‘h’ from ‘hellowhordh’) from dual;
select trim(’ hello ') from dual;
–数字操作函数 round 自动进行四舍五入
–trunc 直接舍弃 mod 是取余函数
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL;
SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = ‘SA_REP’;
select MOD(14,3) from dual;
–日期函数 MONTHS_BETWEEN(date1, date2)
select ADD_MONTHS (TO_DATE(‘2018-01-02’,‘yyyy/mm/dd’),6) from dual;
–对于Oracle中的next_day(date,char)的理解，它是指指定日期date之后的下一个周char指定天的日期，char可以表示为星期或者天数，星期从星期日开始算 1表示星期日
SELECT NEXT_DAY(SYSDATE,5) from dual;
SELECT LAST_DAY(SYSDATE) FROM DUAL; --查看当月的最后一天
SELECT ROUND(TO_DATE(‘2018-01-16’,‘yyyy/mm/dd’),‘MONTH’) from dual; --大于15进入下一个月，小于则变为1号
SELECT ROUND(TO_DATE(‘2018-07-16’,‘yyyy/mm/dd’),‘YEAR’) from dual;
SELECT TRUNC(TO_DATE(‘2018-01-16’,‘yyyy/mm/dd’),‘MONTH’) from dual; --trunc直接舍弃

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS, sysdate+1 as tomorrow , hire_date + 8/24 FROM employees WHERE department_id = 90;

select TO_CHAR(SYSDATE,‘HH24:MI:SS AM’) from dual; --16:55:08 下午
–TO_CHAR() 函数：数字到字符串的转换
select TO_CHAR(salary,‘L99,999.00’) from EMPLOYEES; --￥24,000.00
select TO_NUMBER(’$4,456,455.000’,’$9,999,999.999’ )from dual; --给出对应的格式
– SELECT last_name, TO_CHAR(hire_date, ‘DD-MON-YYYY’) FROM employees WHERE hire_date < TO_DATE(‘01-Jan-90’, ‘DD-Mon-RR’);

–NVL函数 第一个为空，就返回第二个
select LAST_NAME,NVL(MANAGER_ID,0) pos from EMPLOYEES;
–NVL2 (expr1, expr2, expr3) 如果expr1为空，这返回expr3（第2个结果）否则返回expr2
select LAST_NAME,NVL2(MANAGER_ID,1,0) pos from EMPLOYEES;
–NULLIF (expr1, expr2) 如果expr1和expr2相等，则返回空
select NULLIF(‘hello’,‘hello’) from dual;
–COALESCE (expr1, expr2, …, exprn) 如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到 一个不为NULL的值 或者如果全部为NULL，也只能返回NULL 了
SELECT COALESCE(null, ‘hello’) from dual;
–decode 用法
SELECT LAST_NAME,DECODE(JOB_ID, ‘AD_VP’, ‘ad’,‘IT_PROG’,‘prog’,‘member’) job
from EMPLOYEES;
–case when
SELECT LAST_NAME,CASE JOB_ID
WHEN ‘AD_VP’ THEN ‘ad’
WHEN ‘IT_PROG’ THEN ‘prog’
ELSE
‘member’
END CASE
from EMPLOYEES;

–多表连接查询 select distinct e.DEPARTMENT_ID,DEPARTMENT_NAME,EMPLOYEE_ID,LAST_NAME from EMPLOYEES e,DEPARTMENTS d where e.DEPARTMENT_ID=d.DEPARTMENT_ID

select LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
from EMPLOYEES e,DEPARTMENTS d
where e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID --左连接，即便右边没有对应的数据

select LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
from EMPLOYEES e LEFT JOIN DEPARTMENTS d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID

–分组查询 SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ，其他字段必须出现在Group By子 句中，否则不合法
select e.DEPARTMENT_ID,DEPARTMENT_NAME,count(*) count,TO_CHAR(AVG(NVL(SALARY, 0)),‘L99,999.00’) avg
from EMPLOYEES e,DEPARTMENTS d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID(+)
GROUP BY e.DEPARTMENT_ID,DEPARTMENT_NAME

having AVG(NVL(SALARY, 0))>=5000
order by e.DEPARTMENT_ID

–子查询
select *
from EMPLOYEES
where SALARY >=(select SALARY from EMPLOYEES where LAST_NAME=‘Abel’)
–any 和all 的用法
select LAST_NAME,JOB_ID,SALARY
from EMPLOYEES
where SALARY > any --any 是比最小的要大，all是比所有的都要大
( select SALARY from EMPLOYEES where JOB_ID=‘SA_REP’)
select min(SALARY) from EMPLOYEES where JOB_ID=‘SA_REP’

SELECT AVG(commission_pct) FROM employees;
select (select sum(commission_pct) from employees)/(select count(*) from employees ) from dual
select (select sum(commission_pct) from employees)/(select count(commission_pct) from employees ) from dual
SELECT AVG(NVL(commission_pct, 0)) FROM employees;
select avg()from EMPLOYEES where DEPARTMENT_ID=90

---

select * from EMPLOYEES where HIRE_DATE>TO_DATE(‘1997-01-01’,‘yyyy-mm-dd’) — select LAST_NAME,JOB_ID,SALARY,COMMISSION_PCT from EMPLOYEES where COMMISSION_PCT is not null order by SALARY desc — select EMPLOYEE_ID,SALARY*1.1, from EMPLOYEES where COMMISSION_PCT is null; – select LAST_NAME,FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12) year,MOD((TO_CHAR(HIRE_DATE,‘mm’)-TO_CHAR(SYSDATE,‘mm’))+12, 12) months from EMPLOYEES – select LAST_NAME from EMPLOYEES where LAST_NAME like ‘j%’ or LAST_NAME like ‘k%’ or LAST_NAME like ‘L%’ or LAST_NAME like ‘M%’ – select LAST_NAME,NVL2(COMMISSION_PCT, ‘yes’, ‘no’) from EMPLOYEES – select d.DEPARTMENT_NAME,STREER_ADDRESS,LAST_NAME,JOB_TITLE,SALARY from DEPARTMENTS d,EMPLOYEES e,JOBS j,LOCATIONS L where e.DEPARTMENT_ID=d.DEPARTMENT_ID and d.LOCATION_ID=L.LOCATION_ID and e.JOB_ID=j.JOB_ID and d.LOCATION_ID=1800

select count(*)
from EMPLOYEES
where LAST_NAME like ‘%n’; ---------------------------
–9.
select d.DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID,count(e.EMPLOYEE_ID)
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID
order by d.DEPARTMENT_ID
–10
select distinct JOB_ID
from EMPLOYEES
where DEPARTMENT_ID in (10,20)
–11
select JOB_ID,COUNT(e.EMPLOYEE_ID) total
from EMPLOYEES e,DEPARTMENTS d
where DEPARTMENT_NAME in (‘Administartion’,‘Executive’) and e.DEPARTMENT_ID=d.DEPARTMENT_ID
GROUP BY JOB_ID
order by total desc
–12
select EMPLOYEE_ID,LAST_NAME
from EMPLOYEE_ID
where HIRE_DATE --------------------???
13.
select LAST_NAME,TO_char(SALARY,’$999,999.00’)
from EMPLOYEES
–14. Show all employees who have managers with a salary higher than $15,000
select e.LAST_NAME employee,d.LAST_NAME manager,d.SALARY
from EMPLOYEES e,EMPLOYEES d
where e.MANAGER_ID=d.employee_id and d.salary>15000
–15
select d.DEPARTMENT_ID,d.DEPARTMENT_NAME,COUNT(EMPLOYEE_ID),ROUND(AVG(e.SALARY), 2)
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
group by rollup(d.DEPARTMENT_ID,d.DEPARTMENT_NAME) ------???
–16.显示平均工资最高的部门的部门编号和最低工资

select DEPARTMENT_ID,ROUND(avg(SALARY),2),MIN(SALARY)
from EMPLOYEES
GROUP BY DEPARTMENT_ID
having avg(SALARY)
>=all(select avg(SALARY)
from EMPLOYEES
GROUP BY DEPARTMENT_ID)
–17 显示没有销售代表工作的部门的编号、名称和位置
select DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID
from DEPARTMENTS
where DEPARTMENT_ID in
(SELECT d.DEPARTMENT_ID
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
group by d.DEPARTMENT_ID
having count(EMPLOYEE_ID)=0)
–18 a
select d.DEPARTMENT_ID,d.DEPARTMENT_NAME,count(EMPLOYEE_ID)
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID,d.DEPARTMENT_NAME
having count(EMPLOYEE_ID)❤️
–18 b

select d.DEPARTMENT_ID,d.DEPARTMENT_NAME,count(EMPLOYEE_ID)
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID,d.DEPARTMENT_NAME
having count(EMPLOYEE_ID) = (
select max(count(EMPLOYEE_ID))
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID
)

–18 c
select d.DEPARTMENT_ID,d.DEPARTMENT_NAME,count(EMPLOYEE_ID)
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID,d.DEPARTMENT_NAME
having count(EMPLOYEE_ID) = (
select min(count(EMPLOYEE_ID))
from DEPARTMENTS d left join EMPLOYEES e
on d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID
)
–19 显示所有员工的员工编号、姓氏、薪资、部门编号以及所在部门的平均薪资。

select employee_id, 

last_name,
department_id,
round(avg(salary) over(partition by department_id),4) as avg_salary
from employees
order by employee_id;
–over练习
–查询当前员工所在部门的平均工资、最高工资和最低工资，即可通过分析函数来实现。
select e.LAST_NAME,d.DEPARTMENT_NAME,AVG(SALARY) over (partition by e.DEPARTMENT_ID),
min(SALARY) over (partition by e.DEPARTMENT_ID), max(SALARY) over (partition by e.DEPARTMENT_ID)

from EMPLOYEES e,DEPARTMENTS d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID

20—.显示在雇佣人数最多的一周中雇佣的所有员工
select LAST_NAME,HIRE_DATE
from EMPLOYEES
where TO_CHAR(HIRE_DATE,‘day’) =(
select day
from (
select TO_CHAR(HIRE_DATE,‘day’) day,count() cc
from EMPLOYEES
group by TO_CHAR(HIRE_DATE,‘day’)
order by count() desc
)
where ROWNUM=1
)
21—根据员工的雇用日期创建周年概况。按升序排列周年纪念日。
–drop view emp_hiredate
create view emp_hiredate as
select LAST_NAME,TO_CHAR(HIRE_DATE,‘mm-dd’) birthday
from EMPLOYEES
order by TO_CHAR(HIRE_DATE,‘mm’),TO_CHAR(HIRE_DATE,‘dd’)
22—找到1990年上半年的工作和1991年同期的工作。
select JOB_ID
from EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,‘yyyy-mm-dd’) BETWEEN ‘1990-01-01’ and ‘1990-06-30’

select job_id
from employees
where extract(year from hire_date) = 1990
and extract(month from hire_date) <= 6
intersect
select job_id
from employees
where extract(year from hire_date) = 1991
and extract(month from hire_date) <= 6

–23.编写一个复合查询，以生成显示加薪百分比、员工ID、旧薪资和新加薪的员工列表。10、50、110部门员工加薪5%，60部门员工加薪10%，20、80部门员工加薪15%，90部门员工不加薪。
select EMPLOYEE_ID,DEPARTMENT_ID,SALARY old_sal,DECODE(DEPARTMENT_ID,
10,SALARY1.05,
50,SALARY1.05,
110,SALARY1.05,
60,SALARY1.1,
20,SALARY1.15,80,SALARY1.15,90,SALARY1,SALARY
) new_sal
from EMPLOYEES
—使用case when
select EMPLOYEE_ID,DEPARTMENT_ID,SALARY old_sal,
case DEPARTMENT_ID
when 10 then SALARY1.05
when 50 then SALARY1.05
when 110 then SALARY1.05
when 60 then SALARY1.1
when 20 then SALARY1.15
when 80 then SALARY*1.15
when 90 then SALARY
ELSE SALARY
END new_sal
from EMPLOYEES
–24 更改会话以将nls_date_格式设置为dd-mon-yyyy hh24:mi:ss。
Alter session set NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’
–25a.编写查询以显示以下时区的时区偏移（Tz_偏移）。澳大利亚/悉尼 智利/复活节岛
SELECT TZ_OFFSET (‘Australia/Sydney’) from dual;
select TZ_OFFSET(‘Chile/EasterIsland’)from dual;
–25b更改会话以将时区参数值设置为澳大利亚/悉尼的时区偏移量。

–25 c Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
select SYSDATE,CURRENT_DATE,CURRENT_TIMESTAMP,LOCALTIMESTAMp
from dual;
–26编写一个查询，显示在1月份加入的员工的姓氏、加入日期的月份和雇用日期，而不考虑加入的年份。
select FIRST_NAME,TO_CHAR(HIRE_DATE,‘mm’) mon,HIRE_DATE
from EMPLOYEES
where EXTRACT(month from HIRE_DATE)=01 (TO_CHAR(HIRE_DATE,‘mm’)=1)
–27 对于部门ID大于80的部门，编写查询以显示以下内容：
– 一个部门内每个工作的总工资
– 工资总额
– 部门所在城市的工资总额
– 每项工作的总工资，不分部门
– 各部门的工资总额，不分城
– 各部门所在城市的工资总额
– 各部门的工资总额，不考虑职称和城市
select city, department_name as dname, e.job_id as job, sum(salary)
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.、索引、序列、同义词等）。
注意：显示的输出只是一个准则。???location_id = l.location_id
and e.department_id > 80
group by cube(city, department_name, e.job_id);

–28–编写查询以显示以下分组：
部门ID、工作ID
作业ID，经理ID
查询应该计算每个组的最高和最低工资。

29–编写一个查询，在Employees表中显示前三名收入者。显示他们的姓和薪水。
select * from
(select LAST_NAME,SALARY
from EMPLOYEES
order by SALARY desc)
where rownum<=3

–30 编写一个查询以显示在加利福尼亚州工作的员工ID和姓氏。提示：使用标量子查询。
select EMPLOYEE_ID,FIRST_NAME,STATE_PROVINCE
from EMPLOYEES e,DEPARTMENTS d,LOCATIONS l
where e.DEPARTMENT_ID=d.DEPARTMENT_ID and d.LOCATION_ID=l.LOCATION_ID and l.STATE_PROVINCE=‘California’
31—通过查找员工的最小值（开始日期）的Job_History表，编写一个查询以删除员工最早的Job_History行。只删除已更改至少两个工作的员工的记录。如果查询执行正确，您将得到反馈：
–INSERT INTO “ORACLE_TRAIN”.“JOB_HISTORY”(“EMPLOYEE_ID”, “START_DATE”, “END_DATE”, “JOB_ID”, “DEPARTMENT_ID”) VALUES (‘101’, TO_DATE(‘1989-09-21 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’), TO_DATE(‘1993-10-27 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’), ‘AC_ACCOUNT’, ‘110’);
–savepoint a;
delete from JOB_HISTORY where rowid =(
select rowid from JOB_HISTORY where START_DATE <=ALL (
select START_DATE from JOB_HISTORY
where employee_id in (
select EMPLOYEE_ID
from
(
select EMPLOYEE_ID,JOB_ID
from EMPLOYEES
UNION
select EMPLOYEE_ID,JOB_ID
from JOB_HISTORY
)
GROUP BY EMPLOYEE_ID
having count(*)>=3
)
)
)

–32 rollback to a;
–33 写一个查询来显示那些最高工资超过整个公司最高工资一半的工作的工作ID。使用WITH子句编写此查询。将查询命名为max_sal_calc。

with
max_sal_calc as (
select employee_id,salary
from EMPLOYEES
where salary > (select max(salary)/2 from EMPLOYEES) )
select * from max_sal_calc

34—编写SQL语句以显示员工编号、姓氏、开始日期和工资，显示
–a 德汉的直接下属
select e.EMPLOYEE_ID,e.FIRST_NAME,e.HIRE_DATE,e.salary
from EMPLOYEES e,EMPLOYEES d
where e.MANAGER_ID=d.EMPLOYEE_ID and d.LAST_NAME=‘De Haan’
–b De Haan下的组织树（员工编号102）
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,’_’) AS org_chart ,EMPLOYEE_ID
from EMPLOYEES
start with EMPLOYEE_ID=102
CONNECT by prior employee_id=MANAGER_ID

–35编写一个分层查询，以显示员工编号、经理编号和员工姓氏（员工编号102）。同时显示员工的级别。
select EMPLOYEE_ID,MANAGER_ID,LPAD(FIRST_NAME, length(FIRST_NAME)+(LEVEL2)-2, ‘_’),level
from EMPLOYEES
start with EMPLOYEE_ID=102
CONNECT by prior EMPLOYEE_ID= MANAGER_ID
–36 生成分层报告以显示员工编号、经理编号、级别伪列和员工姓氏。对于Employees表中的每一行，您应该打印一个树结构，显示员工、员工经理、经理经理等。对名称列使用缩进。
select EMPLOYEE_ID,MANAGER_ID,LEVEL,LPAD(FIRST_NAME, LENGTH(FIRST_NAME)+(level2)-2, ‘_’)
from EMPLOYEES
start with EMPLOYEE_ID=100
connect by prior employee_id=MANAGER_ID

–37 编写查询以执行以下操作：
–从“员工”表中检索员工ID大于或等于200的员工的员工ID、雇用日期、工资和经理ID的详细信息
如果工资低于5000美元，请将员工ID和工资的详细信息插入到特殊表格中。
将员工ID、雇用日期和工资的详细信息插入sal_history表。
将员工ID、经理ID和工资的详细信息插入到“经理历史记录”表中。
–create table sal_history as select employee_id,HIRE_DATE,salary from EMPLOYEES where 1=2
–create table manager_history as select employee_id,MANAGER_ID,salary from EMPLOYEES where 1=2
insert into sal_history
select employee_id,HIRE_DATE,SALARY
from EMPLOYEES
where employee_id >=200 and salary<5000
insert into manager_history
select employee_id,MANAGER_ID,salary
from EMPLOYEES
where employee_id >=200 and salary<5000
–38Query the SPECIAL_SAL, SAL_HISTORY and the MGR_HISTORY tables to view the inserted records.
select * from v$sqlarea ; ???
–39 根据下表实例图表创建名为“索引表”的位置。将主键列的索引命名为locations_pk_idx。

create index locations_pk_idx on LOCATIONS(LOCATION_ID)