MS SQL Server Proficiency Exam

Select the correct clause to return information from a table.

• GET

• RETURN

• SELECT FROM

• SELECT INTO

A pre-compiled executable object which contains one or more Transact-SQL statements is called a:

• Function

• Stored Procedure

• Package

• Binary

Select the correctly formed SQL Statement from the below choices.

• INSERT ('1','Sam' INTO (ID,Name) USING People

• INSERT INTO People ('1','Sam') VALUES (ID, Name)

• INSERT INTO People (ID, Name) VALUES (@@IDENTITY,'Sam')

• INSERT INTO People SET ID=1, Name='Sam'

Choose the incorrect clause.

• SELECT INTO

• INSERT INTO

• SELECT FROM

• INSERT FROM

True or False: Will this statement update the PeopleActive field? UPDATE dbo.People SET PeopleActive = 1 HAVING TermDate IS NULL

• True

• False

Select the correct clause to finish the below SELECT statement: SELECT COUNT(ID), ItemName, LastModifiedDate FROM dbo.Inventory ....

• No further clause is necessary.

• WHERE LastModifiedDate IS NOT NULL

• HAVING LastModifiedDate IS NOT NULL

• GROUP BY LastModifiedDate, ItemName

• GROUPING ItemName, LastModifiedDate

You have written a stored procedure which uses the @@IDENTITY function to return the last inserted value.  This was working correctly until last night when you found out that @@IDENTITY began to return NULL each time. What tool do you use to diagnose your problem?

• Profiler utility

• Osql utility

• SQL Server Profiler

• Query Optimizer Tool

• SQLDiag utility

SQL Server 2008 uses Distributed Transaction Services (DTS) as it's primary integration tool.

• True

• False

Select the correct T-SQL statement that removes all rows from the table and resets the Identity counter.

• DELETE * FROM People WITH @@IDENTITY(0,0)

• TRUNCATE People WITH @@IDENTITY(0)

• TRUNCATE TABLE People

• DELETE * FROM People

What is the preferred error handling method in SQL Server 2008?

• RAISERROR

• ON ERROR

• TRY/CATCH

Select all appropriate choices: SQL Server Integration Services connection managers are used to

• Connect to different data sources

• Prepare or copy data

• Sequence of package flow

• None of the above

Select all the correct answers: SQL Server 2008 supports the following network protocols:

• Banyan VINES SSP

• AppleTalk

• TCP/IP

• Named Pipes

• NWLink IPX/SPX

• Shared Memory

Select the methods you can use to copy data from one SQL Server to another of the same version.

• BCP

• SELECT INTO

• SQL Server Integration Services

• SQL Server Import and Export Wizard

Using the following table structure: CREATE TABLE PurchaseOrders ( [PurchaseOrderID] [int] NOT NULL REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID) , [LineNumber] [smallint] NOT NULL , [ProductID] [int] NULL REFERENCES Production.Product(ProductID) , [UnitPrice] [money] NULL , [OrderQty] [smallint] NULL , [ReceivedQty] [float] NULL , [RejectedQty] [float] NULL, [DueDate] [datetime] NULL ;) Create the SELECT statement which returns all rows, in the following column order: Unit Price, ProductID, OrderQty and Due Date, in reverse alphabetical order by ProductID.