SQLServerCh03

Chapter 3 How to retrieve data from a single table Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 1 Objectives Applied  Code and run SELECT statements that use any of the language elements presented in this chapter. Knowledge  Distinguish between the base table values and the calculated values in SELECT statements.  Describe the use of a column alias.  Describe the order of precedence and the use of parentheses for arithmetic expressions.  Describe the use of the DISTINCT keyword and the TOP clause.  Describe the use of comparison operators, logical operators, and parentheses in WHERE clauses. Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 2 Objectives (cont.) Knowledge  Describe the use of the IN, BETWEEN, and LIKE operators in WHERE clauses.  Describe the use of the IS NULL clause in a WHERE clause.  Describe the use of column names, aliases, calculated values, and column numbers in ORDER BY clauses.  Describe the use of the OFFSET and FETCH clauses in ORDER BY clauses. Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 3 The simplified syntax of the SELECT statement SELECT select_list FROM table_source [WHERE search_condition] [ORDER BY order_by_list] The four clauses of the SELECT statement  SELECT  FROM  WHERE  ORDER BY Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 4 A simple SELECT statement SELECT * FROM Invoices; (114 rows) Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 5 A SELECT statement that retrieves and sorts rows SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal; (114 rows) Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 6 A SELECT statement that retrieves a calculated value SELECT InvoiceID, InvoiceTotal, CreditTotal + PaymentTotal AS TotalCredits FROM Invoices WHERE InvoiceID = 17; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 7 A SELECT statement that retrieves all invoices between given dates SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceDate BETWEEN '2012-01-01' AND '2012-05-31' ORDER BY InvoiceDate; (101 rows) Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 8 A SELECT statement that returns an empty result set SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceTotal > 50000; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 9 The expanded syntax of the SELECT clause SELECT [ALL|DISTINCT] [TOP n [PERCENT] [WITH TIES]] column_specification [[AS] result_column] [, column_specification [[AS] result_column]] ... Five ways to code column specifications  All columns in a base table  Column name in a base table  Arithmetic expression  String expression  Function Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 10 Column specifications that use base table values The * is used to retrieve all columns SELECT * Column names are used to retrieve specific columns SELECT VendorName, VendorCity, VendorState Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 11 Column specifications that use calculated values An arithmetic expression is used to calculate BalanceDue SELECT InvoiceNumber, InvoiceTotal - PaymentTotal – CreditTotal AS BalanceDue A string expression is used to calculate FullName SELECT VendorContactFName + ' ' + VendorContactLName AS FullName A function is used to calculate CurrentDate SELECT InvoiceNumber, InvoiceDate, GETDATE() AS CurrentDate Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 12 Two ways to name the columns in a result set Using the AS keyword (the preferred technique) SELECT InvoiceNumber AS [Invoice Number], InvoiceDate AS Date, InvoiceTotal AS Total FROM Invoices; Using the equal operator (an older technique) SELECT [Invoice Number] = InvoiceNumber, Date = InvoiceDate, Total = InvoiceTotal FROM Invoices; The result set for both SELECT statements Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 13 A SELECT statement that doesn’t name a calculated column SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal - PaymentTotal - CreditTotal FROM Invoices; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 14 How to concatenate string data SELECT VendorCity, VendorState, VendorCity + VendorState FROM Vendors; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 15 How to format string data using literal values SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 16 How to include apostrophes in literal values SELECT VendorName + '''s Address: ', VendorCity + ', ' + VendorState + ' ' + VendorZipCode FROM Vendors; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 17 The arithmetic operators in order of precedence * / % + - Multiplication Division Modulo (Remainder) Addition Subtraction Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 18 A SELECT statement that calculates the balance due SELECT InvoiceTotal, PaymentTotal, CreditTotal, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Invoices; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 19 A SELECT statement that uses parentheses to control the sequence of operations SELECT InvoiceID, InvoiceID + 7 * 3 AS OrderOfPrecedence, (InvoiceID + 7) * 3 AS AddFirst FROM Invoices ORDER BY InvoiceID; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 20 A SELECT statement that uses the modulo operator SELECT InvoiceID, InvoiceID / 10 AS Quotient, InvoiceID % 10 AS Remainder FROM Invoices ORDER BY InvoiceID; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 21 What determines the sequence of operations  Order of precedence  Parentheses Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 22 A SELECT statement that uses the LEFT function SELECT VendorContactFName, VendorContactLName, LEFT(VendorContactFName, 1) + LEFT(VendorContactLName, 1) AS Initials FROM Vendors; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 23 A SELECT statement that uses the CONVERT function SELECT 'Invoice: #' + InvoiceNumber + ', dated ' + CONVERT(char(8), PaymentDate, 1) + ' for $' + CONVERT(varchar(9), PaymentTotal, 1) FROM Invoices; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 24 A SELECT statement that computes the age of an invoice SELECT InvoiceDate, GETDATE() AS 'Today''s Date', DATEDIFF(day, InvoiceDate, GETDATE()) AS Age FROM Invoices; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 25 Terms  Function  Parameter  Argument Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 26 A SELECT statement that returns all rows SELECT VendorCity, VendorState FROM Vendors ORDER BY VendorCity; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 27 A SELECT statement that eliminates duplicate rows SELECT DISTINCT VendorCity, VendorState FROM Vendors; (53 rows) Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 28 A SELECT statement with a TOP clause SELECT TOP 5 VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 29 A SELECT statement with a TOP clause and the PERCENT keyword SELECT TOP 5 PERCENT VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 30 A SELECT statement with a TOP clause and the WITH TIES keyword SELECT TOP 5 WITH TIES VendorID, InvoiceDate FROM Invoices ORDER BY InvoiceDate ASC; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 31 The syntax of the WHERE clause with comparison operators WHERE expression_1 operator expression_2 The comparison operators  =  >  <  <=  >=  Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 32 Examples of WHERE clauses that retrieve… Vendors located in Iowa WHERE VendorState = 'IA' Invoices with a balance due (two variations) WHERE InvoiceTotal – PaymentTotal – CreditTotal > 0 WHERE InvoiceTotal > PaymentTotal + CreditTotal Vendors with names from A to L WHERE VendorName < 'M' Invoices on or before a specified date WHERE InvoiceDate <= '2012-05-31' Invoices on or after a specified date WHERE InvoiceDate >= '5/1/12' Invoices with credits that don’t equal zero WHERE CreditTotal 0 Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 33 The syntax of the WHERE clause with logical operators WHERE [NOT] search_condition_1 {AND|OR} [NOT] search_condition_2 ... Examples of queries using logical operators The AND operator WHERE VendorState = 'NJ' AND YTDPurchases > 200 The OR operator WHERE VendorState = 'NJ' OR YTDPurchases > 200 The NOT operator WHERE NOT (InvoiceTotal >= 5000 OR NOT InvoiceDate <= '2012-07-01') The same condition without the NOT operator WHERE InvoiceTotal < 5000 AND InvoiceDate <= '2012-07-01' Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 34 A compound condition without parentheses WHERE InvoiceDate > '01/01/2012' OR InvoiceTotal > 500 AND InvoiceTotal - PaymentTotal - CreditTotal > 0 (100 rows) The order of precedence for compound conditions  NOT  AND  OR Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 35 The same compound condition with parentheses WHERE (InvoiceDate > '01/01/2012' OR InvoiceTotal > 500) AND InvoiceTotal - PaymentTotal - CreditTotal > 0 (11 rows) Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 36 The syntax of the WHERE clause with an IN phrase WHERE test_expression [NOT] IN ({subquery|expression_1 [, expression_2]...}) Examples of the IN phrase An IN phrase with a list of numeric literals WHERE TermsID IN (1, 3, 4) An IN phrase preceded by NOT WHERE VendorState NOT IN ('CA', 'NV', 'OR') An IN phrase with a subquery WHERE VendorID IN (SELECT VendorID FROM Invoices WHERE InvoiceDate = '2012-05-01') Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 37 The syntax of the WHERE clause with a BETWEEN phrase WHERE test_expression [NOT] BETWEEN begin_expression AND end_expression Examples of the BETWEEN phrase A BETWEEN phrase with literal values WHERE InvoiceDate BETWEEN '2012-05-01' AND '2012-05-31' A BETWEEN phrase preceded by NOT WHERE VendorZipCode NOT BETWEEN 93600 AND 93799 A BETWEEN phrase with a test expression coded as a calculated value WHERE InvoiceTotal – PaymentTotal – CreditTotal BETWEEN 200 AND 500 A BETWEEN phrase with calculated values WHERE InvoiceDueDate BETWEEN GetDate() AND GetDate() + 30 Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 38 Warning about date comparisons  All columns that have the datetime data type include both a date and time, and so does the value returned by the GetDate function.  When you code a date literal without a time, the time defaults to 12:00 AM (midnight). As a result, a date comparison may not yield the results you expect. Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 39 The syntax of the WHERE clause with a LIKE phrase WHERE match_expression [NOT] LIKE pattern Wildcard symbols  %  _  [ ]  [ - ]  [ ^ ] Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 40 WHERE clauses that use the LIKE phrase Example 1 WHERE VendorCity LIKE 'SAN%' Cities that will be retrieved “San Diego” and “Santa Ana” Example 2 WHERE VendorName LIKE 'COMPU_ER%' Vendors that will be retrieved “Compuserve” and “Computerworld” Example 3 WHERE VendorContactLName LIKE 'DAMI[EO]N' Names that will be retrieved “Damien” and “Damion” Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 41 WHERE clauses that use the LIKE phrase (cont.) Example 4 WHERE VendorState LIKE 'N[A-J]' States that will be retrieved “NC” and “NJ” but not “NV” or “NY” Example 5 WHERE VendorState LIKE 'N[^K-Y]' States that will be retrieved “NC” and “NJ” but not “NV” or “NY” Example 6 WHERE VendorZipCode NOT LIKE '[1-9]%' Zip codes that will be retrieved “02107” and “08816” Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 42 The syntax of the WHERE clause with the IS NULL clause WHERE expression IS [NOT] NULL The contents of the NullSample table SELECT * FROM NullSample; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 43 A SELECT statement that retrieves rows with zero values SELECT * FROM NullSample WHERE InvoiceTotal = 0; A SELECT statement that retrieves rows with non-zero values SELECT * FROM NullSample WHERE InvoiceTotal 0; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 44 A SELECT statement that retrieves rows with null values SELECT * FROM NullSample WHERE InvoiceTotal IS NULL; A SELECT statement that retrieves rows without null values SELECT * FROM NullSample WHERE InvoiceTotal IS NOT NULL; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 45 The expanded syntax of the ORDER BY clause ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]]... An ORDER BY clause that sorts by one column SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY VendorName; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 46 The default sequence for an ascending sort  Nulls  Special characters  Numbers  Letters Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 47 An ORDER BY clause that sorts by one column in descending sequence SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY VendorName DESC; Murach's SQL Server 2012, C3 © 2012, Mike Murach & Associates, Inc. Slide 48