Basic SQL Querying Techniques for Data Analysis

ByJu Yeon Eum

Query languages allow data professionals to retrieve, summarize, filter, and organize data stored in databases. SQL, or Structured Query Language, is the most widely used query language for relational databases.

Although individual database systems may implement different functions and syntax, the core analytical techniques are broadly similar. These include:

  • Counting records
  • Identifying distinct values
  • Calculating summary statistics
  • Finding minimum and maximum values
  • Filtering rows
  • Sorting results
  • Matching text patterns
  • Grouping and aggregating data

This article introduces these techniques using a hypothetical table named used_car_sales.

Example Dataset

Suppose a relational database contains the following table:

used_car_sales

Its columns might include:

ColumnDescription
sale_idUnique identifier for the sale
customer_idUnique identifier for the customer
dealer_nameName of the automobile dealer
purchase_dateDate of purchase
postal_codeCustomer’s postal code
car_pricePrice paid for the vehicle

The exact syntax shown below may vary slightly across database systems.

1. Counting Rows with COUNT()

The COUNT() aggregate function counts rows or non-null values.

To count every row in a table, use:

SELECT COUNT(*) AS total_sales
FROM used_car_sales;

COUNT(*) counts all rows, including rows containing null values in individual columns.

To count the number of non-null values in a particular column:

SELECT COUNT(dealer_name) AS sales_with_dealer
FROM used_car_sales;

Unlike COUNT(*), this query does not count rows where dealer_name is NULL.

2. Finding Distinct Values

The DISTINCT keyword removes duplicate combinations from a query result.

To list the unique dealers:

SELECT DISTINCT dealer_name
FROM used_car_sales;

It is important to note that DISTINCT is normally a SQL keyword rather than a function. Therefore, it is generally written as DISTINCT dealer_name, not DISTINCT(dealer_name).

Counting distinct values

COUNT() and DISTINCT can be combined to count unique dealers:

SELECT COUNT(DISTINCT dealer_name) AS unique_dealers
FROM used_car_sales;

Most SQL databases support this form for one column. Support for counting distinct combinations of multiple columns varies by database system.

3. Calculating Aggregate Statistics

Aggregate functions summarize multiple rows and return one value for each group being analyzed.

Common aggregate functions include:

  • SUM() for totals
  • AVG() for arithmetic means
  • MIN() for minimum values
  • MAX() for maximum values
  • COUNT() for record counts
  • Standard-deviation functions for measuring variability

Calculating a total

To calculate the total value of all recorded vehicle sales:

SELECT SUM(car_price) AS total_sales_value
FROM used_car_sales;

Calculating an average

To calculate the average vehicle price:

SELECT AVG(car_price) AS average_car_price
FROM used_car_sales;

Most aggregate functions ignore NULL values. Therefore, the average is generally calculated only from rows containing a non-null car_price.

This behavior matters because missing prices could cause the result to describe only part of the dataset.

4. Measuring Variability with Standard Deviation

Standard deviation measures how widely values are dispersed around their mean.

A small standard deviation indicates that values tend to remain close to the average. A large standard deviation indicates greater variation.

SQL implementations commonly provide one or both of the following functions:

STDDEV_POP(car_price)

and:

STDDEV_SAMP(car_price)

STDDEV_POP() calculates the population standard deviation when the available records represent the complete population of interest.

STDDEV_SAMP() calculates the sample standard deviation when the records are treated as a sample from a larger population.

For example:

SELECT
    AVG(car_price) AS average_price,
    STDDEV_SAMP(car_price) AS sample_standard_deviation
FROM used_car_sales;

Function names differ across database products. Some systems use names such as STDEV(), STDEVP(), STDDEV(), or STDDEV_SAMP().

A large standard deviation may indicate substantial variation, skewness, or extreme values. It does not, by itself, identify the reason for that variation.

5. Finding Minimum and Maximum Values

The MIN() and MAX() functions identify the smallest and largest values in a column.

SELECT
    MIN(car_price) AS lowest_price,
    MAX(car_price) AS highest_price
FROM used_car_sales;

These functions are useful for an initial examination of a dataset. An implausibly low or high result may indicate:

  • A legitimate extreme observation
  • A data-entry error
  • A unit mismatch
  • A missing digit
  • An improperly converted value

Retrieving the row with the highest value

MAX(car_price) returns only the highest price. It does not automatically return information about the corresponding customer or vehicle.

One way to retrieve every row tied for the maximum price is:

SELECT *
FROM used_car_sales
WHERE car_price = (
    SELECT MAX(car_price)
    FROM used_car_sales
);

This approach handles situations where more than one sale has the same maximum price.

6. Filtering Rows with WHERE

