Hands-on Practical: Connecting and Querying

Applying knowledge of database tool selection and connection processes, combined with SQL commands (SELECT, INSERT, UPDATE, DELETE), allows for interaction with a real, simple database environment. This practical exercise bridges the gap between understanding databases and actively using them.

Setting Up Your Practice Space: SQLite

For this exercise, we need a database system to work with. Instead of setting up a complex server, we'll use SQLite. SQLite is a fantastic choice for learning because it stores the entire database in a single file on your computer, requiring minimal setup. Many programming languages have built-in support for it, and it's freely available.

Getting SQLite:

• macOS and Linux: SQLite is often pre-installed. Open your terminal application and type sqlite3 --version. If you see a version number, you're ready.
• Windows: Download the precompiled binaries (specifically the command-line shell program) from the official SQLite Download Page. Extract the sqlite3.exe file to a convenient location. You can run it by opening Command Prompt, navigating to the directory where you saved the file, and typing sqlite3.

Creating and Connecting to a Database:

Connecting to SQLite using the command-line tool also creates the database file if it doesn't exist.

1. Open your terminal or Command Prompt.

2. Navigate to a directory where you want to store your practice database file (using cd directory_name).

3. Type the following command and press Enter:

   sqlite3 practice_db.sqlite
   

This command starts the SQLite command-line shell and connects to (or creates) a database file named practice_db.sqlite in your current directory. You should see a prompt like sqlite>. This prompt indicates you are now interacting directly with the SQLite database.

Creating Your First Table

Before we can query data, we need a place to store it. Let's create a simple table called students to hold information about students. Type the following SQL command at the sqlite> prompt and press Enter:

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    enrollment_date DATE
);

• CREATE TABLE students: This tells SQLite to create a new table named students.
• (...): The parentheses enclose the column definitions.
• student_id INTEGER PRIMARY KEY: Defines an integer column named student_id. PRIMARY KEY means this column will uniquely identify each student record, and SQLite will often automatically generate values for it if you don't provide one during insertion.
• first_name TEXT, last_name TEXT: Define text columns for the student's name.
• enrollment_date DATE: Defines a column to store the date the student enrolled.

If the command is successful, SQLite usually won't output anything, just return you to the sqlite> prompt.

Adding Data with INSERT

Now that we have a table structure, let's add some data using the INSERT statement you learned about. Execute these commands one by one:

INSERT INTO students (first_name, last_name, enrollment_date)
VALUES ('Ada', 'Lovelace', '2023-09-01');

INSERT INTO students (first_name, last_name, enrollment_date)
VALUES ('Charles', 'Babbage', '2023-09-01');

INSERT INTO students (first_name, last_name, enrollment_date)
VALUES ('Grace', 'Hopper', '2024-01-15');

Each INSERT INTO statement adds a new row (a record) to the students table with the specified values for the columns. Notice we didn't specify student_id; SQLite will likely handle that automatically because it's the primary key.

Retrieving Data with SELECT

Let's check if our data was inserted correctly. Use the SELECT statement to retrieve information.

1. Select all data: To see all columns and all rows, use *:

SELECT * FROM students;

You should see output similar to this (the exact student_id might differ):

1|Ada|Lovelace|2023-09-01
2|Charles|Babbage|2023-09-01
3|Grace|Hopper|2024-01-15

2. Select specific columns: If you only want the names, specify the columns:

SELECT first_name, last_name FROM students;

Output:

Ada|Lovelace
Charles|Babbage
Grace|Hopper

3. Filter data with WHERE: To find only students who enrolled on a specific date:

SELECT first_name, last_name FROM students WHERE enrollment_date = '2023-09-01';

Output:

Ada|Lovelace
Charles|Babbage

Modifying Data with UPDATE

Imagine Charles Babbage's enrollment date needs correction. We can use the UPDATE statement combined with WHERE to change only his record.

UPDATE students
SET enrollment_date = '2023-09-05'
WHERE first_name = 'Charles' AND last_name = 'Babbage';

Let's verify the change using SELECT:

SELECT * FROM students WHERE first_name = 'Charles';

Output (notice the updated date):

2|Charles|Babbage|2023-09-05

Removing Data with DELETE

Suppose Ada Lovelace withdraws. We can remove her record using the DELETE statement. It's important to use WHERE to specify exactly which record(s) to delete. Warning: Without a WHERE clause, DELETE FROM students; would remove all records from the table!

DELETE FROM students
WHERE first_name = 'Ada' AND last_name = 'Lovelace';

Let's check the table contents again:

SELECT * FROM students;

Output (Ada's record is gone):

2|Charles|Babbage|2023-09-05
3|Grace|Hopper|2024-01-15

Exiting SQLite

When you're finished practicing, you can exit the SQLite command-line shell by typing:

.quit

Or by pressing Ctrl+D (macOS/Linux) or Ctrl+Z then Enter (Windows). Your database file (practice_db.sqlite) remains saved in the directory where you created it.

A Note on Graphical Tools

While the command line is a direct way to interact with databases, many developers use graphical user interface (GUI) tools. Tools like DB Browser for SQLite, DBeaver, or TablePlus provide a visual way to see table structures, browse data, and write/run SQL queries in a dedicated editor window. They perform the same actions (connecting, sending SQL commands) but offer a different user experience that some find easier, especially when starting. Feel free to explore these tools as well; the SQL commands you learned remain the same.

You've now successfully connected to a database, created a table, and performed the fundamental data manipulation operations: INSERT, SELECT, UPDATE, and DELETE. This practical experience is a significant step in working effectively with databases. Continue experimenting with different queries on your sample data to build confidence and familiarity.