To create a table in SQL, you use the CREATE TABLE statement. This command defines the table’s structure by specifying column names, data types, and optional constraints like primary keys or unique values. A basic example is CREATE TABLE users (id INT, name VARCHAR(50));, which creates a table named users with an integer id and a string name column (limited to 50 characters). Each column must have a data type, such as INT for integers, VARCHAR(n) for variable-length strings, DATE for dates, or BOOLEAN for true/false values. Constraints like NOT NULL can enforce rules, e.g., email VARCHAR(100) NOT NULL ensures the email column cannot be empty.
Advanced table creation includes defining primary keys, indexes, and relationships. A primary key uniquely identifies rows, often using id INT PRIMARY KEY or PRIMARY KEY (column1, column2) for composite keys. For example, CREATE TABLE products (product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2)); ensures each product has a unique ID. You can add defaults (e.g., stock_quantity INT DEFAULT 0) or auto-incrementing IDs (id INT AUTO_INCREMENT in MySQL). Foreign keys link tables: CREATE TABLE orders (order_id INT, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id)); ties orders to users via user_id. Indexes like INDEX(name) improve query speed for frequently searched columns.
Best practices include using clear naming conventions (e.g., snake_case), avoiding reserved keywords for table/column names, and choosing data types that minimize storage (e.g., SMALLINT instead of INT if values are small). Always enforce data integrity with constraints—like UNIQUE(email) to prevent duplicate emails. Tools like schema designers or ORM migrations help manage complex tables. Start simple: over-engineering with excessive columns or indexes early can complicate maintenance. For instance, a basic employees table might include id, first_name, last_name, and hire_date, with optional fields added later as needed. Test your schema with sample data to ensure it meets application requirements.