The actual certification exam is 50 questions. To best simulate exam circumstances, you should try to complete the 50 questions in 90 minutes.
| 1: | You are a database developer for your company's SQL Server 2000. The database you are working with supports an Internet-based application to a busy website. The database is extremely large with many tables and indexes. Some of the tables and indexes are accessed frequently, whereas others have been created to support data of a historical nature and are used much less often. The server has two striped disk volumes and two other volumes that have been mirrored. You need to place objects in a location that will provide for the best performance. How would you place the objects? A. Create one filegroup on each of the striped volumes. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups. B. Create one filegroup on each of the striped volumes. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup. C. Create one filegroup on each of the mirrored volumes. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups. D. Create one filegroup on each of the mirrored volumes. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup. E. Create one filegroup on each of the striped volumes. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and all indexes belonging to those tables on different filegroups. F. Create one filegroup on each of the striped volumes. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.
|
| 2: | You are a database developer who works for a national company called Argo Alimentum. The company is responsible for database design and hosting for independent organizations. You are creating a database that will store statistical information for various amateur hockey teams. The project is being backed by one of the largest semi-pro hockey leagues in the nation. The information contained within the database will be used by the teams in the semi-pro league as they determine the value of the variety of players available to their draft system. Each of the professional teams uses the data internally. Each team has its own application that uses a variety of data structures and databases provided by many vendors. You would like to prepare the data in a singular framework that can be used by all concerned. How would you prepare the data? A. Use the sp_makewebtask system stored procedure to generate HTML data. B. Create DTS packages that export the data into tab-delimited text files. C. Back up the data, store the backup to CD, and distribute the CD to all teams. D. Extract the data using SELECT statements with the FOR XML clause. E. Configure replication. The hosted database publishes data to the subscribing teams.
|
| 3: | You are creating a database that will store sales order information. Orders will be entered in a client/server application with your site and over the Internet via an ASP.NET application. Each time a new order is placed into the system, a unique order number must be assigned that is in ascending sequence. It is expected that the system will generate in excess of 100,000 orders weekly. You create a new table named Orders and a related table named Order Details. How should you create the Order Number to provide the required functionality? A. Use a UniqueIdentifier data type. B. Use an Integer data type, and set the IDENTITY property for the column. C. Use a TimeStamp data type. Create a user-defined function that sets the order number. D. Create a table to hold key values and assign order numbers from this table. E. Have the front-end application assign sequential integer order numbers.
|
| 4: | You are creating a one-time report to supply the office staff with a revenue breakdown. The data source for the report contains cryptic column headings that cover several categories. You must provide the report in a manner the users can easily understand. Which of the following would be the best solution? Select 2; each answer represents half of the correct solution. A. Provide friendly aliases for the table names. B. Provide friendly aliases for the column names. C. Create a VIEW with corresponding definition. D. Create a corresponding DEFAULT definition. E. Execute a corresponding query from the Analyzer. F. Create a front-end program to execute the required query.
|
| 5: | You have implemented a database for an international research organization and are performing some test queries against the tables within the database. You have some date fields within the database that store only date information. No time information is maintained within these columns. You would like to have a listing of the data from only the year 2000. Which of the following queries represents the best solution to the problem? A. SELECT * FROM RTab WHERE RDate BETWEEN '01/01/2000' AND '01/01/2001'
B. SELECT * FROM RTab WHERE RDate BETWEEN '12/31/1999' AND '12/31/2000'
C. SELECT * FROM RTab WHERE RDate BETWEEN '12/31/1999' AND '01/01/2001'
D. SELECT * FROM RTab WHERE RDate BETWEEN '01/01/2000' AND '12/31/2000'
E SELECT * FROM RTab WHERE RDate BETWEEN '12/31/2000' AND '01/01/2000'
|
| 6: | You have entered a query using a TOP function to limit the number of records being viewed to five. When you see the results of the query, the dates being viewed are not the first five in the data. What is the most likely source of the problem? A. The resultset has not been grouped. B. The data contains NULL values. C. There is an incorrect ORDER. D. Table aliases were used. E. Schema binding has been applied.
|
| 7: | You have been chosen by your development team to provide a set of queries that will print various reports from the Customer table. After opening the Query Analyzer, you discover that the test database and table you just created do not appear. Which of the following is likely to solve the problem? A. There was an error in the script you used to create the objects, and you need to correct the error and rerun the scripts. B. The Query Analyzer needs to be refreshed, thus giving it access to all objects in the system. C. You need to create the database and table from the Enterprise Manager to ensure that temporary objects are not used. D. You must restart the SQL Server service so that it has access to all newly created objects.
|
| 8: | You are developing a query that will look for invalid entries in a table before implementing a new check constraint. The constraint you are implementing will enforce data entry into the gender column of the table. Which of the following queries will seek out records that have no value or are not male or female? (Select all that apply.) A. SELECT * FROM Employee WHERE Gndr = NULL or Gndr <> 'M' or Gndr <> 'F' or Gndr <> 'm' or Gndr <> 'f'
B. SELECT * FROM Employee WHERE Gndr = NULL and Gndr <> 'M' and Gndr <> 'F' and Gndr <> 'm' and Gndr <> 'f'
C. SELECT * FROM Employee WHERE Gndr IS NULL and Gndr <> 'M' and Gndr <> 'F' and Gndr <> 'm' and Gndr <> 'f'
D. SELECT * FROM Employee WHERE Gndr IS NULL or Gndr <> 'M' or Gndr <> 'F' or Gndr <> 'm' or Gndr <> 'f'
E SELECT * FROM Employee WHERE Gndr IS NULL or (Gndr <> 'M' and Gndr <> 'F' and Gndr <> 'm' and Gndr <> 'f')
F SELECT * FROM Employee WHERE Gndr IS NULL or Gndr NOT IN ('M', 'F', 'm', 'f')
|
| 9: | A local manufacturing company uses a SQL Server to receive statistical information from various points on an assembly line. The information is gathered into a table called Production. Date information is maintained in a standard datetime column called Pdate. You would like to prepare a query that would list the production information from the preceding day. Which of the following queries solves the problem? A. SELECT * FROM Production WHERE Pdate = GetDate() - 1
B. SELECT * FROM Production WHERE Pdate BETWEEN = GetDate() - 2 AND GetDate()
C. SELECT * FROM Production WHERE datediff(dd, PDate, getdate()) = 1
D. SELECT * FROM Production WHERE datediff(dd, getdate(), PDate) = 1
|
| 10: | You are preparing to move a test database that you have been working on for the past several months over onto a production system. You are planning to go live with the new database sometime over the next few days. All the current data will be removed and replaced with production information when the system does go live. You are performing some final tests when you notice that the column that is going to be used as the ROWGUIDCOL does not have any values. The ID is to be generated by the system and afterward used as a permanent value. How can you ensure that values are placed into this column as data is entered in the production system? A. Correct the current data by providing the missing values and then add a constraint so that this doesn't happen again. B. Correct the current data by providing the missing values and then add a NEWID() as a default for the column. C. Correct the current data by providing the missing values and provide a formula to generate the value of the column. D. Correct the current values by using an UPDATE operation and the NEWID() function and then add a NEWID() as a default for the column. E. Empty the current table into a temporary table, add a default as NEWID(), and load the data back in using an INSERT INTO operation, omitting the ID column.
|
| 11: | A local car dealership maintains a list of the current inventory on a single SQL Server. The machine also takes on some other networking roles for name resolution. Data is going to be moved off the existing machine to create a dedicated database server. You would like to execute a query that copies all the data to a test database. The test database has been created but no tables exist. Which of the following will solve the problem? A. INSERT INTO Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles
B. SELECT Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles
C. INSERT Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles
D. SELECT INTO Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles
E INSERT INTO Inventory.dbo.Automobiles SELECT * FROM Test.dbo.Automobiles
|
| 12: | An accounting system that has been recording company financial information for the past three years is being upgraded and having some additional columns added to the structure of several of the existing tables. You need to ensure that all existing data remains intact during these operations. How is this best accommodated? (Select two.) A. Set the database files to read-only. B. Set the table properties to read-only. C. The addition of columns shouldn't affect the database. D. Set permissions on the database to prevent malicious updates. E. Set column-level permissions to prevent updates to existing data.
|
| 13: | You administer the database server for a large lumber and building-materials supplier. You want to query the materials used by a single site. Which of the following queries would suit your needs? A. SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Site
B. SELECT Materials, Weight, Quantity FROM Inventory WHERE Site = 4
C. SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Quantity
D. SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Site, Quantity
E SELECT Materials, Weight, Quantity FROM Inventory WHERE Materials = "Drywall"
|
| 14: | Which of the following provides the slowest data throughput for BCP? |
| 15: | In preparation for a major system upgrade, multiple data changes are going to be made on a system. You would like to implement various changes without disturbing any of the existing data. Which of the following operations do not affect any existing data values? (Select all that apply.) |
| 16: | You work for a large manufacturing organization that maintains a large production database system on a single SQL Server 2000 machine. In attempting to enter a query to add a record to a table, you find that it is not possible. Which of the following is not a likely cause for the error? A. Data doesn't meet constraint. B. Referential integrity. C. Database is read-only. D. Permissions. E. Other applications are locking data. F. SQL Server Agent is not started.
|
| 17: | You are a database developer for a small private school. Currently the school maintains all records of students, instructors, courses, and classroom assignments on paper. The school would like to keep records by developing a database. You begin with the following table design: Classroom ClassroomID ClassRoomNumber ClassTime Student StudentID LastName FirstName HomePhone WorkPhone Courses CourseID CourseNumber CourseTitle Description RequiredName InstructorName OfficePhone
You want to promote quick response times for queries and minimize redundant data. What should you do? A. Create a new table called Instructors. Include an InstructorID column, and the instructor's other information. Add an InstructorID column to the Courses table. B. Move all columns from the Classroom table to the Courses table. C. Base the PRIMARY KEY constraint for the Courses table on the CourseID and CourseTitle. D. Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.
|
| 18: | You are a database developer for a large international mail-order company called M and M and M. The company has two SQL Server 2000 computers named CANDY and CANDO. CANDY handles all the company's online transaction processing, and CANDO stores mostly historical sales data that is accessed only periodically. CANDO has been added as a linked server to CANDY. You are asked to create a list of customers who have purchased fudge bars. Lists will be generated on a recurring basis. Fudge bars are stored in the database with a category ID of 37. You must retrieve this information from a table named CandySalesHistory. This table is located in the PastSales database, which resides on CANDO. You need to execute this query from CANDY. Which script should you use? A. EXEC sp_addlinkedserver 'CANDO', 'SQL Server' GO SELECT CustomerID FROM CANDO.Sales.dbo.CandySalesHistory WHERE CategoryID = 37
B. SELECT CusomerID FROM OPENROWSET('SQLOLEDB','CANDO';' ', 'SELECT CustomerID FROM Sales.dbo.CandySalesHistory WHERE CategoryID = 37')
C. SELECT CustomerID FROM CANDO.Sales.dbo.CandySalesHistory WHERE CategoryID = 37
D. EXEC sp_addserver 'CANDO' GO SELECT CustomerID FROM CANDO.Sales.dbo.CandySalesHistory WHERE CategoryID = 37
|
| 19: | You are designing a database that will serve as a back end for several large websites. The websites themselves will communicate with each other and pass data back and forth using XML. You would like to control the data displayed on the user browser based on interactions with the user. In many cases columns and rows need to be eliminated based on the criteria supplied. You would like to minimize round-trips to the server for data exchange purposes. What technology is the best to apply? A. Use a user-defined function with SCHEMABINDING set to the XML recordsets. B. Create an indexed view of the XML recordset specifying only the columns needed, and supply a WHERE condition based on the rows selected. C. Create standard views of SQL Server data and export the requested data using XML. D. Send data requests and updates directly from the client machine to the SQL Server using FOR XML and OPENXML options. E. Use HTML and an XML schema to provide the necessary view of the data.
|
| 20: | You have a SQL Server database implemented in a library that stores library-specific information. The description of each title that is present in the library is stored in the Titles table. The Description column is implemented as data type text. A full-text index exists for all columns in the Titles table. You want to search for a title that includes the phrase Programming SQL. Which query should you execute to return the required results? A. SELECT * FROM Titles WHERE Description like '_Programming SQL_%'
B. SELECT * FROM Titles WHERE Description like '%Programming SQL%'
C. SELECT * FROM Titles WHERE CONTAINS(Titles, Description,'Programming SQL')
D. SELECT * FROM Titles WHERE FREETEXT(Description, 'Programming SQL')
|
| 21: | You are a database developer responsible for overseeing a CLIENT database system. The application currently in the design stage will be a multi-tier application with an Internet-facing front end. The application will also be used internally for employees to manage client accounts. The developer needs to retrieve client names from the CLIENT database to populate a list box in the application. You would like to limit the data retrieval to 10 rows at a time. Which technique is appropriate? A. Create a stored procedure to retrieve data called from the client. B. Use a server-side cursor initiated by the client to retrieve the data. C. Retrieve all the data at once and then load the data into the list box 10 at a time. D. Use a server-side cursor initiated by the server to retrieve the data.
|
| 22: | You have 50,000 records in a database file, and you know you want to add 25,000 records in the next month. What FILLFACTOR should you specify to maximize performance? It should be mentioned that a new index is to be created and that you will change your FILLFACTOR; you also want fast INPUT into the tables. A. 0 (default setting) B. 100 C. 70 D. 50
|
| 23: | Which of the following statements is true when full-text indexing is used? (Choose all that apply.) A. The column that you plan on indexing must not contain text if at all possible. B. The column that you index has to be made up of text data. C. Full-text indexes are not automatically updated and they reside in a storage space called a full-text catalog. D. There can be up to 200 catalogs and not more in a single server. E. Full-text searches are best performed on columns that hold integer values.
|
| 24: | You are a database developer for a high-volume online transaction processing database. All tables are indexed. The heavily accessed tables have at least one index. Two RAID arrays on the database server will be used to contain the data. You want to place the tables and indexes to ensure optimal I/O performance. How should the files be configured? A. Place frequently joined tables on the same array. Place heavily accessed tables and all indexes belonging to those tables on different arrays. B. Place frequently joined tables on the same array. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same array. C. Place frequently joined tables on different arrays. Place heavily accessed tables and the nonclustered indexes belonging to those tables on different arrays. D. Place frequently joined tables on different arrays. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same array.
|
| 25: | What is true about the WITH SCHEMABINDING argument of the CREATE INDEX statement? (Choose all that apply.) A. It must be specified to create an indexed view. B. It allows a view's name and other properties to be changed dynamically. C. It prevents the dropping and altering of tables participating in the view. D. It has to be specified only when you are creating a unique clustered index on text data.
|
| 26: | On a low-volume application you have a few sporadic periods during which volume is high, and the system needs to be able to carry the spikes of volume and still provide reasonable response times. You must try to isolate the cause of data locking and slow query response times. How would this be done? A. Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. B. Query the sysprocesses and sysobjects to find deadlocked resources. C. Set a shorter lock timeout for the processes that are accessing the deadlock resources. D. Add clustered indexes on the primary keys of all the tables. E. Use the sp_monitor system stored procedure to identify which processes are being affected by the increased query response times. F. Set a less restrictive transaction isolation level.
|
| 27: | You are a database developer for a computer manufacturing company named Optima. For a limited time, Optima ships free software with the purchase of any desktop computer or notebook. The software titles, descriptions, values, and other information are located in the Software table. You configure full-text indexing on the Software_Description column that contains more than 2,000 rows and is located in the Software table. After executing a search using FREETEXT for the word Windows, you notice an empty resultset in the results pane. Why is this happening? A. The catalog is not populated. B. FREETEXT is not a valid keyword recognized by SQL Server 2000. C. FREETEXT is not allowed for columns that contain 2,000 or more rows. D. You didn't create a nonclustered index.
|
| 28: | Which statements show the maximum number of clustered and nonclustered indexes allowed in a single table? A. Clustered 249 and nonclustered 149 B. Clustered 249 and nonclustered 249 C. Clustered 1 and nonclustered 249 D. Clustered 1 and nonclustered unlimited
|
| 29: | You are building a new database for a company with 10 departments. Each department contains multiple employees. In addition, each employee might work for several departments. How should you logically model the relationship between the department entity and the employee entity? A. Create a mandatory one-to-many relationship between department and employee. B. Create an optional one-to-many relationship between department and employee. C. Create a new entry; create a one-to-many relationship from the employee to the new entry; and create a one-to-many relationship from the department entry to the new entry. D. Create a new entry; create a one-to-many relationship from the new entry to the employee entry; and then create a one-to-many relationship from the entry to the department entry.
|
| 30: | 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 for each toy by type? A. select ToyType, Count(*) from woof compute count(*) by toytype
B. select toytype from woof compute count(toytype) by toytype
C. select toyname, toytype from woof order by toytype compute count(ToyName) by Toytype
D. select toyname, toytype from woof with cube
|
| 31: | Peekaboo is at it again. This time, she just wants a quick list of all 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) )
A. select ToyType, ToyName from woof B. select ToyName, case ToyType when 'B' then 'Ball' When 'S' then 'Squeaky' else 'broken' end from woof
C. Select ToyName, case ToyType if 'B' then 'Ball' if 'S' then 'Squeaky' else 'broken' end from woof
D. Select ToyName, case ToyType when 'B' then 'Ball' When 'S' then 'Squeaky' else 'broken' from woof
|
| 32: | The people at the EconoVan Corporation are 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 VanSalesS WHERE Type = "YEAR"
|
| 33: | The people at the EconoVan Corporation are 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
|
| 34: | 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 there are 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
|
| 35: | Southwest Specialists is a firm dealing with the production of valuable ornamental goods. It is using merge replication to publish customer and order information to its infrequently connected sales representatives. The DBA does not, however, want sales representatives to see actual amounts paid, so it deselects the Amounts column and then replicates the data. This article is made so that it contains only selected columns from a table. What is this called? |
| 36: | What is the role of the Merge agent? A. Propagates updates, and monitors and resolves conflicts on Publishers and Subscribers. B. Stores records on the Distribution folder until needed, and then merges them with transactions. C. Enables records to be broken down into smaller subsets. D. Triggers when data modifications are made at the Subscriber after it has received a replica of data.
|
| 37: | You have set up a replication process using the default locations for the agents. Your process involves data being replicated to 20 Subscribers using a transactional replication strategy. How are the replication agents configured in transactional replication that uses a push subscription? A. The Log Reader agent resides on the Subscriber and the Distribution agent is on the Subscriber. B. The Log Reader agent resides on the Distributor and the Distribution agent is on the Subscriber. C. The Log Reader agent resides on the Subscriber and the Distribution agent is on the Publisher. D. The Log Reader agent resides on the Distributor and the Distribution agent is also located on the Distributor.
|
| 38: | Debra has just recently configured replication from the Products table to the Analysis table, located on the Headquarters and Research servers, respectively. Recently, Debra has noticed that replication failed for some unknown cause. Which of the following might be a likely cause of this problem? (Choose all that apply.) A. Replication requires three separate servers and Debra may not have implemented three servers. B. Replication cannot involve the Master, Msdb, Tempdb, and Model databases. Debra could have incorporated one of these databases with replication. C. Any replication process requires the use of at least two replication agents. Debra may have disabled one of the agents. D. Debra might have enabled Updating Subscribers, which is not allowed with any type of replication except for merge replication. E. The Transaction log of the Publisher may have been fully occupied, thus creating problem replication.
|
| 39: | Which of the following statements describes the role of the Log Reader agent? A. The Log Reader agent moves transactions from the Transaction log of the published database on the Publisher to the Distribution database or server. B. The Log Reader agent moves transactions and snapshot jobs held in the Distribution database out to the Subscribers. C. The Log Reader agent gives you the ability to run transactions in a sequence. D. The Log Reader agent deletes transactions at the click of a button.
|
| 40: | You are consulting for a manufacturing company that is running a single SQL Server 2000 computer. The server contains a database named Sales. The database has a group of tables that are used to examine sales trends. The database options are set to their default values. Analysts who use the database are reporting that query performance has become slower. You analyze the clustered primary key on the Invoices table and receive the following results: DBCC SHOWCONTIG scanning 'Invoices' table... Table: 'Invoices' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned......................: 200 - Extents Scanned.....................: 50 - Extent Switches.....................: 40 - Avg. Pages per Extent..............: 4.0 - Scan Density [Best Count:Actual Count]: 60.00% [3:5] - Logical Scan_Fragmentation.......: 0.00% - Extent Scan_Fragmentation.......: 40.00% - Avg. Bytes Free per Page.........: 146.5 - Avg. Page Density (full)........: 98.19% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
You want to improve the performance of queries that join tables to the Invoices table. What are three possible T-SQL statements you can execute to achieve this goal? (Each correct answer represents a complete solution. Choose three.) A. DBCC UPDATEUSEAGE ('Sales','Invoices','PK_Invoices')
B. CREATE UNIQUE CLUSTERED INDEX PK__Invoices On Invoices(InvoiceID) WITH DROP_EXISTING
C. DBCC INDEXDEFRAG ('Sales','Invoices','PK_Invoices')
D. DBCC DBREINDEX (Sales.dbo.Invoices, 'PK_Invoices')
E. UPDATE STATISTICS 'Invoices' F. DBCC CHECKALLOC (Sales, REPAIR_FAST)
|
| 41: | You are working on a SQL Server 2000 computer that contains a database that stores product data for your company. You need to execute an existing stored procedure that examines prices for your company's products and can modify them if necessary. You execute the stored procedure after business hours, but it does not complete. You execute the sp_lock stored procedure and receive the following output: spid dbid ObjId IndId Type Mode ------ ------ ----------- ------- ---- ---- 61 7 0 0 DB S 64 7 0 0 DB S 72 7 0 0 DB S 72 7 2145623952 1 PAG IS 72 7 2145623952 0 TAB IS 72 7 2145623952 1 KEY S 78 7 0 0 DB S 78 7 2145623952 1 PAG IX 78 7 2145623952 0 TAB IX 78 7 2145623952 1 KEY X
You want the stored procedure to complete successfully. What should you do? A. Execute the stored procedure, and specify the WITH RECOMPILE option. B. Execute the DBCC FREEPROCCACHE statement. C. Release the locks that are held by connections 61 and 64. D. Release the locks that are held by connections 72 and 78.
|
| 42: | You are working on a SQL Server 2000 computer that contains a database named Orders, which is used to record customer orders for the products your company sells. Your company's order volume exceeds one million orders per day. Each order uses approximately 100KB of space in the database. Users report that the database responds slowly when they enter new orders. You use SQL Profiler to monitor the activity on the database and receive the data shown in Figure 11.1. Figure 11.1. SQL Server Profiler output. 
You need to modify the database to improve performance. What should you do? A. Double the size of the data file. B. Configure the database to automatically grow by 10%. C. Separate the database into two physical files. D. Increase the size of the transaction log file.
|
| 43: | You are creating a database for a large government office. The primary key has already been established, but you need to supply another column that must have different values for each record. What data types could you use without creating additional constraints? (Choose all that apply.) A. timestamp B. bigint C. uniqueidentifier D. nvarchar E. sql_variant
|
| 44: | You are creating a database for a large government office. The primary key has already been established, but you need to supply another column that must have different values for each record. What implementation techniques are available other than data type selection? (Choose all that apply.) A. Identity B. Foreign key C. Unique index D. Unique constraint E. Rule
|
| 45: | You are putting together the logical design for a database. Tables to be included in the database are Employees, Customers, Supplies, Products, and Sales. The table used to store customer data has the following attributes: CustomerID (primary key), CustomerName, StreetAddress, City, State, ZipCode, BalanceOwing, SalesmanID, and SalesmanName. Which of the following rules of normalization are not being maintained? (Select all that apply.) |
| 46: | You are working for an automobile dealership that tracks inventory in a SQL Server database. The database contains information on the autos in stock. A partial listing of attributes is as follows: VehicleIDNo(20 char), InvoiceNo_(bigint), Make(20 char), Model(15 char), Year(smalldatetime), Colorcode(int), PurchasePrice(smallmoney), StickerPrices_(smallmoney). Which of the columns would you choose as a primary key? A. Use a compound key with Make, Model, and Year. B. Create a surrogate identity key. C. Use the VehicleIDNo as the key. D. Use the InvoiceNo as the key. E. Use a compound key with InvoiceNo and VehicleIDNo.
|
| 47: | You are working in a database that has an nchar(5) attribute used to store solely numeric data. You want to minimize the amount of disk space used for storage and need to select an alternative data type. Which of the following data types would you select? A. char(5) B. real C. smallint D. int E. bigint
|
| 48: | You are creating a historical database that stores information about important dates in history. You need to be able to store dates from the beginning of the 14th century. You want to minimize the storage space used by the data. Which data type would you use? A. datetime B. smalldatetime C. bigint D. int E. char(8)
|
| 49: | You are preparing a database structure for a large construction company. At any one time, the company is working on five or more job sites, and each site has between 25 and 200 homes. In charge of each site is a site supervisor who organizes the subcontractors at each phase of the building process (landscaping, framing, drywalling, electrical, plumbing, and so on). Any subcontractor who is planning on working on a given site must be found in a database of approved vendors. The company would like a structure that would allow for storage of the subcontractors' personal information and information about each site that includes the subcontractors assigned to the site. How would you set up this structure? A. A Site entity and a Contractor entity B. A Site entity, a Contractor entity, and a Site/Contractor entity C. A Site entity, a Process entity, and a Contractor entity D. A Site entity, a Contractor entity, and a Site/Process entity
|
| 50: | A small scientific laboratory needs a powerful database server to perform analysis of complex measures performed on scientists' regular experiments. The lab requires exact accuracy with all calculations because the results determine the fracture points of various metals. Which data type offers the most accurate results? A. smallmoney B. money C. float D. real E. decimal
|
