Day 14 of 15 Days of Advanced SQL Series
Welcome back peeps. Hope all’s well.
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 14 of 15 days of Advanced SQL, we will cover —
MySQL database in depth
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 14.
- MySQL is a relational database management system (RDBMS) that uses the Structured Query Language (SQL) to manage and manipulate the data stored in databases.
- When a user submits a query, MySQL’s query optimizer analyzes the query and generates an execution plan. The query optimizer selects the most efficient way to retrieve the data based on the available indexes and the structure of the tables.
- Once the execution plan is generated, MySQL’s storage engine retrieves the data from the appropriate data files, and the query is executed. The results are returned to the user and the connection is closed.
- MySQL uses a client-server architecture, where the client program sends SQL statements to the server, and the server returns the results. The client and server can run on the same machine or on different machines connected over a network.
- MySQL supports various types of storage engines, each with its own advantages and disadvantages. The most common storage engine is InnoDB, which supports transactions, foreign keys, and row-level locking. Other storage engines include MyISAM, which is optimized for read-heavy workloads, and Memory, which stores all data in memory.
Some important functions —
COUNT(): The COUNT() function is used to count the number of rows returned by a query or the number of occurrences of a specific value in a column.
-- Count the number of rows in a table
SELECT COUNT(*) FROM your_table;-- Count the number of occurrences of a specific value in a column
SELECT COUNT(column_name) FROM your_table WHERE column_name = 'value';SUM(): The SUM() function is used to calculate the sum of values in a column.
-- Calculate the sum of values in a column
SELECT SUM(column_name) FROM your_table;AVG(): The AVG() function is used to calculate the average value of a column.
-- Calculate the average value of a column
SELECT AVG(column_name) FROM your_table;MIN(): The MIN() function is used to find the minimum value in a column.
-- Find the minimum value in a column
SELECT MIN(column_name) FROM your_table;MAX(): The MAX() function is used to find the maximum value in a column.
-- Find the maximum value in a column
SELECT MAX(column_name) FROM your_table;CONCAT(): The CONCAT() function is used to concatenate two or more strings together.
-- Concatenate two columns together
SELECT CONCAT(column1, column2) FROM your_table;GROUP_CONCAT(): The GROUP_CONCAT() function is used to concatenate values from multiple rows into a single string, grouped by a specific column.
-- Concatenate values from multiple rows into a single string
SELECT column1, GROUP_CONCAT(column2) FROM your_table GROUP BY column1;DATE_FORMAT(): The DATE_FORMAT() function is used to format dates or timestamps according to a specific format.
-- Format a date column as "YYYY-MM-DD"
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM your_table;IFNULL(): The IFNULL() function is used to replace NULL values with a specified value.
-- Replace NULL values with a default value
SELECT IFNULL(column_name, 'N/A') FROM your_table;CASE statement: The CASE statement is used to perform conditional logic within a query and return different values based on specified conditions.
-- Perform conditional logic using CASE statement
SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS calculated_column
FROM your_table;ROW_NUMBER(): The ROW_NUMBER() function is used to assign a unique sequential number to each row in the result set, based on the specified ordering.
-- Assign a sequential number to each row
SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_number, column_name FROM your_table;In terms of data replication, MySQL supports several methods for replicating data across multiple servers for high availability and scalability. Some of these methods include Master-slave replication, Master-Master replication and Group Replication.
MySQL also supports various other features such as full-text search, spatial data support and triggers.
Note : A full course on practical MySQL is under progress and would be published here (subscribe today) —
We have already covered an introduction of MySQL database in our previous post (Day 13).
In this post we will go in depth and cover the most important constructs of MySQL.
MySQL databases are ideal for businesses where data security is a priority. It stores the data in the form of relational tables and supports master-slave and master-master replications.
To start with, we will cover MySQL most important constructs —
- MySQL Data Types
- MySQL Date and Time Functions
- MySQL common line client functions
- Work with databases and tables
- Work with views and triggers, stored functions
- Query the data from the tables
- Back up database to SQL file
- Insert, Update, delete, Repair tables
- Conditions, keys and privileges
Let’s get started —
MySQL Data Types
- For Characters — CHAR, VARCHAR
2. For text strings— TINYTEXT, TEXT, BLOB, LONGTEXT
3. For Integers — INTEGER, BIGINT, TINYINT
4. For decimals — FLOAT, DOUBLE, DECIMAL
5. For date and timestamp — DATE, DATETIME, TIMESTAMP, TIME
MySQL Date and Time Functions
- DAYOFWEEK — To show day of the week
2. WEEKDAY — To show weekday name
3. DAYOFMONTH — To show day of the month
4. MONTH — To show month
5. WEEK — To show week
6. YEAR — To show year
7. HOUR — To show hour of the day
8. MINUTE — To show minute of the day
9. SECOND — To show second of the day
10. MONTHNAME — To show month of the year
Implementation —
-- MySQL Data Types
CREATE TABLE data_types (
char_col CHAR(10),
varchar_col VARCHAR(255),
tinytext_col TINYTEXT,
text_col TEXT,
blob_col BLOB,
longtext_col LONGTEXT,
int_col INT,
bigint_col BIGINT,
tinyint_col TINYINT,
float_col FLOAT,
double_col DOUBLE,
decimal_col DECIMAL(10,2),
date_col DATE,
datetime_col DATETIME,
timestamp_col TIMESTAMP,
time_col TIME
);
-- MySQL Date and Time Functions
SELECT DAYOFWEEK(NOW()) AS day_of_week;
SELECT WEEKDAY(NOW()) AS weekday;
SELECT DAYOFMONTH(NOW()) AS day_of_month;
SELECT MONTH(NOW()) AS month;
SELECT WEEK(NOW()) AS week;
SELECT YEAR(NOW()) AS year;
SELECT HOUR(NOW()) AS hour;
SELECT MINUTE(NOW()) AS minute;
SELECT SECOND(NOW()) AS second;
SELECT MONTHNAME(NOW()) AS month_name;
-- MySQL Command Line Client Functions
-- Connect with MySQL command line interface
-- mysql -u [username] -p
-- Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
-- Show users
SELECT user, host FROM mysql.user;
-- Create backup using mysqldump
-- mysqldump -u username -p databasename > databasename_backup.sql
-- Working with Databases and Tables
-- Create database
CREATE DATABASE database_name;
-- Drop database
DROP DATABASE database_name;
-- Alter database
ALTER DATABASE database_name;
-- Show databases
SHOW DATABASES;
-- Use database
USE database_name;
-- Create table
CREATE TABLE table_name (
column1 DATATYPE,
column2 DATATYPE,
column3 DATATYPE
);
-- Alter table
ALTER TABLE table_name ADD COLUMN column_name DATATYPE;
ALTER TABLE table_name DROP COLUMN column_name;
-- Drop table
DROP TABLE table_name;
-- Describe table
DESCRIBE table_name;
-- Truncate table
TRUNCATE TABLE table_name;
-- Show tables
SHOW TABLES;
-- Working with Views and Triggers, Stored Procedures
-- Create view
CREATE VIEW view_name AS SELECT_STATEMENT;
-- Create or replace view
CREATE OR REPLACE VIEW view_name AS SELECT_STATEMENT;
-- Drop view
DROP VIEW view_name;
-- Rename view
RENAME TABLE view_name TO new_name;
-- Show views
SHOW FULL TABLES FROM database_name WHERE table_type = 'VIEW';
-- Create trigger
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE/ALTER
ON table_name
FOR EACH ROW SET operation;
-- Drop trigger
DROP TRIGGER trigger_name;
-- Show triggers
SHOW TRIGGERS FROM database_name WHERE condition;
-- Create stored procedure
CREATE PROCEDURE procedure_name(param_list)
BEGIN
SQL Statement;
END;
-- Drop stored procedure
DROP PROCEDURE procedure_name;
-- Show stored procedures
SHOW PROCEDURE STATUS WHERE condition;
-- Create stored function
CREATE FUNCTION function_name(param_list)
RETURNS datatype
BEGIN
SQL Statement;
END;
-- Drop stored function
DROP FUNCTION function_name;
-- Show stored functions
SHOW FUNCTION STATUS WHERE condition;
-- Querying Data from Tables
-- Select
SELECT column1, column2 FROM table_name;
-- Search using LIKE
SELECT * FROM table_name WHERE column_name LIKE '%ny%';
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]%';
-- Distinct
SELECT DISTINCT column_name FROM table_name;
-- Joins
SELECT column_names
FROM table1 JOIN table2
ON column_name1 = column_name2
WHERE condition;
-- Group by
SELECT column_names
FROM table
WHERE condition
GROUP BY column_names;
-- Order by
SELECT column_names
FROM table
WHERE condition
ORDER BY column_names;
-- Filter Data
SELECT t1
FROM table_name
WHERE t1 > 4;
-- Using other comparison operators
SELECT t1
FROM table_name
WHERE t1 != 'string';
SELECT t1, t2
FROM table_name
WHERE t1 = 17 AND t2 = 'string';
-- Backup database to SQL file
-- mysqldump -u user_name -p database_name > databasename_backup.sql
-- Insert, Update, Delete, Repair Tables
-- Insert
INSERT INTO table_name (column_1, column_2) VALUES (value_1, value_2);
-- Update
UPDATE table_name SET column_name = value WHERE condition;
-- Delete
DELETE FROM table_name WHERE condition;
-- Aggregate Functions
-- COUNT()
SELECT COUNT(employee_id) FROM employee_table WHERE salary > 40000;
-- SUM()
SELECT SUM(salary) FROM employee_table;
-- AVG()
SELECT AVG(salary) FROM employee_table;
-- MIN()
SELECT MIN(salary) FROM employee_table;
-- MAX()
SELECT MAX(salary) FROM employee_table;
-- Keys and Privileges
-- Create primary key
CREATE TABLE table (
..., PRIMARY KEY (col1, col2)
);
-- Create foreign key
CREATE TABLE table (
..., FOREIGN KEY (col1, col2) REFERENCES table2(t2_col1, t2_col2)
);
-- Create user
CREATE USER 'user_name'@'localhost';
-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';
-- Revoke privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';
MySQL common line client functions
- MySQL — to allow user to connect with MySQL command line interface
Syntax —
MySQL -u [username] -p
2. Create user — to allow create a new user
Syntax —
CREATE USER ‘USERNAME@LOCALHOST’ ‘Password’
3. Show user — To show all users who have access to the MySQL client
Syntax —
SELECT user, host from MySQL.User
4. mysqldump — to create a backup of a set of SQL statements.
Syntax —
MYSQLDUMP -u username -p databasename > databasename_backup.sql
Work with databases and tables
- Create database — to create a new database.
Syntax —
Create database database_name;
2. Drop database — To delete already existing database.
Syntax —
Drop Database database_name;
3. Alter database — To modify the existing database.
Syntax —
Alter database database_name;
4. Show database — To show/list all the databases available in the MySQL server.
Syntax —
Show Database;
5. Use database — To use a database/change the current one to another db.
Syntax —
Use database_name;
To work with tables —
- Create Table — To create a new table
Syntax —
Create TABLE table_name( Column1 DATATYPE, Column2 DATATYPE, Column3 DATATYPE, …. );
2. Alter Table — To add/modify an existing row/column in the table
Syntax —
Alter table table_name add/drop column column_name;
3. Drop table — To delete the entire table
Syntax —
Drop table table_name;
4. Describe table — To display all the columns of an existing table
Syntax —
Describe table_name;
5. Truncate table — To remove all the records in the MySQL table
Syntax —
Truncate table table_name;
6. Show table — To show all the tables in the current database
Syntax —
Show tables;
Work with views and triggers, stored functions
1.Create a new view
Syntax —
CREATE VIEW view_name
As SELECT_STATEMENT;
2. Create or replace a view
Syntax —
CREATE OR REPLACE VIEW view_name
As SELECT_STATEMENT;
3.Drop a view
Syntax —
DROP VIEW view_name;
4. Rename a view
Syntax —
RENAME TABLE view_name
TO new_name
5. Show views
Syntax —
SHOW FULL TABLES
FROM database_name
WHERE tabletype = ‘VIEW’;
Triggers
- Create a new trigger
Syntax —
CREATE TRIGGER trigger_name
BEFORE/AFTER
SQL Statement (INSERT/UPDATE/DELETE/ALTER)
ON table_name
FOR EACH ROW SET operation;
2. Drop a trigger
Syntax —
DROP Trigger trigger_name;
3. Show triggers
Syntax —
SHOW Triggers FROM database_name
WHERE condition
Stored Procedures
- Create a stored procedure
Syntax —
CREATE PROCEDURE procedure_name(param_list)
BEGIN SQL Statement;
END;
2. Drop a stored procedure
Syntax —
Drop Procedure procedure_name;
3. Show stored procedure
Syntax —
Show procedure status
WHERE condition;
Stored Functions
- Create a stored functions
Syntax —
CREATE FUNCTION function_name(param_list)
Returns datatype
BEGIN
SQL Statement
END;
2. Drop a stored functions
Syntax —
Drop Procedure procedure_name;
3. Show a stored functions
Syntax —
Show function status
WHERE condition;
Query the data from the tables
- Select
Syntax —
SELECT t1, t2, … FROM table_name;
2. Search using like
Syntax —
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]%’
3. Distinct
Syntax —
SELECT DISTINCT (column_name) FROM table_name;
4. Joins
Syntax —
SELECT column_names
FROM table1 JOIN table2
ON column_name1 = column_name2
WHERE condition
5. Group by
Syntax —
SELECT column_names
FROM table
WHERE condition
GROUP BY column_names
6. Order by
Syntax —
SELECT column_names
FROM table
WHERE condition
ORDER BY column_names
7. Filter Data
Syntax —
Select t1
from table_name
Where t1 > 4
Using other comparison Operator —
Select t1
from table_name
Where t1 != ‘string’
Select t1,t2
from table_name
Where t1 == 17 AND t2 == ‘string’
Back up database to SQL file
To backup database —
mysqldump -u user_name -p database_name > databasename_backup.sql
Insert, Update, delete, Repair tables
- Insert
Syntax —
Insert into table_name ( column_1, column_2) values (values_1,values_2,…values_n);
2. Update
Syntax —
Update table_name SET Column_name = value where Condition;
3. Delete
Syntax —
Delete FROM table_name;
Aggregate Functions
Most important aggregate Functions —
- 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
Keys and privileges
- Create keys like primary keys and foreign keys —
CREATE TABLE table (…, PRIMARY KEY (col1, col2))
CREATE TABLE table (…, FOREIGN KEY (col1, col2) REFERENCES table2(t2_col1, t2_col2))
2. To create or grant or revoke privileges to the users —
CREATE USER ‘user_name’@’localhost’;
GRANT SELECT, INSERT, UPDATE ON base.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘username’@’host’;
Snippet —
That’s it for now.
Find Day 15 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
