SQL

1.select sal salary from emp;

   select sal as salary from emp;

   select sal as "salary" from emp;

   select comm "comm sal" from emp;

  column alias can not use where clause

 

2.DML select,insert,update,delete,merge

   DCL grant,revoke

   DDL create,drop,alter,rename,truncate

   DTL  commit,rollback,savepoint

 

3.select statements are not  case-sensitive

   select * from emp;

   select ename,id from emp;

   select sal+100 from emp; (+,-,*,/,()),number or date data can use them

  

4.NULL value   unknown value,is not the same as zero or a blank space

                        the value of numeric expression containing NULL is NULL

                        the value of character expression containing NULL is no null, is original value 

5.||

   select ename||' '||job from emp;

 

6.string character or date  using single quotation

   select  ename||'is a'||job from emp;

   select  ename||q'['s a ]'||job from emp;

 

7.select distinct deptid from emp;

   select distinct ename,empno from emp;

 

8 desc emp;

   select dbms_metadata.getddl('TABLE','T') from dual;

 

9.character strings and dates

   strings and dates must use single quotations

   strings are case sensitive

   date are format-sensitive

   the defult date format is dd-mon-rr

 

10.comparison operators(>,>=,<,<=,=,<>,between...and(>=...<=),in(set),like,is null)

 

11.select sal from emp where sal between 100 and 3000;

     select ename from emp where ename between 'ALLEN' and 'CLARK';

 

12.in/not in

     select ename from emp where mgr in(7698,7782,7788); 

     select ename from emp where mgr=7698 or mgr=7788 or mgr=7782;

 

     select ename from emp where job in ('CLERK','MANAGER');

     select ename from emp where job='CLERK' or job='MANAGER';

 

13.like,_,%

     select ename from emp where ename like 's%';

     select ename from employees where hire_date like '%95';

     select job_id from employees where job_id like '%ST\_%' escape '\'

 

14.is null

     is not null

 

15.and,or,not

     not in

     not like

 

16.order by

     1. asc(default)

         desc

     2. come last the select clause

     3. sorting by alias

     4. in asc, null last

             desc,nul first

    

     select employee_id,salary,hire_date from employees order by 3;

     select employee_id,salary,hire_date from employees order by hire_date;

     select employee_id,salary*12 sal,hire_date from employees order by sal;

     select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc;

     select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc nulls first;

     select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc nulls last;

 

17.substitution variables

     1.&|&&

     2.using quotation when date or character string  '&hire_date'

     3. can use in anyway in the sql statement

     4. is client function ,not oracle server

     5.&& can not promt the same column_name

     select employee_id,department_id,salary*12 sal,hire_date from employees where employee_id=&empid;

     select employee_id,department_id,salary*12 sal,hire_date from employees where hire_date='&hiredate'; 

    select employee_id,&hire_date,salary*12 sal,hire_date from employees where hire_date='&hiredate'; 

    select employee_id,&&hire_date,salary*12 sal from employees order by &hire_date

 

18.define

     define empno=20

     undefine empno

     select employee_id from employees where employee_id=&empno; 

 

19.verify: weather is or display the before and after of substitution variables

     set verify on

     set verify off

   

20.single row:return one result per row

     character functions

     lower()   concat('hello','world')

     upper()   substr('helloworld',1,5)

     initcap()  length()

                    instr('helloworld','w')

                    lpad(salary,10,'*'),rpad(salary,10,'*')

                    replace('jack and jue','j','bl')

                    trim('h' from 'helloworld')  elloworld

      

     number functions

     round()

     trunc()

     mod()

  

     date functions

     select sysdate from dual;

     months_between('01-sep-95','11-jan-94')

     add_months('31-jan-96',1)

     next_day('01-sep-95','friday')  

     last_day('01-feb-95')

     trunc()

     round()

 

 

 21.conversion function

      select '12'+1 from dual;

      select to_char(sysdate,'yy/mm') from dual;

      select to_char(sysdate,'fmyy-mm-dd') from dual;  

      select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual; 

      select to_char(6000,'$999,999.00') from dual; 

      select to_number('-$95.24','$99.99') from dual; 

      select to_date('12,05 99','dd,mm yyyy') from dual;

 

22. genaral function

      nvl(1,2)

      nvl2(1,2,3)

      nullif(length(1),length(2)) from dual  if the length of 1 ,2 is equal, return null, not equal,return lenth(1)

      coalese(1,2,3,'') if 1is not null, return 1,else do remaing coalse

     

 

