SQL Cheatsheet for Data Analytics Cheat Sheet

by sourabhsahu12345 via cheatography.com/192985/cs/40126/

RDBMS Softwares BETWEEN AND IN SUBQUERIES

Oracle MYSQL IN checks if a column value matches any Scalar Subquery: Returns a single value
MS SQL Server MariaDB value in a list, while BETWEEN checks if a and can be used within SELECT, WHERE,
column value falls within a specified range. or FROM clauses.

Follow @Sourabh Sahu SELECT * FROM product WHERE category SELECT produc​t_name, (SELECT
IN ('Elec​tro​nics', 'Cloth​ing', 'Furni​ture'); MAX(price) FROM products) AS max_price
SELECT * FROM product WHERE price FROM products;
QUERYING DATA BETWEEN 20 AND 50; Single-Row Subquery: Subquery that
Fetch all columns from the products table: returns a single row of results, typically
SELECT * FROM products; CONSTR​AINTS used in comparison operat​ions.

Fetch product IDs and names from the Constr​aints are rules applied to database SELECT produc​t_name FROM products
inventory table: SELECT produc​t_id, tables to ensure data accuracy, integrity, WHERE price = (SELECT MAX(price)
produc​t_name FROM inventory; and consis​tency. FROM products);

Fetch employee names sorted by their PRIMARY KEY enforces unique, non-null Multi-Row Subquery: Subquery that returns
salary in descending order: SELECT values in the "​pro​duc​t_i​d" column. multiple rows, often used with IN, ANY, or
employ​ee_name FROM employees ORDER ALL operators.
FOREIGN KEY links the "​man​ufa​ctu​rer​_id​"
BY salary DESC; column to a related table. SELECT produc​t_name FROM products
Fetch customer names sorted by their WHERE manufa​ctu​rer_id IN (SELECT
NOT NULL ensures the "​pro​duc​t_n​ame​"
regist​ration date in ascending order manufa​ctu​rer_id FROM manufa​cturers
must have a value.
(default): SELECT custom​er_name FROM WHERE country = 'USA');
CHECK enforces a condition, like ensuring
customers ORDER BY regist​rat​ion​_date; Correlated Subquery: References values
"​pri​ce" is positive.
from the outer query within the subquery.
DEFAULT assigns a default value to a
ALIASING (AS) SELECT produc​t_name FROM products p
column when no value is provided during
SELECT name AS produc​t_name FROM WHERE price > (SELECT AVG(price)
insertion.
products; FROM products WHERE manufa​ctu​rer_id =
CREATE TABLE product ( product_id INT
p.manu​fac​tur​er_id);
PRIMARY KEY, produc​t_name VARCHA​‐
INDEXING Inline View Subquery: Creates a temporary
R(255) NOT NULL, price DECIMA​L(10, 2)
Indexing improves data retrieval speed by table-like structure for complex subqueries
CHECK (price > 0), manufa​ctu​rer_id INT,
creating a reference system. It enhances within the FROM clause.
FOREIGN KEY (manuf​act​ure​r_id)
query perfor​mance but may slightly impact REFERENCES manufa​ctu​rer​(ma​nuf​act​ure​‐ SELECT AVG(su​bqu​ery.av​g_p​rice) FROM
insert​/update operat​ions. r_id), discount DECIMAL(5, 2) DEFAULT (SELECT manufa​ctu​rer_id, AVG(price) AS

Clustered Index: Dictates physical data 0.00 -- Default discount set to 0.00 ); avg_price FROM products GROUP BY
order, often used for primary keys. Non-Cl​‐ manufa​ctu​rer_id) AS subquery;
ustered Index: Creates separate index
struct​ures, allowing multiple indexes for
specific columns, without affecting data
storage order.

By sourabhsahu12345 Not published yet. Sponsored by ApolloPad.com

Last updated 3rd September, 2023. Everyone has a novel in them. Finish
Page 1 of 4. Yours!
https://apollopad.com
cheatography.com/sourabhsahu12345/
 SQL Cheatsheet for Data Analytics Cheat Sheet
by sourabhsahu12345 via cheatography.com/192985/cs/40126/

SQL and NO SQL SET OPERATION IS NULL / IS NOT NULL

