Explore topic-wise InterviewSolutions in .

This section includes InterviewSolutions, each offering curated multiple-choice questions to sharpen your knowledge and support exam preparation. Choose a topic below to get started.

51.

Which of the following are TCL commands?(a) UPDATE and TRUNCATE(b) SELECT and INSERT(c) GRANT and REVOKE(d) ROLLBACK and SAVEPOINTThis question was posed to me in exam.Query is from Basic SQL topic in division Laying the Foundation of SQL Server

Answer» RIGHT choice is (d) ROLLBACK and SAVEPOINT

Best explanation: Transaction control commands manage CHANGES made by DML commands. These SQL commands are used for managing changes AFFECTING the data.
Discussion
52.

Which of the following is not a DDL command?(a) UPDATE(b) TRUNCATE(c) ALTER(d) None of the MentionedThis question was addressed to me in unit test.My enquiry is from Basic SQL topic in division Laying the Foundation of SQL Server

Answer»

Correct choice is (a) UPDATE

Easiest explanation: Data definition language (DDL) commands ENABLE you to PERFORM the following TASKS:CREATE, alter, and drop schema OBJECTS.
Discussion
53.

___________removes all rows from a table without logging the individual row deletions.(a) DELETE(b) REMOVE(c) DROP(d) TRUNCATEThe question was asked during an interview for a job.I need to ask this question from Basic SQL in section Laying the Foundation of SQL Server

Answer»

The CORRECT option is (d) TRUNCATE

Easiest explanation: TRUNCATE statement is a Data DEFINITION Language (DDL) operation that marks the EXTENTS of a table for DEALLOCATION.
Discussion
54.

Which of the following SQL clauses is used to DELETE tuples from a database table?(a) DELETE(b) REMOVE(c) DROP(d) CLEARThis question was posed to me during an interview.This intriguing question originated from Basic SQL in chapter Laying the Foundation of SQL Server

Answer»

Right option is (a) DELETE

Easy EXPLANATION: The SQL DELETE Query is USED to delete the EXISTING records from a table.
Discussion
55.

Which SQL keyword is used to retrieve a maximum value?(a) MOST(b) TOP(c) MAX(d) UPPERThis question was posed to me in homework.This intriguing question comes from Basic SQL topic in division Laying the Foundation of SQL Server

Answer»

Right choice is (c) MAX

The EXPLANATION is: The MAX() function returns the LARGEST value of the selected COLUMN.
Discussion
56.

Which SQL function is used to count the number of rows in a SQL query?(a) COUNT()(b) NUMBER()(c) SUM()(d) COUNT(*)This question was posed to me during an interview.My question comes from Basic SQL in division Laying the Foundation of SQL Server

Answer» RIGHT CHOICE is (d) COUNT(*)

For explanation: COUNT(*) takes NULL value row in to CONSIDERATION.
Discussion
57.

Built in Functions in SQL Server is categorized into how many categories?(a) 4(b) 5(c) 6(d) 7I had been asked this question in final exam.My doubt is from Builtin Functions in chapter Laying the Foundation of SQL Server

Answer»

Correct ANSWER is (a) 4

Easy explanation: SQL Server PROVIDES many built-in functions and also lets you create user-defined functions:- Rowset, Aggregate, RANKING and Scalar Function.
Discussion
58.

______________ returns the rank of rows within the partition of a result set, without any gaps in the ranking.(a) RANK(b) NTILE(c) DENSE_RANK(d) ROW_NUMBERI had been asked this question in an interview for job.The question is from Builtin Functions in division Laying the Foundation of SQL Server

Answer» RIGHT ANSWER is (c) DENSE_RANK

The EXPLANATION is: DENSE_RANK function makes the RANK of a row is one plus the number of distinct ranks that COME before the row in question.
Discussion
59.

Which of the following is not a conversion function?(a) CAST and CONVERT(b) PARSE(c) TRY_CAST(d) TRY_CASEI got this question at a job interview.The question is from Builtin Functions in portion Laying the Foundation of SQL Server

Answer»

The correct option is (d) TRY_CASE

For EXPLANATION: CONVERSION functions SUPPORT DATA type casting and converting.
Discussion
60.

Text and Image Functions are ___________(a) nondeterministic(b) deterministic(c) table valued(d) all of the mentionedThis question was posed to me in semester exam.The question is from Builtin Functions in division Laying the Foundation of SQL Server

Answer»

Right option is (a) NONDETERMINISTIC

The explanation: TEXT and image functions are nondeterministic. This means they do not always return the same results every TIME they are called, even with the same set of input VALUES.
Discussion
61.

