You are a database developer for Litware,Inc. You are restructuring the company’s sales database. The
database contains customer information in a table named Customers. This table includes a character
field named Country that contains the name of the country in which the customer is located.
You have created a new table named Country. The scripts that were used to create the Customers and
Country tables are shown in the exhibit.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE dbo.Country
(
CountryID int IDENTITY( 1 , 1 ) NOT NULL,
CountryName char( 20 ) NOT NULL,
CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID)
)
CREATE TABLE dbo.Customers
(
CustomerID int NOT NULL,
CustomerName char( 30 ) NOT NULL,
Country char( 20 ) NULL,
CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID)
)
You must move the country information from the Customers table into the new Country tables as quickly
as possible.
Which script should you use?
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
A. INSERT INTO Country (CountryName)
SELECT DISTINCT Country
FROM Customers
B. SELECT (*) AS ColID, cl.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
D. DECLARE @SQL varchar ( 225 )
SELECT @SQL = ‘bcp “SELECT ColID = COUNT(*), c1. Country’ +
‘FROM (SELECT DISTINCT Country FROM Sales..Customers) AS
cl, ' +
(SELECT DISTINCT Country FROM Sales..Customers) AS c2 '
+
WHERE c1.Country >= c2.Country’ +
‘GROUP BY c1.Country ORDER BY 1 ” ’ +
‘query out c:\country.txt -c’
EXEC master..xp_cmdshell @SQL, no_output
EXEC master..xp_cmdshell ‘bcp Sales..country in c:\country. Txt-c’, no_output
Я выбрал C. Но, видимо, неверно, т.к. в случае наличия NULL в Customers.Country все упадет...
