Day 6 of 30 days of Data Analytics with Projects Series
Welcome back peeps. Happy to share that we have finished —
Finished Series —
60 Days of Data Science and Machine Learning with projects Series
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!
We are now starting a new series — 30 days of Data Analytics with Projects. This series would run in parallel with —
Ongoing Series —
What’s covered till now in Data Analytics Series —
Day 1 : Data Analytics basics and kickstart of Data analytics with projects series
Day 3 : Data Analytics Ecosystem — Data Life Cycle, Data Analysis complete process ( most important things)
Day 5 : Statistics
Day 6 : Basic and Advanced SQL
In this post we will cover SQL. Let’s dive in!
Some important basic concepts in SQL include:
- SELECT statement: The SELECT statement is used to retrieve data from one or more tables in a database. The basic syntax is “SELECT column1, column2, … FROM table_name;”.
- WHERE clause: The WHERE clause is used to filter rows based on certain conditions. The basic syntax is “SELECT column1, column2, … FROM table_name WHERE condition;”.
- JOINs: JOINs are used to combine data from multiple tables based on a related column. The basic syntax is “SELECT column1, column2, … FROM table1 JOIN table2 ON table1.column = table2.column;”.
- GROUP BY and aggregate functions: GROUP BY is used to group rows by one or more columns, and aggregate functions such as SUM, COUNT, AVG, etc. are used to perform calculations on the grouped data. The basic syntax is “SELECT column1, COUNT(column2), SUM(column3), … FROM table_name GROUP BY column1;”.
- Subqueries: Subqueries are used to embed one SELECT statement within another SELECT statement. The basic syntax is “SELECT column1, column2, … FROM (SELECT column1, column2, … FROM table_name WHERE condition) AS subquery;”.
Some important advanced SQL concepts include:
Indexes: Indexes are used to improve the performance of SQL queries by allowing the database to quickly locate the data it needs.
-- Creating an index on a column
CREATE INDEX idx_customers_name ON customers (name);
-- Using an index in a query
SELECT * FROM customers WHERE name = 'John';Stored procedures: Stored procedures are pre-compiled SQL code that can be called and executed by applications.
-- Creating a stored procedure
CREATE PROCEDURE get_customer(IN customer_id INT)
BEGIN
SELECT * FROM customers WHERE id = customer_id;
END;
-- Executing the stored procedure
CALL get_customer(1001);Triggers: Triggers are used to automatically execute certain actions in response to specific events, such as inserting, updating, or deleting data in a table.
-- Creating a trigger to update a timestamp column on insert
CREATE TRIGGER update_timestamp AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE orders SET created_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;
-- Inserting a new row that triggers the trigger
INSERT INTO orders (id, customer_id) VALUES (1, 1001);Views: Views are virtual tables that are based on the result of a SELECT statement. They allow you to create a custom view of the data in a table without actually modifying the underlying data.
-- Creating a view based on a SELECT statement
CREATE VIEW high_value_orders AS
SELECT * FROM orders WHERE total_amount > 1000;
-- Querying the view
SELECT * FROM high_value_orders;Partitioning: Partitioning is the process of dividing a large table into smaller, more manageable pieces, which can improve query performance by allowing the database to access only the relevant partitions.
-- Creating a partitioned table
CREATE TABLE sales (
id INT,
order_date DATE,
amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p1 VALUES LESS THAN (2022),
PARTITION p2 VALUES LESS THAN (2023),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
-- Querying the partitioned table
SELECT * FROM sales PARTITION (p2);Window functions: Window functions are used to perform calculations across a set of rows, such as calculating a running total or finding the average of a group of rows.
-- Calculating a running total using a window function
SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM sales;Materialized views: Materialized views are pre-computed tables that store the results of a SELECT statement, which can be used to improve query performance by reducing the need to compute the results each time the view is queried.
-- Creating a materialized view
CREATE MATERIALIZED VIEW mv_high_value_orders AS
SELECT * FROM orders WHERE total_amount > 1000;
-- Refreshing the materialized view
REFRESH MATERIALIZED VIEW mv_high_value_orders;
-- Querying the materialized view
SELECT * FROM mv_high_value_orders;Performance tuning: Performance tuning is the process of optimizing the performance of SQL queries and the database itself, which can include optimizing the query structure, indexing, and other database settings.
-- Analyzing table statistics
ANALYZE TABLE orders;
-- Optimizing a query using proper indexing
CREATE INDEX idx_orders_customer_id ON orders (customer_id);SQL Basics and Kick start of Advanced SQL Series
SQL is a must know skill when it comes to data retrieval and manipulation. In this we covered the most important SQL constructs ( both basic and advanced)
Link :
SQL Basics, Query Structure, Built In functions Conditions
In this we covered in detail-
- DQL — Data Query Language : Commands [ SELECT]
- DML — Data Manipulation Language : Commands [INSERT, UPDATE, DELETE, LOCK CALL]
- DDL — Data Definition Language: Commands [CREATE,DROP,ALTER,TRUNCATE,RENAME]
- DCL — Data Control Language : Commands [GRANT, REVOKE]
- TCL — Transaction Control Language : Commands [ROLLBACK, COMMIT, SAVEPOINT, SET TRANSACTION]
Link :
Most Important Commands, Joins and Filters
In this we covered the most important and useful commands that you should know in SQL and their syntax which can be found here.
Link :
Set Theory Operations, Stored Procedures and CASE statements in SQL
In this we covered the main set operations —
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Stored procedures and Case statements.
The benefits of using stored procedure are —
- Reusable — Call the stored procedure any number of time instead of writing the query again and again.
- Can be modified easily and saves time
- Performance improvement because of the fast execution once stored in the buffer.
Link :
Wildcards, Aggregation and Sequences in SQL
In this we covered wildcards which is used to search for a pattern/data within a table and these characters ( listed below) are used with the LIKE operator.
Aggregation and Sequences were also covered in this.
Link :
Subqueries, Group by, order by and Having clauses in SQL and Analytical Functions
In this we covered subqueries, Group by, order by and having clause. Subqueries 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.
Link :
Window Functions, Grouping Sets and Constraints in SQL
In this we covered window functions, grouping sets and constraints 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.
There are four types of window functions in SQL —
Ranking Functions
-- Calculating the rank of sales amounts
SELECT customer_id, amount,
RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;Distribution Functions
-- Calculating the cumulative distribution of sales amounts
SELECT customer_id, amount,
CUME_DIST() OVER (ORDER BY amount) AS cumulative_dist
FROM sales;Analytical Functions
-- Calculating the moving average of sales amounts
SELECT order_date, amount,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales;Aggregate Functions
-- Calculating the total sales amount
SELECT SUM(amount) AS total_amount
FROM sales;
-- Calculating the average sales amount
SELECT AVG(amount) AS average_amount
FROM sales;
-- Calculating the maximum and minimum sales amounts
SELECT MAX(amount) AS max_amount, MIN(amount) AS min_amount
FROM sales;
-- Calculating the count of sales
SELECT COUNT(*) AS sales_count
FROM sales;Link :
BigQuery Basics, SELECT, FROM, WHERE and Date and Extract in BigQuery
In this we covered BigQuery in detail. 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.
Link :
Common Expression Table, UNNEST Clause, SQL vs NoSQL Databases
In this we covered CTE, UNNEST Clause, SQL vs NoSQL databases. In order to simplify the complex queries, make them more readable and query against them, Common Table Expressions is used. Common Table Expressions is nothing but a query whose result set you can reference in a later section of your query i.e it returns a temporary table that you return within your query. It uses the WITH clause.
Link :
Triggers, Pivot and Cursors in SQL
In this we covered Triggers, Pivot and Cursors in SQL. Triggers are an important concept in SQL. These are nothing but event driven SQL queries which are stored in the memory and fired when required.
The benefits of using triggers are reduce computational times, reuse of the code, helps check integrity of the database and detect any error or issues with the code.
Triggers
-- Creating a trigger to update a column on insert
CREATE TRIGGER update_total_amount AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE customers SET total_amount = total_amount + NEW.amount WHERE id = NEW.customer_id;
END;
-- Inserting a new order that triggers the trigger
INSERT INTO orders (id, customer_id, amount) VALUES (1, 1001, 500);Pivot
-- Using PIVOT to transform rows into columns
SELECT *
FROM
(
SELECT customer_id, product, amount
FROM sales
) AS sales_data
PIVOT
(
SUM(amount)
FOR product IN ('Product A', 'Product B', 'Product C')
) AS pivoted_sales;Cursors
-- Using a cursor to iterate over a result set and perform operations
DECLARE @customer_id INT;
DECLARE @total_amount DECIMAL(10, 2);
DECLARE customer_cursor CURSOR FOR
SELECT id, total_amount
FROM customers;
OPEN customer_cursor;
FETCH NEXT FROM customer_cursor INTO @customer_id, @total_amount;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Perform operations with @customer_id and @total_amount
FETCH NEXT FROM customer_cursor INTO @customer_id, @total_amount;
END;
CLOSE customer_cursor;
DEALLOCATE customer_cursor;Link :
Views, Indexes and Auto Increment in SQL
In this we covered Views, Indexes and Auto Increment in SQL. Views are helpful in creating complex queries and implementing readability in the SQL statements. Views also help in maintaining data integrity as it shows an accurate and consistent views of data in the database. Most importantly, this helps in providing better security to the data.
Views
-- Creating a view to display high-value orders
CREATE VIEW high_value_orders AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE total_amount > 1000;Indexes
-- Creating an index on a column
CREATE INDEX idx_customers_name ON customers (name);
-- Creating a unique index on a column
CREATE UNIQUE INDEX idx_orders_order_id ON orders (order_id);Auto Increment
-- Creating a table with an auto-increment column
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(50),
PRIMARY KEY (customer_id)
);
-- Inserting a new row into the table with auto-incremented value
INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]');Link :
Query optimizations, Performance tuning in SQL
In this we covered Query Optimization and Performance tuning in SQL.
An optimized SQL query helps develop a better execution plan, used to analyze the runtime duration of the queries, Indexes which help fasten the query execution and organize the tables in most optimized way to make read and write operations faster and efficient.
Query optimization is the process of implementing the most efficient and optimal techniques that can be used to improve query performance and the runtime ( as well as other metrics).
Link :
Introduction to MySQL, PostgreSQL and Mongo DB, Comparison between MySQL and PostgreSQL and Mongo DB, Introduction to SQL and NoSQL Databases
In this we covered MySQL, PostgreSQL and MongoDB.
Link :
MySQL in Depth
In this we covered MySQL in Depth. MySQL databases are relational which means the the data is organized in the form of tables, rows, columns and views and rules are established for the relationships between the different tables, data fields — such as one to one, one to many, many to one and many to many.
Link :
PostgreSQL inDepth
In this we covered PostgreSQL in Depth. PostgreSQL is a relational DBMS which is open source and has many features such as — Multi version concurrency, point in time recovery, async replication, SQL sub selects, Views, Transaction, Trigger, Complex SQL queries etc. It stores the data securely and supports the best practices. It helps developers build fault tolerant applications and protect data integrity.
Link :
That’s it for now. Day 7 : Coming Soon!
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
System Design Case Studies — In Depth
Complete Data Structures and Algorithm Series
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
