Querying Data Using T-SQL

Module 1 Course Overview
  SQL is around for 50+ years 
  How SQL work > Syntax
  Logical challenges & pitfall

  SQL Terminology & Tools
  Intricacies of SQL
  Underlying concepts of SQL & Relational Model counter part
  How data is processed by every clause of SELECT query
  How to prepare source data, filter it effectively 
  How to handle complications of missing data
  How to combine multiple Rows
  How to support presentation, ordering and paging and when NOT to   

Module 2 What Is T-SQL?
  Module Introduction
    Terminology
    Architectural Overview
    Tools

  What Is T-SQL?
    Standardized by ANSI and ISO
    1st edition published in 1986, latest in 2019
    Each vandor uses subset of and extensions to ANSI SQL standard 
    T-SQL = Transact SQL 
      used by Microsoft SQL Server and Sybase
    
    MS SQL Server = relational DB management system (RDBMS)
    Other types of Database Management System (DBMS) include:
      Hierarchal
      Object-oriented 
      Document stores 
      Key value stores
      Graph database

    Relational model  
      a data modeling paradigm based on set theory and first order predicate logic

    What is DB? Simon Allardice's 'Databases - executive briefing'

  A Little SQL Server Architecture
    SQL Server => SQL Server Services
      Core SQL DB Engine
      Macine learning 
      Integration Analysis 
      Reporting 
      Replication
      Others 

    Core SQL DB Engine can be installed on 
      Windows / Linux OS
      Docker container

    Most Cloud provider offer SQL Server as a managed cloud product 
    MS Azure has Azure SQL DB, a SQL Server DB in a platform as a Service 

    SQL Server is 
      a multi-user server
      communicated via TCP / IP port

    User and application servers use API to send queries to server and receive results from network

    Two authentication methods for users and application clients
      SQL Authentication - login credential stored internally 
      Windows Authentication - Active Directory for single sign-on scenario

    SQL Server Object Hierarchy
      OS contains 1 or more ...
      SQL Server Instance that contains 1 or more ...
      Database that contains 1 or more ...
      Schema that contains ...
      Object(s) like
        Table
        View
        Store Procedure
        others    

    Fully Qualified Object Names
      Can access every object
      Consist of 4 parts: [Instance].[Database].[Schema].Object

    Omit 
      Instance - use current instance connection
      Database - use current session's DB context
      Schema   - use dbo (database owner) or explicit default schema

  Tools
    SQL Server Instance: azure sql database also available  
    Client IDE: SSMS or Azure Data Studio
    Online tools - No persist data:
      www.sbfiddle.uk
      www.sqlfiddle.com

  Demo: Tools
  Module Review

