9i SQL notes

General functions:
– NVL
– NVL2
– NULLIF
– COALSECE
– CASE
– DECODE

CONCAT('Hello', 'World')
SUBSTR('HelloWorld',1,5)
LENGTH('HelloWorld')
INSTR('HelloWorld', 'W')
LPAD(salary,10,'*')
RPAD(salary, 10, '*')
TRIM('H' FROM 'HelloWorld')

HelloWorld
Hello
10
6
*****24000
24000*****
elloWorld

 
TRIM(leading|trailing|both, trim_character FROM trim_source)
Enables you to trim heading or trailing characters (or both)
from a character string. If  trim_character or
trim_source is a  character literal, you must enclose it in
single quotes.
This is a feature available from Oracle8i and later.

INSTR: Finds numeric position of a named character

Number Functions
• ROUND: Rounds value to specified decimal
ROUND(45.926, 2) 45.93
• TRUNC: Truncates value to specified decimal
TRUNC(45.926, 2) 45.92

SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2)

--------- ------------------

100 124.16

 

Date Functions
Number of months
between two dates
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Add calendar months to
date
Next day of the date
specified
Last day of the month
Round date
Truncate date
Function Description

• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Using Date Functions
• ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-FEB-95')
19.6774194
'11-JUL-94'
'08-SEP-95'
'28-FEB-95'

• ROUND(SYSDATE,'MONTH')         01-AUG-95
• ROUND(SYSDATE ,'YEAR')         01-JAN-96
• TRUNC(SYSDATE ,'MONTH')        01-JUL-95
• TRUNC(SYSDATE ,'YEAR')         01-JAN-95
Using Date Functions
Assume SYSDATE = '25-JUL-95':

日期类型的几个转换及格式函数还需要重新看看!!

NVL  Converts a null value to an actual value
NVL2  If  expr1 is not null,  NVL2 returns  expr2. If  expr1 is null,  NVL2
returns expr3. The argument expr1can have any data type.
NULLIF  Compares two expressions and returns null if they are equal, or the first
expression if they are not equal
COALESCE  Returns the first non-null expression in the expression list 

Note: The NULLIF function is logically equivalent to the following CASE expression. The CASE
expression is discussed in a subsequent page:
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
==========================
The CASE Expression
Facilitates conditional inquiries by doing the work of
an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
WHEN 'ST_CLERK' THEN  1.15*salary
WHEN 'SA_REP'   THEN  1.20*salary
ELSE      salary
END     "REVISED_SALARY"
FROM   employees;
====================
The DECODE Function
Facilitates conditional inquiries by doing the work of
a CASE or IF-THEN-ELSE statement:
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])

SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG',  1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP',   1.20*salary,
salary)
REVISED_SALARY
FROM   employees;

The same statement can be expressed in psuedocode as an IF-THEN-ELSE statement:
IF job_id = 'IT_PROG'     THEN  salary = salary*1.10
IF job_id = 'ST_CLERK'    THEN  salary = salary*1.15
IF job_id = 'SA_REP'      THEN salart = salary*1.20
ELSE salary = salary

==========================

Equijoins
To determine an employee’ s department name, you compare the value in the DEPARTMENT_ID column in
the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table. The relationship
between the EMPLOYEES and DEPARTMENTS tables is an equijoin, that is, values in the
DEPARTMENT_ID column on both tables must be equal. Frequently, this type of join involves primary and
foreign key complements.
Note: Equijoins are also called simple joins or inner joins.

CROSS JOIN Returns a Cartesian product(叉积,笛卡尔积) from the two tables
NATURAL JOIN Joins two tables based on the same column name

Retrieving Records with Natural Joins
SELECT department_id, department_name,
location_id, city
FROM   departments
NATURAL JOIN locations;

In the example in the slide, the LOCATIONS table is joined to the DEPARTMENT table by the
LOCATION_ID column, which is the only column of the same name in both tables. If other common
columns were present, the join would have used them all.

SELECT l.city, d.department_name
FROM   locations l JOIN departments d USING (location_id)
WHERE  location_id = 1400;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

SELECT  department_id, department_name,
location_id, city
FROM    departments
NATURAL JOIN locations
WHERE   department_id IN (20, 50);

Three-Way Joins with the ON Clause
SELECT employee_id, city, department_name
FROM   employees e
JOIN   departments d
ON     d.department_id = e.department_id
JOIN   locations l
ON     d.location_id = l.location_id;
This can also be written as a three-way equijoin:
SELECT employee_id, city, department_name
FROM   employees, departments, locations
WHERE  employees.department_id = departments.department_id
AND    departments.location_id = locations.location_id;
=================

LEFT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

This query retrieves all rows in the EMPLOYEES table, which is the left table even if there is no match in
the DEPARTMENTS table.
This query was completed in earlier releases as follows:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;
=================

RIGHT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no match
in the EMPLOYEES table.
This query was completed in earlier releases as follows:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id  (+);

============
FULL OUTER JOIN
A join between two tables that returns the results
of an inner join as well as the results of a left and
right join is a full outer join.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

