**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Creating and Managing SQLite Databases **Topic:** Using CREATE TABLE, ALTER TABLE, and DROP TABLE. In this topic, we will explore the essential SQL commands for creating, modifying, and deleting tables in an SQLite database. These commands are fundamental to mastering SQLite and are used extensively in database management. ### 1. CREATE TABLE The `CREATE TABLE` command is used to create a new table in an SQLite database. The basic syntax for creating a table is as follows: ```sql CREATE TABLE table_name ( column1 data_type, column2 data_type, column3 data_type, .... ); ``` Here, `table_name` is the name of the new table, and `column1`, `column2`, `column3`, etc., are the names of the columns in the table, followed by their respective data types. **Example:** Create a table named `employees` with columns for employee ID, name, department, and salary. ```sql CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, name TEXT NOT NULL, department TEXT, salary REAL ); ``` In this example, we created a table named `employees` with four columns: `employee_id` (integer), `name` (text), `department` (text), and `salary` (real). The `employee_id` column is defined as the primary key, which uniquely identifies each row in the table. For more information on primary keys, see the next topic, "Best practices for defining primary keys and foreign keys in SQLite." **Key Concepts:** * Use meaningful and descriptive table and column names. * Choose the most suitable data type for each column. * Define primary keys to uniquely identify rows in a table. ### 2. ALTER TABLE The `ALTER TABLE` command is used to modify the structure of an existing table in an SQLite database. The basic syntax for altering a table is as follows: ```sql ALTER TABLE table_name action; ``` Here, `table_name` is the name of the table to be modified, and `action` specifies the modification to be made. **Example:** Add a new column named `email` to the existing `employees` table. ```sql ALTER TABLE employees ADD COLUMN email TEXT; ``` In this example, we added a new column named `email` with a text data type to the existing `employees` table. Other actions that can be performed with the `ALTER TABLE` command include: * **Renaming a table:** `ALTER TABLE table_name RENAME TO new_table_name;` * **Renaming a column:** `ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;` * **Dropping a column:** `ALTER TABLE table_name DROP COLUMN column_name;` **Note:** SQLite has some limitations when it comes to modifying existing tables. For example, SQLite does not support dropping or renaming a column in a table that has foreign key constraints. For more information, see the official SQLite documentation: [ALTER TABLE](https://www.sqlite.org/lang_altertable.html). **Key Concepts:** * Use the `ALTER TABLE` command to modify the structure of existing tables. * Be cautious when modifying tables with existing data or foreign key constraints. ### 3. DROP TABLE The `DROP TABLE` command is used to delete a table from an SQLite database. The basic syntax for dropping a table is as follows: ```sql DROP TABLE table_name; ``` Here, `table_name` is the name of the table to be deleted. **Example:** Delete the `employees` table from the database. ```sql DROP TABLE employees; ``` In this example, we deleted the `employees` table from the database. Note that this operation is irreversible and will delete all data in the table. **Note:** If the table does not exist, SQLite will raise an error. To avoid this, you can use the `IF EXISTS` clause with the `DROP TABLE` command: ```sql DROP TABLE IF EXISTS table_name; ``` This will drop the table only if it exists, avoiding any potential errors. **Key Concepts:** * Use the `DROP TABLE` command to delete tables. * Be cautious when deleting tables, as this operation is irreversible. ### Practical Takeaways: * Use the `CREATE TABLE` command to create new tables with meaningful and descriptive table and column names. * Use the `ALTER TABLE` command to modify the structure of existing tables, being cautious of limitations and potential errors. * Use the `DROP TABLE` command to delete tables, being mindful of the irreversible nature of this operation. **What's Next:** In the next topic, we will explore best practices for defining primary keys and foreign keys in SQLite, which are essential for maintaining data integrity and relationships between tables. **Leave a Comment/Ask for Help:** If you have any questions or need further clarification on any of the concepts or examples presented in this topic, feel free to leave a comment below.

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ....
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    salary REAL
);

ALTER TABLE table_name action;

ALTER TABLE employees
ADD COLUMN email TEXT;

DROP TABLE table_name;

DROP TABLE employees;

DROP TABLE IF EXISTS table_name;