Designing And Implementing Databases With SQL Server 2000

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Niketanp

Niketanp

Community Contributor

Quizzes Created: 1 | Total Attempts: 669

Questions: 40 | Attempts: 669 | Updated: Mar 20, 2022

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own Quiz

Microsoft Certification Quizzes & Trivia

Practice Questions for MCDA Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition. This also applies as credit towards MCSD (Microsoft Certified Solution Developer)and Microsoft Certified Application Developer (MCAD)

Questions and Answers

1.

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?
- A.
  
  Set the data type of the column to uniqueidentifier.
- B.
  
  Set the data type of the column to int. Create a user-defined function that selects the maximum order number in the table.
- C.
  
  Set the data type of the column to int. Create a NextKey table, and add a NextOrder column to the table. Set the data type of the NextOrder column to int. Create a stored procedure to retrieve and update the value held in the NextKey.
- D.
  
  Set the data type of the column to int, and set the IDENTITY property for the column.
Correct Answer
D. Set the data type of the column to int, and set the IDENTITY property for the column.

Explanation
The correct answer is to set the data type of the column to int and set the IDENTITY property for the column. This is because an order number needs to be unique and assigned in ascending order. By setting the data type to int and using the IDENTITY property, the database will automatically generate a unique and incremental order number for each new order entered. This ensures that each order will have a unique identifier and that the order numbers will be assigned in the desired order.

Rate this question:

1
0
2.

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 the exhibit. You want to promote quick response times for queries and minimize redundant data. What should you do?
- A.
  
  Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.
- B.
  
  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.
- C.
  
  Remove the PRIMARY KEY constraint from the Courses table, and replace the PRIMARY KEY constraint with a composite PRIMARY KEY constraint based on the CourseID and CourseTitle.
- D.
  
  Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.
Correct Answer
B. 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.

Explanation
To promote quick response times for queries and minimize redundant data, the correct approach is to create a new table named Instructors. This table should include an InstructorID column, an InstructorName column, and an OfficePhone column. Additionally, an InstructorID column should be added to the Courses table to establish a relationship between instructors and courses. This design allows for efficient querying and avoids redundant storage of instructor information in multiple tables.

Rate this question:
3.

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 the 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.)
- A.
  
  Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table.
- B.
  
  Create a new order table named OrderDetail. Add OrderID, ProductID, and Quantity columns to this table.
- C.
  
  Remove the ProductID and Quantity columns from the Orders table.
- D.
  
  Create a UNIQUE constraint on the OrderID column of the Orders table.
- E.
  
  Move the UnitPrice column from the Products table to the Orders table.
Correct Answer(s)
A. Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table.
B. Create a new order table named OrderDetail. Add OrderID, ProductID, and Quantity columns to this table.

Explanation
To promote quick response times for queries and minimize redundant data, it is recommended to create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table. This will ensure that each combination of OrderID and ProductID is unique, allowing for efficient retrieval and elimination of duplicate data. Additionally, creating a new order table named OrderDetail and adding OrderID, ProductID, and Quantity columns to this table will further optimize the database design by separating the order details from the main Orders table, reducing redundancy and improving query performance.

Rate this question:

1
0
4.

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?
- A.
  
  DECLARE @retval int DECLARE @ytd int EXEC get_sales_for_title �Net Etiquette�, @ytd IF @retval < 0 PRINT �No sales found� ELSE PRINT �Year to date sales: � + STR (@ytd) GO
- B.
  
  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
- C.
  
  DECLARE @retval int DECLARE @ytd int EXEC get_sales_for_title �Net Etiquette�,@retval OUTPUT IF @retval < 0 PRINT �No sales found� ELSE PRINT �Year to date sales: � + STR (@ytd) GO
- D.
  
  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
Correct Answer
D. 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
5.

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 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?
- A.
  
  Create a stored procedure that requires ContainerID as an argument and returns the volume of the container
- B.
  
  Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.
- C.
  
  Add a column named volume to the Container table. Create a trigger that calculates and store the volume in this column when a new container is inserted into the table.
- D.
  
  Add a computed column to the Container table that calculates the volume of the container.
