How to Practice SQL on BigQuery for Free
|

How to Practice SQL on BigQuery for Free

To Practice SQL on BigQuery for Free, the easiest method is to use the public data available in BigQuery. Log in to the console, and click on BigQuery. But…

What is Google BigQuery?

BigQuery is Google Cloud’s fully managed, petabyte-scale, and cost-effective analytics data warehouse that lets you run analytics over vast amounts of data in near real-time. With BigQuery, there’s no infrastructure to set up or manage, letting you focus on finding meaningful insights using standard SQL and taking advantage of flexible pricing models across on-demand and flat-rate options ” – Google Cloud Platform

How to Start BigQuery for Free? BigQuery without Credit Card?

Firstly you need to have a google account. You can use your Gmail account for this purpose.
Once the Gmail account is ready, head to the Google Cloud Platform and log in or signup using Gmail.

After logging in click on Console.

GCP console
GCP Console

Click on “All Products” at the bottom

And search for BigQuery.

Watch the Video for a step-by-step guide.

How to Practice SQL on BigQuery for Free

After clicking on BigQuery, the BigQuery environment should load. As shown in the Image below:

Google BigQuery Enviornment
Google BigQuery Environment

Beginners Lesson for Google BigQuery, How BigQuery Works.

To start with BigQuery, the easiest method is to use the Free Data also called public data available in BigQuery.
Click on the BigQuery-Public-Data

For example, you can search “usa_1910_2013”. This will load the new Table. Now, select option QUERY, and click on “In new tab”. This will open a new tab to write Queries. This table is free to use and does not hold commercial & other licenses.

BigQuery Console
BigQuery Console

Top 4 Free SQL practice on BigQuery to help you improve your BigQuery skills

To start with the SQL practice you must select the table “usa_1910_2013”. This will load the new Table. Now, select option QUERY, and click on “In new tab”. This will open a new tab to write Queries. Now you may start with the following Queries:

1. Is the last letter of the Name a good proxy for Gender? 
select alphabet, gender,total_count, (total_count/count_of_aphabet_both_gender) * 100 as percentage
from (
select alphabet, gender,total_count, sum(total_count) over (partition by alphabet) as count_of_aphabet_both_gender
from (
select alphabet, gender, occured+count_of_alphabet as total_count
from(
select right(name,1) as alphabet, sum(number) as occured, gender, count(*) as count_of_alphabet
FROM `bigquery-public-data.usa_names.usa_1910_2013`
group by gender, alphabet
order by alphabet asc, occured) a 
order by alphabet asc, total_count desc)b
order by alphabet asc, total_count desc)c
order by percentage desc
2.  What was the 4th most common male name in the 60¬īs ? 
select name, total_number
from (
select name, sum(number) as total_number
FROM `bigquery-public-data.usa_names.usa_1910_2013`
where gender like 'M' and
year > 1959 and year < 1970
group by name
order by total_number desc
limit 4)a
order by total_number asc
limit 1
3. Gender Mix development across years in the USA.  What is the year with the highest % of Female names from 1920 until now?
select gender, year, round(sum(name_appeared)/sum(sum(b.name_appeared)) over(order by (select null)) * 100,2) as perc
from
(select gender, year, count_of_name as name_appeared
from (
select gender, year, count(name) as count_of_name
FROM `bigquery-public-data.usa_names.usa_1910_2013`
where gender like 'F'
and year > 1919
group by gender, year
order by year desc) a
order by name_appeared asc)b
group by gender, year
order by perc desc
limit 1
4. Is there any particular development you may notice when looking at the share of Female Vs Male names?
select year, gender , total_number,total_by_year , (total_number/total_by_year) * 100 as perc
from
(select year, gender , total_number, sum(total_number) over (partition by year) as total_by_year
from
(select year, gender, sum(number) as total_number
FROM `bigquery-public-data.usa_names.usa_1910_2013`
group by year, gender
order by year desc)a
#group by year, gender, total_number
order by year desc)
group by year, gender , total_number,total_by_year 
order by year desc, perc desc

Read more on SQL Queries:
Powerful SQL practice for a data analyst in just 5 minutes
How to Install SQL 2022 and SSMS 18 on Windows 8/10/11

watch more SQL Videos:

Similar Posts

One Comment

  1. Howdy! I “õnow this is sort of off-top—Ė—Ā “Ľowever I h–įd to ask.
    Does mana…°ing a well-establi—ēhed blog such as yours require a lot of work?
    I am brand new to operating a bl–ĺg h–ĺ‘Ěev–Ķr I do write in my journal on a daily basis.
    I’d like to start a blog so I can easily share my p–Ķrsonal
    experienc–Ķ and feelings online. Please let me know if you have any suggestions or tips
    for brand new aspiring blo…°gers. Appreciate it!

Leave a Reply

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