Chapter 6: Accessing and Modifying Data

1
Chapter 6 Accessing and Modifying Data
2
Overview

• Accessing Data in a SQL Server Database
• Using Advanced Query Techniques to Access Data
• Modifying Data in a SQL Server Database

3
Accessing Data in a SQL Server Database
P206

• SELECT Statement typically includes
• 1) Columns to be included in result set
• 2) Tables from which data is retrieved
• 3) Condition to be met (WHERE clause)
• 4) Ordering sequence (ORDER BY clause)

SELECT ProductID, ProductName, UnitPrice FROM
Products WHERE UnitPrice 40 ORDER BY UnitPrice
ASC
4
SELECT statement syntax
P207

• SELECT select_list
• INTO new_table_name
• FROM table_list
• WHERE search_conditions
• GROUP BY group_by_list, WITH ROLLUPCUBE
• HAVING search_conditions
• ORDER BY order_list ASC / DESC
• COMPUTECOMPUTE BY expression

5
SELECT clause key words
P207-208

• DISTINCT
• Eliminates duplicates
• TOP n
• Returns first n rows
• AS
• Creates aliases
• Concatenation

6
Information in the Select List
P209

• INTO clause
• Creates a new table
• FROM clause
• Specifies original source

7
Specifying Columns
P209
USE northwind SELECT employeeid, lastname,
firstname, title FROM employees GO
8
Optional Information in the Select List
P209-211

• WHERE
• Filters the result set
• GROUP BY
• Produces aggregate values
• HAVING filters results of GROUP BY
• ORDER BY
• Sorts the query result
• ASC (default) or DESC

9
Using the WHERE Clause to Specify Rows
P209-211
10
Using Comparison Operators
P209-211
Example 1
USE northwind SELECT lastname, city FROM
employees WHERE country 'USA GO
11
Using String Comparisons
P209-211
USE northwind SELECT companyname FROM
customers WHERE companyname LIKE 'Restaurant GO
12
Using Logical Operators
P209-211
Example 1
USE northwind SELECT productid, productname,
supplierid, unitprice FROM products WHERE
(productname LIKE 'T' OR productid 46) AND
(unitprice 16.00) GO
13
Retrieving a Range of Values
P209-211
Example 1
USE northwind SELECT productname, unitprice FROM
products WHERE unitprice BETWEEN 10 AND 20 GO
14
Using a List of Values as Search Criteria
P209-211
Example 1
USE northwind SELECT companyname, country FROM
suppliers WHERE country IN ('Japan', 'Italy') GO
15
Sorting Data
P209-211
Example 1
USE northwind SELECT productid, productname,
categoryid, unitprice FROM products ORDER BY
categoryid, unitprice DESC GO
16
Eliminating Duplicate Rows
P209-211
Example 1
USE northwind SELECT DISTINCT country FROM
suppliers ORDER BY country GO
17
Changing Column Names
P209-211
USE northwind SELECT firstname AS First,
lastname AS Last ,employeeid AS 'Employee ID'
FROM employeesGO
18
Listing the TOP n Values

• Lists Only the First n Rows of a Result Set
• Specifies the Range of Values in the ORDER BY
  Clause
• Returns Ties if WITH TIES Is Used

19
Using the GROUP BY Clause
USE northwind SELECT productid, orderid
,quantity FROM orderhistGO
USE northwind SELECT productid ,SUM(quantity)
AS total_quantity FROM orderhist GROUP BY
productidGO
Only rows thatsatisfy the WHERE clause are
grouped
USE northwind SELECT productid ,SUM(quantity)
AS total_quantity FROM orderhist WHERE
productid 2 GROUP BY productidGO
20
Using the GROUP BY Clause with the HAVING Clause
21
Advanced Query Techniques
P215

• JOINS
• Subqueries
• Summaring Data

22
JOINS
P216

• Retrieves data from two or more tables
• INNER JOIN
• Returns only those rows where there is an equal
  value

SELECT t.Title, p.Pub_name FROM Publishers AS p
INNER JOIN Titles AS t ON p.Pub_id
t.Pub_id ORDER BY Title ASC
23
P216
Using Inner Joins
USE joindb SELECT buyer_name, sales.buyer_id,
qty FROM buyers INNER JOIN sales ON
buyers.buyer_id sales.buyer_idGO
Example 1
24
JOINS
P217-218

• OUTER JOIN
• Left includes all rows from the table referenced
  to the left of the statement
• Right includes all rows from the table
  referenced to the right of the statement
• Full includes all row from both tables

USE Pubs SELECT a.Au_fname, a.Au_lname,
p.Pub_name FROM Authors a LEFT OUT JOIN
Publishers p ON a.City p.City ORDER BY
p.Pub_name ASC, a.Au_Lname ASC, a.Au_fname ASC
25
P217-218
Using Outer Joins
USE joindb SELECT buyer_name, sales.buyer_id,
qty FROM buyers LEFT OUTER JOIN sales ON
buyers.buyer_id sales.buyer_idGO
Example 1
26
Using Cross Joins
Example 1
27
Joining More Than Two Tables
SELECT buyer_name, prod_name, qty FROM buyers
INNER JOIN sales ON buyers.buyer_id
sales.buyer_id INNER JOIN produce ON
sales.prod_id produce.prod_idGO
Example 1
28
Joining a Table to Itself
Example 3
29
Subqueries
P218-219

