Oracle 10 g SQL Fundamentals I(学習ノート第7-8章)

         Union/UNION ALL/MINUS/INTERSECT
	union   :          
	   select employee_id,job_id from employees
	      union
	   select employee_id,job_id from job_history;
	union all   :(         )
	   select employee_id,job_id ,deparment_id
	   from employees
	      union all
	   select employee_id,job_id,deparment_id
 	   from job_history
       order by employee_id;
	   
    INTERSECT   :(         )
      select employee_id,job_id ,deparment_id
	   from employees
	      intersect
	   select employee_id,job_id,deparment_id
 	   from job_history
       order by employee_id;
	   
	minus   :(                    )
      select employee_id,job_id ,deparment_id
	   from employees
	      minus
	   select employee_id,job_id,deparment_id
 	   from job_history
       order by employee_id;
	
	  :1.          ,  union all.
   :    
	insert ,delete,update,select,truncate
    
	       
	insert into test
	  select * from test_1
	  where 1=1;
	
	     (DCL)
	
     DDL        
    :
      
       :
	create table  xxx
	(id number(8) constraint xxx_id_pk primary key,
	hire_date  date default sysdate);
	
	
	   :
    create table  xxx
	(id number(8) ,
	hire_date  date default sysdate,
	constraint xxx_id_pk primary key(id));

	    :
	create table  xxx
	(id number(8) ,
	email varchar(30),
	hire_date  date default sysdate,
	deptno number,
	salary number(2),
	constraint emp_salary_min check(salary>0),
	constraint xxx_id_fk foreign key(deptno)  ---    
	references departments(deptno),
	constraint xxx_email_uk unique(email));   ---    
	
	
	  :
	CREATE TABLE employees
    ( employee_id    NUMBER(6)
        CONSTRAINT     emp_employee_id   PRIMARY KEY
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
        CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
        CONSTRAINT     emp_email_nn      NOT NULL
        CONSTRAINT     emp_email_uk      UNIQUE
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
        CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
        CONSTRAINT     emp_job_nn        NOT NULL
    , salary         NUMBER(8,2)
        CONSTRAINT     emp_salary_ck     CHECK (salary>0)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
        CONSTRAINT     emp_dept_fk       REFERENCES
           departments (department_id));