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 fruits containing the elements apples, banana, and cherry.

    • 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 00.

    • Example: In an indexed structure such as coordinates, accessing position 00 via coordinates[0] retrieves 1010, whereas index 11 via coordinates[1] retrieves 2020.

  • 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 age requires "Grant's age is " + str(age), which produces the output Grant's age is 20 when age equals 2020.

    • 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 outputs Grant 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 using float().

  • Mathematical Operators:

    • Mathematical operations can only be executed on numeric types (int and float), 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: 27 mod 5=227 \bmod 5 = 2 (since 55 fits into 2727 a total of 55 times with a remainder of 22).

  • 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: True or False.

    • 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 8585 (temperature>85\text{temperature} > 85).

    • If the input is 9090, the evaluation returns True, triggering associated outputs such as "Wow. It's hot.".

    • If the input is exactly 8585, the condition 85>8585 > 85 evaluates to False.

  • Conditional Branching (if/else):

    • Provides two divergent execution paths.

    • The if code block executes if the designated Boolean condition evaluates to True.

    • The else code block executes if the condition evaluates to False.

  • while Loops 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., count=count+1\text{count} = \text{count} + 1 or count+=1\text{count} += 1) so that the loop condition eventually evaluates to False, preventing an infinite loop.

    • Example Trace:

    • Loop header: while count<10\text{while } \text{count} < 10.

    • Body: count=count+1\text{count} = \text{count} + 1.

    • When count equals 99, the condition 9<109 < 10 is True; count updates to 9+1=109 + 1 = 10 and prints 1010.

    • On the subsequent check, 10<1010 < 10 evaluates to False, immediately terminating the loop.

    • If the relational operator were less than or equal to (≤10\le 10), the loop would execute an additional iteration up to 1111.

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 DISTINCT Statement:

    • 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 ID 55 ordering multiple parts of a Halloween costume on Amazon at different times, resulting in customer 55 appearing 55 separate times).

    • Prepending DISTINCT (SELECT DISTINCT customer_id FROM orders;) compresses results into unique customer instances.

  • Database Context:

    • In an Oracle environment, Northwinds serves as the primary relational database, within which tables such as suppliers reside.

  • ORDER BY Clause 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 ASC is optional.

    • When sorting by multiple columns simultaneously, the desired direction (ASC or DESC) must be explicitly declared for clarity and precision.

    • Precedence in Sorting:

    • Columns sort sequentially based on the order declared in the ORDER BY clause.

    • Example: ORDER BY supplier_id ASC, unit_cost DESC sorts records primarily by supplier_id from lowest to highest (11 to 22); for records sharing the same supplier_id, it sorts unit_cost from highest to lowest.

    • If inverted to ORDER BY unit_cost DESC, supplier_id ASC, the highest unit cost appears first (e.g., beginning at 17.6917.69, then 15.315.3, and downward), with supplier_id used only as a tiebreaker.

SQL Filtering with WHERE, Logical Operators, and NULL Values

  • Row Filtering with the WHERE Clause:

    • 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 (AND vs. OR):

    • AND: Requires every specified condition to evaluate to True for a record to be selected.

    • OR: Requires only one of the specified conditions to evaluate to True.

    • Boundary Exclusions:

    • A query filtering WHERE unit_cost > 5 AND unit_cost < 10 strictly excludes the boundary values 55 and 1010 because strict comparison operators (> and <) are used rather than inclusive operators (>= and <=).

  • Date Literals:

    • The DATE keyword 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 IN Operator:

    • Functions as a condensed shorthand for multiple chained OR conditions.

    • Example: WHERE first_name IN ('Nancy', 'Andrew') is equivalent to writing WHERE first_name = 'Nancy' OR first_name = 'Andrew'.

  • NULL Values in Oracle SQL:

    • NULL is never equal to zero (00).

    • Represents missing, unknown, absent, or inapplicable data.

    • Standard equality operators cannot detect nullity (cannot use = NULL).

    • Dedicated operators IS NULL and IS NOT NULL must be utilized to test for missing values.

    • Any mathematical operation executed against a NULL value results in NULL.

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 JOIN or JOIN):

    • 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 ALFKI appears twice in the join output if that customer placed two distinct orders.

  • Standard JOIN Syntax Order:

    • Clause 1: SELECT followed by the specified output columns.

    • Clause 2: FROM naming the initial base table.

    • Clause 3: JOIN (or INNER JOIN) naming the secondary related table to attach.

    • Clause 4: ON defining the exact matching relationship between primary key and foreign key columns.

SQL Calculations, Aliases, and Aggregate Functions

  • Column Aliases:

    • Created using the AS keyword following an expression or column name in the SELECT clause (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 SELECT list.

    • 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(), and MAX().

    • Aggregate functions automatically ignore NULL values during calculation.

    • Keyword Triggers for Function Selection:

    • Inquiries asking "how many" or seeking general volume indicate COUNT(*).

    • Inquiries specifying "unique" occurrences require the DISTINCT modifier (e.g., COUNT(DISTINCT column_name)).

  • Aggregations Without GROUP BY:

    • Executing an aggregate function without a GROUP BY clause 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 the SELECT list with an aggregate function unless an explicit grouping clause is defined.

Grouping and Group Filtering with GROUP BY and HAVING

  • The GROUP BY Clause:

    • Collapses rows sharing identical values in specified columns into summary groups.

    • Example: Grouping by category_id aggregates all individual products belonging to that category together into a single summary row.

  • The Non-Aggregate Column Rule:

    • Every column listed in the SELECT clause that is not enclosed within an aggregate function must appear in the GROUP BY clause.

    • Columns enclosed within aggregate functions (e.g., SUM(), AVG(), COUNT(), MIN(), MAX()) do not and should not appear in the GROUP BY list.

  • The HAVING Clause:

    • Filters aggregated summary groups, functioning analogously to a WHERE clause for aggregate values.

    • While WHERE evaluates non-aggregate columns on individual rows prior to grouping, HAVING evaluates expressions involving aggregate functions after groups are formed.

    • Example: Filtering for categories containing substantial volume: HAVING COUNT(*) > 100.

  • Execution Sequence of Clauses:

    • The WHERE clause precedes the GROUP BY clause.

    • The GROUP BY clause precedes the HAVING clause.