Microsoft SQL Server 2005 Question NIIT (51-100)

51. There is a file named myfile001.txt in the /home/mydir directory. You need to rename the file to myfile002.txt. You are currently in the mydir directory. For this, you typed the following command at the shell prompt:

rm /home/mydir/myfile001.txt /home/mydir/myfile002.txt

Analyze and predict whether the preceding command is able to accomplish the required task. If not, predict the correct command.

a) Yes, the given command will rename the file.

b) No, the correct command is:mv /home/mydir/myfile001.txt /home/mydir/myfile002.txt

c) No, the correct command is:rm myfile001.txt myfile002.txt

d) No, the correct command is:cp /home/mydir/myfile001.txt /home/mydir/myfile002.txt

Answer:b

52. ABC Bank is offering personal loans to its customers at reduced interest rates. This offer is valid for only a limited period. The bank intends to use a software application to record all loan details. The bank wants that once the offer period ends, the interest rates and other details such as loan limits are changed to the original values. The bank does not want to modify the complete software application as a result of change in such loan details. Identify the tier on which the bank should place such details in order to accomplish their requirement.

a) Client tier
b) Server tier
c) Business tier
d) Database tier

Answer:c

53. ___________ provides support for asynchronous communication between clients and the database server, enabling reliable query processing.

a) Service Broker
b) Full-text Search

c) Replication
d) Notification Services

Answer:a

54. Which of the following is NOT a feature of the CLR component of the .NET Framework?

a) Automatic memory management
b) Support for Web services

c) Standard type system
d) Language interoperability

Answer:b

55. Which of the following features of SQL Server 2005 allows you to implement programming logic in any language supported by the .NET Framework?

a) Service-oriented architecture
b) Scalability

c) CLR integration
d) Support for data migration and analysis

Answer:c

56. Jonathan is using the ALTER statement on a database that he has created. Under which of the following options is the statement being used by Jonathan included?

a) Data Manipulation Language
b) Data Control Language

c) Data Query Language
d) Data Definition Language

Answer:d

57. Which of the following mathematical functions provided by SQL Server 2005 returns the largest integer less than or equal to the specified value?

a) floor
b) power
c) radians
d) Abs

Answer:a

58. You need to display a report that includes the Login Ids of the employees and their birth dates. You want that the birth date should be displayed in yy.mm.dd format. Which of the following statements will you use to accomplish the same?

a)SELECT LoginID, convert(char,BirthDate) AS 'Birth Date' FROM HumanResources.Employee

b)SELECT LoginID, convert(char,BirthDate,2) AS 'Birth Date' FROMHumanResources.Employee

c)SELECT LoginID, convert(char(2),BirthDate) AS 'Birth Date' FROM HumanResources.Employee

d)SELECT LoginID, convert(BirthDate(2), char(6)) AS 'Birth Date'FROM HumanResources.Employee

Answer:b

59. You need to generate summary rows from a table by using aggregate functions in the query results. Which of the following options will you use to accomplish the same?

a) COMPUTE
b) GROUP BY
c) COMPUTE BY
d) PIVOT

Answer:a

60. The _______ join displays the result set containing all the rows from one table and the matching rows from another table.

a) inner
b) outer
c) cross
d) equi

Answer:b

61. Which of the following operator refers to a value that is greater than the maximum value in the list?

a) >ALL
b) >ANY
c) =ANY
d) <>ALL

Answer:a

62. You have to retrieve the details of employees from the Employee table, which contains the EmpID and EmpTitle columns. You have to write a query that displays Employee ID and title under the column headings, Employee ID and Designation, respectively. Which of the following SQL queries would you use to perform the required task?

a) SELECT EmpID 'Employee ID', EmpTitle 'Designation' FROM HumanResources.Employee

b) SELECT 'Employee ID' EmpID, 'Designation' EmpTitle FROM HumanResources.Employee

c) SELECT EmpID = 'Employee ID', EmpTitle = 'Designation' FROM HumanResources.Employee

d) SELECT 'Employee ID' AS EmpID, 'Designation' AS EmpTitle FROM HumanResources.Employee

Answer:a

