J

Chapter 03 slides

Chapter Overview

  • Title: How to Retrieve Data from a Single Table

  • Source: Murach’s SQL Server 2022 for Developers

  • Instructor: Mike Murach & Associates, Inc.

  • Year: 2023

Objectives

Applied Objectives

  • Code and run SELECT statements using language elements from this chapter.

Knowledge Objectives

  1. Distinguish between base table values and calculated values in SELECT statements.

  2. Describe the use of a column alias.

  3. Explain order of precedence and the use of parentheses in arithmetic expressions.

  4. Describe the DISTINCT keyword and the TOP clause.

  5. Explain comparison operators, logical operators, and parentheses in WHERE clauses.

  6. Describe the use of IN, BETWEEN, and LIKE operators in WHERE clauses.

  7. Explain the use of IS NULL clause in WHERE clauses.

  8. Describe the use of column names, aliases, calculated values, and column numbers in ORDER BY clauses.

  9. Discuss the use of OFFSET and FETCH clauses in ORDER BY clauses.

Basic SELECT Statement Syntax

  • Syntax: SELECT select_list [FROM table_source] [WHERE search_condition] [ORDER BY order_by_list]

Examples of SELECT Statements

1. Retrieving All Rows

  • Query: SELECT * FROM Invoices;

  • Output: 114 total rows, displaying InvoiceID, VendorID, Invoice Number, Date, Total, Payment Total, Credit Total, TermsID.

2. Retrieving and Sorting Data

  • Query: SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal;

3. Calculating Values

  • Query: SELECT InvoiceID, InvoiceTotal, CreditTotal + PaymentTotal AS TotalCredits FROM Invoices WHERE InvoiceID = 17;

4. Using BETWEEN Clause

  • Query: SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceDate BETWEEN '2023-01-01' AND '2023-03-31' ORDER BY InvoiceDate;

5. Empty Result Set Query

  • Query: SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceTotal > 50000;

Expanded SELECT Clause Syntax

  • Syntax: SELECT [ALL|DISTINCT] [TOP n [PERCENT] [WITH TIES]] column_specification ...

Column Specifications Examples

1. All Columns

  • SELECT * FROM table_name;

2. Specific Column Retrieval

  • SELECT VendorName, VendorCity, VendorState FROM Vendors;

3. Calculated Values

  • Arithmetic Example: SELECT InvoiceNumber, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Invoices;

  • String Example: SELECT VendorContactFName + ' ' + VendorContactLName AS FullName FROM Vendors;

  • Date Example: SELECT InvoiceNumber, InvoiceDate, GETDATE() AS CurrentDate FROM Invoices;

Naming and Calculating Columns

  • Use aliases for better readability.

  • Non-named calculated columns example: SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal - PaymentTotal - CreditTotal FROM Invoices;

  • String concatenation example: SELECT VendorCity, VendorState, VendorCity + VendorState FROM Vendors;

Date Calculations

  • Query: SELECT InvoiceDate, GETDATE() AS 'Today's Date', DATEDIFF(day, InvoiceDate, GETDATE()) AS Age FROM Invoices;

DISTINCT Keyword

  • Use: To eliminate duplicate rows.

  • Example: SELECT DISTINCT VendorCity, VendorState FROM Vendors;

TOP Clause Usage

  • Query with LIMIT: SELECT TOP 5 VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC;

  • With PERCENT: SELECT TOP 5 PERCENT VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC;

  • With TIES: SELECT TOP 5 WITH TIES VendorID, InvoiceDate FROM Invoices ORDER BY InvoiceDate ASC;

WHERE Clause Syntax

  • Syntax: WHERE expression_1 operator expression_2

Comparison Operators

  • = Equal, > Greater than, < Less than, <= Less than or equal to, >= Greater than or equal to, <> Not equal.

Logical Operators in WHERE Clauses

  1. AND: Combines multiple conditions that must all be true.

  2. OR: Combines multiple conditions where any must be true.

  3. NOT: Reverses the condition's truth value.

Examples of WHERE Clauses

  • Vendors located in Iowa: WHERE VendorState = 'IA';

  • Invoices with a balance due: WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

  • Filtering with IN clause, BETWEEN clause, and LIKE clause.

Using Parentheses for Order of Operations

  • Importance of parentheses to control evaluation order in compound conditions.

  • Query example: WHERE (InvoiceDate > '01/01/2023' OR InvoiceTotal > 500) AND InvoiceTotal - PaymentTotal - CreditTotal > 0;

IS NULL Clause

  • Syntax: WHERE expression IS [NOT] NULL;

  • Examples of queries retrieving rows with null values and non-null values.

ORDER BY Clause Syntax

  • Syntax: ORDER BY expression [ASC|DESC] [...]

Sorting Examples

  1. Single column ascending: ORDER BY VendorName;

  2. Descending order: ORDER BY VendorName DESC;

  3. Multiple columns: ORDER BY VendorState, VendorCity, VendorName;

Advanced ORDER BY with OFFSET and FETCH

  • Query: ORDER BY order_by_list OFFSET offset_row_count ROWS FETCH NEXT fetch_row_count ROWS ONLY;

  • Use it to retrieve a specific range of rows.