MS SQL Server Proficiency Exam

15 Questions | Total Attempts: 542

SettingsSettingsSettings
Please wait...
MS SQL Server Proficiency Exam

A quiz which uses a base of questions which increase in difficulty as the test completes. The purpose of this test it to gain a general understanding of the quiz takers MS SQL Server knowledge and proficiency. Quiz Takers: Please go through each question. If you do not know the answer, leave it blank and continue forward to the next.


Questions and Answers
  • 1. 
    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.
  • 2. 
    Select the correctly formed SQL Statement from the below choices.
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

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

      True

    • B. 

      False

  • 4. 
    Select all the correct answers: SQL Server 2008 supports the following network protocols:
    • A. 

      Banyan VINES SSP

    • B. 

      AppleTalk

    • C. 

      TCP/IP

    • D. 

      Named Pipes

    • E. 

      NWLink IPX/SPX

    • F. 

      Shared Memory

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

      No further clause is necessary.

    • B. 

      WHERE LastModifiedDate IS NOT NULL

    • C. 

      HAVING LastModifiedDate IS NOT NULL

    • D. 

      GROUP BY LastModifiedDate, ItemName

    • E. 

      GROUPING ItemName, LastModifiedDate

  • 6. 
    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?
    • A. 

      Profiler utility

    • B. 

      Osql utility

    • C. 

      SQL Server Profiler

    • D. 

      Query Optimizer Tool

    • E. 

      SQLDiag utility

  • 7. 
    Select the correct clause to return information from a table.
    • A. 

      GET

    • B. 

      RETURN

    • C. 

      SELECT FROM

    • D. 

      SELECT INTO

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

      True

    • B. 

      False

  • 9. 
    Select all appropriate choices: SQL Server Integration Services connection managers are used to
    • A. 

      Connect to different data sources

    • B. 

      Prepare or copy data

    • C. 

      Sequence of package flow

    • D. 

      None of the above

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

      Function

    • B. 

      Stored Procedure

    • C. 

      Package

    • D. 

      Binary

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

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

    • B. 

      TRUNCATE People WITH @@IDENTITY(0)

    • C. 

      TRUNCATE TABLE People

    • D. 

      DELETE * FROM People

  • 12. 
    What is the preferred error handling method in SQL Server 2008?
    • A. 

      RAISERROR

    • B. 

      ON ERROR

    • C. 

      TRY/CATCH

  • 13. 
    Choose the incorrect clause.
    • A. 

      SELECT INTO

    • B. 

      INSERT INTO

    • C. 

      SELECT FROM

    • D. 

      INSERT FROM

  • 14. 
    Microsoft SQL Server is an example of a:
    • A. 

      Relational Database

    • B. 

      NoSQL Database

    • C. 

      Map/Reduce Database

    • D. 

      Vector Database

    • E. 

      None

    • F. 

      All

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

      BCP

    • B. 

      SELECT INTO

    • C. 

      SQL Server Integration Services

    • D. 

      SQL Server Import and Export Wizard

Back to Top Back to top