63. You have to create a report containing the Employee ID, title, and total leaves of those employees whose total leaves are not greater than 20. Which of the following SQL queries would you use to create this report?

a) SELECT EmployeeID, Title, TotalLeaves FROM

HumanResources.Employee WHERE TotalLeaves >20

b) SELECT EmployeeID, Title, TotalLeaves FROM

HumanResources.Employee WHERE TotalLeaves != 20

c) SELECT EmployeeID, Title, TotalLeaves FROM

HumanResources.Employee WHERE TotalLeaves < 20

d) SELECT EmployeeID, Title, TotalLeaves FROM

HumanResources.Employee WHERE TotalLeaves <= 20

Answer:d

64. You have to retrieve the Name, DepartmentID, and GroupName columns from the Department table. The data of the Name column should be displayed in capital letters with a user-defined heading, Department Name. Which of the following SQL queries would you use to retrieve the required information in the desired format?

"1, SELECT upper(Name), DepartmentID, GroupName FROM HumanResources.Department"

"2, SELECT 'Department Name'= Name, DepartmentID, GroupName FROM HumanResources.Department"

3, SELECT 'Department Name'= patindex(Name), DepartmentID,GroupNameFROM

HumanResources.Department

4, SELECT 'Department Name'= upper(Name), DepartmentID, GroupName FROM HumanResources.Department

65 You need to create a report containing the product ID along with their list prices. The products should be ranked based on their list price. The product with the highest value of list price should be given the rank as 1. In addition, if two products have the same list price, they should be given the same rank. However, the next product in the order of list price would be assigned the next rank value. Which of the following SQL queries would you use to retrieve the required report?

"1, SELECT ProductId, ListPrice, rank() OVER(ORDER BY ListPrice desc) AS rank

FROM Production.Product"

"2, SELECT ProductId, ListPrice, dense_rank() OVER(ORDER BY ListPrice desc)AS rank

FROM Production.Product"

"3, SELECT ProductId, ListPrice, row_number() OVER(ORDER BY ListPrice asc) AS rank

FROM Production.Product"

"4, SELECT ProductId, ListPrice, rank() OVER(ORDER BY ListPrice asc) AS rank

FROM Production.Product"

66 You have to display a report that contains the Employee ID and the name of department for each employee. The Employee ID is stored in the EmployeeDepartmentHistory table, and the department name is stored in the Department table. The Department and EmployeeDepartmentHistory tables contain a common column, DepartmentID. Which of the following SQL queries would you use to display the required report?

"1, SELECT e.EmployeeID, d.Name FROM HumanResources.Department d JOIN HumanResources.EmployeeDepartmentHistory e ON e.DepartmentID = d.DepartmentID"

"2, SELECT e.EmployeeID, d.Name FROM HumanResources.Department d OUTER JOIN HumanResources.EmployeeDepartmentHistory e ON e.DepartmentID = d.DepartmentID"

"3, SELECT e.EmployeeID, d.Name FROM HumanResources.Department d

EQUI JOIN HumanResources.EmployeeDepartmentHistory e ON e.DepartmentID = d.DepartmentID"

"4, SELECT e.EmployeeID, d.Name FROM HumanResources.Department d CROSS JOIN HumanResources.EmployeeDepartmentHistory e ON e.DepartmentID = d.DepartmentID"

67 "You have to create a report that displays the number of years an employee has been working with the organization. For this, you created the following SQL query:

SELECT datepart (yy, HireDate, getdate()) AS 'Age'

FROM HumanResources.Employee

However, the preceding query does not compile. Identify the reason and provide the correct solution."

"1, SELECT datepart (yy, HireDate) AS 'Age'FROM HumanResources.Employee"

"2, SELECT datediff (yy, HireDate, getdate()) AS 'Age'FROM HumanResources.Employee"

"3, SELECT dateadd (yy, HireDate, getdate()) AS 'Age'FROM HumanResources.Employee"

"4, SELECT day (HireDate) AS 'Age'FROM HumanResources.Employee"

68 "You have to create a report that displays the EmployeeID of all the employees, who live in the Bothell city, from the EmployeeAddress table. For this, you created the following SQL query:

