- You a database developer for a large grocery store chain. The partial database schema is shown in the Partial Database Schema exhibit (exhibit not available). The script that was used to create the Customers table is shown in the Script for Customers Table exhibit (exhibit not available). The store managers want to track customer demographics so they can target advertisements and coupon promotions to customers.

These advertisements and promotions will be based on the past purchases of existing customers. The advertisements and promotions will target buying patterns by one or more of these demographics: gender, age, postal code, and region. Most of the promotions will be based on gender and age. Queries will be used to retrieve the customer demographics information. You want the query response time to be as fast as possible. What should you do?   Add indexes on the PostalCode, State, and DateOfBirth columns of the Customers table.

- You a database developer for a large grocery store chain. The partial database schema is shown in the Partial Database Schema exhibit (exhibit not available). The script that was used to create the Customers table is shown in the Script for Customers Table exhibit (exhibit not available). The store managers want to track customer demographics so they can target advertisements and coupon promotions to customers.

These advertisements and promotions will be based on the past purchases of existing customers. The advertisements and promotions will target buying patterns by one or more of these demographics: gender, age, postal code, and region. Most of the promotions will be based on gender and age. Queries will be used to retrieve the customer demographics information. You want the query response time to be as fast as possible. What should you do?   Denormalized the customers table.

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem? Place the following troubleshooting steps in their correct order. Use only order choices that apply.

A1. Use the trace file as an input to the Index Tuning Wizard. B1. Run the trace. C1. Use SQL Profiler to create a trace based on the SOLP Profiler Tuning Template. D1. Specify that the trace data be saved to a file, and specify a maximum file size. E1. Use SQL Profiler to create a trace based on the SQL Profiler standard template. F1. Re-apply the trace and examine the output. G1. Specify a stop time for the trace.   C1,D1,G1,B1,F1

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem? Place the following troubleshooting steps in their correct order. Use only order choices that apply.

A1. Use the trace file as an input to the Index Tuning Wizard. B1. Run the trace. C1. Use SQL Profiler to create a trace based on the SOLP Profiler Tuning Template. D1. Specify that the trace data be saved to a file, and specify a maximum file size. E1. Use SQL Profiler to create a trace based on the SQL Profiler standard template. F1. Re-apply the trace and examine the output. G1. Specify a stop time for the trace.   E1,D1,B1,F1

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem (Place the following troubleshooting steps in their correct order).? (choose all that apply.)   Use the trace file as an input to the Index Tuning Wizard.

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem (Place the following troubleshooting steps in their correct order).? (choose all that apply.)   Run the trace.

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem (Place the following troubleshooting steps in their correct order).? (choose all that apply.)   Use SQL Profiler to create a trace based on the SOL Profiler Tuning Template.

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem (Place the following troubleshooting steps in their correct order).? (choose all that apply.)   Specify that the trace data be saved to a file, and specify a maximum file size.

- You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required. Which steps should you take to analyze this performance problem (Place the following troubleshooting steps in their correct order).? (choose all that apply.)   Specify a stop time for the trace.

- You are a database consultant. You have been hired by a local dog breeder to develop a database. This database will be used to store information about the breeder's dogs. You create a table named Dogs by using the following script: CREATE TABLE[dbo].[Dogs] ( [DogID] [int] NOT NULL, [BreedID] [int] NOT NULL, [Date of Birth] [datetime] NOT NULL, [WeightAtBirth] [decimal] (5, 2) NOT NULL, [NumberOfSiblings] [int] NULL, [MotherID] [int] NOT NULL, [FatherID] [int] NOT NULL )_on [PRIMARY] GO ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD CONSTRAINT [PK_Dogs]PRIMARYT KEY CLUSTERED ( [DogID] ) ON [PRIMARY] GO

You must ensure that each dog has a valid value for the MotherID and FatherID columns. You want to enforce this rule while minimizing disk I/O. What should you do?   Create two FOREIGN KEY constraints, create one coinstraint on the MOtherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column.

- You are a database consultant. You have been hired by a local dog breeder to develop a database. This database will be used to store information about the breeder’s dogs. You create a table named Dogs by using the following script:

CREATE TABLE[dbo].[Dogs] ( [DogID] [int] NOT NULL, [BreedID] [int] NOT NULL, [Date of Birth] [datetime] NOT NULL, [WeightAtBirth] [decimal] (5, 2) NOT NULL, [NumberOfSiblings] [int] NULL, [MotherID] [int] NOT NULL, [FatherID] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD

CONSTRAINT [PK_Dogs]PRIMARY KEY CLUSTERED ( [DogID] ) ON [PRIMARY] GO

You must ensure that each dog has a valid value for the MotherID and FatherID columns. You want to enforce this rule while minimizing disk I/O.

What should you do?   Create two FOREIGN KEY constraints, create one constraint on the MotherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column

- You are a database consultant. You have been hired by a local dog breeder to develop a database. This database will be used to store information about the breeder’s dogs. You create a table named Dogs by using the following script:

CREATE TABLE[dbo].[Dogs] ( [DogID] [int] NOT NULL, [BreedID] [int] NOT NULL, [Date of Birth] [datetime] NOT NULL, [WeightAtBirth] [decimal] (5, 2) NOT NULL, [NumberOfSiblings] [int] NULL, [MotherID] [int] NOT NULL, [FatherID] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD

CONSTRAINT [PK_Dogs]PRIMARY KEY CLUSTERED ( [DogID] ) ON [PRIMARY] GO

You must ensure that each dog has a valid value for the MotherID and FatherID columns. You want to enforce this rule while minimizing disk I/O.

What should you do?   Create two FOREIGN KEY constraints, create one constraint on the MotherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column

- You are a database consultant. You have been hired by a local dog breeder to develop a database. This database will be used to store information about the breeder's dogs. You create a table named Dogs by using the following script:

CREATE TABLE[dbo].[Dogs] ( [DogID] [int] NOT NULL, [BreedID] [int] NOT NULL, [Date of Birth] [datetime] NOT NULL, [WeightAtBirth] [decimal] (5, 2) NOT NULL, [NumberOfSiblings] [int] NULL, [MotherID] [int] NOT NULL, [FatherID] [int] NOT NULL )_on [PRIMARY] GO ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD CONSTRAINT [PK_Dogs]PRIMARYT KEY CLUSTERED ( [DogID] ) ON [PRIMARY] GO

You must ensure that each dog has a valid value for the MotherID and FatherID columns. You want to enforce this rule while minimizing disk I/O. What should you do?   Create two FOREIGN KEY constraints, create one coinstraint on the MOtherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column.

- You are a database developer for a bookstore. Each month, you receive new supply information from your vendor’s in the form of an XML document. The XML document is shown in the XML Document exhibit.

XML Document

<CATEGORYID= CategoryName="“Videos”" “2”

Server 2000”>

You are designing a stored procedure to read the XML document and to insert the data into a table named Products. The Products table is shown in the Products Table exhibit.

Which script should you use to create this stored procedure?   CREATE PROCEDURE spAddCatalogItems ( @xmlDocument varchar (8000)) AS BEGIN DECLARE @docHandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument INSERT INTO Products SELECT * FROM OPENXML (@docHandle, ‘/ROOT/Category/Product’, 1) WITH (ProductID int ‘./@ProductID’, CategoryID int ‘../@CategoryID’, [Description] varchar (100) ‘./@Description’) EXEC sp_xml_removedocument @docHandle END

- You are a database developer for a bookstore. Each month, you receive new supply information from your vendors in the form of an XML document. The XML document is shown below:

XML Document <CATEGORYID= CategoryName="Videos" ?2?

You are designing a stored procedure to read the XML document and to insert the data into a table named Products. The Products table is shown below:

Which script should you use to create this stored procedure?   CREATE PROCEDURE spAddCatalogItems (@xmlDocument varchar (8000)) AS BEGIN DECLARE @docHandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument INSERT INTO Products SELECT FROM OPENXML (@docHandle, '/ROOT/Category/Product', 1) WITH (ProductID int './@ProductID', CategoryID int '../@CategoryID', [Description] varchar (100) './@Description') EXEC sp_xml_removedocument @docHandle END

- You are a database developer for a bookstore. Each month, you receive new supply information from your vendors in the form of an XML document. The XML document is shown below: XML Document <ROOT><CategoryID= "2" CategoryName= "Videos"> <Product ProductID= "80248" Description= "7 Minute Abs"> </Product></Category><Category CategoryID= "3" CategoryName= "Computer Books"> <Product ProductID= "12345" Description= "Inside SQL Server 2000"> </Product></Category><Category CategoryID= "3" CategoryName= "Computer Books"> <Product ProductID= "22345" Description= "Analysis Services with SQL Server 2000"> </Product></Category><ROOT>

You are designing a stored procedure to read the XML document and to insert the data into a table named Products. The Products table is shown below:

Which script should you use to create this stored procedure?   CREATE PROCEDURE spAddCatalogItems (@xmlDocument varchar (8000)) AS

BEGIN DECLARE @docHandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

INSERT INTO Products SELECT FROM OPENXML (@docHandle, '/ROOT/Category/Product', 1) WITH (ProductID int './@ProductID', CategoryID int '../@CategoryID', [Description] varchar (100) './@Description')

EXEC sp_xml_removedocument @docHandle END

- You are a database developer for a bookstore. You are designing a stored procedure to process XML documents. You use the following script to create the stored procedure:

CREATE PROCEDURE spParseXML (@xmlDocument varchar(1000)) AS DECLARE @dochandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument SELECT * FROM OPENXML (@docHandle, ‘/ROOT/Category/Product’,2) WITH (ProductID int, CategoryID int, CategoryName varchar (50), [Description] varchar (50)) EXEC sp_xml_removedocument @docHandle

You execute this stored procedure and use an XML documents as the input document. The XML document is shown in the XML Document exhibit.

XML Document

you”>

Server 2000”>

<br" ProductID="“22345”" /Services with SQL Server 2000”>

You receive the output shown in the Output exhibit.

Output:

ProductID CategoryID CategoryName Description NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL (4 row(s) affected)

You need to replace the body of the stored procedure.

Which script should you use?   SELECT * FROM OPENXML (@docHandle, ‘/ROOT/category/Product’, 1) WITH (ProductID int, CategoryID int ‘../@CategoryID’, CategoryName varchar(50), ‘../@CategoryName’, [Description] varchar (50))

- You are a database developer for a bookstore. You are designing a stored procedure to process XML documents. You use the following script to create the stored procedure:

CREATE PROCEDURE spParseXML (@xmlDocument varchar(1000)) AS DECLARE @dochandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument SELECT * FROM OPENXML (@docHandle, ‘/ROOT/Category/Product’,2) WITH (ProductID int, CategoryID int, CategoryName varchar (50), [Description] varchar (50)) EXEC sp_xml_removedocument @docHandle

You execute this stored procedure and use an XML documents as the input document. The XML document is shown in the XML Document exhibit.

XML Document

you”>

Server 2000”>

<br" ProductID="“22345”" /Services with SQL Server 2000”>

You receive the output shown in the Output exhibit.

Output:

ProductID CategoryID CategoryName Description NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL (4 row(s) affected)

You need to replace the body of the stored procedure.

Which script should you use?   SELECT * FROM OPENXML (@docHandle, ‘/ROOT/category/Product’, 1) WITH (ProductID int, CategoryID int ‘../@CategoryID’, CategoryName varchar(50), ‘../@CategoryName’, [Description] varchar (50))

- You are a database developer for a bookstore. You are designing a stored procedure to process XML documents. You use the following script to create the stored procedure:

CREATE PROCEDURE spParseXML (@xmlDocument varchar(1000)) AS DECLARE @dochandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument SELECT FROM OPENXML (@docHandle, '/ROOT/Category/Product',2) WITH (ProductID int, CategoryID int, CategoryName varchar (50), [Description] varchar (50)) EXEC sp_xml_removedocument @docHandle

You execute this stored procedure and use an XML documents as the input document. The XML document is shown below: XML Document <CATEGORYID= CategoryName="General Books" ?1?

You receive the output shown below: Output ProductID CategoryID CategoryName DescriptionNULL NULL NULL NULLNULL NULL NULL NULLNULL NULL NULL NULLNULL NULL NULL NULL(4 row(s) affected)

You need to replace the body of the stored procedure.

Which script should you use?   SELECT FROM OPENXML (@docHandle, '/ROOT/category/Product', 1) WITH (ProductID int, CategoryID int, CategoryName varchar(50), [Description] varchar (50))

- You are a database developer for a bookstore. You are designing a stored procedure to process XML documents. You use the following script to create the stored procedure:

CREATE PROCEDURE spParseXML (@xmlDocument varchar(1000)) AS DECLARE @dochandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument SELECT FROM OPENXML (@docHandle, '/ROOT/Category/Product',2) WITH (ProductID int, CategoryID int, CategoryName varchar (50), [Description] varchar (50)) EXEC sp_xml_removedocument @docHandle

You execute this stored procedure and use an XML documents as the input document. The XML document is shown below: XML Document <CATEGORYID= CategoryName="General Books" ?1?

You receive the output shown below: Output ProductID CategoryID CategoryName DescriptionNULL NULL NULL NULLNULL NULL NULL NULLNULL NULL NULL NULLNULL NULL NULL NULL(4 row(s) affected)

You need to replace the body of the stored procedure.

Which script should you use?   SELECT FROM OPENXML (@docHandle, '/ROOT/category/Product', 1) WITH (ProductID int, CategoryID int '../CategoryID', CategoryName varchar(50), '../CategoryName', [Description] varchar (50))

- You are a database developer for a bookstore. You are designing a stored procedure to process XML documents. You use the following script to create the stored procedure: CREATE PROCEDURE spParseXML (@xmlDocument varchar(1000)) AS DECLARE @dochandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

SELECT FROM OPENXML (@docHandle, '/ROOT/Category/Product',2) WITH (ProductID int, CategoryID int, CategoryName varchar (50), [Description] varchar (50)) EXEC sp_xml_removedocument @docHandle

You execute this stored procedure and use an XML documents as the input document. The XML document is shown below: XML Document <ROOT><CategoryID= "1" CategoryName= "General Books"> <Product ProductID="10248" Description="Cooking for you"> </Product></Category><Category CategoryID= "2" CategoryName= "Videos"> <Product ProductID= "80248" Description= "7 Minute Abs"> </Product></Category><Category CategoryID= "3" CategoryName= "Computer Books"> <Product ProductID= "12345" Description= "Inside SQL Server 2000"> </Product> <Product ProductID= "22345" Description= "Analysis Services with SQL Server 2000"> </Product></Category><ROOT>

You receive the output shown below:

Output ProductID CategoryID CategoryName DescriptionNULL NULL NULL NULLNULL NULL NULL NULLNULL NULL NULL NULLNULL NULL NULL NULL(4 row(s) affected)You need to replace the body of the stored procedure.

Which script should you use?   SELECT FROM OPENXML (@docHandle, '/ROOT/category/Product', 1) WITH (ProductID int, CategoryID int '../@CategoryID', CategoryName varchar(50) '../@CategoryName', [Description] varchar (50) '@Description')

- You are a database developer for a clothing retailer. The company has a database named Sales. This database contains a table named Inventory. The Inventory table contains the list of items for sale and the quantity available for each of those items. When sales information is inserted into the database, this table is updated. The stored procedure that updates the inventory table is shown below:

CREATE PROCEDURE UpdateInventory @IntID int AS BEGIN DECLARE @Count intBEGIN TRANSELECT @Count = AvailableFROM Inventory WITH (HOLDLOCK) WHERE InventoryID = @IntIDIF (@Count > 0) UPDATE Inventory SET Available = @Count ¨C 1 WHERE InventoryID = @IntIDCOMMIT TRANEND

When this procedure executes, the database server occasionally returns the following error message: Transaction (Process ID 53) was deadlock on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. You need to prevent the error message from occurring while maintaining data integrity. What should you do?   Change the table hint to UPDLOCK.

- You are a database developer for a clothing retailer. The company has a database named sales. This database contains a table named inventory. The inventory table contains the list of items for sale and the quantity available for each of those items. When sales information is inserted into the database, this table is updated. The stored procedure that updates the inventory table is shown in an exhibit.

CREATE PROCEDURE UpdateInventory @IntID int AS BEGIN DECLARE @Count int BEGIN TRAN SELECT @Count = Available FROM Inventory WITH (HOLDLOCK) WHERE InventoryID = @IntID IF (@Count > 0) UPDATE Inventory SET Available = @Count – 1 WHERE InventoryID = @IntID COMMIT TRAN END

When this procedure executes, the database server occasionally returns the following error message:

Transaction (Process ID 53) was deadlock on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

You need to prevent the error message from occurring while maintaining data integrity.

What should you do?   Change the table hint to UPDLOCK

- You are a database developer for a clothing retailer. The company has a database named Sales. This database contains a table named Inventory. The Inventory table contains the list of items for sale and the quantity available for each of those items. When sales information is inserted into the database, this table is updated. The stored procedure that updates the inventory table is shown below:

CREATE PROCEDURE UpdateInventory @IntID int AS BEGIN DECLARE @Count intBEGIN TRAN SELECT @Count = AvailableFROM Inventory WITH (HOLDLOCK) WHERE InventoryID = @IntIDIF (@Count > 0) UPDATE Inventory SET Available = @Count ¨C 1 WHERE InventoryID = @IntIDCOMMIT TRAN END

When this procedure executes, the database server occasionally returns the following error message: Transaction (Process ID 53) was deadlock on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. You need to prevent the error message from occurring while maintaining data integrity. What should you do?   Change the table hint to UPDLOCK.

- You are a database developer for a company that compiles statistics for baseball teams. These statistics are stored in a database named Statistics. The Players of each team are entered in a table name Rosters table in the Statistics database. The script that was used to create the Rosters table is shown in an exhibit.

CREATE TABLE Rosters ( RosterID int NOT NULL, TeamID int NOT NULL, FirstName char(20) NOT NULL, LastName char(20) NOT NULL, CONSTRAINT PK_Rosters PRIMARY KEY (RosterID), CONSTRAINT FK_TeamRoaster FOREIGN KEY (TeamID) REFERENCES Team (TeamID) )

Each baseball team can have a maximum of 24 players on the roster at any one time. You need to ensure that the number if players on the team never exceeds the maximum.

What should you do?   Create a trigger on the Rosters table that validates the data

- You are a database developer for a company that compiles statistics for baseball teams. These statistics are stored in a database named Statistics. The Players of each team are entered in a table name Rosters table in the Statistics database. The script that was used to create the Rosters table is shown below:

CREATE TABLE Rosters ( RosterID int NOT NULL, TeamID int NOT NULL, FirstName char(20) NOT NULL, LastName char(20) NOT NULL, CONSTRAINT PK_Rosters PRIMARY KEY (RosterID), CONSTRAINT FK_TeamRoaster FOREIGN KEY (TeamID) REFERENCES Team (TeamID) )

Each baseball team can have a maximum of 24 players on the roster at any one time. You need to ensure that the number if players on the team never exceeds the maximum. What should you do?   Create a trigger on the Rosters table that validates the data

- You are a database developer for a company that conducts telephone surveys of consumer music preferences. As the survey responses are received from the survey participants, they are inserted into a table named SurveyData. After all of the responses to a survey are received, summaries of the results are produced. You have been asked to create a summary by sampling every fifth row of responses for a survey. You need to produce the summary as quickly as possible.

What should you do?   Use a cursor to retrieve all of the data for the survey. Use the FETCH RELATIVE 5 statement to select the summary data from the cursor.

- You are a database developer for a company that conducts telephone surveys of consumer music preferences. As the survey responses are received from the survey participants, they are inserted into a table named SurveyData. After all of the responses to a survey are received, summaries of the results are produced.

You have been asked to create a summary by sampling every fifth row of responses for a survey. You need to produce the summary as quickly as possible. What should you do?   Use a cursor to retrieve all of the data for the survey. Use the FETCH RELATIVE 5 statement to select the summary data from the cursor.

- You are a database developer for a company that conducts telephone surveys of consumer music preferences. As the survey responses are received from the survey participants, they are inserted into a table named SurveyData. After all of the responses to a survey are received, summaries of the results are produced.

You have been asked to create a summary by sampling every fifth row of responses for a survey. You need to produce the summary as quickly as possible.

What should you do?   Use a cursor to retrieve all of the data for the survey. Use FETCH RELATIVE 5 statement to select the summary data from the cursor.

- You are a database developer for a company that leases trucks. The company has created a web site that customer can use to reserve trucks. You are designing the SQL server 2000 database to support the web site. New truck reservations are inserted into a table named Reservations. Customers who have reserved a truck can return to the web site and update their reservation. When a reservation is updated, the entire existing reservation must be copied to a table named History.

Occasionally, customers will save an existing reservation without actually changing any of the information about the reservation. In this case, the existing reservation should not be copied to the History table. You need to develop a way to create the appropriate entries in the History table.

What should you do?   Create a trigger on the Reservations table to create the History table entries.

- You are a database developer for a Company that produces an online telephone directory. A table named PhoneNumber is shown below:

PhoneNumbers ?¡À PhoneNumberID?¡À AreaCode?¡À Exchange?¡À Number?¡À LastName?¡À FirstName?¡À StreetAddress?¡À City?¡À State?¡À PostalCode

After loading 100,000 names into the table, you create indexes by using the following script: ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName], ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID] ) ON [PRIMARY] GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute: Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'

How should you improve the processing performance of these types of queries?

(Each correct answer presents part of the solution.)(Choose two)   Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.

- You are a database developer for a Company that produces an online telephone directory. A table named PhoneNumber is shown below:

PhoneNumbers ?¡À PhoneNumberID?¡À AreaCode?¡À Exchange?¡À Number?¡À LastName?¡À FirstName?¡À StreetAddress?¡À City?¡À State?¡À PostalCode

After loading 100,000 names into the table, you create indexes by using the following script: ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName], ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID] ) ON [PRIMARY] GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute: Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'

How should you improve the processing performance of these types of queries?

(Each correct answer presents part of the solution.)(Choose two)   Add a nonclustered index on the City column.

- You are a database developer for a Company that produces an online telephone directory. A table named PhoneNumber is shown below:

PhoneNumbers PhoneNumberID AreaCode Exchange Number LastName FirstName StreetAddress City State PostalCode

After loading 100,000 names into the table, you create indexes by using the following script:

ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName], ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID] ) ON [PRIMARY] GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute: Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'

How should you improve the processing performance of these types of queries? (Choose two)   Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.

- You are a database developer for a Company that produces an online telephone directory. A table named PhoneNumber is shown below:

PhoneNumbers PhoneNumberID AreaCode Exchange Number LastName FirstName StreetAddress City State PostalCode

After loading 100,000 names into the table, you create indexes by using the following script:

ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName], ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID] ) ON [PRIMARY] GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute: Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'

How should you improve the processing performance of these types of queries? (Choose two)   Add a nonclustered index on the City column.

- You are a database developer for a Company that produces an online telephone directory. A table named PhoneNumber is shown in an exhibit.

After loading 100,000 names into the table, you create indexes by using the following script:

ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName], ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID] ) ON [PRIMARY] GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute:

Return all names and phone numbers for persons who live in a certain city and whose last name begin with ‘W’

How should you improve the processing performance of these types of queries (Each correct answer presents part of the solution)? (Choose two)   Update statistics on the Policy table.

- You are a database developer for a Company that produces an online telephone directory. A table named PhoneNumber is shown in an exhibit.

After loading 100,000 names into the table, you create indexes by using the following script:

ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED ( [FirstName], [LastName], ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers]( [PhoneNumberID] ) ON [PRIMARY] GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute:

Return all names and phone numbers for persons who live in a certain city and whose last name begin with ‘W’

How should you improve the processing performance of these types of queries (Each correct answer presents part of the solution)? (Choose two)   Update statistics on the Policy table.

- You are a database developer for a company. Your database lies a table named ORDER which has 750,000 rows. Cluster index already exists on this table. Number of rows grows 5% each week. Company has planned to launch a promotion next week, which will increase the volume of the table ORDER table by 50%. You want to optimize the performance of these inserts into the table.

What strategy will you opt for?   Rebuild the clustered indexes with fill factor as 50%

- You are a database developer for a container manufacturing Company. The container produced by your Company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit. A sample of the data stored in the tables is shown in the Sample Data exhibit.

Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables. You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information.

What should you do?   Create a user-defined function that requires ContainerID as an argument and returns the volume of the container

- You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:A sample of the data stored in the tables is shown below: Size Table SizeID SizeName Height 1 Small 40 2 Medium 60 3 Large 80 4 Jumbo 100

Shape Table ShapeID ShapeName Measurement 1 Triangle 10 2 Triangle 20 3 Triangle 30 4 Square 20 5 Square 30 6 Square 40 7 Circle 15 8 Circle 25 9 Circle 35

Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables. You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?   Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.

- You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:A sample of the data stored in the tables is shown below:

Size Table SizeID SizeName Height 1 Small 40 2 Medium 60 3 Large 80 4 Jumbo 100

Shape Table ShapeID ShapeName Measurement 1 Triangle 10 2 Triangle 20 3 Triangle 30 4 Square 20 5 Square 30 6 Square 40 7 Circle 15 8 Circle 25 9 Circle 35

Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables. You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?   Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.

- You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:A sample of the data stored in the tables is shown below:

Size Table SizeID SizeName Height 1 Small 40 2 Medium 60 3 Large 80 4 Jumbo 100

Shape Table ShapeID ShapeName Measurement 1 Triangle 10 2 Triangle 20 3 Triangle 30 4 Square 20 5 Square 30 6 Square 40 7 Circle 15 8 Circle 25 9 Circle 35

Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables. You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?   Add a column named volume to the container table. Create a trigger that calculates and stores volume in this column when a new container is inserted into the table.

- You are a database developer for a database named Accounts at Woodgrove Bank (or savings and loan company). A developer is creating a multi-tier application for the bank. Bank employees will use the application to manage customer accounts. The developer needs to retrieve customer names from the accounts database to populate a drop-down list box in the application. A user of the application will use the list box to locate a customer account. The database contains more than 50,000 customer accounts. Therefore, the developer wants to retrieve only 25 rows as the user scrolls through the list box. The most current list of customers must be available to the application at all times. You need to recommend a strategy for the developer to use when implementing the drop-down list box. What should you recommend?   Use an API server-side cursor to retrieve the data that is loaded into list box.

- You are a database developer for a database named Accounts at Woodgrove Bank. A developer is creating a multi-tier application for the bank. Bank employees will use the application to manage customer accounts. The developer needs to retrieve customer names from the accounts database to populate a drop-down list box in the application. A user of the application will use the list box to locate a customer account. The database contains more than 50,000 customer accounts. Therefore, the developer wants to retrieve only 25 rows as the user scrolls through the list box. The most current list of customers must be available to the application at all times. You need to recommend a strategy for the developer to use when implementing the drop-down list box. What should you recommend?   Use an API server-side cursor to retrieve the data that is loaded into list box.

- You are a database developer for a database named Accounts at Woodgrove Bank. A developer is creating a multi-tier application for the bank. Bank employees will use the application to manager customer accounts. The developer needs to retrieve customer named from the accounts database to populate a drop-down list box in the application. A user of the application will use the list box to locate a customer account.

The database contains more than 50,000 customer accounts. Therefore, the developer wants to retrieve only 25 rows as the user scrolls through the list box. The most current list of customers must be available to the application at all times. You need to recommend a strategy for the developer to use when implementing the drop-down list box.

What should you recommend?   Use an API server-side cursor to retrieve the data that is loaded into list box.

- You are a database developer for A Datum Corporation. You are creating a database that will store statistics for 15 different high school sports. This information will be used by 50 companies that publish sports information on their web sites. Each company's Web site arranges and displays the statistics in a different format.

You need to package the data for delivery to the companies. What should you do?   Extract the data by using SELECT statements that include the FOR XML clause

- You are a database developer for A Datum Corporation. You are creating a database that will store statistics for 15 different high school sports. This information will be used by 50 companies that publish sports information on their web sites. Each company's Web site arranges and displays the statistics in a different format.

You need to package the data for delivery to the companies. What should you do?   Extract the data by using SELECT statements that include the FOR XML clause

- You are a database developer for a food wholesaler. Each week, the company fulfills orders from various customers. Normally, each customer orders the same quantity of certain items each week. Occasionally, the quantity of an item that a customer orders is significantly less than the customer’s usual quantity. The information about each order is stored in a table named Invoice, which is located in a SQL server 2000 database. The script that was used to create this table is shown in an exhibit.

EXHIBIT CREATE TABLE Invoice ( InvoiceID int NOT NULL, InvoiceNumber char(10) NOT NULL, CustomerName char(30) NOT NULL, InvoiceAmount money NOT NULL DEFAULT (0), CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceID) )

