Designing and Implementing Databases with SQL Server 2000 Quiz

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

Add Tom to the db_datareader database role.

. Revoke SELECT permissions on the Salary and BonusPercentage columns of the Employees table forTom�s database user account.

Add Tom to the db_accessadmin database role.

not-available-via-ai

Explanation

not-available-via-ai

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

Set the data type of the column to int, and set the IDENTITY property for the column.

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.

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.

3. 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?

CREATE STATISTICS Sales WITH FULLSCAN

Sp_autostats �Sales�

UPDATE STATISTICS Sales WITH FULLSCAN ALL

not-available-via-ai

Explanation

not-available-via-ai

4. 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?

Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.

Remove the ClassroomID column, and base the PRIMARY KEY constraint on theClassroomNumber and ClassTime columns.

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.

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.

5. 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?

Execute the DBCC UPDATEUSAGE statement against the database to update the sysindexes system table.

Execute the DBCC SHOW_STATISTICS statement to find high-density indexes. Drop the high-density indexes.

Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.

Use SQL profiler to find table scans. Add indexes to tables that were found to have table scans.

not-available-via-ai

Explanation

not-available-via-ai

6. 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?

Add your login name to the HR database role.

Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

Disable the trigger before executing the DROP TRIGGER statement.

Define the trigger number in the DROP TRIGGER statement.

not-available-via-ai

Explanation

not-available-via-ai

7. 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 ON UPDATE clause to modify the Products table.

Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Products table.

Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Companies table.

Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Companies table.

Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Products table.

not-available-via-ai

Explanation

not-available-via-ai

8. 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?

Rewrite the query to eliminate BETWEEN keyword.

Add a join hint that includes the HASH option to the query.

Add the WITH (INDEX(0)) table hint to the Policy table.

Update statistics on the Policy table.

not-available-via-ai

Explanation

not-available-via-ai

9. 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?

SELECT Country FROM CustomersGROUP BY Country HAVING COUNT (Country)>10

SELECT TOP 10 Country FROM Customers

SELECT TOP 10 Country FROM CustomersFROM (SELECT DISTINCT Country FROM Customers) AS XGROUP BY Country HAVING COUNT(*)> 10

SET ROWCOUNT 10SELECT Country, COUNT (*) as �NumCountries�FROM CustomersGROUP BY Country ORDER BY NumCountries, Desc

not-available-via-ai

Explanation

not-available-via-ai

10. 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?

BEGIN TRANSACTION OrderINSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)SAVE TRANSACTION Order

INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)IF (@@Error = 0)COMMIT TRANSACTIONELSEROLLBACK TRANSACTION

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.

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.

11. 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?

Create a stored procedure that requires ContainerID as an argument and returns the volume of the container

Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.

Add a computed column to the Container table that calculates the volume of the container.

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.

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.

12. 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?

Use a scrollable static cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

Use a scrollable dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases

Use a stored procedure to retrieve the list of tickets.Use a second stored procedure to make purchases.

Use a user-defined function to retrieve the list of tickets.Use a second stored procedure to make purchases

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.

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.

13. 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?

Create a view that displays the order information for a customer.

Create a scalar user-defined function that retrieves the order information for a customer.

Grant SELECT permissions on the Order and LineItem tables to the customers.

Create a stored procedure that retrieves the order information for a customer.

not-available-via-ai

Explanation

not-available-via-ai

14. 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?

Include the WITH CHECK OPTION clause in the view definition.

Create a view that includes all columns in the Department table and the SQLLogin column from the Employees table.

Grant INSERT and UPDATE permissions on the Department table.

not-available-via-ai

Explanation

not-available-via-ai

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

Execute each query in SQL Query Analyzer, and use the Show Execution Plan option. Use output for the report

Run the Index Tuning Wizard against a Workload file. Use the output for the report.

Execute the DBCC SHOW_STATISTICS statement. Use the output for the report.

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.

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.

16. 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?

SELECT l.LocationName, e.FirstName, e.LastNameFROM Employee AS e LEFT OUTER JOIN Location AS 1ON e.LocationID= l.LocationIDORDER BY l.LocationName, e.LastName, e.FirstName

SELECT l.LocationName, e.FirstName, e.LastNameFROM Location AS 1 LEFT OUTER JOIN EMPLOYEE AS 1ON e.LocationID= l.LocationIDORDER BY l.LocationName, e.LastName, e.FirstName

SELECT l.LocationName, e.FirstName, e.LastNameFROM Employee AS e FULL OUTER JOIN Location AS 1ON e.LocationID= l.LocationIDORDER BY l.LocationName, e.LastName, e.FirstName

SELECT l.LocationName, e.FirstName, e.LastNameFROM Employee AS e CROSS JOIN Location AS 1ORDER BY l.LocationName, e.LastName, e.FirstName

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.

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.

17. 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?

Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.

not-available-via-ai

Explanation

not-available-via-ai

18. 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?

Set the query rowcount to five. Use a SELECT statement to retrieve and summarize the survey data.

Use a SELECT TOP 5 statement to retrieve and summarize the survey data.

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.

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.

19. 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?

Create a table-level CHECK constraint on the MotherID and FatherID columns.

Create a rule and bind it to the MotherID. Bind the same rule to the FatherID column.

not-available-via-ai

Explanation

not-available-via-ai

20. 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?

INSERT INTO Country (CountryName)SELECT DISTINCT CountryFROM Customers

not-available-via-ai

Explanation

not-available-via-ai

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

Create a cascading referential integrity constraint on the Reservations table to create the History tableentries.

