Database MCQ Set 1
1. CHECK TABLE works with how many of the following?
MyISAM, ARCHIVE, CSV
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] The ‘CHECK TABLE’ statement provides an interface to the table checking capabilities of the server. It works for MyISAM and InnoDB tables as well as for ARCHIVE and CSV tables.
2. REPAIR TABLE works with how many of the following?
MyISAM, ARCHIVE, CSV
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] The ‘REPAIR TABLE’ statement provides an interface to the table repair capabilities of the server. It works with MyISAM and ARCHIVE tables. It also works with CSV tables as of MySQL 5.1.19.
3. Which myisamchk variable represents the size of buffer used to hold index blocks?
a) key_buffer_size
b) read_buffer_size
c) sort_buffer_size
d) write_buffer_size
Answer
Answer: a [Reason:] The myisamchk variable ‘key_buffer_size’ stores the size of buffer that is used to hold index blocks. ‘read_buffer_size’ stores size of buffer used for read operations, ‘sort_buffer_size’ is used for sorting.
4. Which option is used in ‘mysqldump’ to make all tables in the destination databases to use a different storage engine?
a) –next-storage-engine
b) –new-storage-engine
c) –clear-storage-engine
d) –get-storage-engine
Answer
Answer: b [Reason:] While using the program ‘mysqldump’ in MySQL to copy databases from server to server, all tables in the destination databases can be directed to use a different storage engine with the –new-storage-engine option.
5. What is the hub of a MySQL installation?
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.
6. With only MyISAM tables, how many of the following are for use?
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.
7. The place where the server stores its databases and status files is the database directory.
a) True
b) False
Answer
Answer: b [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.
8. What is the output from PHP?
a) statically generated
b) dynamically generated
c) not generated
d) no output
Answer
Answer: b [Reason:] The interpreter switches between code mode back and text mode when it sees another special tag which signals the end of the code. This enables the mix of static text with dynamically generated results.
9. What are PHP variables preceded by?
a) _
b) @
c) $
d) &
Answer
Answer: c [Reason:] All the PHP scripts that use variables have variable names preceded by the dollar sign (‘$’). The variables are signified by this identifier, irrespective of the value it stores.
10. Which data type is best suited to store currency values?
a) INT
b) FLOAT
c) DOUBLE
d) DECIMAL
Answer
Answer: d [Reason:] Currency is a numeric information. For monetary calculations, FLOAT and DOUBLE are subject to rounding error and may not be suitable. A DECIMAL(M, 2) type is best suited for it.
Database MCQ Set 2
1. The AUTO_INCREMENT column attribute is best used with which type?
a) FLOAT
b) INT
c) CHARACTER
d) DOUBLE
Answer
Answer: b [Reason:] The AUTO_INCREMENT is a column attribute and it is best used with the integer datatypes. It automatically assigns monotonically increasing values to the AUTO_INCREMENT columns.
2. Which datatype is best suited to store currency values?
a) INT
b) FLOAT
c) DOUBLE
d) DECIMAL
Answer
Answer: d [Reason:] Currency is a numeric information. For monetary calculations, FLOAT and DOUBLE are subject to rounding error and may not be suitable. A DECIMAL(M, 2) type is best suited for it.
3. MySQL provides a date type that has an optional time part.
a) True
b) False
Answer
Answer: b [Reason:] In MySQL, there is no data type provided which has an optional time part. The ‘DATE’ type values never have a time part. The ‘DATETIME’ type values must have a time part in them.
4. If a column is expected to store values up to 2 million, the best datatype for it is _______
a) SMALLINT
b) TINYINT
c) MEDIUMINT
d) BIGINT
Answer
Answer: d [Reason:] The different numeric types used in MySQL are used to store different range of values. To store values of the order of a million, the MEDIUMINT or BIGINT datatype is sufficient.
5. Which of these types store the longest length of strings?
a) CHAR
b) VARCHAR
c) TINYTEXT
d) TEXT
Answer
Answer: d [Reason:] in MySQL, the different string datatypes are used to store different lenghts of the string. Here, the length would refer to the number of characters in the string. TEXT stores longer strings.
6. AUTO_INCREMENT columns must be NOT NULL.
a) True
b) False
Answer
Answer: a [Reason:] The ‘AUTO_INCREMENT’ columns must be NOT NULL. If the NOT NULL is omitted, MySQL adds it automatically. AUTO_INCREMENT column values begin with 1 and increase monotonically after it.
7. Which of these values is not valid as an AUTO_INCREMENT value?
a) 0
b) 1
c) 2
d) 3
Answer
Answer: a [Reason:] In MySQL, the ‘AUTO_INCREMENT’ values are a sequence of unique values assigned to the columns such that they increase monotonically. They begin with the value 1 and increment by a unit.
8. If an integer column is used for the values in range 1 to 99999, the best suitable datatype is ______
a) MEDIUMINT SIGNED
b) MEDIUMINT UNSIGNED
c) SMALLINT SIGNED
d) SMALLINT UNSIGNED
Answer
Answer: b [Reason:] Since the range starts from the positive value 1, it is best to keep the datatype as UNSIGNED. The maximum value is of the order of 10^5, so a MEDIUMINT datatype is well suited.
9. INT2 maps to MySQL type _______
a) TINYINT
b) SMALLINT
c) MEDIUMINT
d) BIGINT
Answer
Answer: b [Reason:] In order to facilitate the use of code written for SQL implementations from other vendors, MySQL maps the data types from the other vendor types to the appropriate MySQL type.
10. INT3 maps to MySQL type _______
a) TINYINT
b) SMALLINT
c) MEDIUMINT
d) BIGINT
Answer
Answer: c [Reason:] In order to facilitate the use of the code written for SQL implementations (reusability of code) from other vendors, MySQL maps the data types from the other vendor types to the appropriate MySQL type.
Database MCQ Set 3
1. Which of these is not a comment specifying construct?
a) #
b) /* */
c) —
d) !#
Answer
Answer: d [Reason:] The MySQL server understands three types of comments. The single line ‘#’ construct, the multiline /*…*/ construct and the — comment construct. MySQL conforms to the comment standards of SQL.
2. The MySQL double dashed comment style is same as the SQL standard.
a) True
b) False
Answer
Answer: b [Reason:] The MySQL double-dash (–) comment style is different from the comment style of standard SQL, which begins with just two dashes. It does not require the space before any following text.
3. What sets the association between one or more MyISAM tables and the named key cache?
a) BEGIN
b) CACHE INDEX
c) ALTER DATABASE
d) ALTER EVENT
Answer
Answer: b [Reason:] ‘CACHE INDEX’ sets up an association between one or more MyISAM tables and the named key cache which must already exist. The INDEX privilege is needed for each table named in the statement.
4. The statement that exits a labeled flow-control construct is _______
a) DESCRIBE
b) LEAVE
c) LOOP
d) RETURN
Answer
Answer: b [Reason:] The ‘LEAVE’ statement is used to exit a labeled flow-control construct. This statement must appear within the construct that has the given label. The syntax of the statement is ‘LEAVE label’.
5. What does ‘mysql_query()’ return on failure?
a) 0
b) 1
c) -1
d) a non-zero value
Answer
Answer: d [Reason:] Both of the functions named ‘mysql_query()’ and ‘mysql_real_query()’ return zero for statements that succeed. They return non zero for failure. A statement is successfully executed if the server accepts it.
6. Input handling can be customized with MySQL.
a) True
b) False
Answer
Answer: b [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.
7. The option that executes all SQL statements in a SQL script irrespective of the number of errors is _______
a) –force
b) –run
c) –ensure
d) –violent
Answer
Answer: a [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.
8. The option that is necessary to compile a C program having math functions is _______
a) -lm
b) -ln
c) -lp
d) -lq
Answer
Answer: a [Reason:] To compile a program written in the C language, it is compiled by issuing the option ‘-lm’. The examples of some math functions found in ‘math.h’ are floor(), sqrt(), pow(), log().
9. Which option is used for specifying the executable name while compiling with gcc?
a) -e
b) -o
c) -a
d) -b
Answer
Answer: b [Reason:] When a C program is compiled with gcc, the option -o is issued in order to specify the name of the executable that is created after compilation. The default name given is ‘a.out’.
10. The file that can be used to execute multiple compile statements is _____
a) makefile
b) dofile
c) putfile
d) pushfile
Answer
Answer: a [Reason:] The Makefile is used to write multiple commands in there. The file is executed by prefixing the command ‘make’ with the command to run the file script containing the statements.
Database MCQ Set 4
1. The statement that constructs a branching flow-control construct is _______
a) BEGIN…END
b) CASE
c) ITERATE
d) LEAVE
Answer
Answer: b [Reason:] The ‘CASE’ statement provides a branching flow-control construct. When the initial expression, ‘expr’, is present, CASE compares it to the expression following each ‘WHEN’.
2. Which statement terminates the execution of a function?
a) BEGIN…END
b) RETURN
c) ITERATE
d) LOOP
Answer
Answer: b [Reason:] The ‘RETURN’ statement is used only within stored functions, not stored procedures, triggers, or events. When this statement is executed it terminates execution of the function.
3. Association between one or more MyISAM tables and the named key cache is set by _______
a) BEGIN
b) CACHE INDEX
c) ALTER DATABASE
d) ALTER EVENT
Answer
Answer: b [Reason:] ‘CACHE INDEX’ sets up an association between one or more MyISAM tables and the named key cache which must already exist. The INDEX privilege is needed for each table named in the statement.
4. Which statement exits a labeled flow-control construct?
a) DESCRIBE
b) LEAVE
c) LOOP
d) RETURN
Answer
Answer: b [Reason:] The ‘LEAVE’ statement is used to exit a labeled flow-control construct. This statement must appear within the construct that has the given label. The syntax of the statement is ‘LEAVE label’.
5. What enables encrypted connections to be established?
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 the 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 does ‘mysql_query()’ return on failure?
a) 0
b) 1
c) -1
d) a non-zero value
Answer
Answer: d [Reason:] Both of the functions named ‘mysql_query()’ and ‘mysql_real_query()’ return zero for statements that succeed. They return non zero for failure. A statement is successfully executed if the server accepts it.
8. When linking to a static MySQL C client library, the client library and the client application can use different compiler options.
a) True
b) False
Answer
Answer: b [Reason:] Generally, when linking to a static MySQL C client library, the client library and the client application must use the same compiler option when it is required to link the C runtime.
9. What can be used in place of MYSQL_SERVER_VERSION?
a) LIBMYSQL_VERSION
b) LIBMYSQL_VERSION_ID
c) MYSQL_VERSION_ID
d) MYSQL_ID
Answer
Answer: a [Reason:] The macros ‘LIBMYSQL_VERSION’ and ‘LIBMYSQL_VERSION_ID’ have the same values as ‘MYSQL_SERVER_VERSION’ and ‘MYSQL_VERSION_ID’ and the two sets of macros can be used in place of each other.
10. What can be used interchangeably with MYSQL_VERSION_ID?
a) LIBMYSQL_VERSION
b) LIBMYSQL_VERSION_ID
c) MYSQL_VERSION_ID
d) MYSQL_ID
Answer
Answer: b [Reason:] The ‘LIBMYSQL_VERSION’ and ‘LIBMYSQL_VERSION_ID’ macros have the same values as ‘MYSQL_SERVER_VERSION’ and ‘MYSQL_VERSION_ID’ and the two sets of macros can be used interchangeably.
Database MCQ Set 5
1. Which statement is used to remove indexes on tables?
a) DROP INDEX
b) DELETE INDEX
c) REMOVE INDEX
d) FLUSH INDEX
Answer
Answer: a [Reason:] MySQL provides statements to change the structure of tables. To add or remove the indexes on the existing database tables, the ‘CREATE INDEX’ and ‘DROP INDEX’ tables are used.
2. Which storage engine is not available in MySQL 5.0?
a) InnoDB
b) ARCHIVE
c) Falcon
d) NDB
Answer
Answer: c [Reason:] All of the storage engines given are available in MySQL versions 5.0 and above, except ‘Falcon’. MySQL supports many storage engines or table handlers that have a specific set of properties.
3. The default storage engine used is ______
a) EXAMPLE
b) ARCHIVE
c) MyISAM
d) NDB
Answer
Answer: c [Reason:] MySQL comes with multiple storage engines. The default storage engine used is ‘MyISAM’. ‘EXAMPLE’ is the stub storage engine, NDB is the storage engine for MySQL Cluster.
4. What is the name of the format file for a table named my_tbl?
a) my_tbl.fmt
b) my_tbl.frm
c) my_tbl.fmr
d) my_tbl.ftm
Answer
Answer: b [Reason:] Every time a table is created in MySQL, it creates a disk file containing the format of the table. It has two components, namely, base name (here, ‘my_tbl’) and an extension (here, ‘frm’).
5. In the CREATE TABLE statement, the engine name specified is case sensitive.
a) True
b) False
Answer
Answer: b [Reason:] Suppose a table is created using the following statement: ‘CREATE TABLE my_tbl (…..) ENGINE = InnoDB;’. The engine name ‘InnoDB’ used is always case insensitive.
6. Which keyword is used to create a table as a temporary copy of itself?
a) TEMP
b) TEMPO
c) TEMPR
d) TEMPORARY
Answer
Answer: d [Reason:] The ‘CREATE TABLE’ clause has a very vast formal format, although its usage is extremely simple and intuitive. One of the many ways is to create a table as a temporary copy of itself.
7. Which storage engine enables to access tables from a MySQL server managed by another server?
a) InnoDB
b) EXAMPLE
c) MyISAM
d) FEDERATED
Answer
Answer: d [Reason:] There are many storage engines that are used in MySQL. By default, MyISAM is used as the storage engine unless specified otherwise. FEDERATED enables access to a foreign MySQL server.
8. It is possible to drop multiple tables in the same statement.
a) True
b) False
Answer
Answer: a [Reason:] To drop a table, all that is required is the table name. No additional information like table format or type is necessary. Multiple tables are dropped in the same DROP TABLE statement.
9. The default index type for MEMORY tables is ____
a) HASH
b) SPATIAL
c) FULLTEXT
d) UNIQUE
Answer
Answer: a [Reason:] The ‘HASH’ index is the default index type for ‘MEMORY’ tables. ‘SPATIAL’ can only be used with ‘MyISAM’ tables for the spatial data types. ‘FULLTEXT’ is used for performing full-text searches.
10. The statement to change the table name is ____
a) CHANGE
b) CHANGENAME
c) CHANGENM
d) RENAME
Answer
Answer: d [Reason:] To change the name of a table, the keyword ‘RENAME’ can be used. It can be used with the ‘ALTER TABLE’ clause. It can also be used as a statement in itself like ‘RENAME TABLE old_tbl new_tbl’.
