SQL/Python Refresher

SELECT

Retrieve data

FROM

Specify table

WHERE

Filter row before aggregation

ORDER BY

Sort results. ASC/DESC.

SELECT *

FROM warranty_claims

ORDER BY cost DESC;

LIMIT

Restricts num rows returned

GROUP BY

Groups rows for aggregation. (Without: aggregation results in one result, With: aggregation returns one result per group).

SELECT part_id, COUNT(*)

FROM warranty_claims

GROUP BY part_id;

HAVING

Filters grouped results AFTER aggregation

INNER JOIN

Returns matching rows from both tables

LEFT JOIN

Returns all rows from left table plus matches

RIGHT JOIN

Returns all rows from right table

FULL OUTER JOIN

Returns all matching and nonmatching rows

PRIMARY KEY

Unique identifier for a row.

FOREIGN KEY

Column linking one table to another (same id in multiple tables).

COALESCE()

Replaces NULL values

SELECT COALESCE(cost, 0)

FROM claims;

DISTINCT

Removes duplicates

SELECT DISTINCT part_id

FROM claims;

ALIAS

Temporary name for readability

SELECT SUM(cost) AS total_cost

FROM claims;

SUBQUERY

A query inside of another query

SELECT *

FROM claims

WHERE cost > (

    SELECT AVG(cost)

   FROM claims

);

COMMON TABLE EXPRESSION (CTE)

Reusable temporary query

WINDOW

Performs calculations across related rows without collapsing results

ROW_NUMBER()

Gives unique row numbers

SELECT

Employee_name,

   ROW_NUMBER() OVER (ORDER BY salary DESC)

FROM employees;

RANK()

Ties share the same rank (100 100 90) -> (1 1 3)

DENSE_RANK()

 No gaps after ties (100 100 90) -> (1 1 2)

PARTITION BY

Splitting window calculations into groups

OVER()

Defines window function behavior

CASE STATEMENT

SQL equivalent of if/else

UNION

Combines query results. Removes duplicates.

SELECT name FROM employees

UNION

SELECT name FROM managers;

UNION ALL

keeps duplicates/is faster

INDEX

Improves query speed, faster lookups instead of scanning whole table

NORMALIZATION

Organizing tables to reduce redundancy

DENORMALIZATION

Adding redundancy for faster reads

TRANSACTIONS

Group of SQL operations treated as one unit

COMMIT

Saves transaction permanently

ROLLBACK

Undoes transaction

VIEW

Virtual table based on a query

STORED PROCEDURE

Saved SQL program

ETL

Extract: pull data

Transform: clean/process data

Load: store data somewhere else

List

Ordered, mutable collection. Allows duplicates and is indexed.

parts = ["engine", "brake", "transmission"]

Tuple

Ordered, immutable collection. Can’t be modified and is faster than lists. Can be used as dictionary keys.

coords = (10, 20)

Set

Unordered collection of unique values. No duplicates, fast lookups, useful for removing duplicates.

parts = {"engine", "brake"}

Dictionary

Key-value pairs. Provides fast lookup by key and is mutable.

truck = { "model": "579", "year": 2025}

Mutable

Can change after creation (e.g., List, Dictionary, Set).

Immutable

Cannot change after creation (e.g., Tuple, String, Int).

if/elif/else

Conditional logic used to execute code based on the truthiness of conditions.

for

Iterates over collections, allowing you to process elements within a data structure.

while

Repeats a block of code while a specified condition is true.

def

Defines a function.

return

Sends value back from function.

Parameter

Variable in function definition.

def greet(name):

Argument

Value passed into function.

greet("Alex")

Lambda

Small anonymous function, common with sorting, map(), filter().

square = lambda x: x * x

try/except

Handles runtime errors.

try:   x = 10 / 0 except ZeroDivisionError:   print("Cannot divide by zero")

finally

Runs no matter what, even if an error occurs.

len()

Length of collection

type()

Returns data type

range()

Generates sequence of numbers

enumerate()

Returns index + value

zip()

Combines iterables

map()

Applies functions to all items

filter()

Filters by condition

sorted()

Sorted Collection

append()

Add one item

extend()

Adds multiple items

pop()

Removes item

remove()

Removes specific value

sort()

Sorts in place

keys()

Returns keys

values()

Returns values

items()

Returns key-value pairs

get()

Retrieves value

“r””w””a”

read, write, append

With Statement

Automatically closes file/resources

Class

Blueprint for objects

Object

Specific version of class

Constructor

Initializes object

Self

Reference to current object

Inheritance

Class inherits from another class

pandas

Library for data analysis

numpy

Numerical Operations

matplotlib

Data visualization

requests

HTTP/API requests

flask

backend APIs

Dataframe

Table-like data structure

Series

Single column of data

read_csv()

Loads csv file

head()

Shows first rows

describe()

Summary statistics

isnull()

Checks missing values

dropna()

Removes missing values

fillna()

Adds missing values

groupby()

Groups data

merge()

Joins dataframes

apply()

Applies function to rows/columns

REST API

Standard architecture for communication between systems

Endpoint

Specific API URL

GET

Retrieve data

POST

Send/create data

JSON

Common API data format

async/wait

Handles tasks concurrently

Threading

Multiple tasks in same process

Multiprocessing

Multiple CPU processes