SQL语句PART1

Oracle SQL(partI)

Data manipulation language(DML): select, insert, update, delete, merge.
Data definition language(DDL): create, alter, drop, rename, truncate, comment
Data control language(DCL): grant, revoke
Transaction control: commit, rollback, savepoint


Arithmetic Expressions:
+, -, *, /
1) Arithmetic expressions containing a null value evaluate to null 


Column Alias
1) requires double quotation marks if it contains spaces or special characters, or if it is case-sensitive
e.g.: select last_name as name, commission_pct comm from employees;
e.g.: select last_name  "Name", salary*12 "Annual Salary" from employees 


Alternative Quote(q) Operator
e.g.: select depart_name||' Department'||q'['s Manager is ]'||manager_id as "Department and Manager"

results:
Department and Manager
Administrator Department's Manager is Me
...


Using DESCRIBE to display the table structure
e.g.: describe tb1;


Rules of precedence for operators in an expression
1   Arithmetic operators
2  Concatenation operator
3  Comparison conditions
4  IS [NOT] NULL, LIKE, [NOT] IN
5  [NOT] BETWEEN
6  Not equal to
7  NOT logical condition
8  AND logical condition
9  OR logical condition
e.g.: select last_name, job_id, salary from employees where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;
means: select job_id='SA_REP' or (job_id='AD_PRES' and salary>15000)
e.g.: select last_name, job_id, salary from employees where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000



Sorting:
can be sorted by column's numeric position
eg.: select last_name, job_id, department_id, hire_date from employees order by 3; 


Substitution Variables
1. temporarily store values with & and &&
2. using in following conditions: where conditions, order by clauses, column expressions, table names, entire select statements.
e.g.: select employee_id, last_name, salary, department_id from employees where employee_id=&employee_num;
3. using single quotation marks for date and character values:
e.g.: select last_name, department_id, salary*12 from employees where job_id='&job_title';
4. specifying column names, expressions, and text:
e.g.: select employee_id, last_name, job_id, &column_name from employees where &condition order by &order_column;
5. && if wanting to reuse the variable value without prompting the user each time:
e.g.: select employee_id, last_name, job_id, && column_name from employees order by &column_name;


DEFINE command
DEFINE: create and assign a value to a variable
UNDEFINE: remove the variable
e.g.:
DEFINE employee_num=200
select employee_id, last_name, salary, department_id from employees order by employee_id=&employee_num;
UNDEFINE employee_num


VERIFY command
SET VERIFY ON: to toggle the display of the substitution variable
e.g.:
SET VERIFY ON
select employee_id, last_name, salary from employees where employee_id=&employee_num;


Character Functions
Function:                         Result:
lower('SqL Course')           sql course
upper('Sql Course')            SQL COURSE
initcap('SQL Course')         Sql Course
concat('Hello', 'World')       HelloWorld
substr('HelloWorld',1,5)       Hello
length('Hello World')          11
instr('HelloWorld','W')        6
LPAD(salary, 10, '*')        *****24000
RPAD(salary, 10, '*')        24000*****
REPLACE('JACK and JUE','J','BL')        BLACK and BLUE
trim('H' FROM 'HelloWorld')    elloWorld


Number Functions:
round(45.926, 2)            45.93
trunc(45.926,2)             45.92
mod(1600,300)             100


