Apply Your Knowledge

Exercises

5.1 Writing Queries with Joins

This exercise demonstrates how to create a query with Query Analyzer to pull information from several tables in the Pubs database.

Estimated Time: 5 minutes.

1. Open SQL Server Query Analyzer, and log into a SQL Server.

2. Switch to the Pubs database using the Database drop-down in the Query window.

3. Find all the authors in the database with this query: " SELECT * from Authors ". Notice there is a field called au_id .

4. Find all the book titles in the database with this query: " SELECT * FROM Titles ". Notice there is a field called title_id .

5. To join the tables, you also need to use the TitleAuthor table. To find the structure of this table, use this query: " SELECT * FROM TitleAuthor ". Notice that this table has both an au_id and a title_id field.

6. Use the following query to join the three tables together and find the author names and titles:

    SELECT au_fname, au_lname, title FROM Authors A      INNER JOIN TitleAuthor TA           ON TA.au_id = A.au_id      INNER JOIN Titles T           ON TA.title_id = T.title_id 

5.2 Writing Queries with GROUP BY

This exercise demonstrates how to create a query with a GROUP BY and an aggregate function. This query finds which authors have written more than one book, and how many books they have written.

Estimated Time: 5 minutes.

1. Open SQL Server Query Analyzer, and log into a SQL Server.

2. Switch to the Pubs database using the Database drop-down in the Query window.

3. Use the following query to join the two tables together and find the author names and the number of books written.

    SELECT au_fname, au_lname, count(*) FROM Authors A      INNER JOIN TitleAuthor TA           ON TA.au_id = A.au_id GROUP BY au_fname, au_lname HAVING count(*) > 1 

5.3 Exporting Data with BCP

This exercise demonstrates how to export data from the Pubs database using BCP and the QUERYOUT option.

Estimated Time: 5 minutes.

1. Open a command prompt by clicking on Start, choosing Run, and typing cmd .

2. Type in the following at the command prompt, all on one line:

   [View full width]

    
    [View full width] 
    bcp "SELECT * FROM pubs..authors a INNER JOIN pubs..titleauthor ta on ta.au_id = a.au_id  INNER JOIN pubs..titles t on t.title_id = ta.title_id" QUERYOUT TitlesAuthors.txt -c  -U<user> -P<password> -S<server> 

3. Be sure to substitute a valid username, password, and servername where appropriate in Step 2.

4. After running the BCP, use the command type titleauthors.txt to view the results.

Review Questions

1:	What are the restrictions on the SELECT clause if a GROUP BY clause is present?
A1:	If there is a GROUP BY , the SELECT must contain all of the columns contained in the GROUP BY , and the columns can't be part of an aggregate function.
2:	What are the restrictions on an ORDER BY clause if a COMPUTE BY clause is present?
A2:	The ORDER BY must be present if the COMPUTE BY is there, and it must contain at least all the columns in the COMPUTE BY in the same order. The ORDER BY can have additional columns, however.
3:	What are the requirements to achieve fast bulk copy?
A3:	To achieve fast bulk copy, you have to have the recovery mode properly set, no triggers on the target table, no replication of the target table, and either no indexes or an empty target.
4:	What is the difference between WHERE and HAVING in a SELECT statement?
A4:	If there is no GROUP BY , there is no effective difference between WHERE and HAVING in a SELECT statement. If there is a GROUP BY , then HAVING can be used to filter output from aggregate functions, whereas the WHERE clause even filters what gets processed by the aggregate function.
5:	Which of the following will generally return the fewest or largest number of rows: CROSS JOIN , LEFT JOIN , RIGHT JOIN , INNER JOIN ?
A5:	The most rows will be returned by the CROSS JOIN , which returns the number of rows in the first table times the number of rows in the second table. The fewest are returned by an INNER JOIN .
6:	What is the difference between a character-mode BCP and a native-mode BCP in terms of making the file readable by other systems? How about in terms of speed of import and export?
A6:	Character-mode BCP puts out files that are easy to read by other systems, but the files tend to be larger and slow the system.
7:	Explain the purpose of the Batch Size argument in BCP.
A7:	The Batch Size argument determines the number of rows that are to be copied in each transaction. Smaller batch sizes tend to take more SQL Server time at the benefit of using less transaction log space.

Exam Questions