Correct Answer
B. Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.

Explanation
Creating a user-defined function that requires ContainerID as an argument and returns the volume of the container is the best option in this scenario. This approach allows the volume calculation to be encapsulated within the function, making it easier to access in a SELECT query with the rest of the container information. It also promotes code reusability, as the function can be used in multiple queries or procedures without duplicating the calculation logic.

Rate this question:
6.

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?
- A.
  
  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.
- B.
  
  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.
- C.
  
  Create an INSTEAD OF UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use SQLAgentMail to send an e-mail message to the supply manager�s pager.
- D.
  
  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.
Correct Answer
A. 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.
7.

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 Order 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 the 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?
- A.
  
  Create a view that displays the order information for a customer.
- B.
  
  Create a scalar user-defined function that retrieves the order information for a customer.
- C.
  
  Grant SELECT permissions on the Order and LineItem tables to the customers.
- D.
  
  Create a stored procedure that retrieves the order information for a customer.
Correct Answer
D. Create a stored procedure that retrieves the order information for a customer.
8.

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 the 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?
- A.
  
  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.
- B.
  
  Include the WITH CHECK OPTION clause in the view definition.
- C.
  
  Create a view that includes all columns in the Department table and the SQLLogin column from the Employees table.
- D.
  
  Grant INSERT and UPDATE permissions on the Department table.
Correct Answer
C. Create a view that includes all columns in the Department table and the SQLLogin column from the Employees table.
9.

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?
- A.
  
  CREATE STATISTICS Sales WITH FULLSCAN
- B.
  
  Sp_autostats �Sales�
- C.
  
  UPDATE STATISTICS Sales WITH FULLSCAN ALL
Correct Answer
C. UPDATE STATISTICS Sales WITH FULLSCAN ALL
10.

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?
- A.
  
  Create a covering index for each query contained in the stored procedures.
- B.
  
  For each query in the stored procedures, create an index that includes each column contained in the WHERE clause.
- C.
  
  Use output from the Index Tuning Wizard to identify whether indexes should be added.
- D.
  
  CREATE STATISTICS on all columns in the SELECT and WHERE clauses of each query.
Correct Answer
C. Use output from the Index Tuning Wizard to identify whether indexes should be added.
11.

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?
- A.
  
  Execute the DBCC UPDATEUSAGE statement against the database to update the sysindexes system table.
- B.
  
  Execute the DBCC SHOW_STATISTICS statement to find high-density indexes. Drop the high-density indexes.
- C.
  
  Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.
- D.
  
  Use SQL profiler to find table scans. Add indexes to tables that were found to have table scans.
Correct Answer
C. Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.
12.

You are a database developer for Proseware, Inc. You are creating a database named HumanResources 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. HumanResources 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?
- A.
  
  SIZE = 1GB
- B.
  
  FILEGROWTH = 20
- C.
  
  FILEGROWTH = 5%
- D.
  
  SIZE= 2048KB
Correct Answer
C. FILEGROWTH = 5%

Explanation
Explanation: The FILEGROWTH parameter determines the amount of space that the database files will grow by when they need to expand. By specifying FILEGROWTH = 5%, the database files will grow by 5% each time expansion is needed. This allows for gradual and incremental growth, which helps conserve disk space and minimize the number of times that expansion needs to occur. The SIZE parameter determines the initial size of the database files, but it does not directly affect the growth rate. Therefore, specifying SIZE = 1GB or SIZE = 2048KB does not address the goal of conserving disk space and minimizing expansion.

Rate this question:
13.

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?
- A.
  
  Use the ALTER TABLE statement and the ON UPDATE clause to modify the Products table.
- B.
  
  Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Products table.
- C.
  
  Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Companies table.
- D.
  
  Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Companies table.
- E.
  
  Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Products table.
Correct Answer
E. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Products table.
14.

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?
- A.
  
  Grant SELECT permissions on the Salary and BonusPercentage columns of the Employees table for Tom�s database user account.
- B.
  
  Add Tom to the db_datareader database role.
- C.
  
  . Revoke SELECT permissions on the Salary and BonusPercentage columns of the Employees table for Tom�s database user account.