Date Functions:
Function                Results                                           
month_between    Number of months between 2 dates
add_months         add calendar months to date
next_day             next day of the date specified
last_day              last day of the month
round                  round date
trunc                    Truncate date
e.g.:
months_between('01-sep-95', '11-jan-94'): 19.6774194
add_months('31-jan-96',1): '29-feb-96'
next_day('01-sep-95','friday'): '08-sep-95'
last_day(01-feb-95'): '28-feb-95'
round(sysdate,'month'): 01-aug-03
round(sysdate,'year'): 01-jan-04
trunc(sysdate,'month'): 01-jul-03
trunc(sysdate,'year'): 01-jan-03


Date Format Model: to_char(date,'xxxx-xxx');
yyyy            Full year in numbers
year            year spelled out(in English)
MM            2 digit value for the month
month        full name of the month
mon            3 letter abbreviation of the month
dy              three-letter abbreviation of the day of the week
day            full name of the day of the week
dd             numeric day of the month
** has an fm element to remove padded blanks or suppress leading zeros
e.g.: select last_name, to_char(hire_date, 'fmDD Month YYYY') as hiredate from employees;


TO_CHAR function with Number
to_char(number,'format_model');
9:     represents a number
0:     forces a zero to be displayed
$:    places a floating dollar sign
L:     uses the floating local currentcy symbol
.:      prints a decimal point
,       prints a comma as a thousands indicator


General Functions：nvl(expr1, expr2), nvl2(expr1, expr2, expr3) , nullif(expr1, expr2), coalesce(expr1, expr2, ... exprn)
1. nvl2(expr1, expr2, expr3): if expr1 is null, then expr3, if expr1 is not null, then expr2
2. nullif(expre1, expr2): return null if expr1=expr2
3. coalesce(expr1, expr2,... exprn): return the first non-null value in an expression list. return NULL if all expressions are null.


IF-THEN-ELSE sql functions: CASE, DECODE
1. case expr when comparison_expr1 then return_expr1
                  [when  comparison_expr2 then return _expr2
                   when comparison_exprn then return_exprn
                  else else_expr]
end
e.g.:
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;
2. decode (col | expression, search1, result1
               [, search2, result2, ...,]
               [, default] )
e.g.:
select last_name, job_id, salary,
     decode (job_id, 'IT_PROG', 1.1*salary,
                            'ST_CLERK', 1.15*salary,
                           'SA_REP', 1.2*salary,
                   salary)
     revised_salary
from employees;


Group Function: AVG,COUNT,MAX,MIN,STDDEV, SUM, VARIANCE
1. AVG, SUM for numeric data
2. MIN, MAX for numeric, character and date data
3. COUNT(*): return number of rows,
   COUNT(expr): returns the number of rows with non-null values for expr.
   COUNT(distinct expr): returns the number of distinct non-null values of expr.
4. group functions ignore null values in the column
e.g.: select avg(commission_pct) from employees;
        select avg(nvl(commission_pct,0)) from employees; -- to include null

Group By clause:
1. any column or expression in Select list (not aggregate function) must be in the GROUP BY clause
e.g.:
error: select department_id, count(last_name) from employees
error: select department_id, job_id, count(last_name) from employees group by department_id;
right: select department_id, job_id, count(last_name) from employees group by department_id, job_id;
2. cannot use "WHERE" clause to restrict groups, instead, using HAVING clause to restrict groups.
3. cannot use group functions in the WHERE clause.
e.g.:
right: select nmoduleinfoid, ndocsortid, count(nprocid) from  table1 group by nmoduleinfoid, ndocsortid having count(nprocid)>7;


 Joins Types
1. natural joins: natural join, using, on
2. outer joins:  left outer join, right outer join, full outer join
3. cross joins
join syntax:
select table1.column, table2.column
from table1
[natural join table2] |
[join table2 using (column_name)] |
join table2 on (table1.column_name=table2.column_name) ] |
[left | right | full outer join table2
on (table1.column_name = table2.column_name) ] |
[ cross join table2];
e.g.:
table wf_docsort:  ndocsortid, cname, ckey..
table wf_procname: nprocid, ndocsortid, cprocname...
1. select * from wf_procname natural join wf_docsort;
results:
ndocsortid, nprocid, cprocname.. cname, ckey..
** if columns with same names have different data types, an error is returned.
2. select * from wf_procname  join wf_docsort using (nmoduleinfoid, ndocsortid)
results:
nmoduleinfoid, ndocsortid, ... .. ..
select * from wf_procname join wf_docsort using (nmoduleinfoid);
nmoduleinfoid, ndocsortid, cname, ckey, nprocid, ndocsortid_1, ... ..
** cannot use natural while using using clause, or an ora-error happened.
** error example: select * from wf_procname p join wf_docsort d using (d.ndocsortid);
3. select * from wf_procname p join wf_docsort d on (d.ndocsortid=p.ndocsortid)
results:
nprocid, cprocname, ndocsortid, nmoduleinfoid, ... ndocsortid_1, nmoduleinfoid_1, cname, ...
4. select * from wf_procname p join wf_docsort d on (d.ndocsortid=p.ndocsortid)
join wf_moduleinfo m on (m.nmoduleinfoid=d.nmoduleinfoid)
where d.ndocsortid>7
select * from wf_procname p join wf_docsort d on (d.ndocsortid=p.ndocsortid)
join wf_moduleinfo m on (m.nmoduleinfoid=d.nmoduleinfoid)
and d.ndocsortid>8
both sqls are correct.
5. select e.entityname, o.entityname as orgname
from tbentity e join tbentity o
on e.belongedentityid = o.entityid
tbentity: entityid... entityname, ... belongedentityid
6. table employees: employeeid, employeename, salary
    table job_grade: grade_level, lowest_salary, highest_salary