Module 3 Our First SELECT
  Module Introduction
    Query execution order
    Terminology 
      Sets
      Expressions
      Operators
      Functions
      Aliases
      Data types
    SELECT without FROM
  What Is a SQL Query?
    Complete list of SQL clause (caluse = part of a sentence that contains a verb):
      (5) SELECT          - List of expressions to return
      (1) FROM            - Source data set, the ONLY data available for all following clauses 
      (2) WHERE           - Filter rows with predicate (a Boolean function that can return True or False)  
      (3) GROUP BY        - Rows combined into groups based on grouping expression
      (4) HAVING          - Filter whole groups instead of rows
      (6) ORDER BY        - Sort for presentation order
      (7) OFFSET - FETCH  - page specification; limit numbers of rows that will be returned 

  More Terminology
    Set - a collection of unique elements with No order (neither for Row, nor for Column); also an element on its own
    Relation - how a subset of elements related to each other which form a meaningful unit (Table)
    Tuples - a finite ordered list of elements of a relation; AKA Rows 
    Rows - unique and have NO order 
    Key - attribute(s) that ensure uniqueness of Rows, like name, ISBN or order number
    Tuple - attribute, known as Column in SQL 
    Alias 
      - name to hold datasets or individual expressions
      - by default Alias is an object's name, like "Employees" table
      - any expression involve more than just base column lose column alias (AS)

    SQL is a Stringly Typed language = every expression in SQL has a specific data type
      Numeric - integers, decimals and floating-point types
      String  - encoded in ASCII or Unicode 
      Binary  - technically a string, but usually used to store images, video and audio 
      Temporal- Time-related attributes, like date, time or combinations 
     
      Special Data Types
        Hierarchy
        Spatial
        Unstructured data type / Document (XML / JSON)
      
      What is NOT in T-SQL
        Interval  - timespan
        Boolean   - use Bit (numeric type) instead
      
      Operators and Functions
        Creates new expressions from existing ones
          Arithmetic: + - * / %
          String: SUBSTRING, UPPER, LTRIM, + 
          Date & Time: GETDATE, YEAR, DATEDIFF, DATEADD
          Bitwise: &, ^, |, ~
          Comparison: >, <, <>, =, !=, LIKE
          Logical: AND, OR, NOT, IN, ANY, BETWEEN

        User-defined functions

  Demo: First SELECT
    SELECT without FROM
    Examples:
      SELECT 'X'; -- return single row with single column and the value string 'X'
      SELECT X; -- SQL will evaluate X as identifier / alias (for a column) and gets an error b/c X does not exist
      
      SELECT 2 * 7, SQRT(2); 
        -- Expression can contains multiple sub-expressions w/ operators 
        -- separate by comma cause each one to be returned as a separate column of the reults set
      
      SELECT 2 * 7 AS Easy, SQRT(2) AS LessEasy;
        -- Use keyword AS to assign alias(column name) to expressions 

      SELECT 7 / 2; -- return 3 (integer)

      SELECT 7 / 2 * 1.00; -- return 3.00; be aware of Operator Precedence 

      SELECT 7 / (2 * 1.00); -- return 3.50  

    The preferred method to process decimal is use CAST()
      CAST() convert expression from one type to another
        CAST( expression AS datatype(length) )
      SELECT CAST(7 AS DECIMAL(5,2)) / 2; -- return 3.50
    
    Always use ISO format (YYYYMMDD) when CAST string into DATE type
      SELECT CAST('20191218' AS DATE) AS TODAY;

    Avoid implicit conversion in SQL
      SELECT '4' + 4; -- return intteger 8

  Module Review

