Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
41. You are a database developer for Litware,Inc. You are restructuring the company's sales database. The database contains customer information in a table named customers.
This table includes a character field named country that contains the name of the country in which the customer is located. You have created a new table named country.
The scripts that were used to create the customers and country tables are shown below:
CREATE TABLE dbo.Country
(
CountryID int IDENTITY(1,1) NOT NULL,
CountryName char(20) NOT NULL,
CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID)
)
CREATE TABLE dbo.Customers
(
CustomerID int NOT NULL,
CustomerName char(30) NOT NULL,
Country char(20) NULL,
CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID)
)
You must move the country information from the customers table into the new country tables as quickly as possible.
Which script should you use?
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,'+
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
Ans: C
WARNING!
The answer B fourth row had a typo with c1 incorrectly, should be c2 (corrected).
42. You are a database developer for Contoso, Ltd. The company has a database named Human Resources that contains information about all employees and office locations.
The database also contains information about potential employees and office locations.
The tables that contain this information are shown in 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 l
ON e.LocationID= l.LocationID
ORDER BY l.LocationName, e.LastName, e.FirstName
B. SELECT l.LocationName, e.FirstName, e.LastName
FROM Location AS l LEFT OUTER JOIN EMPLOYEE AS l
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 l
ON e.LocationID= l.LocationID
ORDER BY 1.LocationName, e.LastName, e.FirstName
D. SELECT l.LocationName, e.FirstName, e.LastName
FROM Employee AS e CROSS JOIN Location AS l
ORDER BY l.LocationName, e.LastName, e.FirstName
E. SELECT l.LocationName, e.FirstName, e.LastName
FROM Employee AS e, Location AS l
ORDER BY l.LocationName, e.LastName, e.FirstName
Ans: C
WARNING: in the orig razor the answer "E" was missing.
43. 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 tickets that have been sold to other users. The user should be able to select tickets from the list and purchase the tickets.
You need to design a way for the user to view and purchase available tickets.
What should you do?
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 stored procedures to retrieve the list of tickets. Use a second stored procedure to make purchase.
D. Use a user-defined function to retrieve the list of tickets. Use a second stored procedure to make purchase.
Ans: B
44. You are a database consultant. You have been hired by a local dog breeder to develop a database. This database will be used to store information about the breeder's dogs.
You create a table named Dogs by using the following script:
CREATE TABLE[dbo].[Dogs]
(
[DogID] [int] NOT NULL,
[BreedID] [int] NOT NULL,
[Date of Birth] [datetime] NOT NULL,
[WeightAtBirth] [decimal] (5, 2) NOT NULL,
[NumberOfSiblings] [int] NULL,
[MotherID] [int] NOT NULL,
[FatherID] [int] NOT NULL
)_on [PRIMARY]
GO
ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD
CONSTRAINT [PK_Dogs]PRIMARY KEY CLUSTERED
(
[DogID]
) ON [PRIMARY]
GO
You must ensure that each dog has a valid value for the MotherID and FatherID columns. You want to enforce this rule while minimizing disk I/O.
What should you do?
A. Create an ALTER INSERT trigger on the dogs table that rolls back the transaction if 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.
Ans:C
45. You are the database developer for a company that provides consulting services. The company maintains data about its employees in a table named Employee.
The script that was used to create the employee table is shown below:
CREATE TABLE Employee
(
EmployeeID int NOT NULL,
EmpType char (1) NOT NULL,
EmployeeName char (50) NOT NULL,
Address char (50) NULL,
Phone char (20) NULL,
CONSTRAINT PK_Employee PRMARY KEY (Employee ID)
)
The Emp type column 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.
E. Create a user-defined function that returns a table containing the non-executive employees.
Ans:B
46. 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-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
C. 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
D. Replace line 5 of the stored procedure with the following: Return @@Error
E. Replace line 5 of the stored procedure with the following
DECLARE @Err int, @Count int
Replace line 9 with the following:
SET Balance = Balance 1.01, @Count = Count (*)
Replace line 15 with the following:
Return @Count
Ans:C
47. You are a database developer for an online electronics company. The company's product catalog is contained in a table named Products.
The Products table is frequently accessed during normal business hours. Modifications to the Products table are written to a table named PendingProductUpdate.
The tables are shown 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 Product 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 Product, 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
Ans: D
48. You are the database developer for a sporting goods company that exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database.
The script that was used to create this table is shown below:
CREATE TABLE customers (
CustmerID int NOT NULL,
CustomerName varchar(30) NOT NULL,
ContactName varchar(30) NULL,
Phone varchar(20) NULL,
Country varchar(30) NOT NULL,
CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
)
There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company's marketing department wants to target its advertising to countries that have more than 10 customers.
You need to create a list of these countries for the marketing department.
Which script(s) 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. SELECT Country, COUNT (*) as "NumCountries"
FROM Customers
GROUP BY Country ORDER BY NumCountries Desc
Ans: A
WARNING! Answer D had here a "SET ROWCOUNT 10" as the first line, in the real exam it is NOT there!
49. 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 LastName, FirstName, COUNT(*) AS sales
FROM sales
GROUP BY LastName, FirstName
HAVING COUNT (*) > 20
ORDER BY 3 DESC
C. SELECT TOP 20 LastName, FirstName, MAX(OrderAmount) AS ytd
FROM sales
GROUP BY LastName, FirstName
ORDER BY 3 DESC
D. SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytd
FROM sales
GROUP BY LastName, FirstName
ORDER BY 3 DESC
E. SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd
FROM sales
GROUP BY LastName, FirstName
ORDER BY 3 DESC
Ans: E
50. 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 two stored procedures, and specify that one of the stored procedures should accept a parameter and that the other should not.
B. Create a user-defined function that accepts a parameter with a default value of 101.
C. Create a stored procedure that accepts a parameter with a default value of 101.
D. Create a view that filters the FlightTimes table on a value of 101.
E. Create a default of 101 on the FlightTimes table.
Ans: C