Server SQL Mode Questions and Answers

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.

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.

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.

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.

« Prev - MySQL Questions and Answers – Interacting with MySQL

» Next - MySQL Questions and Answers – MySQL Identifier Syntax and Naming Rules

Related Posts:

Recommended Articles: