Introduction to Structured Query Language (SQL) - Chapter 2 Part 1

Chapter context and BI systems

  • Business Intelligence (BI) systems are information systems used to support management decisions by producing information for assessment, analysis, planning, and control.
  • SQL (Structured Query Language) is a data sublanguage for creating and processing database data and metadata; used to communicate with and manipulate databases.
  • SQL was developed in the 1970s by IBM researchers and is highly accessible across platforms; generally user-friendly.
  • The most common SQL commands are:
    • SELECT (used to select data from a database)
    • FROM (specifies the table(s) to select or delete data from)
    • WHERE (filters rows based on a condition)
  • Semicolon (;) is the standard statement terminator in many DBMSs; some require it at the end of each statement.
  • ANSI standards and versions mentioned: SQL-92, with newer versions like SQL:2008, SQL:2011, SQL:2016 incorporating features like XML, JSON, and some object-oriented concepts.
  • This book uses Microsoft SQL Server 2019 syntax.

SQL basics and history

  • SQL is not a full programming language; it is a data sublanguage for creating and processing database data and metadata.
  • SQL is ubiquitous in enterprise-class DBMS products and a critical skill for database work.
  • Core SQL statements are often categorized as five main sublanguages: DDL, DML, DQL, TCL, DCL.

SQL categories (sublanguages)

  • DDL (Data Definition Language): used for creating tables, relationships, and other structures; defines database schema and objects.
  • DML (Data Manipulation Language): used for querying, inserting, modifying, and deleting data; includes:
    • Queries (SELECT)
    • Inserting (INSERT)
    • Modifying (UPDATE)
    • Deleting (DELETE)
  • DQL (Data Query Language): used for querying data; the primary purpose is to retrieve data via the SELECT statement.
  • TCL (Transaction Control Language): used to group tasks into transactions and define behavior; begins with a task and ends when all tasks succeed; if any task fails, the transaction may be rolled back.
  • DCL (Data Control Language): used to grant or revoke permissions (e.g., GRANT, REVOKE).

The SELECT/FROM/WHERE framework

  • The fundamental framework for an SQL query is:
    • SELECT {ColumnName(s)}
    • FROM {TableName(s)}
    • WHERE {Condition(s)}
  • All SQL statements end with a semicolon (;).
  • Example: selecting specific columns from a single table:
    • SELECT SKU, SKU_Description, Department, Buyer FROM SKU_DATA;
  • Selecting all columns:
    • SELECT * FROM SKU_DATA;
  • Specifying column order examples:
    • SELECT Department, Buyer FROM SKU_DATA;
    • SELECT Buyer, Department FROM SKU_DATA;
  • In Microsoft Access 2019 there are UI-specific options; the examples show how SQL server compatible syntax can be selected (ANSI-92) or default settings.

Data sources and the Cape Codd case study

  • Cape Codd Outdoor Sports is a fictitious company used in examples:
    • 15 retail stores in the US and Canada, online store, and mail-order department.
    • All retail sales recorded in an Oracle Database 19c.
  • A data warehouse is used to store BI data; extraction from the operational database into an offline database is used by marketing to analyze retail sales and catalog content.
  • Data extraction example shows Linked tables: RETAILORDER, ORDERITEM, SKUDATA, BUYER vs non-linked CATALOGSKU2020 and CATALOGSKU_2021.
  • Cape Codd database availability online includes versions for:
    • Microsoft Access 2019
    • Microsoft SQL Server 2019
    • Oracle Database
    • MySQL 8.0
  • Online chapters 10A, 10B, 10C provide setup details for SQL Server, Oracle, and MySQL respectively.

Semicolon, ANSI standards, and platform nuances

  • Semicolon is the standard statement separator in many DBMSs.
  • ANSI SQL-92 introduced a standardized baseline; newer versions extend features (XML, JSON, object-oriented features in some dialects).
  • Microsoft SQL Server 2019 syntax is used in the book; there are dialect differences across:
    • Oracle SQL
    • MySQL
    • SQL Server
  • The server-specific syntax differences affect features like string concatenation and date literals.

The S Q L as a data sublanguage and its five categories (expanded)

  • DDL: creates and modifies database schema (tables, relationships, structures).
  • DML: inserts, updates, deletes, and queries data; includes SELECT, INSERT, UPDATE, DELETE.
  • DQL: data query language; essentially the SELECT statement and related querying operations.
  • TCL: transaction boundaries; behavior of transactions (BEGIN, COMMIT, ROLLBACK).
  • DCL: permissions (GRANT, REVOKE).

Basic SELECT syntax and examples

  • Fundamental query shape:
    • SELECT {ColumnName(s)} FROM {TableName(s)} WHERE {Condition(s)};
  • Distinct rows (remove duplicates):
    • SELECT DISTINCT Buyer, Department FROM SKU_DATA;
  • Limiting number of rows:
    • Fixed number: SELECT TOP 5 Buyer, Department FROM SKU_DATA;
    • Percentage: SELECT TOP 75 PERCENT Buyer, Department FROM SKU_DATA;
  • Filtering with WHERE and literals:
    • SELECT * FROM SKU_DATA WHERE Department = 'Water Sports';
    • Note: string literals require quotes; be careful with plain quotes vs typographic quotes.
  • Filtering on dates and numbers:
    • Date example: SELECT * FROM CATALOG_SKU_2020 WHERE DateOnWebSite = '01-JAN-2020';
    • Numeric example: SELECT * FROM SKU_DATA WHERE SKU > 200000;
  • Column presence in WHERE vs SELECT:
    • SQL does not require a column in the SELECT clause to appear in the WHERE clause, but remember the SELECT list is the projection, while WHERE filters rows.
  • Sorting results:
    • SELECT * FROM ORDER_ITEM ORDER BY OrderNumber;
    • Multi-column sort: ORDER BY Price DESC, OrderNumber ASC;

LIKE, IN, BETWEEN, NULL handling in WHERE

  • LIKE and NOT LIKE with wildcards:
    • Wildcards: _ (single character), % (any sequence of characters).
    • Examples:
    • SELECT * FROM SKU_DATA WHERE Buyer LIKE 'Pete%';
    • SELECT * FROM SKU_DATA WHERE SKU LIKE '%2%';
  • NULL checks:
    • NULL: SELECT * FROM CATALOG_SKU_2020 WHERE CatalogPage IS NULL;
    • NOT NULL: SELECT * FROM CATALOG_SKU_2020 WHERE CatalogPage IS NOT NULL;
  • IN and NOT IN for sets of values:
    • SELECT * FROM SKU_DATA WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin');
    • SELECT * FROM SKU_DATA WHERE Buyer NOT IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin');
  • BETWEEN and NOT BETWEEN for ranges:
    • SELECT * FROM ORDER_ITEM WHERE ExtendedPrice BETWEEN 100 AND 200 ORDER BY ExtendedPrice;
    • SELECT * FROM ORDER_ITEM WHERE ExtendedPrice NOT BETWEEN 100 AND 200 ORDER BY ExtendedPrice;

Computations and built-in aggregate functions

  • Built-in aggregates (applied to a column or expression):
    • SUM, AVG, MIN, MAX, COUNT
  • Examples:
    • SELECT SUM(OrderTotal) AS OrderSum FROM RETAIL_ORDER;
    • SELECT SUM(ExtendedPrice) AS OrderItemSum, AVG(ExtendedPrice) AS OrderItemAvg, MIN(ExtendedPrice) AS OrderItemMin, MAX(ExtendedPrice) AS OrderItemMax FROM ORDER_ITEM;
    • SELECT COUNT(*) AS NumberOfRows FROM ORDER_ITEM;
    • SELECT COUNT(DISTINCT Department) AS DeptCount FROM SKU_DATA;
  • Limitations of built-in functions:
    • You cannot combine table column names with an SQL built-in function in the SELECT list in certain contexts.
    • You cannot use aggregates directly in a WHERE clause (since WHERE operates on rows while aggregates operate on groups/columns).
    • Example illustrating a limitation to avoid: SELECT Department, COUNT(*) FROM SKU_DATA; (without GROUP BY) would be invalid in many dialects.

Grouping, HAVING, and ordering grouped data

  • GROUP BY basics:
    • Used to group rows by one or more columns for aggregate calculations.
    • Example: grouping by Department:
    • SELECT Department, COUNT(SKUs) AS NumberOfCatalogItems FROM CATALOG_SKU_2020 WHERE CatalogPage IS NOT NULL GROUP BY Department;
  • HAVING clause with groups:
    • Filters after aggregation:
    • SELECT Department, COUNT(SKUs) AS NumberOfCatalogItems FROM CATALOG_SKU_2020 WHERE CatalogPage IS NOT NULL GROUP BY Department HAVING COUNT(SKUs) > 2;
  • Grouping by more than one column:
    • SELECT Department, Buyer, COUNT(SKUs) AS Dept_Buyer_SKU_Count FROM SKU_DATA GROUP BY Department, Buyer;
  • Rules about SELECT list with GROUP BY:
    • Any column in SELECT that is not used in an aggregate function must appear in the GROUP BY clause.
    • Example causing error if violated: SELECT Department, SKU, COUNT(*) FROM SKU_DATA GROUP BY Department; (SKU not in GROUP BY or in an aggregate).
  • ORDER BY with grouped results:
    • SELECT Department, COUNT(SKUs) AS Dept_SKU_Count FROM SKU_DATA GROUP BY Department ORDER BY Dept_SKU_Count;

