BigQuery SQL for Beginners

Table of Content

Learn BigQuery SQL step by step with 10 simple queries you can run free in the BigQuery sandbox, from SELECT and WHERE to GROUP BY, HAVING and CASE

In our last BigQuery guide, you set up the free sandbox and ran your first query. Now it’s time to learn the language properly. BigQuery SQL is the skill behind most data analyst and data engineering jobs on Google Cloud, and the good news is that you only need a handful of commands to do most everyday work. In this tutorial, you’ll practise 10 BigQuery SQL queries on a free public dataset, each one building on the last, with a plain-English explanation of what every line does.

Quick Answer

To learn BigQuery SQL, start with these core commands: SELECT (choose columns), WHERE (filter rows), ORDER BY (sort), LIMIT (shorten results), SUM and COUNT (calculate), GROUP BY (summarise), HAVING (filter summaries), LIKE (match patterns) and CASE (create categories). You can practise all of them free in the BigQuery sandbox, with no credit card, using Google’s public dataset bigquery-public-data.usa_names.usa_1910_current. Always check the query validator before running a query, and select only the columns you need to stay within the free 1 TB per month of query processing.

Before You Start

You need:

  • A BigQuery sandbox project. If you haven’t set one up, follow our step-by-step guide first (linked at the end of this article).
  • A new query tab: in the BigQuery console, click + or Compose new query.

We’ll use the same US baby names dataset as before. It has five columns:

ColumnWhat it contains
stateTwo-letter US state code, such as CA or TX
genderF or M
yearYear of birth, from 1910 onwards
nameFirst name
numberNumber of babies given that name in that state and year

Each row means: “In this state, in this year, this many babies of this gender were named this.”

Note: the sandbox only supports reading and analysing data. Commands that change data, such as INSERT, UPDATE and DELETE, aren’t available, according to Google Cloud’s sandbox documentation. That’s fine for learning BigQuery SQL, because analysis is where most of the real work happens.

Query 1: SELECT Specific Columns

The most basic BigQuery SQL query picks columns from a table.

SELECT
  name,
  year,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
LIMIT 10;

What it does: shows 10 rows with only the name, year and number columns. When we ran it, the query validator showed it would process about 144 MB, a tiny fraction of the free 1 TB monthly allowance.

BigQuery SQL query results showing name, year and number columns

Good habit: in BigQuery, you pay for the columns you scan, not the rows you see. Listing only the columns you need is the single best way to save your free allowance. Avoid SELECT *.

Query 2: Filter Rows With WHERE

In BigQuery SQL, WHERE keeps only the rows that match a condition.

SELECT
  state,
  year,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  name = 'Emma'
LIMIT 20;

What it does: returns only rows where the name is Emma.

Remember: text values go inside single quotes, and they are case-sensitive, so 'emma' won’t match 'Emma'.

Query 3: Combine Conditions With AND / OR

SELECT
  name,
  gender,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  state = 'CA'
  AND year = 2020
LIMIT 20;

What it does: shows names given to babies in California in 2020.

Use AND when both conditions must be true, and OR when either can be true. For a range, use BETWEEN, for example year BETWEEN 2000 AND 2010.

Query 4: Sort Results With ORDER BY

Sorting is how BigQuery SQL turns raw rows into a ranked list.

SELECT
  name,
  gender,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  state = 'CA'
  AND year = 2020
ORDER BY
  number DESC
LIMIT 10;

What it does: shows the 10 most popular names in California in 2020.

DESC sorts from highest to lowest, and ASC (the default) sorts from lowest to highest.

Query 5: Add Up Values With SUM and GROUP BY

SELECT
  name,
  SUM(number) AS total_babies
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = 'F'
GROUP BY
  name
ORDER BY
  total_babies DESC
LIMIT 10;
BigQuery SQL results showing the top 10 girls' names

What it does: lists the 10 most popular girls’ names across all states and all years. In our run, Mary came first by a wide margin, with over 37 lakh babies.

GROUP BY name combines every row for the same name into one, and SUM(number) adds up the babies. AS total_babies gives the result column a readable name, called an alias.

Rule to remember: every column in SELECT must either be inside a calculation (like SUM) or be listed in GROUP BY.

Query 6: Count Things With COUNT and COUNT(DISTINCT)

Counting is one of the most common tasks in BigQuery SQL.

SELECT
  year,
  COUNT(DISTINCT name) AS unique_names
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
  year
ORDER BY
  year;

What it does: shows how many different names were used each year.

COUNT(*) counts rows, while COUNT(DISTINCT name) counts unique names. This is a quick way to see whether parents are choosing more varied names over time.

Query 7: Track a Trend Over Time

SELECT
  year,
  SUM(number) AS total_babies
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  name = 'Liam'
GROUP BY
  year
ORDER BY
  year;

What it does: shows how popular the name Liam has been each year. The chart shows it was rare until the 1990s, then rose sharply after 2005.

BigQuery SQL visualization of the name Liam over the years

Try this: after running it, click the Visualization tab in the results panel to see the trend as a chart, with no extra tools needed.

Query 8: Filter Groups With HAVING

SELECT
  name,
  SUM(number) AS total_babies
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year >= 2010
GROUP BY
  name
HAVING
  SUM(number) > 100000
ORDER BY
  total_babies DESC;

What it does: lists names given to more than 1 lakh (100,000) babies since 2010.

WHERE vs HAVING:

  • WHERE filters rows before grouping.
  • HAVING filters groups after grouping.

This is one of the most common BigQuery SQL interview questions.

Query 9: Match Patterns With LIKE

When you don’t know the exact value, BigQuery SQL lets you search for a pattern.

SELECT
  name,
  SUM(number) AS total_babies
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  name LIKE 'Ar%'
GROUP BY
  name