1:	The EconoVan Corporation is trying to figure out how many vans they have sold. They currently have a table that contains a sales record for each van by type that was created with this script: create table VanSales ( VIN varchar(50), SalePrice float, Cost float, Type int, SaleDate datetime ) Which of the following queries will show them the number of vans they have sold? select * from vansales order by 1 select cnt(*) from VanSales order by 1 SELECT count(*) FROM VanSales SELECT COUNT(*) FROM VANSALES WHERE TYPE = "YEAR"
A1:	C. The other close answer is A, but that shows you all the records, and not necessarily the count. Option B is wrong because CNT() is not an aggregate function. COUNT(*) is an aggregate function, covered in the aptly named "Aggregate Functions" section.
2:	The EconoVan Corporation is trying to figure out how many vans of each type they have sold. They currently have a table that contains a sales record for each van by type that was created with this script: create table VanSales ( VIN varchar(50), SalePrice float, Cost float, Type int, SaleDate datetime ) Which of the following queries will show them the number of vans they have sold by type? SELECT COUNT(*) FROM VanSales ORDER BY Type SELECT Type, COUNT(*) From VanSales GROUP BY 1 SELECT Type, COUNT(*) From VanSales GROUP BY Type ORDER BY Type SELECT COUNT(*) from VanSales GROUP BY Type
A2:	C. Option A would work if you could find someone who wanted to sit and count through the output. Options B and D are invalid syntax: B because you have to name fields in a GROUP BY , and D because the Type field isn't in the SELECT list. There's a whole section on using the GROUP BY syntax called "Using GROUP BY to Aggregate Data" in this chapter.
3:	You have two tables that were created like this: create table birds ( BirdID int IDENTITY(1,1), BirdName varchar(30) ) create table BirdFeathers ( BirdID int, FeatherID int ) You need to find all the FeatherID s for each BirdName . Which of the following queries will do the job? select BirdName, FeatherID from birds b inner join birdfeathers bf on b.BirdID = bf.birdid select BirdName, FeatherID from birds b left join birdfeathers bf on b.BirdID = bf.birdid select BirdName, FeatherID from birds b right join birdfeathers bf on b.BirdID = bf.birdid select BirdName, FeatherID from birds b cross join birdfeathers bf on b.BirdID = bf.birdid
A3:	A. They're all valid syntax except for the ON clause in the CROSS JOIN (option D), but only option A returns the correct rowset. This is all about structuring joins, which is covered in the "Join Mechanics" section.
4:	You're doing asset management for a small business. You need to figure out how many laptops, how many desktops, and the total number of computers in the company. Here's the table where the data is stored: create table PCAsset ( AssetID int, PCType char(1), -- L or D, Laptop or Desktop AcquireDate datetime ) Which of the following queries will do the job? select AssetID, PCType from PCAsset group by AssetID select PCType, Count(*) from PCAsset group by PCType with rollup Select PCType, count(*) from PCAsset compute by PCType Select PCType, count(*) from PCAsset compute group by pctype with rollup
A4:	B. Only B uses correct syntax. Option A doesn't have everything in the select list it needs, and doesn't count anything, Option C should read COMPUTE COUNT(*) BY PCType and it would work, but it still wouldn't be as efficient as B. Option D is just a mess syntactically. COMPUTE and COMPUTE BY are discussed in the "Creating Breaks with COMPUTE and COMPUTE BY" section.
5:	You need to query some data on a temporary remote server for a one-time report. Which of the following functions is the best to use? OPENQUERY() OPENROWSET() Linked Servers OPENXML()
A5:	B. Either Option A or C would work, too, but they'd involve creating a linked server, and for a one-time report it's more efficient to just use OPENROWSET . There's a whole section on OPENROWSET called "Using OPENROWSET".
6:	You need to send an XML rowset to a parts supplier for your business that produces radio kits. The rowset should contain a parts list for your order for next week. The supplier has provided you with a schema you must use. Which of the following FOR XML options will enable you to conform to his schema? FOR XML RAW, XMLDATA FOR XML AUTO, BINARY BASE64 FOR XML EXPLICIT FOR XML AUTO
A6:	C. To successfully exchange data in a provided schema, you're going to have to dig through the documentation and figure out how to do explicit XML. If you need help on creating XML files from rowsets, check out the section on "Extracting Data into XML".
7:	There's a performance problem on one of your SQL Servers that you use to process XML. After a period of time processing XML data, the server's memory utilization is very high and the server won't allow any more XML statements to be prepared. Which of the following is the most likely cause for the problem? There are cursors in the Transact -SQL batches that aren't deallocating properly. The XML that is being parsed is not well formed . The server has a hardware problem. The XML strings are not being properly removed after preparation and use.
A7:	D. The sp_xml_removedocument stored procedure must be called to free memory by deallocating the memory for the XML strings, and if you lose the handles, the resulting memory leak will eventually cause SQL Server to be unable to process more XML. The section titled "Preparing and Removing an XML Document" goes into great detail about how all this works.
8:	Leeks Unlimited has just acquired another company, the Garlic Crock. They're trying to import the several million rows of data from the Garlic Crock's databases by exporting the data from Garlic Crock's mainframe into text files and then using BCP to bulk copy the data into the Leeks Unlimited database server. The problem is that they keep filling up the transaction log of their database. Which of the following options would not help alleviate the problem? Rather than using BCP, use the same options with BULK INSERT . Use the batch size limiter on the BCP command line. Use the batch size limitation as a bulk copy hint. Make sure that the database's SELECT INTO/BULKCOPY option is turned on.
A8:	A. The BULK INSERT statement with the same parameters will fill up the logs just as fast as BCP. Any of the other options will help, though. There are two great sections"Using the BULK INSERT Statement" and "Importing and Exporting Data with BCP"in this chapter.
9:	After importing all the Garlic Crock data from their mainframe, Leeks Unlimited needs to read a bunch of Garlic Crock's data from their Web server database back ends, which are currently using Microsoft Access. Which of the following tools would be the best for the job? The Bulk Copy Program OPENXML The BULK INSERT command The DTS Import/Export Wizard
A9:	D. The only tool that can directly read Microsoft Access databases and import them is the DTS Import Export wizard. Any of the other tools listed require either exporting the data to text files or exporting it to XML. The DTS Import/ Export wizard is covered in the "Importing and Exporting Data with Data Transformation Services" section.
10:	Peekaboo needs to write a query that will help her understand how many toys are in her toy box. Here's the table she created to track her toys: create table woof ( ToyID int, ToyType char(1), --B for ball, S for squeaky ToyName varchar(15) ) Which of the following queries will give Peekaboo a report complete with subtotals of each toy she has with subtotals for types? select ToyType, Count(*) from woof compute count(*) by toytype select toytype from woof compute count(toytype) by toytype select toyname, toytype from woof order by toytype compute count(ToyName) by Toytype select toyname, toytype from woof with cube
A10:	C. (It took me three tries to get this one right, the first two options are actually things I tried on the way.) Option A is not correct because you can't use COUNT(*) with COMPUTE BY . Option B is not correct because it's missing the ORDER BY . The fourth one is totally fictitious. (Peekaboo is a dog, by the way, and she wandered into the office just in time to get famous.) Yet another question on COMPUTE BY calls for another wonderful referral back to the "Creating Breaks with COMPUTE and COMPUTE BY" section.
11:	Eric has a couple of fairly large tables that handle the food inventory for his snack smuggling operation. He wants to cause a problem for his system administrator, preferably by just using up resources unnecessarily. What's the best way to do this? Cross-join the two tables. Left-join the two tables and union them together. Inner-join the tables and union them together. Right-join the tables and union them together.
A11:	A. Cross-joins consume the most resources because they have the largest rowset, which is the size of table 1 times table 2. None of the other queries can come even close. A quick jump back to the "Join Mechanics" section would be in order if that one tripped you up.
12:	Gordon is trying to get access to a server on the other side of the Black Mesa facility to complete his research. He just got access to the server, and is having problems running even simple queries. What's the problem with this query: select count(*) from LambdaSQL. BadThings.Creepy His colleagues seem to be having no problems. You can't run an aggregate function across a remote server. You should use OPENROWSET for this kind of an operation. You should use OPENXML for this. He left the owner name out of the object specification.
A12:	D. All cross-server activity has to be done with four-part names. The correct name is probably something like LambdaSQL.BadThings.dbo.Creepy . Cross-server joins were all covered in the sections "Creating a Linked Server" and "Using Linked Servers".
13:	Things have been pretty boring lately, so Barney the security guard wants to put some random top secret data on a laptop and then throw it over the wall at the local embassy. He wants to make sure the data is nice, complete, and easy-to-read XML. Which of the following should he use? Use FOR XML AUTO, XMLDATA Use FOR XML RAW Use FOR XML BASE BINARY64 Use FOR XML BASE PORTABLE
A13:	A. The XMLData flag puts the schema, including all the data types, into the XML document for easy viewing. Putting data into XML files is covered by the "Extracting Data into XML" section in this chapter.
14:	Peekaboo is at it again. This time, she just wants a quick list of all of her toy names and types, but she'd like the types to be spelled out with Ball for B and Squeaky for S . How can you make that happen? Here's the table again: create table woof ( ToyID int, ToyType char(1), --B for ball, S for squeaky ToyName varchar(15) ) select ToyType, ToyName from woof select ToyName, case ToyType when 'B' then 'Ball' When 'S' then 'Squeaky' else 'broken' end from woof Select ToyName, case ToyType if 'B' then 'Ball' if 'S' then 'Squeaky' else 'broken' end from woof Select ToyName, case ToyType when 'B' then 'Ball' When 'S' then 'Squeaky' else 'broken' from woof
A14:	B. Option A doesn't fit the requirements; Option C uses if rather than when , and option D is missing an end . They're all about the CASE expression, which is covered in the section titled "CASE Expressions."
15:	Which of the following provides the slowest data throughput for BCP? Native mode Native mode with Unicode support Wide native mode Comma-delimited text
A15:	D. Option A is the fastest , and they get slower as you go down the list. BCP performance is covered in the "Importing and Exporting Data with BCP" section.

Suggested Readings and Resources

• For information about standard deviation and variance, try the Internet Statistics Glossary, located at http://www.animatedsoftware.com/statglos/statglos.htm

• New Riders publishes a book called XML and SQL Server 2000 , the ISBN is 0735711127, and the author is John Griffin.

• SQL Server Books Online, the Overview articles on OPENXML , OPENROWSET , and OPENQUERY .

• SQL Server Books Online, the syntax for sp_addlinkedserver , sp_serveroption , and sp_dropserver .

• SQL Server Books Online, the overview page for BCP.