You want to identify any pattern to these unusual orders. To do this, you need to produce a list of the invoices for each customer that are for a lesser amount than average invoice amount for that customer.

Which query should you use?   SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount FROM Invoice As i1, Invoice AS i2 GROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount HAVING i1.InvoiceAmount < AVG (i2.InvoiceAmount) ORDER BY i1.CustomerName, i1.InvoiceNumber

- You are a database developer for a food wholesaler. Each week, the company fulfills orders from various customers. Normally, each customer orders the same quantity of certain items each week. Occasionally, the quantity of an item that a customer orders is significantly less than the customer’s usual quantity. The information about each order is stored in a table named Invoice, which is located in a SQL server 2000 database. The script that was used to create this table is shown in an exhibit.

EXHIBIT CREATE TABLE Invoice ( InvoiceID int NOT NULL, InvoiceNumber char(10) NOT NULL, CustomerName char(30) NOT NULL, InvoiceAmount money NOT NULL DEFAULT (0), CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceID) )

You want to identify any pattern to these unusual orders. To do this, you need to produce a list of the invoices for each customer that are for a lesser amount than average invoice amount for that customer.

Which query should you use?   SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount FROM Invoice As i1 WHERE i1.InvoiceAmount < (SELECT AVG (i2.InvoiceAmount) FROM Invoice AS i2 WHERE i2.CustomerName=i1.CustomerName) ORDER BY i1.CustomerName, i1.InvoiceNumber

- You are a database developer for a hospital. There are four supply rooms on each floor of the hospital, and the hospital has 26 floors. You are designing an inventory control database for disposable equipment. Certain disposable items must be kept stored at all times. As each item is used, a barcode is scanned to reduce the inventory count in the database. The supply manager should be paged as soon as a supply room has less than the minimum quantity of an item. What should you do?   Create a stored procedure that will be called to update the inventory table. If the resultant quantity is less than the restocking quantity, use the xp_logevent system stored procedure to page the supply manager.

- You are a database developer for a hospital. There are four supply rooms on each floor of the hospital, and the hospital has 26 floors. You are designing an inventory control database for disposable equipment. Certain disposable items must be kept stored at all times. As each item is used, a barcode is scanned to reduce the inventory count in the database. The supply manager should be paged as soon as a supply room has less than the minimum quantity of an item. What should you do?   Create a FOR UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use the xp_sendmail system stored procedure to page the supply manager.

- You are a database developer for a hospital. There are four supply rooms on each floor of the hospital, and the hospital has 26 floors. You are designing an inventory control database for disposable equipment. Certain disposable items must be kept stored at all times. As each item is used, a barcode is scanned to reduce the inventory count in the database. The supply manager should be paged as soon as a supply room has less than the minimum quantity of an item. What should you do?   Schedule the SQL server job to run at four-hour intervals. Configure the job to use the @notify_level_page = 2 argument. Configure the job so that it tests each item’s quantity against the restocking quantity. Configure the job so that it returns a false value if the item requires restocking. This will trigger the paging of the supply manager.

- You are a database developer for a hospital. There are four supply rooms on each floor of the hospital, and the hospital has 26 floors. You are designing an inventory control database for disposable equipment. Certain disposable items must be kept stored at all times. As each item is used, a barcode is scanned to reduce the inventory count in the database. The supply manager should be paged as soon as a supply room has less than the minimum quantity of an item. What should you do?   Create a FOR UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use the xp_sendmail system stored procedure to page the supply manager.

- You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and information. This database will contain a table named physicians and a table named Patients.

Physicians treat multiple patients. Patients have a primary physician and usually have a secondary physician. The primary physician must be identified as the primary physician. The Patients table will contain no more than 2 million rows. You want to increase I/O performance when data is selected from the tables. The database should be normalized to the third normal form.

Which script should you use to create the tables?   CREATE TABLE Patient ( PatientID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Physicians ( PhysiciansID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE PatientPhysician ( PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID), PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit NOT NULL, CONSTRAINT PK_PatientPhysician PRIMARY KEY (PhysicianID, PatientID) )

- You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and patient information. This database will contain a table named Physicians and a table named Patients. Physicians treat multiple patients. Patients have a primary physician and usually have a secondary physician. The primary physician must be identified as the primary physician. The Patients table will contain no more than 2 million rows. You want to increase I/O performance when data is selected from the tables. The database should be normalized to the third normal form. Which script should you use to create the tables?   CREATE TABLE Patients ( PatientID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CRAETE TABLE Physicians ( PhysiciansID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE PatientPhysician ( PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID), PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit NOT NULL, CONSTRAINT PK_PatientPhysician PRIMARY KEY (PhysicianID, PatientID) )

- You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and patient information. This database will contain a table named Physicians and a table named Patients. Physicians treat multiple patients. Patients have a primary physician and usually have a secondary physician. The primary physician must be identified as the primary physician. The Patients table will contain no more than 2 million rows. You want to increase I/O performance when data is selected from the tables. The database should be normalized to the third normal form. Which script should you use to create the tables?   CREATE TABLE Patients ( PatientID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CRAETE TABLE Physicians ( PhysiciansID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE PatientPhysician ( PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID), PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit NOT NULL, CONSTRAINT PK_PatientPhysician PRIMARY KEY (PhysicianID, PatientID) )

- You are a database developer for a large electric utility company. The company is divided into many departments, and each employee of the company is assigned to a department. You create a table named Employee that contains information about all employees, including the department to which they belong. The script that was used to create the Employee table is shown in an exhibit.

CREATE TABLE Employee ( EmployeeID uniqueidentifier NOT NULL, FirstName char (20) NOT NULL, LastName char (25) NOT NULL, DepartmentID int NOT NULL, Salary money NOT NULL, CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID) )

Each department manager should be able to view only the information in the Employee table that pertains to his or her department.

What should you do?   Build tables and views that enforce row-level security on the Employee table

- You are a database developer for a large travel Company. Information about each of the Company’s departments is stored in a table named Department. Data about each of the Company’s travel agents and department managers is stored in a table named Employees. The SQLLogin column of the Employees table contains the database login for the travel agent or department manager. The department and employees table are shown in an exhibit.

Each department manager has been added to the managers database role. You need to allow members of this database role to view all of the data in the department table. Members of this role should be able to insert or update only the row that pertains to their department. You grant the managers database role SELECT permissions on the department table.

What should you do next?   Create a trigger on the Department table that checks whether the database login of the user performing the insert or update operation belongs to a member of that department.

- You are a database developer for a large travel Company. Information about each of the Company’s departments is stored in a table named Department. Data about each of the Company’s travel agents and department managers is stored in a table named Employees. The SQLLogin column of the Employees table contains the database login for the travel agent or department manager. The department and employees table are shown in an exhibit.

Each department manager has been added to the managers database role. You need to allow members of this database role to view all of the data in the department table. Members of this role should be able to insert or update only the row that pertains to their department. You grant the managers database role SELECT permissions on the department table.

What should you do next?   Create a view that includes all columns in the Department table and the SQLLogin column from the employees table. Include the WITH CHECK OPTION clause in the view definition.

- You are a database developer for a large travel company. Information about each of the company's departments is stored in a table named Department. Data about each of the company's travel agents and department managers is stored in a table named Employees. The SQLLogin column of the Employees table contains the database login for the travel agent or department manager. The department and employees table are shown below: Each department manager has been added to the managers database role. You need to allow members of this database role to view all of the data in the department table. Members of this role should be able to insert or update only the row that pertains to their department. You grant the managers database role SELECT permissions on the department table. What should you do next?   Create a trigger on the department table that checks whether the database login of the user performing the insert or update operation belongs to a member of that department.

- You are a database developer for a large travel company. Information about each of the company's departments is stored in a table named Department. Data about each of the company's travel agents and department managers is stored in a table named Employees. The SQLLogin column of the Employees table contains the database login for the travel agent or department manager. The department and employees table are shown below: Each department manager has been added to the managers database role. You need to allow members of this database role to view all of the data in the department table. Members of this role should be able to insert or update only the row that pertains to their department. You grant the managers database role SELECT permissions on the department table. What should you do next?   Create a view that includes all columns in the department table and the SQLLogin column from the employees table.include the WITH CHECK OPTION clause in the view definition.

- You are a database developer for a large travel company. Information about each of the company's departments is stored in a table named Department. Data about each of the company's travel agents and department managers is stored in a table named Employees. The SQLLogin column of the Employees table contains the database login for the travel agent or department manager. The department and employees table are shown below:

Each department manager has been added to the managers database role. You need to allow members of this database role to view all of the data in the department table. Members of this role should be able to insert or update only the row that pertains to their department. You grant the managers database role SELECT permissions on the department table. What should you do next?   Create a trigger on the department table that checks whether the database login of the user performing the insert or update operation belongs to a member of that department.

- You are a database developer for a large travel company. You have been granted CREATE VIEW permissions in the Reservations database. Your company's worker, Eric, has been granted CREATE TABLE permissions. Neither of you have been given database owner or system permissions, nor have you been added to any fixed server roles.

Eric has created a table named Traveler that holds information about your company's customers. This table is shown in an exhibit.

Travel agents will connect to the database and view the information stored in this table. The database logins for the travel agents have been assigned to the Agent database role.

You want the travel agents to be able to view the name and address information from the Traveler table in two columns instead of six. One column should contain the traveler name and the other column should contain the address.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Instruct Eric to grant the Agent role SELECT permissions on the Traveler table.

- You are a database developer for a large travel company. You have been granted CREATE VIEW permissions in the Reservations database. Your company's worker, Eric, has been granted CREATE TABLE permissions. Neither of you have been given database owner or system permissions, nor have you been added to any fixed server roles.

Eric has created a table named Traveler that holds information about your company's customers. This table is shown in an exhibit.

Travel agents will connect to the database and view the information stored in this table. The database logins for the travel agents have been assigned to the Agent database role.

You want the travel agents to be able to view the name and address information from the Traveler table in two columns instead of six. One column should contain the traveler name and the other column should contain the address.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Instruct Eric to grant you SELECT permissions of needed columns on the Traveler table

- You are a database developer for a large travel company. You have been granted CREATE VIEW permissions in the Reservations database. Your company's worker, Eric, has been granted CREATE TABLE permissions. Neither of you have been given database owner or system permissions, nor have you been added to any fixed server roles.

Eric has created a table named Traveler that holds information about your company's customers. This table is shown in an exhibit.

Travel agents will connect to the database and view the information stored in this table. The database logins for the travel agents have been assigned to the Agent database role.

You want the travel agents to be able to view the name and address information from the Traveler table in two columns instead of six. One column should contain the traveler name and the other column should contain the address.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Create a view named vwTravelers that displays the data in the desired format.

- You are a database developer for a large travel company. You have been granted CREATE VIEW permissions in the Reservations database. Your company's worker, Eric, has been granted CREATE TABLE permissions. Neither of you have been given database owner or system permissions, nor have you been added to any fixed server roles.

Eric has created a table named Traveler that holds information about your company's customers. This table is shown in an exhibit.

Travel agents will connect to the database and view the information stored in this table. The database logins for the travel agents have been assigned to the Agent database role.

You want the travel agents to be able to view the name and address information from the Traveler table in two columns instead of six. One column should contain the traveler name and the other column should contain the address.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Grant the Agent role SELECT permissions on the vwTravelers view

- You are a database developer for a large travel company. You have been granted CREATE VIEW permissions in the Reservations database. Your company's-worker, Eric, has been granted CREATE TABLE permissions. Neither of you have been given database owner or system permissions, nor have you been added to any fixed server roles.

Eric has created a table named Traveler that holds information about your company's customers. This table is shown in the exhibit (exhibit not available). Travel agents will connect to the database and view the information stored in this table. The database logins for the travel agents have been assigned to the Agent database role. You want the travel agents to be able to view the name and address information from the Traveler table in two columns instead of six. One column should contain the traveler name and the other column should contain the address. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose three)   Grant the Agent role SELECT permissions on the vwTravelers view.

- You are a database developer for a loan servicing company. You are designing database transactions to support a new data entry application. Users of the new database entry application will retrieve loan information from a database. Users will make any necessary changes to the information and save the updated information to the database. How should you design these transactions? Place the following steps in their correct order (use only order choices that apply):

A1. Retrive loan information from the database. B1. The user reviews and modifies a piece of loan information. C1. The user reviews and modifies all of the loan information. D1. Roll back the transaction. E1. Commit the transaction. F1. Save the update information in the database. G1. Begin a transaction. H1. Repeat the modify and update process for each piece of loan information.   A1,C1,G1,F1,E1

- You are a database developer for a loan servicing company. You are designing database transactions to support a new data entry application. Users of the new database entry application will retrieve loan information from a database. Users will make any necessary changes to the information and save the updated information to the database. How should you design these transactions? Place the following steps in their correct order (use only order choices that apply):

A1. Retrive loan information from the database. B1. The user reviews and modifies a piece of loan information. C1. The user reviews and modifies all of the loan information. D1. Roll back the transaction. E1. Commit the transaction. F1. Save the update information in the database. G1. Begin a transaction. H1. Repeat the modify and update process for each piece of loan information.   A1, B1, G1, F1, E1, H1

- You are a database developer for a lumber company. You are performing a one-time migration from a flat-file database to SQL Server 2000. You export the flat-file database to a text file in comma-delimited format. The text file is shown in the Import file exhibit button.

1111, ‘*4 Interior’, 4, ‘Interior Lumber’, 1.12 1112, ‘2*4 Exterior’, 5, ‘Exterior Lumbar’, 1.87 2001, ‘16d galvanized’,2, ‘Bulk Nails’, 2.02 2221, ‘8d Finishing brads’,3, ‘Nails’, 0.01

You need to import this file into SQL Server tables named Product and Category. The product and category tables are shown in the product and Category Tables exhibit.

You want to import the data using the least amount of administrative effort.

What should you do?   Use Data Transformation Services to create two Transform Data tasks. For each task, map the text file columns to the database columns.

- You are a database developer for a mail order company. The company has two SQL Server 2000 computers named CORP1 and CORP2. CORP1 is the online transaction processing server. CORP2 stores historical sales data. CORP2 has been added as a linked server to CORP1.

The manager of the sales department asks you to create a list of customers who have purchased floppy disks. This list will be generated each month for promotional mailings. Floppy disks are representatives in the database with a category ID of 21 You must retrieve this information from a table named SalesHistory. This table is located in the Archive database, which resides on CORP2. You need to execute this query from CORP1.

Which script should you use?   EXEC sp_addlinkedserver ‘CORP2’, ‘SQL Server’ GO SELECT CustomerID FROM CORP2. Archive.dbo.SalesHistory WHERE CategoryID = 21

- You are a database developer for a mail order company. The company has two SQL Server 2000 computers named CORP1 and CORP2. CORP1 is the online transaction processing server. CORP2 stores historical sales data. CORP2 has been added as a linked server to CORP1.

The manager of the sales department asks you to create a list of customers who have purchased floppy disks. This list will be generated each month for promotional mailings. Floppy disks are representatives in the database with a category ID of 21 You must retrieve this information from a table named SalesHistory. This table is located in the Archive database, which resides on CORP2. You need to execute this query from CORP1.

Which script should you use?   SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory WHERE CategoryID = 21

- You are a database developer for a mail order company. The company has two SQL Server 2000 computers named CORP1 and CORP2. CORP1 is the online transaction processing server. CORP2 stores historical sales data. CORP2 has been added as a linked server to CORP1. The manger of the sales department asks you to create a list of customers who have purchased floppy disks. This lists will be generated each month for promotional mailings. Floppy disks are representatives in the database with a category ID of 21.

You must retrieve this information from a table named SalesHistory. This table is located in the Archive database, which resides on CORP2. You need to execute this query from CORP1. Which script should you use?   SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory WHERE CategoryID = 21 (my ans- already got link, so providing the fully qualified object should allow to access to another server's data)

- You are a database developer for a marketing firm. You have designed a quarterly sales view. This view joins several tables and calculates aggregate information. You create a unique index on the view. You want to provide a parameterised query to access the data contained in your indexed view. The output will be used in other SELECT lists. How should you accomplish this goal?   Create an inline user-defined function that accepts the parameter as input.

- You are a database developer for a marketing firm. You have designed a quarterly sales view. This view joins several tables and calculates aggregate information. You create a unique index on the view. You want to provide a parameterised query to access the data contained in your indexed view. The output will be used in other SELECT lists.

How should you accomplish this goal?   Create a scalar user-defined function that accepts the parameter as input

- You are a database developer for a marketing firm. You have designed a quarterly sales view. This view joins several tables and calculates aggregate information. You create a unique index on the view. You want to provide a parameterised query to access the data contained in your indexed view. The output will be used in other SELECT lists.

How should you accomplish this goal?   Create an inline user-defined function that accepts the parameter as input

- You are a database developer for a multinational corporation. The Company has a centralized online transaction processing database located on a SQL Server 2000 computer. This database has a table named Sales, which contains consolidated sales information from the Company's offices. During the last year, more than 150,000 rows have been added to the sales table. Users of the database report that performance during the course of the year has steadily decreased. You need to improve the performance of queries against the Sales table. In the SQL Query Analyzer, which script should you execute?   UPDATE STATISTICS_Sales WITH FULLSCAN ALL

- You are a database developer for a multinational corporation. The Company has a centralized online transaction processing database located on a SQL Server 2000 computer. This database has a table named Sales, which contains consolidated sales information from the Company's offices. During the last year, more than 150,000 rows have been added to the sales table. Users of the database report that performance during the course of the year has steadily decreased. You need to improve the performance of queries against the Sales table. In the SQL Query Analyzer, which script should you execute?   EXEc sp_updatestatics 'resample'

- You are a database developer for a multinational corporation. The Company has a centralized online transaction processing database located on a SQL Server 2000 computer. This database has a table named Sales, which contains consolidated sales information from the Company's offices. During the last year, more than 150,000 rows have been added to the sales table. Users of the database report that performance during the course of the year has steadily decreased. You need to improve the performance of queries against the Sales table. In the SQL Query Analyzer, which script should you execute?   UPDATE STATISTICS Sales WITH FULLSCAN,ALL

- You are a database developer for a multinational corporation. The Company has a centralized online transaction processing database located on a SQL Server 2000 computer. This database has a table named Sales, which contains consolidated sales information from the Company’s offices.

During the last year, more than 150,000 rows have been added to the Sales table. Users of the database report that performance during the course of the year has steadily decreased. You need to improve the performance of queries against the Sales table.

In the SQL query analyzer, which script should you execute?   EXEC sp_updatestats 'resample'

- You are a database developer for a multinational corporation. The Company has a centralized online transaction processing database located on a SQL Server 2000 computer. This database has a table named Sales, which contains consolidated sales information from the Company’s offices.

During the last year, more than 150,000 rows have been added to the Sales table. Users of the database report that performance during the course of the year has steadily decreased. You need to improve the performance of queries against the Sales table.

In the SQL query analyzer, which script should you execute?   UPDATE STATISTICS Sales WITH FULLSCAN ALL

- You are a database developer for a rapidly growing company. The company is expanding into new sales regions each month. As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database. The RegionSales table is shown in an exhibit.

Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions. It must be as easy as possible to extend the solution as new regions and sales associates are added. What should you do?   Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the Security table. Grant EXECUTE permissions on the stored procedures to all sales associates.

- You are a database developer for a rapidly growing company. The company is expanding into new sales regions each month. As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database. The RegionSales table is shown below:

Region Sales RegionsalesID SaleDate CustomerID ProductID RegionID

Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions. It must be as easy as possible to extend the solution as new regions and sales associates are added.

What should you do?   Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the security table. Grant EXECUTE permissions on the stored procedures to all sales associates.

- You are a database developer for a rapidly growing company. The company is expanding into new sales regions each month. As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database. The RegionSales table is shown below:

Region Sales

RegionsalesID SaleDate CustomerID ProductID RegionID

Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions. It must be as easy as possible to extend the solution as new regions and sales associates are added. What should you do?   Create one view on the RegionSales table for each sales region. Grant the sales associates permission to access the views that correspond to the sales region to which they have been assigned.

- You are a database developer for a rapidly growing company. The company is expanding into new sales regions each month. As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database. The RegionSales table is shown below:

Region Sales

RegionsalesID SaleDate CustomerID ProductID RegionID

Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions. It must be as easy as possible to extend the solution as new regions and sales associates are added. What should you do?   Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the security table. Grant EXECUTE permissions on the stored procedures to all sales associates.

- You are a database developer for a sales organization. Your database has a table named Sales that contains summary information regarding the sales orders from salespeople. The sales manager asks you to create a report of the salespeople who had the 20 highest total sales. Which query should you use to accomplish this?   SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDE BY 3 DESC

- You are a database developer for a sales organization. Your database has a table named sales that contains summary information regarding the sales orders from salespeople. The sales manager asks you to create a report of the salespeople who had the 20 highest total sales.

Which query should you use to accomplish this?   SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

- You are a database developer for a sales organization. Your database has a table named sales that contains summary information regarding the sales orders from salespeople. The sales manager asks you to create a report of the salespeople who had the 20 highest total sales.

Which query should you use to accomplish this?   SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drivers. The Order and OrderDetail tables are often joined in queries. You need to tune the performance of the database. What should you do?

(Each correct answer presents part of the solution.)(Choose two.)   Store the data and the clustered index for the OrderDetail table on one filegroup, and create the non-clustered indexes on another filegroup

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drivers. The Order and OrderDetail tables are often joined in queries. You need to tune the performance of the database. What should you do?

(Each correct answer presents part of the solution.)(Choose two.)   Create the order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drivers. The Order and OrderDetail tables are often joined in queries. You need to tune the performance of the database. What should you do?

(Each correct answer presents part of the solution.)(Choose two.)   Create two filegroups that each consist of two disk drivers connected to the same controller.

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stored order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The order and OrderDetail tables are often joined in queries.

You need to tune the performance of the database.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Store the data and the clustered index for the OrderDetail table on one filegroup, and create the nonclustered indexes on another filegroup

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stored order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The order and OrderDetail tables are often joined in queries.

You need to tune the performance of the database.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Create the Order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stored order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The order and OrderDetail tables are often joined in queries.

You need to tune the performance of the database.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Create two filegroups that each consists of two disk drivers connected to the same controller.

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drivers. The Order and OrderDetail tables are often joined in queries.

You need to tune the performance of the database. What should you do (Each correct answer presents part of the solution.)? (Choose two.)   Create the order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup

- You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drivers. The Order and OrderDetail tables are often joined in queries.

You need to tune the performance of the database. What should you do (Each correct answer presents part of the solution.)? (Choose two.)   Create two filegroups that each consist of two disk drivers connected to the same controller.

- You are a database developer for a sport school. You have developed a database which consists of all sport information. This information will be used by 50 different companies that publishes sport information on their website. Each company arranges and displays data in different format. You have been assigned to design the package for data delivery.

Which option will you choose?   Fire a Select query by using FOR XML clause.

- You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in the default snapshot folder on the Distributor. Create a CD from that folder. Distribute the CD to the regional offices.

- You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternate snapshot folder on the server. Create a CD from that folder. Distribute the CD to the regional offices.

- You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternative snapshot folder on the network. Use FTP to distribute the snapshot to the regional offices.

- You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Use transactional replication. Mark all subscriptions for reinitialization.

- You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in the default snapshot folder on the Distributor. Create a CD from that folder. Distribute the CD to the regional offices.

- You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternative snapshot folder on the network. Use FTP to distribute the snapshot to the regional offices.

- You are a database developer for a sporting goods Company. The Company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the Company's products. The main office SQL Server databases is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.

Each quarter, you create a new snapshot file. You see this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternative snapshot folder on the network. Create CD from that folder. Distribute the CD to the regional offices.

- You are a database developer for a sporting goods Company. The Company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the Company's products. The main office SQL Server databases is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.

Each quarter, you create a new snapshot file. You see this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternative snapshot folder on the network. Use FTP to distribute the snapshot to the regional offices.

- You are a database developer for a sporting goods Company. The Company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the Company's products. The main office SQL Server databases is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.

Each quarter, you create a new snapshot file. You see this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternative snapshot folder on the network. Create CD from that folder. Distribute the CD to the regional offices.

- You are a database developer for a sporting goods Company. The Company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the Company's products. The main office SQL Server databases is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.

Each quarter, you create a new snapshot file. You see this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense. What should you do?   Create a compressed snapshot in an alternative snapshot folder on the network. Use FTP to distribute the snapshot to the regional offices.

- You are a database developer for a SQL Server 2000 database. The database is in the default configuration. The number of users accessing the database has increased from 100 to 1,000 in the last month. Users inform you that they are receiving error messages frequently. The following is an example of an error message that was received:

"Transaction (Process ID 56) was deadlocked on [lock] resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

What should you do?   Use SQL Profiler to capture deadlock events.

- You are a database developer for a SQL Server 2000 database. The database is in the default configuration. The number of users accessing the database has increased from 100 to 1,000 in the last month. Users inform you that they are receiving error messages frequently. The following is an example of an error message that was received:

Transaction (Process ID 56) was deadlocked on [lock] resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What should you do?   Use SQL Profiler to capture deadlock events

- You are a database developer for a SQL Server 2000 database. You are planning to add new indexes, drop some indexes, and change other indexes to composite and covering indexes. For documentation purposes, you must create a report that shows the indexes used by queries before and after you make changes. What should you do?   Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.

- You are a database developer for a SQL Server 2000 database. You have created a stored procedure to produce the EndOfMonthSales report for the sales department.

You issue the following statement to assign permissions to the EndOfMonthSales report:

GRANT EXECUTE ON EndOfMonthSales TO SalesDept

Andrea has just joined the sales department and is a member of the SalesDept role. Andrea is also a member of marketing role, which has been denied access to the EndOfMonthSales report. Andrea is unable to execute the stored procedure. No other sales department employees are experiencing this problem.

What should you do?   Remove Andrea from the Marketing database role

- You are a database developer for a SQL Server 2000 database. You have created a stored procedure to produce the EndOfMonthSales report for the sales department. You issue the following statement to assign permissions to the EndOfMonthSales report:

GRANT EXECUTE ON EndOfMonthSales TO SalesDept

Andrea has just joined the sales department and is a member of the SalesDept role. Andrea is also a member of marketing role, which has been defined with access to the EndOfMonthSales report. Andrea is unable to execute the stored procedure. No other sales department employees are experiencing this problem. What should you do?   Remove Andrea from the Marketing database role.

- You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper. The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database. Your design is shown in an exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do?   Create a new table named Instructors. Include an InstructorID column, and InstructorName column, and an OfficePhone column. Add an InstructorID column to the Courses table.

- You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper. The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database. Your design is shown below:

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?   Create a new table named Instructors. Include an InstructorID column, and InstructorName column, and an OfficePhone column. Add an InstructorID column to the Courses table.

- You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper. The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database. Your design is shown below:

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?   Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.

- You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size. You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C. Which script should you use to create the database?   CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = 'c:\data\contacts.mdf', SIZE = 1GB)

- You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size. You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C.

Which script should you use to create the database?   CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = ‘c:\data\contacts.mdf’, SIZE = 1GB)

- You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size. You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C. Which script should you use to create the database?   CREATE DATABASE CustomerContacts ON (NAME = Contacts_database, FILENAME = 'c:datacontacts.mdf', SIZE = 10, MAXSIZE = 1GB FILEGROWTH= 5)

- You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size. You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C. Which script should you use to create the database?   CREATE DATABASE CustomerContacts ON (NAME = Contacts_dat, FILENAME = 'c:datacontacts.mdf', SIZE = 1GB)

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown below:

Toysales

Pk ToysaleID ToyID CompanyDiviionID Saledate Sa;eAccount

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other's data. The two departments currently have no access to the data.

Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role. You must ensure that the two departments can view only their own data. Which three actions should you take?

(Each correct answer presents part of the solution.) (Choose three)   Grant SELECT permission on the vwActionFigureSales view to the ActionFigure database role.

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown below:

Toysales Pk ToysaleID ToyID CompanyDiviionID Saledate Sa;eAccount

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other's data. The two departments currently have no access to the data. Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role. You must ensure that the two departments can view only their own data. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose three)   Transfer the ownership of the table and the views to the database owner.

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown below:

Toysales Pk ToysaleID ToyID CompanyDiviionID Saledate Sa;eAccount

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other's data. The two departments currently have no access to the data. Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role. You must ensure that the two departments can view only their own data. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose three)   Grant SELECT permissions on the vwDollSales view to the Doll database role.

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown below:

Toysales Pk ToysaleID ToyID CompanyDiviionID Saledate Sa;eAccount

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other's data. The two departments currently have no access to the data. Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role. You must ensure that the two departments can view only their own data. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose three)   Grant SELECT permission on the vwActionFigureSales view to the ActionFigure database role.

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown in an exhibit.

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other’s data. The two departments currently have no access to the data.

Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role.

You must ensure that the two departments can view only their own data.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Transfer the ownership of the table and the views to the database owner.

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown in an exhibit.

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other’s data. The two departments currently have no access to the data.

Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role.

You must ensure that the two departments can view only their own data.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Grant SELECT permissions on the vwDollSales view to the Doll database role.

- You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role. The ToySales table stored the sales information for all departments in the company. This table is shown in an exhibit.

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls. Employees in the dolls department should be given full access to the data. You have also created a view named vxActionFigureSales to display only the information that pertains to sales of action figures. Employees in the action figures department should be given full access each other’s data. The two departments currently have no access to the data.

Employees in the data department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role.

You must ensure that the two departments can view only their own data.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose three)   Grant SELECT permission on the vxActionFigureSales view to the ActionFigure database role.

