Site icon IT Tutorial

Oracle SQL Tutorials Chapter 7 ( Part 2 of 2 )

CREATING TABLE WITH SUB-QUERIES

CREATE TABLE hr.workers
AS
    SELECT * FROM hr.employees;

 

 

ALTER TABLE

Here is some alter table examples ;

ALTER TABLE hr.employees RENAME COLUMN employee_id TO emp_id;

 

 

ALTER TABLE hr.employees READ ONLY;
DELETE FROM hr.employees;

ALTER TABLE hr.employees READ WRITE;

 

 

ALTER TABLE hr.employees RENAME TO hr.workers;

 

 

CREATE TABLE HR.EMPLOYEES
(
EMP_ID             NUMBER(6),
FIRST_NAME         VARCHAR2(20 BYTE),
LAST_NAME          VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
EMAIL              VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
PHONE_NUMBER       VARCHAR2(20 BYTE),
HIRE_DATE          DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
JOB_ID             VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
SALARY             NUMBER(8,2),
COMMISSION_PCT     NUMBER(2,2),
MANAGER_ID         NUMBER(6),
DEPARTMENT_ID      NUMBER(4)
);

 

ALTER TABLE HR.EMPLOYEES ADD (CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMP_ID));

 

 

ALTER TABLE CONSTRAINT

 

To Disable a Constraint :

ALTER TABLE HR.DEPARTMENTS DISABLE CONSTRAINT DEPT_LOC_FK;

To Enable a Constraint :

ALTER TABLE HR.DEPARTMENTS ENABLE CONSTRAINT DEPT_LOC_FK;

 

ALTER TABLE – SET UNUSED

 

SELECT * FROM hr.departments ;

ALTER TABLE HR.DEPARTMENTS SET UNUSED (MANAGER_ID);

Exit mobile version