23.condition expression

     1.case

     select last_name,job_id,
          case job_id when 'SH_CLERK' THEN 1.10*salary
                       when 'SA_REP'  then 1.15*salary
                       when 'SA_MAN' then 2.0*salary
                      else salary end "revised salary"
      from employees
     2.decode

        select last_name,job_id,

           decode(job_id,'SA_REP',salary*1.10,'SA_MAN',salary*1.15,'SA_CLERK',salary*2.0,salary)

        from employees

     

24.mutiple rows function : ignore null value

     avg()

     count()

     min()

     max()

     sum()

     stddev()

     variance()   

 

     select count(*) from emp;

     select count(department_id) from emp;  //return the number of rows with  not null vlues for expr

     select count(department_id) from employees

     select count(distinct department_id) from employees

     select avg(nvl(commission_pct,0)) from employees;  

 

25.group by

     1.all columns in the select list that are not in group functions must be in the group by clause;

     2.can nou use group functions in the where clause;

     3. can not use where to restrict groups

     4. can use having to restrict groups

     select department_id,avg(salary)

     from employees

     group by department_id

 

     select department_id,job_id,sum(salary)

     from employees

     group by department_id,job_id

 

     select job_id,max(salary) payroll

     from employees

     group by job_id

     having max(salary)>10000

 

26.execute order of the select statements

     from-where-group by-group functions-having-order by

    

27.select from muti tables

     1.tables alias, when we use table alias, we must use alias in places;

     2.column alias

     3.natural join(using) ,the same name,the same type

      

     一。sql std

      select department_id,department_name,location_id,city                      
      from departments                                                           
      natural join locations;  (sql standard)

      where department_id in(20,30);

 

      select employee_id,last_name,department_id,department_name
      from employees join departments
      using(department_id)
      where department_id=30;

 

      select e.employee_id,e.last_name,e.department_id,d.department_name
      from employees e join departments d
      on (e.department_id=d.department_id) and (e.manager_id=d.manager_id)

 

     二。oracle std

   

      select department_id,department_name,d.location_id,city                      
      from departments d,locations l                                                          
      where d.location_id=l.location_id(oracle sql)

   

     

    4.self join

      select w.last_name,m.last_name 

      from employees w join employees m

      on (w.manager_id=m.employee_id)

     

   5.not equaljoin

      select e.last_name,e.salary,j.grade_level

      from employees e join job_grades  j

      on e.salary between j.lowerest_sal and j.hightest_sal;

 

      select e.last_name,e.salary,j.grade_level

      from employees e , job_grades  j

      where e.salary between j.lowerest_sal and j.hightest_sal;

 

   6.left/right/full 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)

 

     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)

 

     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)

 

     oracle std

     select e.last_name,e.department_id,d.department_name
     from employees e ,departments d
     where e.department_id(+)=d.department_id  (rigth join)

 

     select e.last_name,e.department_id,d.department_name
     from employees e ,departments d
     where e.department_id =d.department_id(+)  (left join)

 

   7.cross join

      select last_name,department_name

      from employees cross join departments (sql std)

 

      select last_name,department_name

      from employees ,departments  (oracle std)

 

 28.subquery(inner query):

      1. the subquery(inner query) executes before the main query

      2. the subquery(inner query) exists from,where,having

      3. enclose subquery in parentheses

      4. place subquery on the right side of the comparison condition for readlibility(howerver,the subquery can appear on either side of the comparison operator)

      5. using single-row operators with single-row subquery

          multiple operators with mutilple operators

     

     一.single-row subquery

      select  last_name,job_id,salary

      from employees

      where salary =(select min(salary) from employees );

     

      select department_id,min(salary)

      from employees

      group by department_id

      having min(salary)>(select min(salary) from employees where department_id=50);

   

      select job_id,avg(salary)

      from employees

      group by job_id     

      having avg(salary)=(select min(avg(salary)) from employees group by job_id);

   

    二.multi-row subquery(in ,any,all)

        not in ==== <> all

        in ====  =any   

      select employee_id,last_name

      from employees

      where salary in (select min(salary) from employees group by department_id);

 

      select employee_id,last_name,job_id,salary

      from employees

      where salary>all(select salary from employees where job_id='IT_PROG')

      and job_id<>'IT_PROG';

 

      select employee_id,last_name,job_id,salary

      from employees

      where salary<all(select salary from employees where job_id='IT_PROG')

      and job_id<>'IT_PROG';

 

      select employee_id,last_name,job_id,salary

      from employees

      where salary>any(select salary from employees where job_id='IT_PROG')

      and job_id<>'IT_PROG';

 

      select employee_id,last_name,job_id,salary

      from employees

      where salary<any(select salary from employees where job_id='IT_PROG')

      and job_id<>'IT_PROG';

            

      select employee_id,last_name,job_id,salary

      from employees

      where salary=any(select salary from employees where job_id='IT_PROG')

      and job_id<>'IT_PROG';