Introduction to SQL - Week 9 Quiz

Course Information

• Course Title: Introduction to Database Systems
• Course Code: CST 8215
• College: Algonquin College
• Week: 9
• Topic: Introduction to Structured Query Language (SQL)

Business Intelligence (BI) Systems

• BI systems are information systems used to support management decisions.
• They produce information for assessment, analysis, planning, and control.
• BI systems typically store their associated data in a data warehouse.

Data Warehouse Components

• Operational Databases
• Other Internal Data
• External Data
• ETL System (Data Extraction, Cleaning, Preparation Programs)
• Data Warehouse Metadata
• Data Warehouse Database
• Data Warehouse DBMS
• Business Intelligence Tools
• BI Users

SQL SELECT/FROM/WHERE Framework

• The fundamental framework for an SQL query is the SQL SELECT statement.
• The SELECT statement is used to retrieve records from a database.
• SQL statements end with a semi-colon (;).
• The framework consists of these parts:
  • SELECT (column names or *)
  • FROM (table name)
  • WHERE (conditions)
  • GROUP BY (optional, with aggregate functions)
  • HAVING (optional, with aggregate functions)
  • ORDER BY (optional, to sort results)

SQL Syntax

• SELECT [ALL | DISTINCT] {select list}
• [INTO new table]
• FROM {table source}
• [WHERE search condition]
• [GROUP BY group_expression]
• [HAVING search condition]
• [ORDER BY order clause]

Field List Options

• *: Includes all columns
• comma-separated list: Specifies specific columns

Specific Columns

• Example: SELECT SKU, SKU Description, Department, Buyer FROM SKU_DATA;

Selecting All Columns (SQL Asterisk)

• Example using *: SELECT * FROM SKU_DATA;

Specifying Column Order

• Example: SELECT Department, Buyer FROM SKU_DATA;

Running an SQL Query in MySQL Workbench

• The SQL editor is a tabbed window.
• The query is entered into a tabbed window.
• The execute button runs the query in the workbench.

Saving a MySQL Query

• The saved scripts are in DBP-e15-Cape-Codd-Database.
• The Save button saves the query to a file.

Reading Specified Rows

• SQL DISTINCT keyword: Eliminates duplicate rows.
• Example: SELECT DISTINCT Buyer, Department FROM SKU_DATA;

Controlling the Number of Rows

• SQL LIMIT function: Controls the number of rows displayed.
• Example: SELECT Buyer, Department FROM SKU_DATA LIMIT 5;

Conditional Statements (WHERE Clause)

• Boolean expressions, several operators
• Multiple clauses
• Brackets

Using Single Quotes for Strings

• Using single quotes is required when working with literal strings in the SQL WHERE clause.

SQL Comparison Operators

• = (Equal to)
• != or <> (Not equal to)

• (Greater than)

• < (Less than)

• = (Greater than or equal to)

• <= (Less than or equal to)
• IN (Equal to any value in a set)
• NOT IN (Not equal to any value in a set)
• BETWEEN (Within a range of values, inclusive)
• NOT BETWEEN (Not within a range of values, inclusive)
• LIKE (Matches a pattern)
• NOT LIKE (Does not match a pattern)
• IS NULL (Checks for NULL values)
• IS NOT NULL (Checks for non-NULL values)

SQL WHERE Clause Using Sets

• IN: Selects rows where a column value is present in a list.
• NOT IN: Selects rows where a column value is not present in a list.
• Examples provided

SQL WHERE Clause Using Math Symbols

• Example (using > and < operators): SELECT * FROM ORDER_ITEM WHERE ExtendedPrice > 100 AND ExtendedPrice < 200;

SQL WHERE Clause Using BETWEEN Operator

• Example: SELECT * FROM ORDER_ITEM WHERE ExtendedPrice BETWEEN 100 AND 200;

SQL WHERE Clause Using NOT BETWEEN Operator

• Example: SELECT * FROM ORDER_ITEM WHERE ExtendedPrice NOT BETWEEN 100 AND 200;

SQL WHERE Clause Using Wildcard Symbols

• The SQL LIKE operator can combine with wildcard symbols.
• _ (underscore): Represents a single character
• % (percent sign): Represents any sequence of characters
• Examples of using these for pattern matching, including examples (using LIKE with 'Pete%') and examples (using LIKE with '%Tent%') and examples (using LIKE with '%2%') and example (using LIKE with '%2%')

SQL Where Clause Using NULL Operator

• IS NULL: Selects rows where the specified column is NULL
• IS NOT NULL: Selects rows where the specified column is NOt NULL

Using Dates in the WHERE Clause

• Example: Using = with a date string '01-JAN-2020'

Using Numbers in the WHERE Clause

• Example: SELECT * FROM SKU_DATA WHERE SKU > 200000;

Using Column Names in the WHERE Clause

• Example: SELECT SKU_Description, Department FROM SKU_DATA WHERE Department = 'Climbing';

Aliases (Renaming)

• AS keyword: Renames columns or tables for the duration of the query.
• Example of using column alias: SELECT SUM(OrderTotal) AS OrderSum FROM RETAIL_ORDER;

Summary of Aliases

• The AS keyword allows developers to rename columns or tables within an SQL query.
• Column aliasing can improve query readability and data organization.
• Using aliases is helpful when dealing with large tables, multi-table queries or complex calculations.
• Example of using column aliasing: SELECT PetID AS ID, PetName AS Pet FROM Clinic;

Test your understanding of Structured Query Language (SQL) and its application in Business Intelligence systems. This quiz covers fundamental concepts like the SELECT statement, data warehouse components, and the role of SQL in data management. Prepare to demonstrate your knowledge of the SQL framework and its relevance to decision-making processes.