SQL Fundamentals, Query Execution Order, and Relational Schema Architecture

Fundamental SQL Clauses and Order of Execution

• Overview of Six Core SQL Clauses:

  • $\text{SELECT}$: Specifies the target columns or calculated fields to retrieve and display in the result set.
  • $\text{FROM}$: Specifies the primary table or table sources from which data will be retrieved.
  • $\text{WHERE}$: Filters individual records prior to aggregation based on specified search conditions.
  • $\text{GROUP BY}$: Groups individual rows sharing common values across specified columns into summary rows or subgroups; enables evaluation of aggregate metrics across a group as a whole (such as calculating a minimum value within a subgroup, a maximum value of a group, or an average across multiple elements).
  • $\text{HAVING}$: Filters groups created by the $\text{GROUP BY}$ clause based on group-level conditions; any query containing a $\text{HAVING}$ clause must contain a preceding $\text{GROUP BY}$ clause, though a $\text{GROUP BY}$ clause does not strictly require a $\text{HAVING}$ condition.
  • $\text{ORDER BY}$: Controls the final visual display order of output records (e.g., sorting in alphabetical or reverse-alphabetical order).

• Written Clause Sequence (Fixed Syntax Order):

  • Step 1: $\text{SELECT}$ (Always written first in query editors and SQL platforms).
  • Step 2: $\text{FROM}$ (Always written immediately after $\text{SELECT}$).
  • Step 3: $\text{WHERE}$ (Written third when row filtering conditions exist).
  • Step 4: $\text{GROUP BY}$ (Written fourth when aggregation grouping is required).
  • Step 5: $\text{HAVING}$ (Written fifth when conditional filtering on aggregated groups is needed).
  • Step 6: $\text{ORDER BY}$ (Written sixth to specify output presentation order).

• Clause Usage Frequency:

  • $\text{SELECT}$, $\text{FROM}$, and $\text{WHERE}$ represent the three most frequently utilized clauses in database operations.
  • $\text{GROUP BY}$, $\text{HAVING}$, and $\text{ORDER BY}$ are optional clauses invoked based on specific user requirements or task specifications.

• Database Engine Execution Order (Internal Processing Sequence):

  • Phase 1 — $\text{FROM}$: The database engine begins execution here to establish directional information and determine which physical table shell to access.
  • Phase 2 — $\text{WHERE}$: The engine applies row-level filters across raw database tables (which may contain hundreds of thousands or millions of rows) to trim down the dataset.
  • Phase 3 — $\text{GROUP BY}$: The filtered records are partitioned into distinct smaller subgroups.
  • Phase 4 — $\text{HAVING}$: Group-level conditions are evaluated against the partitioned subgroups.
  • Phase 5 — $\text{SELECT}$: The engine projects and extracts only the specified columns or expressions to display on screen.
  • Phase 6 — $\text{ORDER BY}$: The final extracted output is sorted according to the requested display format.

• Conceptual Query Design Workflow:

  • First: Determine which target table to access ($\text{FROM}$).
  • Second: Specify filtering criteria to apply to raw rows ($\text{WHERE}$).
  • Third: Determine if rows need partitioning into smaller subcategories ($\text{GROUP BY}$).
  • Fourth: Apply required conditional constraints to the formed groups ($\text{HAVING}$).
  • Fifth: Select specific columns to project on screen ($\text{SELECT}$).
  • Sixth: Define presentation sorting criteria ($\text{ORDER BY}$).

Logical Operators in SQL Filtering: AND and OR

• Fundamental Mechanics of Logical Operators:

  • Logical operators serve as operational evaluation tools within conditional filtering statements, functioning analogously to arithmetic operators ($+$, $-$, $\times$, $\div$).
  • They evaluate Boolean expressions to return either a definitive $\text{TRUE}$ or $\text{FALSE}$ evaluation for dataset record filtering.

