DAT 153: Introduction to SQL Select Statements and Database Querying

Logistics, Administration, and Workflow Setup

• Course Announcements & Extra Credit Opportunities

  • Data Science Department Open House: Scheduled for September.

  • Public Good Data Wrangle: Scheduled for October. Participation offers extra credit ranging from $1\%$ to $3\%$ added directly to the final grade at the end of the semester.

  • Communication: Course updates, script files, and announcements are distributed via the course Slack workspace.

  • Office Hours & Support: Office hours take place today from $2:00\text{ PM}$ to $4:00\text{ PM}$. The embedded tutor for the course is Paul Park.

• Automated Lesson Retrieval Workflow (get_lessons.py)

  • Purpose: Pulls scaffolded lesson files directly from a designated GitHub repository into the user's local JupyterHub learning environment.

  • Initial Setup Procedure:

  • Download get_lessons.py from the course Slack workspace.

  • Drag and drop get_lessons.py into the root directory of JupyterHub, situated alongside files such as database.txt.

  • Right-click the file explorer area in JupyterHub and select Open in Integrated Terminal.

  • Execute the command:

    bash python get_lessons.py       

  • Upon initial execution, the terminal displays a message indicating that no existing repository exists in the folder and begins cloning fresh.

  • Execution generates a directory named dat153_lessons containing SQL exercise scripts (e.g., sql_queries_01_exercises.sql).

  • Update Behavior: Running python get_lessons.py in subsequent weeks retrieves new lesson files from GitHub without overwriting existing local modifications or obliterating completed exercise progress.

• Gradescope Problem Set Submissions

  • Problem Set $01$ covers $10$ questions utilizing the countries database.

  • Requirement: Every query for problem sets must be saved and submitted as an individual .sql script file to ensure compatibility with the automated autograder.

Relational Database Architecture and Schemas

• Postgres Database Hierarchy

  • Server Level: A single PostgreSQL server hosts one or more independent databases. A standard student server comes preconfigured with approximately $8$ to $9$ databases, expanding to roughly $15$ databases over the semester as personal databases are created.

  • User Databases: Each user possesses a personal database named using the format db-<username> (e.g., db-pebenbo). Users hold write permissions on their personal database instance, whereas system-level shared databases are read-only.

  • Default System Database: Every PostgreSQL server installation contains a default system management database named postgres.

• Database Schemas

  • Definition: Logical collections of database objects including tables, views, indexes, and automated scripts.

  • Default Schema Names across Database Engines:

  • PostgreSQL default schema: public.

  • Microsoft SQL Server default schema: dbo.

  • MySQL default schema: main.

  • Practical Application: Most queries in this course execute within the default public schema unless additional schemas are added for structural organization.

• Dataset Specification: The countries Database

  • Structure: Contains a single database table named countries.

  • Grain of Data: One row per country per year. The provided dataset consists exclusively of year $2022$ World Bank data.

  • Table Columns: country, population, area, region, capital.

Select Statements, Execution Order, and Basic Syntax Rules

• Fundamental Query Structure

  • Every standard query requires the SELECT and FROM clauses at minimum.

  • Statement Termination: SQL statements should be terminated with a semicolon ;. While optional in certain SQL dialects, it explicitly signals statement completion to the database engine and represents industry best practice.

• The SELECT * Operator and Production Guidelines

  • Function: SELECT * retrieves every column present in the specified table.

  • Ad-Hoc Use Case: Useful exclusively during initial data exploration to inspect the contents and structure of an unfamiliar table.

  • Production Prohibition: SELECT * must never be used in production code (e.g., software functions, database views, automated ETL pipelines, analytical reports).

  • Danger of Production Use: SELECT * makes code brittle. If underlying schema modifications occur (such as adding or dropping columns), downstream applications expecting fixed positional attributes will fail or generate corrupted pipeline outputs.