select e.employeeid, e.employeename, e.salary
from employees e join job_grade j
on e.salary between j.lowest_salary and j.highest_salary
7.
select d.ndocsortid, d.cname, g.ndocid,g.curtitle
from wf_docsort d LEFT outer join wf_doc_gw g
on(D.NDOCSORTID=G.NDOCSORTID)
AND g.NDOCID>1361000
ORDER BY g.NDOCID
results:
* first shows the wf_doc_gw ndocids>1361000
* second shows wf_docsort where ndocsortid not in the (select ndocsortid from wf_doc_gw where ndocid>13610000)(showing ndocid is null)

select d.ndocsortid, d.cname, g.ndocid,g.curtitle
from wf_docsort d RIGHT outer join wf_doc_gw g
on(D.NDOCSORTID=G.NDOCSORTID)
AND g.NDOCID>1361000
ORDER BY g.NDOCID
results:
* first shows the wf_doc_gw ndocids not in the (select ndocsortid from wf_doc_gw where ndocid>1361000). (showing ndocsortid is null)
* second shows the wf_doc_gw ndocids in (select ndocsortid from wf_doc_gw where ndocid>1361000).

select d.ndocsortid, d.cname, g.ndocid,g.curtitle
from wf_docsort d FULL outer join wf_doc_gw g
on(D.NDOCSORTID=G.NDOCSORTID)
AND g.NDOCID>1361000
ORDER BY g.NDOCID
results:
* first shows the wf_doc_gw ndocids not in the (select ndocsortid from wf_doc_gw where ndocid>1361000). (showing ndocsortid is null)
* second shows the wf_doc_gw ndocids in (select ndocsortid from wf_doc_gw where ndocid>1361000).
* third shows wf_docsort where ndocsortid not in the (select ndocsortid from wf_doc_gw where ndocid>13610000)(showing ndocid is null)

 

e.g.:
wf_docsort:  CREATE TABLE WF_DOCSORT(NDOCSORTID     NUMBER                         NOT NULL,  NMODULEINFOID  NUMBER                         NOT NULL,  CNAME          VARCHAR2(250 BYTE)             NOT NULL,  KEY            VARCHAR2(250 BYTE)             NOT NULL)
wf_procname:  CREATE TABLE WF_PROCNAME(  NPROCID        NUMBER                         NOT NULL,  CPROCNAME      VARCHAR2(250 BYTE),
  NDOCSORTID     NUMBER,  NMODULEINFOID  NUMBER,  NORDER         NUMBER,  NDAYS          NUMBER,  NSTATUS        NUMBER                         DEFAULT 0)
wf_moduleinfo: CREATE TABLE WF_MODULEINFO(  NMODULEINFOID  NUMBER                         NOT NULL,  CNAME          VARCHAR2(250 BYTE)             NOT NULL,  KEY            VARCHAR2(250 BYTE)             NOT NULL)
1. select p.* from wf_procname p join wf_docsort d on(p.ndocsortid=p.ndocsortid) // ok
2. select * from wf_procname join wf_moduleinfo using(nmoduleinfoid) // ok
3. select * from wf_docsort join wf_moduleinfo using(nmoduleinfoid) // OK
4. select * from wf_procname join wf_docsort using(ndocsortid) // ok
5. select * from wf_procname join wf_docsort using(ndocsortid) join wf_moduleinfo using(nmoduleinfoid) //  error: nmoduleinfoid: 未明确定义列
6. select * from wf_procname join wf_moduleinfo using(nmoduleinfoid) where key='GW' // ok, count=26
   select * from wf_procname join wf_moduleinfo using(nmoduleinfoid) // count=62
7. select * from wf_docsort join wf_moduleinfo using(nmoduleinfoid) where key='GW' // error: key: 未明确定义列