Oracle SQL Tutorials Chapter 7 ( Part 2 of 2 )

ismail tolga can

5 years ago

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