MySQL Questions and Answers for Freshers focuses on “MySQL Identifier Syntax and Naming Rules”.
1. Which of the following characters is illegal in naming an unquoted identifier in SQL?
a) _
b) $
c) 2
d) .
Answer: d
Clarification: 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.
2. In MySQL, identifier names can start with a digit.
a) True
b) False
Answer: a
Clarification: 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.
3. How are identifiers quoted in MySQL?
a) single quotes
b) double quotes
c) backticks
d) can’t be quoted
Answer: c
Clarification: 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.
4. Which of the following is an illegal unquoted identifier name?
a) 123_id
b) 123id
c) id1
d) 123
Answer: d
Clarification: 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.
5. Which server mode value enables use of double quotes to wrap identifier names?
a) ANSI
b) ANSI_QUOTES
c) TRADITIONAL
d) PIPES_AS_CONCAT
Answer: b
Clarification: 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.
6. Which server mode value disables use of built in function names as identifiers?
a) ANSI
b) IGNORE_FUNC
c) TRADITIONAL
d) IGNORE_SPACE
Answer: d
Clarification: 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.
7. What is the maximum length for alias names in terms of characters?
a) 64
b) 128
c) 256
d) 32
Answer: c
Clarification: 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.
8. 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 name
Answer: d
Clarification: 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.
9. If no database has been selected, specifying a database qualifier is necessary.
a) True
b) False
Answer: a
Clarification: 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.
10. 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`
Answer: b
Clarification: 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.