Introduction to Database Systems (CST 8215) Lecture Slides PDF

INTRODUCTION TO DATABASE SYSTEMS (CST 8215) WEEK 9 INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL) Lesson Objectives ❖ To understand the use of extracted datasets in business intelligence (BI) systems ❖ To understand the use of ad-hoc queries in business int...

INTRODUCTION TO DATABASE SYSTEMS (CST 8215) WEEK 9 INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL) Lesson Objectives ❖ To understand the use of extracted datasets in business intelligence (BI) systems ❖ To understand the use of ad-hoc queries in business intelligence (BI) systems ❖ To understand the SQL SELECT/FROM/WHERE framework as the basis for database queries Lesson Objectives Cont’d ❖ To create SQL queries to retrieve data from a single table ❖ To create SQL queries that use the SQL DISTINCT keywords ❖ To create SQL queries that use the SQL comparison operators, including BETWEEN, LIKE, IN, and IS NULL Business Intelligence (BI) Systems Business Intelligence (BI) systems are information systems used to support management decisions by producing information for assessment, analysis, planning, and control. Components of a Data Warehouse BI systems typically store their associated data in a data warehouse. The SQL SELECT/FROM/WHERE Framework ❖The fundamental framework for an SQL query is the SQL SELECT statement. ❖ Select statement is used to retrieve records from a database. SELECT {ColumnName(s)} FROM {TableName(s)} WHERE {Condition(s)} ❖ All SQL statements end with a semicolon(;). Six (6) parts of the SELECT statement ❖ SELECT (return value selection) ❖ FROM (source selection, usually tables) ❖ WHERE (Conditional Boolean operations, a.k.a. predicates) ❖ GROUP BY (Optional, used with aggregate functions) ❖ HAVING (Optional, used with aggregate functions) ❖ ORDER BY (Optional, sorts results) Syntax for SELECT statement 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 ❖There are two options: * (the asterisk) – which means to include all available columns , (the comma) - defined field list separated by commas ❖SELECT * ❖SELECT id, name Specific Columns from a Single Table SELECT SKU, SKU_Description, Department, Buyer FROM SKU_DATA; Selecting All Columns: The SQL Asterisk (*) Wildcard Character SELECT * FROM SKU_DATA; Specifying Column Order SELECT Department, Buyer FROM SKU_DATA; Specifying Column Order (2) SELECT Buyer, Department FROM SKU_DATA; Running an SQL Query in the MySQL Workbench Saving a MySQL Query Saving a MySQL Query as an SQL Script in the MySQL Workbench. Reading Specified Rows from a Single Table We can eliminate duplicates by using the SQL DISTINCT keyword as shown above. SELECT DISTINCT Buyer, Department FROM SKU_DATA; Controlling the Number of Rows by Using the LIMIT function We can control how many rows are displayed by using the SQL LIMIT {NumberOfRows} function. SELECT Buyer, Department FROM SKU_DATA LIMIT 5; Conditional Statements These are known as the WHERE clause ❖ Series of Boolean expressions ❖ Several operators ❖ Multiple clauses ❖ Brackets Controlling Specified Rows Using the WHERE clause SQL requires the use of single quotes when using a literal character string. But be sure to use the plain, nondirectional quotes used in basic text editors and not the slanted ones. SELECT * FROM SKU_DATA WHERE Department = 'Water Sports'; SQL Comparison Operators Operator Meaning = Is equal to Is NOT Equal to. Also != is now acceptable < Is less than > Is greater than Is greater than OR equal to IN Is equal to one of a set of values NOT IN Is NOT equal to one of a set of values BETWEEN Is within a range of numbers (includes the end points) NOT BETWEEN Is NOT within a range of numbers (includes the end points) LIKE Matches a sequence of characters NOT LIKE Does NOT match a sequence of characters IS NULL Is equal to NULL IS NOT NULL Is NOT equal to NULL SQL WHERE Clause Using Sets of Values With the IN Function SELECT * FROM SKU_DATA WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo’, 'Jerry Martin'); SQL WHERE Clause Using Sets of Values With the NOT IN Function SELECT * FROM SKU_DATA WHERE Buyer NOT IN ('Nancy Meyers’, 'Cindy Lo', 'Jerry Martin'); SQL WHERE Clause Using Math Symbols SELECT * FROM ORDER_ITEM WHERE ExtendedPrice >= 100 AND ExtendedPrice 200000; Using Column Names in the SQL WHERE Clause SELECT SKU_Description, Department FROM SKU_DATA WHERE Department = 'Climbing'; Using Column Names in the SQL WHERE Clause (2) SELECT SKU_Description, Buyer FROM SKU_DATA WHERE Department = 'Climbing’; SQL does not require that the column used in the WHERE clause also appear in the SELECT clause column list. Aliases: renaming in SQL Query ❖ Aliases (AS) are used to rename objects for the duration of the query. ❖ You can rename fields or tables ❖ Field renaming is good for ensuring valid field names when data is being received by a program ❖ Table renaming becomes important when JOINs are involved. Joins will be studied later. SELECT SUM(OrderTotal) AS OrderSum FROM RETAIL_ORDER; Aliases: renaming in SQL Query (2) Alias Column Syntax: SELECT column_name AS alias_name FROM table_name; Alias Table Syntax: SELECT column_name(s) FROM table_name AS alias_name; Examples of Alias Statements ❖ The SQL statement creates two aliases, one for the PetID column and one for the PetName column: SELECT PetID AS ID, PetName AS Pet FROM Clinic; ❖ The SQL statement below (in MySQL) creates an alias named “Location" that merges four columns (Location, PostalCode, City and Country): SELECT ClientName, CONCAT(Location,', ',PostalCode,', ',City,', ',Country) AS Location FROM Clients;