MySQL Database Multiple Choice Questions on “Obtaining Database Metadata”.
1. The general term for information about databases and the objects in MySQL is _________
a) datum
b) info
c) record
d) metadata
Answer: d
Clarification: There are various ways in which MySQL facilitates the methods to obtain information about databases and the objects in them. For example, ‘SHOW’ statements and ‘INFORMATION_SCHEMA’.
2. Which statement is used to determine the storage engine for individual tables?
a) SHOW STATUS OF TABLE
b) SHOW STATUS TABLE
c) SHOW TABLE STATUS
d) SHOW DEFAULT STATUS
Answer: c
Clarification: MySQL provides a method to determine the storage engine for each table with the ‘SHOW TABLE STATUS’ statement. The output of this statement is the name of the storage engine indicator.
3. INFORMATION_SCHEMA is based on the SQL standard.
a) True
b) False
Answer: a
Clarification: ‘INFORMATION_SCHEMA’ is another way to access information about the databases and its objects (metadata). It is based on the ‘SQL standard’, although some content is MySQL-specific.
4. Which disk data does FILES table in INFORMATION_SCHEMA store?
a) NDB
b) NBD
c) NBK
d) NCD
Answer: a
Clarification: The ‘INFORMATION_SCHEMA’ is a method to access information about the databases and its objects known as metadata. It has various tables that can be viewed with ‘SHOW’ statement.
5. The information about table index characteristics is stored in which table of INFORMATION_SCHEMA?
a) FILES
b) STATISTICS
c) SCHEMATA
d) VIEWS
Answer: b
Clarification: The ‘STATISTICS’ table in the ‘INFORMATION_SCHEMA’ stores information about the table index characteristics. ‘INFORMATION_SCHEMA’ is responsible for storing database metadata.
6. Which table in INFORMATION_SCHEMA stores information about storage engines and server plugins?
a) ENGINES, PLUGINS
b) FILES, PLUGINS
c) ENGINES, FILES
d) PLUGINS, STATISTICS
Answer: a
Clarification: The ‘INFORMATION_SCHEMA’ is a very useful in MySQL. It has many tables each serving its individual purpose. The storage engine information is stored in ENGINES and plugin information in PLUGINS.
7. INFORMATION_SCHEMA is more portable than SHOW statements.
a) True
b) False
Answer: a
Clarification: The ‘INFORMATION_SCHEMA’ is a method to access information about the databases and its objects known as metadata. Its content is MySQL-specific which makes it more portable.
8. Which table stores information about the threads executing within the server?
a) PROCESS
b) PROCESSLIST
c) LIST
d) THREADSLIST
Answer: b
Clarification: The table ‘PROCESSLIST’ is responsible for storing all the information about the threads executing within the server. The other tables are not valid tables in the INFORMATION_SCHEMA.
9. The command which lists databases managed by the server is _____________
a) mysqld
b) mysqlshow
c) mysqllist
d) mysqldb
Answer: b
Clarification: The ‘mysqlshow’ command provides almost the similar information as some ‘SHOW’ statements. It also enables to extract database and table information at the command prompt.
10. The option in mysqlshow to show information about indexes in a table is _________
a) –no-data
b) –indexes
c) –keys
d) –flag
Answer: c
Clarification: The ‘mysqlshow’ command has various flags to provide a lot of options on displaying tables. The ‘–keys’ flag is specifically used to show information about indexes in a table.