- You are a database developer for a toy manufacturer. Each employee of the Company is assigned to either an executive, administrative, or labor position. The home page of the Company intranet displays Company news that is customized for each position type. When an employee logs on to the Company intranet, the home page identifies the employee’s position type and displays the appropriate Company news.

Company news is stored in a table named News, which is located in the corporate database. The script that was used to create the News table is shown in an exhibit.

CREATE TABLE News ( NewsID int NOT NULL, NewsText varchar (8000) NOT NULL, EmployeePositionType char (15) NOT NULL, DisplayUntil datetime NOT NULL, DateAdded datetime NOT NULL DEFAULT (getdate( )), CONSTRAINT PK_News PRIMARY KEY (NewsID) ) Users of the intranet need to view data in the news table, but do not need to insert, update, or delete data in the table. You need to deliver only the appropriate data to the intranet, based on the employee’s position type. What should you do?   Create a stored procedure that returns the rows that apply to a specified position type

- You are a database developer for a toy manufacturer. Each employee of the company is assigned to either an executive, administrative, or labor position. The home page of the company intranet displays company news that is customized for each position type. When an employee logs on to the company intranet, the home page identifies the employee's position type and displays the appropriate company news.

Company news is stored in a table named News, which is located in the corporate database. The script that was used to create the News table is shown below:

CREATE TABLE News ( NewsID int NOT NULL, NewsText varchar (8000) NOT NULL, EmployeePositionType char (15) NOT NULL, DisplayUntil datetime NOT NULL, DateAdded datetime NOT NULL DEFAULT (getdate( )), CONSTRAINT PK_News PRIMARY KEY (NewsID) )

Users of the intranet need to view data in the news table, but do not need to insert, update, or delete data in the table. You need to deliver only the appropriate data to the intranet, based on the employee's position type.

What should you do?   Create a stored procedure that returns the rows that apply to a specified position type.

- You are a database developer for a travel agency. A table named FlightTimes in the Airlines database contains flight information for all airlines. The travel agency uses an intranet-based application to manage travel reservations. This application retrieves flight information for each airline from the FlightTimes table. Your company primarily works with one particular airline. In the Airlines database, the unique identifier for this airline is 101.The application must be able to request flight times without having to specify a value for the airline. The application should be required to specify a value for the airline only if a different airline's flight times are needed. What should you do?   Create a stored procedure that accepts a parameter with a default value of 101.

- You are a database developer for a travel agency. A table named FlightTimes in the Airlines database contains flight information for all airlines. The travel agency uses an intranet-based application to manage travel reservations. This application retrieves flight information for each airline from the FlightTimes table. Your company primarily works with one particular airline. In the Airlines database, the unique identifier for this airline is 101.

The application must be able to request fight times without having to specify a value for the airline. The application should be required to specify a value for the airline only if a different airline’s flight times are needed. What should you do?   Create a user-defined function that accepts a parameter with a default value of 101.

- You are a database developer for a travel agency. A table named FlightTimes in the Airlines database contains flight information for all airlines. The travel agency uses an intranet-based application to manage travel reservations. This application retrieves flight information for each airline from the FlightTimes table. Your company primarily works with one particular airline. In the Airlines database, the unique identifier for this airline is 101.

The application must be able to request fight times without having to specify a value for the airline. The application should be required to specify a value for the airline only if a different airline’s flight times are needed. What should you do?   Create a stored procedure that accepts a parameter with a default value of 101.

- You are a database developer for a travel agency. A table named FlightTimes in the Airlines database contains flight information for all airlines. The travel agency uses an intranet-based application to manage travel reservations. This application retrieves flight information for each airline from the FlightTimes table. Your company primarily works with one particular airline. In the Airlines database, the unique identifier for this airline is 101.The application must be able to request flight times without having to specify a value for the airline. The application should be required to specify a value for the airline only if a different airline's flight times are needed. What should you do?   Create a stored procedure that accepts a parameter with a default value of 101.

- You are a database developer for a vacuum sales company. The company has a database named sales that contains tables named VacuumSales and Employee. Sales information is stored in the VacumSales table. Employee information is stored in the Employee table. The Employee table has a bit column named IsActive. This column indicates whether an employee is currently employed. The Employee table also has a column named EmployeeID that uniquely identifies each employee. All sales entered into the VacuumSales table must contain an employee ID of a currently employed employee.

How should you enforce this requirement?   Add a FOR INSERT trigger on the VacuumSales table. In the trigger, join the Employee table with the inserted tabled based on the EmployeeID column, and test the IsActive column.

- You are a database developer for a vacuum sales company. The company has a database named Sales that contains tables named VacuumSales and Employee. Sales information is stored in the VacuumSales table. Employee information is stored in the Employee table. The Employee table has a bit column named IsActive. This column indicates whether an employee is currently employed. The Employee table also has a column named EmployeeID that uniquely identifies each employee. All sales entered into the VacuumSales table must contain an employee ID of a currently employed employee. How should you enforce this requirement?   Add a FOR INSERT trigger on the Vacuumsales table. In the trigger, join the Employee table with the inserted tabled based on the EmployeeID column, and test the IsActive.

- You are a database developer for a vacuum sales company. The company has a database named Sales that contains tables named VacuumSales and Employee. Sales information is stored in the VacuumSales table. Employee information is stored in the Employee table. The Employee table has a bit column named IsActive. This column indicates whether an employee is currently employed. The Employee table also has a column named EmployeeID that uniquely identifies each employee. All sales entered into the VacuumSales table must contain an employee ID of a currently employed employee. How should you enforce this requirement?   Add a FOR INSERT trigger on the Vacuumsales table. In the trigger, join the Employee table with the inserted tabled based on the EmployeeID column, and test the IsActive.

- You are a database developer for Adventure Works. A large amount of data has been exported from a human resources application to a text file. The format file that was used to export the human resources data is shown below:

8.0 5 1 SQLINT 0 4 "," 1 EmployeeID "" 2 SQLCHAR 0 50 "," 2 FirstName SQL.Latin1_Gen?K 3 SQLCHAR 0 50 "," 3 LastName SQL.Latin1_Gen?K 4 SQLCHAR 0 10 "," 4 SSN SQL.Latin1_Gen?K 5 SQLDATETIME 0 8 "," 5 Hire_Date ""

You need to import that data programmatically into a table named Employee. The Employee table is shown below: You need to run this import as quickly as possible. What should you do?   Use data transformation services and Microsoft Visual Basic Scripting edition to create a Package object. Create a connection object for the text file. Add a BulkInsertTask object to the Package object. Use the Execute method of the package object to load the data.

- You are a database developer for Adventure Works. A large amount of data has been exported from a human resources application to a text file. The format file that was used to export the human resources data is shown below:

8.0 5 1 SQLINT 0 4 "," 1 EmployeeID "" 2 SQLCHAR 0 50 "," 2 FirstName SQL.Latin1_Gen?K 3 SQLCHAR 0 50 "," 3 LastName SQL.Latin1_Gen?K 4 SQLCHAR 0 10 "," 4 SSN SQL.Latin1_Gen?K 5 SQLDATETIME 0 8 "," 5 Hire_Date ""

You need to import that data programmatically into a table named Employee. The Employee table is shown below: You need to run this import as quickly as possible. What should you do?   Use data transformation services and Microsoft Visual Basic Scripting edition to create a Package object. Create a connection object for the text file. Add a BulkInsertTask object to the Package object. Use the Execute method of the package object to load the data.

- You are a database developer for Adventure Works. A large amount of data has been exported from a human resources application to a text file. The format file that was used to export the human resources data is shown in the Format File exhibit.

Format File 1 SQLINT 0 4 “,” 1 EmployeeID “” 2 SQLCHAR 0 50 “,” 2 Firstname SQL_Latin1_General_CP1_AS 3 SQLCHAR 0 50 “,” 3 Lastname SQL_Latin1_General_CP1_AS 4 SQLCHAR 0 10 “,” 4 SSN SQL_Latin1_General_CP1_AS 5 SQLDATETIME 0 8 “” 5 Hiredate “”

You need to import that data programmatically into a table named Employee. The Employee table is shown in an exhibit table.

You need to run this import as quickly as possible. What should you do?   Use Data Transformation Services and Microsoft Visual Basic Scripting edition to create a Package object. Create a Connection object for the text file. Add a BulkInsertTask object to the Package object. Use the Execute method of the Package object to load the data.

- You are a database developer for Adventure Works. You are designing a script for the human resources department that will report yearly wage information. There are three types of employee. Some employees earn an hourly wage, some are salaried, and some are paid commission on each sale that they make. This data is recorded in a table named Wages, which was created by using the following script:

CREATE TABLE Wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, commission decimal NULL, num_sales tinyint NULL )

An employee can have only on type of wage information. You must correctly report each employee’s yearly wage information. Which script should you use?   SELECT CAST (COALESCE (hourly_wage * 40 * 52, Salary, commission * num_sales) AS MONEY) as YearlyWages FROM Wages

- You are a database developer for Adventure Works. You are designing a script for the human resources department that will report yearly wage information. There are three types of employee. Some employees earn an hourly wage, some are salaried, and some are paid commission on each sale that they make. This data is recorded in a table named Wages, which was created by using the following script:

CREATE TABLE Wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, commission decimal NULL, num_sales tinyint NULL )

An employee can have only one type of wage information. You must correctly report each employee's yearly wage information. Which script should you use?   SELECT CAST (COALESCE(hourly_wage*40*52 , salary , commission*num_sales)AS MONEY)as YearlyWages FROM Wages

- You are a database developer for Adventure Works. You are designing a script for the human resources department that will report yearly wage information. There are three types of employee. Some employees earn an hourly wage, some are salaried, and some are paid commission on each sale that they make. This data is recorded in a table named Wages, which was created by using the following script:

CREATE TABLE Wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, commission decimal NULL, num_sales tinyint NULL ) An employee can have only one type of wage information. You must correctly report each employee's yearly wage information. Which script should you use?   SELECT CAST (COALESCE(hourly_wage*40*52 , salary , commission*num_sales)AS MONEY)as YearlyWages FROM Wages

- You are a database developer for an automobile dealership. The company stored its automobiles inventory data in a SQL Server 2000 database. Many of the critical queries in the database join three tables named Make, Model, and Manufacturer. These tables are updated infrequently.

You want to improve the response time of the critical queries.

What should you do?   Create an indexed view on the tables.

- You are a database developer for an automobile dealership. The company stores its automobile inventory data in a SQL Server 2000 database. Many of the critical queries in the database join three tables named Make, Model, and Manufacturer. These tables are updated infrequently. You want to improve the response time of the critical queries. What should you do?   Create an indexed view on the tables.

- You are a database developer for an automobile dealership. You are designing a database to support a Web site that will be used for purchasing automobiles. A person purchasing an automobile from the Web site will be able to customize his or her order by selecting the model and color.

The manufacturer makes four different models of automobiles. The models can be ordered in any one of five colors. A default color is assigned to each model. The models are stored in a table named Models, and the colors are stored in a table named Colors. These tables are shown below:

Modeles

PK ModelID ColorID Fk Reference Color¡¯s colorID ModelName

Color

PK ColorID ColorName

You need to create a list of all possible model and color combinations. Which script should you use?   SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName

- You are a database developer for an automobile dealership. You are designing a database to support a Web site that will be used for purchasing automobiles. A person purchasing an automobile from the Web site will be able to customize his or her order by selecting the model and color.

The manufacturer makes four different models of automobiles. The models can be ordered in any one of five colors. A default color is assigned to each model. The models are stored in a table named Models, and the colors are stored in a table named Colors. These tables are shown below:

Modeles PK ModelID ColorID Fk Reference Color¡¯s colorID ModelName

Color PK ColorID ColorName

You need to create a list of all possible model and color combinations. Which script should you use?   SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName

- You are a database developer for an automobile dealership. You are designing a database to support a WEB site that will be used for purchasing automobiles. A person purchasing an automobile from the web site will be able to customize his or her order by selecting the model and color The manufacturer makes four different models of automobiles. The models can be ordered in any one of five colors. A default color is assigned to each model.

The models are stored in a table named Models, and the colors are stored in a table named Colors. These tables are shown in an exhibit.

You need to create a list of all possible model and color combinations.

Which script should you use?   SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName

- You are a database developer for an electric utility company. When customers fail to pay the balance on a billing statement before the statement due date, the balance of the billing statement needs to be increased by 1 percent each day until the balance is paid. The company needs to track the number of overdue billing statements. You create a stored procedure to update the balances and to report the number of billing statements that are overdue. The stored procedure is shown below:

L1. CREATE PROCEDURE procUpdateBalances L2. AS L3. BEGIN L4. L5. DECLARE @Err int L6. SET @Err = -1 L7. L8. UPDATE Statements L9. SET Balance = Balance 1.01 L10. WHERE DueDate <gatedate( ) L11. L12. SET @Err = @@ERROR L13. L14. If @Err = 0 L15. Return @@ROWCOUNT L16. Else L17. Return @Err L18. L19. END

Each time the stored procedure executes without error, it reports that zero billing statements are overdue. However, you observe that balances are being updated by the procedure. What should you do to correct the problem?   Replace line 5 of the stored procedure with the following: DECLARE @Err int, @Count int Replace lines 12-17 with the following: SELECT @Err = @ERROR, @Count = @@ROWCOUNT IF @Err = 0 Return @Count Else Return @Err

- You are a database developer for an electric utility Company. When customers fail to pay the balance on a billing statement before the statement due date, the balance of the billing statement need to be increased by 1 percent each day until the balance is paid. The Company needs to track the number of overdue billing statements. You create a stored procedure to update the balances and to report the number of billing statements that are overdue. The stored procedure is shown in an exhibit.

Each time the stored procedure executes without error, it reports that zero billing statements are overdue. However, you observe that balances are being updated by the procedure. What should you do to correct the problem?   Replace line 5 of the stored procedure with the following: DECLARE @Err int, @Count int Replace lines 12-17 with the following: SELECT @Err = @@ERROR, @Count = @@ROWCOUNT IF @Err = 0 Return @Count Else Return @Err

- You are a database developer for an electric utility company. When customers fail to pay the balance on a billing statement before the statement due date, the balance of the billing statement needs to be increased by 1 percent each day until the balance is paid. The company needs to track the number of overdue billing statements. You create a stored procedure to update the balances and to report the number of billing statements that are overdue. The stored procedure is shown below:

1. CREATE PROCEDURE procUpdateBalances 2. AS 3. BEGIN 4. 5. DECLARE @Err int 6. SET @Err = -1 7. 8. UPDATE Statements 9. SET Balance = Balance 1.01 10. WHERE DueDate 11. 12. SET @Err = @@ERROR 13. 14. If @Err = 0 15. Return @@ROWCOUNT 16. Else 17. Return @Err 18. 19. END

Each time the stored procedure executes without error, it reports that zero billing statements are overdue. However, you observe that balances are being updated by the procedure. What should you do to correct the problem?   Replace line 5 of the stored procedure with the following: DECLARE @Err int, @Count int Replace lines 12-17 with the following: SELECT @Err = @ERROR, @Count = @@ROWCOUNT IF @Err = 0 Return @Count Else Return @Err

- You are a database developer for an insurance Company. Information about the Company's insurance policies is stored in a SQL Server 2000 database. You create a table named policy for this database by using the script shown in an exhibit.

CREATE TABLE Policy ( PolicyNumber int NOT NULL DEFAULT (0), InsuredLastName char (30) NOT NULL, InsuredFirstName char (20) NOT NULL, InsuredBirthDate dattime NOT NULL, PolicyDate datetime NOT NULL, FaceAmount money NOT NULL, CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber) )

Each time the Company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered.

What should you do?   Create an INSTEAD OF INSERT trigger to generate a new policy number, and include the policy number in the data inserted into the table.

- You are a database developer for an insurance Company. Information about the Company's insurance policies is stored in a SQL Server 2000 database. You create a table named policy for this database by using the script shown in an exhibit.

CREATE TABLE Policy ( PolicyNumber int NOT NULL DEFAULT (0), InsuredLastName char (30) NOT NULL, InsuredFirstName char (20) NOT NULL, InsuredBirthDate dattime NOT NULL, PolicyDate datetime NOT NULL, FaceAmount money NOT NULL, CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber) )

Each time the Company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered.

What should you do?   Create an INSTEAD OF INSERT trigger to generate a new policy number, and include the policy number in the data inserted into the table.

- You are a database developer for an insurance company. Information about the company's insurance policies is stored in a SQL Server 2000 database. You create a table named policy for this database by using the script shown below:

CREATE TABLE Policy ( PolicyNumber int NOT NULL DEFAULT (0), InsuredLastName char (30) NOT NULL, InsuredFirstName char (20) NOT NULL, InsuredBirthDate dattime NOT NULL, PolicyDate datetime NOT NULL, FaceAmount money NOT NULL, CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber) )

Each time the company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered. What should you do?   Create an INSTEAD OF INSERT trigger to generate a new policy number, and include the policy number in the data instead into the table.

- You are a database developer for an insurance company. Information about the company's insurance policies is stored in a SQL Server 2000 database. You create a table named policy for this database by using the script shown below: CREATE TABLE Policy ( PolicyNumber int NOT NULL DEFAULT (0), InsuredLastName char (30) NOT NULL, InsuredFirstName char (20) NOT NULL, InsuredBirthDate dattime NOT NULL, PolicyDate datetime NOT NULL, FaceAmount money NOT NULL, CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber) ) Each time the company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered. What should you do?   Create an INSTEAD OF INSERT trigger to generate a new policy number, and include the policy number in the data instead into the table.

- You are a database developer for an insurance company. The company has a database named Policies. You have designed stored procedures for this database that will use cursors to process large result sets. Analysts who use the stored procedures report that there is a long initial delay before data is displayed to them. After the delay, performance is adequate. Only data analysts, who perform data analysis, use the Policies database. You want to improve the performance of the stored procedures. Which script should you use?   EXEC sp_configure 'cursor threshold', 0

- You are a database developer for an insurance company. The company has a database named Policies. You have designed stored procedures for this database that will use cursors to process large result sets. Analysts who use the stored procedures report that there is a long initial delay before data is displayed to them. After the delay, performance is adequate. Only data analysts, who perform data analysis, use the Policies database. You want to improve the performance of the stored procedures. Which script should you use?   EXEC sp_configure 'cursor threshold', 0

- You are a database developer for an insurance company. The company has a database named Policies. You have designed stored procedures for this database that will use cursors to process large result sets. Analysts who use the stored procedures report that there is a long initial delay before data is displayed to them.

After the delay, performance is adequate. Only data analysts, who perform data analysis, use the Policies database. You want to improve the performance of the stored procedures. Which script should you use?   EXEC sp_configure ‘cursor threshold’, 0

- You are a database developer for an insurance company. The company has one main office and 18 regional offices. Each office has one SQL Server 2000 database. The regional offices are connected to the main office by a high-speed network. The main office database is used to consolidate information from the regional office databases. The table in the main office database are partitioned horizontally. The regional office location is used as part of the primary key for the main office database. You are designing the physical replication model.

What should you do?   Configure the regional offices as Publishers and the man office as a central Subscriber

- You are a database developer for an insurance company. The company has one main office and 18 regional offices. Each office has one SQL Server 2000 database. The regional offices are connected to the main office by a high-speed network. The main office database is used to consolidate information from the regional office databases. The table in the main office database are partitioned horizontally. The regional office location is used as part of the primary key for the main office database. You are designing the physical replication model. What should you do?   Configure the regional offices as Publishers and the man office as a central Subscriber.

- You are a database developer for an insurance company. The company's regional offices transmit their sales information to the company's main office in an XML document. The XML documents are then stored in a table named SalesXML, which is located in a SQL Server 2000 database. The data contained in the XML documents includes the names of insurance agents, the names of insurance policy owners, information about insurance policy beneficiaries, and other detailed information about the insurance policies. You have created tables to store information extracted from the XML documents. You need to extract this information from the XML documents and store it in the tables. What should you do?   Use the OPENXML function to access the data and to insert it into the appropriate tables.

- You are a database developer for an insurance company. The company's regional offices transmit their sales information to the company's main office in an XML document. The XML documents are then stored in a table named SalesXML, which is located in a SQL Server 2000 database. The data contained in the XML documents includes the names of insurance agents, the names of insurance policy owners, information about insurance policy beneficiaries, and other detailed information about the insurance policies. You have created tables to store information extracted from the XML documents. You need to extract this information from the XML documents and store it in the tables. What should you do?   Use the OPENXML function to access the data and to insert it into the appropriate tables.

- You are a database developer for an insurance company. The company's regional offices transmit their sales information to the company's main office in XML document. The XML documents are then stored in a table named SalesXML, which is located in a SQL Server 2000 database. The data contained in the XML documents includes the names of insurance agents, the names of insurance policy owners, information about insurance policy beneficiaries, and other detailed information about the insurance policies. You have created tables to store information extracted from the XML documents.

You need to extract this information from the XML documents and store it in the tables.

What should you do?   Use the OPENXML function to access the data and to insert it into the appropriate tables.

- You are a database developer for an insurance company. The company's regional offices transmit their sales information to the company's main office in XML document. The XML documents are then stored in a table named SalesXML, which is located in a SQL Server 2000 database. The data contained in the XML documents includes the names of insurance agents, the names of insurance policy owners, information about insurance policy beneficiaries, and other detailed information about the insurance policies. You have created tables to store information extracted from the XML documents.

You need to extract this information from the XML documents and store it in the tables.

What should you do?   Use the OPENXML function to access the data and to insert it into the appropriate tables.

- You are a database developer for an insurance Company. The insurance Company has a multi-tier application that is used to enter data about its policies and the owners of the policies. The policy owner information is stored in a table named Owners. The script that was used to create this table is shown in an exhibit.

CREATE TABLE Owners ( OwnerID int IDENTITY (1, 1) NOT NULL, FirstName char(20) NULL, LastName char(30) NULL, BirthDate date NULL, CONSTRAINT PK_Owners PRIMARY KEY (Owner ID) )

When information about policy owners is entered, the owner’s birth date is not included; the database needs to produce a customized error message that can be displayed by the data entry application. You need to design a way for the database to validate that the birth date is supplied and to produce the error message if it is not.

What should you do?   Create a trigger on the Owners table that validates the BirthDate column

- You are a database developer for an insurance company. The insurance company has a multi-tier application that is used to enter data about its policies and the owners of the policies. The policy owner information is stored in a table named Owners. The script that was used to create this table is shown below:

CREATE TABLE Owners ( OwnerID int IDENTITY (1, 1) NOT NULL, FirstName char(20) NULL, LastName char(30) NULL, BirthDate date NULL, CONSTRAINT PK_Owners PRIMARY KEY (Owner ID) )

When information about policy owners is entered, the owner's birth date is not included; the database needs to produce a customized error message that can be displayed by the data entry application. You need to design a way for the database to validate that the birth date is supplied and to produce the error message if it is not. What should you do?   Create a trigger on the Owners table that validates the BirthDate column

- You are a database developer for an insurance company. The insurance company has a multi-tier application that is used to enter data about its policies and the owners of the policies. The policy owner information is stored in a table named Owners. The script that was used to create this table is shown below: CREATE TABLE Owners ( OwnerID int IDENTITY (1, 1) NOT NULL, FirstName char(20) NULL, LastName char(30) NULL, BirthDate date NULL, CONSTRAINT PK_Owners PRIMARY KEY (Owner ID) )

When information about policy owners is entered, the owner's birth date is not included; the database needs to produce a customized error message that can be displayed by the data entry application. You need to design a way for the database to validate that the birth date is supplied and to produce the error message if it is not. What should you do?   Create a trigger on the Owners table that validates the BirthDate column

- You are a database developer for an insurance company. You are informed that database operations such as selects, inserts, and updates, are taking much longer than they were when the database was created a year ago. The previous developer added necessary indexes on the tables when the database was created. Since that time, additional tables and stored procedures have been added to the database. In addition, many of the queries are no longer used.

You want to improve the response time of the database operations as quickly as possible. What should you do?   Run the Index Drop Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.

- You are a database developer for an insurance company. You are informed that database operations such as selects, inserts, and updates, are taking much longer than they were when the database was created a year ago. The previous developer added necessary indexes on the tables when the database was created. Since that time, additional tables and stored procedures have been added to the database. In addition, many of the queries are no longer used.

You want to improve the response time of the database operations as quickly as possible. What should you do?   Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.

- You are a database developer for an insurance Company. You are informed that database operations, such as selects, inserts, and updates, are taking much longer than they were when the database was created a year ago.

The previous developer added necessary indexes on the tables when the database was created. Since that time, additional tables and stored procedures have been added to the database. In addition, many of the queries are no longer used. You want to improve the response time of the database operations as quickly as possible.

What should you do?   Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.

- You are a database developer for an insurance company. You are tuning the performance of queries in SQL Query Analyzer. In the query pane, you create the following query:

SELECT P.PolicyNumber, P.IssueState, AP.Agent FROM Policy AS P JOIN AgentPolicy AS AP ON (P.PolicyNUmber = AP.PolicyNumnber) WHERE IssueState = 'IL' AND PolicyDate BETWEEN '1/1/2000' AND '3/1/2000' AND FaceAmount > 1000000

You choose display estimated execution plan from the query menu and execute the query. The query execution plan that generated is shown below:

You then choose 'Show estimated execution plan' from the query menu and execute the query. The query execution plan that generated is shown in the query execution plan exhibit above. What should you do?   Update statistics on the Policy table

- You are a database developer for an insurance company. You are tuning the performance of queries in SQL Query Analyzer. In the query pane, you create the following query: SELECT P.PolicyNumber, P.IssueState, AP.Agent FROM Policy AS P JOIN AgentPolicy AS AP ON (P.PolicyNUmber = AP.PolicyNumnber) WHERE IssueState = 'IL' AND PolicyDate BETWEEN '1/1/2000' AND '3/1/2000' AND FaceAmount > 1000000

You choose display estimated execution plan from the query menu and execute the query. The query execution plan that generated is shown below: You then choose 'Show estimated execution plan' from the query menu and execute the query. The query execution plan that generated is shown in thequery execution plan exhibit above. What should you do?   Update statistics on the Policy table

- You are a database developer for an insurance company. You create a table named Insured, which will contain information about persons covered by insurance policies. You use the script shown below: CREATE TABLE dbo.Insured ( InsuredID int IDENTITY (1, 1) NOT NULL, PolicyID int NOT NULL, InsuredName char(30) NOT NULL, InsuredBirthDate datetime NOT NULL, CONSTRAINT PK_Insureed PRIMARY KEY CLUSTERED (InsuredID ), CONSTRAINT FK_Insured_Policy FOREIGN KEY ( PolicyID ) REFERENCES dbo.Policy( PlicyID ))

A person covered by an insurance policy is uniquely identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy. You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies. What should you do?   Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.

- You are a database developer for an insurance company. You create a table named Insured, which will contain information about persons covered by insurance policies. You use the script shown below:

CREATE TABLE dbo.Insured ( InsuredID int IDENTITY (1, 1) NOT NULL, PolicyID int NOT NULL, InsuredName char(30) NOT NULL, InsuredBirthDate datetime NOT NULL, CONSTRAINT PK_Insureed PRIMARY KEY CLUSTERED (InsuredID ), CONSTRAINT FK_Insured_Policy FOREIGN KEY ( PolicyID ) REFERENCES dbo.Policy( PlicyID ))

A person covered by an insurance policy is uniquely identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy. You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies. What should you do?   Add the PolicyID, InsuredName, and InsuredBirthDate columns to the primary key.

- You are a database developer for an insurance company. You create a table named Insured, which will contain information about persons covered by insurance policies. You use the script shown below:

CREATE TABLE dbo.Insured ( InsuredID int IDENTITY (1, 1) NOT NULL, PolicyID int NOT NULL, InsuredName char(30) NOT NULL, InsuredBirthDate datetime NOT NULL, CONSTRAINT PK_Insureed PRIMARY KEY CLUSTERED (InsuredID ), CONSTRAINT FK_Insured_Policy FOREIGN KEY ( PolicyID ) REFERENCES dbo.Policy( PlicyID ))

