**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Creating and Managing SQLite Databases **Topic:** Create a database and tables, and insert initial data using SQLite.(Lab topic) ### Introduction In this lab topic, we will guide you through the process of creating a database and tables, and inserting initial data using SQLite. By the end of this exercise, you will have a solid understanding of how to apply SQLite concepts to real-world scenarios. ### Step 1: Create a New Database Before we create any tables or insert data, let's create a new SQLite database. Open your terminal or command prompt and type the following command: ```sql sqlite3 school.db ``` This will create a new SQLite database named `school.db` in your current directory. You can replace `school.db` with any name of your choice. ### Step 2: Create Tables Let's create three tables: `students`, `courses`, and `enrollments`. We will use the following SQL commands: ```sql -- Create the students table CREATE TABLE students ( id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE NOT NULL ); -- Create the courses table CREATE TABLE courses ( id INTEGER PRIMARY KEY, course_name TEXT NOT NULL, description TEXT ); -- Create the enrollments table CREATE TABLE enrollments ( id INTEGER PRIMARY KEY, student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, Enrollment_date DATE NOT NULL, FOREIGN KEY (student_id) REFERENCES students (id), FOREIGN KEY (course_id) REFERENCES courses (id) ); ``` ### Step 3: Insert Initial Data Now that we have created our tables, let's insert some initial data. We will use the following SQL commands: ```sql -- Insert data into the students table INSERT INTO students (first_name, last_name, email) VALUES ('John', 'Doe', 'johndoe@example.com'), ('Jane', 'Doe', 'janedoe@example.com'); -- Insert data into the courses table INSERT INTO courses (course_name, description) VALUES ('Mathematics', 'Mathematics course'), ('Science', 'Science course'); -- Insert data into the enrollments table INSERT INTO enrollments (student_id, course_id, Enrollment_date) VALUES (1, 1, '2022-01-01'), (2, 2, '2022-01-15'), (1, 2, '2022-02-01'); ``` ### Step 4: Verify the Data To verify that our data has been inserted correctly, we can use the `SELECT` statement: ```sql -- Select data from the students table SELECT * FROM students; -- Select data from the courses table SELECT * FROM courses; -- Select data from the enrollments table SELECT * FROM enrollments; ``` ### Key Concepts * Creating a new database in SQLite * Creating tables with primary keys and foreign keys * Inserting initial data into tables * Using the `SELECT` statement to verify data ### Best Practices * Always use meaningful and descriptive table and column names * Use primary keys to uniquely identify each row in a table * Use foreign keys to establish relationships between tables * Verify data after insertion to ensure accuracy ### Practical Takeaways By completing this lab, you have demonstrated your ability to: * Create a new SQLite database and tables * Define primary keys and foreign keys * Insert initial data into tables * Verify data using the `SELECT` statement ### What's Next? In the next topic, we will cover "Using SELECT statements for querying data." We will learn how to use various clauses and functions to filter and manipulate data. **Additional Resources:** * SQLite official documentation: [https://www.sqlite.org/docs.html](https://www.sqlite.org/docs.html) * SQLite tutorial by Tutorials Point: [https://www.tutorialspoint.com/sqlite/index.htm](https://www.tutorialspoint.com/sqlite/index.htm) **Do you have any questions or need help with this topic? Please ask us in the comments below.**