• Written Order vs. Logical Execution Order

  • SQL code is written in a standard order, but the database execution engine parses and processes clauses in a fundamentally different order:

  • Written Order:

    $\text{SELECT} \rightarrow \text{FROM} \rightarrow \text{JOIN} \rightarrow \text{WHERE} \rightarrow \text{GROUP BY} \rightarrow \text{HAVING} \rightarrow \text{ORDER BY} \rightarrow \text{LIMIT}$

• Logical Execution Order:

    $\text{FROM} \rightarrow \text{JOIN} \rightarrow \text{WHERE} \rightarrow \text{GROUP BY} \rightarrow \text{HAVING} \rightarrow \text{SELECT} \rightarrow \text{ORDER BY} \rightarrow \text{LIMIT}$

• Operational Consequence: The database engine locates the target dataset in the FROM clause first, filters rows in WHERE, performs aggregation, and executes the SELECT clause near the very end of processing.

• Alias Scope Restriction: Because WHERE executes before SELECT, column aliases defined in the SELECT clause (e.g., SELECT population AS total_population) cannot be evaluated or referenced within the WHERE clause.

Logical Filtering and Conditional Operators

• Data Type Formatting in Syntax

  • String / Text Literals: Must always be wrapped in single quote marks ' (e.g., WHERE country = 'Liberia'). Double quotes are reserved for object identifiers containing special characters.

  • Numeric Values: Must never be wrapped in single quote marks (e.g., WHERE population > 68000000). Enclosing numbers in quotes forces unnecessary type coercion and violates SQL standard practice.

• Comparison Operators

  • Standard Equality: Executed using a single equals sign =. SQL does not recognize double equals ==; attempting to use == generates a syntax error.

  • Efficiency Consideration: Direct equality (=) is computationally more efficient than wildcard string evaluation (LIKE) because direct equality enables optimized index lookup algorithms rather than flexible pattern matching scans.

• Boolean Logic: AND vs. OR

  • AND: Restricts query output by requiring all joined evaluation criteria to be true simultaneously.

  • OR: Expands query output by returning rows where any individual evaluation criterion is true.

  • Operator Precedence & Parentheses: Combining AND and OR logical clauses within a single WHERE block without explicit parentheses () causes logical evaluation errors. Databases prioritize AND evaluations before OR, altering intended result sets. Complex boolean expressions must use explicit parentheses to isolate conditions.

• Range and List Operators

  • IN Operator: Functionally serves as concise shorthand for multiple OR equivalency checks (e.g., WHERE region IN ('North America', 'Sub-Saharan Africa')).

  • Limitations: IN relies purely on direct equivalency and cannot be combined with wildcard characters (e.g., %).

  • BETWEEN Operator: Filters numeric or text ranges (e.g., WHERE area BETWEEN 500000 AND 2000000).

  • Edge Boundaries: Range criteria evaluate inclusively for standard numbers, but text-based string range evaluations depend strictly on alphabetical collation order. PostgreSQL official documentation explicitly recommends replacing BETWEEN with explicit boundary comparison operators (>= and <=, or > and <) to prevent subtle boundary errors.

• Pattern Matching (LIKE and ILIKE)

  • Wildcard Symbol: Percent symbol % represents zero, one, or multiple arbitrary characters.

  • 'A%': Matches string values beginning with capital A.

  • '%stan': Matches string values ending with stan.

  • '%United%': Matches string values containing United at any position.

  • Case Sensitivity:

  • LIKE: Standard ANSI SQL operator; strictly case-sensitive.

  • ILIKE: PostgreSQL-specific operator; performs case-insensitive pattern matching.

Sorting, Limiting, and Aggregate Previews

• Sorting (ORDER BY)

  • Default Behavior: Sorts query results in ascending order (ASC). Adding ASC explicitly is optional.

  • Descending Sort: Requires the explicit DESC keyword appended after the target sort column (e.g., ORDER BY area DESC).

  • Multi-Column Sorting: Columns specified in ORDER BY are evaluated sequentially from left to right (e.g., ORDER BY region ASC, country ASC).

