Day 19 of 30 days of Data Engineering Series with Projects

Welcome back peeps to Day 19 of Data Engineering Series with Projects!

In this we will cover —

PostgreSQL

List database, views and tables

Create, Delete and Alter table in the database

Views , triggers and Indexes

Query data from tables

Create Roles and Change the Roles

Performance and Statistics

MySQL

MySQL Data Types

MySQL Date and Time Functions

MySQL CLI commands

Work with database 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

Aggregate Functions

Keys and privileges

Pre-requisite to Day 19 is to complete Day 1–18( link below):

Day 1 : What’s Data Engineering, Why Data Engineering, Data Engineers — ML Engineers — Data Scientists, Purpose and Scope

Day 2 : Complete Python for Data Engineering — Part 1

Day 3 : Complete Advanced Python for Data Engineering — Part 2

Day 4: Techniques to write efficient and Optimized Code

Day 5 : SQL

Day 6 : Advanced SQL

Day 7 : BigQuery and SQL vs NOSQL databases

Day 8 : Advanced Functions

Day 9 : Query Optimizations

Day 10 : MySQL and PostgreSQL

Day 11: Shell scripting and Linux “touch” command

Day 12 : Map Reduce, Data Warehouse, Data Lakes

Day 13: Pandas, Pandas, Data Cleaning and processing, Outlier Detection, Noisy Data, Missing Data, Pandas Functions, Aggregate Functions, Joins

Day 14 : Numpy

Day 15 : Advanced Pandas Techniques

Day 16 : Data Pre-processing, Handling missing values, Data Cleaning, Mean/mode/median Imputation, Hot Deck Imputation, Rescale Data, Binarize Data, Regression Imputation, Stochastic regression imputation, Feature Scaling

Day 17 : Data Augmentation, Read and Process Large Datasets

Day 18 : Data Visualization basics, Data Visualization Projects, Data Visualization using Plotly and Bokeh, Data Profiling, Summary Functions, Indexing, Grouping, Linear Regression, Multi Linear Regression, Polynomial Regression, Regression, Support Vector Regression, Decision Tree Regression, Random Forest Regression, Feature Engineering, GroupBy Features, Categorical and Numerical Features, Missing Value Analysis, Fill the missing Values, Unique Value Analysis, Univariate Analysis, Bivariate Analysis, Multivariate Analysis, Correlation Analysis, Spearman’s ρ, Pearson’s r, Kendall’s τ, Cramér’s V (φc), Phik (φk)

Day 19 : MySQL and PostgreSQL

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!

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 Ignito:

System Design Case Studies — In Depth

Design Instagram

Design Netflix

Design Reddit

Design Amazon

Design Messenger App

Design Twitter

Design URL Shortener

Design Dropbox

Design Youtube

Design API Rate Limiter

Design Web Crawler

Design Amazon Prime Video

Design Facebook’s Newsfeed

Design Yelp

Design Uber

Design Tinder

Design Tiktok

Design Whatsapp

Most Popular System Design Questions

Mega Compilation : Solved System Design Case studies

Let’s get started!

To start with, we will cover MySQL most important constructs —

1. MySQL Data Types
2. MySQL Date and Time Functions
3. MySQL common line client functions
4. Work with databases and tables
5. Work with views and triggers, stored functions
6. Query the data from the tables
7. Back up database to SQL file
8. Insert, Update, delete, Repair tables
9. Conditions, keys and privileges

Implementation —

-- PostgreSQL

-- List databases, views, and tables
SELECT datname FROM pg_database;
SELECT viewname FROM pg_views;
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- Create table
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INTEGER
);

-- Delete table
DROP TABLE my_table;

-- Alter table
ALTER TABLE my_table ADD COLUMN email VARCHAR(100);

-- Create view
CREATE VIEW my_view AS SELECT * FROM my_table;

-- Create trigger
CREATE TRIGGER my_trigger BEFORE INSERT ON my_table FOR EACH ROW EXECUTE FUNCTION my_function();

-- Create index
CREATE INDEX my_index ON my_table (name);

-- Query data from table
SELECT * FROM my_table;

-- Create role
CREATE ROLE my_role LOGIN PASSWORD 'mypassword';

-- Change role
SET ROLE my_role;

-- Performance and statistics
EXPLAIN SELECT * FROM my_table;
ANALYZE my_table;


-- MySQL

-- MySQL data types
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- MySQL date and time functions
SELECT NOW();
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM my_table;

-- MySQL CLI commands
SHOW DATABASES;
USE database_name;
SHOW TABLES;

-- Work with database and tables
CREATE DATABASE my_database;
DROP DATABASE my_database;
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
DROP TABLE my_table;