ORDER BY
  total_babies DESC
LIMIT 10;

What it does: finds the most popular names starting with “Ar”, such as Arthur or Aria.

  • % means “any number of characters”, so 'Ar%' means starts with Ar.
  • '%an' means ends with “an”.
  • '%ri%' means contains “ri” anywhere.

Query 10: Create Categories With CASE

SELECT
  CASE
    WHEN year < 1950 THEN 'Before 1950'
    WHEN year < 2000 THEN '1950 to 1999'
    ELSE '2000 onwards'
  END AS era,
  SUM(number) AS total_babies
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  name = 'Mary'
GROUP BY
  era
ORDER BY
  MIN(year);

What it does: groups the name Mary into three time periods and totals each one.

BigQuery SQL CASE query results grouping Mary by era

ORDER BY MIN(year) keeps the eras in time order instead of alphabetical order. CASE works like an if-then-else rule inside your query. It’s used everywhere in real data work, for example to label customers as “new” or “returning”, or transactions as “low”, “medium” or “high” value.

BigQuery SQL Cheat Sheet

CommandUse it toExample
SELECTChoose columnsSELECT name, year
WHEREFilter rowsWHERE state = 'TX'
AND / ORCombine conditionsWHERE year = 2020 AND gender = 'F'
ORDER BYSortORDER BY number DESC
LIMITShow fewer rowsLIMIT 10
SUM / COUNTCalculateSUM(number)
GROUP BYSummarise by a columnGROUP BY name
HAVINGFilter groupsHAVING SUM(number) > 1000
LIKEMatch patternsWHERE name LIKE 'A%'
CASECreate categoriesCASE WHEN ... THEN ... END

Tips to Practise BigQuery SQL Within the Free Limits

According to Google Cloud, the sandbox gives you 1 TB of query processing per month and a 10 GB storage limit, and tables you create expire after 60 days. To make your allowance last:

  • Check the query validator (top right of the editor) before every run. It tells you how much data the query will process.
  • Select only the columns you need. Never use SELECT * on large tables.
  • Don’t rely on LIMIT to save data. It shortens the results, but BigQuery may still scan the whole column.
  • Use the Preview tab on a table to look at sample rows for free.

Common BigQuery SQL Mistakes Beginners Make

  • Using double quotes for text: in BigQuery SQL, use single quotes for text values, as in 'Emma'. Backticks are for table names.
  • Forgetting GROUP BY: if you use SUM or COUNT with other columns, those columns must be in GROUP BY.
  • Using WHERE instead of HAVING to filter totals.
  • Mistyping the table name: copy the full path, including the backticks: `bigquery-public-data.usa_names.usa_1910_current`.

What to Learn Next in BigQuery SQL

Once you’re comfortable with these 10 queries, the next BigQuery SQL topics are:

  1. JOINs: combining two tables
  2. Date functions: working with dates and times
  3. Window functions: rankings and running totals
  4. Creating your own tables: uploading a CSV file to the sandbox

We’ll cover these in the next parts of this BigQuery series.

Frequently Asked Questions

Is BigQuery SQL different from normal SQL?
BigQuery uses GoogleSQL, which follows standard SQL. If you know basic SQL from MySQL or another database, most of it works the same way. Small differences include using backticks around table names.

Can I practise BigQuery SQL for free?
Yes. The BigQuery sandbox is free and needs no credit card. It includes 1 TB of query processing per month, and you can practise on Google’s free public datasets.

Why can’t I run INSERT or UPDATE in the sandbox?
The BigQuery sandbox doesn’t support data manipulation (DML) statements such as INSERT, UPDATE and DELETE. To use them, you need to upgrade by adding a billing account.

What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping, while HAVING filters the grouped results after GROUP BY.

How long does it take to learn BigQuery SQL?
With daily practice, most beginners can learn the basics in this guide within one or two weeks. JOINs and window functions usually take a few more weeks.

Conclusion

BigQuery SQL is easier to learn than it looks. With just 10 commands, SELECT, WHERE, AND/OR, ORDER BY, LIMIT, SUM, COUNT, GROUP BY, HAVING, LIKE and CASE, you can answer real questions from millions of rows of data. Practise each query in the free BigQuery sandbox, change the names, years and states to explore on your own, and always check the query validator before you run anything. Master these basics, and you’ll be ready for JOINs and window functions, the next step towards a career in data analytics and data engineering on Google Cloud.

My take: I believe BigQuery SQL is one of the most valuable skills for anyone who wants a career in data analytics or data engineering, especially on Google Cloud. The best way to learn SQL is not by memorising commands, but by practising with real-world datasets and solving practical problems. That’s why this tutorial uses Google’s public datasets and the free sandbox, building one concept at a time. Once learners are comfortable with these fundamentals, the next step is JOINs, CTEs, window functions and more advanced analytical queries. Consistent hands-on practice is what turns SQL knowledge into real-world capability. I would conclude by saying: “Don’t just learn SQL commands; learn how to turn data into meaningful decisions.”

Over to You

Which of these 10 BigQuery SQL queries did you find most useful? Try changing one of them and share what you discovered in the comments below.

Tags :

Biswaranjan Mishra

Biswaranjan Mishra is a Principal Data Architect with over 21 years of experience in Google Cloud, BigQuery, Snowflake and data engineering. He trains professionals in GCP Data Engineering, Generative AI and Agentic AI through CloudSkills Academy, and writes practical, tested guides on AI, cloud, digital payments and online safety for Indian readers.

Leave a Reply

Your email address will not be published. Required fields are marked *

Explore More

Latest Posts

You May Also Like

Get in Touch

© Copyright 2026 TechGilt. All Rights Reserved.