SQL Notebook

2026-03-06

Basic Query & Sorting

SELECT

SELECT license_plate, entry_time
FROM parking_history;

Using * gets everything.

Column Aliases

SELECT parking_id,
       parked_hours * 40 AS amount
FROM parking_history;

When you calculate something, it is better using AS to give it a clear name.

Removing Duplicates

SELECT DISTINCT parking_type
FROM parking_history;

DISTINCT makes sure the same row doesn’t appear more than once.

ORDER BY Clause

SELECT *
FROM parking_history
ORDER BY entry_time ASC;

ORDER BY sorts the results. ASC means earliest first.

Filtering & Conditions

WHERE Clause

SELECT *
FROM parking_history
WHERE license_plate = 'AAA-1111';

WHERE filters the rows. Here, it returns the details for the license plate AAA-1111.

Conditions with AND, OR, NOT

SELECT license_plate, payment_method, amount, is_electric
FROM payment_history
WHERE (payment_method = 'Credit_Card' AND amount > 5000)
   OR (is_electric = 1);
  • AND: All conditions must be true.
  • OR: At least one condition must be true.
  • NOT: Reverses a condition.

SQL checks in this order: NOT → AND → OR. Always use parentheses () for complex logic to avoid mistakes.

Not Equal (<>)

SELECT parking_id, parking_type, entry_time, exit_time
FROM parking_history
WHERE parking_type <> 'monthly';

<> means "not equal". This returns all non-monthly parking records. Same as WHERE NOT parking_type = 'monthly'. <> is shorter and very common.

Check for Missing Values (IS NULL)

SELECT station_code, license_plate, entry_time
FROM parking_history
WHERE exit_time IS NULL;
  • IS NULL finds rows with no value (car still parked).
  • Never use = NULL or <> NULL — they don't work!
  • Use IS NULL or IS NOT NULL only.

Date Range (BETWEEN or >= / <)

SELECT COUNT(*) AS total_cars
FROM parking_history
WHERE entry_time >= '2025-01-01'
  AND entry_time <  '2026-01-01';
  • Best way for a full year: >= start AND < next_year_start.
  • Safe even with timestamps (hours, minutes, seconds).
  • BETWEEN also works, but can miss records on Dec 31 if the time is not exactly 00:00:00.

IN (Match a List)

SELECT station_code, station_name, open_date
FROM management_table
WHERE strftime('%Y', open_date) IN ('2025', '2024', '2023');

IN checks if the value is in the list. Cleaner than using many ORs.

Pattern Matching (LIKE)

SELECT station_code, license_plate
FROM parking_history
WHERE license_plate LIKE 'E%';

LIKE finds text patterns. - % = any characters (zero or more) - _ = exactly one character

Aggregation & Grouping

Aggregate Functions

SELECT station_code,
       COUNT(*) AS vehicle_count,
       AVG(TIMESTAMPDIFF(MINUTE, entry_time, exit_time)) AS avg_duration_mins,
       MAX(TIMESTAMPDIFF(MINUTE, entry_time, exit_time)) AS max_duration_mins,
       MIN(TIMESTAMPDIFF(MINUTE, entry_time, exit_time)) AS min_duration_mins
FROM parking_history
WHERE exit_time IS NOT NULL
GROUP BY station_code;
  • Per group: COUNT(*) → row count, AVG() → average, MAX/MIN → highest/lowest value.
  • Use WHERE first to filter data before grouping.

Basic Grouping (GROUP BY)

SELECT station_code, COUNT(*) AS total_entries
FROM parking_history
GROUP BY station_code
ORDER BY total_entries DESC;

Group by Multiple Columns

SELECT city, station_type, COUNT(*) AS station_count
FROM management_table
GROUP BY city, station_type
ORDER BY city, station_type;

GROUP BY city, THEN station_type.

Grouping via Expressions

SELECT HOUR(entry_time) AS entry_hour,
       COUNT(*) AS traffic_volume
FROM parking_history
GROUP BY HOUR(entry_time)
ORDER BY entry_hour;

GROUP BY HOUR(entry_time) → hourly buckets (0-23). Analyze trends without extra columns.

Filter Groups (HAVING)

SELECT station_code, COUNT(*) AS total_visits
FROM parking_history
GROUP BY station_code
HAVING COUNT(*) > 1000;
  • HAVING filters AFTER grouping.
  • WHERE filters BEFORE (raw rows).
  • Can't use HAVING on non-aggregates.

Add Totals (ROLLUP)

SELECT payment_method, COUNT(*) AS tx_count
FROM payment_history
GROUP BY payment_method WITH ROLLUP;

WITH ROLLUP → adds subtotal rows + grand total (NULL row). Quick reports without extra queries.

Table Joins

FROM Clause & Table Aliases

SELECT ph.station_code, ph.station_name, py.amount
FROM parking_history ph
JOIN payment_history py
  ON py.parking_id = ph.parking_id;

FROM tells the query which table to read. Short aliases (like ph, py) make the query shorter and easier to read, especially when joining tables.

Inner Join (Most Common)

SELECT ph.license_plate, ph.entry_time, mt.bd_name, mt.city
FROM parking_history ph
INNER JOIN management_table mt
  ON ph.station_code = mt.station_code;

INNER JOIN keeps only rows with matching keys in both tables.

Left Join (Keep All from Left Table)

SELECT mt.station_name,
       COALESCE(SUM(pay.amount_received), 0) AS total_revenue
FROM management_table mt
LEFT JOIN payment_history pay
  ON mt.station_code = pay.station_code
 AND DATE(pay.paid_time) = CURRENT_DATE