===============

You cannot use group functions in the WHERE clause.
SELECT department_id, AVG(salary)
FROM   employees
WHERE  AVG(salary) > 8000
GROUP BY department_id;
WHERE  AVG(salary) > 8000
*
ERROR at line 3:
ORA-00934: group function is not allowed here

================
Types of Group Functions
STDDEV([DISTINCT|ALL]x)  Standard deviation of n, ignoring null values
VARIANCE([DISTINCT|ALL]x)  Variance of n, ignoring null values

===========
Using a Subquery in an INSERT Statement
INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,
department_id
FROM   employees
WHERE  department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);
1 row created.
=============

如果视图定义包括条件（譬如 WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句，则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则返回 SQL 错误。

INSERT INTO  (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM   employees
WHERE  department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

===============

MERGE Statement Syntax:
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
=================

ALTER TABLE dept80 ADD (job_id VARCHAR2(9));

ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30));

ALTER TABLE dept80 DROP  COLUMN   job_id;

ALTER TABLE table SET   UNUSED (column);
OR
ALTER TABLE  table SET UNUSED COLUMN column;

ALTER TABLE table DROP  UNUSED COLUMNS;

Changing the Name of an Object
RENAME    old_name TO  new_name;

COMMENT ON TABLE employees IS 'Employee Information';

Defining Constraints

column [CONSTRAINT constraint_name] constraint_type,

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),

ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
ALTER TABLE     employees
ADD CONSTRAINT  emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);

LTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

ALTER TABLE      employees
DROP CONSTRAINT  emp_manager_fk;

ALTER TABLE departments
DROP PRIMARY KEY CASCADE;

ALTER TABLE test1
DROP (pk) CASCADE CONSTRAINTS;

ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;

SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

 

==============================
Volumn 2
==============================

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT  employee_id, first_name || ' ' || last_name,
salary, department_id
FROM    employees
WHERE   department_id = 80;

• An inline view is a subquery with an alias (or
correlation name) that you can use within a SQL
statement.
• A named subquery in the FROM clause of the main
query is an example of an inline view.
• An inline view is not a schema object.

SELECT  a.last_name, a.salary, a.department_id, b. maxsal
FROM    employees a,
(SELECT   department_id, max(salary) maxsal
FROM     employees
GROUP BY department_id) b
WHERE   a.department_id = b.department_id
AND     a.salary < b.maxsal;

========================

CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales';

SELECT *  FROM [e-mail];

CREATE PUBLIC SYNONYM HQ_EMP FOR [e-mail];

SELECT * FROM HQ_EMP;

=========================
The UNION ALL operator returns results from both
queries including all duplications.

The INTERSECT operator returns results that are common to both queries.

===============
datetime functions:
• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• DBTIMEZONE
• SESSIONTIMEZONE
• EXTRACT
• FROM_TZ
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL
• TZ_OFFSET

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;

The syntax of the EXTRACT function is:
SELECT  EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND]
[TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression]
[interval_value_expression]);
SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
SELECT last_name, hire_date,
EXTRACT (MONTH FROM HIRE_DATE)
FROM employees;

SELECT 
FROM DUAL;
SELECT TO_TIMESTAMP ('2000-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS')
FROM DUAL;
TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
'YYYY-MM-DD HH:MI:SS TZH:TZM')

SELECT hire_date,
hire_date + TO_YMINTERVAL('01-02') AS
HIRE_DATE_YMININTERVAL
FROM EMPLOYEES
WHERE department_id = 20;
The example in the slide calculates a date that is one year two months after the hire date for the employees
working in the department 20 of the EMPLOYEES table.

SELECT hire_date, hire_date +  TO_YMINTERVAL('-02-04') AS   
HIRE_DATE_YMINTERVAL
FROM EMPLOYEES WHERE department_id = 20;
Observe that the character string passed to the TO_YMINTERVAL function has a negative value. The
example returns a date that is two years and four months before the hire date for the employees working in
the department 20 of the EMPLOYEES table.

SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL;
SELECT TZ_OFFSET('Europe/London') FROM DUAL;

The TZ_OFFSET function returns the time zone offset corresponding to the value entered.

========================

SELECT   department_id, job_id, SUM(sal)
FROM     employees 
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

SELECT  department_id, job_id, SUM(salary)
FROM    employees 
WHERE   department_id < 60
GROUP BY CUBE (department_id, job_id);

GROUPING Function
• Using it, you can find the groups forming the
subtotal in a row.
• Using it, you can differentiate stored NULL values
from NULL values created by ROLLUP or CUBE.
• It returns 0 or 1.

SELECT   department_id DEPTID, job_id JOB, SUM(salary),
FROM     employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
GROUPING(department_id) GRP_DEPT,GROUPING(job_id) GRP_JOB

GROUPING SETS

SELECT department_id, job_id, manager_id, AVG(salary)
FROM employees
GROUP BY
GROUPING SETS
((department_id, job_id, manager_id),
(department_id, manager_id),(job_id, manager_id));
This statement calculates aggregates over three groupings:
(department_id, job_id, manager_id), (department_id, manager_ id)
and (job_id, manager_id)

