173 + Interview Questions in Using Sql To Manage Data in Mysql Page 4 InterviewSolution

151.	Which variable is used to set table alias names as non-case sensitive?(a) lower_case_table_names(b) lower_case_all(c) lower_case_alias(d) lower_case_aliasesThe question was asked in final exam.My doubt stems from Case Sensitivity in SQL Statements topic in section Using SQL to Manage Data of MySQL
Answer» The correct option is (a) lower_case_table_names The best I can explain: In MySQL, by DEFAULT the alias names are CASE sensitive. An alias can HENCE be specified in any letter case, upper, lower or mixed. If the variable ‘lower_case_table_names’ is non ZERO, the alias names of TABLES are not case sensitive.

Discussion

152.	Which of the following is case sensitive?(a) Stored function name(b) Stored procedure name(c) Trigger name(d) Event nameThe question was posed to me in semester exam.This question is from Case Sensitivity in SQL Statements topic in section Using SQL to Manage Data of MySQL
Answer» The correct choice is (c) Trigger name Explanation: The stored functions and stored PROCEDURE names in MySQL are not CASE sensitive. EVENT names are ALSO not case sensitive. UNLIKE the standard SQL, the trigger names in MySQL is case sensitive.

Discussion

153.	The default case sensitivity of database and table names depends on ___________(a) SQL server(b) Server SQL mode(c) Operating system of machine(d) Does not depend on anythingThe question was posed to me in an international level competition.This intriguing question originated from Case Sensitivity in SQL Statements in section Using SQL to Manage Data of MySQL
Answer» Correct choice is (c) Operating SYSTEM of machine To ELABORATE: The default case sensitivity imposes a dependency on the operating system of the machine on which the MySQL server is running. WINDOWS does not treat database and table names as case sensitive unlike UNIX.

Discussion

154.	Case sensitivity in SQL statements does not vary for different operating systems of the machine on which the server is running.(a) True(b) FalseI got this question during an interview.I need to ask this question from Case Sensitivity in SQL Statements in portion Using SQL to Manage Data of MySQL
Answer» Correct choice is (b) False To elaborate: The case sensitivity rules in SQL statements are different for different statement elements, depend on what is being REFERRED to and ALSO on the MACHINE’s operating system on which the server is RUNNING.

Discussion

155.	Which of the following MySQL statements is valid if ‘`sampledb`’ is a database and ‘`tbl`’ is a table in it?(a) SELECT * FROM `sampledb.member`(b) SELECT * FROM `sampledb`.`member`(c) SELECT * FROM `member`.`sampledb`(d) SELECT * FROM `member.sampledb`The question was asked in an online quiz.This intriguing question comes from MySQL Identifier Syntax and Naming Rules topic in division Using SQL to Manage Data of MySQL
Answer» Right choice is (b) SELECT * FROM `sampledb`.`MEMBER` Explanation: When quotes are being used to refer to a qualified NAME, the individual identifiers are QUOTED within the name separately. So, the database name `sampledb` and table name `TBL` are quoted separately.

Discussion

156.	If no database has been selected, specifying a database qualifier is necessary.(a) True(b) FalseI got this question during an interview.This interesting question is from MySQL Identifier Syntax and Naming Rules topic in portion Using SQL to Manage Data of MySQL
Answer» Right ANSWER is (a) True Explanation: When no database has been selected, not specifying the database qualifier would be CONFUSING. A table cannot be referred to if the database qualfier is ABSENT SINCE it would be unclear as to which database it belongs to.

Discussion

157.	What does a fully qualified table name consist of?(a) only the table name(b) only the database name(c) table name followed by database name(d) database name followed by table nameThis question was addressed to me in a national level competition.My question comes from MySQL Identifier Syntax and Naming Rules in division Using SQL to Manage Data of MySQL
Answer» Correct ANSWER is (d) database name FOLLOWED by table name Easy explanation: MySQL identifiers USE qualifiers. An element of a database has a FULLY qualified name. A database table in MySQL has the fully qualified name as the database name followed by the table name.

Discussion

158.	What is the maximum length for alias names in terms of characters?(a) 64(b) 128(c) 256(d) 32The question was posed to me during an interview.This is a very interesting question from MySQL Identifier Syntax and Naming Rules in chapter Using SQL to Manage Data of MySQL
Answer» The correct answer is (c) 256 To EXPLAIN I would say: The IDENTIFIERS in MySQL have a maximum length of 64 characters. However, the alias names for identifiers can have the number of characters up to 256. Aliases can ALSO be quoted or UNQUOTED.

Discussion