Uses a struct​ured, Offers various flexible INTERSECT: Returns the common rows IS NULL: Filters rows where the "​man​ufa​‐
tabular data data models, suitable between two SELECT statem​ents. ctu​rer​_id​" is not assigned. SELECT * FROM
model with for semi-s​tru​ctured or Retrieves products that exist in both sets of product WHERE manufa​ctu​rer_id IS NULL;
predefined unstru​ctured data. results. IS NOT NULL: Filters rows where the "​man​‐
schemas. SELECT produc​t_id, produc​t_name FROM ufa​ctu​rer​_id​" is assigned. SELECT * FROM
Primarily Designed for products INTERSECT SELECT produc​t_id, product WHERE manufa​ctu​rer_id IS NOT
designed for horizontal scalab​ility, produc​t_name FROM some_o​the​r_t​able; NULL;
vertical scaling, making it easier to UNION: Combines the results of two
can be complex to handle high traffic and SELECT statem​ents, removing duplic​ates. FILTERING
scale horizo​ntally large datasets. Retrieves all unique products from both sets Retrieve all products with a price greater
of results. than $50: SELECT * FROM product
WINDOW AND RANKING FUNCTIONS
SELECT produc​t_id, produc​t_name FROM WHERE price > 50;
Window functions allow you to perform products UNION SELECT produc​t_id, Get products with a quantity in stock less
calcul​ations across a set of rows related to produc​t_name FROM some_o​the​r_t​able; than or equal to 10: SELECT * FROM
the current row within the result set. Rank product WHERE stock_​qua​ntity <= 10;
UNION ALL: Similar to UNION, but includes
functions assign a rank or row number to
all rows, including duplic​ates. Retrieves all Retrieve products with a specific category
each row based on specified criteria.
products from both sets of results, allowing (e.g., "​Ele​ctr​oni​cs"): SELECT * FROM
SELECT produc​t_name, price, ROW_NU​‐ duplic​ates. product WHERE category = 'Elect​ron​ics';
MBER() OVER (ORDER BY price) AS
SELECT produc​t_id, produc​t_name FROM Find products with names containing the
row_num, DENSE_​RANK() OVER (ORDER
products UNION ALL SELECT produc​t_id, word "​pho​ne": SELECT * FROM product
BY price) AS dense_​rank, SUM(price)
produc​t_name FROM some_o​the​r_t​able; WHERE produc​t_name LIKE '%phone%';
OVER (PARTITION BY manufa​ctu​rer_id)
EXCEPT: Returns rows that exist in the first Get products added after a certain date
AS manufa​ctu​rer​_total FROM products;
SELECT statement but not in the second. (e.g., '2023-​01-​01'): SELECT * FROM
we use window functions like ROW_NU​‐
Retrieves products that are in the "​pro​duc​ts" product WHERE date_added > '2023-​01-
MBER, DENSE_​RANK, and SUM with the "​‐
table but not in "​som​e_o​the​r_t​abl​e." 01';
pro​duc​ts" table to assign row numbers,
SELECT produc​t_id, produc​t_name FROM Retrieve products with a price between $20
dense ranks, and calculate the total price for
products EXCEPT SELECT produc​t_id, and $30: SELECT * FROM product WHERE
each manufa​ctu​rer's products.
produc​t_name FROM some_o​the​r_t​able; price BETWEEN 20 AND 30;
RANK assigns the same rank to rows with
Find products with low stock and a price
equal values, leaving gaps, while
greater than $50: SELECT * FROM product
DENSE_RANK assigns consec​utive ranks
WHERE stock_​qua​ntity < 5 AND price > 50;
without gaps, and ROW_NUMBER assigns
a unique row number to each row.

By sourabhsahu12345 Not published yet. Sponsored by ApolloPad.com

Last updated 3rd September, 2023. Everyone has a novel in them. Finish
Page 2 of 4. Yours!
https://apollopad.com
cheatography.com/sourabhsahu12345/
 SQL Cheatsheet for Data Analytics Cheat Sheet
by sourabhsahu12345 via cheatography.com/192985/cs/40126/

FILTERING (cont) JOINS (cont) PERFOR​MANCE OPTIMI​ZATION

