Charles Zhang for Methodox Technologies, Inc.

Posted on Jun 2

[Share] SQL - A Quick Intro

#sql #database #reference #sqlite

Originally posted on Methodox Wiki.

Overview

In this article, we’ll walk through the essentials of SQL using the SQLite dialect. We’ll start by creating a couple of sample tables and populating them with data. These tables will serve as the foundation for our examples in later sections. By the end of this overview, you will have a simple schema to work with and understand how to set the scene for common SQL operations.

-- Create a table for users
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    joined_date TEXT NOT NULL  -- stored as ISO-8601 strings in SQLite
);

-- Create a table for orders
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert sample data into users
INSERT INTO users (username, email, joined_date) VALUES
    ('alice', '[email protected]', '2023-01-15'),
    ('bob', '[email protected]', '2023-02-10'),
    ('carol', '[email protected]', '2023-03-05');

-- Insert sample data into orders
INSERT INTO orders (user_id, product, quantity, order_date) VALUES
    (1, 'Keyboard', 2, '2023-04-01'),
    (1, 'Mouse', 1, '2023-04-07'),
    (2, 'Monitor', 1, '2023-04-15'),
    (3, 'Desk Lamp', 3, '2023-05-02'),
    (2, 'USB Cable', 5, '2023-05-10');

These two tables - users and orders - will be the basis for our queries. Feel free to run these statements in an SQLite environment (e.g., the sqlite3 CLI or any GUI tool) to experiment with the examples that follow.

Common Syntax

In this section, we’ll introduce the four most common SQL statements:

SELECT
UPDATE
DELETE
INSERT

We’ll focus on the typical usage patterns for each statement without covering every possible option or keyword. All SQL keywords are shown in uppercase by convention - this is a good practice. However, they are usually case-insensitive to the SQL engine.

1. SELECT

The SELECT statement is used to retrieve data from one or more tables. At its simplest, you specify the columns you want and the table from which to retrieve them:

SELECT id, username, email
FROM users;

If you want all columns, use *:

SELECT *
FROM orders;

You can filter results using WHERE:

SELECT username, email
FROM users
WHERE joined_date >= '2023-02-01';

To sort results, use ORDER BY:

SELECT id, product, quantity, order_date
FROM orders
WHERE quantity > 1
ORDER BY order_date DESC;

Limit the number of rows returned:

SELECT *
FROM orders
LIMIT 3;

Combine clauses:

SELECT username, email
FROM users
WHERE username LIKE 'a%'
ORDER BY joined_date;

The LIKE keyword here matches all names starting with an a.

2. UPDATE

Use UPDATE to modify existing rows. Always include a WHERE clause to avoid updating every row unintentionally:

-- Change Bob's email address
UPDATE users
SET email = '[email protected]'
WHERE username = 'bob';

You can update multiple columns at once:

UPDATE users
SET email = '[email protected]',
    joined_date = '2023-01-20'
WHERE username = 'alice';

It’s good practice to run a SELECT with the same WHERE first:

SELECT * FROM users WHERE username = 'alice';
-- Review the row, then:
UPDATE users
SET email = '[email protected]'
WHERE username = 'alice';

3. DELETE

The DELETE statement removes rows from a table. Again, a WHERE clause is essential:

-- Delete Carol's record
DELETE FROM users
WHERE username = 'carol';

To delete all rows in a table (but keep the table structure):

DELETE FROM orders;

SQLite supports DELETE FROM table_name; without WHERE to remove every row. If you only want to remove specific rows:

DELETE FROM orders
WHERE order_date < '2023-04-01';

4. INSERT

INSERT adds new rows into a table. We already used this in the overview to populate users and orders. The basic syntax specifies columns and values:

INSERT INTO users (username, email, joined_date)
VALUES ('dave', '[email protected]', '2023-06-01');

You can omit the column list if you supply values for every column in the exact order:

INSERT INTO orders VALUES (NULL, 4, 'Webcam', 2, '2023-06-02');
-- NULL for id since it's an AUTOINCREMENT primary key

If you want to insert from another table (a simple form of SELECT inside INSERT):

INSERT INTO orders (user_id, product, quantity, order_date)
SELECT id, 'Headphones', 1, '2023-06-05'
FROM users
WHERE username = 'bob';

Techniques

The examples above covered basic CRUD operations. In this section, we’ll focus on more advanced - but frequently used - SELECT patterns in SQLite, including implicit joins, explicit joins, Common Table Expressions (WITH), and subqueries. These techniques are particularly helpful when you need to combine data from multiple tables or derive aggregated results.

1. Implicit Join Using Multiple Tables in FROM

An implicit join (sometimes called a “comma join”) lists multiple tables in the FROM clause and specifies the join condition in WHERE. This style is concise but can be less clear for complex queries:

-- List every order along with the username of the user who placed it
SELECT u.username, o.product, o.quantity, o.order_date
FROM users u, orders o
WHERE u.id = o.user_id;

Here, u is an alias for users and o is an alias for orders. The join condition u.id = o.user_id ties rows in users to matching rows in orders.

You can add filters:

-- Only orders with quantity > 1
SELECT u.username, o.product, o.quantity
FROM users u, orders o
WHERE u.id = o.user_id
  AND o.quantity > 1
ORDER BY o.order_date;

2. Explicit Join Syntax (INNER JOIN, LEFT JOIN)

SQLite supports the standard explicit JOIN syntax, which is recommended for clarity:

-- INNER JOIN retrieves only matching rows
SELECT u.username, o.product, o.order_date
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id;

For a left (or left outer) join, which returns all rows from the left table and matching rows (if any) from the right table:

-- List all users, including those who have not placed orders
SELECT u.username, o.product, o.order_date
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id;

If a user has no matching orders, the product and order_date columns will be NULL.

Multiple Joins
When you have more than two tables, chain the joins together:

-- Suppose we add a table for payment info
CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    payment_date TEXT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Sample data
INSERT INTO payments (order_id, amount, payment_date) VALUES
    (1, 49.98, '2023-04-02'),
    (2, 19.99, '2023-04-08'),
    (3, 149.99, '2023-04-16');

-- Query: find username, product, and payment amount for each paid order
SELECT u.username,
       o.product,
       p.amount,
       p.payment_date
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id
INNER JOIN payments p
    ON o.id = p.order_id
ORDER BY p.payment_date;

3. WITH Clause (Common Table Expressions)

A Common Table Expression (CTE) defined by WITH allows you to create a temporary, named result set that you can reference in a subsequent query. This is useful for breaking complex queries into more readable pieces.

-- Example: Find users who have spent more than $50 in total
WITH user_totals AS (
    SELECT u.id AS user_id,
           u.username,
           SUM(p.amount) AS total_spent
    FROM users u
    INNER JOIN orders o
        ON u.id = o.user_id
    INNER JOIN payments p
        ON o.id = p.order_id
    GROUP BY u.id, u.username
)
SELECT username, total_spent
FROM user_totals
WHERE total_spent > 50;

In this example:

The CTE named user_totals aggregates total spending per user.
The outer SELECT filters users based on their aggregated spending.

CTEs can be recursive as well, but for a quick intro, focus on non-recursive CTEs.

4. Subqueries

Subqueries (also called inner queries or nested queries) are queries embedded inside other SQL statements. They can appear in the SELECT list, FROM clause, or WHERE clause.

4.1 Subquery in SELECT List

-- Add a column showing the number of orders each user has placed
SELECT u.username,
       u.email,
       (
           SELECT COUNT(*)
           FROM orders o
           WHERE o.user_id = u.id
       ) AS order_count
FROM users u;

The subquery (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) executes for each row in users.

4.2 Subquery in WHERE Clause

-- Find users who have never placed an order
SELECT username, email
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM orders
);

Alternatively, using NOT EXISTS:

SELECT username, email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

4.3 Subquery in FROM Clause

-- Join users to a derived table that calculates order counts
SELECT t.username, t.order_count
FROM (
    SELECT u.id AS user_id,
           u.username,
           COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o
        ON u.id = o.user_id
    GROUP BY u.id, u.username
) AS t
WHERE t.order_count >= 2;

Here, the subquery in the FROM clause (aliased as t) computes the number of orders per user. The outer query then filters on order_count.

