Appendix C: Performing Basic Queries

1
Appendix CPerforming Basic Queries
2
Overview

• Retrieving Data
• Formatting Result Sets
• Modifying Data

3
Retrieving Data

• Using the SELECT Statement
• Specifying Columns
• Using the WHERE Clause to Specify Rows
• WHERE Clause Search Conditions

4
Using the SELECT Statement

• Select List Specifies the Columns
• WHERE Clause Specifies the Rows
• FROM Clause Specifies the Table

Partial Syntax
SELECT ALL DISTINCT FROM
,n WHERE
5
Specifying Columns
6
Using the WHERE Clause to Specify Rows
7
WHERE Clause Search Conditions

• Using Comparison Operators (,,,)
• Using String Comparisons (LIKE and NOT LIKE)
• Using Logical Operators (AND, OR, NOT)
• Retrieving a Range of Values (BETWEEN and NOT
  BETWEEN)
• Using a List of Values as Search Criteria (IN and
  NOT IN)
• Retrieving Unknown Values (IS NULL and NOT NULL)

8
Using Comparison Operators
9
Using String Comparisons
10
Using Logical Operators
11
Retrieving a Range of Values
12
Using a List of Values as Search Criteria
13
Retrieving Unknown Values
USE northwind SELECT companyname, fax FROM
suppliers WHERE fax IS NULL
14
Formatting Result Sets

• Sorting Data
• Eliminating Duplicates
• Changing Column Names
• Using Literals

15
Sorting Data
16
Eliminating Duplicates
USE northwind SELECT DISTINCT country FROM
suppliers ORDER BY country
17
Changing Column Names
18
Using Literals
19
Lab C.1 Retrieving Data and Manipulating Result
Sets
20
Modifying Data

• Inserting Rows
• Deleting Rows
• Updating Rows

21
Inserting Rows
USE northwind INSERT customers (customerid,
companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone,
fax) VALUES ('PECOF', 'Pecos Coffee Company',
'Michael Dunn', 'Owner', '1900 Oak Street',
'Vancouver', 'BC', 'V3F 2K1', 'Canada', '(604)
555-3392', '[phone]')
22
Inserting Data by Using Default Values

• DEFAULT Keyword
• Inserts default values for specified columns
• Columns must have a default value or allow null
  values
• DEFAULT VALUES Keyword
• Inserts default values for all columns
• Columns must have a default value or allow null
  values

USE northwind INSERT shippers (companyname,
phone) VALUES ('Kenya Coffee Co.', DEFAULT)
23
Inserting Partial Data
Allows NULL Values
37
24
Deleting Rows

• DELETE Statement
• Use to remove one or more rows in a table
• Always include a WHERE clause
• Each deleted row is logged in the transaction log
• TRUNCATE TABLE Statement
• Use to delete all rows in a table
• SQL Server retains table structure and associated
  objects
• Only deallocation of data pages is logged in the
  transaction log

25
Updating Rows

• WHERE Clause Specifies Rows to Change
• SET Keyword Specifies the New Data
• Input Values Must Be the Same Data Types as
  Columns

USE northwind UPDATE products SET unitprice
(unitprice 1.1)
26
Performance Considerations

• Use Positive Search Conditions
• Avoid Using the LIKE Search Condition
• Use Exact Matches or Ranges
• ORDER BY Clause May Slow Data Retrieval

27
Recommended Practices
28
Lab C.2 Modifying Data
29
Review

• Retrieving Data
• Formatting Result Sets
• Modifying Data