A person covered by an insurance policy is uniquely identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy. You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies. What should you do?   Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.

- You are a database developer for an insurance company. You create a table named Insured, which will contain information about persons covered by insurance policies. You use the script shown in an exhibit to create this table.

CREATE TABLE dbo.Insured ( InsuredID int IDENTITY (1, 1) NOT NULL, PolicyID int NOT NULL, InsuredName char(30) NOT NULL, InsuredBirthDate datetime NOT NULL, CONSTRAINT PK_Insureed PRIMARY KEY CLUSTERED ( InsuredID ), CONSTRAINT FK_Insured_Policy FOREIGN KEY ( PolicyID ) REFERENCES dbo.Policy( PolicyID ) )

A person covered by an insurance policy is unique identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy.

You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies.

What should you do?   Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.

- You are a database developer for an investment brokerage company. The company has a database named Stocks that contains tables named CurrentPrice and PastPrice. The current prices of investment stocks are stored in the CurrentPrice table. Previous stock prices are stored in the PastPrice table. These tables are shown in the CurrentPrice and PastPrice Tables exhibit.

A sample of the data contained in thee tables is shown in the sample Data exhibit.

All of the rows in the CurrentPrice table are updated at the end of the business day, even if the price of the stock has not changed since the previous update. If the stock price has changed since the previous update, then a row must also be inserted into the PastPrice table. You need to design a way for the database to execute this action automatically.

What should you do?   Create an AFTER trigger on the CurrentPrice table that compares the values of the StockPrice column in the inserted and deleted tables. If the values are different, then the trigger will insert a row into the PastPrice table.

- You are a database developer for an investment brokerage company. The company has a database named Stocks that contains tables named CurrentPrice and PastPrice. The current prices of investment stocks are stored in the CurrentPrice table. Previous stock prices are stored in the PastPrice table. These tables are shown below:

CurrentPrice · CurrentPriceID · StockID · StockPrice · LastUpdate

PastPrice · PastPriceID · StockID · StockPrice · PriceUpdateTime

A sample of the data contained in thee tables is shown below:

CurrentPriceID StockID StockPrice LastUpdate 1 4 1 10.0000 2000-09-29 15:00:00.000 2 9 2 25.0000 2000-09-29 15:01:00.000 PastPriceID StockID StockPrice PriceupdateTime 1 2 1 10.5000 2000-09-27 15:00:00.000 2 3 1 10.7500 2000-09-28 15:02:00.000 3 6 2 22.0000 2000-09-26 03:00:00.000 4 7 2 22.5000 2000-09-27 14:59:00.000 5 8 2 24.5000 2000-09-28 15:00:00.000

All of the rows in the CurrentPrice table are updated at the end of the business day, even if the price of the stock has not changed since the previous update. If the stock price has changed since the previous update, then a row must also be inserted into the PastPrice table. You need to design a way for the database to execute this action automatically. What should you do?   Create an AFTER trigger on the CurrentPrice table that compares the values of the StockPrice column in the inserted and deleted tables. If the values are different, then the trigger will insert a row into the PastPrice table.

- You are a database developer for an IT consulting company. You are designing a database to record information about potential consultants. You create a table named CandidateSkills for the database. The table is shown below:

CandidateSkills · CandidateID · SkillID · DataLastUsed · Proficiency

How should you uniquely identify the skills for each consultant?   Create a PRIMARY KEY constraint on the CandidateID column.

- You are a database developer for an IT consulting company. You are designing a database to record information about potential consultants. You create a table named CandidateSkills for the database. The table is shown below:

CandidateSkills · CandidateID · SkillID · DataLastUsed · Proficiency

How should you uniquely identify the skills for each consultant?   Create a PRIMARY KEY constraint on the CandidateID and SkillID columns.

- You are a database developer for an IT consulting company. You are designing a database to record information about potential consultants. You create a table named CandidateSkills for the database. The table is shown in an exhibit.

How should you uniquely identify the skills for each consultant?   Create a PRIMARY KEY constraint on the CandidateID and SkillID columns

- You are a database developer for an online book retailer. Customers use the Company’s web site to place orders for books. As orders are entered, they are inserted into a database named BookOrders. During a nightly batch process, the order information is transferred to a database named Reports. The Reports database includes a table named Orders and a table named LineItem. The Order table contains basic information about the orders. The LineItem table contains information about the individual items in the orders. The Order and LineItem tables are shown in an exhibit.

Customers must be able to use the Company’s web site to view orders stored in the Reports database. Customers should be able to see only their own orders. Customers should not be able to modify the orders. The primary key values of the orders are not relevant to the customers and should not be visible. What should you do?   Create a stored procedure that retrieves the order information for a customer.

- You are a database developer for an online book retailer. Customers use the company's web site to place orders for books. As orders are entered, they are inserted into a database named BookOrders. During a nightly batch process, the order information is transferred to a database named Reports.The Reports database includes a table named orders and a table named LineItem. The Order table contains basic information about the orders. The LineItem table contains information about the individual items in the orders. The order and LineItem tables are shown below:

Customers must be able to use the Company's web site to view orders stored in the Reports database. Customers should be able to see only their own orders. Customers should not be able to modify the orders. The primary key values of the orders are not relevant to the customers and should not be visible. What should you do?   Create a stored procedure that retrieves the order information for a customer.

- You are a database developer for an online book retailer. Customers use the company's web site to place orders for books. As orders are entered, they are inserted into a database named BookOrders. During a nightly batch process, the order information is transferred to a database named Reports.The Reports database includes a table named orders and a table named LineItem. The Order table contains basic information about the orders. The LineItem table contains information about the individual items in the orders. The order and LineItem tables are shown below:

Customers must be able to use the Company's web site to view orders stored in the Reports database. Customers should be able to see only their own orders. Customers should not be able to modify the orders. The primary key values of the orders are not relevant to the customers and should not be visible.

What should you do?   Create a view that displays the order information for a customer.

- You are a database developer for an online book retailer. Customers use the company's web site to place orders for books. As orders are entered, they are inserted into a database named BookOrders. During a nightly batch process, the order information is transferred to a database named Reports.The Reports database includes a table named orders and a table named LineItem. The Order table contains basic information about the orders. The LineItem table contains information about the individual items in the orders. The order and LineItem tables are shown below:

Customers must be able to use the Company's web site to view orders stored in the Reports database. Customers should be able to see only their own orders. Customers should not be able to modify the orders. The primary key values of the orders are not relevant to the customers and should not be visible.

What should you do?   Create a stored procedure that retrieves the order information for a customer.

- You are a database developer for an online brokerage firm. The prices of the stocks owned by customers are maintained in a SQL Server 2000 database.

To allow tracking of the stock price history, all updates of stock prices must be logged. To help correct problems regarding price updates, any error that occur during an update must also be logged. When errors are logged, a message that identifies the stock producing the error must be returned to the client application.

You must ensure that the appropriate conditions are logged and that the appropriate messages are generated.

Which procedure should you use?   CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN DECLARE @Err int, @RCount int, @Msg varchar (50) UPDATE Stocks SET CurrentPrice = @Price WHERE STockID = @StockID AND CurrentPrice <> @Price SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT If @Err <> 0 RAISEERROR (‘Error %d occurred updating Stock %d.’, 10, 1, @Err, @StockID) WITH LOG If @RCount > 0

BEGIN SELECT @Msg = ‘Stock’ + STR (@StockID) + ‘update to’ + STR (@Price) + ‘.’ EXEC master. . xp_logevent 50001, @Msg END END

- You are a database developer for an online brokerage firm. The prices of the stocks owned by customers are maintained in a SQL Server 2000 database. To allow tracking of the stock price history, all updates of stock prices must be logged. To help correct problems regarding price updates, any error that occurs during an update must also be logged. When errors are logged, a message that identifies the stock producing the error must be returned to the client application.

You must ensure that the appropriate conditions are logged and that the appropriate messages are generated. Which procedure should you use?   CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN

DECLARE @Err int, @RCount int, @Msg varchar (50)

UPDATE Stocks SET CurrentPrice = @Price WHERE STockID = @StockID AND CurrentPrice <> @Price

SELECT @Err = @@ERROR, @RCount = @@ROWCOUNBT If @Err <> 0 RAISEERROR ('Error %d occurred updating Stock %d.', 10, 1, @Err, @StockID) WITH LOG If @RCount > 0 BEGIN SELECT @Msg = 'Stock' + STR (@StockID) + 'update to' + STR (@Price) + '.' EXEC master. . xp_logevent 50001, @Msg END

END

- You are a database developer for an online brokerage firm. The prices of the stocks owned by customers are maintained in a SQL Server 2000 database. To allow tracking of the stock price history, all updates of stock prices must be logged. To help correct problems regarding price updates, any error that occurs during an update must also be logged. When errors are logged, a message that identifies the stock producing the error must be returned to the client application.

You must ensure that the appropriate conditions are logged and that the appropriate messages are generated. Which procedure should you use?   CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN DECLARE @Err int, @RCount int, @Msg varchar (50) UPDATE Stocks SET CurrentPrice = @Price WHERE STockID = @StockID AND CurrentPrice <> @Price SELECT @Err = @@ERROR, @RCount = @@ROWCOUNBT If @Err <> 0 RAISEERROR ('Error %d occurred updating Stock %d.', 10, 1, @Err, @StockID) WITH LOG If @RCount > 0 BEGIN SELECT @Msg = 'Stock' + STR (@StockID) + 'update to' + STR (@Price) + '.' EXEC master. . xp_logevent 50001, @Msg END END

- You are a database developer for an online electronics company. The company's product catalog is contained in a table named Products. The Products table is frequently accessed during normal business hours. Modifications to the Products table are written to a table named PendingProductUpdate. The tables are shown below: The PendingProductUpdate table will be used to update the Products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode. You need to create a script that will be used to update the products table. Which script should you use?   UPDATE p1 SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPrice FROM Products P1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

- You are a database developer for an online electronics company. The company’s product catalog is contained in a table named Products. The products table is frequently accesses during normal business hours. Modifications to the products table are written to a table named PendingProductUpdate. These tables are shown in an exhibit.

The PendingProductUpdate table will be used to update the products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode.

You need to create a script that will be used to update the products table.

Which script should you use?   UPDATE Products SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPrice FROM Products p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

- You are a database developer for an online electronics company. The company’s product catalog is contained in a table named Products. The products table is frequently accesses during normal business hours. Modifications to the products table are written to a table named PendingProductUpdate. These tables are shown in an exhibit.

The PendingProductUpdate table will be used to update the products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode.

You need to create a script that will be used to update the products table.

Which script should you use?   UPDATE Products p1 SET [Description] = p2.[Description], UnitPrice = p2.UnitPrice FROM Products, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

- You are a database developer for an online electronics company. The company’s product catalog is contained in a table named Products. The products table is frequently accesses during normal business hours. Modifications to the products table are written to a table named PendingProductUpdate. These tables are shown in an exhibit.

The PendingProductUpdate table will be used to update the products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode.

You need to create a script that will be used to update the products table.

Which script should you use?   UPDATE p1 SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPrice FROM Products p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate

- You are a database developer for an online electronics company. The company's product catalog is contained in a table named Products. The Products table is frequently accessed during normal business hours. Modifications to the Products table are written to a table named PendingProductUpdate. The tables are shown below:

The PendingProductUpdate table will be used to update the Products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode. You need to create a script that will be used to update the products table. Which script should you use?   UPDATE Products SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPriceFROM Product p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

- You are a database developer for an online electronics company. The company's product catalog is contained in a table named Products. The Products table is frequently accessed during normal business hours. Modifications to the Products table are written to a table named PendingProductUpdate. The tables are shown below:

The PendingProductUpdate table will be used to update the Products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode. You need to create a script that will be used to update the products table. Which script should you use?   UPDATE p1 SET p1.[Description]=p2.[Description], p1.UnitPrice = P2.UnitPrice FROM PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

- You are a database developer for an SQL Server 2000 database. You are planning to add new indexes, drop some indexes, and change other indexes to composite and covering indexes. For documentation purposes, you must create a report that shows the indexes used by queries before and after you make changes. What should you do?   Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.

- You are a database developer for an SQL Server 2000 database. You are planning to add new indexes, drop some indexes, and change other indexes to composite and covering indexes. For documentation purposes, you must create a report that shows the indexes used by queries before and after you make changes. What should you do?   Run the Index Tuning Wizard against a Workload file. Use the output for the report

- You are a database developer for Contoso, Ltd. The company has a database named Human Resources that contains information about all employees and office locations. The database also contains information about potential employees and office locations. The tables that contain this information are shown below:Current employees are assigned to a location, and current locations have one or more employees assigned to them. Potential employees are not assigned to a location, and potential office locations do not have any employees assigned to them. You need to create a report to display all current and potential employees and office locations. The report should list each current and potential location, followed by any employees who have been assigned to that location. Potential employees should be listed together.

Which script should you use?   SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e FULL OUTER JOIN LocationAS1 ON e.LocationID= 1.LocationID ORDER BY 1.LocationName, e.LastName, e.FirstName

- You are a database developer for Contoso, Ltd. The company has a database named Human Resources that contains information about all employees and office locations. The database also contains information about potential employees and office locations. The tables that contain this information are shown below:Current employees are assigned to a location, and current locations have one or more employees assigned to them. Potential employees are not assigned to a location, and potential office locations do not have any employees assigned to them. You need to create a report to display all current and potential employees and office locations. The report should list each current and potential location, followed by any employees who have been assigned to that location. Potential employees should be listed together. Which script should you use?   SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e LEFT OUTER JOIN LocationAS1 ON e.LocationID= 1.LocationID ORDER BY 1.LocationName, e.LastName, e.FirstName

- You are a database developer for Contoso, Ltd. The company has a database named Human Resources that contains information about all employees and office locations. The database also contains information about potential employees and office locations. The tables that contain this information are shown below:Current employees are assigned to a location, and current locations have one or more employees assigned to them. Potential employees are not assigned to a location, and potential office locations do not have any employees assigned to them. You need to create a report to display all current and potential employees and office locations. The report should list each current and potential location, followed by any employees who have been assigned to that location. Potential employees should be listed together. Which script should you use?   SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e FULL OUTER JOIN LocationAS1 ON e.LocationID= 1.LocationID ORDER BY 1.LocationName, e.LastName, e.FirstName

- You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contains a table name Customers, which has 500,000 rows. The script that was used to create the Customers table is shown in an exhibit.

CREATE TABLE Customers ( CustomerID int IDENTITY NOT NULL, CompanyName varchar(40) NOT NULL, ContactName varchar(30) NULL, ContactTitle varchar(30) NULL, Address varchar(60) NULL, City varchar(15) NULL, Region varchar(15) NULL, PostalCode varchar(10) NULL, Country varchar(15) NULL, Phone varchar(24) NULL, Fax varchar(24) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID) )

Many critical queries are executed against the table, which select customer records based on the City and Region columns. Very few customers live in the same city as each other, but many live in same region as each other.

How should you optimize the performance of these queries?   Create a composite, nonclustered index on the City and Region columns, and use City as the first column in the index.

- You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contains a table named Customers, which has 500,000 rows. The script that was used to create the Customers table is shown in the exhibit below:

CREATE TABLE Customers ( CustomerID int IDENTITY NOT NULL, CompanyName varchar(40) NOT NULL, ContactName varchar(30) NULL, ContactTitle varchar(30) NULL, Address varchar(60) NULL, City varchar(15) NULL, Region varchar(15) NULL, PostalCode varchar(10) NULL, Country varchar(15) NULL, Phone varchar(24) NULL, Fax varchar(24) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID) )

Many critical queries are executed against the table, which select customer records based on the City and Region columns. Very few customers live in the same region as each other. How should you optimize the performance of these queries?   Create a composite, nonclustered index on the City and Region columns, and use City as the first column in the index.

- You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contains a table named Customers, which has 500,000 rows. The script that was used to create the Customers table is shown in the exhibit below:

CREATE TABLE Customers ( CustomerID int IDENTITY NOT NULL, CompanyName varchar(40) NOT NULL, ContactName varchar(30) NULL, ContactTitle varchar(30) NULL, Address varchar(60) NULL, City varchar(15) NULL, Region varchar(15) NULL, PostalCode varchar(10) NULL, Country varchar(15) NULL, Phone varchar(24) NULL, Fax varchar(24) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID) )

Many critical queries are executed against the table, which select customer records based on the City and Region columns. Very few customers live in the same region as each other. How should you optimize the performance of these queries?   Create a composite, nonclustered index on the City and Region columns, and use Region as the first column in the index.

- You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contains a table named Customers, which has 500,000 rows. The script that was used to create the Customers table is shown in the exhibit below:

CREATE TABLE Customers ( CustomerID int IDENTITY NOT NULL, CompanyName varchar(40) NOT NULL, ContactName varchar(30) NULL, ContactTitle varchar(30) NULL, Address varchar(60) NULL, City varchar(15) NULL, Region varchar(15) NULL, PostalCode varchar(10) NULL, Country varchar(15) NULL, Phone varchar(24) NULL, Fax varchar(24) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID) )

Many critical queries are executed against the table, which select customer records based on the City and Region columns. Very few customers live in the same region as each other. How should you optimize the performance of these queries?   Create a view on the Customers table.

- You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contains a table named Customers, which has 500,000 rows. The script that was used to create the Customers table is shown in the exhibit below:

CREATE TABLE Customers ( CustomerID int IDENTITY NOT NULL, CompanyName varchar(40) NOT NULL, ContactName varchar(30) NULL, ContactTitle varchar(30) NULL, Address varchar(60) NULL, City varchar(15) NULL, Region varchar(15) NULL, PostalCode varchar(10) NULL, Country varchar(15) NULL, Phone varchar(24) NULL, Fax varchar(24) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID) )

Many critical queries are executed against the table, which select customer records based on the City and Region columns. Very few customers live in the same region as each other. How should you optimize the performance of these queries?   Create a composite, nonclustered index on the City and Region columns, and use City as the first column in the index.

- You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contains a table named Customers, which has 500,000 rows. The script that was used to create the Customers table is shown in the exhibit below:

CREATE TABLE Customers ( CustomerID int IDENTITY NOT NULL, CompanyName varchar(40) NOT NULL, ContactName varchar(30) NULL, ContactTitle varchar(30) NULL, Address varchar(60) NULL, City varchar(15) NULL, Region varchar(15) NULL, PostalCode varchar(10) NULL, Country varchar(15) NULL, Phone varchar(24) NULL, Fax varchar(24) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID) )

Many critical queries are executed against the table, which select customer records based on the City and Region columns. Very few customers live in the same region as each other. How should you optimize the performance of these queries?   Create a composite, nonclustered index on the City and Region columns, and use Region as the first column in the index.

- You are a database developer for Contoso,Ltd. The company has a database named Human Resources that contains information about all employees and office locations. The database also contains information about potential employees and office locations. The tables that contain this information are shown in an exhibit.

Current employees are assigned to a location, and current locations have one or more employees assigned to them. Potential employees are not assigned to a location, and potential office locations do not have any employees assigned to them.

You need to create a report to display all current and potential employees and office locations. The report should list each current and potential location, followed by any employees who have been assigned to that location. Potential employees should be listed together.

Which script should you use?   SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e FULL OUTER JOIN LocationAS 1 ON e.LocationID= 1.LocationID ORDER BY 1.LocationName, e.LastName, e.FirstName

- You are a database developer for Litware,Inc. You are restructuring the company’s sales database. The database contains customer information in a table named Customers. This table includes a character field named Country that contains the name of the country in which the customer is located. You have created a new table named Country. The scripts that were used to create the Customers and Country tables are shown in an exhibit.

CREATE TABLE dbo.Country ( CountryID int IDENTITY(1,1) NOT NULL, CountryName char(20) NOT NULL, CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID) ) CREATE TABLE dbo.Customers ( CustomerID int NOT NULL, CustomerName char(30) NOT NULL, Country char(20) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID) )

You must move the country information from the Customers table into the new Country tables as quickly as possible.

Which script should you use?   DECLARE @Country char (20) DECLARE cursor_country CURSOR FOR SELECT Country FROM Customers OPEN cursor_country FETCH NEXT FROM cursor_country INTO @Country WHILE (@@FETCH_STATUS <> -1) BEGIN If NOT EXISTS (SELECT countryID FROM Country WHERE CountryName = @Country) INSERT INTO Country (CountryName) VALUES (@Country) FETCH NEXT FROM cursor_country INTO @Country END CLOSE cursor_country DEALLOCATE cursor_country

- You are a database developer for Litware,Inc. You are restructuring the company's sales database. The database contains customer information in a table named customers. This table includes a character field named country that contains the name of the country in which the customer is located. You have created a new table named country. The scripts that were used to create the customers and country tables are shown below:

CREATE TABLE dbo.Country ( CountryID int IDENTITY(1,1) NOT NULL, CountryName char(20) NOT NULL, CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID) ) CREATE TABLE dbo.Customers ( CustomerID int NOT NULL, CustomerName char(30) NOT NULL, Country char(20) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID) )

You must move the country information from the customers table into the new country tables as quickly as possible. Which script should you use?   INSERT INTO Country (CountryName) SELECT DISTINCT Country FORM Customers

- You are a database developer for Litware,Inc. You are restructuring the company's sales database. The database contains customer information in a table named customers. This table includes a character field named country that contains the name of the country in which the customer is located. You have created a new table named country. The scripts that were used to create the customers and country tables are shown below:

CREATE TABLE dbo.Country ( CountryID int IDENTITY(1,1) NOT NULL, CountryName char(20) NOT NULL, CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID) ) CREATE TABLE dbo.Customers ( CustomerID int NOT NULL, CustomerName char(30) NOT NULL, Country char(20) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID) )

You must move the country information from the customers table into the new country tables as quickly as possible. Which script should you use?   DECLARE @Country char (20) DECLARE cursor_country CURSOR FOR SELECT Country FROM Customers OPEN cursor_country FETCH NEXT FROM cursor_country INTO @Country WHERE (@@FETCH_STATUS <> -1) BEGIN If NOT EXISTS ( SELECT countryID FROM Country WHERE CountryName = @Country) INSERT INTO Country (CountryName) VALUES (@Country) FETCH NEXT FROM cursor_country INTO @Country END

- You are a database developer for Litware,Inc. You are restructuring the company's sales database. The database contains customer information in a table named customers. This table includes a character field named country that contains the name of the country in which the customer is located. You have created a new table named country. The scripts that were used to create the customers and country tables are shown below: CREATE TABLE dbo.Country ( CountryID int IDENTITY(1,1) NOT NULL, CountryName char(20) NOT NULL, CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID) ) CREATE TABLE dbo.Customers ( CustomerID int NOT NULL, CustomerName char(30) NOT NULL, Country char(20) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID) )

You must move the country information from the customers table into the new country tables as quickly as possible. Which script should you use?   INSERT INTO Country (CountryName) SELECT DISTINCT Country FORM Customers

- You are a database developer for Litware,Inc. You are restructuring the company's sales database. The database contains customer information in a table named customers. This table includes a character field named country that contains the name of the country in which the customer is located. You have created a new table named country. The scripts that were used to create the customers and country tables are shown below: CREATE TABLE dbo.Country ( CountryID int IDENTITY(1,1) NOT NULL, CountryName char(20) NOT NULL, CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID) ) CREATE TABLE dbo.Customers ( CustomerID int NOT NULL, CustomerName char(30) NOT NULL, Country char(20) NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID) )

You must move the country information from the customers table into the new country tables as quickly as possible. Which script should you use?   DECLARE @Country char (20)

DECLARE cursor_country CURSOR FOR SELECT Country FROM Customers OPEN cursor_country FETCH NEXT FROM cursor_country INTO @Country

WHERE (@@FETCH_STATUS <> -1) BEGIN If NOT EXISTS ( SELECT countryID FROM Country WHERE CountryName = @Country) INSERT INTO Country (CountryName) VALUES (@Country) FETCH NEXT FROM cursor_country INTO @Country END

- You are a database developer for Luce Publishing. The company stores its sales data in a SQL Server 2000 database. This database contains a table named Orders. There is currently a clustered index on the table, which is generated by using a customer's name and the current date. The Orders table currently contains 750,000 rows, and the number of rows increased by 5 percent each week. The company plans to launch a promotion next week that will increase the volume of inserts to the Orders table by 50 percent. You want to optimize inserts to the Orders table during the promotion. What should you do?   Rebuild the clustered index with a FILLFACTOR of 50.

- You are a database developer for Lucerne Publishing. You are designing a human resources database that contains tables named Employee and Salary.

You interview users and discover the following information:

• The Employee table will often be joined with the Salary table on the EmployeeID column • Individual records in the Employee table will be selected by social security number (SSN) • A list of employees will be created. The list will be produced in alphabetical order by last name, and then followed by first name.

You need to design the indexes for the tables while optimizing the performance of the indexes.

Which three scripts should you use (Each correct answer presents part of the solution)? (Choose three)   CREATE CLUSTERED INDEX [IX_EmployeeName] ON [dbo].[Employee] ([LastName], [FirstName])

- You are a database developer for Lucerne Publishing. You are designing a human resources database that contains tables named Employee and Salary.

You interview users and discover the following information:

• The Employee table will often be joined with the Salary table on the EmployeeID column • Individual records in the Employee table will be selected by social security number (SSN) • A list of employees will be created. The list will be produced in alphabetical order by last name, and then followed by first name.

You need to design the indexes for the tables while optimizing the performance of the indexes.

Which three scripts should you use (Each correct answer presents part of the solution)? (Choose three)   CREATE UNIQUE INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID])

- You are a database developer for Lucerne Publishing. You are designing a human resources database that contains tables named Employee and Salary.

You interview users and discover the following information:

• The Employee table will often be joined with the Salary table on the EmployeeID column • Individual records in the Employee table will be selected by social security number (SSN) • A list of employees will be created. The list will be produced in alphabetical order by last name, and then followed by first name.

You need to design the indexes for the tables while optimizing the performance of the indexes.

Which three scripts should you use (Each correct answer presents part of the solution)? (Choose three)   CREATE UNIQUE INDEX [IX_EmployeeSSN] ON [dbo].[Employee] ([SSN])

- You are a database developer for Proseware,Inc. The company has a database that contains information about companies located within specific postal codes. This information is contained in the Company table within this database. Currently, the database contains company data for five different postal codes. The number of companies in a specific postal code currently ranges from 10 to 5,000. More companies and postal codes will be added to the database over time.

You are creating a query to retrieve information from the database. You need to accommodate new data by making only minimal changes to the database. The performance of your query must not be affected by the number of companies returned. You want to create a query that performs consistently and minimizes future maintenance. What should you do?   Create a stored procedure that requires a postal code as a parameter. Include the WITH Recompile option when the procedure is created.

- You are a database developer for Proseware,Inc. The company has a database that contains information about companies located within specific postal codes. This information is contained in the company table within this database. Currently, the database contains company data for five different postal codes. The number of companies in a specific postal code currently ranges from 10 to 5,000. More companies and postal code will be added to the database over time. You are creating a query to retrieve information from the database. You need to accommodate new data by making only minimal changes to the database. The performance of your query must not be affected by the number of companies returned. You want to create a query that performs consistently and minimizes future maintenance.

What should you do?   Create a stored procedure that requires a postal code as a parameter. Include the WITH Recompile option when the procedure is created.

- You are a database developer for Proseware,Inc. The company has a database that contains information about companies located within specific postal codes. This information is contained in the Company table within this database. Currently, the database contains company data for five different postal codes. The number of companies in a specific postal code currently ranges from 10 to 5,000. More companies and postal codes will be added to the database over time.

You are creating a query to retrieve information from the database. You need to accommodate new data by making only minimal changes to the database. The performance of your query must not be affected by the number of companies returned. You want to create a query that performs consistently and minimizes future maintenance. What should you do?   Create a stored procedure that requires a postal code as a parameter. Include the WITH Recompile option when the procedure is created.

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the company. This database will contain all employee records and demographics information. The company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes. The scripts that will be used to create the indexes are shown below: ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full. Which two parameters should you specify for the CREATE DATABASE statement?

(Each correct answer presents part of the solution.)(Choose two)   SIZE= 2048KB

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the company. This database will contain all employee records and demographics information. The company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes. The scripts that will be used to create the indexes are shown below: ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full. Which two parameters should you specify for the CREATE DATABASE statement?

(Each correct answer presents part of the solution.)(Choose two)   FILEGROWTH = 5%

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the Company. This database will contain all employee records and demographics information. The Company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the Company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.

The schema for the human resources database is shown in the human resources schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Scripts exhibit.

Human Resource Schema ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full.

Which two parameters should you specify for the CREATE DATABASE statement (Each correct answer presents part of the solution)? (Choose two)   SIZE= 2048KB

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the Company. This database will contain all employee records and demographics information. The Company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the Company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.