SELECT EmployeeID FROM HumanResources.EmployeeAddress WHERE AddressID

= (SELECT AddressID FROM Person.Address WHERE City = 'Bothell')

However, the preceding query gives an error ""Subquery returned more than 1 value"". Provide the correct solution for this problem."

"1, SELECT EmployeeID FROM HumanResources.EmployeeAddress WHERE AddressIDIN (SELECT AddressID FROM Person.Address WHERE City = 'Bothell')"

2, SELECT EmployeeID FROM HumanResources.EmployeeAddress WHERE AddressID EXISTS (SELECT AddressID FROM Person.Address WHERE City = 'Bothell')

3, SELECT AddressID FROM Person.Address WHERE City = 'Bothell'

4, SELECT EmployeeID FROM HumanResources.EmployeeAddress e JOIN Person.Address a ON e.AddressID=a.AddressID

69 "You have to create a report that displays the Employee ID and the title of all the employees whose sick leave hours are more than the sick leave hours of employees designated as Stocker. For this, you used the following SQL query:

SELECT EmployeeID, Title, SickLeaveHours

FROM HumanResources.Employee

WHERE SickLeaveHours >ANY (SELECT SickLeaveHours

FROM HumanResources.Employee WHERE Title ='Stocker')

Analyze and predict whether the preceding query would display the required report. If not, provide the correct solution."

1, Yes, the query will display the required report.

"2, No, the correct query is:SELECT EmployeeID, Title, SickLeaveHours

FROM HumanResources.Employee WHERE SickLeaveHours >ALL (SELECT SickLeaveHours

FROM HumanResources.Employee WHERE Title ='Stocker')"

"3, No, the correct query is:SELECT EmployeeID, Title, SickLeaveHoursFROM HumanResources.EmployeeWHERE SickLeaveHours <>ALL (SELECT SickLeaveHours

FROM HumanResources.Employee WHERE Title ='Stocker')"

"4, No, the correct query is SELECT EmployeeID, Title, SickLeaveHours

FROM HumanResources.Employee WHERE SickLeaveHours =ANY (SELECT SickLeaveHours

FROM HumanResources.Employee WHERE Title ='Stocker')"

70 "You have to create a report that displays the EmployeeID of those employees whose sick leave hours are greater than the average sick leave hours of employees with title as 'Network Administrator'. For this, you used the following SQL query:

SELECT EmployeeId FROM HumanResources.Employee WHERE SickLeaveHours

= (SELECT AVG(SickLeaveHours) FROM HumanResources.Employee

WHERE Title = 'Network Administrator')

Analyze and predict whether the preceding query would display the required report. If not, provide the correct solution."

1, Yes, the query will display the required report.

"2, No, the correct query is:SELECT EmployeeId FROM HumanResources.Employee WHERE SickLeaveHours > (SELECT AVG(SickLeaveHours) FROM HumanResources.Employee WHERE Title = 'Network Administrator')"

"3, No, the correct query is:SELECT EmployeeId FROM HumanResources.Employee WHERE Avg(SickLeaveHours) > (SELECT SickLeaveHours FROM HumanResources.Employee WHERE Title = 'Network Administrator')"

"4, No, the correct query is:SELECT EmployeeId FROM HumanResources.Employee WHERE SickLeaveHours = (SELECT SickLeaveHours FROM HumanResources.Employee WHERE Title = 'Network Administrator')"

71 "You have to create a report that displays all the titles of the employees along with their average sick leave hours when the sick leave hours are more than 20 and the group average value is greater than 35. For this, you used the following SQL query:

SELECT Title, 'Average Sick Leave Hours' = avg(SickLeaveHours) FROM

HumanResources.Employee WHERE SickLeaveHours > 20 GROUP BY Title

Analyze and predict whether the preceding query would display the required report. If not, provide the correct solution."

1, Yes, the query will display the required report.

"2, No, the correct query is:SELECT Title, 'Average Sick Leave Hours' = avg(SickLeaveHours) FROMHumanResources.Employee WHERE SickLeaveHours > 20 AND avg(SickLeaveHours) >35

"3, No, the correct query is:SELECT Title, 'Average Sick Leave Hours' = avg(SickLeaveHours) FROM HumanResources.Employee WHERE avg(SickLeaveHours) >55"

