Table Operations

## Learning Objectives - Create and modify tables - Add and modify columns - Use indexes effectively - Drop tables safely ## CREATE TABLE ### Basic Syntax ```sql CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, hire_date DATE, salary DECIMAL(10, 2) ); ``` ### Data Types | Type | Description | |------|-------------| | INTEGER | Whole numbers | | REAL | Decimal numbers | | TEXT | Character strings | | DATE | Date values | | DATETIME | Date and time | | BOOLEAN | True/false | | BLOB | Binary data | ### Database-Specific Types ```sql -- MySQL VARCHAR(255) INT BIGINT DECIMAL(10,2) -- PostgreSQL VARCHAR(255) INT BIGSERIAL NUMERIC(10,2) SERIAL -- SQL Server VARCHAR(255) INT DECIMAL(10,2) ``` ## ALTER TABLE ### Add Column ```sql ALTER TABLE employees ADD COLUMN phone VARCHAR(20); ``` ### Add Multiple Columns ```sql ALTER TABLE employees ADD COLUMN phone VARCHAR(20), ADD COLUMN address TEXT; ``` ### Drop Column ```sql ALTER TABLE employees DROP COLUMN phone; ``` ### Modify Column ```sql -- PostgreSQL ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(100); -- MySQL ALTER TABLE employees MODIFY COLUMN email VARCHAR(100); -- SQL Server ALTER TABLE employees ALTER COLUMN email VARCHAR(100); ``` ### Rename Column ```sql -- PostgreSQL ALTER TABLE employees RENAME COLUMN first_name TO given_name; -- MySQL ALTER TABLE employees CHANGE first_name given_name VARCHAR(50); ``` ### Rename Table ```sql ALTER TABLE employees RENAME TO staff; ``` ## DROP TABLE ### Basic Drop ```sql DROP TABLE employees; ``` ### Drop If Exists ```sql DROP TABLE IF EXISTS employees; ``` ### CASCADE ```sql -- Drop and dependent objects DROP TABLE employees CASCADE; ``` ### Safety First ```sql -- Always check dependencies first SELECT * FROM information_schema.table_constraints WHERE table_name = 'employees'; ``` ## Indexes ### Create Index ```sql CREATE INDEX idx_department ON employees(department); ``` ### Composite Index ```sql CREATE INDEX idx_dept_salary ON employees(department, salary DESC); ``` ### UNIQUE Index ```sql CREATE UNIQUE INDEX idx_email ON employees(email); ``` ### When to Index - Columns used in WHERE clauses - Columns used in JOIN conditions - Columns used in ORDER BY - Columns with high cardinality (many unique values) ### Drop Index ```sql DROP INDEX idx_department; ``` ## CREATE TABLE ... AS ### Copy Table Structure and Data ```sql CREATE TABLE employees_backup AS SELECT * FROM employees; ``` ### Copy with Filter ```sql CREATE TABLE sales_employees AS SELECT * FROM employees WHERE department = 'Sales'; ``` ### Structure Only (No Data) ```sql CREATE TABLE employees_archive AS SELECT * FROM employees WHERE 1 = 0; ``` ## TRUNCATE ### Remove All Rows ```sql TRUNCATE TABLE employees; ``` ### Faster than DELETE - No transaction log per row - Cannot be rolled back - Resets auto-increment counters ### CASCADE with TRUNCATE ```sql TRUNCATE TABLE employees CASCADE; ``` ## Temporary Tables ### Session Table ```sql CREATE TEMPORARY TABLE temp_results ( id INTEGER, name TEXT ); -- Auto-deleted when session ends ``` ### PostgreSQL ```sql CREATE TEMP TABLE temp_data AS SELECT * FROM source_table WHERE condition; ``` ## Column Constraints ### NOT NULL ```sql CREATE TABLE example ( name TEXT NOT NULL ); ``` ### UNIQUE ```sql CREATE TABLE example ( email TEXT UNIQUE ); ``` ### PRIMARY KEY ```sql CREATE TABLE example ( id INTEGER PRIMARY KEY, name TEXT ); ``` ### DEFAULT ```sql CREATE TABLE example ( created_at DATE DEFAULT CURRENT_DATE, status TEXT DEFAULT 'active' ); ``` ### CHECK ```sql CREATE TABLE example ( age INTEGER CHECK (age >= 0), salary DECIMAL CHECK (salary > 0) ); ``` ## Summary - **CREATE TABLE**: Define new tables with columns and constraints - **ALTER TABLE**: Modify existing table structure - **DROP TABLE**: Remove tables (use IF EXISTS) - **Indexes**: Speed up queries on large tables - **TRUNCATE**: Fast way to delete all rows - **Temporary tables**: Session-scoped storage - Use **constraints** to enforce data integrity