The schema for the human resources database is shown in the human resources schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Scripts exhibit.

Human Resource Schema ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full.

Which two parameters should you specify for the CREATE DATABASE statement (Each correct answer presents part of the solution)? (Choose two)   FILEGROWTH = 5%

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the company. This database will contain all employee records and demographics information. The company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.

The scripts that will be used to create the indexes are shown below:

ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full. Which two parameters should you specify for the CREATE DATABASE statement? Each correct answer presents part of the solution. (Choose two)   SIZE= 2048KB

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the company. This database will contain all employee records and demographics information. The company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.

The scripts that will be used to create the indexes are shown below:

ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full. Which two parameters should you specify for the CREATE DATABASE statement? Each correct answer presents part of the solution. (Choose two)   FILEGROWTH = 5%

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the Company. This database will contain all employee records and demographics information. The Company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the Company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.

The schema for the human resources database is shown in the human resources schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Scripts exhibit.

Human Resource Schema ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full.

Which two parameters should you specify for the CREATE DATABASE statement (Each correct answer presents part of the solution)? (Choose two)   SIZE= 2048KB

- You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the Company. This database will contain all employee records and demographics information. The Company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the Company, all of their records must be retained for auditing purposes. Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.

The schema for the human resources database is shown in the human resources schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Scripts exhibit.

Human Resource Schema ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED ([EmployeeID]) WITH FILLFACTOR = 90 GO

You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full.

Which two parameters should you specify for the CREATE DATABASE statement (Each correct answer presents part of the solution)? (Choose two)   FILEGROWTH = 5%

- You are a database developer for Proseware,Inc. You are implementing a database for the database of the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and E-mailAddress.

You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?   Add CHECK constraints on the HomePhone and BusinessPhone columns that prevent null values from being entered into the HomePhone or BusinessPhone columns.

- You are a database developer for Proseware,Inc. You are implementing a database for the database of the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and E-mailAddress.

You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?   Add CHECK constraints on the EmpoyeeContact table that prevent null values from being entered into the HomePhone or BusinessPhone columns.

- You are a database developer for Proseware,Inc. You are implementing a database for the database of the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and E-mailAddress.

You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?   Add CHECK constraint on the table to validate that a least one of the values entered into theHomePhone and BusinessPhone columns is no-null.

- You are a database developer for Proseware,Inc. You are implementing a database for the database of the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and E-mailAddress.

You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?   Add CHECK constraints on the HomePhone and BusinessPhone columns that prevent null values from being entered into either columns.

- You are a database developer for Prosware,Inc. You are implementing a database for the database of the Company’s human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns:

HomePhone, BusinessPhone, FaxNumber, and E-mailAddress

You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column.

What should you do?   Add CHECK constraints on the HomePhone and BusinessPhone columns that prevent null values from being entered into the columns

- You are a database developer for Prosware,Inc. You are implementing a database for the database of the Company’s human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns:

HomePhone, BusinessPhone, FaxNumber, and E-mailAddress

You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column.

What should you do?   Add a CHECK constraint on the tabe to validate that at least one of the values entered into the HomePhone and BusinessPhone columns is non-null

- You are a database developer for Southridge Video. The company stores its sales information in a SQL Server 2000 database. You are asked to delete order records from this database that are more than five years old. To delete the records, you execute the following statement in SQL query analyzer:

DELETE FROM Orders WHERE OrderDate < (DATEADD (year, -5, getdate())) You close SQL Query Analyzer after you receive the following message: (29979 row(s) affected)

You examine the table and find that the old records are still in the table. The current connection properties are shown in an exhibit.

What should you do?   Execute a SET IMPLICIT_TRANSACTIONS OFF statement.

- You are a database developer for Southridge Video. The company stores its sales information in a SQL Server 2000 database. You are asked to delete order records from this database that are more than five years old. To delete the records, you execute the following statement in SQL Query Analyzer: DELETE FROM Orders WHERE OrderDate <DATEADD (year, -5, getdate()) You close SQL Query Analyzer after you receive the following message: (29979 row(s) affected)

You examine the table and find that the old records are still in the table. The Current Connection properties are configured as follows:

Set NoCount: Unchecked Set NoExec Unchecked Set ParseOnly Unchecked Set concat_null_fields_null Checked Set ArithAbort Checked Set ShowPlan_text Unchecked Set StatisticsTime Unchecked Set Statistics10 Unchecked Set RowCount 0 Set ansi_default Checked Set ansi_nulls Checked Set ansi_null_dlt_on Checked Set ansi_padding Checked Set ansi_warning Checked Set cursor_close_on_commit Unchecked Set implicit_transactions Checked Set QuotedIdentities Unchecked

What should you do?   Execute a SET IMPLICIT_TRANSACTIONS OFF statement.

- You are a database developer for Southridge Video. The company stores its sales information in a SQL Server 2000 database. You are asked to delete order records from this database that are more than five years old. To delete the records, you execute the following statement in SQL Query Analyzer: DELETE FROM Orders WHERE OrderDate You close SQL Query Analyzer after you receive the following message:

(29979 row(s) affected)

You examine the table and find that the old records are still in the table. The Current Connection properties are configured as follows:

Set NoCount: Unchecked Set NoExec Unchecked Set ParseOnly Unchecked Set concat_null_fields_null Checked Set ArithAbort Checked Set ShowPlan_text Unchecked Set StatisticsTime Unchecked Set Statistics10 Unchecked Set RowCount 0 Set ansi_default Checked Set ansi_nulls Checked Set ansi_null_dlt_on Checked Set ansi_padding Checked Set ansi_warning Checked Set cursor_close_on_commit Unchecked Set implicit_transactions Unchecked Set QuotedIdentities Unchecked

What should you do?   Execute a SET IMPLICIT_TRANSACTIONS OFF statement.

- You are a database developer for Tailspin Toys (or World of Toys). You have two SQL Server 2000 computers named CORP1 (or SQL1) and CORP2 (or SQL2). Both of these computers use SQL Server authentication. CORP2 stores data that has been archived from CORP1. At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose Three)   Create a stored procedure that will use the fully qualified table name on CORP2 to retrieve the data.

- You are a database developer for Tailspin Toys (or World of Toys). You have two SQL Server 2000 computers named CORP1 (or SQL1) and CORP2 (or SQL2). Both of these computers use SQL Server authentication. CORP2 stores data that has been archived from CORP1. At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose Three)   On CORP1, execute the sp_addlinkedserver system stored procedure.

- You are a database developer for Tailspin Toys (or World of Toys). You have two SQL Server 2000 computers named CORP1 (or SQL1) and CORP2 (or SQL2). Both of these computers use SQL Server authentication. CORP2 stores data that has been archived from CORP1. At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible. Which three actions should you take (Each correct answer presents part of the solution.)? (Choose Three)   On CORP1, execute the sp_addlinkedsrvlogin system stored procedure.

- You are a database developer for Tailspin Toys. You have two SQL Server 2000 computers named CORP1 and CORP2. Both of these computers user SQL server Authentication. CORP2 stores data that has been archived from CORP1. At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose Three)   Create a stored procedure that will use the fully qualified table name on CORP2 to retrieve the data.

- You are a database developer for Tailspin Toys. You have two SQL Server 2000 computers named CORP1 and CORP2. Both of these computers user SQL server Authentication. CORP2 stores data that has been archived from CORP1. At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose Three)   On CORP1, execute the sp_addlinkedserver system stored procedure.

- You are a database developer for Tailspin Toys. You have two SQL Server 2000 computers named CORP1 and CORP2. Both of these computers user SQL server Authentication. CORP2 stores data that has been archived from CORP1. At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible.

Which three actions should you take (Each correct answer presents part of the solution)? (Choose Three)   On CORP1, execute the sp_addlinkedsrvlogin system stored procedure.

- You are a database developer for the A. Datum Corporation. You are creating a database that will store statistics for 15 different high school sports. This information will be used by 50 companies that publish sports information on their Web sites. Each company's Web site arranges and displays the statistics in a different format. You need to package the data for delivery to the companies. What should you do?   Extract the data by using SELECT statements that include the FOR XML clause.

- You are a database developer for Trey Research. You are designing a SQL Server 2000 database that will be distributed with an application to numerous companies. You create several stored procedures in the database that contain confidential information. You want to prevent the companies from viewing this confidential information. What should you do?   Encrypt the text of the store procedures.

- You are a database developer for Trey Research. You are designing a SQL Server 2000 database that will be distributed with an application to numerous companies. You create several stored procedures in the database that contain confidential information. You want to prevent the companies from viewing this confidential information. What should you do?   Encrypt the text of the store procedures.

- You are a database developer for Trey Research. You are designing a SQL Server 2000 database that will be distributed with an application to numerous companies. You create several stored procedures in the database that contain confidential information. You want to prevent the companies from viewing this confidential information.

What should you do?   Encrypt the text of the stored procedures.

- You are a database developer for Trey Research. You create two transactions to support the data entry of employee information into the company's database. One transaction inserts employee name and address information into the database. This transaction is important. The other transaction inserts employee demographics information into the database. This transaction is less important. The database administrator has notified you that the database server occasionally encounters errors during periods of high usage. Each time this occurs, the database server randomly terminates one of the transactions.

You must ensure that when the database server terminates one of these transactions, it never terminates the more important transaction.

What should you do?   Set the DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee demographics information

- You are a database developer for Trey Research. You create two transactions to support the data entry of employee information into the company's database. One transaction inserts employee name and address information into the database. The other transaction inserts employee demographics information into the database. This transaction is less important than the first transaction.

The database administrator has notified you that the database server occasionally encounters errors during periods of high usage. Each time this occurs, the database server randomly terminates one of the transactions. You must ensure that when the database server terminates one of these transactions, it never terminates the more important transaction. What should you do? (Choose all that apply)   Set the DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee demographics information.

- You are a database developer for Trey Research. You create two transactions to support the data entry of employee information into the company's database. One transaction inserts employee name and address information into the database. This transaction is important. The other transaction inserts employee demographics information into the database. This transaction is less important. The database administrator has notified you that the database server occasionally encounters errors during periods of high usage. Each time this occurs, the database server randomly terminates one of the transactions.

You must ensure that when the database server terminates one of these transactions, it never terminates the more important transaction.

What should you do?   Set the DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee demographics information

- You are a database developer for Trey Research. You create two transactions to support the data entry of employee information into the company's database. One transaction inserts employee name and address information into the database. This transaction is important. The other transaction inserts employee demographics information into the database. This transaction is less important. The database administrator has notified you that the database server occasionally encounters errors during periods of high usage. Each time this occurs, the database server randomly terminates one of the transactions.

You must ensure that when the database server terminates one of these transactions, it never terminates the more important transaction.

What should you do?   Set the transaction isolation level to SERIALIZABLE for the transaction that inserts the employee name and address information

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named order and LineItem. The script that was used to create these tables is shown in an exhibit.

CREATE TABLE dbo.Order ( OrderID int NOT NULL, CustomerID int NOT NULL, OrderDate datetime NOT NULL,

CONSTRAINT DF_Order_OrderDate DEFAULT (getdate())FOR OrderDate, CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderID) ) CREATE TABLE dbo.LineITEM ( ItemID int NOT NULL, OrderID INT NOT NULL, ProductID int NOT NULL, Price money NOT NULL, CONSTRAINT PK_LineITEM PRIMARY KEY CLUSTERED (ItemID), CONSTRAINT FK_LineITEM_Order FOREIGN KEY (OrderID) REFERENCES dbo.Order (OrderID) )

The company's auditors have discovered that every item that was ordered on June 1, 2000, was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible.

Which script should you use?   UPDATE 1 SET Price = Price – 10 FROM LineItem AS 1 INNER JOIN [Order] AS o ON 1.OrderID = o.OrderID WHERE o.OrderDate >= ‘6/1/2000’ AND o.OrderDate < ‘6/2/2000’

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named order and LineItem. The script that was used to create these tables is shown in an exhibit.

CREATE TABLE dbo.Order ( OrderID int NOT NULL, CustomerID int NOT NULL, OrderDate datetime NOT NULL,

CONSTRAINT DF_Order_OrderDate DEFAULT (getdate())FOR OrderDate, CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderID) ) CREATE TABLE dbo.LineITEM ( ItemID int NOT NULL, OrderID INT NOT NULL, ProductID int NOT NULL, Price money NOT NULL, CONSTRAINT PK_LineITEM PRIMARY KEY CLUSTERED (ItemID), CONSTRAINT FK_LineITEM_Order FOREIGN KEY (OrderID) REFERENCES dbo.Order (OrderID) )

The company's auditors have discovered that every item that was ordered on June 1, 2000, was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible.

Which script should you use?   UPDATE 1 SET Price = Price – 10 FROM LineItem AS 1 INNER JOIN [Order] AS o ON 1.OrderID = o.OrderID WHERE o.OrderDate >= ‘6/1/2000’ AND o.OrderDate < ‘6/2/2000’

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named order and LineItem. The script that was used to create these tables is shown in an exhibit.

CREATE TABLE dbo.Order ( OrderID int NOT NULL, CustomerID int NOT NULL, OrderDate datetime NOT NULL,

CONSTRAINT DF_Order_OrderDate DEFAULT (getdate())FOR OrderDate, CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderID) ) CREATE TABLE dbo.LineITEM ( ItemID int NOT NULL, OrderID INT NOT NULL, ProductID int NOT NULL, Price money NOT NULL, CONSTRAINT PK_LineITEM PRIMARY KEY CLUSTERED (ItemID), CONSTRAINT FK_LineITEM_Order FOREIGN KEY (OrderID) REFERENCES dbo.Order (OrderID) )

The company's auditors have discovered that every item that was ordered on June 1, 2000, was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible.

Which script should you use?   DECLARE @ItemID int DECLARE items_ursor CUSOR FOR SELECT 1.ItemID FROM LineItem AS 1 INNER JOIN [Order] AS o ON l.OrderID = o.OrderID WHERE o.OrderDate >= ‘6/1/2000’ AND o.OrderDate < ‘6/1/2000’ FOR UPDATE OPEN items_cursor FETCH NEXT FROM Items_cursor INTO @ItemID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE LineItem SET Price = Price – 10 WHERE CURRENT OF items_cursor FETCH NEXT FROM items_cursor INTO @ItemID END CLOSE items_cursor DEALLOCATE items_cursor

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named Order and LineItem. The company's auditors have discovered that every item that was ordered from June 1, 2000 onwards was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible. Which script should you use?   UPDATE 1 SET Price = Price - 10 FROM LineItem AS 1 INER JOIN [Order] AS o ON 1.OrderID = o.OrderID WHERE o.OrderDate >= '6/1/2000'

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named Order and LineItem. The company's auditors have discovered that every item that was ordered from June 1, 2000 onwards was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible. Which script should you use?   DECLARE @OrderID int DECLARE order_cursor CURSOR FOR SELECT ordered FROM [Order] WHERE OrderDate = '6/1/2000' OPEN order_cursor FETCH NEXT FROM order_cursor INTO @OrdeID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE LineItem SET Price = Price - 10 WHERE OrderID= @OrderID FETCH NEXT FROM order_cursor INTO @OrderID END CLOSE order_cursor DEALLOCATE order_cursor

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named Order and LineItem. The company's auditors have discovered that every item that was ordered on June 1, 2000 was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible. Which script should you use?   UPDATE 1 SET Price = Price - 10 FROM LineItem AS 1 INER JOIN [Order] AS o ON 1.OrderID = o.OrderID WHERE o.OrderDate >= '6/1/2000' AND o.OrderDate >= '6/1/2000'

- You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named Order and LineItem. The company's auditors have discovered that every item that was ordered on June 1, 2000 was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible. Which script should you use?   DECLARE @OrderID int DECLARE order_cursor CURSOR FOR SELECT ordered FROM [Order] WHERE OrderDate = '6/1/2000' OPEN order_cursor FETCH NEXT FROM order_cursor INTO @OrdeID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE LineItem SET Price = Price - 10 WHERE OrderID= @OrderID FETCH NEXT FROM order_cursor INTO @OrderID END CLOSE order_cursor DEALLOCATE order_cursor

- You are a database developer for wide world importers. You are creating a database that will store order information. Orders will be entered in a client/server application. Each time a new order is entered, a unique order number must be assigned. Order numbers must be assigned in ascending order. An average of 10, 000 orders will be entered each day.

You create a new table named Orders and add one OrderNumber column to this table.

What should you do next?   Set the data type of the column to uniqueidentifier

- You are a database developer for wide world importers. You are creating a database that will store order information. Orders will be entered in a client/server application. Each time a new order is entered, a unique order number must be assigned. Order numbers must be assigned in ascending order. An average of 10, 000 orders will be entered each day.

You create a new table named Orders and add one OrderNumber column to this table.

What should you do next?   Set the data type of the column to int, and set the IDENTITY property for the column

- You are a database developer for Wide World Importers. You are creating a database that will store order information. Orders will be entered in a client/server application. Each time a new order is entered, a unique order number must be assigned. Order numbers must be assigned in ascending order. An average of 10,000 orders will be entered each day. You create a new table named Orders and add an OrderNumber column to this table. What should you do next?   Set the data type of the column to UniqueIdentifier.

- You are a database developer for Wide World Importers. You are creating a database that will store order information. Orders will be entered in a client/server application. Each time a new order is entered, a unique order number must be assigned. Order numbers must be assigned in ascending order. An average of 10,000 orders will be entered each day. You create a new table named Orders and add an OrderNumber column to this table. What should you do next?   Set the data type of the column to int, and set the IDENTITY property for the column.

- You are a database developer for Wide World Importers. You are creating a table named Orders for the company’s SQL Server 2000 database. Each order contains an orderID, an OrderDate, a customerID, a shipperID, and a ShipDate. Customer services representatives who take the orders must enter the OrderDate, customerID, and shipperID when the order is taken. The orderID must be generated automatically by the database and must be unique. Orders can be taken from existing customers only. Shippers can be selected only from an existing set of shippers. After the customer service representatives complete the order, the order is send to the shopping department for final processing. The shipping department enters the ship date when the order is shipped.

Which script should you use to create the Orders table?   Rebuild the clustered index with a FILLFACTOR of 50

- You are a database developer for Wide World Importers. You are creating a table named Orders for the company's SQL Server 2000 database. Each order contains an order ID, an order date, a customer ID, a shipper ID, and a ship date. Customer services representatives who take the orders must enter the order date, customer ID, and shipper ID when the order is taken. The order ID must be generated automatically by the database and must be unique. Orders can be taken from existing customers only. Shippers can be selected only from an existing set of shippers. After the customer service representatives complete the order, the order is send to the shipping department for final processing. The shipping department enters the ship date when the order is shipped. Which script should you use to create the Orders table?   CREATE TABLE Orders ( Order ID int identity (1, 1)PRIMARY KEY NOT NULL, OrderDate, datetime NOT NULL, CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID), ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID), ShipDate datetime Null )

- You are a database developer for Wingtip Toys. The company stores its sales information in a SQL Server 2000 database. This database contains a table named Orders. You want to move old data from the orders table to an archive table. Before implementing this process, you want to identify how the query optimizer will process the INSERT statement. You execute the following script in SQL Query Analyzer:

SET SHOWPLAN_TEXT ON GO CREATE TABLE Archived_Orders_1995_1999 ( OrderID int, CustomerID char (5), EmployeeID int, OrderDate datetime, ShippedDate datetime )

INSERT INTO Archived_Order_1995_1999 SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate FROM SalesOrders WHERE ShippedDate < DATEADD (year, -1, getdate())

You receive the following error message: Invalid object name 'Archived_Oredrs_1995_1999.'

What should you do to resolve the problem?   Create the Archived_Orders_1995_1999 table before you execute the SET SHOWPLAN_TEXT ON statement.

- You are a database developer for Wingtip Toys. The company stores its sales information in a SQL Server 2000 database. This database contains a table named Orders. You want to move old data from the orders table to an archive table. Before implementing this process, you want to identify how the query optimizer will process the INSERT statement.

You execute the following script in SQL Query Analyzer:

SET SHOWPLAN_TEXT ON GO CREATE TABLE Archived_Orders_1995_1999 ( OrderID int, CustomerID char (5), EmployeeID int, OrderDate datetime, ShippedDate datetime ) INSERT INTO Archived_Order_1995_1999 SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate FROM SalesOrders WHERE ShippedDate < DATEADD (year, -1, getdate())

You receive the following error message: Invalid object name ‘Archived_Orders_1995_1999’.

What should you do to resolve the problem?   Create the Archived_Orders_1995_1999 table before you execute the SET SHOWPLAN_TEXT ON statement

- You are a database developer for Wingtip Toys. The company tracks its inventory in a SQL server 2000 database. You have several queries and stored procedures that are executed on the database indexes to support the queries have been created.

As the number of catalogued inventory items has increased, the execution time of some of the stored procedures has increased significantly. Other queries and procedures that access the same information in the database have not experienced an increase in execution time.

You must restore the performance of the slow-running stored procedures to their original execution times.

What should you do?   Execute the sp_recompile system stored procedure for each of the slow-running procedures

- You are a database developer for Wingtip Toys. The company tracks its inventory in an SQL Server 2000 database. You have several queries and stored procedures that are executed on the database indexes to support the queries that have been created. As the number of catalogued inventory items has increased, the execution time of some of the stored procedures has increased significantly. Other queries and procedures that access the same information in the database have not experienced an increase in execution time. You must restore the performance of the slow-running stored procedures to their original execution times. What should you do?   Always use the WITH RECOMPILE option to execute the slow-running stored procedures.

- You are a database developer for Wingtip Toys. The company tracks its inventory in an SQL Server 2000 database. You have several queries and stored procedures that are executed on the database indexes to support the queries that have been created. As the number of catalogued inventory items has increased, the execution time of some of the stored procedures has increased significantly. Other queries and procedures that access the same information in the database have not experienced an increase in execution time. You must restore the performance of the slow-running stored procedures to their original execution times. What should you do?   Execute the sp_recompile system stored procedure for each of the slow-running procedures.

- You are a database developer for Wingtip Toys. You have created an order entry database that includes two tables, as shown below: Users enter orders into the entry application. When a new order is entered, the data is saved to the Order and LineItem tables in the order entry database. You must ensure that the entire order is saved successfully.

Order LineItem Pk OrderID Pk temID CustomerID OrderID FK_reference order¡¯s OrderID OrderDate ProductID Price

Which script should you use?   BEGIN TRANSACTION INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) IF (@@Error = 0) BEGIN INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION END ELSE ROLLBACK TRANSACTION

- You are a database developer for Wingtip Toys. You have created an order entry database that includes two tables, as shown in an exhibit.

Users enter orders into entry application. When a new order is entered, the data is saved to the order and LineItem tables in the order entry database. You must ensure that the entire order is saved successfully. Which script should you use?   BEGIN TRANSACTION INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) IF (@@Error = 0) BEGIN INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END ELSE ROLLBACK TRANSACTION END

- You are a database developer for WoodGrove Bank (or online advertising agency). The company has a database that contains human resources information. You are designing transactions to support data entry into this database. The script for two of the transactions that you designed are shown below:

Transaction 1 BEGIN TRANSACTION

UPDATE Customer *SET CustomerName=@Name *WHERE CustomerID=@CustID

UPDATE CustomerPhone *SET PhoneNumber=@Phone *WHERE CustomerID=@CustID *AND PhoneType=@PType COMMIT TRANSACTION

Transaction 2 BEGIN TRANSACTION UPDATE CustomerPhone SET PhoneNumber=@Phone *WHERE CustomerID=@CustID AND PhoneType = @PType

UPDATE CustomerAddress SET Street = @Street *WHERE CustomerID=@CustID *AND AddressType=@AType

UPDATE Customer SET CustomerName = @Name WHERE CustomerID = @CustID COMMIT TRANSACTION

While testing these scripts, you discover that the database server occasionally detects a deadlock condition. What should you do?   In Transaction 2, move the UPDATE Customer statement before the UPDATE CustomerPhone statement

- You are a database developer for WoodGrove Bank. The company has a database that contains human resources information. You are designing transactions to support data entry into this database. Script for two of the transactions that you designed are shown in an exhibit.

Transaction 1 Transaction 2 BEGIN TRANSACTION UPDATE Customer SET CustomerName=@Name WHERE CustomerID=@CustID UPDATE CustomerPhone BEGIN TRANSACTION UPDATE CustomerPhone SET PhoneNumber=@Phone WHERE CustomerID=@CustID AND PhoneType = @PType UPDATE CustomerAddress SET Street = SET PhoneNumber=@Phone WHERE CustomerID=@CustID AND PhoneType=@PType COMMIT TRANSACTION @Street WHERE CustomerID=@CustID AND AddressType=@AType UPDATE Customer SET CustomerName = @Name WHERE CustomerID = @CustID COMMIT TRANSACTION

While testing these scripts, you discover that the database server occasionally detects a deadlock condition.

What should you do?   In Transaction 2, move the UPDATE Customer statement before the UPDATE CustomerPhone statement

- You are a database developer for WoodGrove Bank. The company stores its sales data in a SQL Server 2000 database. You want to create an indexed view in this database. To accomplish his, you execute the script shown in an exhibit.

Set NUMERIC_ROUNDABORT OFF GO CREATE VIEW Sales AS SELECT SUM(UnitPrice*Quantity*(1.00-Discount))AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM dbo.OrderDetails AS OD JOIN dbo.Orders AS O ON OD.OrderID = O.OrderID GROUP BY O.OrderDate, OD.ProductID GO CREATE UNIQUE CLUSTERED INDEX IX_Sales ON Sales (OrderDate, ProductID) GO

The index creation fails, and you receive an error message. You want to eliminate the error message and create the index. What should you do?   Add the WITH SCHEMABINDING option to the view.

- You are a database developer for WoodGrove Bank. The company stores its sales data in a SQL Server 2000 database. You want to create an indexed view in this database. To accomplish his, you execute the script shown below:

Set NUMERIC_ROUNDABORT OFF GO CREATE VIEW Sales AS SELECT SUM(UnitPrice*Quantity*(1.00-Discount))AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM dbo.OrderDetails AS OD JOIN dbo.Orders AS O ON OD.OrderID = O.OrderID GROUP BY O.OrderDate, OD.ProductID GO CREATE UNIQUE CLUSTERED INDEX IX_Sales ON Sales (OrderDate, ProductID) GO

The index creation fails, and you receive an error message. You want to eliminate the error message and create the index. What should you do?   Add the WITH SCHEMABINDING option to the view.

- You are a database developer for WoodGrove Bank. The company stores its sales data in a SQL Server 2000 database. You want to create an indexed view in this database. To accomplish this, you execute the script shown below:

Set NUMERIC_ROUNDABORT OFF GO CREATE VIEW Sales AS SELECT SUM(UnitPrice*Quantity*(1.00-Discount))AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM dbo.OrderDetails AS OD JOIN dbo.Orders AS O ON OD.OrderID = O.OrderID GROUP BY O.OrderDate, OD.ProductID GO CREATE UNIQUE CLUSTERED INDEX IX_Sales ON Sales (OrderDate, ProductID) GO The index creation fails, and you receive an error message. You want to eliminate the error message and create the index. What should you do?   Add the WITH SCHEMABINDING option to the view.

- You are a database developer for Woodgrove Bank. You are implementing a process that loads data into a SQL Server 2000 database. As a part of this process, data is temporarily loaded into a table named Staging. When the data load process is complete, the data is deleted from this table. You will never need to recover this deleted data. You need to ensure that the data from the Staging table is deleted as quickly as possible. What should you do?   Use a TRUNCATE TABLE statement to remove the data from the table.

- You are a database developer for Woodgrove Bank. You are implementing a process that loads data into a SQL Server 2000 database. As a part of this process, data is temporarily loaded into a table named Staging. When the data load process is complete, the data is deleted from this table. You will never need to recover this deleted data.

You need to ensure that the data from the Staging table is deleted as quickly as possible.

What should you do?   Use a TRUNCATE TABLE statement to remove the data from the table.

- You are a database developer for your company’s SQL Server 2000 database. This database contains a table named Products and a table named Companies. You want to insert new product information from a linked server into the Products table. The Products table has a FOREIGN KEY constraint that references the Companies table. An UPDATE trigger is defined on the Products table.

You want to load the data as quickly as possible.

What should you do?   Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Products table.

- You are a database developer for your Company’s SQL Server 2000 database. You are deleting objects in the database that are no longer used. You are unable to drop the 1997Sales view. After investigation, you find that the view has the following characteristics:

• There is a clustered index on the view • The sales database role has permissions on the view. • The view uses the WITH SCHEMABINDING option. • A schema-bound inline function references the view • An INSTEAD OF trigger is defined on the view