"4, No, the correct query is:SELECT Title, 'Average Sick Leave Hours' = avg(SickLeaveHours) FROM HumanResources.Employee WHERE SickLeaveHours > 20 GROUP BY Title HAVINGavg(SickLeaveHours) >35"

72 Which of the following databases acts as a template or a prototype for the new databases in SQL Server 2005?

1, master 2, model 3, msdb 4, Resource

73 What is the extension of the primary data file in SQL Server 2005?

1, .mdf 2, .ndf 3, .ldf 4, .sql

74 Which of the following stored procedures is used to rename a database in SQL Server 2005?

1, sp_name 2, sp_helpdb 3, sp_rename 4, sp_renamedb

75 The ____________ statement is used to delete a database when it is no longer required.

1, DROP DATABASE 2, DELETE DATABASE

3, ALTER DATABASE 4, DROP DB

76 Which of the following functions provided by the XML data type of the SQL Server is used toadd nodes to XML in an XML column or variable?

1, query 2, value 3, modify 4, exist

77 You are creating an Employee table in SQL Server 2005. The table contains the EmpPhone column to store the phone numbers of the employees. You need to ensure that the phone number entered by the employee should not be less 10 digits. Which of the following constraints would you apply on the EmpPhone column to accomplish this task?

1, Foreign Key 2, Check 3, Unique 4, Default

78 The Employee table in SQL Server 2005 contains the details about employees working in an organization. According to certain new requirements, you have to add another column for storing PAN card details of each employee in the Employee table. Which of the following statements would you use to implement this change in the Employee table?

1, ALTER TABLE 2, DROP TABLE 3, CREATE TABLE 4, TRUNCATE TABLE

79 The details about various products are stored in the Products table. The price of one of the products has changed. You need to reflect this change in the Products table. Which of the following statements allows you to perform this task?

1, UPDATE 2, ALTER 3, INSERT 4, SELECT

80 An XML document needs to be shredded in order to store it in a database table. Which of the following tasks is NOT a part of shredding the XML document?

1, Parse the XML document 2, Retrieve a rowset from the tree

3, Delete the data from the rowset 4, Clear the memory

81 You have to retrieve the relational data from the Products table into the XML format for reporting purposes. For this, you want to write a Select query that returns the result in the form of nested XML elements. Which of the following modes of the FOR XML clause would you use to retrieve the XML data in the required format?

1, RAW 2, AUTO 3, PATH 4, EXPLICIT

82 You have to create the Orders table in the database. The table contains three columns: OrderID, OrderDate, and OrderPrice. The OrderID column cannot be left empty while inserting data in the table. Which of the following statements would you use to create the Orders table?

"1, CREATE TABLE Orders

