ZS Associate SQL Questions and Answers Quiz-1

Question 1

Time: 00:00:00
Which of the following statement is true ?

DELETE does not free the space containing the table and TRUNCATE free the space containing the table

DELETE does not free the space containing the table and TRUNCATE free the space containing the table

Both DELETE and TRUNCATE free the space containing the table

Both DELETE and TRUNCATE free the space containing the table

Both DELETE and TRUNCATE does not free the space containing the table

Both DELETE and TRUNCATE does not free the space containing the table

DELETE free the space containing the table and TRUNCATE does not free the space containing the table

DELETE free the space containing the table and TRUNCATE does not free the space containing the table

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 2

Time: 00:00:00
Purpose of GET_TRANSMISSION_STATUS is :

Returns the status for the first transmission for end side of a conversation

Returns the status for the first transmission for end side of a conversation

Returns the status for the first transmission for one side of a conversation

Returns the status for the first transmission for one side of a conversation

Returns the status for the last transmission for end side of a conversation

Returns the status for the last transmission for end side of a conversation

Returns the status for the last transmission for one side of a conversation

Returns the status for the last transmission for one side of a conversation

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 3

Time: 00:00:00
Point out the correct statement :

T-SQL is SQL language for SYBASE

T-SQL is SQL language for SYBASE

GET_TRANSMISSION_STATUS does provide information for messages that do not have a conversation endpoint in the current instance

GET_TRANSMISSION_STATUS does provide information for messages that do not have a conversation endpoint in the current instance

CREATE USER transfers a securable between schemas

CREATE USER transfers a securable between schemas

CREATE LOGIN modifies the login details

CREATE LOGIN modifies the login details

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 4

Time: 00:00:00
Which of the following code creates a server audit called “HIPPA_Audit” with a binary file as the target and no options?
a)







CREATE SERVER AUDIT HIPAA_Audit
TO SUBJECT ( FILEPATH ='\\SQLPROD_1\Audit\' );








b)







CREATE SERVER AUDIT HIPAA_Audit
TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );








c)







CREATE AUDIT HIPAA_Audit
TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );








d)







CREATE SERVER AUDIT HIPAA
TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );







a)

a)

b)

b)

c)

c)

d)

d)

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 5

Time: 00:00:00
Which of the following is used to determine the fragmentation for tables and indexes ?

DBCC SHOWCONTIG

DBCC SHOWCONTIG

DBCC CHECKDB

DBCC CHECKDB

DBCC CHECKCATALOG

DBCC CHECKCATALOG

None of the mentioned

None of the mentioned

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 6

Time: 00:00:00
Reasons to use native XML features in SQL Server instead of managing your XML data in the file system would be:

You need language support for query and data modification for cross-domain applications

You need language support for query and data modification for cross-domain applications

You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schema

You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schema

You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer

You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer

All of the mentioned

All of the mentioned

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 7

Time: 00:00:00
Consider the following ORACLE relations : One (x, y) = {<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4, 8>} Two (x, y) = {<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>, <4, 8>, <9, 9>, <1, 6>} Consider the following two SQL queries SQ1 and SQ2 :
SQ1 : SELECT * FROM One)
EXCEPT
(SELECT * FROM Two);
SQ2 : SELECT * FROM One)
EXCEPT ALL
(SELECT * FROM Two);
For each of the SQL queries, what is the cardinality (number of rows) of the result obtained when applied to the instances above ?

2 and 1 respectively

2 and 1 respectively

1 and 2 respectively

1 and 2 respectively

1 and 1 respectively

1 and 1 respectively

2 and 2 respectively

2 and 2 respectively

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 8

Time: 00:00:00
Which of the following statement is true ?

DELETE does not free the space containing the table and TRUNCATE free the space containing the table

DELETE does not free the space containing the table and TRUNCATE free the space containing the table

Both DELETE and TRUNCATE free the space containing the table

Both DELETE and TRUNCATE free the space containing the table

Both DELETE and TRUNCATE does not free the space containing the table

Both DELETE and TRUNCATE does not free the space containing the table

DELETE free the space containing the table and TRUNCATE does not free the space containing the table

DELETE free the space containing the table and TRUNCATE does not free the space containing the table

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 9

Time: 00:00:00
What is the purpose of the SQL AS clause ?

The AS SQL clause is used change the name of a column in the result set or to assign a name to a derived column

The AS SQL clause is used change the name of a column in the result set or to assign a name to a derived column

The AS clause is used with the JOIN clause only

The AS clause is used with the JOIN clause only

The AS clause defines a search condition

The AS clause defines a search condition

All of the mentioned

All of the mentioned

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

Question 10

Time: 00:00:00
The document containing the XML schema for the XML output by SET STATISTICS XML is located at :

\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xml

\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xml

Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsl

Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsl

Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsl

Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsl

None of the mentioned

None of the mentioned

Once you attempt the question then PrepInsta explanation will be displayed.

Please login to submit your explanation

["0","40","60","80","100"]
["Need more practice!","Keep trying!","Not bad!","Good work!","Perfect!"]

Personalized Analytics only Availble for Logged in users

Analytics below shows your performance in various Mocks on PrepInsta

Your average Analytics for this Quiz

Rank

-

Percentile

0%

Get over 200+ Courses under One Subscription

mute

Don’t settle Learn from the Best with PrepInsta Prime Subscription

Learn from Top 1%

One Subscription, For Everything

The new cool way of learning and upskilling -

Limitless Learning

One Subscription access everything

Job Assistance

Get Access to PrepInsta Prime

Top Faculty

from FAANG/IITs/TOP MNC's

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others.