• The $\text{AND}$ Operator:

  • Rule of Evaluation: Requires every single condition combined under the operator to evaluate to $\text{TRUE}$ for the final overall statement result to evaluate to $\text{TRUE}$.
  • Strictness: If any individual condition evaluates to $\text{FALSE}$ (whether one condition, multiple conditions, or all conditions), the overall evaluation immediately becomes $\text{FALSE}$.
  • Scalability: Applies strictly regardless of the number of conditions combined ($N = 2, 3, 4, 5, \dots$); all $N$ components must be strictly $\text{TRUE}$.
  • Truth Table Analysis for Two Conditions ($C_1 \text{ AND } C_2$):
  • Condition 1 = $\text{TRUE}$, Condition 2 = $\text{TRUE}$ $\rightarrow$ Final Result = $\text{TRUE}$
  • Condition 1 = $\text{TRUE}$, Condition 2 = $\text{FALSE}$ $\rightarrow$ Final Result = $\text{FALSE}$
  • Condition 1 = $\text{FALSE}$, Condition 2 = $\text{TRUE}$ $\rightarrow$ Final Result = $\text{FALSE}$
  • Condition 1 = $\text{FALSE}$, Condition 2 = $\text{FALSE}$ $\rightarrow$ Final Result = $\text{FALSE}$
  • Illustrative Scenario: Playing a video game contingent on two conditions: $C_1$ = homework finished, $C_2$ = room clean.
  • Homework finished ($\text{Yes}$) AND Room clean ($\text{Yes}$) $\rightarrow$ Allowed to play ($\text{Yes}$).
  • Homework finished ($\text{Yes}$) AND Room clean ($\text{No}$) $\rightarrow$ Allowed to play ($\text{No}$).
  • Homework finished ($\text{No}$) AND Room clean ($\text{Yes}$) $\rightarrow$ Allowed to play ($\text{No}$).
  • Homework finished ($\text{No}$) AND Room clean ($\text{No}$) $\rightarrow$ Allowed to play ($\text{No}$).

• The $\text{OR}$ Operator:

  • Rule of Evaluation: Requires at least one condition among the evaluated criteria to evaluate to $\text{TRUE}$ for the overall statement result to evaluate to $\text{TRUE}$.
  • Flexibility: Yields a $\text{FALSE}$ output only when every single component condition evaluates to $\text{FALSE}$.
  • Truth Table Analysis for Two Conditions ($C_1 \text{ OR } C_2$):
  • Condition 1 = $\text{TRUE}$, Condition 2 = $\text{FALSE}$ $\rightarrow$ Final Result = $\text{TRUE}$
  • Condition 1 = $\text{FALSE}$, Condition 2 = $\text{TRUE}$ $\rightarrow$ Final Result = $\text{TRUE}$
  • Condition 1 = $\text{TRUE}$, Condition 2 = $\text{TRUE}$ $\rightarrow$ Final Result = $\text{TRUE}$
  • Condition 1 = $\text{FALSE}$, Condition 2 = $\text{FALSE}$ $\rightarrow$ Final Result = $\text{FALSE}$
  • Mathematical Outcome Distribution (Two-Variable System):
  • $\text{AND}$ produces a $\text{TRUE}$ evaluation in exactly $\frac{1}{4}$ ($25\%$) of condition combinations.
  • $\text{OR}$ produces a $\text{TRUE}$ evaluation in $\frac{3}{4}$ ($75\%$) of condition combinations.
  • Illustrative Scenario: Receiving a free pizza contingent on two conditions: $C_1$ = possess a coupon, $C_2$ = birthday today.
  • Possess coupon ($\text{Yes}$) OR Birthday today ($\text{No}$) $\rightarrow$ Receive free pizza ($\text{Yes}$).
  • Possess coupon ($\text{No}$) OR Birthday today ($\text{Yes}$) $\rightarrow$ Receive free pizza ($\text{Yes}$).
  • Possess coupon ($\text{Yes}$) OR Birthday today ($\text{Yes}$) $\rightarrow$ Receive free pizza ($\text{Yes}$).
  • Possess coupon ($\text{No}$) OR Birthday today ($\text{No}$) $\rightarrow$ Receive free pizza ($\text{No}$).

Database Schema Architecture: Shoe Store Relational Model

• Relational Database Structure Overview:

  • Graphical Schema Layout: Table names are situated at the top of entity boxes; white background fields denote table attributes/columns.
  • Schema Connectivity: Entities are linked across relational networks via primary key (PK) and foreign key (FK) constraints.