Standard SQL does not provide a general SLICE() function for filtering table rows. A subset—or “slice”—of a table is normally selected with a WHERE clause.

To retrieve sales from one postal code:

SELECT *
FROM used_car_sales
WHERE postal_code = '85001';

Text values are usually enclosed in single quotation marks.

Filtering numeric ranges

To find cars priced from \$1,000 through \$2,000:

SELECT *
FROM used_car_sales
WHERE car_price BETWEEN 1000 AND 2000;

In SQL, BETWEEN normally includes both endpoints. The preceding query is therefore equivalent to:

SELECT *
FROM used_car_sales
WHERE car_price >= 1000
  AND car_price <= 2000;

Combining multiple conditions

Use AND when every condition must be satisfied:

SELECT *
FROM used_car_sales
WHERE car_price BETWEEN 1000 AND 2000
  AND postal_code = '85001';

Use OR when at least one condition must be satisfied:

SELECT *
FROM used_car_sales
WHERE postal_code = '85001'
   OR postal_code = '85002';

Parentheses make the intended logic explicit when AND and OR are used together:

SELECT *
FROM used_car_sales
WHERE car_price <= 5000
  AND (
      postal_code = '85001'
      OR postal_code = '85002'
  );

7. Filtering from a List with IN

The IN operator tests whether a value appears in a specified list.

SELECT *
FROM used_car_sales
WHERE postal_code IN ('85001', '85002', '85003');

This is more concise than writing several conditions joined by OR.

The opposite condition can be expressed with NOT IN:

SELECT *
FROM used_car_sales
WHERE postal_code NOT IN ('85001', '85002');

Care is required when NOT IN is used with a subquery that may return NULL, because SQL’s three-valued logic can produce unexpected results.

8. Sorting Results with ORDER BY

The ORDER BY clause arranges query results by one or more columns.

To sort sales from the earliest to the latest date:

SELECT *
FROM used_car_sales
ORDER BY purchase_date ASC;

ASC means ascending order and is usually the default.

To display the most recent sales first:

SELECT *
FROM used_car_sales
ORDER BY purchase_date DESC;

ORDER BY is a SQL clause, not a function, so parentheses are not used.

Sorting by multiple columns

Results can be sorted by more than one column:

SELECT *
FROM used_car_sales
ORDER BY dealer_name ASC, car_price DESC;

This query sorts rows alphabetically by dealer and then places the most expensive sales first within each dealer.

SQL does not guarantee result order unless an ORDER BY clause is provided.

9. Limiting the Number of Results

After sorting data, analysts often retrieve only the first few rows.

In databases that support LIMIT, the five most expensive sales can be obtained with:

SELECT *
FROM used_car_sales
ORDER BY car_price DESC
LIMIT 5;

Other database systems use syntax such as:

SELECT TOP 5 *
FROM used_car_sales
ORDER BY car_price DESC;

or:

SELECT *
FROM used_car_sales
ORDER BY car_price DESC
FETCH FIRST 5 ROWS ONLY;

The correct form depends on the database system.

10. Matching Text Patterns with LIKE

The LIKE operator filters text using pattern matching.

Two commonly supported wildcard characters are:

  • % for zero or more characters
  • _ for exactly one character

Values beginning with a pattern

To retrieve postal codes beginning with 850:

SELECT *
FROM used_car_sales
WHERE postal_code LIKE '850%';

Values ending with a pattern

SELECT *
FROM used_car_sales
WHERE dealer_name LIKE '%Motors';

Values containing a pattern

SELECT *
FROM used_car_sales
WHERE dealer_name LIKE '%Auto%';

Matching a fixed number of characters

If the postal code must begin with 850 and contain exactly two additional characters:

SELECT *
FROM used_car_sales
WHERE postal_code LIKE '850__';

The two underscores each represent one character.

Case sensitivity for LIKE depends on the database, collation, and column configuration. Some systems also provide a case-insensitive operator such as ILIKE.

11. Working with NULL Values

A missing database value is represented by NULL. It cannot reliably be tested using ordinary equality:

WHERE dealer_name = NULL

Instead, use IS NULL:

SELECT *
FROM used_car_sales
WHERE dealer_name IS NULL;

To retrieve non-null values:

SELECT *
FROM used_car_sales
WHERE dealer_name IS NOT NULL;

Understanding null behavior is essential because comparisons involving NULL generally evaluate as unknown rather than true or false.

12. Grouping Data with GROUP BY

The GROUP BY clause divides rows into groups so that aggregate functions can be calculated separately for each group.

To calculate total spending by postal code:

SELECT
    postal_code,
    SUM(car_price) AS total_spending