Create a view on the Reservations table. Include the WITH SCHEMABINDING option in the viewdefinition.

Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the viewdefinition.

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.

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.

22. 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 one stored procedure for each postal code

Create one view for each postal code.

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.

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.

23. 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?

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.

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.

24. 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, �24 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 the bcp utility, and specify the �t option.

Use the BULK INSERT statement, and specify the FIRE_TRIGGERS argument.

Use the SQL-DMO BulkCopy2 object and set the TableLock property to TRUE.

Use Data Transformation Services to create two Transform Data tasks.

not-available-via-ai

Explanation

not-available-via-ai

25. 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?

Create a stored procedure to retrieve all of the data that is loaded into the list box.

Use an API server-side cursor to retrieve the data that is loaded into list box.

Use a Transact-SQL server-side cursor to retrieve the data that is loaded into the list box.

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.

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.

26. 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?

Create a view, and include the WITH ENCRYPTION clause.

Create a view, and include the WITH CHECK OPTION clause.

Create a view, and include the SCHEMABINDING clause.

Create a view, and build a covering index on the view.

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.

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.

27. 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?

Replace the lines 12-17 of the stored procedure with the following:Return @@ROWCOUNT

Replace line 5 of the stored procedure with the following:Return @@Error

not-available-via-ai

Explanation

not-available-via-ai

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

Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.

Add a nonclustered index on the City column.

Add a nonclustered index on the AreaCode, Exchange, and Number columns.

Remove the unique index from the PhoneNumberID column.

Change the PRIMARY KEY constraints to nonclustered indexes.

not-available-via-ai

Explanation

not-available-via-ai

Submit

29. 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 DELETE statement to remove the data from the table.

Use a TRUNCATE TABLE statement to remove the data from the table.

Use a DROP TABLE statement to remove the data from the table.

Use an updateable cursor to access and remove each row of data from the table.

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.

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.

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

Create a user-defined function that accepts a parameter with a default value of 101.

Create a default of 101 on the FlightTimes table.

not-available-via-ai

Explanation

not-available-via-ai

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?

UPDATE ProductsSET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPriceFROM Products p1, PendingProductUpdate p2WHERE p1.ProductID= p2.ProductIDGOTRUNCATE TABLE PendingProductUpdateGO

. UPDATE Products p1SET [Description] = p2.[Description], UnitPrice = p2.UnitPriceFROM Products, PendingProductUpdate p2WHERE p1.ProductID= p2.ProductIDGOTRUNCATE TABLE PendingProductUpdateGO

UPDATE Products p1SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPriceFROM (SELECT [Description], UnitPriceFROM PendingProductUpdate p2WHERE p1.ProductID= p2.ProductID)GOTRUNCATE TABLE PendingProductUpdateGO

UPDATE p1SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPriceFROM Products p1, PendingProductUpdate p2WHERE p1.ProductID= p2.ProductIDGOTRUNCATE TABLE PendingProductUpdate

not-available-via-ai

Explanation

not-available-via-ai

32. 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?

not-available-via-ai

Explanation

not-available-via-ai

33. 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?

SELECT m.ModelName, c.ColorNameFROM Colors AS c FULL OUTER JOIN Models AS mON c.ColorID = m.ColorIDORDER BY m.ModelName, c.ColorName

SELECT m.ModelName, c.ColorNameFROM Colors AS c CROSS JOIN Models AS mORDER BY m.ModelName, c.ColorName

. SELECT m.ModelName, c.ColorNameFROM Colors AS m INNER JOIN Colors AS cON m.ColorID = c.ColorIDORDER BY m.ModelName, c.ColorName

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.

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.

34. 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?

not-available-via-ai

Explanation

not-available-via-ai

35. 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 PERCENT LastName, FirstName, SUM (OrderAmount) AS ytdFROM salesGROUP BY LastName, FirstNameORDER BY 3 DESC

SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytdFROM salesGROUP BY LastName, FirstNameORDER BY 3 DESC

SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytdFROM salesGROUP BY LastName, FirstNameORDER BY 3 DESC

SELECT TOP 20 LastName, FirstName, MAX(OrderAmount) AS ytdFROM salesGROUP BY LastName, FirstNameORDER BY 3 DESC

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.

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.

36. 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?

not-available-via-ai

Explanation

not-available-via-ai

37. 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?

Create a stored procedure that returns the rows that apply to a specified position type.

Create a view that is defined to return the rows that apply to a specified position type.

Grant SELECT permissions on the EmployeePositionType column for each position type

Grant permission on the News table for each position type.

not-available-via-ai

Explanation

not-available-via-ai

38. 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?

SIZE = 1GB

FILEGROWTH = 20

FILEGROWTH = 5%

SIZE= 2048KB

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.

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.

39. 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?

Create a covering index for each query contained in the stored procedures.

For each query in the stored procedures, create an index that includes each column contained in the WHERE clause.

Use output from the Index Tuning Wizard to identify whether indexes should be added.

CREATE STATISTICS on all columns in the SELECT and WHERE clauses of each query.

not-available-via-ai

Explanation

not-available-via-ai

40. 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.)

Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table.

Create a new order table named OrderDetail.Add OrderID, ProductID, and Quantity columns to this table.

Remove the ProductID and Quantity columns from the Orders table.

Create a UNIQUE constraint on the OrderID column of the Orders table.

Move the UnitPrice column from the Products table to the Orders table.

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.

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.

Submit

Designing and Implementing Databases With SQL Server 2000 Quiz

2.

What first name or nickname would you like us to use?