Database MCQ Set 1
1. The MySQL server is highly configurable.
a) True
b) False
Answer
Answer: a [Reason:] The MySQL server is highly configurable. Some of the operational characteristics that you can control include which storage engines the server supports, the default character set, and its default time zone.
2. Multiple MySQL servers cannot be run on the same machine.
a) True
b) False
Answer
Answer: a [Reason:] It is useful to run multiple servers on the same machine under certain circumstances. A new MySQL release can also be tested while leaving the current production server in place.
3. The log in which data changes received from a replication master server are written are ________
a) error log
b) general query log
c) binary log
d) relay log
Answer
Answer: d [Reason:] The Relay Log has the data changes received from a replication master server written in it. The problems encountered during the starting, running or stopping of ‘mysqld’ is written in error log.
4. Which of these is the metadata log?
a) error log
b) ddl log
c) binary log
d) relay log
Answer
Answer: b [Reason:] The DDL log is also known as the metadata log. The metadata operations performed by the DDL statements. The Relay Log has the data changes received from a replication master server written in it.
5. The max_binlog_cache_size system variable has default size ________
a) 1 GB
b) 2 GB
c) 4 GB
d) 8 GB
Answer
Answer: c [Reason:] The max_binlog_cache_size system variable can be used to restrict the total size used to cache a multiple-statement transaction. If transaction is larger than this it fails and rolls back.
6. What has higher priority?
writes, reads
a) writes
b) reads
c) same priority
d) machine dependent
Answer
Answer: a [Reason:] A client performing an operation that modifies a table is a writer and the client that performs a retrieval from the table is a reader. The writes have higher priority than reads.
7. The default scheduling property of MySQL is implemented by how many of these?
MyISAM, MERGE, MEMORY
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] The MyISAM, MERGE and the MEMORY storage engines implement the default scheduling policy of MySQL with the help of the table locks. Whenever a client accesses a table a lock for it must be acquired first.
8. Which of these is faster when accessing data?
memory, disk
a) memory
b) disk
c) same speed
d) machine dependent
Answer
Answer: a [Reason:] In MySQL, the administrators have more privileges since they have more control of the MySQL server or the machine on which it runs. Accessing data in memory is faster than from disk.
9. The system variable controls the size of the table cache _____
a) table_cache
b) cache_table
c) open_cache
d) cache_open
Answer
Answer: a [Reason:] In MySQL, when the server opens table files it keeps them open to minimize the number of file-opening operations. It maintains information about open files in the table cache.
10. Which of these exports table definitions and contents?
a) mysqldump
b) mysqladmin
c) mysqlimport
d) mysqlexport
Answer
Answer: a [Reason:] In MySQL, some distributions include a set of some client programs. ‘mysqldump’ exports the table definitions and the contents. ‘mysqladmin’ performs adminisitrative tasks.
Database MCQ Set 2
1. Bulk loading is less efficient than single row loading.
a) True
b) False
Answer
Answer: b [Reason:] In MySQL, bulk loading is more efficient than the single row loading. The key cache does not need to be flushed after each input record is loaded. It can also be flushed at the end of the batch of records.
2. Loading is faster when a table has no indexes than when it is indexed.
a) True
b) False
Answer
Answer: a [Reason:] Loading is faster when a table has no indexes. If there are indexes, the contents of the row should be added to the table and each index must also be modified to notify the addition of the new row.
3. Which is more efficient?
LOAD DATA, INSERT
a) LOAD DATA
b) INSERT
c) same
d) indeterminate
Answer
Answer: a [Reason:] In MySQL, the ‘LOAD DATA’ in all forms is more efficient than the ‘INSERT’ because it loads rows in bulk. The server first parses then interprets only one statement instead of many.
4. Without LOCAL, LOAD DATA is _____
a) more efficient
b) less efficient
c) same speed
d) arbitrary
Answer
Answer: a [Reason:] LOAD DATA is more efficient without LOCAL. Without LOCAL, the file must be located on the server. The FILE privilege must be given. The server can read the file directly from disk.
5. Which option turns on the –extended-insert?
a) –opt
b) –opti
c) –op
d) –optimize
Answer
Answer: a [Reason:] If the –opt option is enabled it turns on the –extended-insert option, which produces multiple row INSERT statements and some other options that allow the dump file to be processed more efficiently.
6. Which table option for MyISAM tables reduces index flushing?
a) DELAY_KEY_WRITE
b) DELAY_WRITE_KEY
c) KEY_WRITE_DELAY
d) WRITE_KEY_DELAY
Answer
Answer: a [Reason:] For MyISAM tables a strategy for reducing index flushing is to use the DELAY_KEY_WRITE table option. With this option the data rows are written to the data file immediately.
7. Which option repairs MyISAM tables automatically after they open?
a) –myisam-recover=FORCE
b) –myisam-recover=STOP
c) –recover-myisam=FORCE
d) –recover-myisam=STOP
Answer
Answer: a [Reason:] In order to make sure that the repairs happen the server is started with the –myisam-recover=FORCE option. This enables the server to check MyISAM tables when it opens them and repair them automatically.
8. The option which delays index flushing for slave server is _____
a) –delay-write-key=ALL
b) –delay-key-write=ALL
c) –key-write-delay=NONE
d) –key-delay-write=NONE
Answer
Answer: b [Reason:] For the replication slave servers, the –delay-key-write=ALL option delays index flushing for all MyISAM tables, regardless of how they were created originally on the master server.
9. TO enable the compressed client/server protocol the option is _____
a) –enable
b) –compress
c) –reduce
d) –restrict
Answer
Answer: b [Reason:] The compressed client/server protocol is used to reduce the amount of data going over the network. For most of the MySQL clients this is specified using the –compress command line option.
10. To reactivate indexes the command used is _____
a) DISABLE KEYS
b) DISABLE INDEXES
c) ENABLE KEYS
d) ENABLE INDEXES
Answer
Answer: c [Reason:] In MySQL, the clauses ‘DISABLE KEYS’ and ‘ENABLE KEYS’ are used to deactivate and reactivate indexes for the table. The server does the work in deactivations and reactivations.
Database MCQ Set 3
1. The option that specifies the data directory location at server startup is ________
a) –data
b) –data_dir
c) –data-dir
d) –datadir
Answer
Answer: d [Reason:] At the server startup the data directory location is specified by using a ‘–datadir=dir_name’ option. It is helpful for naming a location other than the compiled in default.
2. The datadir variable value can be seen using ________
a) SHOW VARIABLES
b) DISP VARIABLES
c) CONNECT VARIABLES
d) SHOW VARIABLE
Answer
Answer: a [Reason:] The data directory location is checked by the ‘datadir’ variable. It can be obtained using a ‘SHOW VARIABLES’ statement or a ‘mysqladmin’ variables command, like: SHOW VARIABLES LIKE ‘datadir’.
3. Which data directory subdirectory provides the information used to inspect the internal execution of the server at runtime.
a) mysql
b) performance_schema
c) sys
d) nbdinfo
Answer
Answer: b [Reason:] The ‘performance_schema’ directory corresponds to the Performance Schema. It provides information used to inspect the internal execution of the server at runtime. The ‘sys’ directory maps to ‘nbdinfo’.
4. Which data directory subdirectory corresponds to the nbdinfo database?
a) mysql
b) performance_schema
c) sys
d) nbdinfo
Answer
Answer: b [Reason:] The sys directory corresponds to the sys schema. It provides a set of objects to interpret Performance Schema information. The ‘performance_schema’ directory corresponds to the Performance Schema.
5. Encrypted connections can be established using ________
a) exec_stmt_ssl
b) exec_ssl_stmt
c) exec_stmnt_ssl
d) exec_ssl_stmnt
Answer
Answer: a [Reason:] For the ‘exec_stmt_ssl’ to work properly, MySQL must have been built with SSL support, and the server must be started with the proper options that identify its certificate and key files.
6. What is exec_stmt_ssl written in?
a) C++
b) C
c) Python
d) Perl
Answer
Answer: b [Reason:] In MySQL, the ‘sampdb’ distribution contains a source file named ‘exec_stmt_ssl.c’ from which the client program ‘exec_stmt_ssl’ can be built. There is a procedure to build this file.
7. What is the embedded server library containing the MySQL server linkable into applications?
a) libmysqla
b) libmysqlb
c) libmysqlc
d) libmysqld
Answer
Answer: d [Reason:] MySQL includes an embedded server library, libmysqld, containing the MySQL server in a way that can be linked into applications. This enables the production of MySQL-based standalone applications.
8. What is the embedded server library when building from source enabled by?
a) –with-embedded-server
b) –with-server-embedded
c) –with-embedded-library
d) –with-library-embedded
Answer
Answer: a [Reason:] While building from the source, the embedded server library is enabled by using the option ‘–with-embedded-server’ while running ‘configure’. This applies equally to MySQL 5.0 and 5.1.
9. Multiple statement execution is not enabled by default.
a) True
b) False
Answer
Answer: a [Reason:] The ‘multiple-statement’ execution is not enabled by default, so the server must be told that it is intended to be used. There are two ways to tell the server to enable the execution.
10. An embedded application includes the server side and client side.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, an embedded application includes both a client side and a server side, in order that it can process one group of options for the client, and another group for the server.
Database MCQ Set 4
1. The hub of a MySQL installation is ________
a) mysqla
b) mysqlb
c) mysqlc
d) mysqld
Answer
Answer: d [Reason:] The server, mysqld , is the hub of a MySQL installation; it performs all manipulation of databases and tables. On Unix, several related scripts are available to assist in server startup.
2. How many of these are for use with only MyISAM tables?
mysql, mysqladmin, mysqldump, mysqlisamchk
a) 1
b) 2
c) 3
d) 4
Answer
Answer: a [Reason:] ‘mysqlcheck’ and ‘mysqlisamchk’ are the programs that perform table checking, analysis, optimization and repairs when tables become damaged. ‘mysqlcheck’ works with MyISAM tables.
3. The place where the server stores its databases and status files is the data directory.
a) True
b) False
Answer
Answer: a [Reason:] The MySQL data directory is the place where the server stores its databases and status files. It is crucial to understand the all structures and contents of the data directory.
4. The tool for copying databases is ________
a) mysql
b) mysqlcheck
c) mysqlhotcopy
d) mysqlisamchk
Answer
Answer: c [Reason:] ‘mysqldump’ and ‘mysqlhotcopy’ are the tools or programs that are used for performing the backing up of the databases and copying databases from one server to the other server(s).
5. Which statement is used to check how MySQL would execute a SELECT query?
a) TELL
b) SHOW
c) DISPLAY
d) EXPLAIN
Answer
Answer: d [Reason:] In MySQL, by issuing an EXPLAIN statement MySQL displays some information about how it would execute a SELECT query without actually executing it. It is prefixed with the query.
6. What is used to perform analysis of key values by the server?
a) ANALYZE KEYS
b) ANALYZE TABLE
c) PERFORM ANALYSIS
d) PERFORM TABLE ANALYSIS
Answer
Answer: b [Reason:] In MySQL, for the MyISAM and InnoDB tables, the server can be told to perform an analysis of key values by issuing the ANALYZE TABLE statement. It helps in knowing about query optimization.
7. The function used to convert an int to string is ________
a) INET_ATON()
b) INET_NTOA()
c) INET_ITOS()
d) INET_STOI()
Answer
Answer: b [Reason:] In MySQL, the function used to convert an integer to a string is INET_NTOA(). On the other hand, the function INET_ATON() converts a string to the corresponding integer value.
8. The function used to convert a string to an int is ________
a) INET_ATON()
b) INET_NTOA()
c) INET_ITOS()
d) INET_STOI()
Answer
Answer: a [Reason:] In MySQL, the function used to convert a string to an integer is INET_ATON(). On the other hand, the function INET_NTOA() converts a string to the corresponding integer value.
9. Which of the following is more efficient?
LOAD DATA, INSERT
a) LOAD DATA
b) INSERT
c) same
d) indeterminate
Answer
Answer: a [Reason:] In MySQL, the ‘LOAD DATA’ in all forms is more efficient than the ‘INSERT’ because it loads rows in bulk. The server first parses then interprets only one statement instead of many.
10. Without LOCAL, LOAD DATA is _____
a) less efficient
b) more efficient
c) same speed
d) arbitrary
Answer
Answer: b [Reason:] LOAD DATA is more efficient without LOCAL. Without LOCAL, the file must be located on the server. The FILE privilege must be given. The server can read the file directly from disk.
Database MCQ Set 5
1. What exports table definitions and contents?
a) mysqldump
b) mysqladmin
c) mysqlimport
d) mysqlexport
Answer
Answer: a [Reason:] In MySQL, some distributions include a set of some client programs. ‘mysqldump’ exports the table definitions and the contents. ‘mysqladmin’ performs adminisitrative tasks.
2. What loads data files into tables?
a) mysqldump
b) mysqladmin
c) mysqlimport
d) mysqlexport
Answer
Answer: c [Reason:] ‘mysqlimport’ loads the data files into the tables. In MySQL, some distributions include a set of some client programs. ‘mysqldump’ exports the table definitions and the contents.
3. What performs administrative operations?
a) mysqldump
b) mysqladmin
c) mysqlimport
d) mysqlexport
Answer
Answer: b [Reason:] In MySQL, some distributions include a set of some client programs. ‘mysqladmin’ performs the administrative operations. ‘mysqlimport’ loads the data files into the tables.
4. With MySQL programs, input handling can be customized.
a) True
b) False
Answer
Answer: a [Reason:] With mysql, raw SQL statements can be entered. With MySQL programs input methods can be provided for the user that are more intuitive and easier to be used. So input handling is customized.
5. Input can be validated in MySQL programs.
a) True
b) False
Answer
Answer: a [Reason:] Input provided by the user can be validated. For instance, dates can be checked to make sure they conform to the format that MySQL expects, or certain fields can be filled in to check.
6. Which option executes all SQL statements in a SQL script irrespective of the number of errors?
a) –ensure
b) –violent
c) –force
d) –run
Answer
Answer: c [Reason:] If SQL queries in a file are run using mysql in batch mode, mysql either quits after the first error. If the –force option is specified all the queries are executed indiscriminately.
7. Which of these is used during MySQL setup?
a) mysqld_multi
b) mysql.server
c) mysqld_safe
d) comp_err
Answer
Answer: d [Reason:] The program ‘comp_err’ is used during the MySQL build or the MySQL installation process. This program compiles the error message files from the error source files. The others are not run during startup.
8. Which of these is not used to connect to the MySQL server?
a) mysql
b) mysqladmin
c) mysqlcheck
d) mysql_upgrade
Answer
Answer: d [Reason:] mysql_upgrade is used after a MySQL upgrade operation. It checks tables for incompatibilities and repairs them if necessary. It updates the grant tables with any changes that have been made in newer versions of MySQL.
9. Which program emulates the client load for a MySQL server?
a) mysqlslap
b) mysqldump
c) mysqladmin
d) mysqlimport
Answer
Answer: a [Reason:] mysqlslap is a client program designed to emulate client load for a MySQL server. It reports the timing of each stage. It works as if multiple clients are accessing the server.
10. Which program reads statement from a binary log?
a) mysql_config
b) mysqlbinlog
c) mysqldump
d) mysqlimport
Answer
Answer: b [Reason:] ‘mysqlbinlog’ is a utility program for reading statements from a binary log. The log of executed statements contained in the binary log files can be used to help recover from a crash.
