Chapter 5. Advanced Data Retrieval and Modification


O BJECTIVES

In Chapter 4, we learned the basics of using the SELECT , INSERT , UPDATE , and DELETE statements. This chapter covers advanced techniques for writing queries to retrieve complex and summary data, along with how to import data into SQL Server from a variety of different systems and formats. Many complicated topics are covered in this chapter that are sure to provoke a couple of exam questions, especially the bits about GROUP BY and COMPUTE BY . Much of the query writing skills you learn in this chapter will also be really handy when you start looking at writing batches and scripts, which is covered in Chapter 6, "Programming SQL Server 2000."

Retrieve and filter data using Transact-SQL.

  • You should know now to use the new and correct join syntax, derived tables, the IN operator, and the UNION operator.

Group Data using Transact-SQL.

  • GROUP BY and HAVING , along with the COMPUTE and COMPUTE BY options of the SELECT statement, are integral parts of this objective, along with understanding the aggregate functions used to group data in SQL Server.

Summarize Data using Transact-SQL.

  • You need to know how to use CUBE and ROLLUP , along with GROUP BY and aggregate functions, to summarize data.

Manipulate heterogeneous data. Methods include linked servers, OPENQUERY , and OPENROWSET .

  • Sometimes the data you want to query isn't all on the same SQL Server, and sometimes it's not even stored in SQL Server at all. This objective says you should know about using SQL Server queries to retrieve data from other data sources.

Extract data in XML format. Considerations include output format and XML schema structure.

  • XML, the eXtensible Markup Language, has gained acceptance as a means to communicate data across platforms, especially over the Internet. This objective requires you to know how to retrieve data from SQL Server to exchange with other systems.

Import and manipulate data using OPENXML .

  • This objective requires you to know how to read data in XML and manipulate it from SQL Server.

Import and export data. Methods include the bulk copy program, the Bulk Insert task, and Data Transformation Services.

  • After you learn how to deal with importing and exporting data from XML, you need to learn how to import and export data from a variety of formats using the appropriate tools. The bulk copy program is used to move data to and from text files of various formats; BULK INSERT has the job of dealing with just data imports. Data Transformation Services provide a graphical interface for moving data between SQL Server and other formats.

O UTLINE

Retrieve and Filter Data Using Transact- SQL

Join Mechanics

Derived Tables

The IN Operator

CASE Expressions

The UNION Operator

Grouping Data with Transact- SQL

Aggregate Functions

Using GROUP BY to Aggregate Data

Using GROUP BY and HAVING to Filter Data

Creating Breaks with COMPUTE and COMPUTE BY

Summarizing Data with Transact- SQL

The CUBE Operator

Summarizing Data with ROLLUP

The GROUPING Keyword

When to Use CUBE and ROLLUP

Manipulating Heterogeneous Data with OPENQUERY, OPENROWSET, and Linked Servers

A Brief Discussion on OLE-DB

Using OPENROWSET

Creating a Linked Server

Using Linked Servers

Extracting Data into XML

Automatic XML Formatting

Minimal Formatting with the RAW Option

Explicitly Creating an XML Format

Using OPENXML to Read XML Data

Preparing and Removing an XML Document

Using OPENXML

Importing and Exporting Data

Importing and Exporting Data with BCP

Using the BULK INSERT Statement

Importing and Exporting Data with Data Transformation Services

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers to Exam Questions

S TUDY S TRATEGIES

  • Practice, practice, practice. This chapter contains many examples on the various objectives covered; use these examples and play with the different types of syntax used. Type the examples into Query Analyzer and run them. Then see whether you can modify them to sort differently or group differently.

  • There is more than one way to write nearly any query. Much of this chapter shows you different approaches to solving the same problem. The factor that establishes the SQL Server gurus from the SQL Server users is understanding the nuances between the methods, and not favoring one method over another, but using each appropriately.

  • If you decide to play with some of your own data, understand that some of the queries covered here are very complex and take a while to run. If your queries are taking a distractingly long time to run, either skip ahead to the chapter on indexing, or find a way to work with a smaller test set.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net