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:
- 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 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
- 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.
- 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.
- 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,extAVG 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.