**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Creating and Managing SQLite Databases **Topic:** Creating and managing SQLite databases and tables In the previous topic, you learned about basic SQL commands in SQLite, including `SELECT`, `FROM`, and `WHERE`. Now, you'll dive deeper into creating and managing databases and tables, which are essential skills for any SQLite developer. By the end of this topic, you'll be able to create and manage SQLite databases and tables with confidence. **What you'll learn:** * How to create a new SQLite database * How to create a new table in a SQLite database * How to define table columns and data types * How to use PRIMARY KEY and UNIQUE constraints * How to use FOREIGN KEY constraints to establish relationships between tables * How to modify and delete tables * Best practices for managing SQLite databases and tables **Creating a New SQLite Database** To create a new SQLite database, you'll use the `.open` command in the SQLite command-line shell or the `sqlite3` command in your terminal (if you're using a Unix-based system). Here's an example: ```sql -- Open the SQLite database file sqlite3 mydatabase.db ``` If the file doesn't exist, SQLite will create a new one. You can also use the `--new` option to create a new database: ```sql sqlite3 --new mydatabase.db ``` **Creating a New Table in a SQLite Database** To create a new table in a SQLite database, you'll use the `CREATE TABLE` statement. Here's the basic syntax: ```sql CREATE TABLE table_name ( column1 data_type, column2 data_type, ... ); ``` For example: ```sql CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, department TEXT ); ``` In this example: * `id` is the primary key column, which uniquely identifies each employee. * `name` is a required text column. * `age` is an integer column. * `department` is an optional text column. **Defining Table Columns and Data Types** When creating a table, you need to define the columns and their data types. SQLite supports various data types, including: * `INTEGER`: whole numbers (e.g., 1, 2, 3) * `TEXT`: strings of characters (e.g., 'John Doe', 'New York') * `REAL`: decimal numbers (e.g., 3.14, -0.5) * `BLOB`: binary data (e.g., images, audio files) * `NULL`: unknown or undefined values Here's a more complex example: ```sql CREATE TABLE orders ( id INTEGER PRIMARY KEY, order_date DATE, customer_name TEXT NOT NULL, order_total REAL, products BLOB ); ``` In this example: * `id` is the primary key column. * `order_date` is a date column. * `customer_name` is a required text column. * `order_total` is a decimal column. * `products` is a binary column. **PRIMARY KEY and UNIQUE Constraints** PRIMARY KEY and UNIQUE constraints help maintain data integrity by ensuring that certain columns contain unique values. ```sql -- PRIMARY KEY constraint CREATE TABLE employees ( id INTEGER PRIMARY KEY, ... ); -- UNIQUE constraint CREATE TABLE employees ( id INTEGER PRIMARY KEY, email TEXT UNIQUE, ... ); ``` **FOREIGN KEY Constraints** FOREIGN KEY constraints establish relationships between tables by preventing deletions that would leave orphaned records. ```sql -- FOREIGN KEY constraint CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER, FOREIGN KEY (customer_id) REFERENCES customers(id) ); ``` **Modifying and Deleting Tables** To modify a table, you can use the `ALTER TABLE` statement. Here's an example: ```sql -- Add a new column ALTER TABLE employees ADD COLUMN phone TEXT; -- Drop a column ALTER TABLE employees DROP COLUMN phone; ``` To delete a table, you can use the `DROP TABLE` statement: ```sql -- Delete a table DROP TABLE employees; ``` **Best Practices for Managing SQLite Databases and Tables** Here are some best practices for managing SQLite databases and tables: * Use clear and descriptive table and column names. * Use PRIMARY KEY and UNIQUE constraints to maintain data integrity. * Use FOREIGN KEY constraints to establish relationships between tables. * Regularly back up your database to prevent data loss. * Use the `SELECT` statement to test your queries before modifying or deleting data. **What's Next?** Now that you've learned about creating and managing SQLite databases and tables, it's time to dive into data types in SQLite. In the next topic, you'll learn about the different data types available in SQLite and how to use them effectively. **External Resources** * [SQLite documentation: CREATE TABLE](https://www.sqlite.org/lang_createtable.html) * [SQLite documentation: ALTER TABLE](https://www.sqlite.org/lang_altertable.html) * [SQLite documentation: DROP TABLE](https://www.sqlite.org/lang_droptable.html) **Your Turn!** Do you have any questions or feedback about this topic? Leave a comment or ask for help below!