GROUP BY mt.station_name;

Joining Three Tables

SELECT ph.license_plate, mt.area, pay.payment_method, pay.paid_time
FROM parking_history ph
INNER JOIN management_table mt
  ON ph.station_code = mt.station_code
INNER JOIN payment_history pay
  ON ph.station_code = pay.station_code
 AND ph.entry_time   = pay.entry_time;

Start with parking_history → add station info → add payment info.

Chain Multiple Outer Joins

SELECT mt.station_name, ph.entry_time, pay.amount_received
FROM management_table mt
LEFT JOIN parking_history ph
  ON mt.station_code = ph.station_code
LEFT JOIN payment_history pay
  ON ph.station_code = pay.station_code
 AND ph.entry_time  = pay.entry_time;

Self-Join

SELECT t1.station_name AS station_a, t1.bd_team AS team_a,
       t2.station_name AS station_b, t2.bd_team AS team_b, t1.area
FROM management_table t1
INNER JOIN management_table t2
  ON t1.area    = t2.area
 AND t1.bd_team <> t2.bd_team;

Use two aliases (t1, t2) for the same table. Find pairs of stations in the same area but different teams.

Cross Join (Every Combo)

SELECT mt.station_name, d.day_num
FROM management_table mt
CROSS JOIN (
    SELECT 1 AS day_num UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
) d
ORDER BY mt.station_name, d.day_num;

Set Operations

The UNION Operator

SELECT station_code FROM Parking_history
UNION
SELECT station_code FROM Payment_history;

UNION stacks two query results and removes duplicates. Rules: Same number of columns, Compatible data types, Column names from first query.

UNION ALL (Combine + Keep All Rows)

SELECT *
FROM (
    SELECT entry_time AS event_time, 'Vehicle Entry' AS event_type
    FROM parking_history
    WHERE station_code = 'ST001'
    UNION ALL
    SELECT paid_time AS event_time, 'Payment Made' AS event_type
    FROM payment_history
    WHERE station_code = 'ST001'
)
ORDER BY event_time;

UNION ALL keeps everything (faster, no deduplication).

INTERSECT (Rows in Both Queries)

SELECT station_code, entry_time
FROM parking_history
INTERSECT
SELECT station_code, entry_time
FROM payment_history;

INTERSECT returns only rows that appear in BOTH results.

EXCEPT (Rows in First but Not Second)

SELECT station_code, entry_time
FROM parking_history
EXCEPT
SELECT station_code, entry_time
FROM payment_history;

EXCEPT returns rows only in the FIRST query.

Subqueries

Basic Subquery

SELECT station_code, station_name, open_date
FROM management_table
WHERE open_date = (
    SELECT MAX(open_date) 
    FROM management_table
);
  1. Inner query runs first to find the latest open_date.
  2. Outer query gets stations opened on that date.

Scalar Subquery in SELECT

SELECT mt.station_name,
       mt.city,
       (SELECT COUNT(*)
        FROM parking_history ph
        WHERE ph.station_code = mt.station_code) AS total_visits
FROM management_table mt;

Subquery returns one value per row.

IN Subquery (Filter by List)

SELECT mt.station_name, ph.license_plate, ph.entry_time
FROM parking_history ph
JOIN management_table mt
  ON mt.station_code = ph.station_code
WHERE ph.station_code IN (
    SELECT station_code
    FROM management_table
    WHERE area = 'Xinyi Dist'
);

Non-correlated (subquery runs once). Subquery → list of station_codes in Xinyi.

Derived Table (Subquery in FROM)

SELECT station_code, day_of_record, daily_total
FROM (
    SELECT station_code,
           DATE(paid_time) AS day_of_record,
           SUM(amount_received) AS daily_total
    FROM payment_history
    GROUP BY station_code, DATE(paid_time)
) AS daily_stats
WHERE daily_total > 10000;
  1. Inner → aggregate daily revenue per station.
  2. Outer → filter high-revenue days (>10,000).

NOT EXISTS (Find Unmatched Rows)

SELECT ph.license_plate, ph.station_code, ph.entry_time
FROM parking_history ph
WHERE NOT EXISTS (
    SELECT 1
    FROM payment_history pay
    WHERE pay.station_code = ph.station_code
      AND pay.entry_time   = ph.entry_time
);
  1. Correlated subquery: It runs once for every row in the outer query (parking_history).
  2. If NO matching record is found in the inner query (payment_history) → returns TRUE and keeps the row.
  3. Use case: Finding unpaid parking sessions. SELECT 1 is used because the actual data doesn't matter, only its existence.

Conditional Logic

Simple CASE (Exact Match Mapping)

SELECT station_name,
       CASE station_type
           WHEN 1 THEN 'Indoor Garage'
           WHEN 2 THEN 'Outdoor Lot'
           WHEN 3 THEN 'Street Parking'
           ELSE 'Unknown'
       END AS type_description
FROM management_table;

Match exact values only.

Searched CASE (Ranges & Complex Rules)

SELECT ticket_id,
       TIMESTAMPDIFF(HOUR, entry_time, exit_time) AS duration_hours,
       CASE
           WHEN TIMESTAMPDIFF(HOUR, entry_time, exit_time) < 2 THEN 'Short-term'
           WHEN TIMESTAMPDIFF(HOUR, entry_time, exit_time) BETWEEN 2 AND 8 THEN 'Medium'
           ELSE 'Long-term'
       END AS duration_category
FROM parking_history
WHERE exit_time IS NOT NULL;

Check conditions freely (ranges, >, <, AND/OR).

← Back to Blog