• Product and E-Commerce Transaction Subsystem (5 Tables):

  • Customer Table (Customer):
  • customer ID (Primary Key)
  • customer's first name
  • last name
  • street address
  • city
  • state
  • postcode
  • country
  • phone
  • Sale Table (Sale):
  • sale ID (Primary Key)
  • sale date
  • customer ID (Foreign Key referencing Customer table)
  • tax
  • shipping
  • Sale Item Table (Sale Item):
  • sale ID (Foreign Key referencing Sale table)
  • product ID (Foreign Key referencing Product table)
  • item size
  • quantity
  • sale price
  • Product Table (Product):
  • product ID (Primary Key)
  • product name
  • manufacturer ID (Foreign Key referencing Manufacturer table)
  • composition
  • list price
  • gender
  • category
  • color
  • description
  • Manufacturer Table (Manufacturer):
  • manufacturer ID (Primary Key)
  • name of the manufacturer
  • address
  • city
  • state
  • postcode
  • phone
  • fax
  • contact
  • URL

• Internal Human Resources Subsystem (3 Tables):

  • Employee Table (Employee):
  • employee ID (Primary Key)
  • first name
  • last name
  • address
  • city
  • state
  • zip
  • phone
  • manager ID
  • SSN (Social Security Number)
  • Salary Table (Salary): Linked internally to the employee model via Social Security Number/employee identification.
  • Wages Table (Wages): Linked internally to the employee model via Social Security Number/employee identification.

Core SQL Syntax, Queries, and Interface Rules

• Minimum Working SQL Statement:

  • A minimum functional SQL query consists of exactly two clauses: $\text{SELECT}$ and $\text{FROM}$.
  • Structure: SELECT <column_name> FROM <table_name>;

• Keyword Usage — $\text{DISTINCT}$:

  • Used immediately following $\text{SELECT}$ when unique values are required from a column containing repetitive entries.
  • Application Scenario: Extracting unique manufacturer identifiers when multiple distinct products originate from the same manufacturer.

• Wildcard Column Selection (SELECT *):

  • The asterisk symbol (*) acts as a wildcard operator denoting "all columns".
  • Selecting all records and attributes from the customer table: SELECT * FROM Customer;
  • Selecting all records and attributes from the product table: SELECT * FROM Product;

• Query Syntax & Formatting Conventions:

  • Case Sensitivity: Column names and table names are not case-sensitive in standard SQL query execution.
  • Query Termination: Every complete SQL statement ends with a semicolon ;.
  • Multi-Query Separation: In interactive SQL windows, the semicolon serves as an execution separator, allowing multiple distinct SQL statements to sit in the same text field and run independently; for single standalone queries, the semicolon can technically be omitted, though standard practice retains it.
  • Identifier Naming Rules: Spaces are omitted from table and column names to ensure ease of query execution; multiple words within column identifiers are concatenated using camelCase or PascalCase capitalization (e.g., ProductName, ManufacturerID, ListPrice).

• Data Display Structures:

  • Graphical Schema Representation: Displays entity structure vertically in design view.
  • Query Output Representation: Displays extracted query records horizontally, where table headers run horizontally across the top and individual dataset records populate as horizontal rows below.

• Verbatim Sample Data Entries from Database Execution:

  • Customer Output Record Example:
  • Customer ID: 12
  • First Name: James
  • Last Name: Dovi
  • Street Address: 4 Mass Street
  • City: 1st Mouse (C B A)
  • State: Formatted as a short state code rather than a full state name.
  • Product Output Record Example:
  • Product ID: 773
  • Product Name: A book-type product entry within the database.
  • Manufacturer ID: 116
  • Composition: Formulated with leather, textile, and a rubber sole.
  • List Price: Standard catalog price field.
  • Gender: m (Male).
  • Category: boots (additional categories identified in dataset include sneakers and heels).

• System Interface Mechanics & Grading Workflow:

  • Test Query Button: Executes query against database engine to display output preview and catch syntax errors without submitting final work.
  • Save Answer Button: Formally records query submission.
  • Practice vs. Homework System Feedback Behavior:
  • Practice Exercises: Displays feedback score along with the exact correct SQL query answer upon saving.
  • Homework Assignments: Returns feedback score only, withholding direct SQL query solution.