MS SQL Server Proficiency Exam

Reviewed by Samy Boulos
Samy Boulos, MSc (Computer Science) |
Data Engineer
Review Board Member
Samy Boulos is an experienced Technology Consultant with a diverse 25-year career encompassing software development, data migration, integration, technical support, and cloud computing. He leverages his technical expertise and strategic mindset to solve complex IT challenges, delivering efficient and innovative solutions to clients.
, MSc (Computer Science)
Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Hlasher
H
Hlasher
Community Contributor
Quizzes Created: 1 | Total Attempts: 615
Questions: 15 | Attempts: 615

SettingsSettingsSettings
MS SQL Server Proficiency Exam - Quiz

Welcome to our MS SQL Server Quiz! This quiz is designed to challenge your expertise and enhance your understanding of Microsoft SQL Server, one of the most popular relational database management systems. Whether you are a budding database administrator, a seasoned developer, or an IT professional, this quiz offers a wide array of questions that cover the foundational to advanced features of SQL Server.

You will be tested on key topics such as database architecture, indexing, querying, performance tuning, security, and transaction management. The questions are crafted to probe your knowledge of SQL Server's tools and functionalities, including the use Read moreof SQL Server Management Studio (SSMS), T-SQL programming, backup strategies, and disaster recovery planning.

By taking this quiz, you'll get a chance to validate your skills, identify areas where you may need further study, and better prepare yourself for professional challenges. Dive in to demonstrate your proficiency with MS SQL Server and gain insights into best practices and effective database management techniques. Let's see how much you really know about SQL Server!


