Web Hosting Java Blog

324 Part I EXAM PREPARATION APPLY YOUR KNOWLEDGE You need to find all the FeatherIDs for each BirdName. Which of the following queries will do the job? A. select BirdName, FeatherID from birds b inner join birdfeathers bf on b.BirdID = bf.birdid B. select BirdName, FeatherID from birds b left join birdfeathers bf on b.BirdID = bf.birdid C. select BirdName, FeatherID from birds b right join birdfeathers bf on b.BirdID = bf.birdid D. select BirdName, FeatherID from birds b cross join birdfeathers bf on b.BirdID = bf.birdid 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? A. select AssetID, PCType from PCAsset group by AssetID B. select PCType, Count(*) from PCAsset group by PCType with rollup C. Select PCType, count(*) from PCAsset compute by PCType D. Select PCType, count(*) from PCAsset compute group by pctype with rollup 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? A. OPENQUERY() B. OPENROWSET() C. Linked Servers D. OPENXML() 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? A. FOR XML RAW, XMLDATA B. FOR XML AUTO, BINARY BASE64 C. FOR XML EXPLICIT D. FOR XML AUTO 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? A. There are cursors in the Transact-SQL batches that aren t deallocating properly. B. The XML that is being parsed is not well formed. C. The server has a hardware problem. D. The XML strings are not being properly removed after preparation and use.

For high quality website hosting services please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 323 A PPLY YOUR K NO WLEDGE Review Questions 1. What are the restrictions on the SELECT clause if a GROUP BY clause is present? 2. What are the restrictions on an ORDER BY clause if a COMPUTE BY clause is present? 3. What are the requirements to achieve fast bulk copy? 4. What is the difference between WHERE and HAVING in a SELECT statement? 5. Which of the following will generally return the fewest or largest number of rows: CROSS JOIN, LEFT JOIN, RIGHT JOIN, 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? 7. Explain the purpose of the Batch Size argument in BCP. 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? A. select * from vansales order by 1 B. select cnt(*) from VanSales order by 1 C. SELECT count(*) FROM VanSales D. SELECT COUNT(*) FROM VANSALES WHERE TYPE = YEAR 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? A. SELECT COUNT(*) FROM VanSales ORDER BY Type B. SELECT Type, COUNT(*) From VanSales GROUP BY 1 C. SELECT Type, COUNT(*) From VanSales GROUP BY Type ORDER BY Type D. SELECT COUNT(*) from VanSales GROUP BY Type 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 )

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

322 Part I EXAM PREPARATION APPLY YOUR KNO WLEDGE 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 blog 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 blog, and how many blogs 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 blogs 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: 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 -P -S 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.

For high quality website hosting services please check tomcat web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 321 CHAPTER SUMMARY One of the fundamental capabilities SQL Server has is as a reporting platform. You have learned how to write some advanced queries, perform aggregate operations, create groups, and subtotal reports. This all builds on the basic SELECT statement from Chapter 3, filling in the gaps of querying multiple tables and showing correlations. Reporting is crucial to survival, and being able to create aggregated summary reports using COMPUTE and COMPUTE BY, along with the other aggregate functions, enables you to create great reports with useful summaries. In addition, you ve also learned how BCP, DTS, and possibly even XML work with SQL Server to provide a wide range of import and export functionality. These are important tasks because a database without data is just, well, base. The next chapter covers script writing and programming techniques. All the JOIN and aggregate material in this chapter is used in Chapter 6 for writing scripts, and it s also used in Chapter 9 on writing stored procedures. Writing joins is fundamental to understanding SQL Server; it s used by nearly every command, so you ll end up using it a lot in the rest of this blog, and you ll see it on the exam as well. KEY TERMS JOIN derived tables IN operator CASE expression UNION operator aggregate functions GROUP BY operator linked server XML

For high quality java hosting services please check java web hosting website.

