Basis Data Modul 11 (Query)

1.
CREATE TABLE DEPT1 (
    id NUMBER (7)
      CONSTRAINT dept1_id_pk PRIMARY KEY
      CONSTRAINT dept1_id_nn NOT NULL
    , name VARCHAR2 (25)
      CONSTRAINT dept1_name_nn NOT NULL);
desc dept1;


2.
INSERT INTO dept1 (id, name)
  SELECT department_id, department_name
  FROM departments;
 
  select *
from dept1;


3.
CREATE TABLE EMP1 (
    id NUMBER (7)
      CONSTRAINT emp1_id_pk PRIMARY KEY
    , last_name VARCHAR2 (25)
    , first_name VARCHAR2 (26)
    , dept_id NUMBER (7)
         CONSTRAINT emp1_dept_fk  REFERENCES
             dept1 (id));            
desc emp1;


4.
ALTER TABLE emp1
MODIFY (first_name VARCHAR2 (50));
desc emp1;


5.
CREATE TABLE EMP2 (
    id NUMBER (6)
      CONSTRAINT e_id_pk PRIMARY KEY
      CONSTRAINT e_id_nn NOT NULL
    , first_name VARCHAR2 (20)
    , last_name VARCHAR2 (25)
    , salary NUMBER (8,2)
    , dept_id NUMBER (4)
         CONSTRAINT e_dept_fk  REFERENCES
             departments (department_id));
desc EMP2;


6.
drop table emp1;
desc emp1;


7.
RENAME emp2 to staff;
desc emp2;
desc staff;


8.
ALTER TABLE staff
drop column first_name;
desc TABLE staff;


9.
CREATE TABLE employees2 (
    employee_id NUMBER (6)
      CONSTRAINT emp2_id_pk  PRIMARY KEY
    , first_name VARCHAR2 (20)
    , last_name VARCHAR2 (25)
      CONSTRAINT emp2_last_name_nn NOT NULL
    , email VARCHAR2 (25)
      CONSTRAINT emp2_email_nn NOT NULL
      CONSTRAINT emp2_email_uk UNIQUE
    , phone_number VARCHAR2 (20)
    , hire_date DATE
      CONSTRAINT emp2_hire_date_nn NOT NULL
    , job_id VARCHAR2 (10)
      CONSTRAINT emp2_job_nn NOT NULL
    , salary NUMBER (8,2)
      CONSTRAINT emp2_salary_ck CHECK (salary>0)
    , commission_pct NUMBER (2,2)
    , manager_id NUMBER (6)
    , department_id NUMBER (4)
      CONSTRAINT emp2_dept_fk REFERENCES
        departments (department_id));
desc employees2;


10.
CREATE TABLE DEPARTMENTS2 (
    department_id NUMBER (10)
      CONSTRAINT d_id_pk PRIMARY KEY
      CONSTRAINT d_id_nn NOT NULL
    , department_name VARCHAR2 (30)
      CONSTRAINT d_name_nn NOT NULL
    , manager_id NUMBER (10)
      CONSTRAINT d_man_id_fk REFERENCES
          employees2 (employee_id)
    , location_id NUMBER (10)
      CONSTRAINT d_loc_id_nn NOT NULL);
desc DEPARTMENTS2;



11.
drop table employees2;
desc employees2;


12.
drop table departments2;
desc departments2;


13.
drop table employees2;
desc employees2;

NB: mungkin Anda bingung akan hasil tampilannya...
ini contoh capture dari salah satu Query di atas...