Table

1. Creating a Table

CREATE TABLE "student" (
    matrik_no VARCHAR2(20) NOT NULL,
    std_name VARCHAR2(150) NOT NULL,
    ic_no VARCHAR2(12),
    PRIMARY KEY (matrik_no)
);

2. Dropping a Table

DROP TABLE "student";

DROP TABLE "student" CASCADE CONSTRAINTS;

3. Altering a Table

Add a Column:

ALTER TABLE table_name ADD column_name datatype;

Modify a Column (e.g., change data type or size):

ALTER TABLE table_name MODIFY column_name new_datatype;

Drop a Column:

ALTER TABLE table_name DROP COLUMN column_name;

4. Renaming a Table or Column

Rename Table:

RENAME old_table_name TO new_table_name;

Rename Column (only in Oracle 12c and later):

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

5. Truncating a Table

Deletes all rows from a table without dropping the table itself.

TRUNCATE TABLE table_name;

6. Adding Constraints

Adds constraints like primary keys, foreign keys, unique constraints, etc.

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

Example: Adding a primary key constraint:

ALTER TABLE table_name ADD CONSTRAINT pk_column PRIMARY KEY (column_name);

7. Viewing Table Structure

Displays the columns, data types, and constraints of a table.

DESC table_name;

8. Creating Indexes

Speeds up data retrieval on specified columns.

CREATE INDEX index_name ON table_name(column_name);

9. Analyzing Table Size and Statistics

Check Row Count:

SELECT COUNT(*) FROM table_name;

Gather Statistics (for optimizer efficiency):

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');

10. Granting and Revoking Privileges

Grant Access to Another User:

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO user_name;

Revoke Access:

REVOKE SELECT, INSERT, UPDATE, DELETE ON table_name FROM user_name;