Cracking the Code: Your First Steps with SQL! 💻

This content originally appeared on DEV Community and was authored by Joseph utuedeye

Alright, future database whiz! We’ve learned why databases are essential and that they store data in an organized way. Now, let’s talk about how we actually talk to these organized databases. That’s where SQL comes in!

What is SQL? (The Language of Databases)

Imagine you’re at a very organized, digital library that stores information in those neat tables we discussed. How do you ask for a book? How do you add a new one? You can’t just shout or type in regular English. You need a specific language the librarian (our DBMS) understands.

SQL stands for Structured Query Language. It’s the standard language used to communicate with and manage relational databases. It’s not a programming language like Python that builds entire applications, but rather a special-purpose language designed specifically for interacting with databases.

Think of it as the universal “command language” for database operations. If you know SQL, you can talk to almost any relational database (like PostgreSQL, MySQL, SQLite, SQL Server, Oracle, etc.), even though they might have tiny dialect differences.

What Can You Do with SQL? (The Four Main Actions)

Just like HTTP methods have their main actions (GET, POST, PUT, DELETE), SQL also has four primary categories of commands, often aligning with CRUD:

Retrieving Data (READ): SELECT
Adding New Data (CREATE): INSERT
Changing Existing Data (UPDATE): UPDATE
Removing Data (DELETE): DELETE

Let’s look at each one with simple examples. Imagine we have a table called Books that looks like this:

id	title	author	publication_year
1	The Hitchhiker’s Guide to the Galaxy	Douglas Adams	1979
2	Pride and Prejudice	Jane Austen	1813

1. `SELECT`: Asking the Database Questions (READ)

This is the most common SQL command. It’s how you ask the database to give you data. You specify what columns you want to see and from which table.

Analogy: “Show me all the books.” or “Show me just the titles of books published after 2000.”
Syntax Basics: SELECT column1, column2 FROM table_name WHERE condition;
Examples:
- Get all columns from all rows:
```
SELECT * FROM Books;
```
  (The `` means “all columns”)*
- Get only the title and author of all books:
```
SELECT title, author FROM Books;
```
- Get the title and author of the book with id 2:
```
SELECT title, author FROM Books WHERE id = 2;
```
  (The WHERE clause filters the rows based on a condition)
- Get books by a specific author:
```
SELECT * FROM Books WHERE author = 'Douglas Adams';
```

2. `INSERT INTO`: Adding New Rows (CREATE)

This command allows you to add new records (rows) into a table.

Analogy: “Add a new book to the library’s collection.”
Syntax Basics: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example:
- Add a new book to the Books table:
```
INSERT INTO Books (id, title, author, publication_year)
VALUES (3, '1984', 'George Orwell', 1949);
```
  Now our Books table would look like this:
  
  id title author publication_year
  
  1 The Hitchhiker’s Guide to the Galaxy Douglas Adams 1979
  
  2 Pride and Prejudice Jane Austen 1813
  
  3 1984 George Orwell 1949

3. `UPDATE`: Changing Existing Data (UPDATE)

This command is used to modify existing records in a table. You typically specify which table, what to change, and which specific rows to change using a WHERE clause.

Analogy: “Change the publication year for ‘Pride and Prejudice’.”
Syntax Basics: UPDATE table_name SET column1 = new_value1 WHERE condition;
Example:
- Update the publication_year for “The Hitchhiker’s Guide to the Galaxy”:
```
UPDATE Books
SET publication_year = 1980
WHERE id = 1;
```
  Now Books table:
  
  id title author publication_year
  
  1 The Hitchhiker’s Guide to the Galaxy Douglas Adams 1980
  
  2 Pride and Prejudice Jane Austen 1813
  
  3 1984 George Orwell 1949
CRITICAL WARNING: Always use a WHERE clause with UPDATE! If you forget it, the UPDATE command will change all rows in the table!

id	title	author	publication_year
1	The Hitchhiker’s Guide to the Galaxy	Douglas Adams	1980
2	Pride and Prejudice	Jane Austen	1813
3	1984	George Orwell	1949

4. `DELETE FROM`: Removing Rows (DELETE)

This command is used to remove existing records (rows) from a table. Just like UPDATE, you use a WHERE clause to specify which rows to delete.

Analogy: “Remove the book ‘1984’ from the collection.”
Syntax Basics: DELETE FROM table_name WHERE condition;
Example:
- Delete the book with id 3:
```
DELETE FROM Books WHERE id = 3;
```
  Now Books table:
  
  id title author publication_year
  
  1 The Hitchhiker’s Guide to the Galaxy Douglas Adams 1980
  
  2 Pride and Prejudice Jane Austen 1813
CRITICAL WARNING: Always use a WHERE clause with DELETE! If you forget it, the DELETE command will wipe out all rows in the table!

id	title	author	publication_year
1	The Hitchhiker’s Guide to the Galaxy	Douglas Adams	1980
2	Pride and Prejudice	Jane Austen	1813

Other Important SQL Commands (Beyond CRUD)

Besides CRUD, SQL also has commands for managing the database structure itself:

CREATE TABLE: To create a new table (e.g., CREATE TABLE Users (id INT PRIMARY KEY, name VARCHAR(255));)
ALTER TABLE: To modify an existing table (e.g., add a new column).
DROP TABLE: To completely delete a table. (Use with extreme caution!)

SQL and Your FastAPI Backend: The Connection!

So, how does this all tie back to FastAPI?

When you build a backend application, your FastAPI code won’t typically be typing SQL commands directly into a terminal. Instead, your Python code will use special libraries (like SQLAlchemy which we’ll explore soon!) that allow you to write Python code that generates and executes these SQL commands behind the scenes.

This means your FastAPI endpoints will receive HTTP requests (GET, POST, etc.), then translate those requests into the appropriate SQL commands to talk to your database, and finally send the database’s response back to the client.

Getting Started with SQL Practice

You don’t need a full FastAPI app to practice SQL. There are many online SQL editors or even local tools like DB Browser for SQLite that let you create tables and run these commands directly. Try creating a simple table for Products and practice INSERT, SELECT, UPDATE, and DELETE queries on it!

Understanding SQL is a fundamental skill for any backend developer. It’s the bedrock of how data is managed in countless applications worldwide. You’re now speaking the language of data!