• Row Limiting (LIMIT)

  • Function: Restricts returned output to the first $n$ rows following sort evaluation.

  • Behavior on Small Datasets: If the total number of qualifying rows is less than the requested LIMIT value $n$, the query simply returns all available matching rows without throwing an error or breaking.

• Duplicate Removal (DISTINCT)

  • Function: Evaluates output columns and strips duplicate matching rows, returning unique values (e.g., SELECT DISTINCT region FROM countries).

• Row Counting (COUNT)

  • COUNT(*): Evaluates total row count across the target dataset, including rows containing NULL values.

  • COUNT(column): Evaluates non-null entries within the designated column, ignoring NULL values.

• Categorical Aggregation Preview (GROUP BY)

  • Rule: Attempting to select a non-aggregated categorical column alongside an aggregate summary function (such as COUNT(), AVG(), SUM(), MIN(), or MAX()) throws an explicit database error unless a GROUP BY clause containing the categorical column is added.

In-Class SQL Exercises and Step-by-Step Solutions

• Exercise Executing Interface

  • SQL exercise code blocks within scaffolded files are wrapped in block comments (/* comment text */).

  • To execute a specific SQL query statement without running the entire script file, highlight the targeted SQL string and press Shift + Enter (or Shift + Return on macOS).

• Exercise 1: Basic Filtering, Sorting, and Limiting

  • Prompt: Locate all countries with populations greater than $68,000,000$, sorted in ascending alphabetical order first by region then by country. Limit output to the top $10$ rows and include only country, region, population, area, and capital.

  • Query Solution:

  sql SELECT country, region, population, area, capital FROM countries WHERE population > 68000000 ORDER BY region ASC, country ASC LIMIT 10;     

  • Output Result ($10$ rows returned): China, Indonesia, Japan, Philippines, Thailand, France, Germany, Russian Federation, Turkey, Brazil.

• Exercise 2.1: Count Aggregation and Column Aliasing

  • Prompt: Count how many countries have fewer than $1,000,000$ inhabitants.

  • Query Solution:

  sql SELECT COUNT(*) AS country_population FROM countries WHERE population < 1000000; &nbsp;&nbsp;&nbsp;&nbsp;

• Exercise 2.2: Exact String Comparison

  • Prompt: Find the capital of Liberia.

  • Query Solution:

  sql SELECT country, capital FROM countries WHERE country = 'Liberia';     

  • Output Result: Monrovia.

• Exercise 2.3: Case-Sensitive Wildcard Pattern Matching

  • Prompt: Select region, country, population, and capital for all countries located in regions containing the word Asia, sorted in ascending order by region and country.

  • Query Solution:

  sql SELECT region, country, population, capital FROM countries WHERE region LIKE '%Asia%' ORDER BY region ASC, country ASC;     

  • Output Result Count: $191$ total rows matching the pattern.

• Exercise 2.4: Compound Logical Filtering with AND

  • Prompt: Select countries in South Asia with populations exceeding $100,000,000$.

  • Query Solution:

  sql SELECT country, region, population FROM countries WHERE region = 'South Asia' AND population > 100000000;     

  • Output Result Count: $20$ rows affected.

• Exercise 2.5: Filtering Numeric Ranges with BETWEEN

  • Prompt: Find all Middle East & North African countries whose land area is between $500,000$ and $2,000,000$ square kilometers, ordered by land area in descending order.

  • Query Solution:

  sql SELECT country, area, region FROM countries WHERE region = 'Middle East & North Africa' AND area BETWEEN 500000 AND 2000000 ORDER BY area DESC;     

  • Output Result Summary: Returns records for Libya, Iran, Egypt, and Yemen.