159.	Which server mode value disables use of built in function names as identifiers?(a) ANSI(b) IGNORE_FUNC(c) TRADITIONAL(d) IGNORE_SPACEThe question was asked during an interview.My query is from MySQL Identifier Syntax and Naming Rules topic in portion Using SQL to Manage Data of MySQL
Answer» RIGHT OPTION is (d) IGNORE_SPACE Easiest explanation: The SERVER SQL mode value ‘IGNORE_FUNC’ is used to RESTRICT the identifier naming conventions. When this is enabled, the names of built in functions can no longer be used as identifiers.

Discussion

160.	Which server mode value enables use of double quotes to wrap identifier names?(a) ANSI(b) ANSI_QUOTES(c) TRADITIONAL(d) PIPES_AS_CONCATThe question was asked in an online interview.Question is from MySQL Identifier Syntax and Naming Rules in division Using SQL to Manage Data of MySQL
Answer» RIGHT CHOICE is (b) ANSI_QUOTES To explain I would SAY: In MYSQL, use of double quotes is enabled when the server SQL mode ‘ANSI_QUOTES’ is set. The use of backticks is still allowed. For example, SELECT TABLE “my table” is a statement that is allowed.

Discussion

161.	Which of the following is an illegal unquoted identifier name?(a) 123_id(b) 123id(c) id1(d) 123This question was posed to me in semester exam.I would like to ask this question from MySQL Identifier Syntax and Naming Rules topic in chapter Using SQL to Manage Data of MySQL
Answer» Correct option is (d) 123 Explanation: In MySQL, the quoted and unquoted IDENTIFIER names have differences in the level of RESTRICTIONS imposed on them. An unquoted identifier name can’t have all digits since it would be impossible to distinguish it from an INTEGER CONSTANT.

Discussion

