Day 6 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).
15 Days of Advanced SQL Series
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 6 of the 15 days of Advanced SQL, we will cover —
Subqueries in SQL
GROUP BY, ORDER BY in SQL
HAVING clauses in SQL
Analytical Functions
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 6.
Subqueries in SQL: A subquery is a query that is nested within another query. It is used to retrieve data that is used in the main query. Subqueries are enclosed in parentheses and can be used in various parts of a SQL statement such as SELECT, FROM, WHERE and HAVING clauses. They can also be used in combination with operators like IN, ANY, ALL, and EXISTS to filter the results of the main query.
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);GROUP BY, ORDER BY in SQL: The GROUP BY clause in SQL is used to group rows based on one or more columns. The ORDER BY clause is used to sort the result set based on one or more columns. GROUP BY is used to group the rows in the table based on one or more columns, and ORDER BY is used to sort the result set based on one or more columns.
SELECT column1, COUNT(*) AS count
FROM table1
GROUP BY column1;
SELECT column1, column2
FROM table1
ORDER BY column1 ASC, column2 DESC;HAVING clauses in SQL: The HAVING clause is used in conjunction with the GROUP BY clause to filter groups based on aggregate functions. The HAVING clause is used to filter groups based on aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
SELECT column1, COUNT(*) AS count
FROM table1
GROUP BY column1
HAVING COUNT(*) > 10;Analytical Functions: Analytical functions are advanced SQL functions that allow you to perform complex calculations and analysis on a set of rows. They are similar to aggregate functions, but they allow you to perform calculations within a “window” of rows defined by an ORDER BY clause. Some examples of analytical functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD() and many others. These functions are used to perform complex calculations and analysis such as calculating running totals, percentiles, and more.
SELECT column1, column2, ROW_NUMBER()
OVER (PARTITION BY column1 ORDER BY column2) AS row_number
FROM table1;Subqueries in SQL
These are nested queries statements which is used when we need results from multiple tables using multiple filters.
These subqueries consists of SQL statements like UPDATE, SELECT, DELETE, ALTER, INSERT.
There are 4 types of Subqueries -
- Single row Subquery
- Multiple Row Subquery
- Nested Subqueries
- Correlated Subqueries
Format —
SELECT t1, t2
FROM table1
WHERE VALUE IN
(SELECT r1
FROM table2
WHERE condition)
Implementation —
-- Single row subquery
SELECT column1
FROM table1
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
-- Multiple row subquery
SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
-- Nested subquery
SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE column3 = (SELECT column3 FROM table3 WHERE condition));
-- Correlated subquery
SELECT column1
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.column2 = t2.column2 AND condition);The subquery executes from the inner query to the outer query. They should be enclosed in the parentheses.
The benefits of using subqueries —
- Provides better readability
- Used to extract/work on complex analytics
- Easy and can be maintained for different databases
Snippet —
Group By, Order By and HAVING clauses in SQL
The aggregate functions are often used in conjunction with DISTINCT, GROUP BY and HAVING clauses. GROUP BY clause can group by one or more columns from your table. It is often used with aggregate functions where it groups your data records into summary rows.
Format —
SELECT column_names
FROM table
WHERE condition
GROUP BY column_names
GROUP BY with aggregate function format —
SELECT COUNT(column_name)
FROM table
GROUP BY column_name
HAVING clause is used to summarize group records returned by GROUP BY. It’s mandatory to use GROUP BY whenever you are using a HAVING clause.
Format —
SELECT column_names
FROM table
WHERE condition
GROUP BY column_names
HAVING condition
Order by is used to return the records in specific sort order i.e Ascending or Descending.
Implementation —
-- GROUP BY
SELECT column1, COUNT(column2)
FROM table
GROUP BY column1;
-- GROUP BY with aggregate function
SELECT column1, SUM(column2)
FROM table
GROUP BY column1
HAVING SUM(column2) > 100;
-- ORDER BY
SELECT column1, column2
FROM table
ORDER BY column1 ASC;
-- ORDER BY with multiple columns
SELECT column1, column2
FROM table
ORDER BY column1 ASC, column2 DESC;
-- HAVING
SELECT column1, COUNT(column2)
FROM table
GROUP BY column1
HAVING COUNT(column2) > 5;
-- HAVING with aggregate function
SELECT column1, AVG(column2)
FROM table
GROUP BY column1
HAVING AVG(column2) > 50;Format —
SELECT column_names
FROM table
WHERE condition
ORDER BY column_names
Example —
/* Order By */SELECT column_names
FROM table
WHERE condition
GROUP BY column_name
ORDER BY column_name ASC|DESC
LIMIT/* Group By */SELECT COUNT(column_name)
FROM table
GROUP BY column_name/* Having Clause */SELECT column_names
FROM table
WHERE condition
GROUP BY column_names
HAVING conditionSnippet —
Analytical Functions
Analytical Function computes results over a group of rows. All analytic functions have an OVER clause, which defines the sets of rows used in each calculation which has three (optional) parts:
- The PARTITION BY clause divides the rows of the table into different groups.
- The ORDER BY clause defines an ordering within each partition.
- The final clause (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) is known as a window frame clause. It identifies the set of rows used in each calculation.
The window frame clauses can have various functions like —
- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW — the previous row and the current row.
- ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING — the 3 previous rows, the current row, and the following row
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — all rows in the partition.
Implementation —
-- ROW_NUMBER()
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1) AS row_number
FROM table;
-- RANK()
SELECT column1, column2, RANK() OVER (ORDER BY column1) AS rank
FROM table;
-- Analytical function with PARTITION BY
SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1) AS sum_column3
FROM table;
-- Analytical function with ORDER BY and window frame clause
SELECT column1, column2, AVG(column3) OVER (ORDER BY column1 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS avg_column3
FROM table;
-- Analytical function with PARTITION BY, ORDER BY, and window frame clause
SELECT column1, column2, COUNT(column3) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_column3
FROM table;The analytical numbering functions which we can use with analytical functions are -
- ROW_NUMBER() — Returns the order in which rows appear in the input (starting with 1)
- RANK() — All rows with the same value in the ordering column receive the same rank value, where the next row receives a rank value which increments by the number of rows with the previous rank value.
Format 1
SELECT column_names,
SUM(column_name)
OVER ( PARTITION BY column_name
ORDER BY column_name
ROWS UNBOUNDED PRECEDING
) AS Alias_name
FROM Table
Format 2
SELECT column_names
RANK() OVER (PARTITION BY column_name ORDER BY column_name) AS rank
FROM Table
Snippet —
That’s it for now.
Find Day 7 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