Module 4 The FROM Clause
  Module Introduction
    FROM clause - evaluation of the source dataset of any query 
    SELECT FROM single source set (table)
    SELECT FROM multiple source sets (tables)

  TSQL Demo Database
    Create Demo DB with script "Module 4 - TSQL Demo DB.sql"
    Table:
      Customers
        Customers' name as key (key icon is SSMS) and field "Country"  
        No customer ID or number - avoid surrogate keys or ids for every table
        Customer ID will not likely to be used to refer to the customer in real world 
        ??? QUESTION: How to uniquely, concisely identify numerous customers without using ID? Composite Attribute?
      Orders
        Autogenerated OrderID as primary key: 
          OrderID is used elsewhere once generated
          OrderID is used to IDENTIFY order by customers and seller - a valid attribute of order itself 
          Customer field is SQL foreign key 
            A foreign key is the counterpart of the relational models referential integrity constraint 
            - Enforce the rule that only valid customers in Customers table can place order
      Items:
        Item is the key and second attribute is "Color"
      Order Items:
        Contains all the items sold in each order
        2 foreign keys: OrderID and Item - ensure both OrderID and Item are valid 
    
  Demo: FROM Single Table
    Example 1
      SELECT *
      FROM Customers; -- Do NOT use this in production

    1. Customers table is evaluated by FROM caluse (the source set)
    2. Customers table is then evaluated by SELECT clause
    3. SELECT clause evaluate EACH ROW from source set to construct the results 

    Example 2
      SELECT 'Pluralsight' AS BestTraining 
      FROM Customers;

    1. Customers table is evaluated by FROM caluse
    2. Then the source set is evaluated in SELECT clause 
    3. Expression in SELECT clause is evaluated for EACH ROW
    4. Expression can be columns from table OR literal constant like a string   
    5. Result set has same number of rows as Customers table under column name 'BestTraining', each display 'Pluralsight'

    Example 3. 
      SELECT  OrderID + 0 AS OrderID, 
              OrderDate, 
              Customer AS Client
      FROM    Orders;
    
    1. Combine column with ANY operator (like '+') will REMOVE column name from resulting column
    2. It is recommanded to use AS keyword to provide alias in this case
    3. An alternative column name can also be used to subsitute the original column name with AS keyword
    4. Aliasing with AS helps clarify expressions
    
  Introduction to Multiple Tables in FROM
    Multiple Data Sources - need to JOIN separate data sources together
    JOIN 2 tables 
      Table_1 = (A, B, C) 
      Table_2 = (B, E)  
    
    Step 1 - CROSS JOIN to produce Cartesian Product
    1. Every JOIN start with Cartesian Product (CROSS JOIN) - every element from table 1 is paried with every elements from table 2
       Cartesian Product = (AB, AE, BB, BE, CB, CE)
       Table_1 CROSS JOIN Table_2 
    2. CROSS JOIN is not often used - typically rows from both table are matched based on common denominator 
       Such as orders from a specific customer OR order with specific items solde 

    Step 2 - Matching Rows 
    Table_1 INNER JOIN Table_2
    1. INNER JOIN start with Cartesian Product and a JOIN PREDICATE that specifies match condition 
    2. JOIN PREDICATE is used to evaluate each pair of element from Cartesian Product
    3. If JOIN PREDICATE evaluated to TRUE, the pair will be kept, else the pair will be removed
    4. Query continue with kept pairs to next phase

    Example.
      Table_1 INNER JOIN Table_2
      ON
      Table_1.Character = Table_2.Character  
      
      For Cartesian Product = (AB, AE, BB, BE, CB, CE)
      Only 'BB' is kept

    Step 3 - Add reserved rows
    1. To keep ALL elements from Table_1, regardless if they have matching element in Table_2 
       Table_1 need to be reserved with LEFT OUTER JOIN - b/c it is on the LEFT side
    2. The elements from reserved set that FAILED JOIN PREDICATE evaluation are now re-introduced 
    3. All rows of result set have same structure 
       so the elements from reserved set that failed predicate need to be paired as well 
    4. "NULL" is paried with re-introduced element to indicate a missing value 

    Example.
      Table_1 LEFT OUTER JOIN Table_2
      ON
      Table_1.Character = Table_2.Character 

      For Cartesian Product = (AB, AE, BB, BE, CB, CE)
      Only 'BB' is kept
      A and C failed JOIN PREDICATE Table_1.Character = Table_2.Character, but now re-introduced  
      result = (AxNULL, BB, CxNULL)

  Demo: Multiple Tables
    Using Customer's name (Primary Key in Customers table) in Orders table as Natural keys
    Natural Keys are meaningful / real attributes
    Natural keys reduce the need to JOIN tables  

    Step 1 - Cartesian Product
      SELECT  *
      FROM    Customers
              CROSS JOIN
              Orders;
    
    Step 2 - INNER JOIN
      SELECT  *
      FROM    Customers AS C
              INNER JOIN
              Orders AS O
              ON C.Customer = O.Customer; 
      The predicate evaluate to TRUE if customer is paired with own order 
      Alias Table to shorter name (C and O) to keep query concise 
      SQL server evaluate every row of the Cartesian Product and eliminate all that failed  

      If a JOIN predicate is ALWAYS TRUE, then the result would be the same as CROSS JOIN

    Step 3 - LEFT OUTER JOIN
      To include customers who never made an order 

      SELECT  *
      FROM    Customers AS C
              LEFT OUTER JOIN
              Orders AS O
              ON C.Customer = O.Customer; 

  Module Review