- D.
  
  Add Tom to the db_accessadmin database role.
Correct Answer
A. Grant SELECT permissions on the Salary and BonusPercentage columns of the Employees table for Tom�s database user account.
15.

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 the 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?
- A.
  
  Create a stored procedure that returns the rows that apply to a specified position type.
- B.
  
  Create a view that is defined to return the rows that apply to a specified position type.
- C.
  
  Grant SELECT permissions on the EmployeePositionType column for each position type
- D.
  
  Grant permission on the News table for each position type.
Correct Answer
A. Create a stored procedure that returns the rows that apply to a specified position type.
16.

You are a database developer for a company that produces an online telephone directory. A table named PhoneNumbers is shown in the 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 begins with �W� How should you improve the processing performance of these types of queries? Choose two
- A.
  
  Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.
- B.
  
  Add a nonclustered index on the City column.
- C.
  
  Add a nonclustered index on the AreaCode, Exchange, and Number columns.
- D.
  
  Remove the unique index from the PhoneNumberID column.
- E.
  
  Change the PRIMARY KEY constraints to nonclustered indexes.
Correct Answer(s)
A. Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.
B. Add a nonclustered index on the City column.
17.

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.PolicyNumber) 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 is generated is shown in the estimated execution plan exhibit. What should you do?
- A.
  
  Rewrite the query to eliminate BETWEEN keyword.
- B.
  
  Add a join hint that includes the HASH option to the query.
- C.
  
  Add the WITH (INDEX(0)) table hint to the Policy table.
- D.
  
  Update statistics on the Policy table.
Correct Answer
D. Update statistics on the Policy table.
18.

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?
- A.
  
  Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.
- B.
  
  Execute each query in SQL Query Analyzer, and use the Show Execution Plan option. Use output for the report
- C.
  
  Run the Index Tuning Wizard against a Workload file. Use the output for the report.
- D.
  
  Execute the DBCC SHOW_STATISTICS statement. Use the output for the report.
Correct Answer
A. Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.

Explanation
To create a report showing the indexes used by queries before and after making changes, the best approach is to execute each query in SQL Query Analyzer and use the SHOWPLAN_TEXT option. This option provides a detailed execution plan for each query, including the indexes used. By analyzing the output of SHOWPLAN_TEXT, the database developer can document the indexes used by queries before and after the changes. This approach allows for accurate documentation and helps in evaluating the impact of index changes on query performance.

Rate this question:
19.

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?
- A.
  
  CREATE TABLE Physicians ( Physicians ID int NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY CLUSTERED, LastName varchar(25) NOT NULL, ) GO CREATE TABLE Patients ( PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, PrimaryPhysician int NOT NULL, SecondaryPhysician int NOT NULL, CONSTRAINT PK_Patients_Physicians1 FOREIGN KEY (PrimaryPhysician) REFERENCES Physicians (PhysicianID), CONSTRAINT PK_Patients_Physicians2 FOREIGN KEY (SecondaryPhysician) REFERENCES Physicians (PhysicianID) )
- B.
  
  . CREATE TABLE Patients ( PatientID smallint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar(25) NOT NULL, FirstName varchar (25) NOT NULL, PrimaryPhysician int NOT NULL, SecondaryPhysician int NOT NULL, ) GO CREATE TABLE Physicians ( PhysicianID smallint NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, CONSTRAINT PK_Physicians_Patients FOREIGN KEY (PhysicianID) REFERENCES Patients (PatientID) )