• A SELECT statement nested inside another
  statement

USE Northwind SELECT ProductName FROM
Products WHERE UnitPrice (SELECT UnitPrice
FROM Products WHERE ProductName Sir
Rodneys Scones)
30
Summarizing Data
P221-222

• CUBE
• Generates a multi-dimensional cube result set
• WITH ROLLUP result set contains totals and
  subtotals

USE Pubs SELECT SUBSTRING (Title, 1, 65) AS
Title, SUM(qty) AS Quantity FROM Sales INNER
JOIN Titles ON Sales.Title_id
Titles.Title_id GROUP BY Title WITH CUBE ORDER BY
Title
31
Using the GROUP BY Clause with the ROLLUP Operator
32
Using the GROUP BY Clause with the CUBE Operator
33
Using the COMPUTE and COMPUTE BY Clauses
COMPUTE BY
COMPUTE
USE northwind SELECT productid, orderid, quantity
FROM orderhist ORDER BY productid, orderid
COMPUTE SUM(quantity) BY productid COMPUTE
SUM(quantity)GO
USE northwind SELECT productid, orderid
,quantity FROM orderhist ORDER BY productid,
orderid COMPUTE SUM(quantity)GO
34
Modifying Data in a SQL Server Database
P229

• Inserting Data
• Modifying Data
• Deleting Data

35
Inserting Data
P230-231

• INSERT statement
• Adds one or more new rows

USE Pubs INSERT INTO NewBooks (BookTitle,
PubCity) VALUES (Life Without Fear, Chicago)
36
Inserting Data
P232

• SELECTINTO statement
• Create a new table and populates it
• WRITETEXT
• Non-logged interactive updating
• Bulk Copy
• BCP, DTS, etc

37
Inserting a Row of Data by Values

• Must Adhere to Destination Constraints or the
  INSERT Transaction Fails
• Use a Column List to Specify Destination Columns
• Specify a Corresponding List of Values

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', '[phone]' ,'(604)
555-7293') GO
38
Using the INSERTSELECT Statement

• All Rows That Satisfy the SELECT Statement Are
  Inserted
• Verify That the Table That Receives New Row
  Exists
• Ensure That Data Types Are Compatible
• Determine Whether Default Values Exist or Whether
  Null Values Are Allowed

USE northwind INSERT customers SELECT
substring(firstname, 1, 3) substring
(lastname, 1, 2) ,lastname, firstname,
title, address, city ,region, postalcode,
country, homephone, NULL FROM employees GO
39
Creating a Table Using the SELECT INTO Statement

• Use to Create a Table and Insert Rows into the
  Table in a Single Operation
• Create a Local or Global Temporary Table
• Set the select into/bulkcopy Database Option ON
  in Order to Create a Permanent Table
• Create Column Alias or Specify Column Names in
  the Select List for New Table

USE northwind SELECT productname AS products
,unitprice AS price ,(unitprice 1.1) AS
tax INTO pricetable FROM products GO
40
Inserting Data by Using Column Defaults

• 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) GO
41
Modifying Data
P232-233

• UPDATE statement
• Uses SET, WHERE, and FROM
• Using APIs and Cursors
• UPDATETEXT statement

42
Modifying Data
P232-234

• UPDATE statement
• Uses SET, WHERE, and FROM
• Using APIs and Cursors
• WHERE CURRENT OF
• UPDATETEXT statement

USE Pubs UPDATE NewBooks SET Price Price 1.1
43
Deleting Data
P235-236

• DELETE statement
• Removes one or more rows
• APIs and Cursors
• TRUNCATE Table statement

USE Pubs DELETE NewBooks FROM Titles WHERE
NewBooks.BookTitle Titles.Title AND
Titles.Royalty 10
44
Using the DELETE Statement

• The DELECT Statement Removes One or More Rows in
  a Table Unless You Use a WHERE Clause
• Each Deleted Row Is Logged in the Transaction Log

USE northwindDELETE orders WHERE
DATEDIFF(MONTH, shippeddate, GETDATE()) 6 GO
45
Using TRUNCATE TABLE Statement

• The TRUNCATE TABLE Statement Deletes All Rows in
  a Table
• SQL Server Retains Table Structure and Associated
  Objects
• Only Deallocation of Data Pages Is Logged in the
  Transaction Log

USE northwind TRUNCATE TABLE orders GO
46
Deleting Rows Based on Other Tables

• Using an Additional FROM Clause
• First FROM clause indicates table to modify
• Second FROM clause specifies restricting criteria
  for the DELETE statement
• Specifying Conditions in the WHERE Clause
• Subqueries determine which rows to delete

47
Review

• Accessing Data in a SQL Server Database
• Using Advanced Query Techniques to Access Data
• Modifying Data in a SQL Server Database

48
Lab Accessing and Modifying Data
49
Lab Accessing and Modifying Data

• Exercise 1
• Pages 211-214 Using SELCT Statements
• Exercise 2
• Pages 223-228 Using Advanced Query Techniques
• Exercise 3
• Pages 236-239 Modifying Data