Day 7 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 7 of the 15 days of Advanced SQL, we will cover —
Window Functions in SQL
Grouping Sets in SQL
Constraints in SQL
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 7.
Window Functions in SQL: Window functions, also known as window aggregate functions, are a type of SQL function that operate on a set of rows and return a single value for each row. They are similar to aggregate functions like SUM or COUNT, but they allow you to perform calculations within a “window” of rows defined by an ORDER BY clause. Some examples of window functions include ROW_NUMBER(), RANK(), and DENSE_RANK().
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1) AS row_number
FROM table;
SELECT column1, column2, RANK() OVER (ORDER BY column1) AS rank
FROM table;
SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1) AS sum_column3
FROM table;
SELECT column1, column2, AVG(column3) OVER (ORDER BY column1 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS avg_column3
FROM table;
SELECT column1, column2, COUNT(column3) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_column3
FROM table;Grouping Sets in SQL: The GROUP BY clause in SQL is used to group rows based on one or more columns. The GROUPING SETS operator allows you to specify multiple grouping sets in a single query. It allows you to create multiple groups of data in a single query, instead of having to run multiple queries with different GROUP BY clauses. You can use the GROUPING SETS operator to group data by different columns or combinations of columns.
SELECT column1, column2, SUM(column3) AS total
FROM table
GROUP BY GROUPING SETS ((column1), (column2), ());Constraints in SQL: A constraint is a rule or restriction that is enforced by the database management system. It is used to ensure the integrity and accuracy of data in a table. There are several types of constraints in SQL, including primary key constraints, foreign key constraints, unique constraints, and check constraints. Primary key constraints ensure that each row in a table has a unique identifier, foreign key constraints ensure that a value in one table corresponds to a value in another table, unique constraints ensure that a column or set of columns has unique values, and check constraints ensure that a column or set of columns meets a specific condition.
CREATE TABLE table_name (
column1 datatype CONSTRAINT constraint_name,
column2 datatype CONSTRAINT constraint_name,
...
PRIMARY KEY (column1),
FOREIGN KEY (column2) REFERENCES other_table(column2),
UNIQUE (column3),
CHECK (column4 > 0)
);Window Functions in SQL
In the window functions, the input values are taken from a specified window which consists of one or more rows in the results returned by the SELECT.
Implementation —
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_number,
RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank,
DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS dense_rank,
PERCENT_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS percent_rank,
CUME_DIST() OVER (PARTITION BY column1 ORDER BY column2) AS cume_dist,
NTILE(4) OVER (PARTITION BY column1 ORDER BY column2) AS ntile,
NTH_VALUE(column2, 3) OVER (PARTITION BY column1 ORDER BY column2) AS nth_value,
LAST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value,
FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value
FROM table;There are four types of window functions in SQL —
- Ranking Functions
- Distribution Functions
- Analytical Functions
- Aggregate Functions
For Ranking Functions —
row_number() : gives unique no of each row within the partition
rank(): gives ranks within the partitions ( with gaps)
dense_rank(): gives ranks within the partitions ( without gaps)
For Distribution Functions —
percent_rank(): gives the percentile ranking no of a row
cume_dist() : gives the cumulative distribution of the values within a group of values
For Analytical Functions —
ntile() : it divides the rows within a partition into n groups
nth_value(): gives the nth value of the nth row in a given window frame
last_value(): gives the value of the last row in a given window frame
first_value(): gives the value of the first row in a given window frame
For Aggregate Functions —
It’s used to perform calculation on mutiple rows/values and return a single result as the answer. It ignores NULL values when performing aggregation.
Some of the most important aggregate functions are —
- COUNT() — To count the number of rows
- SUM() — To return sum of the column
- AVG () — To return average of the column
- MIN() — To return min value in the column
- MAX() — To return max value in the column
Format —
Count(column_name)
Sum(column_name)
Avg(column_name)
Min(column_name)
Max(column_name)
Implementation —
SELECT column1, column2,
-- Ranking Functions
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_number,
RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank,
DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS dense_rank,
-- Distribution Functions
PERCENT_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS percent_rank,
CUME_DIST() OVER (PARTITION BY column1 ORDER BY column2) AS cume_dist,
-- Analytical Functions
NTILE(4) OVER (PARTITION BY column1 ORDER BY column2) AS ntile,
NTH_VALUE(column2, 3) OVER (PARTITION BY column1 ORDER BY column2) AS nth_value,
LAST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value,
FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
-- Aggregate Functions
COUNT(column2) OVER (PARTITION BY column1) AS count,
SUM(column2) OVER (PARTITION BY column1) AS sum,
AVG(column2) OVER (PARTITION BY column1) AS avg,
MIN(column2) OVER (PARTITION BY column1) AS min,
MAX(column2) OVER (PARTITION BY column1) AS max
FROM table;Example —
SELECT COUNT(employee_id)
FROM employee_table
WHERE salary > 40000
SELECT MAX(salary)
FROM employee_table
SELECT MIN(salary)
FROM employee_table
SELECT AVG(salary)
FROM employee_table
SELECT SUM(salary)
FROM employee_table
Grouping Sets in SQL
A grouping set us a group of columns by which you create a group. For example a single SQL statement combined with Aggregate functions is a single grouping set.
It is an extension of the group by clause which is used to summarize the data.
Format —
SELECT column_list, aggregate(t1)
FROM table_name
GROUP BY
GROUPING SETS (
(t1, t2),
(t1),
(t2))
Grouping sets takes those set of columns which are to be grouped together.
Implementation —
SELECT column1, column2, SUM(column3) AS total_sum
FROM table_name
GROUP BY GROUPING SETS (
(column1, column2),
(column1),
(column2)
);Example —
SELECT employee_name, City, Year, SUM(Salary) AS TotalSalary
FROM employee_table
GROUP BY GROUPING SETS ((City), (Year), (City, Year),())
ORDER BY City, Year
Constraints in SQL
In order to avoid ambiguity in the data, we use Constraints which helps us better manage our data. These are nothing but rules which help us maintain integrity of the data and are applied at two levels —
Table level — applied to the whole table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department_id INT,
CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT chk_salary
CHECK (salary > 0)
);Column level — applied to the data which is stored in the columns
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
department_id INT,
salary DECIMAL(10, 2) DEFAULT 0,
CONSTRAINT chk_salary
CHECK (salary > 0)
);Format —
CREATE TABLE table_name
(
column1 datatype constraint1,
column2 datatype constraint2,
….
)
Example —
CREATE TABLE employee
(
emp_Id int(10) NOT NULL UNIQUE,
employee_name varchar(10),
email varchar(20)
)
In the above example we are using, NOT NULL and UNIQUE constraint.
That’s it for now.
Find Day 8 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
