Day 5 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 5 of 15 days of Advanced SQL, we will cover —
Wildcards in SQL
Aggregation in SQL
Sequences 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 5.
- Wildcards in SQL are used to search for data in a column that matches a certain pattern. The two most commonly used wildcards are the percent sign (%), which represents zero or more characters, and the underscore (_), which represents a single character.
- Aggregation in SQL is the process of combining data from multiple rows into a single result. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. These functions are typically used in conjunction with a GROUP BY clause to group the data by one or more columns.
- Sequences in SQL are used to generate unique integers. They are often used to generate primary key values for tables. Common functions used to work with sequences include NEXTVAL and CURRVAL. The specific syntax for using sequences may vary depending on the SQL database management system being used.
Wildcards in SQL
It is used to search for a pattern/data within a table and these characters ( listed below) are used with the LIKE operator.
% means any character with any number of count
? means any single character
*means one or more characters
[] means one value within the specified limit in the square brackets
\ means it matches the character that follows the backslash
^ means all character except [^characters]
-means range of characters within the brackets [characters]
Format —
SELECT * FROM table_name WHERE column_name LIKE ‘%ny%’
SELECT * FROM table_name WHERE column_name LIKE ‘_ewyork’
SELECT * FROM table_name WHERE column_name LIKE ‘_e_yo_k’
SELECT * FROM table_name WHERE column_name LIKE ‘[n-y]%’
SELECT * FROM table_name WHERE column_name LIKE ‘[adf]%’
Implementation —
-- Example: Get all employees whose names start with 'J'
SELECT *
FROM Employees
WHERE EmployeeName LIKE 'J%';
-- Example: Get all employees whose names end with 'son'
SELECT *
FROM Employees
WHERE EmployeeName LIKE '%son';
-- Example: Get all employees whose names contain 'doe' in any position
SELECT *
FROM Employees
WHERE EmployeeName LIKE '%doe%';
-- Example: Get all employees whose names have 'a' as the second character
SELECT *
FROM Employees
WHERE EmployeeName LIKE '_a%';
-- Example: Get all employees whose names have 'in' at the third and fourth position
SELECT *
FROM Employees
WHERE EmployeeName LIKE '__in%';
-- Example: Get all employees whose names start with 'A', 'B', or 'C'
SELECT *
FROM Employees
WHERE EmployeeName LIKE '[A-C]%';
-- Example: Get all employees whose names contain either 'a' or 'e'
SELECT *
FROM Employees
WHERE EmployeeName LIKE '%[ae]%';
-- Example: Get all employees whose names do not contain 'x'
SELECT *
FROM Employees
WHERE EmployeeName LIKE '%[^x]%';
-- Example: Get all employees whose names start with any character except 'A', 'B', or 'C'
SELECT *
FROM Employees
WHERE EmployeeName LIKE '[^A-C]%';Snippet —
Aggregation in SQL
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)
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
Implementation —
-- Example: Get the total number of employees
SELECT COUNT(*) AS TotalEmployees
FROM Employees;
-- Example: Get the sum of salaries for all employees
SELECT SUM(Salary) AS TotalSalary
FROM Employees;
-- Example: Get the average salary of employees
SELECT AVG(Salary) AS AverageSalary
FROM Employees;
-- Example: Get the minimum and maximum salary of employees
SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary
FROM Employees;Sequences in SQL
Sequences are very useful to identify each row of the table with a unique key in the form of a numerical series for the data which doesnt have unique identifiers/keys.
Format —
CREATE SEQUENCE sequence_name
START WITH initialize
INCREMENT BY increment_value
MINVALUE min_value
MAXVALUE max_value
CYCLE or NOCYCLE
Example —
CREATE SEQUENCE sequence64
START WITH 12
INCREMENT BY 2
MINVALUE 10
MAXVALUE 98
CYCLE;
Implementation —
-- Create a sequence
CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
CYCLE;
-- Use the sequence to generate values
INSERT INTO table_name (id, column1, column2)
VALUES (NEXTVAL('sequence_name'), value1, value2);
-- Retrieve the next value from the sequence
SELECT NEXTVAL('sequence_name');
-- Reset the sequence to its initial value
ALTER SEQUENCE sequence_name RESTART;That’s it for now.
Find Day 6 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
