Oracle SQL Tutorials Chapter 7 ( Part 2 of 2 )

CREATING TABLE WITH SUB-QUERIES

  • With this method, table is created automatically with the column types that result from a query.
  • While the table is created, data that came from the subquery is automatically transferred to new table.
  • All column names must be given in the subquery.
  • It is a technique that produces fast results.

  • Let’s create a table called hr.workers then transfer hr.employees’ data to hr.workers table.
CREATE TABLE hr.workers
AS
    SELECT * FROM hr.employees;

 

 

ALTER TABLE

Here is some alter table examples ;

  • Changing column name.
ALTER TABLE hr.employees RENAME COLUMN employee_id TO emp_id;

 

 

  • Switch table to read-only mode. When a table is in Read Ony mode, you can’t change any data of it.
ALTER TABLE hr.employees READ ONLY;
  • Let’s try a DML on this table.
DELETE FROM hr.employees;

  • Switch table to read-write mode again.
ALTER TABLE hr.employees READ WRITE;

 

 

  • Changing table name.
ALTER TABLE hr.employees RENAME TO hr.workers;

 

 

  • Adding primary key to a table : Let’s create a table without primary key and then add PK manually.
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)
);

 

  • Let’s add Primary Key to hr.employees table.
ALTER TABLE HR.EMPLOYEES ADD (CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMP_ID));

 

 

ALTER TABLE CONSTRAINT

  • By disabling constraint, the control of the constraint is removed from the table.
  • When Constraint is enabled, the control are activated again.
  • When Unique Key or Primary key enabled, the unique index is created automatically if it does not exist.

 

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

  • If there are columns on a table that you think are not used, they can be marked UNUSED instead of dropping.
  • These columns are not physically removed from the table.
  • Marked as UNUSED columns don’t appear in SQL’s and views.
  • All constraints, indexes and statistics on these columns are removed.
  • These column names can be assigned to a new column.

 

  • Let’s mark as unused the manager_id column in hr.departments table.
SELECT * FROM hr.departments ;

ALTER TABLE HR.DEPARTMENTS SET UNUSED (MANAGER_ID);