Structured Query Language (SQL) Fundamentals

TL;DR

SQL is a standard language used to manage and manipulate relational databases. You'll use it to retrieve, insert, update, and delete data efficiently. Understanding its core commands is essential for interacting with almost any modern database.

1. The Mental Model

Think of a database as a collection of organized tables, much like spreadsheets. SQL is the set of instructions you give the database to ask questions about these tables or change the information within them.

2. The Core Material

SQL lets you perform four main actions, often called CRUD operations: Create, Read, Update, and Delete. These actions are handled by specific SQL commands.

SELECT: Reading Data

Photo by SHVETS production on Pexels

This is how you retrieve information from a database. You specify which columns you want to see and from which table. You can also filter results using a WHERE clause.

SELECT column1, column2
FROM your_table_name
WHERE condition;

• SELECT *: Retrieves all columns.
• FROM: Specifies the table you're querying.
• WHERE: Filters rows based on a condition (e.g., age > 30, city = 'London').

INSERT: Adding New Data

Photo by Aleksander Dumała on Pexels

Use INSERT to add new rows (records) into a table.

INSERT INTO your_table_name (column1, column2, column3)
VALUES (value1, value2, value3);

• You can omit the column list if you provide values for all columns in the correct order.

UPDATE: Changing Existing Data

Photo by Miguel Á. Padriñán on Pexels

UPDATE modifies existing records in one or more rows. Be careful with UPDATE without a WHERE clause, as it will affect all rows.

UPDATE your_table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

DELETE: Removing Data

Photo by Miguel Á. Padriñán on Pexels

DELETE removes rows from a table. Again, using DELETE without a WHERE clause will remove all rows from the table.

DELETE FROM your_table_name
WHERE condition;

Basic SQL Query Flow

graph TD
    A["Start Query"] --> B{"What data do you want?"};
    B --> C["SELECT columns"];
    C --> D["FROM table"];
    D --> E{"Do you need to filter?"};
    E -- Yes --> F["WHERE conditions"];
    E -- No --> G["End Query"];
    F --> G;

3. Worked Example

Let's say you have a table called Employees with columns EmployeeID, FirstName, LastName, and Department.

1. Create the table (just for context, you'd usually have this already):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

2. Insert some data:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'Alice', 'Smith', 'HR');

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (2, 'Bob', 'Johnson', 'IT');

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (3, 'Charlie', 'Brown', 'HR');

3. Retrieve all employees in the 'HR' department:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR';

This would return:

EmployeeID | FirstName | LastName
---------------------------------
1          | Alice     | Smith
3          | Charlie   | Brown

4. Update Bob Johnson's department to 'Marketing':

UPDATE Employees
SET Department = 'Marketing'
WHERE FirstName = 'Bob' AND LastName = 'Johnson';

5. Delete Charlie Brown from the table:

DELETE FROM Employees
WHERE EmployeeID = 3;

4. Key Takeaways

• SQL uses distinct commands (SELECT, INSERT, UPDATE, DELETE) for different data operations.
• The FROM clause always specifies the table you're working with.
• The WHERE clause is crucial for filtering and targeting specific rows for operations.
• Always be careful with UPDATE and DELETE commands, especially without a WHERE clause.
• SQL is case-insensitive for keywords (e.g., SELECT is the same as select), but table and column names might be case-sensitive depending on the database.

Common Mistakes to Avoid:
- Forgetting a WHERE clause in UPDATE or DELETE, leading to unintended widespread changes or deletions.
- Misspelling table or column names, resulting in errors.
- Using single quotes for numeric values or forgetting them for text values.
- Not committing changes in some database systems after INSERT, UPDATE, or DELETE.

5. Now Try It

Connect to a local SQLite database (you can use an online SQL sandbox if you prefer). Create a table called Products with columns ProductID (integer, primary key), ProductName (text), and Price (real number). Insert at least three different products. Then, write a query to display only the products that cost more than $20.00.