-- Work with views and triggers, stored functions
CREATE VIEW my_view AS SELECT * FROM my_table;
CREATE TRIGGER my_trigger BEFORE INSERT ON my_table FOR EACH ROW BEGIN ... END;
CREATE FUNCTION my_function() RETURNS INT BEGIN ... END;

-- Query data from tables
SELECT * FROM my_table;

-- Back up database to SQL file
mysqldump -u username -p database_name > backup.sql

-- Insert, update, delete, repair tables
INSERT INTO my_table (id, name, age) VALUES (1, 'John', 30);
UPDATE my_table SET age = 35 WHERE id = 1;
DELETE FROM my_table WHERE id = 1;
REPAIR TABLE my_table;

-- Aggregate functions
SELECT COUNT(*) FROM my_table;
SELECT AVG(age) FROM my_table;

-- Keys and privileges
ALTER TABLE my_table ADD PRIMARY KEY (id);
GRANT SELECT, INSERT, UPDATE ON my_table TO my_user;

MySQL Data Types

1. 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

1. 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

Snippet —

MySQL common line client functions

1. 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

1. 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 —

1. 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

1. 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

1. 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

1. 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

1. 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

1. 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

1. 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’;

Let’s cover the most important constructs of PostgreSQL as follows —

• Access the PostgreSQL server
• Connect to a database
• List database, views and tables
• Create, Delete and Alter databases
• Views and Indexes
• Query data from tables
• SET operations
• Insert, Delete and Update
• Performance and Statistics

Implementation —

-- Access the PostgreSQL server
psql -h localhost -p 5432 -U username -d database_name

-- Connect to a database
\c database_name;

-- List databases, views, and tables
SELECT datname FROM pg_database;
SELECT viewname FROM pg_views;
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- Create database
CREATE DATABASE new_database;

-- Delete database
DROP DATABASE database_name;

-- Alter database
ALTER DATABASE database_name RENAME TO new_name;

-- Create view
CREATE VIEW my_view AS SELECT * FROM my_table;

-- Create index
CREATE INDEX my_index ON my_table (column_name);

-- Query data from tables
SELECT * FROM my_table;

-- SET operations
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
DELETE FROM table_name WHERE condition;
UPDATE table_name SET column1 = value1 WHERE condition;

-- Performance and statistics
EXPLAIN SELECT * FROM my_table;
ANALYZE my_table;

Access the PostgreSQL server

To access the server from psql

Syntax —

psql -U [user_name];

Connect to a database and check version

1. To connect to a database —

Syntax —

\c database_name

2. Check version

Syntax —

psql -V

List database, views and tables

1. List database

Syntax —

psql -l

2. List views

Syntax —

\dv

3. List tables

Syntax —

\dt

Create, Delete and Alter table in the database

1. Create new table Database

Syntax —

CREATE TABLE table_name

( primarykey SERIAL PRIMARY KEY, c1 type(size) NOT NULL, … );

2. Delete Table

Syntax —

DROP TABLE table_name CASCADE;

3. Alter Table

Syntax —

ALTER TABLE table_name DROP COLUMN column_name;

Views , triggers and Indexes

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

1. 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

Indexes

To create Indexes —

CREATE INDEX index_name

ON table (column_1, column_2, …column_n)

Query data from tables

1. 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’

SET operations

For UNION

Syntax —

Select t1, t2 FROM table1 UNION Select r1, r2 FROM table2

INTERSECT

Syntax —

Select t1, t2

FROM table_name

WHERE Condition

INTERSECT

Select r1, r2

FROM table_name

WHERE Condition

EXCEPT

Syntax —

Select t1, t2

FROM table_name

WHERE Condition

EXCEPT

Select r1, r2

FROM table_name

WHERE Condition

Insert, Delete and Update

1. 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;

Create Roles and Change the Roles

Create Role

Syntax —

CREATE ROLE role_name;

Change the Role

Syntax —

SET Role newrole_name;

Performance and Statistics

For statistics collection

Syntax —

ANALYZE table_name;

To show and execute query plan

Syntax —

EXPLAIN ANALYZE sql_query;

Snippet —

That’s it for now.

Find Day 20 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 —

All the Complete System Design Series Parts —

1. System design basics

2. Horizontal and vertical scaling

3. Load balancing and Message queues

4. High level design and low level design, Consistent Hashing, Monolithic and Microservices architecture

5. Caching, Indexing, Proxies

6. Networking, How Browsers work, Content Network Delivery ( CDN)

7. Database Sharding, CAP Theorem, Database schema Design

8. Concurrency, API, Components + OOP + Abstraction

9. Estimation and Planning, Performance

10. Map Reduce, Patterns and Microservices

11. SQL vs NoSQL and Cloud

12. Most Popular System Design Questions

Github —

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