Contraints/Objects/Set Operators


Not null
Unique
Primary key
Foreign key
Check
Create table employees
(
          employee_id number(6) ,
          First_name varchar2(20),
          Job_id varchar2(10) not null,
          Constraint emp_emp_id_pk primary key (employee_id),
          Constraint emp_dep_fk foreign key (department_id) references
          Departments (department_id) [on delete]
[cascade | set null] );

Constraint <constraint_name>       pimary key          (col_names)
                                   Unique              (col_names)
                                   Check               (condition)
                                   Foreign key         (col_name) references
                                                       tab_name (col_name)

Alter table table_name add constraint c_name typce (col);

Constraint emp_Sal_min check (salary > 0);

Alter table employees drop | disable | enable constraint const_name;

Create view v$name as select * from tbl_name;

Create or replace view empv10 as
...
with check option name
read only;

select rownum as rank , last_name , salary from ... where rownum <= 3;

Objects:
table , view, sequence, index, synonym

        sequence:


create sequence seq_name
increment by n
start with n
maxvalue n
minvalue n
cycle | no cycle
cache | no cache

seq_name.currval
seq_name.nextval

alter sequence seq_name ...
describe user_sequences

create index index on table (column)
create index index on table (upper(column));

create public synonym synonym_name for object

create user scott identified by tiger
grant privilige ro user_name | role
      create session
      create table
      create sequence
      create view

create role manager
grant priv to manager
grant manager to user

alter user scott identified by lion;
grant object_priv on object to user | role [with grant option]
alter
delete
execute
index
insert
references
select
update
Grant update (department_name , location_id)
       on departments to scott, manager

Set operators:
union
union all                           //includes duplicate rows
intersect
minus
select department_id, job_id, sum(salary) from employees where department_id < 60
        group by rollup | cube (department_id, job_id);

select department_id , job_id manager_id, avg(salary) from employees
      group by grouping sets ((department_id, job_id) , (job_id, manager_id));

select department_id , job_id , sum(salary) from employees where department_id < 60
      group by [rollup | cube] (department_id, job_id);

group by grouping sets ((, , , ) , (, , ,))

exists , not exists

alter table employees add (department_name varchar2(14));

update employees e set department_name = (select department_name from departments.d
    where e.department_id = d.department_id);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: