Day 2 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 2 of 15 days of Advanced SQL, we will cover —
Categories in SQL
Most Important SQL commands
Built-in Functions
NULL values
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 2.
There are five Categories in SQL —
- 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]
Implementation —
-- DQL (Data Query Language) - SELECT
SELECT * FROM customers;
SELECT name, age FROM customers WHERE age > 18;
-- DML (Data Manipulation Language) - INSERT, UPDATE, DELETE, LOCK, CALL
INSERT INTO customers (name, age) VALUES ('John Doe', 25);
UPDATE customers SET age = 30 WHERE name = 'John Doe';
DELETE FROM customers WHERE name = 'John Doe';
LOCK TABLE customers IN SHARE MODE;
CALL stored_procedure();
-- DDL (Data Definition Language) - CREATE, DROP, ALTER, TRUNCATE, RENAME
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);
DROP TABLE customers;
ALTER TABLE customers ADD COLUMN email VARCHAR(255);
TRUNCATE TABLE customers;
RENAME TABLE customers TO clients;
-- DCL (Data Control Language) - GRANT, REVOKE
GRANT SELECT, INSERT ON customers TO user1;
REVOKE DELETE ON customers FROM user2;
-- TCL (Transaction Control Language) - ROLLBACK, COMMIT, SAVEPOINT, SET TRANSACTION
START TRANSACTION;
SAVEPOINT savepoint1;
INSERT INTO customers (name, age) VALUES ('Jane Smith', 35);
ROLLBACK TO savepoint1;
COMMIT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;I’ll cover the most important commands that you need to know.
The most important SQL commands include:
- SELECT: used to retrieve data from one or more tables in a database.
- INSERT: used to add new data to a table in a database.
- UPDATE: used to modify existing data in a table in a database.
- DELETE: used to delete data from a table in a database.
- CREATE: used to create a new table or database.
- ALTER: used to modify the structure of a table or database.
- DROP: used to delete a table or database.
- JOIN: used to combine data from multiple tables based on related columns.
- WHERE: used to specify conditions that must be met for a query to retrieve or modify data.
- GROUP BY: used to group data retrieved by a query based on one or more columns.
Implementation —
-- SELECT: Retrieve data from a table
SELECT * FROM customers;
SELECT name, age FROM customers WHERE age > 18;
-- INSERT: Add new data to a table
INSERT INTO customers (name, age) VALUES ('John Doe', 25);
-- UPDATE: Modify existing data in a table
UPDATE customers SET age = 30 WHERE name = 'John Doe';
-- DELETE: Delete data from a table
DELETE FROM customers WHERE name = 'John Doe';
-- CREATE: Create a new table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
salary DECIMAL(10,2)
);
-- ALTER: Modify the structure of a table
ALTER TABLE employees ADD COLUMN department VARCHAR(100);
-- DROP: Delete a table
DROP TABLE employees;
-- JOIN: Combine data from multiple tables
SELECT orders.order_id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
-- WHERE: Specify conditions for data retrieval or modification
SELECT * FROM employees WHERE age > 30;
-- GROUP BY: Group data based on one or more columns
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;These commands are considered the most important because they provide the basic functionality to interact with a database and allow for the retrieval, modification, and manipulation of data stored in a relational database.
Snippet —
Create a table
It is used to create a table .
Syntax —
CREATE TABLE table_name(
t1 datatype(len),
t2 datatype(len)
…
PRIMARY KEY(t1)
)
Insert into a table
It is used to insert values in a table .
Syntax —
INSERT INTO table_name1(t1,t2)
SELECT t1,t2 from table_name2
WHERE conditions
Update a table
It is used to update a table.
Syntax —
UPDATE table_name
SET t1 = k1
WHERE conditions
Alter Table
It is used to add columns to the database table.
Syntax —
ALTER TABLE table_name
Add column_name
Delete from a table
It is used to delete from a table based on the where condition .
Syntax —
DELETE from table_name
WHERE Conditions
Drop a table
It is used to remove table from the database.
Syntax —
DROP table table_name
SELECT statements
Select from all the rows and columns form table
Select * from table_name
Select column t1, t2 and all the rows for the table
Select t1 , t2 from table_name
Select column t1, t2 with where condition
Select t1, t2 from table_name
where conditions
order by t1 ASC
Select distinct rows by columns t1 and t2
Select distinct t1, t2
from table_name
Select column t1 and use aggregate function
Select t1, aggregate(expression)
from table_name
Group By t1
Select column t1 and t2 and use filter group
Select t1, aggregate(expression) as t3
from table_name
Group By t1
Having t3 < value
LIKE Operator
Like operator % is used to replace any number of characters.
Syntax —
Select t1
from table_name
where t1 LIKE ‘%condition’
Built in Functions —
Round up to a nearest integer
Select ROUND(1601.678943)
Round number up
Select Ceil(16.3)
Round number down
Select floor(16.8)
Get absolute value
Select ABS(-16)
Get Square root of a number
Select SQRT(16)
Count no of characters in the string
Select LENGTH(‘String’)
Convert all the letters to uppercase
Select UPPER(‘string’)
Convert all the letters to lowercase
Select LOWER(‘STRING’)
Get a substring
Select SUBSTRING(‘string’,3)
Replace a string
Select REPLACE(‘main_string’,’to_be_replace_string',’replace_string’)
How to retrieve NULL values
To retrieve all the rows with a missing value
Select t1 , t2 from table_name
Where t1 is NULL
To replace NULL with a value we use COALESCE—
Select t1, COALESCE(t1,’Missing value’)
from table_name
That’s it for now.
Find Day 3 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