5. Aggregation and GROUP BY

While technically a part of SELECT patterns, aggregation is so common that it merits a dedicated mention:

-- Total quantity ordered per product
SELECT product,
       SUM(quantity) AS total_quantity
FROM orders
GROUP BY product
ORDER BY total_quantity DESC;

HAVING

To filter on aggregated values, use HAVING rather than WHERE:

-- Only products with total quantity > 2
SELECT product,
       SUM(quantity) AS total_quantity
FROM orders
GROUP BY product
HAVING total_quantity > 2;

6. Putting It All Together

Let’s combine a few of these techniques into a more comprehensive example:

We want to list each user, their total number of orders, total quantity ordered, and their most recent order date.
Only include users who have placed at least one order.
Sort by total quantity descending.

WITH user_orders AS (
    SELECT u.id    AS user_id,
           u.username,
           COUNT(o.id)           AS order_count,
           SUM(o.quantity)       AS total_quantity,
           MAX(o.order_date)     AS last_order_date
    FROM users u
    INNER JOIN orders o
        ON u.id = o.user_id
    GROUP BY u.id, u.username
)
SELECT username,
       order_count,
       total_quantity,
       last_order_date
FROM user_orders
WHERE order_count >= 1
ORDER BY total_quantity DESC;

Explanation:

The CTE user_orders aggregates order-related metrics per user.
The outer query filters out users with zero orders and sorts the results.

Putting It into Practice

Here are a few additional examples and variations you might find useful in everyday data analysis with SQLite:

Top 3 Users by Total Spending Combine payments and orders to rank users.

   WITH spending AS (
       SELECT u.id         AS user_id,
              u.username,
              SUM(p.amount) AS total_spent
       FROM users u
       INNER JOIN orders o
           ON u.id = o.user_id
       INNER JOIN payments p
           ON o.id = p.order_id
       GROUP BY u.id, u.username
   )
   SELECT username, total_spent
   FROM spending
   ORDER BY total_spent DESC
   LIMIT 3;

Find Orders with No Payment Records Use a left join and look for NULL in the payment side.

   SELECT o.id AS order_id,
          u.username,
          o.product,
          o.order_date
   FROM orders o
   LEFT JOIN payments p
       ON o.id = p.order_id
   INNER JOIN users u
       ON o.user_id = u.id
   WHERE p.id IS NULL;

Update via Subquery Suppose we want to mark users who joined before a certain date as “legacy” in a new column. First, add a column:

   ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

   UPDATE users
   SET status = 'legacy'
   WHERE joined_date < '2023-02-01';

Delete Based on a Joined Condition If you needed to delete payments for orders that were canceled (imagine a hypothetical orders.status = 'canceled' column), you could write:

   DELETE FROM payments
   WHERE order_id IN (
       SELECT id
       FROM orders
       WHERE status = 'canceled'
   );

Nested CTEs Chain multiple CTEs to break down a complex problem:

   WITH recent_orders AS (
       SELECT *
       FROM orders
       WHERE order_date >= '2023-04-01'
   ),
   recent_payments AS (
       SELECT o.id     AS order_id,
              o.user_id,
              p.amount,
              p.payment_date
       FROM recent_orders o
       INNER JOIN payments p
           ON o.id = p.order_id
   )
   SELECT u.username,
          SUM(rp.amount) AS recent_spending
   FROM users u
   INNER JOIN recent_payments rp
       ON u.id = rp.user_id
   GROUP BY u.username
   ORDER BY recent_spending DESC;

Conclusion

This article has introduced the basics of SQL using the SQLite dialect, focusing on the most common statements - SELECT, UPDATE, DELETE, and INSERT - as well as several useful SELECT techniques. You now have a schema with users, orders, and payments to experiment with, and you’ve seen how to:

Create tables and insert data.
Perform simple CRUD operations.
Join tables both implicitly and explicitly.
Use Common Table Expressions (WITH).
Write subqueries in various parts of a statement.
Aggregate data and filter with HAVING.

These examples should provide a solid foundation for everyday data analysis tasks. As you become more comfortable, you can explore advanced topics like window functions, triggers, and indexing strategies in SQLite.

DEV Community