What should you do before you can drop the view?   Remove the WITH SCHEMABINDING option from the function that is referencing the view.

- You are a database developer for your Company’s SQL Server 2000 online transaction processing database. You have written several stored procedures that will produce critical sales reports. The stored procedures access existing tables, which are indexed.

Before you put the stored procedures in the production environment, you want to ensure optimal performance of the new stored procedures. You also want to ensure that daily operations in the database are not adversely affected.

What should you do?   Use output from the Index Tuning Wizard to identify whether indexes should be added.

- You are a database developer for your company's database named Insurance. You execute the following script in SQL Query Analyzer to retrieve agent and policy information:

SELECT A.LastName, A.FirstName, A.CompanyName, P.PolicyNumber FROM Policy AS P JOIN AgentPolicy AS AP ON (P.PolicyNumber = AP.PolicyNumber) JOIN Agents AS A ON (A.AgentID= AP.AgentID)

The query execution plan that is generated is shown below: The information received when you move the pointer over the Table Scan icon is shown below: Table ScanScan rows from a tablePhysical operation: Table ScanLogical operation: Table ScanRow count 6,969Estimated row size: 105I/O cost: 0.149CPU cost: 0.00774 Number of executives: 1 Cost: 0.157175(12%) Subtree cost: 0.157 Estimated row count: 6. 969Argument:OBJECT:([Insurance].[dbo].[Agents]AS[A])

You want to improve their performance of this query. What should you do?   Create a clustered index on the AgentID column of the Agent table.

- You are a database developer for your company's database named Insurance. You execute the following script in SQL Query Analyzer to retrieve agent and policy information:

SELECT A.LastName, A.FirstName, A.CompanyName, P.PolicyNumber FROM Policy AS P JOIN AgentPolicy AS AP ON (P.PolicyNumber = AP.PolicyNumber) JOIN Agents AS A ON (A.AgentID= AP.AgentID) The query execution plan that is generated is shown in the Query Execution Plan exhibit. The information received when you move the pointer over the Table Scan icon is shown in the Table Scan Information exhibit. Table scan Information

You want to improve their performance of this query.

What should you do?   Create a clustered index on the AgentID column of the Agent table

- You are a database developer for your company's database named Insurance. You execute the following script in SQL Query Analyzer to retrieve agent and policy information:

SELECT A.LastName, A.FirstName, A.CompanyName, P.PolicyNumber FROM Policy AS P JOIN AgentPolicy AS AP ON (P.PolicyNumber = AP.PolicyNumber) JOIN Agents AS A ON (A.AgentID= AP.AgentID)

The query execution plan that is generated is shown below: The information received when you move the pointer over the Table Scan icon is shown below:

Table Scan Scan rows from a table Physical operation: Table Scan Logical operation: Table Scan Row count 6,969 Estimated row size: 105I/O cost: 0.149 CPU cost: 0.00774 Number of executives: 1 Cost: 0.157175(12%) Subtree cost: 0.157 Estimated row count: 6.969 Argument:OBJECT:([Insurance].[dbo].[Agents]AS[A])

You want to improve their performance of this query. What should you do?   Create a clustered index on the AgentID column of the Agent table.

- You are a database developer for your company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown below:

CREAE TBLE Orders ( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetie NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustonmerID)

An application will execute queries like the following to retrieve orders for a customer:

SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = 'WHITC' ORDER BY OrderDate DESC

The query execution plan that is generated is shown below:

You want this query to execute as quickly as possible. What should you do?   Create a nonclustered index on the OrderDate column.

- You are a database developer for your company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown below:

CREAE TBLE Orders ( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetie NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustonmerID)

An application will execute queries like the following to retrieve orders for a customer:

SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = 'WHITC' ORDER BY OrderDate DESC

The query execution plan that is generated is shown below:

You want this query to execute as quickly as possible. What should you do?   Change the nonclustered index on the CustomerID column to include the OrderDate column.

- You are a database developer for your company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown below: CREAE TBLE Orders( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetie NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustonmerID) An application will execute queries like the following to retrieve orders for a customer: SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = 'WHITC' ORDER BY OrderDate DESC

The query execution plan that is generated is shown below: You want this query to execute as quickly as possible. What should you do?   Change the nonclustered index on the CustomerID column to include the OrderDate column.

- You are a database developer for your Company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown in the Script Orders Table exhibit.

CREATE TABLE Orders ( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetime NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustomerID)

An application will execute queries like the following to retrieve orders for a customer:

SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = ‘WHITC’ ORDER BY OrderDate DESC

The query execution plan that is generated is shown in the Query Execution Plan exhibit. .

Query Execution Plan You want this query to execute as quickly as possible.

What should you do?   Change the nonclustered index on the CustomerID column to include the OrderDate column.

- You are a database developer for your Company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown in the Script Orders Table exhibit.

CREATE TABLE Orders ( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetime NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustomerID)

An application will execute queries like the following to retrieve orders for a customer:

SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = ‘WHITC’ ORDER BY OrderDate DESC

The query execution plan that is generated is shown in the Query Execution Plan exhibit. .

Query Execution Plan You want this query to execute as quickly as possible.

What should you do?   Create a nonclustered index on the OrderDate column.

- You are a database developer for your Company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown in the Script Orders Table exhibit.

CREATE TABLE Orders ( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetime NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustomerID)

An application will execute queries like the following to retrieve orders for a customer:

SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = ‘WHITC’ ORDER BY OrderDate DESC

The query execution plan that is generated is shown in the Query Execution Plan exhibit. .

Query Execution Plan You want this query to execute as quickly as possible.

What should you do?   Create a clustered index on the OrderDate column.

- You are a database developer for your Company's database named sales. The database contains a table named Orders. The script that was used to create the table is shown in the Script Orders Table exhibit.

CREATE TABLE Orders ( OrderID int NOT NULL, CustomerID char (5) NOT NULL, OrderDate datetime DEFAULT GETDATE ( ) NULL, ShippedDate datetime NULL, Freight money NULL, ShipName varchar (40) NULL ) GO CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID) GO CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustomerID)

An application will execute queries like the following to retrieve orders for a customer:

SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = ‘WHITC’ ORDER BY OrderDate DESC

The query execution plan that is generated is shown in the Query Execution Plan exhibit. .

Query Execution Plan You want this query to execute as quickly as possible.

What should you do?   Change the nonclustered index on the CustomerID column to include the OrderDate column.

- You are a database developer for your company's Human Resources database. This database includes a table named Employee that contains confidential ID numbers and salaries. The table also includes non-confidential information, such as employee names and addresses. You need to make all the non-confidential information in the Employee table available in XML format to an external application. The external application should be able to specify the exact format of the XML data. You also need to hide the existence of the confidential information from the external application. What should you do?   Create a view that includes only the non-confidential information from the Employee table. Give the external application permission to submit queries against the view.

- You are a database developer for your company's Human Resources database. This database includes a table named Employee that contains confidential ID numbers and salaries. The table also includes nonconfidential information, such as employee names and addresses. You need to make all the non-confidential information in the Employee table available in XML format to an external application. The external application should be able to specify the exact format of the XML data. You also need to hide the existence of the confidential information from the external application.

What should you do?   Create a view that includes only the non-confidential information from the Employee table. Give the external application permission to submit queries against the view.

- You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her from viewing or changing any of the data in the tables. Currently, Andrea belongs only to the Public database role. What should you do?   Add Andrea to the db_ddladmin database role

- You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server. Database activity peaks during the day, when sales representatives enter and update sales transactions. Batch reporting is performed after business hours. The sales representatives report slow updates and inserts. What should you do?   Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL Profiler as input to the index Tuning Wizard.

- You are a database developer for your company's SQL Server 2000 database. This database contains a table named products and a table named companies. You want to insert new product information from a linked server into the products table. The products table has a FOREIGN KEY constraint that references the companies table. An UPDATE trigger is defined on the products table. You want to load the data as quickly as possible. What should you do?   Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the products table.

- You are a database developer for your Company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The sales table contains sales information for all departments in the company. Each department is identified in the table by the DepartmentID column. Most queries against the table are used to find sales for a single department.You want to increase the I/O performance of these queries. However, you do not want to affect the applications that access the table. What should you do?   Create one new table for each department, and move the sales information for each department to that department's table. Add a CHECK constraint on the departmentID columns of the new tables. Create a view on the new tables.

- You are a database developer for your Company's SQL Server 2000 database. You are deleting objects in the database that are no longer used. You are unable to drop the 1997 Sales view. After investigation, you find that the view has the following characteristics: ?P There is a clustered index on the view. ?P The sales database role has permissions on the view. ?P The view uses the WITH SCHEMABINDING option. ?P A schema-bound inline function references the view. ?P An INSTEAD OF trigger is defined on the view. What should you do before you can drop the view?   Remove the WITH SCEMABINDING option from the function that is referencing the view.

- You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her from viewing or changing any of the data in the tables. Currently, Andrea belongs only to the Public database role. What should you do?   Add Andrea to the db_ddladmin database role

- You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her from viewing or changing any of the data in the tables. Currently, Andrea belongs only to the Public database role. What should you do?   Grant Andrea ALTER VIEW permissions

- You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her form viewing or changing any of the data in the tables. Currently, Andrea belongs only to the Public database role.

What should you do?   Add Andrea to the db_ddladmin database role

- You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her form viewing or changing any of the data in the tables. Currently, Andrea belongs only to the Public database role.

What should you do?   Add Andrea to the db_ddladmin database role

- You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server. The database activity peaks during the day, when sales representatives enter and update sales transaction. Batch reporting is performed after business hours. The sales representatives report slow updates and inserts.

What should you do?   Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL profiler as input to the Index Tuning Wizard

- You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server. The database activity peaks during the day, when sales representatives enter and update sales transaction. Batch reporting is performed after business hours. The sales representatives report slow updates and inserts.

What should you do?   Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL profiler as input to the Index Tuning Wizard

- You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server. Database activity peaks during the day, when sales representatives enter and update sales transactions. Batch reporting is performed after business hours. The sales representatives report slow updates and inserts. What should you do?   Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL Profiler as input to the index Tuning Wizard.

- You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server. Database activity peaks during the day, when sales representatives enter and update sales transactions. Batch reporting is performed after business hours. The sales representatives report slow updates and inserts. What should you do?   Increase the value of the min server memory option.

- You are a database developer for your company's SQL Server 2000 database. This database contains a table named products and a table named companies. You want to insert new product information from a linked server into the products table. The products table has a FOREIGN KEY constraint that references the companies table. An UPDATE trigger is defined on the products table. You want to load the data as quickly as possible. What should you do?   Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the products table.

- You are a database developer for your Company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The sales table contains sales information for all departments in the company. Each department is identified in the table by the DepartmentID column. Most queries against the table are used to find sales for a single department.You want to increase the I/O performance of these queries. However, you do not want to affect the applications that access the table. What should you do?   Create one new table for each department, and move the sales information for each department to that department's table. Add a CHECK constraint on the departmentID columns of the new tables. Create a view on the new tables.

- You are a database developer for your Company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The sales table contains sales information for all departments in the Company. Each department is identified in the table by the DepartmentID column. Most queries against the table are used to find sales for a single department. You want to increase the I/O performance of these queries. However, you do not want to affect the applications that access the table.

What should you do?   Create a one new table for each department, and move the sales information for each department to that department’s table. Add a CHECK constraint on the DepartmentID columns of the new tables. Create a view on the new tables.

- You are a database developer for your Company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The sales table contains sales information for all departments in the Company. Each department is identified in the table by the DepartmentID column. Most queries against the table are used to find sales for a single department. You want to increase the I/O performance of these queries. However, you do not want to affect the applications that access the table.

What should you do?   Create a one new table for each department, and move the sales information for each department to that department’s table. Add a CHECK constraint on the DepartmentID columns of the new tables. Create a view on the new tables.

- You are a database developer for your Company's SQL Server 2000 database. You are deleting objects in the database that are no longer used. You are unable to drop the 1997 Sales view. After investigation, you find that the view has the following characteristics: ?P There is a clustered index on the view. ?P The sales database role has permissions on the view. ?P The view uses the WITH SCHEMABINDING option. ?P A schema-bound inline function references the view. ?P An INSTEAD OF trigger is defined on the view.

What should you do before you can drop the view?   Remove the WITH SCEMABINDING option from the function that is referencing the view.

- You are a database developer for your company's SQL Server 2000 database. You update several stored procedures in the database that create new end-of-month reports for the sales department. The stored procedures contain complex queries that retrieve data from three or more tables. All tables in the database have at least one index.

Users have reported that the new end-of-month reports are running much slower than the previous version of the reports. You want to improve the performance of the reports.

What should you do?   Capture the execution of each stored procedure in a SQL Profiler trace. Use the trace file as a workload file for the Index Tuning Wizard.

- You are a database developer for your company's SQL Server 2000 database. You update several stored procedures in the database that create new end-of-month reports for the sales department. The stored procedures contain complex queries that retrieve data from three or more tables. All tables in the database have at least one index. Users have reported that the new end-of-month reports are running much slower than the previous version of the reports. You want to improve the performance of the reports. What should you do?   Capture the execution of each stored procedure in a SQL Profiler trace. Use the trace file as a workload file for the Index Tuning Wizard.

- You are a database developer for your company's SQL Server 2000 database. You update several stored procedures in the database that create new end-of-month reports for the sales department. The stored procedures contain complex queries that retrieve data from three or more tables. All tables in the database have at least one index. Users have reported that the new end-of-month reports are running much slower than the previous version of the reports. You want to improve the performance of the reports. What should you do?   Update the index statistics for the tables used in the stored procedures.

- You are a database developer for your company's SQL Server 2000 database. You update several stored procedures in the database that create new end-of-month reports for the sales department. The stored procedures contain complex queries that retrieve data from three or more tables. All tables in the database have at least one index.

Users have reported that the new end-of-month reports are running much slower than the previous version of the reports. You want to improve the performance of the reports.

What should you do?   Capture the execution of each stored procedure in a SQL Profiler trace. Use the trace file as a workload file for the Index Tuning Wizard.

- You are a database developer for your company's SQL server 2000 database. You use the following script to create a view named Employee in the database:

CREATE VIEW Employee AS SELECT P.SSN, P.LastName, P.FirstName, P. Address, P.City, P.State,   Create an AFTER trigger on the view

- You are a database developer for your company's SQL server 2000 database. You use the following script to create a view named Employee in the database:

CREATE VIEW Employee AS SELECT P.SSN, P.LastName, P.FirstName, P. Address, P.City, P.State,   Create an INSTEAD OF trigger on the view

- You are a database developer for your company's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one nonclustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array.

What should you do next?   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.

- You are a database developer for your company's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one nonclustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array.

What should you do next?   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.

- You are a database developer for your company's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one nonclustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array.

What should you do next?   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.

- You are a database developer for your company's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one nonclustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array. What should you do next?   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.

- You are a database developer for your company's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one nonclustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array. What should you do next?   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.

- You are a database developer for your company's SQL Server 2000 online transaction processing database. You have written several stored procedures that will produce critical sales reports. The stored procedures access existing tables, which are indexed.Before you put the stored procedures in the production environment, you want to ensure optimal performance of the new stored procedures. You also want to ensure that daily operations in the database are not adversely affected. What should you do?   Use output from the Index Tuning Wizard to identify whether indexes should be added.

- You are a database developer for your company's SQL Server 2000 online transaction processing database. You have written several stored procedures that will produce critical sales reports. The stored procedures access existing tables, which are indexed.Before you put the stored procedures in the production environment, you want to ensure optimal performance of the new stored procedures. You also want to ensure that daily operations in the database are not adversely affected. What should you do?   Use output from the Index Tuning Wizard to identify whether indexes should be added.

- You are a database developer fro a lumber company. You are performing a one-time migration from a flat-file database to SQL Server 2000. You export the flat-file database to a text file in comma-delimited format. The text file is shown in the Import file below:

1111, '*4 Interior', 4, 'Interior Lumber', 1.12 1112, '2*4 Exterior', 5, 'Exterior Lumbar', 1.87 2001, '16d galvanized',2, 'Bulk Nails', 2.02 2221, '8d Finishing brads',3, 'Nails', 0.01

You need to import this file into SQL Server tables named Product and Category. The product and category tables are shown below: You want to import the data using the least amount of administrative effort.

What should you do?   Use data transformation services to create two Transform Data tasks. For each task, map the text file columns to the database columns.

- You are a database developer fro a lumber company. You are performing a one-time migration from a flat-file database to SQL Server 2000. You export the flat-file database to a text file in comma-delimited format. The text file is shown in the Import file below:

1111, '*4 Interior', 4, 'Interior Lumber', 1.12 1112, '2*4 Exterior', 5, 'Exterior Lumbar', 1.87 2001, '16d galvanized',2, 'Bulk Nails', 2.02 2221, '8d Finishing brads',3, 'Nails', 0.01

You need to import this file into SQL Server tables named Product and Category. The product and category tables are shown below:

You want to import the data using the least amount of administrative effort. What should you do?   Use data transformation services to create two Transform Data tasks. For each task, map the text file columns to the database columns.

- You are a database developer. Andrea is a member of your group. Andrea needs to alter views. But you need to prevent her from viewing and changing the data in the tables. She currently is a member to the public database role.   Add her to the db_ddl admin database role.

- You are a database developer. Andrea is a member of your group. Andrea needs to alter views. But you need to prevent her from viewing and changing the data in the tables. She currently is a member to the public database role.   Grant her create view permission.

- You are a database developer. Andrea is also other member of your group. Andrea needs to alter views. But you need to prevent her from viewing and changing tables.Now she belongs to the public database role.   Add her to the db_ddl admin database role.

- You are a database developer. Andrea is also other member of your group. Andrea needs to alter views. But you need to prevent her from viewing and changing tables.Now she belongs to the public database role.   Grant her create view permission.

- You are a database developer. Andrea is also other member of your group. Andrea needs to alter views. But you need to prevent her from viewing and changing tables.Now she belongs to the public database role.   Grant her ALTER view permission.

- You are a developer for a company that leases trucks. The company has created a Web site that customer can be used to reserve trucks. You are designing the SQL server 2000 database to support the Web site. New truck reservations are inserted into a table named Reservations. Customers who have reserved a truck can return to the Web site and update their reservation. When a reservation is updated, the entire existing reservation must be copied to a table named History. Occasionally, customers will save an existing reservation without actually changing any of the information about the reservation. In this case, the existing reservation should not be copied to the History table. You need to develop a way to create the appropriate entries in the History table. What should you do?   Create a trigger on the reservations table to create the History table entries.

- You are a developer for a company that leases trucks. The company has created a Web site that customer can be used to reserve trucks. You are designing the SQL server 2000 database to support the Web site. New truck reservations are inserted into a table named Reservations. Customers who have reserved a truck can return to the Web site and update their reservation. When a reservation is updated, the entire existing reservation must be copied to a table named History. Occasionally, customers will save an existing reservation without actually changing any of the information about the reservation. In this case, the existing reservation should not be copied to the History table. You need to develop a way to create the appropriate entries in the History table.

What should you do?   Create a trigger on the reservations table to create the History table entries.

- You are a member of a database development team for a telecommunications company. Another developer on the team, Marc, has created a table named Customers in the Corporate database. Because the table contains confidential information, he has granted SELECT permissions on the table only to the other members of your team. You are developing an application that will allow employees in the marketing department to view some of the information in the Customers table. These employees are all members of the Marketing database role. To support this application, you create a view named vwCustomers on the Customers table. After creating the view, you grant SELECT permissions on the view to the Marketing role. When members of the Marketing role attempt to retrieve data from the view, they receive the following error message:

SELECT permission denied on object 'Customers', database 'Corporate', owner 'Mare' You must ensure that the members of the Marketing role can only use the vwCustomers view to access the data in the Customers table. What should you do?   Drop the vwcustomers view. Instruct Marc to re-create the view and to grant SELECT permissions on the view to the marketing role.

- You are a member of a database development team for a telecommunications company. Another developer on the team, Marc, has created a table named Customers in the Corporate database. Because the table contains confidential information, he has granted SELECT permissions on the table only to the other members of your team.

You are developing an application that will allow employees in the marketing department to view some of the information in the Customers table. These employees are all members of the Marketing database role. To support this application, you create a view named vwCustomers on the Customers table. After creating the view, you grant SELECT permissions on the view to the Marketing role. When members of the Marketing role attempt to retrieve data from the view, they receive the following error message:

SELECT permission denied on object 'Customers', database 'Corporate', owner 'Mare'

You must ensure that the members of the Marketing role can only use the vwCustomers view to access the data in the Customers table. What should you do?   Drop the vwcustomers view. Instruct Marc to re-create the view and to grant SELECT permissions on the view to the marketing role.

- You are a member of a database development team for a telecommunications Company. Another developer on the team, Marc, has created a table named Customers in the Corporate database. Because the table contains confidential information, he has granted SELECT permissions on the table only to the other members of your team.

You are developing an application that will allow employees in the marketing department to view some of the information in the customers table. These employees are all members of the Marketing database role. To support this application, you create a view named vwCustomers on the Customers table. After creating the view, you grant SELECT permissions on the view to the Marketing role. When members of the Marketing role attempt to retrieve data from the view, they receive the following error message:

SELECT permission denied on object ‘Customers’, database ‘Corporate’, owner ‘Marc’. You must ensure that the members of the Marketing role can only use the vwCustomers view to access the data in the Customers table.

What should you do?   Drop the vwCustomers view. Instruct Marc to re-create the view and to grant SELECT permissions on the view to the marketing role.

- You are an administrator of a SQL Server2000 database. Number of users accessing the database has increased from 100 to 1000. Users are frequently getting messages that there transaction has become a victim of deadlock.

What is the feasible option with you?   Use sql profiler to capture deadlock events.

- You are building a prototype for a new SQL Server 2000 database. You have built several new tables with scripts that follow the form:

CREATE TABLE newtable ( pk uniqueidentifier, productname varchar(30), catalogshortid int, catalogfullid uniqueidentifier, price float )

The only differences in the scripts are the table and column names. Your development team has told you that they are building several generic queries that can be run on this or any number of similar tables throughout the database. Because the column names differ slightly from one table to the next, they would like to build the queries so that the primary key values can be extracted without necessarily calling the key column by name. They¡äve decided to use the ROWGUIDCOL keyword in their queries. What is true about their approach to the generic queries?   Add a keyword to the CREATE PROCEDURE statement.

- You are building a prototype for a new SQL Server 2000 database. You have built several new tables with scripts that follow the form: CREATE TABLE newtable ( pk uniqueidentifier, productname varchar(30), catalogshortid int, catalogfullid uniqueidentifier, price float )

The only differences in the scripts are the table and column names. Your development team has told you that they are building several generic queries that can be run on this or any number of similar tables throughout the database. Because the column names differ slightly from one table to the next, they would like to build the queries so that the primary key values can be extracted without necessarily calling the key column by name. They have decided to use the ROWGUIDCOL keyword in their queries.

What is true about their approach to the generic queries?   Set a database option.

- You are building a prototype for a new SQL Server 2000 database. You have built several new tables with scripts that follow the form: CREATE TABLE newtable ( pk uniqueidentifier, productname varchar(30), catalogshortid int, catalogfullid uniqueidentifier, price float )

The only differences in the scripts are the table and column names. Your development team has told you that they are building several generic queries that can be run on this or any number of similar tables throughout the database. Because the column names differ slightly from one table to the next, they would like to build the queries so that the primary key values can be extracted without necessarily calling the key column by name. They have decided to use the ROWGUIDCOL keyword in their queries.

What is true about their approach to the generic queries?   Add a keyword to the CREATE PROCEDURE statement.

- You are conducting a training class on SQL Server 2000 database design, and construct the following stored procedure to enforce the students?understanding of error handling within an explicit transaction. You submit the following script to your local server while Northwind is your current database:

CREATE PROCEDURE testprocess AS BEGIN TRANSACTION --update (possibly all, but maybe only some) rows UPDATE employees SET lastname = ¡äJONES?where employeeid > 0 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END ---now update all rows UPDATE employees SET lastname = ¡äSMITH?BR> IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END --end of procedure

After you run the procedure, you direct the students to run linked-server queries to your server in a Query Analyzer session and display all rows in Northwind¡äs Employees table. All students report that the lastname value of each employee row is the same and is the value ¡äSmith? They add, however, that all values displayed are in complete lowercase, where your updates specifically denote uppercase values be present. You run your procedure again, with the same result. What should you do first to get your class back on track?   Check your machine’s current SQL Server server-wide options.

- You are conducting a training class on SQL Server 2000 database design, and construct the following stored procedure to enforce the students?understanding of error handling within an explicit transaction. You submit the following script to your local server while Northwind is your current database:

CREATE PROCEDURE testprocess AS BEGIN TRANSACTION --update (possibly all, but maybe only some) rows UPDATE employees SET lastname = ¡äJONES?where employeeid > 0 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END ---now update all rows UPDATE employees SET lastname = ¡äSMITH?BR> IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END --end of procedure

After you run the procedure, you direct the students to run linked-server queries to your server in a Query Analyzer session and display all rows in Northwind¡äs Employees table. All students report that the lastname value of each employee row is the same and is the value ¡äSmith? They add, however, that all values displayed are in complete lowercase, where your updates specifically denote uppercase values be present. You run your procedure again, with the same result. What should you do first to get your class back on track?   Run an ALTER PROCEDURE command.

- You are conducting a training class on SQL Server 2000 database design, and construct the following stored procedure to enforce the students?understanding of error handling within an explicit transaction. You submit the following script to your local server while Northwind is your current database:

CREATE PROCEDURE testprocess AS BEGIN TRANSACTION --update (possibly all, but maybe only some) rows UPDATE employees SET lastname = ¡äJONES?where employeeid > 0 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END ---now update all rows UPDATE employees SET lastname = ¡äSMITH?BR> IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END --end of procedure

After you run the procedure, you direct the students to run linked-server queries to your server in a Query Analyzer session and display all rows in Northwind¡äs Employees table. All students report that the lastname value of each employee row is the same and is the value ¡äSmith? They add, however, that all values displayed are in complete lowercase, where your updates specifically denote uppercase values be present. You run your procedure again, with the same result. What should you do first to get your class back on track?   Run an ALTER PROCEDURE command.

- You are database developer for your company's SQL Server 2000 database named Sales. The company has several custom Web-based applications that retrieve data from the Sales database. Some of these applications use the EXECUTE statement to allow users to issue administrator ad hoc queries. As the use of the Web-based applications increases, queries are taking longer to execute.

You want to discover which applications are sending a high number of these queries to the database server. What should you do?   Use SQL profiler to capture the RPC:Completed evemt. Group the trace by the ApplicationName data column

- You are database developer for your Company's SQL Server 2000 database named sales. The Company has several custom web-based applications that retrieve data from the sales database. Some of these application use the EXECUTE statement to allow users to issue administrator hoc queries. As the use of the Web-based applications increases, queries are taking longer to execute. You want to discover which applications are sending a high number of these queries to the database server.

What should you do?   Use SQL profiler to capture the SQL:StmtCompleted event. Group the trace by the ApplicationName data column

- You are database developer for your Company's SQL Server 2000 database named sales. The Company has several custom web-based applications that retrieve data from the sales database. Some of these application use the EXECUTE statement to allow users to issue administrator hoc queries. As the use of the Web-based applications increases, queries are taking longer to execute. You want to discover which applications are sending a high number of these queries to the database server.

What should you do?   Use SQL profiler to capture the SQL:StmtCompleted event. Group the trace by the ApplicationName data column

- You are database developer for your company's SQL Server 2000 database named Sales. The company has several custom Web-based applications that retrieve data from the Sales database. Some of these applications use the EXECUTE statement to allow users to issue administrator ad hoc queries. As the use of the Web-based applications increases, queries are taking longer to execute.

You want to discover which applications are sending a high number of these queries to the database server. What should you do?   Use SQL profiler to capture the RPC:Completed evemt. Group the trace by the ApplicationName data column

- You are designing a database for a Web-based ticket reservation application. There might be 500 or more tickets available for any single event. Most users if the application will view fewer than 50 of the available tickets before purchasing tickets. However, it must be possible for a user to view the entire list of available tickets. As the user scrolls through the list, the list should be updated to reflect that have been sold to other users. The user should be able to select tickets from the list and purchase the tickets. You need to design a way for the user to view and purchase available tickets. What should you do?   Use a scrollbar dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

- You are designing a database for a Web-based ticket reservation application. There might be 500 or more tickets available for any single event. Most users if the application will view fewer than 50 of the available tickets before purchasing tickets. However, it must be possible for a user to view the entire list of available tickets. As the user scrolls through the list, the list should be updated to reflect that have been sold to other users. The user should be able to select tickets from the list and purchase the tickets. You need to design a way for the user to view and purchase available tickets. What should you do?   Use stored procedures to retrieve the list of tickets. Use a second stored procedure to make purchase.