320 Part I EXAM PREPARATION ESSENCE OF THE CASE Here are the essential elements in this case: Install SQL Server. Move existing data. Fix the user program and web site. Write a query that can create XML to send to vendors. CASE STUDY: PORTER STEAK COMPANY SCENARIO The Porter Steak Company is a nationwide gourmet foods distributor with offices in Des Moines, Iowa. They ship frozen gourmet steaks, fish, and some side dishes across the country and around the world. Their business is booming thanks to their new web site, so they need to move up to something a little faster than the FoxPro application they are currently using. They d also like to be able to send XML transaction streams to Super Parcel USA, their shipping vendor, and to other vendors. ANALYSIS The first step is to get SQL Server up and running, then use the Import/Export wizard in DTS to copy all the data over into SQL Server. Fix up the web site so it can talk to SQL Server via ODBC instead of using FoxPro via ODBC, and the user interface should be ready. Then schedule a query to run periodically to create an XML document that can be sent to vendors.

For high quality java hosting services please check java web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 319 7. Next is the Save, Schedule and Replicate Package window, which you can see in Figure 5.9. This window enables you to run the package immediately, and/or schedule it to run periodically. You can also save the package to SQL Server, a Meta Data Services store, a Visual Basic file, or a Structured Storage file. For now, just run the package by clicking on Next. 8. The Summary window, shown in Figure 5.10, shows you all the options you ve chosen for your export. Make sure everything is correct and click on Finish. 9. Then the Executing Package window appears, and it shows you what s going on. It counts rows up to 18 and then pops up a window telling you it s all done, as shown in Figure 5.11. FIGURE 5.9 Save packages or even replicate them to other servers. The only really tricky part of the entire wizard is the transformations. DTS enables you to write transformations in VBScript that can make simple changes to data, such as formatting or localizing. By clicking on the Transform window, you can go into the transformation and change the VBScript so it changes the data format. That s all there is for import and export. You ve now finished quite a long chapter that covers everything from complex queries to the DTS Import/Export Wizard. Next up is a lesson on scripting, so you can find out what that @ was doing in the section on OPENXML. FIGURE 5.10 The summary screen for the wizard. Just click Finish to create your text file. FIGURE 5.11 The window shows you the number of rows exported, where the rows were exported from, and shows completion status.

For reliable and cheap web hosting services please check tomcat web hosting website.

318 Part I EXAM PREPARATION FIGURE 5.6 The window you can use to specify where to put your exported data. 4. Next is the Choose a Destination window, shown in Figure 5.6. This looks very similar to the Choose a Data Source window. Just choose the destination source, which should be a text file. For a file name, use C:DTSTEST.TXT. 5. Next is the Specify Table Copy or Query window, which is shown in Figure 5.7. If you choose Copy Table(s) and Views from the Source Database, you can copy an entire table; if you choose Use a Query to Specify the Data to Transfer, you ll get to enter a query to run. If you chose SQL Server as the source and destination, you can choose the third option, which is to copy an entire database or at least a subset of database objects. Click on Next. 6. Almost done! Now you just need to tell the wizard specifically what you want to copy and what format you want it to land in, using the Select Destination File Format window, shown in Figure 5.8. Choose the [pubs].[dbo].[titles] table as the source. Do a delimited file, with a tab delimiter and no text qualifier. Also, check the First Row Has Column Names check box. This puts the column names in the first row, which will be handy when you copy the table back in later. This is also where you can specify a transformation to use, which is discussed in the text. Click on Next after you have the column delimiter set to Tab and the First Row Has Column Names check box is checked. FIGURE 5.7 Where you tell the wizard what it s going to copy: a table or a query. FIGURE 5.8 Window used to specify the file type, delimiters, and what table is going to be copied.

