Designing And Implementing Databases With SQL Server 2000
Settings
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)
- 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.
- 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.
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- 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
- 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.
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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) )
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
Back to top