BleepxQuery Guide: SQL Query Guide

A comprehensive guide to SQL query types with examples and variants, applicable across all domains.

← Back to Challenges

0 pts

0/95 cleared

*bleep* Zero points. The clock is ticking, trainee.

Bleepx’s SQL Query Types

Select specific columns from a dataset.

Basic SELECT

Select specific columns from a table.

SELECT All

Select all columns using *.

SELECT DISTINCT

Select unique values.

Filter rows based on conditions.

Basic WHERE

Filter rows with a single condition.

WHERE with Operators

Combine conditions with AND, OR, NOT.

Sort the result set.

Basic ORDER BY

Sort by a column in ascending or descending order.

Multi-Column ORDER BY

Sort by multiple columns.

Aggregate rows into groups.

Basic GROUP BY

Group rows and apply an aggregate function.

Filter groups based on aggregate conditions.

Basic HAVING

Apply conditions to grouped results.

Restrict the number of rows returned.

Basic LIMIT

Return a fixed number of rows.

LIMIT with OFFSET

Skip rows before returning a fixed number.

Combine results of multiple SELECT statements.

Basic UNION

Combine unique rows from multiple queries.

UNION ALL

Combine all rows, including duplicates.

Pattern matching for strings.

Basic LIKE

Match strings using wildcards.

Filter values within a range.

Basic BETWEEN

Include values between two bounds.

Filter rows matching a list of values.

Basic IN

Check if a column value is in a list.

Check for NULL values.

Basic IS NULL

Filter rows where a column is NULL.

Sum numeric values in each group.

Basic SUM

Calculate the total of a numeric column.

Count rows or non-null values.

Basic COUNT

Count all rows.

Average numeric values.

Basic AVG

Calculate the average of a numeric column.

Find minimum or maximum values.

Basic MIN/MAX

Retrieve the smallest or largest value.

Combine two tables row-wise based on a related column.

INNER JOIN

Returns rows where there is a match in both tables.

LEFT JOIN

Returns all rows from the left table, with matches or NULL.

Compute metrics across a moving frame.

ROW_NUMBER

Assign a unique number to each row within a partition.

Common Table Expressions (WITH) define temporary named result sets that make complex queries readable and modular. Think of them as creating a temporary view that only exists for one query.

Basic CTE

Use WITH to create a named subquery that you can reference like a table.

CTE with Aggregation

Use a CTE to pre-aggregate data, then filter or rank the results in the outer query.

Multiple CTEs

Chain multiple CTEs by separating them with commas. Each CTE can reference the ones defined before it.

CTE with JOIN

Use a CTE to prepare data from one table, then JOIN it with another table in the outer query.

Nest SELECTs inside other queries.

Basic SUBQUERY

Use a subquery in the WHERE clause.

Apply conditional logic in SELECT.

Basic CASE

Transform values based on conditions.

Test whether a subquery returns any rows.

Basic EXISTS

Filter rows based on subquery results.

Assign ranks in windowed results.

Basic RANK

Assign ranks with gaps for ties.

DENSE_RANK

Assign ranks without gaps for ties.

Access previous or next row in a window.

Basic LAG

Retrieve the previous row’s value.

Basic LEAD

Retrieve the next row’s value.

Compute distribution quantiles (DuckDB extension).

Basic QUANTILE

Calculate a specific quantile of a column.

Map points into spatial grids (DuckDB spatial extension).

Basic GEO_JOIN

Join tables based on spatial relationships.

Rotate groups into columns (DuckDB extension).

Basic PIVOT

Transform rows into columns based on a pivot column.

Handle hierarchical or recursive data.

Basic RECURSIVE CTE

Traverse a hierarchy.

Pull a path out of JSON text (SQLite JSON1 extension).

Basic JSON_EXTRACT

Extract a value from a JSON column.

Compare against all or any values in a subquery.

Basic ALL

Ensure a condition holds for all subquery values.

Basic ANY

Ensure a condition holds for any subquery value.

Return the first non-NULL value from a list of expressions. Essential for handling missing data gracefully.

Basic COALESCE

Replace NULL with a default value.

COALESCE with Multiple Fallbacks

Try multiple columns in order, returning the first non-NULL.

COALESCE in Calculations

Use COALESCE to safely handle NULL in math expressions (NULL in arithmetic makes the whole result NULL).

Return NULL if two expressions are equal. Commonly used to prevent division by zero.

Basic NULLIF

Returns NULL when both arguments are the same, otherwise returns the first argument.

Round a numeric value to a specified number of decimal places.

Basic ROUND

Round to N decimal places.

ROUND to Integer

Round to the nearest whole number by using 0 decimal places.

Convert a value from one data type to another.

Basic CAST

Convert between types like TEXT, INTEGER, REAL, DATE.

CAST for Division

Cast integers to REAL/FLOAT before dividing to get decimal results instead of integer division.

The OVER clause turns an aggregate or ranking function into a window function. PARTITION BY divides rows into groups (like GROUP BY but without collapsing rows). ORDER BY within OVER defines the row order for the window.

Aggregate with OVER

Compute an aggregate per group while keeping every row visible (unlike GROUP BY which collapses rows).

Running Total with OVER

Compute a cumulative sum by ordering rows within each partition.

PARTITION BY with Ranking

Rank rows within each group independently.

Retrieve the first or last value in a window frame.

Basic FIRST_VALUE

Get the first value within a partition.

Basic LAST_VALUE

Get the last value within a partition. Note — you often need a frame clause to get the true last value.

Divide rows into N roughly equal buckets within a partition.

Basic NTILE

Split rows into N groups and assign a bucket number.

Concatenate string values from multiple rows into a single string, separated by a delimiter.

Basic STRING_AGG

Combine values into a comma-separated list.

Functions for manipulating text values.

REPLACE

Replace occurrences of a substring.

SUBSTR

Extract a portion of a string by position and length.

LENGTH

Get the number of characters in a string.

TRIM / UPPER / LOWER

Clean and normalize text.

Functions for extracting and formatting dates and timestamps.

STRFTIME

Format a date/timestamp into a string (SQLite/DuckDB).

EXTRACT

Pull a specific part (year, month, day) from a date.

Simple conditional expressions for handling NULLs and inline if-else logic.

IFNULL

Return the first argument if it is not NULL, otherwise return the second. Shorthand for COALESCE with two arguments.

IIF

Inline if-else — returns one value if a condition is true, another if false.

Return the absolute (non-negative) value of a number.

Basic ABS

Remove the sign from a number.

Set operations to find differences or commonalities between two result sets.

EXCEPT

Return rows from the first query that are NOT in the second query.

INTERSECT

Return only rows that appear in BOTH queries.

All SwiftLink Challenges by Domain

business

crime

farming

finance

healthcare

social

space

sports

guide

guide

trials