DecryptByKeyAutoCert is ________________type function.(a) Symmetric Encryption and decryption(b) Encryption Hashing(c) Asymmetric Encryption and decryption(d) Symmetric decryption with Automatic key handlingThe question was asked in an interview for job.Question is taken from Builtin Functions topic in division Laying the Foundation of SQL Server

Answer»

The correct option is (d) SYMMETRIC DECRYPTION with Automatic key handling

Best explanation: DECRYPTBYKEYAUTOCERT FUNCTION decrypts by using a symmetric key that is automatically decrypted with a certificate.
Discussion
62.

__________ are used for supporting encryption, decryption, digital signing and their validation.(a) Cryptographic functions(b) Cursor functions(c) Configuration functions(d) None of the mentionedI had been asked this question in quiz.This is a very interesting question from Builtin Functions topic in division Laying the Foundation of SQL Server

Answer» RIGHT choice is (a) Cryptographic FUNCTIONS

Explanation: Encryption and decryption of DATA is DONE by cryptographic functions.
Discussion
63.

NESTLEVEL functionfalls under which of the following category?(a) Configuration functions(b) Cursor functions(c) Mathematical functions(d) Date and Time Data FunctionsI got this question by my school principal while I was bunking the class.Enquiry is from Builtin Functions topic in chapter Laying the Foundation of SQL Server

Answer» CORRECT CHOICE is (a) CONFIGURATION functions

To EXPLAIN: Configuration functions return information about the current configuration.
Discussion
64.

Which of the following is not a mathematical function?(a) ATN2(b) POWER(c) PI(d) CEILThe question was posed to me in an online interview.The query is from Builtin Functions topic in chapter Laying the Foundation of SQL Server

Answer» RIGHT choice is (d) CEIL

The explanation is: SQL Server has CEILING function to get the smallest integer GREATER than the specified expression.
Discussion
65.

Which of the following function checks whether the expression is a valid date or not?(a) ISDATE(b) ISDAY(c) ISVALID(d) ISYEARI had been asked this question in examination.I'd like to ask this question from Builtin Functions in section Laying the Foundation of SQL Server

Answer»

The CORRECT answer is (a) ISDATE

To EXPLAIN I would say: ISDATE() determines whether a datetime or smalldatetime input EXPRESSION is a VALID date or time value.
Discussion
66.

Which of the following data type is not present in SQL Server?(a) bit(b) boolean(c) hierarchyid(d) geographyI got this question by my school principal while I was bunking the class.This intriguing question originated from Data Types in portion Laying the Foundation of SQL Server

Answer»

Correct option is (B) boolean

For explanation: SQL Server doesn’t have a Boolean data type, at LEAST not by that NAME. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL.
Discussion
67.

______________ functionreturns current date and time.(a) SET DATEFIRST(b) SYSDATETIME(c) Cert_ID(d) GETDATEThe question was asked by my college professor while I was bunking the class.The query is from Builtin Functions in section Laying the Foundation of SQL Server

Answer»

The correct option is (d) GETDATE

The explanation is: GETDATE function is used to obtain the current system DATE and TIME. ALTHOUGH GETDATE doesn’t have any input parameters, you still NEED to include the parentheses in your code because that’s how SQL SERVER typically identifies functions.
Discussion
68.

Which of the data type has a storage size of 8 bytes?(a) timestamp(b) uniqueidentifier(c) real(d) smallmoneyThis question was posed to me in a national level competition.Query is from Data Types in portion Laying the Foundation of SQL Server

Answer»

The CORRECT choice is (a) timestamp

Explanation: uniqueidentifier, real and SMALLMONEY data TYPES have STORAGE size of 16,4 and 4 bytes respectively.
Discussion
69.

______________ is monetary data type in SQL Server.(a) Smallmoney(b) sql_variant(c) Cursor(d) None of the MentionedThe question was asked in my homework.The query is from Data Types topic in division Laying the Foundation of SQL Server

Answer» CORRECT choice is (a) Smallmoney

Explanation: Monetary data types are data types that REPRESENT monetary or currency values such as smallmoney and money.
Discussion
70.

_________ isa spatial data type.(a) geometry(b) sql_variant(c) cursor(d) all of the mentionedThis question was addressed to me during a job interview.This is a very interesting question from Data Types in division Laying the Foundation of SQL Server

Answer»

Correct answer is (b) sql_variant

To EXPLAIN I would say: SQL Server supports the geometry and geography data types for storing spatial data. These types support methods and properties that allow for the creation, COMPARISON, analysis, and retrieval of spatial data.
Discussion
71.