162.	How are identifiers quoted in MySQL?(a) single quotes(b) double quotes(c) backticks(d) can’t be quotedThis question was addressed to me in quiz.This is a very interesting question from MySQL Identifier Syntax and Naming Rules in division Using SQL to Manage Data of MySQL
Answer» Correct choice is (c) backticks The best I can explain: An identifier is quoted within the backtick CHARACTERS ‘`’. This allows USE of any character except a byte with value 0 or 255. Single QUOTES and double quotes are not allowed when quoting identifier NAMES.

Discussion

163.	In MySQL, identifier names can start with a digit.(a) True(b) FalseThe question was asked in my homework.This question is from MySQL Identifier Syntax and Naming Rules topic in division Using SQL to Manage Data of MySQL
Answer» The correct OPTION is (a) True Explanation: Identifiers are allowed to start with any LEGAL character. So it may begin with a digit. The special characters in the identifier characters consist of the ‘_’ and ‘$’. The other characters are the alphanumeric characters.

Discussion

164.	Which of the following characters is illegal in naming an unquoted identifier in SQL?(a) _(b) $(c) 2(d) .This question was addressed to me by my school principal while I was bunking the class.My doubt stems from MySQL Identifier Syntax and Naming Rules topic in portion Using SQL to Manage Data of MySQL
Answer» The correct choice is (d) . Explanation: An IDENTIFIER is used to refer to a database or its elements. These elements can be entire tables or ATTRIBUTES. The names of these identifiers FOLLOW some SET of rules, so they have a set of legal characters.

Discussion

165.	Which statement can be used to specify the sql_mode system variable at runtime?(a) SPECIFY(b) SET(c) ASSIGN(d) CHANGEThis question was posed to me in a job interview.This key question is from Server SQL Mode in portion Using SQL to Manage Data of MySQL
Answer» The CORRECT ANSWER is (B) SET The 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.

Discussion

166.	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’I got this question by my college professor while I was bunking the class.This intriguing question originated from Server SQL Mode in division Using SQL to Manage Data of MySQL
Answer» The correct choice is (c) –SQL-mode=’TRADITIONAL’ The EXPLANATION is: 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.

Discussion

167.	MySQL server mode values are case sensitive.(a) True(b) FalseThe question was asked in an interview for job.Query is from Server SQL Mode in portion Using SQL to Manage Data of MySQL
Answer» Correct answer is (B) False To EXPLAIN I would say: 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.

Discussion

168.	Which of these modes is a composite server mode?(a) COMPOSITE(b) COMPOSITE_MODE(c) COMPOSITE_SERVER(d) ANSIThe question was posed to me during an online interview.Question is taken from Server SQL Mode topic in portion Using SQL to Manage Data of MySQL
Answer» CORRECT option is (d) ANSI Easiest 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.

Discussion

169.	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_CONCATThe question was asked in an interview for job.Query is from Server SQL Mode in chapter Using SQL to Manage Data of MySQL
Answer» CORRECT answer is (a) PIPES_AS_CONCAT Best 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.

Discussion

170.	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_DQUOTESThe question was posed to me in unit test.The origin of the question is Server SQL Mode in portion Using SQL to Manage Data of MySQL
Answer» Right option is (b) ANSI_QUOTES The 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.

Discussion

171.	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 rejectThis question was posed to me during an interview.My enquiry is from Server SQL Mode topic in portion Using SQL to Manage Data of MySQL
Answer» Right option is (a) REJECT them Easiest 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.

Discussion

172.	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) FalseThe question was asked in a national level competition.My enquiry is from Server SQL Mode in portion Using SQL to Manage Data of MySQL
Answer» Correct ANSWER is (a) True 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.

Discussion

173.	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_enableI got this question in examination.The query is from Server SQL Mode in section Using SQL to Manage Data of MySQL
Answer» The correct option is (b) sql_mode To explain I WOULD say: 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.

Discussion

Explore topic-wise InterviewSolutions in .

Which variable is used to set table alias names as non-case sensitive?(a) lower_case_table_names(b) lower_case_all(c) lower_case_alias(d) lower_case_aliasesThe question was asked in final exam.My doubt stems from Case Sensitivity in SQL Statements topic in section Using SQL to Manage Data of MySQL

Which of the following is case sensitive?(a) Stored function name(b) Stored procedure name(c) Trigger name(d) Event nameThe question was posed to me in semester exam.This question is from Case Sensitivity in SQL Statements topic in section Using SQL to Manage Data of MySQL

Case sensitivity in SQL statements does not vary for different operating systems of the machine on which the server is running.(a) True(b) FalseI got this question during an interview.I need to ask this question from Case Sensitivity in SQL Statements in portion Using SQL to Manage Data of MySQL

If no database has been selected, specifying a database qualifier is necessary.(a) True(b) FalseI got this question during an interview.This interesting question is from MySQL Identifier Syntax and Naming Rules topic in portion Using SQL to Manage Data of MySQL

What is the maximum length for alias names in terms of characters?(a) 64(b) 128(c) 256(d) 32The question was posed to me during an interview.This is a very interesting question from MySQL Identifier Syntax and Naming Rules in chapter Using SQL to Manage Data of MySQL

Which server mode value disables use of built in function names as identifiers?(a) ANSI(b) IGNORE_FUNC(c) TRADITIONAL(d) IGNORE_SPACEThe question was asked during an interview.My query is from MySQL Identifier Syntax and Naming Rules topic in portion Using SQL to Manage Data of MySQL

Which server mode value enables use of double quotes to wrap identifier names?(a) ANSI(b) ANSI_QUOTES(c) TRADITIONAL(d) PIPES_AS_CONCATThe question was asked in an online interview.Question is from MySQL Identifier Syntax and Naming Rules in division Using SQL to Manage Data of MySQL

Which of the following is an illegal unquoted identifier name?(a) 123_id(b) 123id(c) id1(d) 123This question was posed to me in semester exam.I would like to ask this question from MySQL Identifier Syntax and Naming Rules topic in chapter Using SQL to Manage Data of MySQL

How are identifiers quoted in MySQL?(a) single quotes(b) double quotes(c) backticks(d) can’t be quotedThis question was addressed to me in quiz.This is a very interesting question from MySQL Identifier Syntax and Naming Rules in division Using SQL to Manage Data of MySQL

In MySQL, identifier names can start with a digit.(a) True(b) FalseThe question was asked in my homework.This question is from MySQL Identifier Syntax and Naming Rules topic in division Using SQL to Manage Data of MySQL

Which statement can be used to specify the sql_mode system variable at runtime?(a) SPECIFY(b) SET(c) ASSIGN(d) CHANGEThis question was posed to me in a job interview.This key question is from Server SQL Mode in portion Using SQL to Manage Data of MySQL

MySQL server mode values are case sensitive.(a) True(b) FalseThe question was asked in an interview for job.Query is from Server SQL Mode in portion Using SQL to Manage Data of MySQL

Which of these modes is a composite server mode?(a) COMPOSITE(b) COMPOSITE_MODE(c) COMPOSITE_SERVER(d) ANSIThe question was posed to me during an online interview.Question is taken from Server SQL Mode topic in portion Using SQL to Manage Data of MySQL

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_CONCATThe question was asked in an interview for job.Query is from Server SQL Mode in chapter Using SQL to Manage Data of MySQL

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_DQUOTESThe question was posed to me in unit test.The origin of the question is Server SQL Mode in portion Using SQL to Manage Data of MySQL

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) FalseThe question was asked in a national level competition.My enquiry is from Server SQL Mode in portion Using SQL to Manage Data of MySQL

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_enableI got this question in examination.The query is from Server SQL Mode in section Using SQL to Manage Data of MySQL