Basic SQL Queries Tutorial

Why Learn SQL?

SQL is the standard language for managing and manipulating relational databases. It's essential for anyone working with data, including developers, data analysts, and database administrators.

Knowing SQL allows you to retrieve, insert, update, and delete data effectively.

Prerequisites

Understanding of Databases (Conceptual): Basic idea of tables, rows, and columns.
Access to a Database (Optional but Recommended): Use tools like DB Browser for SQLite, MySQL Workbench, pgAdmin, or online SQL fiddles (e.g., sqlfiddle.com) to practice.

Let's query some data!

Chapter 1: Retrieving Data with SELECT

The `SELECT` statement is used to fetch data from one or more tables.

Select All Columns: Retrieve all columns from a table: `SELECT * FROM customers;`.
Select Specific Columns: Choose specific columns: `SELECT name, email FROM customers;`.
Filtering with WHERE: Select rows based on a condition: `SELECT name FROM customers WHERE country = 'USA';`.
Using Operators: Use operators like `=`, `>`, `<`, `>=`, `<=`, `!=` (or `<>`), `AND`, `OR`, `LIKE` (for pattern matching, e.g., `WHERE name LIKE 'J%'`).
Ordering Results: Sort the output using `ORDER BY`: `SELECT name, city FROM customers ORDER BY city ASC;` (or `DESC`).
Limiting Results: Restrict the number of rows returned: `SELECT * FROM products LIMIT 10;`.

Practice these `SELECT` variations on a sample table.

Chapter 2: Modifying Data

Besides retrieving data, SQL lets you add, change, and remove records.

Adding Data with `INSERT INTO`:

This statement adds a new row to a table. You specify the table, the columns to fill, and the corresponding values.

-- Insert a new customer
INSERT INTO customers (name, email, country)
VALUES ('Charlie', 'charlie@email.com', 'Canada');

-- If providing values for ALL columns in order, you can omit column names:
INSERT INTO customers
VALUES (4, 'David', 'david@email.com', 'UK'); -- Assuming ID is the first column

Changing Data with `UPDATE`:

This statement modifies existing rows in a table. You specify the table, the column(s) to change (`SET`), and crucially, *which* rows to change using a `WHERE` clause.

-- Update Charlie's email
UPDATE customers
SET email = 'charlie.updated@email.com'
WHERE name = 'Charlie';

-- Update multiple columns for customers in the USA
UPDATE customers
SET country = 'United States'
WHERE country = 'USA';

Warning: Omitting the `WHERE` clause in an `UPDATE` statement will modify *all* rows in the table!

Removing Data with `DELETE`:

This statement removes existing rows from a table. Again, the `WHERE` clause is essential to specify *which* rows to delete.

-- Delete the customer named David
DELETE FROM customers
WHERE name = 'David';

Warning: Omitting the `WHERE` clause in a `DELETE` statement will remove *all* rows from the table!

Conclusion & Next Steps

You now understand the basic SQL commands for querying (`SELECT`) and modifying data (`INSERT`, `UPDATE`, `DELETE`). This is the foundation for interacting with relational databases.

Further learning:

Learn about aggregate functions (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) and `GROUP BY`.
Explore `JOIN` operations to combine data from multiple tables.
Understand data types in SQL.
Learn about indexes for performance optimization.
Dive into specific database systems like PostgreSQL, MySQL, or SQLite.

See Database Tool Reviews Read Data Management Guides