- C.
  
  CREATE TABLE Patients ( PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Physicians ( PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE PatientPhysician ( PatientPhysicianID bigint NOT NULL CONSTRAINT PK_PatientsPhysicians PRIMARY KEY CLUSTERED, PhysicianID int NOT NULL, PatientID bigint NOT NULL, PrimaryPhysician bit NOT NULL, FOREIGN KEY (PhysicianID) REFERENCES Physicians (PhysicianID), FOREIGN KEY (PatientID) REFERENCES Patients (PatientID) )
- D.
  
  CREATE TABLE Patients ( PatientID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Physicians ( PhysicianID 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_PatientsPhysicians PRIMARY KEY (PhysicianID, PatientID) )
Correct Answer
D. CREATE TABLE Patients ( PatientID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Physicians ( PhysicianID 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_PatientsPhysicians PRIMARY KEY (PhysicianID, PatientID) )

Explanation
This script should be used to create the tables because it follows the normalization principles and satisfies the requirements of the database design. The Patients table has a primary key defined on the PatientID column, ensuring uniqueness and efficient data retrieval. The Physicians table also has a primary key defined on the PhysicianID column. The PatientPhysician table serves as a junction table to establish the relationship between patients and physicians, with foreign key constraints referencing the primary keys of the Patients and Physicians tables. Additionally, a composite primary key is defined on the PhysicianID and PatientID columns in the PatientPhysician table. This design allows for efficient querying and maintains data integrity.

Rate this question:
20.

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?
- A.
  
  Add your login name to the HR database role.
- B.
  
  Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.
- C.
  
  Disable the trigger before executing the DROP TRIGGER statement.
- D.
  
  Define the trigger number in the DROP TRIGGER statement.
Correct Answer
B. Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.
21.

You have designed the database for a web site that is used to purchase concert tickets. During a ticket purchase, a buyer views 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?
- A.
  
  Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.
- B.
  
  Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.
- C.
  
  Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction.
- D.
  
  Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.
Correct Answer
B. Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.
22.

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?
- A.
  
  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.
- B.
  
  Use a SELECT INTO statement to retrieve the data for the survey into a temporary table. Use a SELECT TOP 1 statement to retrieve the first row from the temporary table.
- C.
  
  Set the query rowcount to five. Use a SELECT statement to retrieve and summarize the survey data.
- D.
  
  Use a SELECT TOP 5 statement to retrieve and summarize the survey data.
Correct Answer
A. 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.

Explanation
Using a cursor to retrieve all of the data for the survey allows for the retrieval of every row in the SurveyData table. Then, using the FETCH RELATIVE 5 statement, the summary data can be selected from the cursor. This approach ensures that every fifth row of responses is included in the summary, as required.

Rate this question:
23.

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?
- A.
  
  Use the bcp utility, and specify the �t option.
- B.
  
  Use the BULK INSERT statement, and specify the FIRE_TRIGGERS argument.
- C.
  
  Use the SQL-DMO BulkCopy2 object and set the TableLock property to TRUE.
- D.
  
  Use Data Transformation Services to create two Transform Data tasks.
Correct Answer
D. Use Data Transformation Services to create two Transform Data tasks.
24.

You are a database developer for a database named Accounts at oodgrove 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?
- A.
  
  Create a stored procedure to retrieve all of the data that is loaded into the list box.
- B.
  
  Use an API server-side cursor to retrieve the data that is loaded into list box.
- C.
  
  Retrieve all of the data at once by using a SELECT statement, and then load the data into the list box.
- D.
  
  Use a Transact-SQL server-side cursor to retrieve the data that is loaded into the list box.
Correct Answer
B. Use an API server-side cursor to retrieve the data that is loaded into list box.

Explanation
Using an API server-side cursor to retrieve the data that is loaded into the list box is the recommended strategy. This approach allows the developer to retrieve only 25 rows at a time as the user scrolls through the list box, which is more efficient for performance when dealing with a large number of customer accounts. Additionally, using a server-side cursor ensures that the most current list of customers is always available to the application.

Rate this question:
25.

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 haracter 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 the 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 (CustomerID) ) You must move the country information from the Customers table into the new Country tables as quickly as possible. Which script should you use?
- A.
  
  INSERT INTO Country (CountryName) SELECT DISTINCT Country FROM Customers
- B.
  
  SELECT (*) AS ColID, c1.Country INTO Country FROM (SELECT DISTINCT Country FROM Customers)AS c1, (SELECT DISTINCT Country FROM Customers) AS c2, WHERE c1.Country >=c2.Country GROUP BY c1.Country ORDER BY 1
- C.
  
  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
Correct Answer
C. 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
26.

You are a database developer for Contoso, Ltd. The company has a database named HumanResources that contains information about all employees and office locations. The database also contains information about potential employees and office locations. The tables that ontain this information are shown in the 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?
- A.
  
  SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e LEFT OUTER JOIN Location AS 1 ON e.LocationID= l.LocationID ORDER BY l.LocationName, e.LastName, e.FirstName
- B.
  
  SELECT l.LocationName, e.FirstName, e.LastName FROM Location AS 1 LEFT OUTER JOIN EMPLOYEE AS 1 ON e.LocationID= l.LocationID ORDER BY l.LocationName, e.LastName, e.FirstName
- C.
  
  SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e FULL OUTER JOIN Location AS 1 ON e.LocationID= l.LocationID ORDER BY l.LocationName, e.LastName, e.FirstName
- D.
  
  SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e CROSS JOIN Location AS 1 ORDER BY l.LocationName, e.LastName, e.FirstName
Correct Answer
C. SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e FULL OUTER JOIN Location AS 1 ON e.LocationID= l.LocationID ORDER BY l.LocationName, e.LastName, e.FirstName

Explanation
The correct script to use is the one that includes a FULL OUTER JOIN between the Employee table and the Location table. This is because the report needs to display all current and potential employees and office locations, and a FULL OUTER JOIN will include all records from both tables, even if there is no match between them. The script also correctly joins the tables on the LocationID column and orders the results by location name, last name, and first name.

Rate this question:
27.

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 tickets 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?
- A.
  
  Use a scrollable static cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.
- B.
  
  Use a scrollable dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases
- C.
  
  Use a stored procedure to retrieve the list of tickets. Use a second stored procedure to make purchases.
- D.
  
  Use a user-defined function to retrieve the list of tickets. Use a second stored procedure to make purchases
Correct Answer
B. Use a scrollable dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases

Explanation
Using a scrollable dynamic cursor allows the user to view the entire list of available tickets and scroll through it. This type of cursor is able to fetch the data dynamically as the user scrolls, ensuring that the list is always up to date with any tickets that have been sold. Additionally, using positioned updates within the cursor allows the user to make purchases directly from the cursor, without the need for additional stored procedures or functions. This approach provides a seamless and efficient way for the user to view and purchase available tickets.

Rate this question:
28.

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, [DateofBirth] [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?
- A.
  
  Create an AFTER INSERT trigger on the Dogs table that rolls back the transaction of the MotherID or FatherID column is not valid.
- B.
  
  Create a table-level CHECK constraint on the MotherID and FatherID columns.
- C.
  
  Create two FOREIGN KEY constraints: one constraint on the MotherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column.
- D.
  
  Create a rule and bind it to the MotherID. Bind the same rule to the FatherID column.
Correct Answer
C. Create two FOREIGN KEY constraints: one constraint on the MotherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column.
29.

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 the 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?
- A.
  
  Create a view, and include the WITH ENCRYPTION clause.
- B.
  
  Create a view, and include the WITH CHECK OPTION clause.
- C.
  
  Create a view, and include the SCHEMABINDING clause.
- D.
  
  Create a view, and build a covering index on the view.
Correct Answer
B. Create a view, and include the WITH CHECK OPTION clause.

Explanation
By creating a view with the WITH CHECK OPTION clause, it ensures that any data modifications made through the view will be checked against the specified conditions. In this case, the condition would be that only administrative employees can add, update, or delete data for non-executive employees. This ensures that any attempt to modify data for executive employees will be rejected, thereby restricting administrative employees to only modifying non-executive employee data.

Rate this question:
30.

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 in the 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?
- A.
  
  Replace the lines 12-17 of the stored procedure with the following: Return @@ROWCOUNT
- B.
  
  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
- C.
  
  Replace line 5-6 of the stored procedure with the following: DECLARE @count int Replace lines 12-17 with the following: SET @Count = @@ROWCOUNT If @@ERROR = 0 Return @Count Else Return �1
- D.
  
  Replace line 5 of the stored procedure with the following: Return @@Error
Correct Answer
B. 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
31.

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. These tables are shown in the 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?
- A.
  
  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
- B.
  
  . UPDATE Products p1 SET [Description] = p2.[Description], UnitPrice = p2.UnitPrice FROM Products, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO
- C.
  
  UPDATE Products p1 SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPrice FROM (SELECT [Description], UnitPrice FROM PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID) GO TRUNCATE TABLE PendingProductUpdate GO
- D.
  
  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
Correct Answer
D. 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
32.

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 Customers in this database. The script that was used to create this table is shown in the 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?
- A.
  
  SELECT Country FROM Customers GROUP BY Country HAVING COUNT (Country)>10
- B.
  
  SELECT TOP 10 Country FROM Customers
- C.
  
  SELECT TOP 10 Country FROM Customers FROM (SELECT DISTINCT Country FROM Customers) AS X GROUP BY Country HAVING COUNT(*)> 10
- D.
  
  SET ROWCOUNT 10 SELECT Country, COUNT (*) as �NumCountries� FROM Customers GROUP BY Country ORDER BY NumCountries, Desc
Correct Answer
A. SELECT Country FROM Customers GROUP BY Country HAVING COUNT (Country)>10
33.

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?
- A.
  
  SELECT TOP 20 PERCENT LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC
- B.
  
  SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC
- C.
  
  SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC
- D.
  
  SELECT TOP 20 LastName, FirstName, MAX(OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC
Correct Answer
B. SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

Explanation
The correct answer is "SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC". This query selects the top 20 salespeople based on their total sales, including any ties. The "WITH TIES" keyword ensures that if there are multiple salespeople with the same sales amount as the 20th salesperson, they will also be included in the result. The query groups the salespeople by their last name and first name, calculates the sum of their order amounts as "ytd", and orders the result by the third column (ytd) in descending order.

Rate this question:
34.

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?
- A.
  
  Create a stored procedure that accepts a parameter with a default value of 101.
- B.
  
  Create two stored procedures, and specify that one of the stored procedures should accept a parameter and that the other should not.
- C.
  
  Create a user-defined function that accepts a parameter with a default value of 101.
- D.
  
  Create a default of 101 on the FlightTimes table.
Correct Answer
A. Create a stored procedure that accepts a parameter with a default value of 101.
35.

You are a database developer for Wingtip Toys. You have created an order entry database that includes two tables, as shown in the exhibit. Users enter orders into an 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?
- A.
  
  BEGIN TRANSACTION Order INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) SAVE TRANSACTION Order
- B.
  
  INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION
- C.
  
  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
- D.
  
  BEGIN TRANSACTION INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION BEGIN TRANSACTION INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION
Correct Answer
C. 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

Explanation
The correct script to use is the one that begins a transaction, inserts the order values into the Order table, and then checks for any errors. If there are no errors, it proceeds to insert the line item values into the LineItem table. If there are no errors in both inserts, it commits the transaction. If there are errors in either insert, it rolls back the transaction. This ensures that the entire order is saved successfully and maintains data integrity.

Rate this question:
36.

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 errors 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?
- A.
  
  CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN DECLARE @Msg varchar(50) UPDATE Stocks SET CurrentPrice = @Price WHERE StockID = @ StockID AND CurrentPrice @ Price IF @@ERROR 0 RAISERROR (�Error %d occurred updating Stock %d.�, 10, 1, @@ERROR, @StockID) WITH LOG IF @@ROWCOUNT > 0 BEGIN SELECT @Msg = �Stock� + STR (@StockID) + �updated to� + STR (@Price) + �.� EXEC master. . xp_LOGEVENT 50001, @Msg END END
- B.
  
  CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN UPDATE Stocks SET CurrentPrice = @Price WHERE StockID = @ StockID AND CurrentPrice @ Price IF @@ERROR 0 PRINT �ERROR� + STR(@@ERROR) + �occurred updating Stock� +STR (@StockID)+ �.� IF @@ROWCOUNT > 0 PRINT �Stock� + STR (@StockID) + �updated to� + STR (@Price) + �.� END
- C.
  
  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 BEGIN SELECT @Msg = �Error� + STR(@Err) + �occurred updating Stock� + STR (@StockID) + �.� EXEC master..xp_logevent 50001, @Msg END IF @RCOUNT > 0 BEGIN SELECT @Msg = �Stock� + STR (@StockID) + �updated to� + STR (@Price) + �.� EXEC master. . xp_LOGEVENT 50001, @Msg END END
- D.
  
  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
Correct Answer
D. 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
37.

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?
- A.
  
  Create a trigger on the Reservations table to create the History table entries.
- B.
  
  Create a cascading referential integrity constraint on the Reservations table to create the History table entries.
- C.
  
  Create a view on the Reservations table. Include the WITH SCHEMABINDING option in the view definition.
- D.
  
  Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the view definition.
Correct Answer
A. Create a trigger on the Reservations table to create the History table entries.

Explanation
Creating a trigger on the Reservations table is the appropriate solution to create the History table entries. A trigger is a database object that automatically executes a specified action when a certain event occurs. In this case, the trigger can be set to copy the entire existing reservation to the History table whenever an update is made to the Reservations table. This ensures that the history of reservations is accurately maintained.

Rate this question:
38.

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?
- A.
  
  Create a stored procedure that requires a postal code as a parameter. Include the WITH RECOMPILE option when the procedure is created.
- B.
  
  Create one stored procedure for each postal code
- C.
  
  Create one view for each postal code.
- D.
  
  Split the Company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.
Correct Answer
A. Create a stored procedure that requires a postal code as a parameter. Include the WITH RECOMPILE option when the procedure is created.

Explanation
Creating a stored procedure that requires a postal code as a parameter and including the WITH RECOMPILE option ensures that the query will perform consistently and minimize future maintenance. By using a stored procedure, the query can be executed repeatedly without the need for recompiling, which improves performance. Additionally, using a parameter allows for flexibility in retrieving information for different postal codes without the need for separate stored procedures or views. This approach also ensures that minimal changes are made to the database, making it easier to accommodate new data in the future.

Rate this question:
39.

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?
- A.
  
  Use a DELETE statement to remove the data from the table.
- B.
  
  Use a TRUNCATE TABLE statement to remove the data from the table.
- C.
  
  Use a DROP TABLE statement to remove the data from the table.
- D.
  
  Use an updateable cursor to access and remove each row of data from the table.
Correct Answer
B. Use a TRUNCATE TABLE statement to remove the data from the table.

Explanation
Using a TRUNCATE TABLE statement to remove the data from the table is the correct answer because it is the fastest way to delete all the data from a table. Unlike the DELETE statement, which removes rows one by one, the TRUNCATE TABLE statement removes all the data in one operation, making it more efficient. Using a DROP TABLE statement would delete the entire table, not just the data, which is not the desired outcome. Using an updateable cursor to remove each row of data would be slower and less efficient than using the TRUNCATE TABLE statement.

Rate this question:
40.

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 the exhibit. You need to create a list of all possible model and color combinations. Which script should you use?
- A.
  
  SELECT m.ModelName, c.ColorName FROM Colors AS c FULL OUTER JOIN Models AS m ON c.ColorID = m.ColorID ORDER BY m.ModelName, c.ColorName
- B.
  
  SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName
- C.
  
  . SELECT m.ModelName, c.ColorName FROM Colors AS m INNER JOIN Colors AS c ON m.ColorID = c.ColorID ORDER BY m.ModelName, c.ColorName
- D.
  
  SELECT m.ModelName, c.ColorName FROM Colors AS c LEFT OUTER JOIN Models AS m ON c.ColorID = m.ColorID UNION SELECT m.ModelName, c.ColorName FROM Colors AS c RIGHT OUTER JOIN Models AS m ON c.ColorID = m.ColorID ORDER BY m.ModelName, c.ColorName
Correct Answer
B. SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName

Explanation
The correct script to use is the second option: SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName. This script performs a cross join between the Colors and Models tables, which will give all possible combinations of model and color. The result is then ordered by the model name and color name.

Rate this question:

1
0

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

Current Version
Mar 20, 2022

Quiz Edited by
ProProfs Editorial Team
Nov 17, 2006

Quiz Created by
Niketanp