sql2005en.txt

本实验利用C语言提供的通用软件包

1.Consider the following structure of the Titles table:
Titles
Title
Advance
Type
...
Identify the query that will display the titles of all those books for which the advance amount is 
more than the average advance paid for the business-related books.

	-4分
	1.Select title 
from titles 
where advance > 
(Select advance 
from titles 
where type = ‘business’)
	2.Select title 
from titles 
where advance > 
(Select avg(advance) 
  from titles 
  where type = ‘business’)
	3.Select title 
from titles 
where advance >= 
(Select avg(advance) 
  from titles 
  where type = ‘business’)
	4.Select title 
from titles 
where advance > avg (advance) 
and type = ‘business’
	B

2.You are developing an application for RedSky Inc., which is in the process of expansion. The 
application should display the formatted report of the status of positions available in the company. 
You are required to create a procedure named prcGetPos that will return the position description 
and the position requirement for a specific code passed as Pcode  (in character format). 
You have created a procedure with the following syntax:
CREATE PROCEDURE prcGetPos
       @Pcd char(4),@Description char(20), @RD int AS
BEGIN
   ...
END
When you execute this procedure from another procedure, it does not return any value to the 
calling procedure. What should the correct syntax?

-4
	1.CREATE PROCEDURE prcGetPos 
@Pcd char(4) OUTPUT,@Description char(20) OUTPUT, @RD int OUTPUT AS 
BEGIN 
... 
END
	2.CREATE PROCEDURE prcGetPos 
       @Pcd char(4),@Description OUTPUT char(20), @RD int AS 
BEGIN 
   ... 
END
	3.ALTER PROCEDURE prcGetPos 
       @Pcd char(4),@Description char(20), @RD int OUTPUT AS 
BEGIN 
... 
END
	4. CREATE PROCEDURE prcGetPos 
       @Pcd char(4),@Description char(20) OUTPUT, @RD int OUTPUT AS 
BEGIN 
   ... 
END
	D

3.A wholesale cloth merchant maintains a system that automatically updates the required tables 
whenever a transaction takes place. When a new row is added to the Orders table, the 
iQuantityOnHand attribute in the Products table must reduce accordingly. 
Click on the exhibit button to view the structures of the Products and the Orders table.
Which of the following triggers should be created to ensure the above update?
 
-3
	1.An insert trigger on the Products table.
	2. An update trigger on the Orders table.
	3.An insert trigger on the Orders table.
	4.An update trigger on the Products table.
	C

4.You have created a stored procedure named prcDisplayEmpDetails in the AdventureWorks 
database. This procedure displays the Employee Id, and 
Login Id of all the employees. Now, you are also required to display the Manager id and title of 
the employees along with the existing employee details. 
What will you do to implement this in the existing stored procedure?

-3
	1.Delete the prcDisplayEmpDetails stored procedure and create a new one.
	2.Create another procedure displaying the employee id, login id, manager id and title of all 
the employees.
	3.Alter the prcDisplayEmpDetails stored procedure to display the employee id, login id, 
manager id and title of all the employees.

	4.Create another procedure displaying the manager id and title of all the employees and call 
this procedure from the prcDisplayEmpDetails stored procedure.
	C

5.Sam is a database developer in eXpert Transfer Inc. He has been asked to implement Service 
Broker in the database of the company. For the same, he need to create a message type that will 
accept a valid XML snippet against the given schSales schema. Write the code for Sam.
 
-3
	1.CREATE MESSAGE TYPE 
sendMessage 
VALIDATION = VALID_XML WITH SCHEMA COLLECTION schSales
	2.CREATE MESSAGE TYPE 
sendMessage 
VALIDATION = WELL_FORMED_XML WITH SCHEMA COLLECTION schSales
	3.CREATE MESSAGE TYPE 
sendMessage 
VALIDATION = NONE WITH SCHEMA COLLECTION schSales
	4. CREATE MESSAGE  
sendMessage 
VALIDATION = VALID_XML WITH SCHEMA COLLECTION schSales
	A

6.Jim is creating an application using SQL database. The application allows a user to create 
customized output using various tables. Jim wants to use views in his application. Which of the 
following is NOT an advantage of using views?
-2
	1.Providing relevant data from different tables.
	2.Changing design of the table.
	3.Hiding data complexity.
	4.Organizing data from heterogeneous sources.
	B

7.You are developing an application for RedSky Inc. to manage the project records of the 
organization. To remove data redundancy, you are normalizing the database structure.  At which 
normalization level will you encounter the condition that every attribute in the row is functionally 
dependent upon the whole key, and not just part of the key?
-2
	1.1NF
	2.2NF
	3. 3NF
	4.Boyce-Codd NF
	B

