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;				SESSIONTIMEZONE						SELECT SESSIONTIMEZONE FROM DUAL;										获取部分时间的值：				   select extract(YEAR FROM SYSDATE) FROM DUAL;				   				   SELECT last_name, hire_date, 						   EXTRACT (MONTH FROM HIRE_DATE)					FROM employees					WHERE 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') hiredate2				FROM employees;				第六章子查询					SELECT	employee_id, manager_id, department_id					FROM	employees					WHERE  (manager_id, department_id) IN										  (SELECT manager_id, department_id										   FROM   employees										   WHERE  employee_id IN (199,174))					AND	employee_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_name					FROM     employees e					ORDER BY (SELECT department_name							   FROM departments d								WHERE e.department_id = d.department_id);					 SELECT last_name, salary, department_id						FROM   employees outer						WHERE  salary >             (SELECT AVG(salary)								  FROM   employees								  WHERE  department_id =  								  outer.department_id);								  					SELECT e.employee_id, last_name,e.job_id					FROM   employees e 					WHERE  2 <= (SELECT COUNT(*)								 FROM   job_history 								 WHERE  employee_id = e.employee_id);								 								 					SELECT employee_id, last_name, job_id, department_id					FROM   employees outer					WHERE  EXISTS ( SELECT 'X'									 FROM   employees									 WHERE  manager_id = 											outer.employee_id);																SELECT department_id, department_name					FROM departments d					WHERE NOT EXISTS (SELECT 'X'									  FROM   employees									  WHERE  department_id 											 = d.department_id);											 											 											 											 					UPDATE table1 alias1					SET    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;

?