For high quality jboss hosting services please check jboss web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 317 This performs a standard native-mode insert of a file created earlier using BCP. The DATAFILETYPE= native tells SQL Server that the file is stored in native mode. Command line really got you down? Tired of all of this typing stuff? Take a look now at how to import and export data using the graphical tools. Importing and Exporting Data with Data Transformation Services SQL Server 2000 provides a great tool that imports and exports data for you called the Data Transformation Services (DTS) Import/Export Wizard. This tool uses SQL Server DTS to copy data into and out of SQL Server using nice, easy-to-understand graphical tools. Step by Step 5.2 provides an explanation of how to copy data out of SQL Server. STEP BY STEP 5.2 Importing Data with the Import and Export Application 1. Start the Import and Export application by going to Start, clicking on Microsoft SQL Server, and then clicking on Import and Export Data. The DTS Import/Export Wizard opening screen appears as shown in Figure 5.4. Click the Next button. FIGURE 5.4 The startup screen for the Import/Export wizard. 2. Next up is the Choose a Data Source window, shown in Figure 5.5. This is where you get to choose where the data will be coming from for your copies. Click the drop-down box labeled Data Source and choose a data source type. For this example, choose Microsoft OLE DB Provider for SQL Server, but notice you can choose a lot of different data sources. 3. After you choose the data source, you need to pick a server. In the example shown in Figure 5.5, the local server is shown, with Windows Authentication. Change the database to Pubs and click the Next button. FIGURE 5.5 The window where you can choose from which data source to read data.

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

316 Part I EXAM PREPARATION TABLE 5.6 continued OPTIONS FOR THE BULK INSERT COMMAND Option Description CODEPAGE Specifies which code page to use: ACP, which is the standard Windows code page; OEM, which is the default and contains all the regional characters the server can display; and RAW, which specifies that no code page translation should happen. You can also specify a code page name. DATAFILETYPE This specifies the type of data in the file. There are four options. The char option specifies that normal characters are used. The native option specifies that the data types used in the tables should be used. The widechar option specifies that the file is Unicode, and widenative specifies that all the results should be Unicode. FIELDTERMINATOR Specifies the field terminator, just like -t in BCP. FIRSTROW Same as -F in BCP. FIRE_TRIGGERS Same as the FIRE_TRIGGERS hint in BCP. FORMATFILE Path to the format file, same as -f in BCP. KEEPIDENTITY Same as -E in BCP. KEEPNULLS Same as -k option in BCP. KILOBYTES_PER_BATCH Same as KILOBYTES_PER_BATCH hint in BCP. LASTROW Same as -L in BCP. MAXERRORS Same as -m in BCP. ORDER Same as ORDER hint in BCP. ROWS_PER_BATCH Same as ROWS_PER_BATCH hint in BCP. ROWTERMINATOR Same as -r in BCP. TABLOCK Same as TABLOCK hint in BCP. Here s an example, using the same bulk copy operation you used before. Remember, this runs in Query Analyzer, not on the command line: BULK INSERT Sales FROM C:salesnative.dat WITH ( DATAFILETYPE = native )

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 315 This is a pretty powerful tool, but it s kind of difficult to use when you need to just read a file from within SQL Server as part of a script or scheduled job. It sure would be nice if there were a T-SQL equivalent. Using the BULK INSERT Statement The BULK INSERT statement is a lot like BCP, but inside T-SQL. It uses most of the same options, but it doesn t need to know which server to use or what security to use because you use it from within T-SQL, so it runs on that server with the security context with which you logged in. The basic syntax goes something like this: BULK INSERT table_name FROM data file WITH ( operational and format options> The table name is a table name or three-part name to use. You can actually use this statement to bulk copy into views as well as tables, which is a handy option. The data file is a data file as read from the server. Keep in mind that if you specify a data file like c:myfile while talking to a remote server, it s going to try and read data from the C: drive on the server, not the one on your workstation. Table 5.6 provides a list of all the operational and format parameters you can use. As mentioned, they re all fairly similar to the ones used by BCP. TABLE 5.6 OPTIONS FOR THE BULK INSERT COMMAND Option Description BATCHSIZE Same as BCP option -b. CHECK_CONSTRAINTS Same as using the CHECK CONSTRAINTS hint in BCP. continues

For reliable and cheap web hosting services please check javaweb hosting website.