You want to track date and time of the last write access per row?(a) Add TIMESTAMP column to the table(b) Add a DATETIME column to the table and assign getdate() as the default value(c) Add a DATETIME column to the table and write a trigger that sets its value(d) Add a UNIQUEIDENTIFIER column to the table and use it with SQL Server’s built-in functionsI got this question in quiz.This key question is from Data Types in chapter Laying the Foundation of SQL Server

Answer»

The correct answer is (a) ADD TIMESTAMP column to the table

The explanation: The correct answer is Add a DATETIME column to the table and WRITE a trigger that sets its value.
Discussion
72.

A column of type __________ may contain rows of different data types.(a) ntext(b) date(c) smallmoney(d) sql_variantI got this question in semester exam.I'm obligated to ask this question of Data Types in section Laying the Foundation of SQL Server

Answer» CORRECT CHOICE is (d) sql_variant

To elaborate: sql_variant is data type that STORES values of various SQL Server-supported data types.
Discussion
73.

ntext data type falls under which category?(a) Exact numerics(b) Character strings(c) Unicode character strings(d) None of the mentionedI had been asked this question during an interview.The origin of the question is Data Types in division Laying the Foundation of SQL Server

Answer»

The CORRECT OPTION is (c) Unicode character strings

Best explanation: ntext is FIXED and variable-length data type for storing large non-Unicode and Unicode character.
Discussion
74.

Data types in SQL Server are organized into how many categories?(a) 6(b) 8(c) 9(d) 10This question was addressed to me during an internship interview.I'm obligated to ask this question of Data Types topic in division Laying the Foundation of SQL Server

Answer»

The correct OPTION is (a) 6

Explanation: SQL SERVER offers six categories of data types for your USE:-EXACT numeric, Unicode character strings, approximate numeric, Binary strings, Date and TIME and Character strings.
Discussion
75.

Exact Numeric data type is ___________(a) bigint(b) int(c) smallmoney(d) all of the mentionedThe question was asked during an online interview.This interesting question is from Data Types in division Laying the Foundation of SQL Server

Answer»

The correct OPTION is (d) all of the mentioned

Easy explanation: Exact numeric DATA types STORE numeric values where you wish to SPECIFY the precision of the variable. They may include integer or DECIMAL numbers.
Discussion
76.

Which of the following is a large object data type?(a) varchar(max)(b) varbinary(max)(c) nvarchar(max)(d) imageThe question was posed to me by my college professor while I was bunking the class.Origin of the question is Data Types topic in chapter Laying the Foundation of SQL Server

Answer»

The correct choice is (d) image

The explanation: In SQL Server, based on their STORAGE characteristics, some DATA TYPES are designated as LARGE value data types and large object data types.
Discussion
77.

The _________ database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.(a) model(b) msdb(c) master(d) tempI had been asked this question in final exam.My question is from Management Studio topic in chapter Laying the Foundation of SQL Server

Answer»

The correct OPTION is (b) msdb

To explain: MSDB Database stores information related to database backups, DTS packages, REPLICATION, SQL Agent information, SQL SERVER jobs.
Discussion
78.

______________ is schematic drawing used for representing relationships in database.(a) Trigger(b) Database Diagrams(c) Table designer(d) Query EditorThe question was posed to me by my college director while I was bunking the class.Question is from Management Studio topic in chapter Laying the Foundation of SQL Server

Answer»

The CORRECT option is (B) Database Diagrams

Explanation: Database DIAGRAM tool allows you to visualize a database structure for further ANALYSIS and generate a table relationship diagram from an existing SCHEMA.
Discussion
79.

Which of the following is not a database object?(a) Database Diagrams(b) Stored Procedures(c) PowerShell(d) IndexThe question was asked at a job interview.Asked question is from Management Studio topic in section Laying the Foundation of SQL Server

Answer» RIGHT choice is (c) PowerShell

For EXPLANATION I would SAY: PowerShell is a tool utility not a database OBJECT.
Discussion
80.

Which of the following is used to build sql statements without writing code?(a) Registered Servers(b) Query Designer(c) Template Explorer(d) Context ExplorerThis question was addressed to me in homework.The doubt is from Management Studio topic in division Laying the Foundation of SQL Server

Answer»

Right CHOICE is (b) Query Designer

The best I can explain: WITHIN the Management Studio, Query Designer can build a SQL STATEMENT without having to write your own SQL code.
Discussion
81.

Which menu is used to restore windows to their original locations?(a) Edit(b) File(c) Tools(d) WindowsI had been asked this question in a job interview.I'd like to ask this question from Management Studio in division Laying the Foundation of SQL Server