• Exercise 2.6: Nested Parenthetical Logic with OR and AND

  • Prompt: Select country, region, population, and area for countries located in either East Asia & Pacific or South Asia that also possess either a population greater than $100,000,000$ or a land area exceeding $1,000,000$ square kilometers. Sort results by land area descending.

  • Query Solution:

  sql SELECT country, region, population, area FROM countries WHERE (region = 'East Asia & Pacific' OR region = 'South Asia') AND (population > 100000000 OR area > 1000000) ORDER BY area DESC;     

  • Output Result Count: Exactly $9$ rows returned.

  • Parentheses Impact Analysis: Removing parenthetical encapsulation surrounding the OR clauses causes logical short-circuiting. Without parentheses, the database evaluates rows meeting region = 'East Asia & Pacific' independently of population limits, incorrectly returning small nations (e.g., populations of only $48,000$).

• Exercise 2.7: Distinct Value Extraction

  • Prompt: Extract a distinct list of regions containing countries with populations exceeding $200,000,000$, sorted alphabetically by region.

  • Query Solution:

  sql SELECT DISTINCT region FROM countries WHERE population > 200000000 ORDER BY region;     

  • Output Result Count: $5$ unique regions returned.

• Exercise 2.8: List Filtering with IN

  • Prompt: Retrieve country, area, and region for countries situated in either North America or Sub-Saharan Africa using the IN operator, sorted by area descending.

  • Query Solution:

  sql SELECT country, area, region FROM countries WHERE region IN ('North America', 'Sub-Saharan Africa') ORDER BY area DESC;     

  • Output Result Count: $51$ rows returned.

Advanced Topics: Schema Qualification, Aliasing, and Schema Visualization

• Table and Schema Qualification

  • Syntax Format: schema_name.table_name.column_name or table_alias.column_name.

  • Code Disambiguation: Essential when joining multiple tables containing identically named attributes (e.g., countries.id vs. indicators.id). Unqualified references to shared column names trigger an ambiguous column reference database error.

  • Example Syntax:

  sql SELECT c.country, c.population FROM public.countries AS c WHERE c.population > 10000000;     

• Database Identifier Quoting Rules

  • Standard unquoted database identifiers (table names, database names, column names) cannot contain special reserved characters or spaces.

  • Identifier Enclosure: If an object identifier contains special characters (e.g., & or %), it must be wrapped in double quote marks "" (e.g., CREATE DATABASE "countries&";). Single quote marks ' trigger string literal parsing and result in syntax errors.

• Schema Visualization Tools

  • Visualizer Access: Right-click target database instance in VS Code -> Select Visualize Schema.

  • Visual Architecture Inspection: Renders relational entity-relationship diagrams showing key link lines across tables.

  • Foreign Key Relationships: Indicated by linking connectors between primary key columns on entity tables (e.g., countries.id) and foreign key columns on transactional or analytical tables (e.g., indicators.country_id).

Questions & Discussion

• Question on LIMIT Behavior with Small Result Sets

  • Prompt / Question: If a query specifies LIMIT 5, but fewer than $5$ countries satisfy the filter criteria, will the SQL engine throw an error or break?

  • Response: The query does not break. It processes normally and simply displays all matching rows that satisfy the filter criteria (i.e., fewer than $5$ rows).

• Question on Direct Equality vs. Wildcard Performance

  • Prompt / Question: Is direct string equality (WHERE country = 'Liberia') more computationally efficient than pattern matching (WHERE country LIKE 'Liberia'), or does it not matter?

  • Response: Direct equality (=) is more computationally efficient because it evaluates exact equivalence, allowing the query engine to utilize direct index lookups. LIKE triggers pattern matching string algorithms. On small datasets, execution differences equal mere nanoseconds, but on massive tables, LIKE scans induce significant performance penalties unless properly indexed.

• Question on Invalid Identifier Naming Rules

  • Prompt / Question: Are there specific special characters prohibited when naming database objects such as columns or databases?

  • Response: Special symbols like % or & represent reserved operators and cause direct syntax failure when parsed as raw identifiers. Standard object names must avoid special characters. However, wrapping the identifier in double quote marks "" overrides character restrictions and forces the engine to accept non-standard names.