MS SQL Server 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'

    Correct Answer
    C. INSERT INTO People (ID, Name) VALUES (@@IDENTITY,'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

    Correct Answer
    B. False
    Explanation
    The statement "UPDATE dbo.People SET PeopleActive = 1 HAVING TermDate IS NULL" will not update the PeopleActive field. The HAVING clause is used with the GROUP BY clause to filter the results of a query. However, in this case, there is no GROUP BY clause present, so the HAVING clause has no effect. Therefore, the PeopleActive field will not be updated.

    Rate this question:

  • 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

    Correct Answer(s)
    C. TCP/IP
    D. Named Pipes
    F. Shared Memory
    Explanation
    SQL Server 2008 supports multiple network protocols for communication. The given options list various protocols, and the correct answers are TCP/IP, Named Pipes, and Shared Memory. These protocols allow SQL Server to communicate with client applications over different network environments.

    Rate this question:

  • 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

    Correct Answer
    D. GROUP BY LastModifiedDate, ItemName
    Explanation
    The correct clause to finish the SELECT statement is "GROUP BY LastModifiedDate, ItemName". This clause is used to group the results by the LastModifiedDate and ItemName columns. It allows for aggregating the COUNT(ID) function and displaying the count of IDs for each unique combination of LastModifiedDate and ItemName.

    Rate this question:

  • 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

    Correct Answer
    C. SQL Server Profiler
    Explanation
    SQL Server Profiler is the correct tool to diagnose the problem in this scenario. SQL Server Profiler allows you to capture and analyze events that occur on a SQL Server instance. By using the Profiler, you can trace the execution of the stored procedure and identify any issues or errors that may be causing the @@IDENTITY function to return NULL. This tool provides detailed information about the queries, events, and performance metrics, which can help in troubleshooting and resolving the problem.

    Rate this question:

  • 7. 

    Select the correct clause to return information from a table.

    • A.

      GET

    • B.

      RETURN

    • C.

      SELECT FROM

    • D.

      SELECT INTO

    Correct Answer
    C. SELECT FROM
    Explanation
    The correct clause to return information from a table is "SELECT FROM". This clause is used in SQL queries to specify the columns that should be retrieved from a table. It allows you to select specific data from one or more tables based on certain conditions, such as filtering by a specific column value or joining multiple tables together. By using the "SELECT FROM" clause, you can retrieve the desired information from a table in a database.

    Rate this question:

  • 8. 

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

    • A.

      True

    • B.

      False

    Correct Answer
    B. False
    Explanation
    SQL Server 2008 does not use Distributed Transaction Services (DTS) as its primary integration tool. Instead, it uses SQL Server Integration Services (SSIS) as its primary integration tool. SSIS is a platform for building high-performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) operations.

    Rate this question:

  • 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

    Correct Answer
    A. Connect to different data sources
    Explanation
    SQL Server Integration Services connection managers are used to connect to different data sources. This means that they provide the necessary functionality to establish connections with various types of databases or other data sources, allowing SSIS packages to retrieve or transfer data from these sources. Connection managers play a crucial role in the overall data integration process, enabling the seamless integration of data from multiple sources into a single destination or performing data transformations and manipulations during the process.

    Rate this question:

  • 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

    Correct Answer
    B. Stored Procedure
    Explanation
    A stored procedure is a pre-compiled executable object that contains one or more Transact-SQL statements. It is used to group a set of SQL statements into a single unit, which can then be executed repeatedly. Stored procedures provide a way to improve performance and maintainability by reducing the amount of code that needs to be written and executed. They can also be used to implement complex business logic and enforce data integrity rules.

    Rate this question:

  • 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

    Correct Answer
    C. TRUNCATE TABLE People
    Explanation
    The correct answer is TRUNCATE TABLE People. This statement removes all rows from the table and also resets the Identity counter. The other options are incorrect because they either have syntax errors or do not reset the Identity counter. The DELETE statement without specifying any condition will remove all rows from the table but will not reset the Identity counter.

    Rate this question:

  • 12. 

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

    • A.

      RAISERROR

    • B.

      ON ERROR

    • C.

      TRY/CATCH

    Correct Answer
    C. TRY/CATCH
    Explanation
    The preferred error handling method in SQL Server 2008 is TRY/CATCH. This method allows for structured error handling by enclosing the code that might generate an error within a TRY block, and then catching and handling any errors that occur within a CATCH block. This approach provides more control and flexibility in handling errors, allowing for specific actions to be taken based on the type of error encountered. It also allows for the possibility of nested error handling, where multiple levels of TRY/CATCH blocks can be used to handle errors at different levels of the code.

    Rate this question:

  • 13. 

    Choose the incorrect clause.

    • A.

      SELECT INTO

    • B.

      INSERT INTO

    • C.

      SELECT FROM

    • D.

      INSERT FROM

    Correct Answer
    D. INSERT FROM
    Explanation
    The correct answer is INSERT FROM. This is because the correct syntax for inserting data into a table is "INSERT INTO" followed by the table name, not "INSERT FROM". The "SELECT INTO" and "SELECT FROM" clauses are used for retrieving data from a table, while the "INSERT INTO" clause is used for inserting data into a table.

    Rate this question:

  • 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

    Correct Answer
    A. Relational Database
    Explanation
    Microsoft SQL Server is an example of a relational database because it is designed to store and manage data in a structured manner, using tables with rows and columns. It supports the relational model, which allows for the establishment of relationships between different tables through keys. SQL Server also supports the SQL language, which is commonly used to query and manipulate relational databases.

    Rate this question:

  • 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

    Correct Answer(s)
    A. BCP
    B. SELECT INTO
    C. SQL Server Integration Services
    D. SQL Server Import and Export Wizard
    Explanation
    The methods that can be used to copy data from one SQL Server to another of the same version are BCP (Bulk Copy Program), SELECT INTO, SQL Server Integration Services, and SQL Server Import and Export Wizard. BCP allows for high-speed data transfers between SQL Server instances. SELECT INTO creates a new table and copies data from an existing table into it. SQL Server Integration Services provides a comprehensive platform for data integration and transformation. SQL Server Import and Export Wizard is a graphical tool that simplifies the process of importing and exporting data.

    Rate this question:

Samy Boulos |MSc (Computer Science) |
Data Engineer
Samy Boulos is an experienced Technology Consultant with a diverse 25-year career encompassing software development, data migration, integration, technical support, and cloud computing. He leverages his technical expertise and strategic mindset to solve complex IT challenges, delivering efficient and innovative solutions to clients.

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • May 08, 2024
    Quiz Edited by
    ProProfs Editorial Team

    Expert Reviewed by
    Samy Boulos
  • Feb 06, 2013
    Quiz Created by
    Hlasher

Related Topics

Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.