CUBE(a, b, c)
is equivalent to
GROUPING SETS
((a, b, c), (a, b), (a, c), (b, c),
(a), (b), (c), ())

 ROLLUP(a, b,c)
is equivalent to
GROUPING SETS ((a, b, c), (a, b),(a), ())

SELECT department_id, job_id, manager_id,avg(salary)
FROM   employees
GROUP BY   GROUPING SETS
((department_id,job_id), (job_id,manager_id));

Composite Columns: Example
SELECT   department_id, job_id, manager_id, SUM(salary)
FROM     employees 
GROUP BY ROLLUP( department_id,(job_id, manager_id));

Concatenated Groupings
The result is a cross-product of groupings from each grouping set.
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
The preceding SQL defines the following groupings:
(a, c), (a, d), (b, c), (b, d)

SELECT   department_id, job_id, manager_id, SUM(salary)
FROM     employees
GROUP BY department_id,ROLLUP(job_id),CUBE(manager_id);
The example in the slide results in the following groupings:
• (department_id, manager_id, job_id )
• (department_id, manager_id)
• (department_id, job_id)
• (department_id)

==================
Scalar Subqueries: Examples
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);
把子查询看作一个视图,外部查询按视图的列名来排序.
18-14

Correlated subqueries are used for row-by-row
processing. Each subquery is executed once for
every row of the outer query.
Nested Subqueries Versus Correlated Subqueries
With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to
be used by the main query. A correlated subquery, however, executes once for each candidate row
considered by the outer query. In other words, the inner query is driven by the outer query

Find all employees who earn more than the average
salary in their department.
SELECT last_name, salary, department_id
FROM   employees outer
WHERE  salary >(SELECT AVG(salary)
FROM   employees
WHERE  department_id = 
outer.department_id);

我們可以使用   WITH   Clause   來定義一個   query   block，然後在   SELECT   statement   的其它地方來使用這個   query   block。如果在一個很複雜的   Query   裡，我們必須重複的使用某個   subquery，使用   WITH   Clause   可以降低   Query   的複雜度以及提高   performance。WITH   Clause   所讀出的資料會暫存在   User   的   temporary   tablespace   中。  
  SQL>   WITH  
      2     DEPT_COSTS   AS   (   SELECT   D.department_name,   SUM(E.salary)   AS   dept_total  
      3                                         FROM   EMPLOYEES   E,   DEPARTMENTS   D  
      4       WHERE   E.department_id   =   D.department_id  
      5     GROUP   BY   D.department_name),  
      6     AVG_COST       AS   (   SELECT   SUM(dept_total)/COUNT(*)   AS   dept_avg  
      7                                         FROM   DEPT_COSTS)  
      8     SELECT   *  
      9         FROM   DEPT_COSTS  
    10       WHERE   dept_total   >   (SELECT   dept_avg   FROM   AVG_COST)  
    11     ORDER   BY   department_name;  
   
  DEPARTMENT_NAME                                 DEPT_TOTAL  
  ------------------------------   ----------  
  Sales--                                                         304500  
  Shipping--                                                   156400

 

Hierarchical Retrieval

SELECT [LEVEL], column, expr...
FROM   table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];

我们要找出员工ID为2的人及其所有下属(包括直接和间接下属)
select ID, EMP_NAME, MANAGER_ID
from employee
start with id = 2
connect by prior id = manager_id ;

Prior放在那里，那一侧就是被比较的一方(父方)，另一侧就是发起比较的一方(子方)。语义上可以这样翻译：xxx字段的值必须等于当前记录XXX字段的值(prior一方)

 

Types of Multitable INSERT Statements
Oracle9i introduces the following types of multitable insert
statements:
• Unconditional INSERT
• Conditional ALL INSERT
• Conditional FIRST INSERT
• Pivoting INSERT

Unconditional INSERT ALL:
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID ,hire_date HIREDATE, salary SAL, manager_id MGR
FROM  employees
WHERE employee_id > 200;

Conditional INSERT ALL:
INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID ,hire_date HIREDATE, salary SAL, manager_id MGR
FROM  employees
WHERE employee_id > 200;

Conditional FIRST INSERT:
INSERT FIRST
WHEN SAL  > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;

Pivoting INSERT:
INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;

External Table
External tables are read-only tables in which the           
data is stored outside the database in flat files.
The data can be queried using SQL but you cannot
use DML and no indexes can be created.
CREATE DIRECTORY emp_dir AS '/flat_files' ;

CREATE TABLE oldemp (
empno NUMBER, empname CHAR(20), birthdate DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad_emp'
LOGFILE 'log_emp'
FIELDS TERMINATED BY ','
(empno CHAR,
empname CHAR,
birthdate CHAR date_format date mask "dd-mon-yyyy"))
LOCATION ('emp1.txt'))
PARALLEL 5
REJECT LIMIT 200;