Writing Basic SQL Queries

The SELECT Statement

The SELECT statement is the foundation of all data retrieval in SQL. Every analytical query starts with SELECT.

Selecting All Columns

SELECT *
FROM orders;

The asterisk (*) returns all columns. Useful for exploring a new table but should be avoided in production queries where you know which columns you need.

Selecting Specific Columns

SELECT order_id, customer_id, order_date, total_amount
FROM orders;

Always prefer naming specific columns for:

• Clarity (readers know what data is returned)
• Performance (less data transferred from database to client)
• Stability (your query does not break if new columns are added to the table)

Column Aliases with AS

Rename columns in the output using AS:

SELECT
  order_id,
  customer_id,
  total_amount AS revenue,
  order_date AS sale_date
FROM orders;

You can also omit the AS keyword (AS is optional but recommended for readability):

SELECT total_amount revenue FROM orders;

Calculated Columns

Perform calculations in the SELECT clause:

SELECT
  product_id,
  units_sold,
  unit_price,
  units_sold * unit_price AS total_revenue,
  units_sold * unit_price * 0.2 AS vat_amount
FROM sales;

The DISTINCT Keyword

Remove duplicate values from results:

SELECT DISTINCT country
FROM customers;

This returns each country only once, regardless of how many customers are in that country. Useful for exploring what unique values exist in a column.

SELECT DISTINCT country, city
FROM customers;

When DISTINCT is applied to multiple columns, it returns unique combinations of all specified columns.

LIMIT: Restricting Result Size

LIMIT prevents returning millions of rows accidentally, especially when exploring unfamiliar tables:

SELECT *
FROM orders
LIMIT 100;

Always use LIMIT when exploring: it makes queries faster and prevents overwhelming your interface with data.

ORDER BY: Sorting Results

Sort results by one or more columns:

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY total_amount DESC;

• DESC: Descending (largest/latest first)
• ASC: Ascending (smallest/earliest first, this is the default)

Multi-column sort (sort by date, then by amount within each date):

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, total_amount DESC;

Exploring Table Structure

Before querying a table, understand its structure:

Get sample rows

SELECT * FROM orders LIMIT 5;

Count total rows

SELECT COUNT(*) AS total_rows FROM orders;

Check column data ranges

SELECT
  MIN(order_date) AS earliest_order,
  MAX(order_date) AS latest_order,
  MIN(total_amount) AS min_amount,
  MAX(total_amount) AS max_amount,
  AVG(total_amount) AS avg_amount
FROM orders;

Check distinct values in a category column

SELECT DISTINCT status, COUNT(*) AS count
FROM orders
GROUP BY status;

Working with NULL Values

NULL represents a missing or unknown value in SQL. NULL is not the same as zero or an empty string -- it is the absence of a value.

Checking for NULL

-- Find rows where email is missing
SELECT customer_id, name
FROM customers
WHERE email IS NULL;

-- Find rows where email is present
SELECT customer_id, name
FROM customers
WHERE email IS NOT NULL;

Note: You cannot use = NULL or != NULL. You must use IS NULL or IS NOT NULL.

Handling NULL in Calculations

NULL in any arithmetic expression returns NULL:

SELECT 100 + NULL;   -- returns NULL, not 100

Use COALESCE to substitute a default value for NULL:

SELECT
  customer_id,
  COALESCE(discount_amount, 0) AS discount  -- replace NULL with 0
FROM orders;

Comments in SQL

Add comments to document your queries:

-- Single line comment: describe what the query does

/*
  Multi-line comment:
  More detailed explanation
  or notes about data quality
*/

SELECT customer_id, name  -- inline comment
FROM customers;

Always comment complex queries, especially if you or a colleague will run them again in the future.

Practical Query Examples

Find the 10 highest-value orders this year

SELECT
  order_id,
  customer_id,
  order_date,
  total_amount
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY total_amount DESC
LIMIT 10;

Find all customers missing an email address

SELECT
  customer_id,
  name,
  signup_date
FROM customers
WHERE email IS NULL
ORDER BY signup_date DESC;

Calculate total and average order value

SELECT
  COUNT(*) AS total_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS average_order_value,
  MIN(total_amount) AS smallest_order,
  MAX(total_amount) AS largest_order
FROM orders;

Key Takeaways

• SELECT specifies which columns to return; FROM specifies the table; use aliases (AS) to rename columns for clarity.
• DISTINCT removes duplicate values and is useful for exploring what unique values exist in a column.
• Always use LIMIT when exploring unfamiliar tables to prevent accidentally retrieving millions of rows.
• NULL represents missing data -- use IS NULL and IS NOT NULL for filtering, and COALESCE to substitute default values.
• Use ORDER BY with DESC or ASC to sort results; combine with LIMIT for top-N queries.

Practice Exercise

Using a database with a customers table and an orders table:

1. Write a query to return all columns from the first 20 rows of the orders table
2. Write a query to return distinct countries from the customers table, sorted alphabetically
3. Write a query that calculates the total revenue, average order value, and number of orders
4. Write a query to find all orders where shipping_address IS NULL
5. Write a query returning the 5 largest orders from 2024, showing order_id, customer_id, order_date, and total_amount