8.Two AFTER triggers have been created for the DELETE operation on the Employee table as 
follows:
CREATE TRIGGER trgDelete1 ON HumanResources.Employee
AFTER
DELETE
AS
PRINT 'This is the first trigger'
CREATE TRIGGER trgDelete2 ON HumanResources.Employee
AFTER
DELETE
AS
PRINT 'This is the second trigger'
trgDelete1 is the first one to be created. You want to change the execution order of the above two 
triggers so that trgDelete1 is executed after 
the trgDelete2 trigger. For this, the following statement was 
executed:
sp_settriggerorder 'trgDelete2', 'FIRST', 'DELETE'
However, the above statement results in an error. Identify the error and provide the solution.

-4
	1.sp_settriggerorder 'HumanResources.trgDelete2', 'FIRST', 'DELETE'
	2.sp_settriggerorder 'trgDelete1', 'SECOND', 'DELETE'
	3.sp_settriggerorder 'trgDelete2', 'SECOND', 'DELETE'
	4.sp_settriggerorder 'HumanResources.trgDelete1', 'FIRST', 'DELETE'
	A

9.A user-defined function EmployeeDetails is no longer required. The following statement was 
executed to remove it from the database.
DROP FUNCTION 'EmployeeDetails'
However, the above statement is showing error. Provide the solution?
-4
	1.DELETE FUNCTION EmployeeDetails
	2.DROP FUNCTION 'dbo.EmployeeDetails'
	3.DROP FUNC 'EmployeeDetails'
	4.DROP FUNCTION EmployeeDetails
	D

10.George, a database developer, created an assembly, ValidEmailAssembly that validates the email 
addresses entered by the user. The assembly should not be able to access any external resource. 
George executed the following statements to create the assembly:
CREATE ASSEMBLY ValidEmailAssembly FROM 
'C:\ValidEmailAssembly.dll' WITH PERMISSION_SET = UNSAFE
After creating the assembly, George found that the assembly was able to access all the external 
resources. Analyze and provide the solution?
1. 2. 3. 4. 
-4
	1.The assembly should be created specifying SAFE as the PERMISSION_SET.
	2.The assembly should be created specifying NO-ACCESS as the PERMISSION_SET.
	3.The assembly should be created specifying EXTERNAL_ACCESS as the 
PERMISSION_SET.
	4.The assembly should be created specifying NOEXTERNAL_ACCESS as the 
PERMISSION_SET.
	A

11.A view displaying the employee id, department id, login id and title of all the employees has been 
defined as follows:
CREATE VIEW vwEmpDep
AS
SELECT e.EmployeeID, d.DepartmentID, d.LoginID, e.Title
FROM HumanResources.Employee e
JOIN HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID
The following update statement when executed generates an error.
UPDATE vwEmpDep 
SET DepartmentID = 7, Title = 'Engineering Manager'
WHERE EmployeeID = 2
Identify the cause of error in the above statement?

-4
	1.You cannot modify the data in two or more underlying tables through a view.
	2.DepartmentID of the Employee cannot be updated.
	3.You cannot update the data in the table through views.
	4.There is a syntax error in the above UPDATE statement.
	A

12.Sam is a database developer for SafeProducts Corporation. The database table Employees 
contains information regarding the employees. The Manager has asked you to create a report of 
the top 20 highest paid employees displayed in the ascending order of salary.
Which query should you use to accomplish this?
-3
	1.SELECT TOP 20 LastName, FirstName, Title, Salary from Employees Group by 
LastName Order by 3 DESC
	2.SELECT TOP 20 LastName, FirstName, Title, Salary from Employees Order by 3 
DESC
	3.SELECT TOP 20 LastName, FirstName, Title, Salary from Employees Order by 4 
DESC
	4.SELECT TOP 20 LastName, FirstName, Title, Salary from Employees Group by 
LastName Order by 4 DESC
	C

13.You have created the function, fx_Employee that accepts an employee id as a parameter and 
returns the details of the department and shift for that employee?
CREATE FUNCTION fx_Employee( @EmployeeID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM HumanResources.EmployeeDepartmentHistory
        WHERE EmployeeID = @EmployeeID
  )
 
How will you execute the above function to display the details of the employee having 
EmployeeID as 1?

-3
	1.EXECUTE fx_Employee(1)
	2.EXECUTE FUNCTION fx_Employee(1)
	3.SELECT * FROM fx_Employee(1)
	4.SELECT fx_Employee(1)
	C

