Day 4 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 Day4 of 15 days of Advanced SQL, we will cover —
Set Theory Operations
Stored Procedures
CASE Statements
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 4.
- Set Theory Operations: In mathematics, set theory is the study of sets, which are collections of objects. There are several basic operations that can be performed on sets, including union, intersection, and complement. The union of two sets is the set of all elements that are in either of the original sets, the intersection is the set of all elements that are in both of the original sets, and the complement is the set of all elements that are in one set but not in the other.
- Stored Procedures: A stored procedure is a precompiled collection of SQL statements that can be executed with a single call. Stored procedures can accept input parameters, return output parameters, and return a result set. They are often used to encapsulate complex logic or business rules, or to improve performance by reducing network traffic and reducing the amount of SQL that needs to be sent to the database.
- CASE Statements in SQL: The CASE statement is a control flow statement in SQL that allows you to perform different actions based on different conditions. It can be used in SELECT, UPDATE, and DELETE statements. The basic syntax of the CASE statement is as follows: CASE WHEN condition THEN result WHEN condition THEN result ELSE result END It will evaluate the condition and return the corresponding result.
Implementation —
-- Set theory operations: UNION, INTERSECT, and EXCEPT
-- Create sample tables
CREATE TABLE setA (id INT);
CREATE TABLE setB (id INT);
-- Insert sample data
INSERT INTO setA VALUES (1), (2), (3);
INSERT INTO setB VALUES (2), (3), (4);
-- Union: combine all unique elements from setA and setB
SELECT id FROM setA
UNION
SELECT id FROM setB;
-- Intersection: find common elements between setA and setB
SELECT id FROM setA
INTERSECT
SELECT id FROM setB;
-- Complement: find elements in setA but not in setB
SELECT id FROM setA
EXCEPT
SELECT id FROM setB;
-- Stored procedures
-- Create a stored procedure
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
SELECT COUNT(*) AS EmployeeCount FROM Employees;
END;
-- Execute the stored procedure
EXEC GetEmployeeCount;
-- CASE statement
-- Update employees' salary based on their position
UPDATE Employees
SET Salary = CASE
WHEN Position = 'Manager' THEN Salary * 1.1
WHEN Position = 'Supervisor' THEN Salary * 1.05
ELSE Salary
END
WHERE Department = 'Sales';Snippet —
Set Theory Operations
The main operations are —
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
UNION and UNION ALL
It is used to combine the results column wise vertically and remove duplicates using two or more select statements.
Format —
Select t1, t2 FROM table1 UNION Select r1, r2 FROM table2
Union removes the duplicates by default. In order to keep the duplicate values, use UNION ALL
Select t1, t2 FROM table1 UNION ALL Select r1, r2 FROM table2
INTERSECT
It is used to combine and return only those rows that are common to both or more SELECT statements.
Format —
Select t1, t2
FROM table_name
WHERE Condition
INTERSECT
Select r1, r2
FROM table_name
WHERE Condition
EXCEPT
It is used to combine and return only those rows which are not present in the second SELECT statement in the Query.
Format —
Select t1, t2
FROM table_name
WHERE Condition
EXCEPT
Select r1, r2
FROM table_name
WHERE Condition
Implementation —
-- Create sample tables
CREATE TABLE Employees (
id INT,
name VARCHAR(50)
);
CREATE TABLE Managers (
id INT,
name VARCHAR(50)
);
-- Insert sample data
INSERT INTO Employees VALUES (1, 'John'), (2, 'Jane'), (3, 'Michael');
INSERT INTO Managers VALUES (2, 'Jane'), (4, 'Robert');
-- UNION: combine the results of two SELECT statements (removes duplicates)
SELECT id, name FROM Employees
UNION
SELECT id, name FROM Managers;
-- UNION ALL: combine the results of two SELECT statements (includes duplicates)
SELECT id, name FROM Employees
UNION ALL
SELECT id, name FROM Managers;
-- INTERSECT: find common rows between two SELECT statements
SELECT id, name FROM Employees
INTERSECT
SELECT id, name FROM Managers;
-- EXCEPT: find rows in the first SELECT statement that are not in the second SELECT statement
SELECT id, name FROM Employees
EXCEPT
SELECT id, name FROM Managers;Snippet —
Stored Procedures
It’s a group of SQL statements that are combined and stored together in a database and used to perform manipulation operation.
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.
Format —
CREATE or REPLACE PROCEDURE procedure_name(parameters)
AS
SELECT * from table_name [ This can be a group of SQL statement]
GO;
To execute procedure that you have created —
EXEC procedure_name [ Pass Parameters]
Implementation —
-- Create a stored procedure
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
-- Query to get the count of employees
SELECT COUNT(*) AS EmployeeCount
FROM Employees;
END;CASE Statements
It is used to return a result or a value based on the specified condition once it’s satisfied/when its true. It operates like an if/else logical statement.
Format —
CASE WHEN condition_stmt THEN answer1 WHEN condition_stmt THEN answer2
WHEN condition_stmt THEN answer3 WHEN condition_stmt THEN answerN ELSE result END
Example —
CASE parking_fee
WHEN 40 THEN ‘Expensive’
WHEN 20 THEN ‘Good’
WHEN 10 THEN ‘Affordable’
ELSE ‘free’
END
All the condition specified in the CASE statements are evaluated in the order and it returns the result when the condition is satisfied/true.
Implementation —
-- Get employee bonus based on their salary
SELECT EmployeeName,
Salary,
CASE
WHEN Salary > 5000 THEN 'High Bonus'
WHEN Salary > 3000 THEN 'Medium Bonus'
ELSE 'Low Bonus'
END AS Bonus
FROM Employees;That’s it for now.
Find Day 5 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
