Database MCQ Set 1
1. Each database has a data directory located under database directory.
a) True
b) False
Answer
Answer: b [Reason:] In MySQL, each database has a database directory that is located under the data directory. The tables, views and triggers within a database correspond to files in the database directory.
2. In the database directory of the database each view and trigger object is associated with how many files?
a) 0
b) 1
c) 2
d) 3
Answer
Answer: b [Reason:] Each view and trigger object is associated with one file in the database directory of the database containing that object. A view consists of an .frm file that contains the view definition.
3. What is the maximum number of characters allowed for a database name to have?
a) 16
b) 32
c) 64
d) 128
Answer
Answer: c [Reason:] MySQL allows the database names and the table names to be maximum 64 characters long. The length of these names is also bound by the length allowed by the operating system on the machine.
4. The data directory subdirectory that corresponds to the nbdinfo database is ________
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. Which of these is preferred when stored procedures are not being used?
CLIENT_MULTI_STATEMENTS, mysql_set_server_option()
a) CLIENT_MULTI_STATEMENTS
b) mysql_set_server_option()
c) any of the two
d) neither of the two
Answer
Answer: c [Reason:] If the program does not use stored procedures anyone is suitable. If the program uses stored procedures and invokes a ‘CALL’ statement that returns a result set, the first method is better.
6. mysql_next_result() does not return.
a) True
b) False
Answer
Answer: b [Reason:] The function ‘mysql_next_result()’ returns a status and initiates retrieval of the next set if more results are available. The status is zero if more results are available and -1 if not.
7. The option that executes all SQL statements in a SQL script irrespective of the number of errors is ________
a) –ensure
b) –force
c) –violent
d) –run
Answer
Answer: b [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. What is the maximum collations a character set can have?
a) 0
b) 1
c) 2
d) more than 1
Answer
Answer: d [Reason:] The MySQL server allows simultaneous use of multiple character sets. A given character set is allowed to have one or more collations. It can be chosen according to the need of the database.
9. What is the synonym for CHARACTER SET?
a) CSET
b) CHSET
c) CHARSET
d) CHCSET
Answer
Answer: c [Reason:] ‘CHARACTER SET’ can be abbreviated into ‘CHARSET’ and can be used in the same contexts and statements where ‘CHARACTER SET’ is used. ‘charset’ is the server-supported character set.
10. What is the statement used to select a default database?
a) USE
b) CREATE
c) DROP
d) SCHEMA
Answer
Answer: a [Reason:] MySQL has the facility to use various statements specifically at the database level. For selecting a default database, the keyword or clause used is the ‘USE’ statement.
Database MCQ Set 2
1. Which system variable when set to 1, makes all temporary tables to get stored on disk rather than in memory?
a) basedir
b) back_log
c) big_tables
d) bind_address
Answer
Answer: c [Reason:] The variable ‘big_tables’ if set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower but the error ‘The table tbl_name is full’ does not occur for SELECT operations that require a large temporary table.
2. The permitted value type for the variable ‘character_set_client’ is _______
a) integer
b) float
c) double
d) string
Answer
Answer: d [Reason:] ‘character_set_client’ is the character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when client connects to server.
3. ‘character_set_database’ represents the character set used by the default database.
a) True
b) False
Answer
Answer: a [Reason:] ‘character_set_database’ is the character set used by the default database. The server sets the variable whenever the default database changes. If there is no default database the variable has the same value as ‘character_set_server’.
4. Which string function returns the index of the first occurrence of substring?
a) INSERT()
b) INSTR()
c) INSTRING()
d) INFSTR()
Answer
Answer: b [Reason:] The string funtion ‘INSTR()’ is used to return the index of the first occurrence of the substring. The function ‘INSERT()’ is used to insert a substring at the specified position up to the specified number of characters.
5. Which module links DBI to the web?
a) CGI.pn
b) CGI.pem
c) CGI.pm
d) CGI.po
Answer
Answer: c [Reason:] The CGI.pm module is used to write scripts that use the ‘Common Gateway Interface’ protocol. It defines how a web server communicates with other programs. It provides an easy link.
6. What does CGI stand for?
a) Computer Gateway Interface
b) Common Gateway Interface
c) Computer Generated Interface
d) Common Generated Interface
Answer
Answer: b [Reason:] The ‘CGI.pm’ module is so called because it helps write scripts that use the ‘Common Gateway Interface’ protocol which defines how a web server communicates with other programs.
7. The line that is written at the top of the script to write a Perl script using CGI.pm is _____
a) use this CGI
b) put CGI
c) use CGI
d) include CGI
Answer
Answer: c [Reason:] In order to write a Perl script that uses the ‘CGI.pm’ module, a ‘use CGI’ statement is included near the beginning of the script that imports the function names of the module.
8. What is the shebang line?
a) #!
b) !#
c) #$
d) $#
Answer
Answer: a [Reason:] The Perl scripts are text files, which can be ceated using any text editor. All Perl scripts generally begin with a #! (shebang) line. A script is a file containing a sequence of commands.
9. What is the Perl DBI?
a) database inheritance
b) database integrity
c) database interface
d) database isolation
Answer
Answer: c [Reason:] The Perl DBI is the Perl Database Interface. The work of the interface is to form a link between the application programming interface in C with all the other programming languages.
10. What is the default path to perl in Unix?
a) /usr/bin/perl
b) /usr/bin
c) /usr/perl
d) /usr/perl/bin
Answer
Answer: a [Reason:] All Perl scripts generally begin with a #! (shebang) line. A script is a file containing a sequence of commands. The Perl scripts are text files, which can be ceated using any text editor.
Database MCQ Set 3
1. Which mode exists at both global level and session-specific level?
a) sql_mode
b) key_buffer_size
c) server_mode
d) query_mode
Answer
Answer: a [Reason:] The system variable ‘sql_mode’ that indicates the default SQL mode is an example of a variable that exists at both the global and session levels. This mode affects several aspects of SQL statement processing.
2. Which system variable determines the number of rows from INSERT DELAYED statements that can be queued per table?
a) fast_queue_size
b) general_queue_size
c) slow_queue_size
d) delayed_queue_size
Answer
Answer: d [Reason:] The system variable ‘delayed_queue_size’ determines the number of rows from INSERT DELAYED statements that can be queued per table (for the storage engines that support DELAYED inserts).
3. The largest value to which the variable ‘max_allowed_packet’ can be set is ________
a) 1GB
b) 2GB
c) 4GB
d) 8GB
Answer
Answer: a [Reason:] The ‘max_allowed_packet’ stores the maximum size to which the buffer used for client communications can grow. The largest value to which ‘max_allowed_packet’ variable can be set is 1GB.
4. Increasing the value of which system variable enables mysqld to keep more tables open simultaneously?
a) table_cache
b) max_connect
c) delayed_queue_size
d) max_allowed_packet
Answer
Answer: a [Reason:] The ‘table_cache’ stores the size of the table cache. Increasing the value enables ‘mysqld’ to keep more tables open simultaneously by reducing the number of file open and close operations.
5. The following statement is valid.
SELECT abc, xyz FROM table1 UNION abc, def FROM table2;
a) True
b) False
Answer
Answer: a [Reason:] Even if the columns ‘xyz’ and ‘def’ have different data types, the results from these columns are placed into the column ‘xyz’. The data types can be determined from the values in the columns.
6. The keyword used with UNION that does not retain duplicate rows is _______
a) ALL
b) NARROW
c) STRICT
d) DISTINCT
Answer
Answer: d [Reason:] The keyword ‘DISTINCT’ used along with ‘UNION’ is synonymous with just the ‘UNION’ statement. It produces only the distinct rows from the combination of the two tables in the SELECT query.
7. The keyword used with UNION that retains duplicate rows is _____
a) ALL
b) NARROW
c) STRICT
d) DISTINCT
Answer
Answer: a [Reason:] The keyword ‘ALL’ used along with ‘UNION’ is not synonymous with just the ‘UNION’ statement. It produces the duplicate rows, if they exist, from the combination of the two tables in the SELECT query.
8. The search mode that uses natural language search as a subroutine is ______
a) Natural language
b) Boolean mode
c) Query expansion
d) Cross mode
Answer
Answer: c [Reason:] The query expansion search occurs in two phases. The natural language search is applied in the first phase. Query expansion search is one of the three modes of fulltext searching.
9. For what can the FULLTEXT indexes be created for?
a) MyISAM
b) InnoDB
c) MEMORY
d) TRANSITION
Answer
Answer: a [Reason:] Full-text searches are based on the FULLTEXT indexes. They can be created for MyISAM tables only. In MySQL, a full text search enables to look for words or phrases without using pattern-matching operations.
10. A FULLTEXT index can be created for multiple columns.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, a full-text search is based on the FULLTEXT indexes. A FULLTEXT index can be created both for a single column and for multiple columns. Searching is possible across columns.
Database MCQ Set 4
1. Which upgrade involves exporting existing data using mysqldump?
a) inplace
b) logical
c) illogical
d) system
Answer
Answer: b [Reason:] The in-place upgrade involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.
2. Which upgrade involves shutting down the old MySQL version and replacing the old binaries?
a) inplace
b) logical
c) illogical
d) system
Answer
Answer: a [Reason:] The logical upgrade involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version.
3. Slow shutdown can be performed by setting innodb_fast_shutdown to _______
a) 0
b) -1
c) 1
d) 2
Answer
Answer: a [Reason:] With a slow shutdown InnoDB performs a full purge and change buffer merge before shutting down. This ensures that data files are fully prepared in case of file format differences.
4. A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading.
a) True
b) False
Answer
Answer: a [Reason:] A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table can damage the table.
5. The command to see the warning messages is ________
a) DISPLAY WARNINGS
b) DISP WARNINGS
c) DISP WARNING
d) SHOW WARNINGS
Answer
Answer: d [Reason:] In MySQL, the default value conversions are reported as warnings for INSERT, REPLACE and UPDATE statements. The SHOW WARNINGS command is used after executing one of those statements to see the warning messages.
6. What is the SQL mode to check for divide by zero error?
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) ERROR_DIVIDE_BY_ZERO
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer
Answer: b [Reason:] To enable the check for divide by zero errors int all the storage engines, the SQL mode named ‘ERROR_FOR_DIVISION_BY_ZERO’ can be enabled. This is done by using SET sql_mode = ‘mode_name’.
7. The mode used to turn on strict mode and all of the additional restrictions is _______
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer
Answer: c [Reason:] The ‘TRADITIONAL’ mode is used to enable the strict mode and all of the additional restrictions. It is done by the command SET sql_mod = ‘TRADITIONAL’; The other SQL modes have other functions.
8. The number of values returned from a stored procedure can be ___
a) 0
b) 1
c) 2
d) 3
Answer
Answer: a [Reason:] In MySQL, unlike the stored functions, the stored procedures cannot return values. They can be used to perform calculations or produce the result sets passed back to the clients.
9. How would a stored function named PI() written in the database ‘sampdb’ be called?
a) PI()
b) sampdb.PI()
c) MySQL.PI()
d) db.PI()
Answer
Answer: b [Reason:] When a stored function is defined with the same name as a built in function, the function name should be qualified with the database name when it is invoked. The function ‘PI()’ is built in.
10. The privilege that must be given to the database to create a stored function or procedure is ______
a) CREATE ROUTINE
b) CREATE METHOD
c) CREATE FUNCTION
d) CREATE PROCEDURE
Answer
Answer: a [Reason:] The ‘CREATE ROUTINE’ privilege must be given for the database in order to create a stored function or procedure. Stored functions and procedures always belong to a particular database.
Database MCQ Set 5
1. User defined variables are session specific.
a) True
b) False
Answer
Answer: a [Reason:] The user defined variables are session specific. A user variable defined by one client cannot be seen or used by other clients. A user with access to the Performance Schema user_variables_by_thread table can see all user variables for all sessions is an exception.
2. Hexadecimal values assigned to user variables are treated as _______
a) strings
b) non binary strings
c) binary strings
d) integers
Answer
Answer: c [Reason:] The hexadcimal or bit values assigned to user variables are treated as binary strings. In order to assign a hexadecimal or bit value as a number to a user variable, it is used in numeric context.
3. The synonym for last_insert_id session variable is _______
a) insert_id
b) identity
c) sql_auto_is_null
d) sql_big_selects
Answer
Answer: b [Reason:] The session only system variable ‘identity’ is a synonym for the ‘last_insert_id’ session variable. Setting ‘last_insert_id’ specifies the value to be returned by the function ‘LAST_INSERT_ID()’.
4. What is the type of Audit_log_events system variable?
a) string
b) integer
c) float
d) double
Answer
Answer: b [Reason:] The system variable ‘Audit_log_events’ is of type integer. The variable scope if GLOBAL, that is, it can be viewed by issuing the statement ‘SHOW GLOBAL STATUS’, instead of ‘SESSION’.
5. Multiple statement execution is disabled 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.
6. The option that enables multiple-statement execution is ______
a) CLIENT_MULTI_STATEMENTS
b) CLIENT_STATEMENTS_MULTI
c) MULTI_STATEMENTS_CLIENT
d) MULTI_CLIENTS_STATEMENTS
Answer
Answer: a [Reason:] There are two ways to enable the multiple-statement execution. The first is to add the ‘CLIENT_MULTI_STATEMENTS’ option in the flags argument to ‘mysql_real_connect()’ at connect time.
7. What is preferred when stored procedures are not being used?
CLIENT_MULTI_STATEMENTS, mysql_set_server_option()
a) CLIENT_MULTI_STATEMENTS
b) mysql_set_server_option()
c) any of the two
d) neither of the two
Answer
Answer: c [Reason:] If the program does not use stored procedures anyone is suitable. If the program uses stored procedures and invokes a ‘CALL’ statement that returns a result set, the first method is better.
8. What is the general statement-issuing routine?
a) mysql_real_query()
b) mysql_query_real()
c) mysql_image_query()
d) mysql_query_image()
Answer
Answer: a [Reason:] The general statement-issuing routine is mysql_real_query(). The statement is provided as a counted string (a string plus a length). The length of the statement string must be kept track of.
9. The one that is more restrictive is ____
mysql_query(), mysql_real_query()
a) mysql_query()
b) mysql_real_query()
c) same
d) machine dependent
Answer
Answer: a [Reason:] The statement-issuing function other than ‘mysql_real_query()’ is ‘mysql_query()’. It is more restrictive in what it allows in the statement string although it is often easier to use.
10. What does mysql_query() return on failure?
a) 0
b) 1
c) -1
d) a non-zero
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.