14.Sam has implemented Service Broker in his database. He need to send message between two 
services named sendService and recieveService. For this, he need to begin a dialog conversation. 
Write the code for Sam.
-3
	1.DECLARE @dialog_handle UNIQUEIDENTIFIER;  
BEGIN DIALOG CONVERSATION @dialog_handle FROM  
SERVICE [sendService] TO SERVICE 'recieveService' ON CONTRACT  
[sendContract];
	2.DECLARE @dialog_handle UNIQUEIDENTIFIER;  
BEGIN CONVERSATION @dialog_handle FROM  
SERVICE [sendService] TO SERVICE 'recieveService' ON CONTRACT  
[sendContract]; 
	3.DECLARE @dialog_handle UNIQUEIDENTIFIER;  
BEGIN DIALOG CONVERSATION @dialog_handle FROM  
SERVICE [recieveService] TO SERVICE 'sendService' ON CONTRACT  
[sendContract];
	4.BEGIN DIALOG CONVERSATION @dialog_handle FROM  
SERVICE [sendService] TO SERVICE 'recieveService' ON CONTRACT  
[sendContract];
	A

15.You are required to create an assembly, ValidEmailAssembly for the ValidEmailAssembly.dll 
stored in the C: drive. You need to ensure that the 
.NET code is not able to access some external resource. How will you create the assembly?
-3
	1.CREATE ASSEMBLY ValidEmailAssembly   
FROM 'ValidEmailAssembly.dll'   
WITH PERMISSION_SET = SAFE  
	2.CREATE ASSEMBLY ValidEmailAssembly   
FROM 'ValidEmailAssembly.dll'   
WITH PERMISSION_SET = UNSAFE  
	3.CREATE ASSEMBLY ValidEmailAssembly   
FROM 'ValidEmailAssembly.dll'   
WITH PERMISSION_SET = EXTERNAL_ACCESS
	4. CREATE ASSEMBLY ValidEmailAssembly   
FROM 'ValidEmailAssembly.dll'   
WITH PERMISSION_SET = NO_ACCESS  
	A

16.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 syntax 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 PRIMARY KEY (EmployeeID)
)
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 the 
data for non-administrative employees only. 
What should you do?

-3
	1. Create a view, and include the WITH ENCRYPTION clause.
	2.Create a view using the WITH CHECK OPTION clause.
	3.Create a stored procedure that requires a EmpType as a parameter. Include the WITH 
RECOMPILE option when the procedure is created.
	4.Create one stored procedure for each EmpType. 
	B

17.You have created a procedure, getOrderDetails that returns a result set. You need to create an 
HTTP endpoint named sqlEndpoint to provide the 
getOrderDetails procedure as a Web method. You can use the authentication method that uses 
encryption for secure transmission of passwords. The 
URL of the endpoint is “/sql/AdventureWorks”. The procedure is created in the dbo schema of the 
AdventureWorks database. The information required 
in response to the client from the endpoint should include the result set, a row count, error 
messages, and warnings in the SOAP response. How will 
you create the endpoint?

-3
	1.CREATE ENDPOINT sqlEndpoint   
STATE = STARTED AS HTTP(   
PATH = '/sql/AdventureWorks',   
AUTHENTICATION = (NTLM),   
PORTS = ( CLEAR ), SITE = 'localhost' )   
FOR SOAP (   
WEBMETHOD 'getOrderDetails' 
(name='AdventureWorks.dbo.getOrderDetails',FORMAT = ALL_RESULTS),   
WSDL =
	2.CREATE ENDPOINT sqlEndpoint   
STATE = STARTED AS HTTP(   
PATH = '/sql/AdventureWorks',   
AUTHENTICATION = (DIGEST),   
PORTS = ( CLEAR ), SITE = 'localhost' )   
FOR SOAP (   
WEBMETHOD 'getOrderDetails' 
(name='AdventureWorks.dbo.getOrderDetails',FORMAT = ROWSETS_ONLY),   
WSDL =
	3.CREATE ENDPOINT sqlEndpoint   
STATE = STARTED AS HTTP(   
PATH = '/sql/AdventureWorks',   
AUTHENTICATION = (KERBEROS),   
PORTS = ( CLEAR ), SITE = 'localhost' )   
FOR SOAP (   
WEBMETHOD 'getOrderDetails' 
(name='AdventureWorks.dbo.getOrderDetails',FORMAT = ROWSETS_ONLY),   
WSDL
	4.CREATE ENDPOINT sqlEndpoint   
STATE = STARTED AS HTTP(   
PATH = '/AdventureWorks',   
AUTHENTICATION = (INTEGRATED),   
PORTS = ( CLEAR ), SITE = 'localhost' )   
FOR SOAP (