MySQL Questions and Answers – Server SQL Mode

This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Server SQL Mode”.

1. The system variable in MySQL server that enables to configure the SQL mode is __________
a) sql_config
b) sql_mode
c) sql_server
d) sql_enable
View Answer

Answer: b
Explanation: The system variable ‘sql_mode’ is used by the MySQL server to configure the SQL mode. It has an impact on various aspects of the SQL statement executions. The other variables do not exist in the server.

2. Any client in the client/server architecture of MySQL can change how the server behaves in relation to itself without impact on other clients.
a) True
b) False
View Answer

Answer: a
Explanation: The MySQL server system variable ‘sql_mode’ can be set up globally. In this way, individual clients would have the ability to change their connection to the server. This variable can affect several aspects of statement executions.

3. How do the STRICT_ALL_TABLES and STRICT_TRANS_TABLES mode values deal with bad data?
a) reject them
b) accept them
c) change them to the closest legal value and accept
d) change them to the closest legal value and reject
View Answer

Answer: a
Explanation: ‘STRICT_ALL_TABLES’ and ‘STRICT_TRANS_TABLES’ are the MySQL server mode values that deal with bad data in a ‘strict’ manner. They do not make any change to the data. It is simply rejected.
advertisement
advertisement

4. Which mode tells the server to recognize double quote as an identifier quoting character?
a) ANSI_DQ
b) ANSI_QUOTES
c) ANSI_RECG_QUOTES
d) ANSI_RECG_DQUOTES
View Answer

Answer: b
Explanation: ‘ANSI_QUOTES’ is the MySQL server mode value stored in the system variable which directs the MySQL server to treat the double quote character as a quoting character for identifiers. The others are not valid mode values.

5. Which mode tells the server to recognize || as the string concatenation operator?
a) PIPES_AS_CONCAT
b) ORS_AS_CONCAT
c) DOUBLE_PIPE_AS_CONCAT
d) LOGIC_OR_AS_CONCAT
View Answer

Answer: a
Explanation: ‘PIPES_AS_CONCAT’, one of the many MySQL server mode values stored in the system variable would direct the server to treat the pipe characters as the standard SQL string concatenation operator rather than the logical or. The rest mode values are invalid.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

6. Which of these modes is a composite server mode?
a) COMPOSITE
b) COMPOSITE_MODE
c) COMPOSITE_SERVER
d) ANSI
View Answer

Answer: d
Explanation: The ‘ANSI’ mode value is a composite mode. It turns on several other mode values like ANSI_QUOTES, PIPES_AS_CONCAT. This makes the server behave much like standard SQL than the default mode.

7. MySQL server mode values are case sensitive.
a) True
b) False
View Answer

Answer: b
Explanation: The MySQL server mode values stored in the system variable are not case sensitive. So it does not make a difference if you accidentally store the mode values in lowercase or uppercase.
advertisement

8. Which of the following commands sets the SQL mode as TRADITIONAL?
a) –sqlmode=’TRADITIONAL’
b) –sql_mode=’TRADITIONAL’
c) –sql-mode=’TRADITIONAL’
d) –sql.mode=’TRADITIONAL’
View Answer

Answer: c
Explanation: When the server starts, the command –sql-mode=’TRADITIONAL’ can be used to set the SQL mode as ‘TRADITIONAL’. This can be stored in an option file or can be directly executed on the command line.

9. Which statement can be used to specify the sql_mode system variable at runtime?
a) SPECIFY
b) SET
c) ASSIGN
d) CHANGE
View Answer

Answer: b
Explanation: The ‘SET’ statement can change the SQL mode at runtime. For example, the statement SET sql_mode = ‘ANSI_QUOTES’ can be used by a client in its own session specific SQL mode.
advertisement

10. When no modes are enabled, what does this SQL statement return?

SELECT @@SESSION.sql_mode;

a) empty value
b) zero
c) a negative value
d) a garbage value
View Answer

Answer: a
Explanation: When it is needed to determine the current value of the session SQL mode, the above statement is executed. The returned value consists of a comma-separated list of the modes that are enabled.

Sanfoundry Global Education & Learning Series – MySQL Database.

To practice all areas of MySQL Database, here is complete set of 1000+ Multiple Choice Questions and Answers.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.