Module 5 Filtering with WHERE
  Module Introduction
    Ternary logic (3 valued logic) 
    NULL indicators - how to handle it correctly for filtering 
    Logical predicates
    Using WHERE

  Understanding NULLs
    NULL 
      Indicate missing or inapplicable data 
      Not part of original relational model - introduced in 1975 (standardized in 1986)
    
    NULL IS NOT A VALUE!
      - can be seen as marker, indicator or state
  
  3VL (Ternary) Logic
    3 values: True, False, Unknown 
    Rule #1: Any comparison of two unknowns is unknown
    Rule #2: Comparison of known value to an unknown is unknown 
    Rule #3: State predicates evaluate to True or False

  Logical Predicates and Operators
    Additional Logical Operators - When using multiple predicates, be sure to use parentheses
      ALL
        Predicate: X > ALL(A, B, C) is True, only when ...
        X > A AND X > B AND X > C
        This will evaluate the same for Any comparison operator: <, ==, !=, ...
        If A, B or C is NULL, the predicate X > ALL(A, B, C) will evaluate to Unknown
      
      ANY / SOME
        Predicate: X > ANY|SOME(A, B, C) is True, when ...
        X > A OR X > B OR X > C
        If any of A, B or C is NULL, as long as X still more than any of the rest, 
        the predicate is True 
        Else, the predicate evaluate to Unknown 

      BETWEEN
        Predicate: X BETWEEN A AND B is true when ...
        X >= A AND X <= B
        Bounderies included 

      IN 
        Predicate: X IN (A, B, C) is true when ...
        X = A OR X = B OR X = C

      NOT IN
        Predicate: X NOT IN (A, B, C) is true when ...
        X <> A AND X <> B AND X <> C
        If any operand is NULL, the entire predicate will always evaluate to Unknown 
        
      LIKE
        Predicate: X LIKE ( <pattern> ) is true when ...
        X matches wildcard pattern 

      EXISTS
        Predicate: EXISTS (sub-query) is true when 
        sub-query returns any row at all 

    NULL Predicates 
      How to check if an expression is or is not NULL
        1. X = NULL is always evaluate to (unknown) NULL, regardless X IS NULL or NOT

        2. X <> NULL is always evaluate to (unknown) NULL, regardless X IS NULL or NOT

        3. X IS NULL evaluate to True if X is null, Flase if not; Never unknown 

        4. X IS NOT NULL evaluate to False if X is null, True if not; never unknown 
        
  Filtering with WHERE
    Example 1
    SELECT	*
    FROM	Customers
    WHERE	Country = NULL; 
    -- This WHERE clause will always evaluate to Unknown, so NO rows can be returned for results
    -- The results will display all columns from table Customers without any row

    Example 2
    SELECT	*
    FROM	Customers
    WHERE	Country IS NULL; 
    -- This WHERE clause will always evaluate to True when Country attribute is NULL

    Example 3
    SELECT  *
    FROM    Customers
    WHERE   Country IS NOT NULL;
    -- This WHERE clause will always evaluate to True when Country attribute is NOT NULL

    Example 4
    SELECT  *
    FROM    Orders
    WHERE   OrderDate BETWEEN '20190101' AND '20190115';
    -- WHERE clause will return OrderDate 01/01/2019 to 01/15/2019, boundaries included 
    -- If OrderDate is NULL, WHERE clause will filter out row as well

    Example 5
    SELECT  *
    FROM    Customers
    WHERE   Country BETWEEN 'A' AND 'Z';   
    -- If Country is NULL, WHERE clause will filter out row as well

    Example 6
    SELECT  *
    FROM    Items 
    WHERE   Item IN ('Turntable', 'Amplifier');
    -- Return rows with Item attribute equal to 'Turntable' OR 'Amplifier' 

    Example 7
    SELECT  *
    FROM    Items 
    WHERE   Item NOT IN ('Turntable', 'Amplifier');
    -- Return rows with Item attribute NOT equal to 'Turntable' OR 'Amplifier' 

    Example 8 
    SELECT  *
    FROM    Items
    WHERE   Item IN ('Turntable', 'Amplifier', NULL);
    -- Still return rows with Item attribute equal to 'Turntable' OR 'Amplifier' 
    -- Item = 'Turntable' OR Item = 'Amplifier' OR Item = NULL
    -- Item = 'Turntable' OR Item = 'Amplifier' OR Unknown
    -- still evaluate to the same results 

    Example 9
    SELECT  *
    FROM    Items 
    WHERE   Item NOT IN ('Turntable', 'Amplifier', NULL);
    -- return NO results
    -- Item != 'Turntable' AND Item != 'Amplifier' AND Item != NULL
    -- Item != 'Turntable' AND Item != 'Amplifier' AND UNknown
    -- Always evaluate to false

    Example 10
    SELECT  *
    FROM    Items 
    WHERE   Item LIKE 'A%';   
    -- Return rows with Item attribute that start with character A
    -- pattern matching ignores difference b/t upper and lower case
    -- '%' = any sequence length of characters 
  
  Module Review