FROM used_car_sales
GROUP BY postal_code;

The query returns one row for each postal code.

Calculating several statistics by group

SELECT
    dealer_name,
    COUNT(*) AS number_of_sales,
    AVG(car_price) AS average_price,
    MIN(car_price) AS lowest_price,
    MAX(car_price) AS highest_price
FROM used_car_sales
GROUP BY dealer_name;

When GROUP BY is used, each selected column generally must either:

  • Appear in the GROUP BY clause, or
  • Be processed by an aggregate function

Some database systems permit exceptions, but relying on them can produce ambiguous or nonportable queries.

13. Filtering Groups with HAVING

WHERE filters individual rows before grouping. HAVING filters the aggregated groups after GROUP BY has been applied.

To find dealers with at least three sales:

SELECT
    dealer_name,
    COUNT(*) AS number_of_sales
FROM used_car_sales
GROUP BY dealer_name
HAVING COUNT(*) >= 3;

A query may use both clauses:

SELECT
    dealer_name,
    COUNT(*) AS number_of_sales,
    AVG(car_price) AS average_price
FROM used_car_sales
WHERE purchase_date >= '2026-01-01'
GROUP BY dealer_name
HAVING COUNT(*) >= 3
ORDER BY average_price DESC;

This query:

  1. Filters sales by date.
  2. Groups the remaining records by dealer.
  3. Calculates the number of sales and average price.
  4. Retains dealers with at least three qualifying sales.
  5. Sorts the groups by average price.

14. Using Aliases for Readable Results

Aliases provide clearer names for output columns and tables.

A column alias can be created with AS:

SELECT
    COUNT(*) AS total_sales,
    AVG(car_price) AS average_car_price
FROM used_car_sales;

Table aliases are especially helpful in joins and longer queries:

SELECT
    s.dealer_name,
    s.car_price
FROM used_car_sales AS s
WHERE s.car_price > 10000;

Although AS is optional for aliases in many SQL dialects, including it can improve readability.

SQL and Other Query Languages

The same general analytical objectives appear outside relational SQL:

  • Cassandra uses CQL, which resembles SQL but is designed around Cassandra’s distributed data model.
  • Neo4j uses Cypher for querying nodes, relationships, and graph patterns.
  • Document databases provide query APIs for filtering and aggregating documents.
  • Web APIs accept parameters that determine which resources or records are returned.

The concepts of selecting, filtering, grouping, ordering, and aggregating remain relevant, but the syntax and underlying execution models differ.

CQL and Cypher should therefore not be treated as interchangeable versions of SQL. Their capabilities reflect fundamentally different database structures and access patterns.

Common Mistakes to Avoid

Treating keywords as functions

DISTINCT, ORDER BY, GROUP BY, WHERE, and LIKE are SQL language elements, not functions called with parentheses.

Assuming rows have an inherent order

Database rows are not guaranteed to appear in insertion order. Use ORDER BY whenever the order matters.

Ignoring missing values

Most aggregate functions ignore NULL. Always investigate how missing data affects a calculation.

Selecting unrelated columns in grouped queries

A selected column that is neither grouped nor aggregated can make the result ambiguous.

Confusing WHERE and HAVING

Use WHERE for row-level conditions and HAVING for conditions applied to aggregated groups.

Assuming every database uses identical syntax

Standard SQL provides a common foundation, but functions, date syntax, regular expressions, case sensitivity, and result-limiting syntax vary among database systems.

Key Takeaways

  • COUNT(*) counts table rows, while COUNT(column) excludes null values in that column.
  • DISTINCT removes duplicates and can be combined with COUNT() to count unique values.
  • SUM(), AVG(), MIN(), MAX(), and standard-deviation functions summarize numerical data.
  • WHERE filters individual records according to specified conditions.
  • BETWEEN, IN, AND, and OR support more detailed filtering.
  • ORDER BY sorts query results in ascending or descending order.
  • LIKE performs basic text-pattern matching with wildcard characters.
  • GROUP BY calculates summaries for categories or groups.
  • HAVING filters groups after aggregation.
  • SQL syntax and available functions vary among database products.

Conclusion

Basic SQL queries allow analysts to move from raw database records to useful summaries and targeted subsets. Counting and aggregation provide an overview of a dataset, while filtering and sorting expose relevant records and patterns. Grouping extends these operations by generating separate summaries for meaningful categories.

These techniques form the foundation for more advanced work involving joins, subqueries, common table expressions, window functions, and analytical data pipelines.

One-sentence summary: Basic SQL querying techniques use selection, filtering, sorting, pattern matching, grouping, and aggregation to transform stored database records into useful analytical results.

Similar Posts

Leave a Reply