Answer» CORRECT option is (d) Windows

Easiest explanation: To restore windows to their original LOCATIONS, on the WINDOW menu, click Reset Window LAYOUT.
Discussion
82.

Which of the following shortcut is used to clear all bookmarks?(a) Ctrl+K+L(b) Ctrl+K+O(c) Ctrl+K+B(d) Ctrl+K+PThe question was posed to me in an international level competition.This is a very interesting question from Management Studio in section Laying the Foundation of SQL Server

Answer» CORRECT CHOICE is (a) Ctrl+K+L

For explanation I WOULD say: To DELETE all within SSMS, press Ctrl+K+L.
Discussion
83.

__________menu allows user to set up some keyboard shortcuts.(a) Edit(b) File(c) Tools(d) None of the MentionedI had been asked this question in quiz.My enquiry is from Management Studio topic in section Laying the Foundation of SQL Server

Answer»

The CORRECT choice is (C) TOOLS

The explanation is: In SSMS click Tools, and then Options.Select Keyboard from the Environment tree and set up the things you are like to TYPE very often.
Discussion
84.

Which of the following feature is not provided by SSS 2008?(a) Automatic formatting(b) IntelliSense(c) Code Outlining(d) SQLCMDThe question was posed to me in unit test.My enquiry is from Management Studio topic in division Laying the Foundation of SQL Server

Answer» RIGHT ANSWER is (a) Automatic formatting

Explanation: Management Studio Query Editor does not provide automatic formatting of T-SQL. There are some free WEBSITES that enable you to submit a SQL STATEMENT and will then FORMAT the code, but I’ve been using SQL Prompt from Red Gate and I’ve come to depend on it for consistent formatting.
Discussion
85.

Ctrl+K+Kshortcut refers to ___________(a) Refresh IntelliSense(b) Add or remove a bookmark(c) Enable all bookmarks(d) Move to the next bookmarkThe question was asked in unit test.The doubt is from Management Studio in portion Laying the Foundation of SQL Server

Answer»

Correct option is (b) ADD or remove a bookmark

For explanation I WOULD say: To TOGGLE BOOKMARKS on/off within SSMS, press Ctrl+K+K.
Discussion
86.

Which of the following pane option is not provided by Query Designer?(a) Diagram pane(b) Grid pane(c) Results pane(d) Query paneThis question was posed to me during an internship interview.My question is based upon Management Studio in portion Laying the Foundation of SQL Server

Answer» CORRECT OPTION is (d) Query PANE

Easiest explanation: Query DESIGNER does not PROVIDE Query pane for querying database schema.
Discussion
87.

SMO in SQL Server stands for ___________(a) Server Management Object(b) Service Management Object(c) SQL Management Object(d) SQL Server Management ObjectThe question was posed to me in my homework.This intriguing question originated from Management Studio in chapter Laying the Foundation of SQL Server

Answer»

The correct answer is (d) SQL SERVER MANAGEMENT Object

For explanation I WOULD SAY: SQL Server Management Objects (SMO) is a collection of objects that are DESIGNED for programming all aspects of managing Microsoft SQL Server.
Discussion
88.

Which of the following tool is used for designing schema of the database in SSMS?(a) Query Designer(b) Database Diagrams(c) Table designer(d) Query EditorThe question was asked in an internship interview.The question is from Management Studio topic in portion Laying the Foundation of SQL Server

Answer»

Right CHOICE is (C) Table designer

The EXPLANATION: Table Designer is used to CREATE and MODIFY tables (rows and columns).
Discussion
89.

Which of the following is a database object?(a) Tables(b) Triggers(c) Functions(d) All of the mentionedThe question was posed to me by my school teacher while I was bunking the class.I would like to ask this question from Management Studio topic in chapter Laying the Foundation of SQL Server

Answer»

Right choice is (d) All of the mentioned

The BEST explanation: SQL SERVER has MANY database objects such as service BROKER, VIEWS, tables and many more.
Discussion
90.

_________ is tool in SSMS used to manage multiple servers.(a) Registered Servers(b) Query Editor(c) Template Explorer(d) Context ExplorerI have been asked this question in an internship interview.The doubt is from Management Studio in division Laying the Foundation of SQL Server

Answer»

Correct choice is (a) Registered Servers

The best explanation: Using Registered Servers, connection INFORMATION can be maintained for connections to the Database Engine, ANALYSIS SERVICES, REPORTING Services, SQL Server Mobile Edition Databases, and INTEGRATION Services.
Discussion
91.

