Exam Prep Questions


1:

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 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 

A1:

Answer: A. They're all valid syntax except for the ON clause in the CROSS JOIN, making option D completely invalid, but only option A returns the correct rowset. This is all about structuring joins, which is covered in the section "Joining Multiple Sets of Data."

2:

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

A2:

Answer: 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 creating XML files from rowsets, check out the section on "Extensible Markup Language (XML)."

3:

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 of the problem?

  • A. Cursors in the Transact-SQL batches 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 properly removed after preparation and use.

A3:

Answer: 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 "Moving Data Using XML" goes into great detail about how all this works.

4:

Leeks Unlimited has just acquired another company, the Garlic Crock. The company is 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 the transaction log of the database keeps filling up. Which of the following options would not help alleviate the problem?

  • A. Rather than using BCP, use the same options with BULK INSERT.

  • B. Use the batch size limiter on the BCP command line.

  • C. Use the batch size limitation as a bulk copy hint.

  • D. Make sure that the SELECT INTO/BULKCOPY option is turned on.

A4:

Answer: 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 is a great section in this chapter to check out: "Mass Movements of Data via BCP."

5:

After importing all the Garlic Crock data from its mainframe, Leeks Unlimited needs to read a bunch of Garlic Crock's data from its Web server database back ends, which are currently using Microsoft Access. Which of the following tools would be the best for the job?

  • A. The Bulk Copy Program

  • B. OPENXML

  • C. The BULK INSERT command

  • D. The DTS Import/Export Wizard

A5:

Answer: D. The only tool that can directly read Microsoft Access databases and import them is the DTS Import/Export Wizard. 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 section "Data Movement with Manipulation."

6:

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?

  • A. Cross-join the two tables.

  • B. Left-join the two tables and union them together.

  • C. Inner-join the tables and union them together.

  • D. Right-join the tables and union them together.

A6:

Answer: 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 section "Joining Multiple Sets of Data" would be in order if that one tripped you up.

7:

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 that the data is nice, complete, and easy-to-read XML. Which of the following should he use?

  • A. FOR XML AUTO, XMLDATA

  • B. FOR XML RAW

  • C. FOR XML BASE BINARY64

  • D. FOR XML BASE PORTABLE

A7:

Answer: 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 section "Moving Data Using XML," earlier in this chapter.

8:

A small manufacturing company has a considerable number of data sources because no standardization has occurred across any platform. One of the database servers has SQL Server installed; the others come from various vendors. For a project you are working on, you need to gather data from the SQL Server and merge it together with data from two other sources. You then need to bring the data into Excel to do some charting. How would you accomplish this task?

  • A. Export the data from the other sources into a comma-delimited file. Then export from SQL Server the data that is to be imported into Excel.

  • B. Export the data from all three sources so that it can be imported into Excel.

  • C. Use DTS to transfer all the data from all sources directly into Excel.

  • D. Use Excel to transfer data from all three sources into a spreadsheet.

A8:

Answer: C. SQL Server is ideal for this situation. Depending on the actual details of the process, this can be performed directly using replication or Data Transformation Services (DTS). Given the complexity of the scenario, it is likelier that DTS would be used because of its limitless flexibility. For more information, consult SQL Server Books Online.

9:

You have been given the assignment of preparing a set of user-friendly queries to produce a series of reports for a building construction supply company. You would like a query that places customers in order by country, state, and then city. Which of the following queries would solve the problem?

  • A.

     SELECT * FROM Customers      ORDER BY Country, State, City 

  • B.

     SELECT CustomerName, City, State, Country      ORDER BY Country, State, City, CustomerName 

  • C.

     SELECT * FROM Customers      ORDER BY City, State, Country 

  • D.

     SELECT CustomerName, City, State, Country      ORDER BY City, State, Country, CustomerName 

  • E.

 SELECT CustomerName, City, State, Country      ORDER BY CustomerName, City, State, Country 

A9:

Answer: A. Assuming that all data from the table is needed, A is correct. It is not necessary to include all columns from a select list when using ORDER. For appropriate use of ORDER BY, see the section "Putting It All in ORDER."

10:

You are working on a database view that will be used by a group of salespeople to query a database for sales statistics. The report that you are debugging is printing duplicates for some states, and in some instances there are three or more duplicates. How would you try to resolve the problem?

  • A. Correct the FROM clause.

  • B. Correct the WHERE clause.

  • C. Correct the TOP clause.

  • D. Correct the ORDER BY clause.

  • E. Correct the DISTINCT clause.

A10:

Answer: E. Appropriate use of the DISTINCT clause could eliminate the recurring data being noted. To diagnose this problem, the correct syntax must be implemented. The following code represents a plausible solution:

 SELECT DISTINCT Country FROM Customers ORDER BY Country 

For further details on the use of DISTINCT, see the section "Selecting Information from the Data."

11:

A local branch of a large hotel chain maintains guest information on a single SQL Server 2000 computer. You are creating an application that will change the contents of a database programmatically through a Visual Basic interface on a local area network. Which technology would you utilize?

  • A. ADO

  • B. RDO

  • C. DAO

  • D. SQL-DMO

  • E. XML

A11:

Answer: A. An XML implementation may be more suited to an active server page Internet application than a LAN application. RDO and DAO represent older technologies that aren't as efficient and versatile as ADO. SQL-DMO is for development of system applications that interact with SQL Server on a non-data level. For more details about any of the technologies, see SQL Server Books Online.



    EXAM CRAM 2 Designing and Implementing Databases with SQL Server 2000 Enterprise
    MCAD/MCSE/MCDBA 70-229 Exam Cram 2: Designing & Implementing Databases w/SQL Server 2000 Enterprise Edition
    ISBN: 0789731061
    EAN: 2147483647
    Year: 2005
    Pages: 154

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