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. Manipulate heterogeneous data. Methods include linked servers, OPENQUERY , and OPENROWSET . 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 . 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. |