Module 6 Grouping Rows
  Module Introduction
    Why grouping?
    Grouping fundamental 
      - How grouping fits in general scheme of query processing 
      - Limit of grouping introduce to the rest of the query 
    Group By clause - create groups
    Having clause - filter groups  

  What Is Grouping Good For?
    Get A higher level of insight (instead of attributes from individual rows)
    from each of the sub-groups (bsed on common denominator)

    The output include:  
      common denominator for grouping
      derived attribute
    
    Once group by common denominator, individual row attribute is no longer accessible  

  Grouping Rows
    Grouping
      GROUP BY 
        - works on the data set passed from WHERE clause
        - takes attribute of all individual rows with the same attribute and place them in a single group
      GROUP BY expression defines how individual rows to be grouped
      After GROUP BY
        working set consisted 1 row per group  
        only values stays the same are the one specified by GROUP BY expression 
        The only way to reference a column that is NOT part of GROUP BY expression is 
          to instruct SQL server 
            pick one of the values from the group, OR
            calculate a single value based of the group  
            i.e, Aggregate function
      
      Aggregate function operates on a set of elements and return A SINGLE value
      Common aggregate Functions:
        Minimum
        Maximum
        Average
        Count
        T-SQL specific
          Standard deviation
          Variance
      
      Example 1:
        Phase 1
          SELECT  *
          FROM    Customers;  -- return ALL customer rows
        
        Phase 2
          SELECT  *
          FROM    Customers
          WHERE   Country IS NOT NULL;  -- remove rows with unknown (NULL) Country 

        Phase 3
          SELECT  *
          FROM    Customers
          WHERE   Country IS NOT NULL
          GROUP BY Country;
        
        This query will produce following error:
          Msg 8120, Level 16, State 1, Line 1
          Column 'Customers.Customer' is invalid in the select list because 
          it is not contained in either an aggregate function or the GROUP BY clause.
        There are potentially manay rows of the same 'Country'
        GROUP BY can return ONLY ONE row for each 'Country' - Thus the error from SELECT * 

        Phase 4
          SELECT  Country, COUNT(*) AS NumberOfCustomers
          FROM    Customers
          WHERE   Country IS NOT NULL
          GROUP BY Country;
        
        New SELECT clause contains 
          Country   - in GROUP BY clause
          COUNT(*)  - * is now in aggregate fucntion; 
                      this counts number of rows (Customers) within each group (Country)
      
      Example 2
        SELECT  Country, COUNT(*) AS NumberOfCustomers
        FROM    Customers
        GROUP BY Country;
        -- This query will now also return rows WHERE Country IS NULL

        -- Re-execute query above after execute following statement 
        INSERT INTO Customers (Customer, Country)
        VALUES ('Jane', NULL);

        Now two rows with Country IS NULL is grouped into single group
        While two NULL are different mathmatically, they will be treated as the same for grouping

  Group Filtering with HAVING
    The HAVING Group filter 
      Having clause operates on group set AFETR GROUP BY clause
      Having clause can eliminate group with aggregate predicate 
      Having clause CANNOT reference individual rows 

    Example 1
      SELECT  Country, COUNT(*) AS NumberOfCustomers
      FROM    Customers
      GROUP BY Country
      HAVING COUNT(*) > 1; 
      -- Show Country with more than 1 customer
      -- An aggregate cannot be used in WHERE (with exceptions)

    Example 2 
      SELECT  Country, COUNT(*) AS NumberOfCustomers
      FROM    Customers
      GROUP BY Country
      HAVING Country IS NOT NULL; 
      -- HAVING clause can use more than just aggregate 
      -- However, filter by GROUP BY expression can be done more efficiently with WHERE clause
    
    Example 3 
      SELECT  Country, COUNT(*) AS NumberOfCustomers
      FROM    Customers
	    WHERE	  Country IS NOT NULL
      GROUP BY Country;
      -- same results as previous example, but more efficient 
      -- b/c extra step to create additional group then remove some of them right after GROUP BY clause 

  Module Review
    NOT ALL relational database management system (RDBMS) respect logical grouping rules
      - SQLite and possibly MySQL (but not by default)

