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);