Tuesday, April 21, 2015

ALTER table #Oracle #SQL






CREATE TABLE locations
    ( location_id    NUMBER(4)
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city       VARCHAR2(30)
CONSTRAINT     loc_city_nn  NOT NULL
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    ) ;

ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
        PRIMARY KEY (location_id)
    , CONSTRAINT loc_c_id_fk
        FOREIGN KEY (country_id)
         REFERENCES countries(country_id) 
    ) ;


REM ******  Creating DEPARTMENTS table ....

CREATE TABLE departments
    ( department_id    NUMBER(4)
    , department_name  VARCHAR2(30)
CONSTRAINT  dept_name_nn  NOT NULL
    , manager_id       NUMBER(6)
    , location_id      NUMBER(4)
    ) ;


ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
        PRIMARY KEY (department_id)
    , CONSTRAINT dept_loc_fk
        FOREIGN KEY (location_id)
         REFERENCES locations (location_id)
     ) ;



CREATE TABLE jobs
    ( job_id         VARCHAR2(10)
    , job_title      VARCHAR2(35)
CONSTRAINT     job_title_nn  NOT NULL
    , min_salary     NUMBER(6)
    , max_salary     NUMBER(6)
    ) ;


ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
      PRIMARY KEY(job_id)
    ) ;

No comments:

Post a Comment

Using Zotero for academic writing