Table Definition Syntax

CREATE TABLE table_name (
    column1 data_type constraint,
    column2 data_type constraint
);

Column Naming Convensions

• Avoid using numeric-only identifiers.
• Special characters are prohibited.
• Recommended practice: Use descriptive, snake_case names like employee_id or department_name for readability.

Data Types

1. Numeric: INT for integers; NUMBER(precision, scale) for decimals (e.g., NUMBER(10,2)).
2. String:
   • CHAR(size): Fixed-length strings (e.g., CHAR(11) for phone numbers).
   • VARCHAR2(size): Variable-length strings.
3. Date/Time: DATE.
4. Large Objects:
   • CLOB: Character Large Object. Stores massive text data (up to 4GB), such as documents. Easier to query than BLOB because it contains character data.
   • BLOB: Binary Large Object. Stores binary data like images or audio (up to 4GB). Requires specialized handling.

Constraints

• Primary Key: Unique and not null. Limited to one per table.
• Unique: Values must be distinct but can be null.
• Not Null: Column must contain a value.
• Check: Validates data against a condition.

Example Usage:

gender VARCHAR2(1) CHECK (gender IN ('M', 'F')),
age NUMBER CHECK (age > 0 AND age < 200)

Setup Examples

Creating a Department Table:

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR2(20) NOT NULL,
    manager_id INT
);

Creating an Employees Table:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    full_name VARCHAR2(50) NOT NULL,
    phone_num CHAR(11) UNIQUE,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    id_card CHAR(18) UNIQUE,
    dept_id INT,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Inserting Records

INSERT INTO departments VALUES (10, 'Engineering', 5001);
INSERT INTO employees VALUES (101, 'Alice Smith', '13800138000', 'F', '110101199001011234', 10);

Deleting Data

To remove all record from a table:

DELETE FROM employees;

Querying Data

To retrieve all columns from a table:

SELECT * FROM employees;

Altering Table Structure

1. Adding a Column:

ALTER TABLE employees ADD hire_date DATE;

2. Removing a Column:

ALTER TABLE employees DROP COLUMN hire_date;

3. Modifying a Column Type:

ALTER TABLE employees MODIFY full_name VARCHAR2(100);