Which command is used to reset default configuration in SSMS?(a) Clear(b) Hide(c) Reset all(d) ResetI had been asked this question in homework.I need to ask this question from Management Studio in chapter Laying the Foundation of SQL Server

Answer» CORRECT choice is (d) RESET

The best EXPLANATION: To reset Management Studio to its default configuration (Object Explorer ➪ Tabbed Documents ➪Property WINDOW) use the Window ➪ Reset Window Layout MENU command.
Discussion
92.

_____________ is not available on standard toolbar.(a) Error List(b) Object Explorer(c) Query Editor(d) Properties windowsI had been asked this question during an interview for a job.I'd like to ask this question from Management Studio in portion Laying the Foundation of SQL Server

Answer» RIGHT choice is (a) Error List

To ELABORATE: The most commonly used TOOLS — Query Editor, Object Explorer, TEMPLATE Explorer, and PROPERTIES windows are available on the standard toolbar.
Discussion
93.

Which of the following shortcut is used for displaying error list?(a) Ctrl + /(b) Ctrl + |(c) Ctrl + \(d) Ctrl + !This question was addressed to me during an online exam.This is a very interesting question from Management Studio topic in portion Laying the Foundation of SQL Server

Answer»

The correct CHOICE is (c) CTRL + \

The EXPLANATION is: Error List (Ctrl+\, Ctrl+E) lists MULTIPLE errors.
Discussion
94.

Template Explorer is used to manage ____________(a) T-SQL scripts(b) Registered Servers(c) Properties(d) Query designThis question was addressed to me in an online interview.My question is based upon Management Studio topic in division Laying the Foundation of SQL Server

Answer»

Right answer is (a) T-SQL scripts

Explanation: Template EXPLORER is USED to CREATE and MANAGE T-SQL code templates.
Discussion
95.

Which panel presents a list of objects under the selected node?(a) Template Explorer(b) Object Explorer(c) Properties window(d) Query EditorI had been asked this question during an interview.I need to ask this question from Management Studio in division Laying the Foundation of SQL Server

Answer» RIGHT ANSWER is (B) Object Explorer

Explanation: Object Explorer is used for administering and DEVELOPING SQL Server database objects.
Discussion
96.

Management Studio is ________ used for development of SQL Server database.(a) Front end tool(b) Back end tool(c) Database engine(d) SQL ServerThis question was posed to me in exam.My question comes from Management Studio topic in portion Laying the Foundation of SQL Server

Answer»

Right OPTION is (a) Front end tool

Best explanation: MANAGEMENT STUDIO is a front-end CLIENT tool used to manage SQL Server and DEVELOP databases.
Discussion
97.

Which was the first version of SQL Server to introduce in-memory capability?(a) SQL Server 2012(b) SQL Server 2014(c) SQL Server 2005(d) SQL Server 2008I have been asked this question in an interview.Query is from Introduction topic in section Laying the Foundation of SQL Server

Answer» RIGHT answer is (B) SQL Server 2014

Easiest explanation: SQL Server 2014 provides a new in-MEMORY capability for tables that can fit entirely in memory (also known as Hekaton).
Discussion
98.

__________is the first true GUI-based database server.(a) SQL Server 7.0(b) SQL Server 6.5(c) SQL Server 2005(d) SQL Server 2008This question was addressed to me in homework.This intriguing question originated from Introduction topic in division Laying the Foundation of SQL Server

Answer»

Right answer is (a) SQL SERVER 7.0

To elaborate: SQL Server 7.0 PROVIDES a graphical USER interface to interactively build a query and view the result for data SOURCE types.
Discussion
99.

_________ is free database software running free SQL Server technology.(a) SQL Server Express(b) SQL Server Workgroup(c) SQL Server Enterprise(d) SQL Server WebThe question was asked in final exam.My doubt stems from Introduction in portion Laying the Foundation of SQL Server

Answer»

Right option is (a) SQL SERVER Express

Explanation: SQL Server Express Edition is a SCALED down, FREE edition of SQL Server, which includes the CORE DATABASE engine.
Discussion
100.

LocalDB was introduced in which of the following versions of SQL Server?(a) 2012(b) 2008(c) 2014(d) 2008 R2This question was addressed to me in class test.The doubt is from Introduction topic in division Laying the Foundation of SQL Server

Answer»

The correct choice is (a) 2012

Explanation: LOCALDB was introduced in SQL SERVER 2012 CTP3. It’s basically a new VERSION of SQL EXPRESS, with same functionality, dedicated to developers.
Discussion