Retrieve products from a specific manufa​‐ SELECT s1.sal​esp​ers​on_​name, s2.sal​esp​‐ Indexing: Use approp​riate indexes.
cturer (e.g., "​Sam​sun​g"): SELECT * FROM ers​on_name FROM sales AS s1 JOIN sales Optimize Queries: Write efficient SQL.
product WHERE manufa​cturer = 'Samsung'; AS s2 ON s1.pro​duct_id = s2.pro​duct_id
Limit Data Retrieval: Fetch only needed
WHERE s1.sal​esp​ers​on_name <> s2.sal​‐
data.
JOINS esp​ers​on_​name;
Normal​iza​tion: Properly structure tables.
INNER JOIN: Returns only the rows with
AGGREG​ATION AND GROUPING Stored Proced​ures: Use precom​piled
matching values in both tables.
proced​ures.
SELECT * FROM sales INNER JOIN Aggreg​ation functions like COUNT, SUM,
AVG, MAX, and MIN are used to perform Table Partit​ioning: Divide large tables.
products ON sales.p​ro​duct_id = produc​ts.p​‐
ro​duc​t_id; calcul​ations on data. GROUP BY clause is Regular Mainte​nance: Rebuild indexes,
used to group rows based on one or more update stats.
LEFT JOIN (or LEFT OUTER JOIN):
columns. Aggreg​ation functions are applied Optimize Hardware: Ensure server
Returns all sales records and their corres​‐
to each group, providing summary inform​‐ resources.
ponding products. If a product has no sales,
ation.
it still appears with NULL values in sales-​‐ Caching: Implement caching mechan​isms.
related columns. -- Count the total number of products
Use Proper Data Types: Choose suitable
SELECT COUNT(*) AS total_​pro​ducts
SELECT * FROM products LEFT JOIN types.
FROM products;
sales ON produc​ts.p​ro​duct_id = sales.p​ro​‐ Concur​rency Control: Manage transa​ctions
duc​t_id; -- Calculate the total price of all products
carefully.
SELECT SUM(price) AS total_​price FROM
RIGHT JOIN (or RIGHT OUTER JOIN):
products;
Opposite of the LEFT JOIN. Returns all Data Types
sales records and includes products that -- Calculate the average price of products
CHAR(N): VARCHA​ VARCHAR and
have no sales SELECT AVG(price) AS averag​e_price
Fixed-​‐ R(N): VARCHAR2 are
FROM products;
SELECT * FROM sales RIGHT JOIN length Variab​le-​‐ used interc​han​‐
products ON sales.p​ro​duct_id = produc​ts.p​‐ -- Find the highest product price SELECT
character length geably in most
ro​duc​t_id; MAX(price) AS highes​t_price FROM
string with character databases, but
products;
FULL OUTER JOIN: Returns all sales a string VARCHAR2 is
records and all products, including those -- Find the lowest product price SELECT specified with a specific to Oracle
without sales. NULL values appear where MIN(price) AS lowest​_price FROM maximum maximum databases.
there is no match. products; length of length of
N. N.
SELECT * FROM products FULL OUTER
DIMENS​IONAL MODELLING
JOIN sales ON produc​ts.p​ro​duct_id = INTEGE​‐ BLOB: DATETIME:
sales.p​ro​duc​t_id; A design approach for data wareho​using. R,F​LOA​‐ Stores Combines date
Organizes data into fact tables (quant​itative T,B​‐ binary and time in
SELF JOIN: A self join could be used if the
data) and dimension tables (descr​iptive OOLEAN large 'YYYY-​MM-DD
"​sal​es" table contains inform​ation about
data). objects, HH:MM:SS'
salesp​eople who sell products. You might
such as format.
join the table with itself to identify salesp​‐ Simplifies complex queries, improves
images or
eople who have sold the same products. perfor​mance, and supports business
files.
analytics by creating a logical structure for
data analysis.

By sourabhsahu12345 Not published yet. Sponsored by ApolloPad.com

Last updated 3rd September, 2023. Everyone has a novel in them. Finish
Page 3 of 4. Yours!
https://apollopad.com
cheatography.com/sourabhsahu12345/
 SQL Cheatsheet for Data Analytics Cheat Sheet
by sourabhsahu12345 via cheatography.com/192985/cs/40126/

Data Types (cont)

DECIMAL(P, DATE: TIME:

S): Fixed-​point Stores a Represents
decimal number date a time of
with P total value in day in
digits and S the format 'HH:MM:SS'
decimal places. 'YYYY-​‐ format.
MM-DD'.

By sourabhsahu12345 Not published yet. Sponsored by ApolloPad.com

Last updated 3rd September, 2023. Everyone has a novel in them. Finish
Page 4 of 4. Yours!
https://apollopad.com
cheatography.com/sourabhsahu12345/