- You are designing a database for a Web-based ticket reservation application. There might be 500 or more tickets available for any single event. Most users if the application will view fewer than 50 of the available tickets before purchasing tickets. However, it must be possible for a user to view the entire list of available tickets. As the user scrolls through the list, the list should be updated to reflect that have been sold to other users. The user should be able to select tickets from the list and purchase the tickets. You need to design a way for the user to view and purchase available tickets. What should you do?   Use a scrollbar dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

- You are designing a database for a web-based ticket reservation application. There might be 500 or more tickets available for any single event. Most users of the application will view fewer than 50 of the available tickets before purchasing tickets. However, it must be possible for a user to view the entire list of available tickets. As the user scrolls through the list, the list should be updated to reflect that have been sold to other users. The user should be able to select tickets from the list and purchase the tickets. You need to design a way for the user to view and purchase available tickets.

What should you do?   Use a scrollable dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

- You are designing a database for Tailspin Toys. You review the database design, which is shown in the exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do?   Create New table named customer contact. Add customer ID, Contact Name and Phone Number Column to theTable.

- You are designing a database for Tailspin Toys. You review the database design, which is shown in the exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do?   Remove Cusomer ID column from Order details Table.

- You are designing a database for Tailspin Toys. You review the database design, which is shown below:You want to promote quick response times for queries and minimize redundant data. What should you do?   Remove the CustomerID column from the OrderDetails table.

- You are designing a database for Tailspin Toys. You review the database design, which is shown in an exhibit.

You want to promote quick response times for queries and minimize redundant data. What should you do?   Remove the CustomerID column from the OrderDetails table.

- You are designing a database that will contain an extremely large table containing customer orders. You need this table to perform at the highest possible levels. What SQL Server feature will be most important for you to use to accomplish this?
   Partitioned views

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is show below:

Customers <-------Orders <---------> Products CustomerID OrderID ProductID CompanyName ProductID Description Address OrderDate UnitPrice City Quantity State CustomerID

You want to promote quick response times for queries and minimize redundant data. Each correct answer presents part of the solution. What should you do? (Choose two)   Create a new order table named OrdersDetail. Add OrderID, ProductID, and Quantity columns to this table.

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is show below:

Customers <-------Orders <---------> Products CustomerID OrderID ProductID CompanyName ProductID Description Address OrderDate UnitPrice City Quantity State CustomerID

You want to promote quick response times for queries and minimize redundant data. Each correct answer presents part of the solution. What should you do? (Choose two)   Remove the ProductID and Quantity columns from the Orders table

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is show below: You want to promote quick response times for queries and minimize redundant data. What should you do? (Each correct answer presents part of the solution. (Choose two)   Remove the ProductID and Quantity columns from the Orders table

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in an exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Create a new order table named OrdersDetail. Add OrderID, ProductID, and Quantity columns to this table.

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in an exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in an exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Remove the ProductID and Quantity columns from the Orders table

- You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in an exhibit.

You want to promote quick response times for queries and minimize redundant data.

What should you do (Each correct answer presents part of the solution)? (Choose two)   Create a UNIQUE constraint on the OrderID column of the Orders table

- You are designing a n Inventory and Shipping databases. You must ensure that referential integrity of the database maintained. What are the 3 types of constraints you choose? (choose three)

ORDER ORDER ID PK Customer ID Ship date PRODUCT PRODUCT ID PK ORDER DETAILS ORDER ID (PK) PRODUCT ID (PK) SUPPLIER SUPPLIER ID ( PK)   Create a FK constraint on the Products table that reference the Supplier table.

- You are designing a n Inventory and Shipping databases. You must ensure that referential integrity of the database maintained. What are the 3 types of constraints you choose? (choose three)

ORDER ORDER ID PK Customer ID Ship date PRODUCT PRODUCT ID PK ORDER DETAILS ORDER ID (PK) PRODUCT ID (PK) SUPPLIER SUPPLIER ID ( PK)   Create a FK constraint on the Order Details table that reference the Order details table.

- You are designing a n Inventory and Shipping databases. You must ensure that referential integrity of the database maintained. What are the 3 types of constraints you choose? (choose three)

ORDER ORDER ID PK Customer ID Ship date PRODUCT PRODUCT ID PK ORDER DETAILS ORDER ID (PK) PRODUCT ID (PK) SUPPLIER SUPPLIER ID ( PK)   Create a FK constraint on the Order Details table that reference the Products table.

- You are designing a n Inventory and Shipping databases. You must ensure that referential integrity of the database maintained.What are the 3 types of constraints you choose. ORDER ORDER ID PK Customer ID Ship date

PRODUCT PRODUCT ID PK

ORDER DETAILS ORDER ID (PK) PRODUCT ID (PK)

SUPPLIER SUPPLIER ID ( PK)

(choose all that apply)   Create a FK constraint on the Products table that reference the Supplier table.

- You are designing a n Inventory and Shipping databases. You must ensure that referential integrity of the database maintained.What are the 3 types of constraints you choose. ORDER ORDER ID PK Customer ID Ship date

PRODUCT PRODUCT ID PK

ORDER DETAILS ORDER ID (PK) PRODUCT ID (PK)

SUPPLIER SUPPLIER ID ( PK)

(choose all that apply)   Create a FK constraint on the Order Details table that reference the Order details table.

- You are designing a n Inventory and Shipping databases. You must ensure that referential integrity of the database maintained.What are the 3 types of constraints you choose. ORDER ORDER ID PK Customer ID Ship date

PRODUCT PRODUCT ID PK

ORDER DETAILS ORDER ID (PK) PRODUCT ID (PK)

SUPPLIER SUPPLIER ID ( PK)

(choose all that apply)   Create a FK constraint on the Order Details table that reference the Products table.

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown below: You must ensure that the referential integrity of the database is maintained. Which three types of constraints should you apply to your design?

(Each correct answer presents part of the solution.)(Choose three)   Create a FOREIGN KEY constraint on the Order Details table that references the Products table

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown below:

You must ensure that the referential integrity of the database is maintained. Each correct answer presents part of the solution. Which three types of constraints should you apply to your design? (Choose three)   Create a FOREIGN KEY constraint on the Products table that references the Suppliers table

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown below:

You must ensure that the referential integrity of the database is maintained. Each correct answer presents part of the solution. Which three types of constraints should you apply to your design? (Choose three)   Create a FOREIGN KEY constraint on the Order Details table that references the Order table

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown below:

You must ensure that the referential integrity of the database is maintained. Each correct answer presents part of the solution. Which three types of constraints should you apply to your design? (Choose three)   Create a FOREIGN KEY constraint on the Order Details table that references the Products table

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown in an exhibit.

You must ensure that the referential integrity of the database is maintained.

Which three types of constraints should you apply to your design (Each correct answer presents part of the solution)? (Choose all that apply)   Create a FOREIGN KEY constraint on the Products table that references the Suppliers table.

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown in an exhibit.

You must ensure that the referential integrity of the database is maintained.

Which three types of constraints should you apply to your design (Each correct answer presents part of the solution)? (Choose all that apply)   Create a FOREIGN KEY constraints on the Order Details table that references the Order table

- You are designing an inventory and shipping database for Contoso,Ltd. You create the logical database design shown in an exhibit.

You must ensure that the referential integrity of the database is maintained.

Which three types of constraints should you apply to your design (Each correct answer presents part of the solution)? (Choose all that apply)   Create a FOREIGN KEY constraints on the Order Details table that references the Products table

- You are designing for a large grocery store chain. The partial database schema is shown in the Partial Database Schema Exhibit.

The script that was used to create the Customers table is shown in the Script for Customers Table Exhibit.

The store managers want to track customer demographics so they can target advertisements and coupon promotions to customers. These advertisements and promotions will be based on the past purchases of existing customers. The advertisements and promotions will target buying patterns by one or more of these demographics gender, age, postal code, and region. Most of the promotions will be based on gender and age. Queries will be used to retrieve the customer demographics information.

You want the query response time to be as fast as possible.

What should you do?   Denormalize the Customers table

- You are designing your company's Sales database. The database will be used by three custom applications. Users who require access to the database are currently members of Microsoft Windows 2000 groups. Users were placed in the Windows 2000 groups according to their database access requirements. The custom applications will connect to the sales database through the sales database through application roles that exist for each application. Each application role was assigned a password. All users should have access to the sales database only through the custom applications. No permissions have been granted in the database.

What should you do?   Assign appropriate permissions to each application role.

- You are designing your company's Sales database. The database will be used by three custom applications. Users who require access to the database are currently members of Microsoft Windows 2000 groups. Users were placed in the Windows 2000 groups according to their database access requirements. The custom applications will connect to the sales database through application roles that exist for each application. Each application role was assigned a password.All users should have access to the Sales database only through the custom applications. No permissions have been granted in the database. What should you do?   Assign appropriate permissions to each application role.

- You are designing your company's Sales database. The database will be used by three custom applications. Users who require access to the database are currently members of Microsoft Windows 2000 groups. Users were placed in the Windows 2000 groups according to their database access requirements. The custom applications will connect to the sales database through application roles that exist for each application. Each application role was assigned a password.All users should have access to the Sales database only through the custom applications. No permissions have been granted in the database. What should you do?   Assign appropriate permissions to each application role.

- You are designing your company's Sales database. The database will be used by three custom applications. Users who require access to the database are currently members of Microsoft Windows 2000 groups. Users were placed in the Windows 2000 groups according to their database access requirements. The custom applications will connect to the sales database through application roles that exist for each application. Each application role was assigned a password.All users should have access to the Sales database only through the custom applications. No permissions have been granted in the database. What should you do?   Assign the Windows 2000 groups to the appropriate application role.

- You are designing your Company's SQL Server 2000 sales database, which will be accessed by a custom application. Customer service and marketing employees require SELECT, INSERT, and UPDATE permissions on all tables in the Sales database. In addition to using the custom application, the marketing employees will use Microsoft Excel to retrieve data from the sales database to create charts.

Customer service employees belong to Microsoft Windows 2000 group named CSR, and marketing employees belong to a Windows 2000 group named Marketing. You want the customer service employees to access the sales database only from within the custom application. You want to allow the marketing employees to use both the custom application and Excel to access the sales database. No permissions have been granted in the database. What should you do?   Create an application role for the custom application. Grant SELECT, INSERT and UPDATE permissions to the application role. Create a database role named Marketing for the marketing employees. Add the Windows 2000 Marketing group to the Marketing database role. Grant SELECT permissions on all tables in the database to the marketing database role.

- You are designing your Company's SQL Server 2000 sales database, which will be accessed by a custom application. Customer service and marketing employees require SELECT, INSERT, and UPDATE permissions on all tables in the Sales database. In addition to using the custom application, the marketing employees will use Microsoft Excel to retrieve data from the sales database to create charts. Customer service employees belong to Microsoft Windows 2000 group named CSR, and marketing employees belong to a Windows 2000 group named Marketing. You want the customer service employees to access the sales database only from within the custom application. You want to allow the marketing employees to use both the custom application and Excel to access the sales database. No permissions have been granted in the database. What should you do?   Create an application role for the custom application. Grant SELECT, INSERT and UPDATE permissions to the application role. Create a database role named Marketing for the marketing employees. Add the Windows 2000 Marketing group to the Marketing database role. Grant SELECT permissions on all tables in the database to the marketing database role.

- You are designing your Company's SQL Server 2000 sales database, which will be accessed by a custom application. Customer service and marketing employees require SELECT, INSERT, and UPDATE permissions on all tables in the Sales database. In addition to using the custom application, the marketing employees will use Microsoft Excel to retrieve data from the sales database to create charts. Customer service employees belong to Microsoft Windows 2000 group named CSR, and marketing employees belong to a Windows 2000 group named Marketing.

You want the customer service employees to access the sales database only from within the custom application. You want to allow the marketing employees to use both the custom application and Excel to access the sales database. No permissions have been granted in the database.

What should you do?   Create an application role for the custom application. Grant SELECT, INSERT and UPDATE permissions to the application role. Create a database role named Marketing for the marketing employees. Add the Windows 2000 Marketing group to the Marketing database role. Grant SELECT permissions on all tables in the database to the Marketing database role.

- You are designing your Company's SQL Server 2000 sales database, which will be accessed by a custom application. Customer service and marketing employees require SELECT, INSERT, and UPDATE permissions on all tables in the Sales database. In addition to using the custom application, the marketing employees will use Microsoft Excel to retrieve data from the sales database to create charts. Customer service employees belong to Microsoft Windows 2000 group named CSR, and marketing employees belong to a Windows 2000 group named Marketing.

You want the customer service employees to access the sales database only from within the custom application. You want to allow the marketing employees to use both the custom application and Excel to access the sales database. No permissions have been granted in the database.

What should you do?   Create an application role for the custom application. Grant SELECT, INSERT and UPDATE permissions to the application role. Create a database role named Marketing for the marketing employees. Add the Windows 2000 Marketing group to the Marketing database role. Grant SELECT permissions on all tables in the database to the Marketing database role.

- You are developing several new stored procedures on an existing SQL Server 2000 database and are experimenting in a Query Analyzer session with the individual queries to be used in these procedures. After running a given SELECT query and using the Graphical Showplan feature to understand the execution plan, you want to prove that internal statistics are available for a particular column used in the query. How would you best find this information?   Hold the mouse over each individual node in the graphical plan relating to the desired column.

- You are developing several new stored procedures on an existing SQL Server 2000 database and are experimenting in a Query Analyzer session with the individual queries to be used in these procedures. After running a given SELECT query and using the Graphical Showplan feature to understand the execution plan, you want to prove that internal statistics are available for a particular column used in the query. How would you best find this information?   Hold the mouse over each individual node in the graphical plan relating to the desired column.

- You are developing several new stored procedures on an existing SQL Server 2000 database and are experimenting in a Query Analyzer session with the individual queries to be used in these procedures. After running a given SELECT query and using the Graphical Showplan feature to understand the execution plan, you want to prove that internal statistics are available for a particular column used in the query. How would you best find this information?   Use a SET SHOWPLAN statement.

- You are evaluating a junior-level developer design for a new database. This database was to be designed with an emphasis on query performance. Replying to this directive, the developer has sketched out several indexes for the tables with the highest expected query load. As you review his design, you notice that the new indexes will provide varying degrees of benefit to queries. Which of his nonclustered indexes is likely to be the most effective?   An index on the sales agent¡äs last initial for a table holding 25,000 orders.

- You are exploring SQL Server 2000¡äs new features for supporting XML documents. To experiment with SQL Server¡äs ability to output XML documents, you have developed the following query:

USE northwind GO SELECT t.territoryID, e.employeeid as ¡äEMPLOYEEID? e.lastname,   Add a clause to the SELECT statement

- You are exploring SQL Server 2000¡äs new features for supporting XML documents. To experiment with SQL Server¡äs ability to output XML documents, you have developed the following query:

USE northwind GO SELECT t.territoryID, e.employeeid as ¡äEMPLOYEEID? e.lastname,   Remove the aggregate function from the query.

- You are exploring SQL Server 2000¡äs new features for supporting XML documents. To experiment with SQL Server¡äs ability to output XML documents, you have developed the following query: USE northwind GO SELECT t.territoryID, e.employeeid as ¡äEMPLOYEEID? e.lastname,e.firstname, t.employeeid FROM employees e INNER JOIN employeeterritories t ON e.employeeid = t.employeeid WHERE t.employeeid = (select min(employeeid) from employees) ORDER BY territoryID

You are able to submit this script in a Query Analyzer session and return tabular results from the server. What must you do before this query can return its results as an XML document?   Add a clause to the SELECT statement

- You are finding unusually high memory utilization on a SQL Server that is primarily used to process XML data. The server will periodically freeze up and refuse to process any more XML statements. The problem can only be fixed by restarting the SQL Server service, at which point it work fine for a while, but the problem always returns.

What is the most likely cause for the problem?   The XML strings are not being removed after being prepared and used.

- You are the database developer for a brokerage firm. The database contains a tabled named Trades. The script that was used to create this table is shown below: CREATE TABLE Trades ( TradeID int IDENTITY(1,1)PRIMAEY KEY NONCLUSTERED NOT NULL, TradeDate datetime NULL, SettlementDate datetime NULL, Units decimal(18, 4) NULL, Symbol char(5) NULL, ClientID int NULL, BrokerID int NULL )GOCREATE CLUSTERED INDEX c_symbol ON Trades (Symbol) The Trades table has frequent inserts and updates during the day. Reports are run against the table each night You execute the following statement in the SQL Query Analyzer: DBCC SHOWCONTIG (Trades) The output for the statement is shown below: DBCC Statement Output DBCC SHOWCONTIG scanning 'Trades' table. . . . . Table: 'Trades'(1621580815); index ID:1, database ID:12Table level scan performed. -Pages Scanned-----------------------------------------:104 -Extents Scanned---------------------------------------:16 -Extent Switches----------------------------------------:24 -Avg. Pages per Extenbt-------------------------------:6.5 -Scan Density[Best Count:Actual Count]-----------:52.00%[13:25] -Logical Scan Fragmentation-------------------------:7.69% -Extent Scan Fragmentation---------------------------:43.75% -Avg. Bytes Free per page-----------------------------:460.1 -Avg. Page Density (full)------------------------------:94.32% DBCC execution completed. If DBCC printed error messages, contact your system

You want to ensure optional performance for the insert and select operations on the Trades table. What should you do?   Execute the DBCC DBREINDEX statement on the table.

- You are the database developer for a brokerage firm. The database contains a table named Trades (or Stocks). The script that was used to create this table is shown below:

CREATE TABLE Trades ( TradeID int IDENTITY(1,1)PRIMAEY KEY NONCLUSTERED NOT NULL, TradeDate datetime NULL, SettlementDate datetime NULL, Units decimal(18, 4) NULL, Symbol char(5) NULL, ClientID int NULL, BrokerID int NULL )GOCREATE CLUSTERED INDEX c_symbol ON Trades (Symbol)

The Trades table has frequent inserts and updates during the day. Reports are run against the table each night You execute the following statement in the SQL Query Analyzer:

DBCC SHOWCONTIG (Trades)

The output for the statement is shown below:

DBCC Statement Output DBCC SHOWCONTIG scanning 'Trades' table. . . . . Table: 'Trades'(1621580815); index ID:1, database ID:12Table level scan performed. -Pages Scanned---:104 -Extents Scanned-:16 -Extent Switches--:24 -Avg. Pages per Extenbt------------:6.5 -Scan Density[Best Count:Actual Count]-----------:52.00%[13:25] -Logical Scan Fragmentation------:7.69% -Extent Scan Fragmentation--------:43.75% -Avg. Bytes Free per page----------:460.1 -Avg. Page Density (full)-----------:94.32%

DBCC execution completed. If DBCC printed error messages, contact your system You want to ensure optional performance for the insert and select operations on the Trades table. What should you do?   Execute the DBCC DBREINDEX statement on the table.

- You are the database developer for a brokerage firm. The database contains a table named Trades. The script that was used to create this table is shown in the Script for Trades Table exhibit.

CREATE TABLE Trades ( TradeID int IDENTITY(1,1)PRIMARY KEY NONCLUSTERED NOT NULL, TradeDate datetime NULL, SettlementDate datetime NULL, Units decimal(18, 4) NULL,

Symbol char(5) NULL, ClientID int NULL, BrokerID int NULL ) GO CREATE CLUSTERED INDEX c_symbol ON Trades (Symbol)

The Trades table has frequent inserts and updates during the day. Reports are run against the table each night. You execute the following statement in the SQL Query Analyzer:

DBCC SHOWCONTIG (Trades) The output for the statement is shown in the DBCC Statement Output exhibit. DBCC Statement Output DBCC SHOWCONTIG scanning ‘Trades’ table. . . . . Table: ‘Trades’(1621580815); index ID:1, database ID:12 Table level scan performed. -Pages Scanned-----------------------------------------:104 -Extents Scanned---------------------------------------:16 -Extent Switches----------------------------------------:24 -Avg. Pages per Extent-------------------------------:6.5 -Scan Density[Best Count:Actual Count]----------- :52.00%[13:25] -Logical Scan Fragmentation-------------------------:7.69% -Extent Scan Fragmentation---------------------------:43.75% -Avg. Bytes Free per page-----------------------------:460.1 -Avg. Page Density (full)------------------------------ :94.32%

DBCC execution completed. If DBCC printed error messages, contact your system

You want to ensure optional performance for the insert and select operations on the Trades table.

What should you do?   Execute the DBCC DBREINDEX statement on the table

- You are the database developer for a brokerage firm. The database contains a table named Trades. The script that was used to create this table is shown in the Script for Trades Table exhibit.

CREATE TABLE Trades ( TradeID int IDENTITY(1,1)PRIMARY KEY NONCLUSTERED NOT NULL, TradeDate datetime NULL, SettlementDate datetime NULL, Units decimal(18, 4) NULL,

Symbol char(5) NULL, ClientID int NULL, BrokerID int NULL ) GO CREATE CLUSTERED INDEX c_symbol ON Trades (Symbol)

The Trades table has frequent inserts and updates during the day. Reports are run against the table each night. You execute the following statement in the SQL Query Analyzer:

DBCC SHOWCONTIG (Trades) The output for the statement is shown in the DBCC Statement Output exhibit. DBCC Statement Output DBCC SHOWCONTIG scanning ‘Trades’ table. . . . . Table: ‘Trades’(1621580815); index ID:1, database ID:12 Table level scan performed. -Pages Scanned-----------------------------------------:104 -Extents Scanned---------------------------------------:16 -Extent Switches----------------------------------------:24 -Avg. Pages per Extent-------------------------------:6.5 -Scan Density[Best Count:Actual Count]----------- :52.00%[13:25] -Logical Scan Fragmentation-------------------------:7.69% -Extent Scan Fragmentation---------------------------:43.75% -Avg. Bytes Free per page-----------------------------:460.1 -Avg. Page Density (full)------------------------------ :94.32%

DBCC execution completed. If DBCC printed error messages, contact your system

You want to ensure optional performance for the insert and select operations on the Trades table.

What should you do?   Execute the DROP INDEX and CREATE INDEX statements on the primary key index

- You are the database developer for a Company that provides consulting service. The Company maintains data about its employees in a table named Employee. The script that was used to create the employee table is shown in an exhibit.

CREATE TABLE Employee ( EmployeeID int NOT NULL; EmpType char (1) NOT NULL, EmployeeName char (50) NOT NULL, Address char (50) NULL, Phone char (20) NULL, CONSTRAINT PK_Employee PRMARY KEY (Employee ID) )

The EmpType column in this table is used to identify employees as executive, administrative, or consultants. You need to ensure that the administrative employees can add, update, or delete data for non-executive employees only. What should you do?   Create a view, and include the WITH CHECK OPTION clause

- You are the database developer for a company that provides consulting services. The company maintains data about its employees in a table named Employee. The script that was used to create the employee table is shown below:

CREATE TABLE Employee ( EmployeeID int NOT NULL; EmpType char (1) NOT NULL, EmployeeName char (50) NOT NULL, Address char (50) NULL, Phone char (20) NULL, CONSTRAINT PK_Employee PRMARY KEY (Employee ID) )

The emp type column is this table is used to identify employees as executive, administrative, or consultants. You need to ensure that the administrative employees can add, update, or delete data for non-executive employees only. What should you do?   Create a view, and include the WITH CHECK OPTION clause.

- You are the database developer for a company that provides consulting services. The company maintains data about its employees in a table named Employee. The script that was used to create the employee table is shown below:

CREATE TABLE Employee ( EmployeeID int NOT NULL; EmpType char (1) NOT NULL, EmployeeName char (50) NOT NULL, Address char (50) NULL, Phone char (20) NULL, CONSTRAINT PK_Employee PRMARY KEY (Employee ID) )

The emp type column is this table is used to identify employees as executive, administrative, or consultants. You need to ensure that the administrative employees can add, update, or delete data for non-executive employees only. What should you do?   Create a view, and build a covering index on the view.

- You are the database developer for a company that provides consulting services. The company maintains data about its employees in a table named Employee. The script that was used to create the employee table is shown below: CREATE TABLE Employee ( EmployeeID int NOT NULL; EmpType char (1) NOT NULL, EmployeeName char (50) NOT NULL, Address char (50) NULL, Phone char (20) NULL, CONSTRAINT PK_Employee PRMARY KEY (Employee ID) ) The emp type column is this table is used to identify employees as executive, administrative, or consultants. You need to ensure that the administrative employees can add, update, or delete data for non-executive employees only. What should you do?   Create a view, and include the WITH CHECK OPTION clause.

- You are the database developer for a large brewery. Information about each of the brewery’s plants and the equipment located at each plant is stored in a database named Equipment. The plant information is stored in a table named Location, and the equipment information is stored in a table named Parts. The scripts that were used to create these tables are shown in the Location and Parts Scripts exhibit.

CREATE TABLE Location ( LocationID int NOT NULL, LocationName char (30) NOT NULL UNIQUE, CONSTRAINT PK_Location PRIMARY KEY (LocationID) ) CREATE TABLE Parts ( PartID int NOT NULL, LocationID int NOT NULL, PartName char (30) NOT NULL, CONSTRAINT PK_Parts PRIMARY KEY (PartID), CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID) REFERENCES Location (LocationID) ) The brewery is in the process of closing several existing plants and opening several new parts. When a plant is closed, the information about the plant and all of the equipment at that plant must be deleted from the database. You have created a stored procedure to perform this operation. The stored procedure is shown in the Script for sp_DeleteLocation exhibit.

CREATE PROCEDURE sp_DeleteLocation @LocName char(30) AS BEGIN DECLARE @PartID int

DECLARE crs_Parts CURSOR FOR SELECT p.PartID FROM Parts AS p INNER JOIN Location AS 1 ON p.LocationID = @LocName WHERE 1.LocationName = @LocName OPEN crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID WHILE (@@FETCH_STATUS <> -1) BEGIN DELETE Parts WHERE CURRENT OF crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID END CLOSE crs_Parts DEALLOCATE crs_Parts DELETE Location WHERE LocatioName = @LocName END

This procedure is taking longer than expected to execute. You need to reduce the execution time of the procedure.

What should you do?   Replace the cursor operation with a single DELETE statement

- You are the database developer for a large brewery. Information about each of the brewery's plants and the equipment located at each plant is stored in a database named Equipment. The plant information is stored in a table named Location, and the equipment information is stored in a table named Parts. The scripts that were used to create these tables are shown below:

CREATE TABLE Location ( LocationID int NOT NULL, LocationName char (30) NOT NULL UNIQUE, CONSTRAINT PK_Location PRIMARY KEY (LocationID) ) CREATE TABLE Parts ( PartID int NOT NULL, LocationID int NOT NULL, PartName char (30) NOT NULL, CONSTRAINT PK_Parts PRIMARY KEY (PartID), CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID) REFERENCES Location (LocationID) )

The brewery is in the process of closing several existing plants and opening several new parts. When a plant is closed, the information about the plant and all of the equipment at that plant must be deleted from the database. You have created a stored procedure to perform this operation. The stored procedure is shown below:

CREATE PROCEDURE sp_DeleteLocation @LocName char(30) AS BEGIN DECLARE @PartID int DECLARE crs_Parts CURSOR FOR SELECT p.PartID FROM Parts AS p INNER JOIN Location AS 1 ON p.LocationID = @LocName WHERE 1.LocationName = @LocName OPEN crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID WHILE (@@FETCH_STATUS <> -1) BEGIN DELETE Parts WHERE CURRENT OF crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID END CLOSE crs_Parts DEALLOCATE crs_Parts DELETE Location WHERE LocatioName = @LocName END

This procedure is taking longer than expected to execute. You need to reduce the execution time of the procedure. What should you do?   Replace the cursor operation with a single DELETE statement.

- You are the database developer for a large brewery. Information about each of the brewery's plants and the equipment located at each plant is stored in a database named Equipment. The plant information is stored in a table named Location, and the equipment information is stored in a table named Parts. The scripts that were used to create these tables are shown below: CREATE TABLE Location ( LocationID int NOT NULL, LocationName char (30) NOT NULL UNIQUE, CONSTRAINT PK_Location PRIMARY KEY (LocationID) ) CREATE TABLE Parts ( PartID int NOT NULL, LocationID int NOT NULL, PartName char (30) NOT NULL, CONSTRAINT PK_Parts PRIMARY KEY (PartID), CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID) REFERENCES Location (LocationID) )

The brewery is in the process of closing several existing plants and opening several new parts. When a plant is closed, the information about the plant and all of the equipment at that plant must be deleted from the database. You have created a stored procedure to perform this operation. The stored procedure is shown below:

CREATE PROCEDURE sp_DeleteLocation @LocName char(30) AS BEGIN DECLARE @PartID int DECLARE crs_Parts CURSOR FOR SELECT p.PartID FROM Parts AS p INNER JOIN Location AS 1 ON p.LocationID = @LocName WHERE 1.LocationName = @LocName OPEN crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID WHILE (@@FETCH_STATUS <> -1) BEGIN DELETE Parts WHERE CURRENT OF crs_Parts FETCH NEXT FROM crs_Parts INTO @PartID END CLOSE crs_Parts DEALLOCATE crs_Parts DELETE Location WHERE LocatioName = @LocName END

This procedure is taking longer than expected to execute. You need to reduce the execution time of the procedure. What should you do?   Replace the cursor operation with a single DELETE statement.

- You are the database developer for a publishing company. You create the following stored procedure to report the year-to-date sales for a particular book title:

CREATE PROCEDURE get_sales_for_title %title varchar(80), @ytd_sales int OUTPUT AS SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title IF @@ROWCOUNT = 0 RETURN(-1) ELSE RETURN(0)

You are creating a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title. If the stored procedure fails to execute, it should report the following message:"No Sales Found" How should you create the script?   DECLARE @retval int DECLARE @ytd int EXEC @retval = get_sales_for_title 'Net Etiquette', @ytd OUTPUT IF @retval < 0 PRINT 'No sales found' ELSE PRINT 'Year to date sales: ' + STR (@ytd) GO

- You are the database developer for a publishing company. You create the following stored procedure to report the year-to-date sales for a particular book title:

CREATE PROCEDURE get_sales_for_title %title varchar(80), @ytd_sales int OUTPUT AS SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title IF @@ROWCOUNT = 0 RETURN(-1) ELSE RETURN(0)

You are creating a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title. If the stored procedure fails to execute, it should report the following message:"No Sales Found"

How should you create the script?   DECLARE @retval int DECLARE @ytd int EXEC @retval = get_sales_for_title 'Net Etiquette', @ytd OUTPUT IF @retval < 0 PRINT 'No sales found' ELSE PRINT 'Year to date sales: ' + STR (@ytd) GO

- You are the database developer for a publishing Company. You create the following stored procedure to report the year-to-date sales for a particular book title:

CREATE PROCEDURE get_sales_for_title %title varchar(80), @ytd_sales int OUTPUT AS SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title IF @@ROWCOUNT = 0 RETURN(-1) ELSE RETURN(0)

You are creating a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title. If the stored procedure fails to execute, it should report the following message: “No Sales Found”

How should you create the script?   DECLARE @retval int DECLARE @ytd int EXEC get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT IF @retval < 0 PRINT ‘No sales found’ ELSE PRINT ‘Year to date sales: ’ + STR (@ytd) GO

- You are the database developer for a publishing Company. You create the following stored procedure to report the year-to-date sales for a particular book title:

CREATE PROCEDURE get_sales_for_title %title varchar(80), @ytd_sales int OUTPUT AS SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title IF @@ROWCOUNT = 0 RETURN(-1) ELSE RETURN(0)

You are creating a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title. If the stored procedure fails to execute, it should report the following message: “No Sales Found”

How should you create the script?   DECLARE @retval int DECLARE @ytd int EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT IF @retval < 0 PRINT ‘No sales found’ ELSE PRINT ‘Year to date sales: ’ + STR (@ytd) GO

- You are the database developer for a sporting goods company that exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database. The script that was used to create this table is shown in an exhibit.

CREATE TABLE customers ( CustomerID int NOT NULL, CustomerName varchar(30) NOT NULL, ContactName varchar(30) NULL, Phone varchar(20) NULL, Country varchar(30) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID) )

There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company’s marketing department wants to target its advertising to countries that have more than 10 customers. You need to create a list of these countries for the marketing department. Which script should you use?   SELECT Country FROM Customers GROUP BY Country HAVING COUNT (Country)>10

- You are the database developer for a sporting goods company that exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database. The script that was used to create this table is shown below:

CREATE TABLE customers ( CustmerID int NOT NULL, CustomerName varchar(30) NOT NULL, ContactName varchar(30) NULL, Phone varchar(20) NULL, Country varchar(30) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID) )

There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company's marketing department wants to target its advertising to countries that have more than 10 customers. You need to create a list of these countries for the marketing department. Which script should you use?   SELECT Country FROM Customers

- You are the database developer for a sporting goods company that exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database. The script that was used to create this table is shown below:

CREATE TABLE customers ( CustmerID int NOT NULL, CustomerName varchar(30) NOT NULL, ContactName varchar(30) NULL, Phone varchar(20) NULL, Country varchar(30) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID) )

There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company's marketing department wants to target its advertising to countries that have more than 10 customers. You need to create a list of these countries for the marketing department. Which script should you use?   GROUP BY Country HAVING COUNT (Country)>10

- You are the database developer for a sporting goods company that exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database. The script that was used to create this table is shown below: CREATE TABLE customers ( CustmerID int NOT NULL, CustomerName varchar(30) NOT NULL, ContactName varchar(30) NULL, Phone varchar(20) NULL, Country varchar(30) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID) )

There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company's marketing department wants to target its advertising to countries that have more than 10 customers. You need to create a list of these countries for the marketing department. Which script should you use? (choose all that apply)   SELECT Country FROM Customers

- You are the database developer for trey research.You are designing SQL 200 database that will be distributed with an application to several companies. You create a stored procedure which contain confidential information.You want to prevent the companies to view the confidential information.   Encrypt the text of the Stored procedure.

- You are the database developer for your Company’s Accounting database. The database contains a table named Employees. Tom is a member of the accounting department. Tom’s database user account has been denied SELECT permissions on the Salary and BonusPercentage columns of the Employees table.

Tom has been granted SELECT permissions on all other columns in the table. Tom now requires access to all the data in the Employees table.

What should you do?   Grant SELECT permissions on the Salary and BonusPercentage columns of the Employees table for Tom’s database user account

- You are the database developer for your company’s SQL Server 2000 database. This database contains a table named Invoices. You are a member of the db_owner role.

Eric, a member of the HR database role, created the Trey Research_Updateinvoices trigger on the Invoices table. Eric is out of the office, and the trigger is no longer needed. You execute the following statement in the Sales database to drop the trigger:

DROP TRIGGER Trey Research_UpdateInvoices

You receive the following error message:

Cannot drop the trigger ‘Trey Research_UpdateInvoices’, because it does not exist in the system catalog.

What should you do before you can drop the trigger?   Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

- You are the database developer for your company's Accounting database. The database contains a table named Employees. Tom is a member of the Accounting department. Tom's database user account has been denied SELECT permissions on the salary and BonusPercentage columns of the Employees table. Tom has been granted SELECT permissions on all other columns in the table. Tom now requires access to all the data in the Employees table.

What should you do?   Grant SELECT permissions on the salary and bonusPercentage columns of the Employees table for Tom's database user account.

- You are the database developer for your company's Accounting database. The database contains a table named Employees. Tom is a member of the Accounting department. Tom's database user account has been denied SELECT permissions on the salary and BonusPercentage columns of the Employees table. Tom has been granted SELECT permissions on all other columns in the table. Tom now requires access to all the data in the Employees table.

What should you do?   Grant SELECT permissions on the salary and bonusPercentage columns of the Employees table for Tom's database user account.

- You are the database developer for your company's SQL Server 2000 database. This database contains a table named Invoices. You are a member of the db_owner role. Eric, a member of the HR database role, created the Trey Research_Updateinvoices trigger on the Invoices table. Eric is out of the office, and the trigger is no longer needed. You execute the following statement in the sales database to drop the trigger:

DROP TRIGGER Trey Research_UpdateInvoices You receive the following error message: Cannot drop the trigger 'Trey Research_UpdateInvoices', because it does not exist in the system catalog.

What should you do before you can drop the trigger?   Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

- You are the database developer for your company's SQL Server 2000 database. This database contains a table named Invoices. You are a member of the db_owner role. Eric, a member of the HR database role, created the Trey Research_Updateinvoices trigger on the Invoices table. Eric is out of the office, and the trigger is no longer needed. You execute the following statement in the sales database to drop the trigger: DROP TRIGGER Trey Research_UpdateInvoices You receive the following error message: Cannot drop the trigger 'Trey Research_UpdateInvoices', because it does not exist in the system catalog.

What should you do before you can drop the trigger?   Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

- You are the database developer of databse named POLICIES. You have designed a stored procedure for the database. Analyst report that there is an initial delay to the query. After that performance OK.

How to improve perfornmance.   Exec sp_configure 'cursor threshold', 0.

- You are the database developer of databse named POLICIES. You have designed a stored procedure for the database. Analyst report that there is an initial delay to the query. After that performance OK. How to improve perfornmance.   Exec sp_configure 'cursor threshold', 0.

- You are the developer of a database named Inventory. You have a list of reports that you must create. These reports will be run at the same time. You write queries to create each report. Based on the queries, you design and create the indexes for the database tables.

You want to ensure that you have created useful indexes.

What should you do?   Run the Index Tuning Wizard against a workload file that contains the queries used in the reports.

- You are the developer of a database named Inventory. You have a list of reports that you must create. These reports will be run at the same time. You write queries to create each report. Based on the queries, you design and create the indexes for the database tables. You want to ensure that you have created useful indexes. What should you do?   Run the Index Tuning Wizard against a workload file that contains the queries used in the reports.

- You are the developer of a database named Inventory. You have a list of reports that you must create. These reports will be run at the same time. You write queries to create each report. Based on the queries, you design and create the indexes for the database tables. You want to ensure that you have created useful indexes. What should you do?   Run the Index Tuning Wizard against a workload file that contains the queries used in the reports.

- You are the developer of a database that supports time reporting for your company. Usually there is an average of five users accessing this database at one time, and query response times are less than one second. However, on Friday afternoons and Monday mornings, when most employees enter their timesheet data, the database usage increases to an average of 50 users at one time. During these times, the query response times increase to an average of 15 to 20 seconds. You need to find the source of the slow query response times and correct the problem. What should you do?   Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. Use this information to identify and redesign the transactions that are causing the locks.

- You are the developer of a database that supports time reporting for your Company. Usually there is an average of five users accessing this database at one time, and query response times are less than one second. However, on Friday afternoons and Monday mornings, when most employees enter their timesheet data, the database usage increases to an average of 50 users at one time. During these times, the query response times increase to an average of 15 to 20 seconds.

You need to find the source of the slow query response times and correct the problem.

What should you do?   Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. Use this information to identify and redesign the transactions that are causing the locks.

- You are the developer of a database that supports time reporting for your company. Usually there is an average of five users accessing this database at one time, and query response times are less than one second. However, on Friday afternoons and Monday mornings, when most employees enter their timesheet data, the database usage increases to an average of 50 users at one time. During these times, the query response times increase to an average of 15 to 20 seconds.

You need to find the source of the slow query response times and correct the problem. What should you do?   Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. Use this information to identify and redesign the transactions that are causing the locks.

- You are the developer of the database named sales. This database was used by 3 custom applications. Users of these 3 applications are members of window 2000 group.Custom applications are will connect to the sales database through application roles. Each was assigned a password.All should have access to the database through custom application.What to do?   Assign appropriate permission to each application Role.

- You are the developer of the database named sales. This database was used by 3 custom applications. Users of these 3 applications are members of window 2000 group.Custom applications are will connect to the sales database through application roles. Each was assigned a password.All should have access to the database through custom application. What to do?   Assign appropriate permission to each application Role.

- You are the developer of the database named sales. This database was used by 3 custom applications. Users of these 3 applications are members of window 2000 group.Custom applications are will connect to the sales database through application roles. Each was assigned a password.All should have access to the database through custom application. What to do?   Assign Windows 2000 group to the appropriate application role.

- You have a table Employee_Salary in the database. You discover the following facts. i) Employee table will often joined with salary table on EmployeeID ii) Individual records in Employee table will be selected on the basis of SCN number. iii) List of employees will be created in alphabetical list order by last_name,First_name.

Choose proper indexing strategy of the following.(Choose 3)   Create composite clustered index on Employee table last_name,First_name.

- You have a table Employee_Salary in the database. You discover the following facts. i) Employee table will often joined with salary table on EmployeeID ii) Individual records in Employee table will be selected on the basis of SCN number. iii) List of employees will be created in alphabetical list order by last_name,First_name.

Choose proper indexing strategy of the following.(Choose 3)   Create unique index on Employee table Employeeid.

- You have a table Employee_Salary in the database. You discover the following facts. i) Employee table will often joined with salary table on EmployeeID ii) Individual records in Employee table will be selected on the basis of SCN number. iii) List of employees will be created in alphabetical list order by last_name,First_name.

Choose proper indexing strategy of the following.(Choose 3)   Create unique index on Employee table SCN.

- You have a table Employee_Salary in the database. You discover the following facts.   iii) List of employees will be created in alphabetical list order by last_name,First_name.

Choose proper indexing strategy of the following: (Choose 3)

- You have a table Employee_Salary in the database. You discover the following facts.   Create index on Employee table First_name. Create index on Employee table Last_name.

- You have a table Employee_Salary in the database. You discover the following facts.   Create unique index on Employee table Employeeid.

- You have been asked by a client to use a comma-delimited flat file at a predetermined location on a server share. The fields in this flat file match perfectly to a table within one of the SQL Server 2000 databases. The client has asked you to develop a quick, easy-to-maintain solution that runs within the SQL Server Query Analyzer environment to import this data file into the table periodically. This flat file is an event log of sorts, and as such is ever-expanding. Each weeknight, several thousand rows are appended to the end of the file. The SQL Server table holding the imported data is built from this file and from two other data sources. Your solution needs to import only new records to the table; that is, it cannot import the same flat-file record twice. At the end of each import, you will be able to know how many records are currently in the flat file. What is the quickest means to achieve your objectives?   Use a BULK INSERT statement

- You have been asked by a client to use a comma-delimited flat file at a predetermined location on a server share. The fields in this flat file match perfectly to a table within one of the SQL Server 2000 databases. The client has asked you to develop a quick, easy-to-maintain solution that runs within the SQL Server Query Analyzer environment to import this data file into the table periodically. This flat file is an event log of sorts, and as such is ever-expanding. Each weeknight, several thousand rows are appended to the end of the file. The SQL Server table holding the imported data is built from this file and from two other data sources. Your solution needs to import only new records to the table; that is, it cannot import the same flat-file record twice. At the end of each import, you will be able to know how many records are currently in the flat file.What is the quickest means to achieve your objectives?   Use a BULK INSERT statement

- You have been granted create view permission in the database. Eric has been granted Create table permission in the database. All other users and logins will use AGENT database role to retrieve the information from the database.

Eric has created a table ORDERS in the database. Users only needs to view information in two columns from the table ORDERS.

What are the possible options for you? (Choose 3)   Instruct Eric to grant you REFERENCE permission on ORDERS table.

- You have been granted create view permission in the database. Eric has been granted Create table permission in the database. All other users and logins will use AGENT database role to retrieve the information from the database.

Eric has created a table ORDERS in the database. Users only needs to view information in two columns from the table ORDERS.

What are the possible options for you? (Choose 3)   Create view on ORDERS table, which consists of these 2 cols.

- You have been granted create view permission in the database. Eric has been granted Create table permission in the database. All other users and logins will use AGENT database role to retrieve the information from the database.

Eric has created a table ORDERS in the database. Users only needs to view information in two columns from the table ORDERS.

What are the possible options for you? (Choose 3)   Grant AGENT role select permission on the above created view.

- You have been hired as a database developer for an insurance agency. The company is using an SQL Server 2000 database. Client information is stored in this database. Many of the critical queries in the database join three tables named House, Year, and Size. These tables are updated infrequently. You want to improve the response time of the critical queries. What should you do?   Create an INSTEAD OF trigger on the view.

- You have been hired as a database developer for an online shoe retailer. The company uses an SQL Server 2000 database. The database is used to process online transactions. You have been assigned the task of writing stored procedures that will produce critical sales reports. The stored procedures access existing tables, which are indexed. Before you put the stored procedures in the production environment, you want to ensure optimal performance of the new stored procedures. You also want to ensure that daily operations in the database are not adversely affected. What should you do?   Use output from the Index Tuning Wizard to identify whether indexes should be added.

- You have database named SALES. It has a table named INVENTORY. New sales information to the database updates the inventory table. When the process execute it gives an error message ransaction was deadlocked on resources with another process etc. " The stored procedure looks like the following.

CREATE PROCEDURE Update Inventory @ Int ID int As Begin DECLARE @ COUNT int BEGIN TRANS SELECT @ Count = Available >From Inventory with (Hold Lock) WHERE Inventory ID = @ Int ID IF ( @ Count >0) UPDATE Inventory SET Available = Count - 1 Where Inventory ID = @ Int ID COMMIT TRANS END   Change Table hint to UPDLOCK

- You have database named SALES. It has a table named INVENTORY. New sales information to the database updates the inventory table. When the process execute it gives an error message ransaction was deadlocked on resources with another process etc. " The stored procedure looks like the following.

CREATE PROCEDURE Update Inventory @ Int ID int As Begin DECLARE @ COUNT int BEGIN TRANS SELECT @ Count = Available >From Inventory with (Hold Lock) WHERE Inventory ID = @ Int ID IF ( @ Count >0) UPDATE Inventory SET Available = Count - 1 Where Inventory ID = @ Int ID COMMIT TRANS END   Remove table hint

- You have designed procedures , both stored procedures updates same table but of the two procedures execution of one procedure and its updates has higher priority than the other stored procedure. When you fire both these procedure you often get message as transaction #4 has become victim of deadlock?

You want to prevent this situation and let the stored procedure which has a higher priority to be executed successfully.

What will you do?   set deadlock_priority low for the non important transaction Don remember other 2 options since I found them irrelevent.

- You have designed the database for a Web site (or online ticketing agency) that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor.

For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor?   Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.

- You have designed the database for a Web site that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor. For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor?   Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.

- You have designed the database for a web site that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempt to purchase the tickets. This list of available tickets is retrieved in a cursor. For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data.

How should you design the cursor?   Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.

- You have designed two transactions as follows:

Begin transaction Begin transaction Update Customer Update CustomerHistory ? ? Update CustomerHistory Update Customer ? ? Commit Transaction Update Table3.. Commit Transaction

Above two transactions occasionally gives you error as transaction #4 has become victim of deadlock. You want to execute both the transaction though either of them is a victim of deadlock

What will you do?   In second transaction put update customer statement before updating customerhistory

- You have designed two transactions as follows:

Begin transaction Begin transaction Update Customer Update CustomerHistory ? ? Update CustomerHistory Update Customer ? ? Commit Transaction Update Table3.. Commit Transaction

Above two transactions occasionally gives you error as transaction #4 has become victim of deadlock. You want to execute both the transaction though either of them is a victim of deadlock

What will you do?   Set deadlock priority low

- You have developed a new database for your development group to use when testing their new application. When the database was first passed to them, neither you nor they were concerned with data recovery. As a result, the database initially carried a log file of only 5MB (with autogrow, but no autoshrink) and had its truncate log on checkpoint option set on. Midway through their tests, however, one of the developers turned the option off, and consequently the log automatically grew to its current size of 150MB. Concerned about the low amount of free space remaining on the disk drive holding the log, you turn the option on once again. What configuration would you expect after the next checkpoint has passed?   The log would have been truncated, but the log allocation remains at 150MB.

- You have developed a new database for your development group to use when testing their new application. When the database was first passed to them, neither you nor they were concerned with data recovery. As a result, the database initially carried a log file of only 5MB (with autogrow, but no autoshrink) and had its truncate log on checkpoint option set on. Midway through their tests, however, one of the developers turned the option off, and consequently the log automatically grew to its current size of 150MB. Concerned about the low amount of free space remaining on the disk drive holding the log, you turn the option on once again. What configuration would you expect after the next checkpoint has passed?   The log would have been truncated, but the log allocation remains at 150MB.

- You have written a stored procedure that takes several parameters and uses them to issue appropriate queries to the database. Most of the parameters are optional, and if all are passed the resulting query you have to run can be quite complex. There is a great deal of conditional logic in the procedure based on the parameters passed. When testing this procedure you found that when you first created it, it worked great even though you started with a rather complicated set of parameters. Since then performance has been disappointing. Every time you re-create it, it works fast once, but rarely shows such speed on later calls. What is the most likely cause of the problem?
   You have forgotten to specify the With Recompile option, which is causing an inappropriate query plan to be used with subsequent runs.

- You need to install SQL Server Developer Edition on a spare machine to deliver to a new developer in your company so he can study for his certification exam. Of the following machines that can be spared, what is the least powerful machine you can use for this purpose?
   Pentium 166MHz, 64MB memory

- You want to see the execution plan for the insert statement only. Your SQL stament batch is as follows

SET SHOWPLAN_TEXT ON GO CREATE TABLE X (ID NUMERIC(12), NAME VARCHAR(500)) INSERT INTO X(ID,NAME) SELECT ID, EMP_NAME FROM EMP_DET GO

When you fire the above statements you get message as table X does not exists.

What is the possible cause of above problem.   Fire the create table statement before SET SHOWPLAN_TEXT ON

- You want to see the execution plan for the insert statement only. Your SQL statement batch is as follows:

SET SHOWPLAN_TEXT ON GO CREATE TABLE X (ID NUMERIC(12), NAME VARCHAR(500))

INSERT INTO X(ID,NAME) SELECT ID, EMP_NAME FROM EMP_DET GO

When you fire the above statements you get message as table X does not exists.

What is the possible cause of above problem?   Fire the create table statement before SET SHOWPLAN_TEXT ON

- You were recently hired to oversee the installation of SQL Server to support a new accounting package for a small business. The company is cost-conscious, so you need to make a conservative recommendation about where to install SQL Server in the company's existing structure. Which of the following makes the best choice to support SQL Server?
   Pentium 1GHz machine with 512MB memory recently purchased to replace the president's workstation

- Your company has two servers running Microsoft SQL 2000 Enterprise Edition. The CORP1 server stores current sales info, CORP2 stores historical sales info. The servers are not linked. You need to make quarterly reports that query the historical data, you will run the report on CORP1. You need the query to run as fast as possible. What do you do?(choose 3)   Run the sp_addlinkedserver stored procedure on CORP1

- Your company has two servers running Microsoft SQL 2000 Enterprise Edition. The CORP1 server stores current sales info, CORP2 stores historical sales info. The servers are not linked. You need to make quarterly reports that query the historical data, you will run the report on CORP1. You need the query to run as fast as possible. What do you do?(choose 3)   Run the sp_addlinkedsrvlogin stored procedure on CORP1

- Your company has two servers running Microsoft SQL 2000 Enterprise Edition. The CORP1 server stores current sales info, CORP2 stores historical sales info. The servers are not linked. You need to make quarterly reports that query the historical data, you will run the report on CORP1. You need the query to run as fast as possible. What do you do?(choose 3)   SELECT InvoiceNo, ProductNo, CustomerNo FROM CORP2.Sales.dbo.Invoices

- Your company is building a new database that will be used to support a Web page. The database will be marked as read-only during normal operations, and will receive small updates through a batch process once a month. The server has two physical disk drives. The problem is to configure the database files to deliver maximum performance for the searches from the Web site. Your boss tells you to create the database with the primary data file on one of the disks, and the transaction log on the other. How would you rate this solution?
   This is a poor solution. Although it will work, it overlooks simple methods to increase performance.

- Your company just purchased a third-party sales tracking application. This application has a database called SalesTracking on one of your SQL Servers. You want to replicate the database to another server, and you cannot change any of the database objects, and many of the tables do not have defined Primary Keys. How can you replicate the data?
   Implement snapshot replication.

- Your database contains table named EMPLOYEE, this table has got some confidential data in the fileds EMPLOYEEID and some non-confidential data in the fields Address, City?

You need to make non-confidential information in the table available in XML format to an external application the external application should be able to specify extract format file.   Create stored procedure that returns information from EMPLOYEE table formatted as XML.

- Your database contains table named EMPLOYEE, this table has got some confidential data in the fileds EMPLOYEEID and some non-confidential data in the fields Address, City?

You need to make non-confidential information in the table available in XML format to an external application the external application should be able to specify extract format file.   Create a view which contains only non-confidential information and grant select permission on the view to external application users.

- Your database contains table named EMPLOYEE, this table has got some confidential data in the fileds EMPLOYEEID and some non-confidential data in the fields Address, City-

You need to make non confidential information in the table available in XML format to an external application the external application should be able to specify extract format file.   Create a view which contains only non-confidential information and grant select permission on the view to external application users.

- Your database has a table named CUSTOMERS which consists of 500,000 rows. Queries will be executed against these table. This table is designed as Customer CustomerID CustomerName City Region

There are very few customers who live in the same city as each other, but there are many customers who live in the same region as other. You want to optimize the performance of SELECT statements which includes criteria on the basis of City and Region.

What will you do?   Create composite nonclustered index with first column as CITY and second column as REGION

- Your database has a table named CUSTOMERS which consists of 500,000 rows. Queries will be executed against these table. This table is designed as Customer CustomerID CustomerName City Region

There are very few customers who live in the same city as each other, but there are many customers who live in the same region as other. You want to optimize the performance of SELECT statements which includes criteria on the basis of City and Region.

What will you do?   Create composite nonclustered index with first column as REGION and second column as CITY.

- Your database has many tables which contains more than 1,000,000 rows. You are using RAID disk arrays. Also each table in your database has got one clustered index. You need to implementing proper indexing strategy.

Which options will you go for?   Placed table that are frequently joined on different filegroups. Place heavily accessed tables and its nonclustered indexes on different filegroups.

- Your database has many tables which contains more than 1,000,000 rows. You are using RAID disk arrays. Also each table in your database has got one clustered index. You need to implementing proper indexing strategy.

Which options will you go for?   Placed table that are frequently joined on same filegroups. Place heavily accessed tables and its nonclustered indexes on different filegroups.

- Your database has many tables which contains more than 1,000,000 rows. You are using RAID disk arrays. Also each table in your database has got one clustered index. You need to implementing proper indexing strategy. Which options will you go for?   Placed table that are frequently joined on different filegroups Place heavily accessed tables and its nonclustered indexes on different filegroups.

- You're conducting a class to help some junior-level developers hone their database-development skills. You enter a discussion with them about local and global variables, and are trying to clarify the differences between the two. Which of the following statements can you always make about these two kinds of variables? (Choose two.)   Global variables are really functions.

- You're conducting a class to help some junior-level developers hone their database-development skills. You enter a discussion with them about local and global variables, and are trying to clarify the differences between the two. Which of the following statements can you always make about these two kinds of variables? (Choose two.)   They use different characters to denote themselves.

- You're conducting a class to help some junior-level developers hone their database-development skills. You enter a discussion with them about local and global variables, and are trying to clarify the differences between the two. Which of the following statements can you always make about these two kinds of variables? (Choose two.)   Global variables are really functions.

- You're conducting a class to help some junior-level developers hone their database-development skills. You enter a discussion with them about local and global variables, and are trying to clarify the differences between the two. Which of the following statements can you always make about these two kinds of variables? (Choose two.)   They use different characters to denote themselves.

- You're conducting a class to help some junior-level developers hone their database-development skills. You enter a discussion with them about local and global variables, and are trying to clarify the differences between the two. Which of the following statements can you always make about these two kinds of variables? (Choose two.)   Both of them are available to all connections.

- You're exploring SQL Server 2000¡äs new features for supporting XML documents. You're experimenting by writing queries to the Northwind database.After developing a query capable of producing a working XML document, you would now like to build an XML Schema document describing the validation rules for the generated document. What steps would you take to produce only a schema document from it?

(Choose two answers.)   Add or extend the query¡äs WHERE clause.

- You're exploring SQL Server 2000¡äs new features for supporting XML documents. You're experimenting by writing queries to the Northwind database.After developing a query capable of producing a working XML document, you would now like to build an XML Schema document describing the validation rules for the generated document. What steps would you take to produce only a schema document from it?

(Choose two answers.)   Add a keyword to the FOR XML clause.

- You're exploring SQL Server 2000¡äs new features for supporting XML documents. You're experimenting by writing queries to the Northwind database.After developing a query capable of producing a working XML document, you would now like to build an XML Schema document describing the validation rules for the generated document. What steps would you take to produce only a schema document from it? (choose two)   Add XML-specific notation to the query¡äs column headers.

- You're exploring SQL Server 2000¡äs new features for supporting XML documents. You're experimenting by writing queries to the Northwind database.After developing a query capable of producing a working XML document, you would now like to build an XML Schema document describing the validation rules for the generated document. What steps would you take to produce only a schema document from it? (choose two)   Add or extend the query¡äs WHERE clause.

- You're exploring SQL Server 2000¡äs new features for supporting XML documents. You're experimenting by writing queries to the Northwind database.After developing a query capable of producing a working XML document, you would now like to build an XML Schema document describing the validation rules for the generated document. What steps would you take to produce only a schema document from it? (choose two)   Add a keyword to the FOR XML clause.