Module 7 Evaluating SELECT Expressions
  Module Introduction
    How SELECT expressions are processed 
    How to deal with NULL - for better presentation to client application
    How to eliminate duplicates with DISTINCT - and its logical implication

  Evaluating SELECT Expression
    SELECT processing takes place AFTER HAVING clause
    SELECT process each row one by one
      For each row, ALL expressions are evaluated ALL at once
    
    The SELECT expressions consisted of:
      The source columns
      Expressions based on these source columns
      Literal constants
      Functions
      Any other valid expression

    Expressions can (and need to) be uniquely aliased 
      Direct column reference inherit column name as Alias - but any operator or function revoke the inheritance  
      A relational set requires uniquely referencable attributes 
      Reference each expression with unique and friendly alias  
    
    Eliminate or keep duplicate rows with ALL | DISTINCT
      After all rows and expressions are evaluated:
        DISTINCT is used to eliminate duplicate rows
      ALL is typically omitted from query and is assumed by default 
        By default or with ALL, SQL server returns all rows without looking for duplicate 

    Example: ROW by ROW and ALL at Once
      SELECT OrderId, Item, (Quantity * Price) AS Amount, Amount * 0.9 AS Discounted
      FROM OrderItems
      -- this query will produce error:
      -- Msg 207, Level 16, State 1, Line 1
      -- Invalid column name 'Amount'.

      Since all expressions from each row is processed ALL AT Once,
      'Amount' will not be recognized as valid name from the same SELECT clause 

  Dealing with NULLs - Replace NULL with more user-frindly values
    T-SQL specific function:
      ISNULL(X, Y) 
        -- If X IS NULL, return Y (a literal constant like "N/A", "0" OR an expression)
        -- This is NOT "IS NULL" predicate 
    More generic case conditional function:
      COALESCE(X, Y, Z)
        -- COALESCE can be used with ANY number of operands
        -- Return the left most non-NULL or known expression 
    Evaluate to NULL with equal operands 
      NULLIF (X, Y) -- Evaluates to NULL if X = Y

  Using DISTINCT
    DISTINCT (optional) applies After ALL expressions have been evaluated for ALL rows, 
    DISTINCT eliminates duplicate rows based on ALL SELECT list expressions
    NULLs are treated as the same (GROUP BY), but NOT equal 
    
    Example
      STEP 1
        SELECT  Country
        FROM    Customer;  -- get duplicated rows

      STEP 2
        Two options to remove duplicate: DISTINCT or GROUP BY - same results 
        Both treated NULL as the same but NOT EQUAL 
          using DISTINCT:
            SELECT  DISTINCT Country
            FROM    Customers;
          
          using GROUP BY:
            SELECT  Country
            FROM    Customers
            GROUP BY Country;
      
      STEP 3
        Adding Customer to SELECT clause with DISTINCT

        SELECT  DISTINCT Country, Customer
        FROM    Customers; 

        DISTINCT applies to BOTH Country and Customer (Primary Key)
        - this effectively returns ALL rows
      
      STEP 4 
        Replace NULL (in Country) with readble message

        SELECT  DISTINCT ISNULL(Country, 'N/A') AS Country, Customer
        FROM    Customers;

        -- ISNULL(Country, 'N/A') replace Country expression when it IS NULL and replace it with 'N/A'
        -- Using function ISNULL() removed inheritted Column name, so aliasing with AS Country  

  Module Review