(

OrderID int NOT NULL,

OrderDate datetime,

OrderPrice int

"2, CREATE TABLE Orders

(

OrderID int NOT NULL,

OrderDate datetime,

OrderPrice int,

"3, CREATE TABLE Orders

(

OrderID int,

OrderDate datetime,

OrderPrice int

"4, CREATE TABLE Orders

(

OrderID int NULL,

OrderDate datetime,

OrderPrice int

83 You have to create the EmpPayDetails table in the database. This table contains the personal details of the employees and their payment rates. Whenever there is a change in the payment rate of an employee, a separate record is inserted in the RateChangeDate column. The EmpPayDetails table needs to be partitioned based on the changes made in the payment rates of employees. For this, you have created a partition function and associated it with various filegroups by using the partition scheme named RateChngDate. Which of the following statements would you use to create the table that will store the partitioned records?

"1, Create Partition Table EmpPayDetails

(

EmployeeID int,

RateChangeDate datetime,

Rate money,

PayFrequency tinyint,

ModifiedDate datetime

)ON RateChngDate(RateChangeDate)"

"2, Create Table EmpPayDetails

(

EmployeeID int,

RateChangeDate datetime,

Rate money,

PayFrequency tinyint,

ModifiedDate datetime

)ON RateChngDate(RateChangeDate)"

"3, Create Table EmpPayDetails

(

EmployeeID int,

RateChangeDate datetime,

Rate money,

PayFrequency tinyint,

ModifiedDate datetime

)ON RateChngDate"

"4, Create Table EmpPayDetails

(

EmployeeID int,

RateChangeDate datetime,

Rate money,

PayFrequency tinyint,

ModifiedDate datetime

)ON RateChangeDate(RateChngDate)"

84 "You have to store the data in the following columns of the Address table in the database:

AddressID

AddressLine1

AddressLine2

StateProvinceID

PostalCode

Which of the following statements would you use to perform this task?"

"1, INSERT Address (AddressID, AddressLine1, AddressLine2, StateProvinceID, PostalCode)

VALUES (104, '24, Herbon Apts',' Arthor Lane', 56, '607009')"

2, INSERT INTO Address (104, '24, Herbon Apts',' Arthor Lane', 56, '607009')

3, INSERT VALUES (104, '24, Herbon Apts',' Arthor Lane', 56, '607009')

"4, INSERT Address VALUE (104, '24, Herbon Apts',' Arthor Lane', 56, '607009')"

85 You have received the order details from a vendor in an XML document. This XML data needs to be stored in a database table. For this, you need to shred the XML document by using the OPENXML function. Identify the correct sequence of tasks that you need to perform to shred the XML document.

"1, 1. Parse the XML document.

2. Store the data from the rowset.

3. Retrieve a rowset from the tree.

4. Clear the memory."

"2, 1. Store the data from the rowset.

2. Parse the XML document.

3. Clear the memory.

4. Retrieve a rowset from the tree."

"3, 1. Parse the XML document.

2. Retrieve a rowset from the tree.

3. Store the data from the rowset.

4. Clear the memory."

"4, 1. Clear the memory.

2. Store the data from the rowset.

3. Parse the XML document.

4. Retrieve a rowset from the tree."

86 "You have written the following statements to retrieve details of employees with Employee ID as 1 or 2:

SELECT EmployeeID, ContactID, LoginID, Title

FROM HumanResources.Employee

WHERE EmployeeID=1 OR EmployeeID=2

FOR XML RAW

Predict the output of the preceding query."

"1, <row EmployeeID=""1"" ContactID=""1209"" LoginID=""adventure-works\guy1""

Title=""Production Technician - WC60"" />

<row EmployeeID=""2"" ContactID=""1030"" LoginID=""adventure-works\kevin0""

Title=""Marketing Assistant"" />"

"2, <row>

<LoginID>adventure-works\guy1</LoginID>

<Title>Production Technician - WC60</Title>

</row>

<row>

<LoginID>adventure-works\kevin0</LoginID>

<Title>Marketing Assistant</Title>

</row>"

"3, <Employee EmployeeID=""1"" ContactID=""1209"" LoginID=""adventure-works\

guy1"" Title=""Production Technician - WC60"" />

<Employee EmployeeID=""2"" ContactID=""1030"" LoginID=""adventure-works\

kevin0"" Title=""Marketing Assistant"" />"

"4, <Employee>

<LoginID>adventure-works\guy1</LoginID>

<Title>Production Technician - WC60</Title>

</Employee>

<LoginID>adventure-works\kevin0</LoginID>

<Title>Marketing Assistant</Title>

</Employee>"

87 How many clustered indexes can be created on a table?

1, 1 2, 2 3, 4 4, 249

88 Which of the following constructs is used in a batch to allow a set of T-SQL statements to execute repeatedly as long as the given condition holds true?

1, IF...ELSE 2, CASE 3, WHILE 4, FOREACH

89 Which of the following data types can be returned by a scalar function in SQL Server 2005?

1, text 2, ntext 3, cursor 4, float

90 Which of the following types of triggers is fired after the execution of the DML operation for which it has been defined?

1, After Triggers 2, Instead of Triggers

3, Nested Triggers 4, Recursive Triggers

91 Which of the following problems occurs in the database when two or more transactions try to modify the same row?

1, Lost updates 2, Uncommitted dependency

3, Inconsistent analysis 4, Phantom reads

92 You have to create a clustered index on the Employee table. While creating the index, you need to reserve a percentage of free space on each data page of the index to accommodate future expansion. Which of the following options should you use to accomplish this while creating the clustered index?

1, PAD_INDEX 2, FILLFACTOR 3, ONLINE 4, DROP_EXISTING

93 Which of the following statements is NOT true about XML indexes?

1, XML indexes can be created only on XML columns.

2, XML indexes only support indexing a single XML column.

3,XML indexes can only be added to tables, views, table-valued variables with XML columns or XML variables.

4, XML indexes created on a table do not allow you to modify the primary key.

94 Which of the following statements is NOT true about batches?

1, When a batch is submitted to the SQL Server, it is compiled to create an execution plan.

2, If any compilation error occurs, none of the statements in the batch is executed.

3, You can use the EXECUTE statement in a batch when it is not the first statement of the batch.

4, You can define and use the CHECK constraint in the same batch.

95 You have to update two tables in a database. For this, you have written two update statements. You want that both update statements must execute as a single logical unit of work to maintain data integrity. If any type of error occurs in any of the update statements, none of the statements should be executed. Which of the following options would you use to implement this?

1, Transaction 2, Batch 3, Trigger 4, Stored Procedure

96 You have to set the timeout period of five seconds for a transaction to wait on a blocked resource. Which of the following statements would you use to accomplish this?

1, SET LOCK_TIMEOUT 5000 2, SET LOCK_TIMEOUT 500

3, SET LOCK_TIMEOUT 50 4, SET LOCK_TIMEOUT 5

97 Identify the correct sequence of tasks that you need to perform to configure full-text search on a database.

"1, 1. Enable the full-text search in the database.

2. Create a full-text catalog.

3. Create a unique index.

4. Create a full-text index.

5. Populate the full-text index."

"2, 1. Enable the full-text search in the database.

2. Create a unique index.

3. Create a full-text catalog.

4. Create a full-text index.

5. Populate the full-text index."

"3, 1. Create a full-text catalog.

2. Enable the full-text search in the database.

3. Create a unique index.

4. Create a full-text index.

5. Populate the full-text index."

"4, 1. Create a full-text index.

2. Create a full-text catalog.

3. Enable the full-text search in the database.

4. Create a unique index.

5. Populate the full-text index."

98 "You have created a view in the database by using the following statements:

CREATE VIEW HumanResources.vwEmployeeDepData

SELECT e.EmployeeID, MaritalStatus, DepartmentID

FROM HumanResources.Employee e JOIN

HumanResources.EmployeeDepartmentHistory d

ON e.EmployeeID = d.EmployeeID

Now, you want to create a clustered index on the vwEmployeeDepData view. For this, you first need to bind the vwEmployeeDepData view to the schema of the underlying tables. Which of the following statements would you use to perform this task?"

"1, CREATE VIEW HumanResources.vwEmployeeDepData WITH SCHEMABINDING

SELECT e.EmployeeID, MaritalStatus, DepartmentIDFROM HumanResources.Employee e JOIN

HumanResources.EmployeeDepartmentHistory dON e.EmployeeID = d.EmployeeID"

"2, ALTER VIEW HumanResources.vwEmployeeDepData WITH SCHEMABINDING

SELECT e.EmployeeID, MaritalStatus, DepartmentIDFROM HumanResources.Employee e JOIN

HumanResources.EmployeeDepartmentHistory dON e.EmployeeID = d.EmployeeID"

"3, ALTER VIEW HumanResources.vwEmployeeDepData WITH SCHEMA

SELECT e.EmployeeID, MaritalStatus, DepartmentIDFROM HumanResources.Employee e JOIN

HumanResources.EmployeeDepartmentHistory dON e.EmployeeID = d.EmployeeID"

"4, SELECT VIEW HumanResources.vwEmployeeDepData WITH SCHEMABINDING

SELECT e.EmployeeID, MaritalStatus, DepartmentIDFROM HumanResources.Employee e JOIN

HumanResources.EmployeeDepartmentHistory dON e.EmployeeID = d.EmployeeID"

99 The CustomerID attribute of the Customer table is an IDENTITY column and its value cannot be specified while inserting a new record. If anyone specifies the value for the CustomerID in the INSERT statement, an error will be generated. To handle such errors, you decide to use the TRY and CATCH blocks. You want that when an error occurs while inserting a record in the Customer table, the error message along with the line number at which the error occurred should be displayed. Which of the following statements would you use to perform this task?

"1, BEGIN TRY INSERT INTO HumanResources.Customer

VALUES ('C001', 1979, 'robertl', 16, 'M')END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS ErrorLine END CATCH GO"

"2, BEGIN TRY INSERT INTO HumanResources.Customer

VALUES ('C001', 1979, 'robertl', 16, 'M')END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR_MESSAGE() AS ErrorMessage, ERROR_NUMBER() AS ErrorLine END CATCH GO"

"3, BEGIN TRY INSERT INTO HumanResources.Customer

VALUES ('C001', 1979, 'robertl', 16, 'M')END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR() AS ErrorMessage, ERROR_NUMBER() AS ErrorLine END CATCH GO"

"4, BEGIN TRY INSERT INTO HumanResources.Customer

VALUES ('C001', 1979, 'robertl', 16, 'M') END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR() AS ErrorMessage, ERROR_LINE() AS ErrorLine END CATCH GO"

100 "You have created following stored procedure in SQL Server:

CREATE PROCEDURE prcGetEmployeeDetail @EmpId int, @DepName char(50) OUTPUT, @ShiftId int OUTPUT

BEGIN

IF EXISTS(SELECT * FROM HumanResources.Employee WHERE EmployeeID = @EmpId)

BEGIN

SELECT @DepName = d.Name, @ShiftId = h.ShiftID

FROM HumanResources.Department d JOIN

HumanResources.EmployeeDepartmentHistory h

ON d.DepartmentID = h.DepartmentID

WHERE EmployeeID = @EmpId AND h.Enddate IS NULL

RETURN 0

END

ELSE

RETURN 1

END

Now, you have to create another procedure that will call the prcGetEmployeeDetail procedure. Which of the following statements would you use to perform this task?"

"1, CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int

BEGIN

DECLARE @DepName char(50)

DECLARE @ShiftId int

DECLARE @ReturnValue int

EXEC @ReturnValue = prcGetEmployeeDetail @EmpId, @DepName OUTPUT, @ShiftId OUTPUT

IF (@ReturnValue = 0)

BEGIN

PRINT 'The details of an employee with ID: ' + convert(char(10), @EmpId)

PRINT 'Department Name: ' + @DepName

PRINT 'Shift ID: ' + convert( char(1), @ShiftId)

END

ELSE

PRINT 'No records found for the given employee'

END"

"2, CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int

BEGIN

DECLARE @DepName

DECLARE @ShiftId

DECLARE @ReturnValue

EXEC @ReturnValue = prcGetEmployeeDetail @EmpId, @DepName OUTPUT, @ShiftId OUTPUT

IF (@ReturnValue = 0)

BEGIN

PRINT 'The details of an employee with ID: ' + convert(char(10), @EmpId)

PRINT 'Department Name: ' + @DepName

PRINT 'Shift ID: ' + convert( char(1), @ShiftId)

END

ELSE

PRINT 'No records found for the given employee'

END"

"3, CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int

BEGIN

DECLARE @DepName char(50)

DECLARE @ShiftId int

DECLARE @ReturnValue int

EXEC @ReturnValue = prcGetEmployeeDetail @EmpId, @DepName OUTPUT, @ShiftId OUTPUT

IF (@ReturnValue = 0)

BEGIN

PRINT 'The details of an employee with ID: ' + convert(char(10), @EmpId)

PRINT 'Department Name: ' + @DepName

PRINT 'Shift ID: ' + convert( char(1), @ShiftId)

END

ELSE

PRINT 'No records found for the given employee'

END"

"4, CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int

BEGIN

DECLARE @DepName char(50)

DECLARE @ShiftId int

EXEC @ReturnValue = prcGetEmployeeDetail @EmpId, @DepName OUTPUT, @ShiftId OUTPUT

Microsoft SQL Server 2005 Question NIIT (51-100)

Microsoft SQL Server 2005 Question NIIT (51-100)

Post a Comment

Contact Form