Day 8 of 15 Days of Advanced SQL Series
Welcome back peeps. Hope all’s well. Last two weeks have been crazy busy at work for me (plus I was traveling).
Day 2 : SQL Basics, Query Structure, Built In functions Conditions
Day 4 : Set Theory Operations, Stored Procedures and CASE statements in SQL
Day 6 : Subqueries, Group by, order by and Having clauses in SQL and Analytical Functions
Day 7 : Window Functions, Grouping Sets and Constraints in SQL
Day 8 : BigQuery Basics, SELECT, FROM, WHERE and Date and Extract in BigQuery
Day 9 : Common Expression Table, UNNEST Clause, SQL vs NoSQL Databases
Day 10 : Triggers, Pivot and Cursors in SQL
Day 14 : MySQL in Depth
Day 15 : PostgreSQL inDepth
Anyways, For Day 8 of the 15 days of Advanced SQL, we will cover —
BigQuery basics
SELECT, FROM and WHERE clauses in Bigquery
Date and Extract Clauses in Bigquery
Github for Advanced SQL that you can follow —
Projects Videos —
All the projects, data structures, SQL, algorithms, system design, Data Science and ML , Data Analytics, Data Engineering, , Implemented Data Science and ML projects, Implemented Data Engineering Projects, Implemented Deep Learning Projects, Implemented Machine Learning Ops Projects, Implemented Time Series Analysis and Forecasting Projects, Implemented Applied Machine Learning Projects, Implemented Tensorflow and Keras Projects, Implemented PyTorch Projects, Implemented Scikit Learn Projects, Implemented Big Data Projects, Implemented Cloud Machine Learning Projects, Implemented Neural Networks Projects, Implemented OpenCV Projects,Complete ML Research Papers Summarized, Implemented Data Analytics projects, Implemented Data Visualization Projects, Implemented Data Mining Projects, Implemented Natural Leaning Processing Projects, MLOps and Deep Learning, Applied Machine Learning with Projects Series, PyTorch with Projects Series, Tensorflow and Keras with Projects Series, Scikit Learn Series with Projects, Time Series Analysis and Forecasting with Projects Series, ML System Design Case Studies Series videos will be published on our youtube channel ( just launched).
Subscribe today!
System Design Case Studies — In Depth
Complete Data Structures and Algorithm Series
Github —
Let’s get started with Day 8.
- In BigQuery, the SELECT, FROM, and WHERE clauses work similarly to how they work in standard SQL.
- The SELECT clause is used to specify the columns that you want to retrieve from the table. You can use the * wildcard character to select all columns.
- The FROM clause is used to specify the table or tables from which you want to retrieve data. You can also use subqueries and join multiple tables together in the FROM clause.
- The WHERE clause is used to filter the data retrieved by the query by specifying the conditions that must be met for a row to be included in the result set.
SELECT *
FROM your_table
WHERE DATE(date_column) = '2023-05-21'- In BigQuery, you can use the DATE and EXTRACT functions to work with dates in your queries.
- The EXTRACT function allows you to extract parts of a date, such as the year, month, or day.
- BigQuery also supports a number of other date and time functions such as DATE_TRUNC, DATE_DIFF and TIMESTAMP_DIFF which can be used to perform operations on date and time fields.
SELECT EXTRACT(YEAR FROM date_column) AS year,
EXTRACT(MONTH FROM date_column) AS month,
EXTRACT(DAY FROM date_column) AS day
FROM your_tableIn summary, the SELECT, FROM and WHERE clauses in BigQuery are used to specify the columns, tables, and conditions for retrieving data from a BigQuery table. Additionally, the DATE and EXTRACT functions can be used to work with dates and extract parts of a date in BigQuery.
BigQuery basics
BigQuery is a Serverless, highly scalable, and cost-effective multi-cloud data warehouse designed for business agility and is integrated on Google Cloud Platform. If you are a developer who works with huge amounts of data, you can use Google Cloud Platform to query petabytes of data at fast speeds. In this post you will learn how to use different clauses of Standard SQL to analyze your data on Google Cloud Platform.
To get started signup : https://cloud.google.com/
SELECT, FROM and WHERE clauses in Bigquery
In order to retrieve or pull all the columns and rows we use * with the SELECT statement. To pull the selective columns we define the column names with the SELECT Statement. FROM statement is used to specify the table from which you would like to retrieve the data. WHERE is used to filter the rows that meet certain condition or criteria.
Format to use SELECT, FROM and Where :
SELECT column_names
FROM table_name
WHERE condition
WHERE clause is used with SELECT, UPDATE and DELETE statement
Implementation —
SELECT *
FROM table_name
WHERE conditionDate and Extract Clauses in Bigquery
Alias is used to give a shorthand name for a table or column. Whenever you create complex queries, Alias can help you to improve the readability of your query.
Format to use Alias
SELECT column_name AS Alias-name
FROM table alias_name
WHERE condition
BigQuery has rich DATETIME Functions.
In order to know the current date and time, use the CURRENT_DATETIME function which will return the current time as a DATETIME object.
Format —
CURRENT_DATETIME([timezone])
Implementation —
SELECT CURRENT_DATETIME() AS current_datetimeSince the DATETIME format is in the form of DATETIME(year, month, day, hour, minute, second). In order to Extract the year or month or day or hour or minute, second etc you can use the EXTRACT clause which returns a value that corresponds to the specified part from a supplied expression_datetime.
Format —
EXTRACT(part FROM expression_datetime)
- DAY: Returns values in the range [1,7] with Sunday as the first day of the week.
- YEAR returns the year.
- WEEK: Returns the week number of the date in the range [0, 53]. Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0.
- MONTH
- QUARTER: Returns values in the range [1,4].
Implementation —
SELECT EXTRACT(YEAR FROM your_datetime_column) AS year,
EXTRACT(MONTH FROM your_datetime_column) AS month,
EXTRACT(DAY FROM your_datetime_column) AS day,
EXTRACT(HOUR FROM your_datetime_column) AS hour,
EXTRACT(MINUTE FROM your_datetime_column) AS minute,
EXTRACT(SECOND FROM your_datetime_column) AS second
FROM your_tableThat’s it for now.
Find Day 9 Below :
Let me know if you have questions in the comment section below. Subscribe/ Follow, Like/Clap as it would encourage me to write more in my free time
Stay Tuned!!
Read More —
11 most important System Design Base Concepts
6. Networking, How Browsers work, Content Network Delivery ( CDN)
13. System Design Template — How to solve any System Design Question
Some of the other best Series —
30 days of Data Structures and Algorithms and System Design Simplified
Data Science and Machine Learning Research ( papers) Simplified **
100 days : Your Data Science and Machine Learning Degree Series with projects
Complete Data Visualization and Pre-processing Series with projects
Exceptional Github Repos — Part 1
Exceptional Github Repos — Part 2
Tech Newsletter —
If you are interested, you can join my newsletter through which I send tech interview tips, techniques, patterns, hacks — Software Development, ML, Data Science, Startups and Technology projects to more than 30K readers. You can subscribe to Tech Brew :
For Python Projects —
For complete 60 days of Data Science and ML : Day 1 — Day 60 : Quick Recap of 60 days of Data Science and ML
Follow for more updates. Stay tuned and keep coding!
For other projects, tune to —
Build Machine Learning Pipelines( With Code)
Recurrent Neural Network with Keras
Clustering Geolocation Data in Python using DBSCAN and K-Means
Facial Expression Recognition using Keras
Hyperparameter Tuning with Keras Tuner
Custom Layers in Keras
