Python Programming and SQL Relational Database Foundations
Python Fundamentals: Data Structures, Variables, and Syntax
Lists in Python:
Defined using square brackets (
[]).Example: a list named
fruitscontaining the elementsapples,banana, andcherry.Properties: Lists are ordered and mutable (items can be modified, added, or removed).
Dictionaries in Python:
Store collections of key-value pairs enclosed within curly braces (
{}).Accessing dictionary elements relies on referencing the specific keys or indices depending on the data structure setup.
Zero-Based Indexing:
Sequences such as lists access elements based on positional indices starting strictly at index
.Example: In an indexed structure such as
coordinates, accessing positionviacoordinates[0]retrieves, whereas indexviacoordinates[1]retrieves.
Variable Naming Rules:
In Python snake_case conventions, variable names must not be capitalized.
Spaces are strictly disallowed within variable identifiers.
Variable names must follow syntactical rules to be classified as valid identifiers (e.g., starting with letters or underscores rather than numbers or reserved symbols).
Comments in Code:
Comments begin with the hash/pound symbol (
#).Any text following
#outside of a string literal is completely ignored by the Python interpreter and does not impact code execution.
Output and String Concatenation with
print():Concatenation using the plus operator (
+):Concatenates string data types exclusively.
Any non-string data type must be explicitly converted using the
str()function before concatenation.Example: Concatenating
agerequires"Grant's age is " + str(age), which produces the outputGrant's age is 20whenageequals.Concatenation using commas (
,):The
print()function accepts mixed data types without requiring manual type conversion.Commas automatically insert a whitespace separator between items.
Example:
print("Grant is", 20)automatically outputsGrant is 20.
Python User Input, Arithmetic Operators, and Program Flow
User Input with
input():Displays a specified prompt on the screen and pauses program execution to wait for user input.
Always stores the captured value as a string data type (
str).Mathematical operations or numeric evaluations on user input require explicit type casting to an integer using
int()or to a floating-point number usingfloat().
Mathematical Operators:
Mathematical operations can only be executed on numeric types (
intandfloat), never directly on strings.Standard arithmetic operators include:
Addition (
+)Subtraction (
-)Multiplication (
*)Division (
/)Modulo Operator (
%):Calculates the integer remainder resulting from division.
Metaphorically identical to long division remainder calculations learned in elementary arithmetic.
Example:
(sincefits intoa total oftimes with a remainder of).
Compound Assignment Operators:
Variables are updated and reassigned in place using compound operators:
+=,-=,*=, and/=.The variable evaluates the calculation and updates its stored value prior to subsequent execution or output.
Comparison and Boolean Operators:
Evaluate conditions and return Boolean outcomes:
TrueorFalse.Equality comparison uses double equals (
==), whereas a single equals sign (=) is reserved for variable assignment.Inequality is tested using exclamation-equals (
!=), representing "not equal to."Other relational operators include greater than (
>), less than (<), greater than or equal to (>=), and less than or equal to (<=).Numerical evaluation example:
Testing whether a temperature is strictly greater than
().If the input is
, the evaluation returnsTrue, triggering associated outputs such as"Wow. It's hot.".If the input is exactly
, the conditionevaluates toFalse.
Conditional Branching (
if/else):Provides two divergent execution paths.
The
ifcode block executes if the designated Boolean condition evaluates toTrue.The
elsecode block executes if the condition evaluates toFalse.
whileLoops and Counter Updates:Repeatedly executes a block of code as long as a specified condition remains
True.Python checks the evaluation condition before each iteration.
Counter variables must be initialized prior to the loop starting.
Counter loops must update the tracking variable within the loop body (e.g.,
or) so that the loop condition eventually evaluates toFalse, preventing an infinite loop.Example Trace:
Loop header:
.Body:
.When
countequals, the conditionisTrue;countupdates toand prints.On the subsequent check,
evaluates toFalse, immediately terminating the loop.If the relational operator were less than or equal to (
), the loop would execute an additional iteration up to.
Relational Database Concepts and Basic SQL Queries
Relational Database Architecture:
Tables represent distinct entities (e.g.,
products,suppliers,orders).Fields (columns) are the attributes displayed beneath table headers (e.g.,
product_id,supplier_id,category_id,unit_cost,units_in_stock).Column Data Types: Restrict what kind of values a field can store, primarily divided into text, numbers, or dates.
Constraints: Enforce relational rules and data integrity, including uniqueness requirements, mandatory/required values (non-null), and valid foreign key relationships across tables.
SQL Syntax Conventions:
SQL language keywords are written in uppercase (e.g.,
SELECT,FROM,WHERE,ORDER BY).Ordinary identifiers (table names, column names) are written in lowercase.
Each query clause must start on a new line.
Text literal values must be enclosed within single quotes (e.g.,
'Nancy').
SELECT DISTINCTStatement:Used to eliminate duplicate records from the query result set.
Example: Running
SELECT customer_id FROM orders;returns duplicate entries if an individual customer placed multiple orders (such as a customer with IDordering multiple parts of a Halloween costume on Amazon at different times, resulting in customerappearingseparate times).Prepending
DISTINCT(SELECT DISTINCT customer_id FROM orders;) compresses results into unique customer instances.
Database Context:
In an Oracle environment,
Northwindsserves as the primary relational database, within which tables such assuppliersreside.
ORDER BYClause and Sorting Multi-Columns:Sorts rows using comma-separated column names.
Ascending order (
ASC) is the default sorting direction in SQL queries.When sorting by a single column in ascending order, specifying
ASCis optional.When sorting by multiple columns simultaneously, the desired direction (
ASCorDESC) must be explicitly declared for clarity and precision.Precedence in Sorting:
Columns sort sequentially based on the order declared in the
ORDER BYclause.Example:
ORDER BY supplier_id ASC, unit_cost DESCsorts records primarily bysupplier_idfrom lowest to highest (to); for records sharing the samesupplier_id, it sortsunit_costfrom highest to lowest.If inverted to
ORDER BY unit_cost DESC, supplier_id ASC, the highest unit cost appears first (e.g., beginning at, then, and downward), withsupplier_idused only as a tiebreaker.
SQL Filtering with WHERE, Logical Operators, and NULL Values
Row Filtering with the
WHEREClause:Acts as a conditional filter or gatekeeper, determining which individual records are permitted into the output set.
Evaluates conditions before grouping or aggregation takes place.
Logical Operators (
ANDvs.OR):AND: Requires every specified condition to evaluate toTruefor a record to be selected.OR: Requires only one of the specified conditions to evaluate toTrue.Boundary Exclusions:
A query filtering
WHERE unit_cost > 5 AND unit_cost < 10strictly excludes the boundary valuesandbecause strict comparison operators (>and<) are used rather than inclusive operators (>=and<=).
Date Literals:
The
DATEkeyword designates a date literal type in SQL.Must be formatted specifically as
DATE 'YYYY-MM-DD'(four-digit year, hyphen, two-digit month, hyphen, two-digit day).
The
INOperator:Functions as a condensed shorthand for multiple chained
ORconditions.Example:
WHERE first_name IN ('Nancy', 'Andrew')is equivalent to writingWHERE first_name = 'Nancy' OR first_name = 'Andrew'.
NULLValues in Oracle SQL:NULLis never equal to zero ().Represents missing, unknown, absent, or inapplicable data.
Standard equality operators cannot detect nullity (cannot use
= NULL).Dedicated operators
IS NULLandIS NOT NULLmust be utilized to test for missing values.Any mathematical operation executed against a
NULLvalue results inNULL.
Table Relationships and SQL JOIN Operations
Categories of Relational Table Relationships:
One-to-One (1:1):
One row in the primary table corresponds to exactly one row in the related table.
Real-world example: One individual citizen can possess only one official passport.
One-to-Many (1:N):
One row in the primary table corresponds to multiple rows in a related table.
Real-world example: A single customer entity can generate multiple distinct purchase orders.
Many-to-Many (M:N):
Multiple rows in one table associate with multiple rows in another table (frequently bridged via associative junction tables).
Inner Joins (
INNER JOINorJOIN):Combines rows from two tables based on a shared relational condition.
Returns exclusively those rows that have corresponding matches in both tables.
Example: In joining customers to orders, a customer such as
ALFKIappears twice in the join output if that customer placed two distinct orders.
Standard
JOINSyntax Order:Clause 1:
SELECTfollowed by the specified output columns.Clause 2:
FROMnaming the initial base table.Clause 3:
JOIN(orINNER JOIN) naming the secondary related table to attach.Clause 4:
ONdefining the exact matching relationship between primary key and foreign key columns.
SQL Calculations, Aliases, and Aggregate Functions
Column Aliases:
Created using the
ASkeyword following an expression or column name in theSELECTclause (e.g.,SELECT column_name AS alias_name).Assigns a custom, readable header to the result column.
Aliases can be referenced downstream throughout the query where SQL execution order permits.
Computed / Arithmetic Columns:
Mathematical operations can be directly performed between columns within the
SELECTlist.Example: Multiplying unit cost by units in stock to calculate inventory valuation:
Syntax:
SELECT unit_cost * units_in_stock AS inventory_cost.Yields a single calculated column containing the inventory values per product row.
Aggregate Functions Overview:
Aggregate functions perform a calculation across multiple rows to return a single summarized result.
Standard functions include
COUNT(),SUM(),AVG(),MIN(), andMAX().Aggregate functions automatically ignore
NULLvalues during calculation.Keyword Triggers for Function Selection:
Inquiries asking "how many" or seeking general volume indicate
COUNT(*).Inquiries specifying "unique" occurrences require the
DISTINCTmodifier (e.g.,COUNT(DISTINCT column_name)).
Aggregations Without
GROUP BY:Executing an aggregate function without a
GROUP BYclause evaluates the entire filtered set as a single group.Example:
SELECT AVG(unit_price) FROM products;produces a single row containing the average price across all products.Non-aggregate columns (such as
category_id) cannot be combined in theSELECTlist with an aggregate function unless an explicit grouping clause is defined.
Grouping and Group Filtering with GROUP BY and HAVING
The
GROUP BYClause:Collapses rows sharing identical values in specified columns into summary groups.
Example: Grouping by
category_idaggregates all individual products belonging to that category together into a single summary row.
The Non-Aggregate Column Rule:
Every column listed in the
SELECTclause that is not enclosed within an aggregate function must appear in theGROUP BYclause.Columns enclosed within aggregate functions (e.g.,
SUM(),AVG(),COUNT(),MIN(),MAX()) do not and should not appear in theGROUP BYlist.
The
HAVINGClause:Filters aggregated summary groups, functioning analogously to a
WHEREclause for aggregate values.While
WHEREevaluates non-aggregate columns on individual rows prior to grouping,HAVINGevaluates expressions involving aggregate functions after groups are formed.Example: Filtering for categories containing substantial volume:
HAVING COUNT(*) > 100.
Execution Sequence of Clauses:
The
WHEREclause precedes theGROUP BYclause.The
GROUP BYclause precedes theHAVINGclause.