Module 8 Ordering and Paging
  Module Introduction
    Presentation Ordering 
    Paging (AKA. Pagination)
    Ordering - why should we NOT do it in DB?
    ORDER BY clause
    Query Determinism 
    Tiebreakers
    Paging results set with 
      OFFSET - FETCH
      T-SQL 'TOP' operator 
  
  Law of Order
    SET has NO ORDER
    CURSOR = Apply order to SET 
    A relational model that deals with SET ONLY cannot handle CURSOR

    CURSOR in SQL is a row level operation
    Order is important in presentation

    Whose job is it to ORDER rows?
      3-Tier Architecture - idealy each tier should be independent 
        Presentation
        Business
        Database
      
      Opinion: 
        It's NOT DB's responsibility to deal with presentation
        Ordering is the responsibility of presentation tier
      
      Downside of ordering in DB
        Lower query performance 
        Data inconsistency 
        Possible bugs
      
      Business rules / logic should stay out of DB

      Exception: Use ORDER BY for Paging

  ORDER BY
    Receive dataset AFTER SELECT clause
    Ideally ORDER BY clause can only access expressions defined in SELECT clause
    ORDER BY is THE ONLY clause that can use aliases defined in SELECT clause
    ORDER BY ascending (by default) or descending order
    In T-SQL: 
      NULL has lowest ordering value 
      Use conditional expression like 'CASE' to replace NULL with value that's higher than others 
        - This is the ONLY way to make NULL sort last
    In POSTGRES: 
      NULL has highest ordering value
      option to use 'NULL FIRST' or 'NULL LAST' options

  Query Determinism and Tiebreakers
    Deterministic query: 
      when executed multiple times over the SAME dataset 
      return the SAME results
    
    This property can be affected by ORDER BY (among other factors)
    
    When Order by non-unique expression (2 or more rows have the same ordering expression value),
    SQL Server can return tied rows in ANY order 

    To ensure consistency in ordering: 
      SQL query is responsible to specify ORDER BY expressions so there is an unique ordering value per row 
    
    To ensure unique ordering value / row:
      Additional expressions are needed to serve as "tibreakers"

    Example:
      ORDER BY OrderID -- 2 or more rows have the same value for OrderID need a tiebreaker

      ORDER BY OrderID, Item -- additional expression used as tiebreaker 

  Paging Result Sets
    Paging (in DB)
      - process the number of rows too many for human; 
      - especially when caching at presentation tier is too costly

    T-SQL has 3 ways to limit result sets:
      1. TOP operator 
        - Specified in SELECT clause, 
        - Before SELECT expressions
        - After (Optional) DISTINCT
        - can be used WITHOUT ORDER BY clause - return any rows
        - Not used in other DBMS
        - No OFFSET option, thus hard to use for paging purpose
      
        TOP(n) WITH TIES 
          would return more than n rows IF additional rows share the same sorting values 

          SELECT TOP(5) WITH TIES OrderID
          FROM Orders
          ORDER BY OrderDate DESC;
      
        TOP(n) PERCENT WITH TIES
          would return more than n% rows IF additional rows share the same sorting values 
      
      2. OFFSET - FETCH clause
        - After ORDER BY clause
        - ANSI/ISO standard 

          SELECT OrderID
          FROM Orders
          ORDER BY OrderDate DESC
          OFFSET 0 ROWS
          FETCH NEXT 5 ROWS ONLY;

      3. LIMIT - OFFSET clause
        - used by many other DBMS
        - non-ANSI standard

          LIMIT 5 OFFSET 0;  

  Demo: Ordering and Paging
    Step 1: Sort order by date and display latest one first
      SELECT  *
      FROM    Orders
      ORDER BY OrderDate DESC;

    STEP 2. Sort OrderItems by Item in ascending order
      SELECT  *
      FROM    OrderItems
      ORDER BY Item ASC;

    STEP 3. Sort OrderItems by Quantity, but not display Quantity
      SELECT  OrderID, Item
      FROM    OrderItems
      ORDER BY Quantity;
    
    STEP 4. Sort OrderItems by OrderID, but without tiebreaker
      SELECT  *
      FROM    OrderItems
      ORDER BY OrderID; -- rows with same OrderID canbe displayed in random order among themselves

    STEP 5. Add tiebreaker to ensure deterministic query (Primary Key is OrderID and Item combined)
      SELECT  *
      FROM    OrderItems
      ORDER BY OrderID, Item;

    STEP 6. Return TOP 3 items based on quantity sold
      SELECT  Item, SUM(Quantity) AS NumberOfItemSold
      FROM    OrderItems
      GROUP BY Item
      ORDER BY NumberOfItemSold DESC
      OFFSET 0 ROWS FETCH NEXT 3 ROWS; -- Page #1 display 3 rows

  Module Review
    A Query without an ORDER BY clause will NOT return rows in ANY specific order!  

Module 9 Wrapping Up
  Module Introduction
  Course Review
  Takeaway
  Feedback
  Next Steps
  Additional Resources and Conclusion