Oracle 10g SQL Fundamentals II(学习笔记二第5-6章)
第五章不同时区管理数据TIME_ZONE 会话参数值时间相对值：ALTER SESSION SET TIME_ZONE = '-05:00';数据库时间区：ALTER SESSION SET TIME_ZONE = dbtimezone;操作系统时间区：ALTER SESSION SET TIME_ZONE = local;区域命名：ALTER SESSION SET TIME_ZONE = 'America/New_York';CURRENT_DATE: 设置时间的格式： ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';设置时区ALTER SESSION SET TIMZE_ZONE='-5:00';SELECT SESSIONTIMEZONE,CURRENT_DATE FROM DUAL; CURRENT_TIMESTAMP:SELECT SESSIONTIMEZONE,CURRENT_TIMESTAMP FROM DUAL; LOCALTIMESTAMP: SELECT CURRENT_TIMESTAMP,LOCALTIMESTAMP FROMDUAL;DBTIMEZONE SELECT DBTIMEZONE FROM DUAL;SESSIONTIMEZONESELECT SESSIONTIMEZONE FROM DUAL;获取部分时间的值： select extract(YEAR FROM SYSDATE) FROM DUAL; SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE)FROM employeesWHERE manager_id = 100;TZ_OFFSET select TZ_OFFSET('US/Eastern') from dual;select TZ_OFFSET('Europe/London') from dual;FROM_TZ select FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','3:00') from dual;select FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','Australia/North') from dual;TO_TIMESTAMP SELECT TO_TIMESTAMP ('2000-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') FROM DUAL;TO_TIMESTAMP_TZ SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM employees WHERE department_id = 20; SELECT last_name, TO_CHAR(hire_date, 'mm-dd-yy:hh:mi:ss') hire_date, TO_CHAR(hire_date + TO_DSINTERVAL('100 10:00:00'), 'mm-dd-yy:hh:mi:ss') hiredate2FROM employees;第六章子查询SELECTemployee_id, manager_id, department_idFROMemployeesWHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (199,174))ANDemployee_id NOT IN (199,174); SELECT employee_id, last_name, (CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800 )THEN 'Canada' ELSE 'USA' END ) location FROM employees;SELECT employee_id, last_nameFROM employees eORDER BY (SELECT department_name FROM departments dWHERE e.department_id = d.department_id); SELECT last_name, salary, department_idFROM employees outerWHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id); SELECT e.employee_id, last_name,e.job_idFROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); SELECT employee_id, last_name, job_id, department_idFROM employees outerWHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);SELECT department_id, department_nameFROM departments dWHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id); UPDATE table1 alias1SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);with的语句的使用：WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name),avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs)SELECT * FROM dept_costs WHERE dept_total >(SELECT dept_avg FROM avg_cost)ORDER BY department_name;?