Lesson 1 - CRUD Operations in DB Browser

Lesson Overview

1. Introduction to SQLite and DB Browser: We'll cover what SQLite is, why it's useful, and how DB Browser makes it easier to work with.
2. Creating a Database and Tables: We'll learn how to create a new SQLite database and define the structure of your data with tables and columns.
3. CRUD Operations: This is the core of our lesson! We'll dive deep into each operation:
   • Creating records: Adding new data to your tables.
   • Reading records: Retrieving data from your tables using different queries.
   • Updating records: Modifying existing data in your tables.
   • Deleting records: Removing data from your tables.

Creating a Database and Tables

Imagine acquiring a new, empty bookcase for your quiet library. Before you can begin organising your collection, you must first prepare the shelves. In the world of SQLite, this translates to creating a database and defining its structure with tables.

Creating a Database

A database is like that bookcase – a container to hold and organise your information. In SQLite, this container is a single file, residing unobtrusively on your computer. To create this file, we'll use DB Browser for SQLite:

You've now brought your new, empty bookcase into existence. It awaits the careful arrangement of your information.

Creating Tables

Within your bookcase, you'll want to organise your books into categories. Perhaps you'll have a shelf for fiction, another for non-fiction, and another for poetry. In SQLite, these categories are called tables.

A table is a structured collection of data, organised into rows and columns. Each row represents a single item (like a book), and each column represents a specific attribute of that item (like its title, author, or genre).

Let's create a table to store information about books:

Task: Create a 'books' Table

1. In DB Browser, go to the "Create Table" tab.
2. Enter "books" as the table name.
3. Now, define the columns for your table:
• id: A unique identifier for each book (INTEGER data type, Primary Key, and check the "Autoincrement" box).
• title: The title of the book (TEXT data type).
• author: The author of the book (TEXT data type).
• genre: The genre of the book (TEXT data type).
4. Click "OK" to create the table.

You've now created your first table, akin to adding a new shelf to your bookcase and labelling it "books". It's ready to receive the details of your literary collection.

CRUD Operations

Now that we've laid the groundwork, it's time to populate our newly created database. This involves the core of database interaction: CRUD operations.

Creating Records

Imagine carefully placing books onto your newly built shelves. Each book represents a record, a single entry in your database table. To add a book to your "books" table, we'll use the "Browse Data" tab in DB Browser:

You've just added your first record to the database, akin to placing a carefully chosen book onto your shelf.

Solidify your understanding by adding ten more books to your "books" table using the straightforward interface of DB Browser. Select titles from your own collection or those you aspire to read, and observe how easily you can manage your data without writing any code. This modest practice will prepare you for further exploration of CRUD operations.

Reading Records

Having diligently populated your database, it's time to delve into the art of retrieval. In the hushed confines of our library analogy, this equates to selecting a book from its designated shelf and perusing its contents. Within DB Browser, this translates to viewing and filtering your data with quiet efficiency.

Viewing Data in DB Browser

DB Browser offers a straightforward way to view your data without the need for complex commands. To view the contents of your "books" table:

Task: View the 'books' Table

1. Navigate to the "Browse Data" tab.
2. Select the "books" table from the list.

To see the same view as the above screenshot, load the 'bookshelf.db' from the lesson resource folder.

The table's contents will be displayed, revealing the fruits of your earlier labours. Each row represents a book, and each column displays a specific attribute. Observe the unassuming orderliness with which your data is presented.

For now, familiarise yourself with the subdued elegance of DB Browser's graphical interface.

Updating Records

Even in our meticulously organised collections, change is inevitable. In our library analogy, this might involve correcting a book's details, such as its author or genre. DB Browser allows us to modify existing records with ease.

Task: Update a Book Record

To modify a record in your "books" table:

1. Go to the "Browse Data" tab.
2. Select the "books" table.
3. Locate the record and double-click the cell you wish to change.
4. Make the necessary adjustments.
5. Click "Write Changes" to save your changes.

In this example, I've updated the genre of the book to "Computer Science" instead of "Media Studies"

DB Browser facilitates these alterations with quiet efficiency.

Deleting Records

Occasionally, the need arises to remove items from our carefully curated collections. In our library analogy, this might involve discarding a worn-out volume or removing a title that no longer aligns with our interests. DB Browser provides a straightforward means to delete records from your database.

Task: Delete a Book Record

1. Go to the "Browse Data" tab.
2. Select the "books" table.
3. Locate the record you wish to remove and click on the row number to select it.
4. Click the "Delete Record" button.
5. Confirm the deletion.

DB Browser executes this task with its characteristic quiet efficiency, leaving no trace of the departed record.

Lesson Summary

In this lesson, we've embarked on our journey into the world of SQLite and DB Browser. We've learned how to create a new database, define tables, and interact with our data using CRUD operations.