SQL for Beginners: SELECT, WHERE, JOIN, and GROUP BY Explained

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational databases. Every web application, mobile app, and data pipeline uses SQL in some form.

Whether you use PostgreSQL, MySQL, SQLite, or SQL Server, the core SQL syntax is the same.

SELECT: Reading Data

The most basic SQL operation — reading data from a table:

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Alias columns
SELECT name AS full_name, email AS contact FROM users;

-- Distinct values (remove duplicates)
SELECT DISTINCT city FROM users;

-- Limit results
SELECT * FROM users LIMIT 10;

-- Skip rows (offset)
SELECT * FROM users LIMIT 10 OFFSET 20;

WHERE: Filtering Data

-- Exact match
SELECT * FROM users WHERE status = 'active';

-- Not equal
SELECT * FROM users WHERE role != 'admin';

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 50 AND price <= 200;
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Pattern matching (LIKE)
SELECT * FROM users WHERE name LIKE 'A%';      -- Starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Ends with @gmail.com
SELECT * FROM users WHERE name LIKE '_lice';    -- Second char onwards is "lice"

-- IN (multiple values)
SELECT * FROM users WHERE role IN ('admin', 'moderator', 'editor');

-- Combining conditions
SELECT * FROM users
WHERE status = 'active'
  AND (role = 'admin' OR role = 'moderator')
  AND created_at > '2024-01-01';

ORDER BY: Sorting

-- Ascending (default)
SELECT * FROM users ORDER BY name;
SELECT * FROM users ORDER BY name ASC;

-- Descending
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple columns
SELECT * FROM users ORDER BY role ASC, name ASC;

-- Sort by column position
SELECT name, email FROM users ORDER BY 1; -- Sort by first column (name)

Aggregate Functions

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 'active';
SELECT COUNT(DISTINCT city) FROM users;

-- Sum, Average, Min, Max
SELECT SUM(amount) FROM orders;
SELECT AVG(price) FROM products;
SELECT MIN(price), MAX(price) FROM products;

-- Round
SELECT ROUND(AVG(price), 2) FROM products;

GROUP BY: Grouping Data

-- Count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- Total sales per month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS total_sales,
  COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- HAVING: Filter groups (like WHERE but for aggregates)
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role
HAVING COUNT(*) > 10;

JOINs

JOINs combine rows from multiple tables. See our detailed guide: SQL Joins Explained Visually

-- INNER JOIN: Only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: All from left + matching from right
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Multiple JOINs
SELECT
  u.name,
  o.id AS order_id,
  p.name AS product_name,
  oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

Subqueries

-- Subquery in WHERE
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE total > 1000
);

-- Subquery in FROM
SELECT avg_total
FROM (
  SELECT user_id, AVG(total) AS avg_total
  FROM orders
  GROUP BY user_id
) AS user_averages
WHERE avg_total > 500;

-- Correlated subquery
SELECT u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

INSERT, UPDATE, DELETE

-- Insert
INSERT INTO users (name, email, role)
VALUES ('Alice', 'alice@example.com', 'user');

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

-- Update
UPDATE users SET role = 'admin' WHERE id = 1;
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- Delete
DELETE FROM users WHERE id = 1;
DELETE FROM orders WHERE created_at < '2023-01-01';

Practical Exercises

Try these queries with our SQL Formatter to format them properly:

1. Find the top 5 customers by total spending
2. List products that have never been ordered
3. Calculate monthly revenue for the past year
4. Find users who signed up but never placed an order

Related Tools & Tutorials

• SQL Formatter — Format and beautify SQL queries
• JSON to SQL — Convert JSON data to SQL INSERT statements
• CSV to SQL — Convert CSV to SQL statements
• SQL Joins Explained Visually — Visual JOIN guide
• Fake Data Generator — Generate test data for SQL practice