Expressions, string functions, and cross-dialect concatenation

  • Expression in SELECT:
    • Example in notes: an expression that compares a computed value to another column (illustrative):
    • (QuantityimesPrice)<br/>≠ExtendedPrice(Quantity imes Price) <br />\ne ExtendedPrice
  • String concatenation across dialects:
    • Oracle syntax (concatenate with ||):
    • SELECT SKU, SKU_Description, (Buyer || ' in ' || Department) AS Sponsor FROM SKU_DATA ORDER BY SKU;
    • SQL Server-like syntax (using + and RTRIM):
    • SELECT SKU, SKU_Description, RTRIM(Buyer) + ' in ' + RTRIM(Department) AS Sponsor FROM SKU_DATA ORDER BY SKU;
    • MySQL syntax (using CONCAT):
    • SELECT SKU, SKU_Description, CONCAT(Buyer, ' in ', Department) AS Sponsor FROM SKU_DATA ORDER BY SKU;

Joins, subqueries, and recursive relationships (overview)

  • Subqueries (2.11): queries nested inside other queries to retrieve data based on another query's result.
  • Joins (2.12, 2.13): retrieving data from multiple tables using JOIN and JOIN ON; handling relationships between tables.
  • Recursive relationships (2.13) and related concepts: querying hierarchical data or self-joins where needed.
  • Outer joins (2.14): retrieving rows with matches and non-matches from related tables.
  • Set operators (2.15): UNION, INTERSECT, and EXCEPT to combine results from two queries.

Practical notes on access to SQL tools and execution

  • The material includes references to how to use various SQL tools: MS SQL Server Management Studio, Oracle SQL Developer, MySQL Workbench, and the MySQL interface in different environments.
  • There are numerous figures and step-by-step screenshots in the slides showing practical tasks like creating queries, saving scripts, and running queries in different platforms.

Data extraction, data warehouse workflow (Cape Codd project)

  • BI data is often derived from operational data by extracting relevant data into a separate offline database (data warehouse) for analysis.
  • The Cape Codd case demonstrates: extracting retail sales data and catalog content, not the entire operational database, to support marketing analysis.
  • Data extraction steps illustrated:
    • Operational database to offline extraction database for BI use.
    • Sample data tables and relationships in the extracted data format (linked vs non-linked tables).
  • Chips of data around this example include:
    • Linked: RETAILORDER, ORDERITEM, SKU_DATA, BUYER
    • Non-linked: CATALOGSKU2020, CATALOGSKU2021
  • The Cape Codd database is available online in multiple formats for practice:
    • Microsoft Access 2019
    • Microsoft SQL Server 2019
    • Oracle Database
    • MySQL 8.0

Quick reference of key SQL terms and concepts covered

  • SELECT/FROM/WHERE framework: core for querying a single table or multiple tables with conditions.
  • Distinct to remove duplicates: SELECT DISTINCT ....
  • TOP and TOP PERCENT to limit output:
    • TOP n or TOP n PERCENT depending on dialect.
  • ORDER BY and sorting semantics (ASC/DESC).
  • GROUP BY and HAVING for aggregations and filtering groups.
  • Aggregate functions: extSUM,extCOUNT,extMIN,extMAX,extAVGext{SUM}, ext{COUNT}, ext{MIN}, ext{MAX}, ext{AVG} with and without GROUP BY.
  • Data types and literal handling (strings with quotes, date literals, numeric literals).
  • Pattern matching with LIKE and NOT LIKE using _ and % wildcards.
  • NULL handling with IS NULL and IS NOT NULL.
  • Joins, subqueries, and set operators for complex data retrieval.
  • Platform-specific nuances in string functions and concatenation across Oracle, SQL Server, and MySQL.
  • Semantics of WHERE vs GROUP BY vs HAVING and avoiding ambiguity by applying WHERE before HAVING.
  • Common pitfalls and limitations when using built-in aggregate functions (e.g., mixing column names with aggregates, using aggregates in WHERE).

Summary of the Cape Codd data modeling and BI workflow (in one glance)

  • BI requires targeted data extraction from transactional systems to support decision-making.
  • A data warehouse houses extracted data for BI analysis, enabling assessments across in-store sales and catalog content.
  • Examples use a variety of tables (SKUDATA, RETAILORDER, ORDERITEM, CATALOGSKU_2020/2021, BUYER) to illustrate typical queries and aggregations.
  • Practice across dialects (Oracle, SQL Server, MySQL, Access) helps understand portable SQL concepts and dialect-specific syntax differences.