Database MCQ Set 1
1. The recovery of data changes made since a given point in time is called _______
a) decremental
b) incremental
c) close
d) clear
Answer
Answer: b [Reason:] Incremental recovery refers to recovery of data changes made since a given point in time. It is performed after restoring a full backup that brings the server to its state as of the time the backup was made.
2. What performs a key distribution analysis and stores the distribution for the named table or tables?
a) ANALYZE TABLE
b) CHECK TABLE
c) CHECKSUM TABLE
d) OPTIMIZE TABLE
Answer
Answer: a [Reason:] ‘ANALYZE TABLE’ performs a key distribution analysis and stores the distribution for the named table or tables. For the MyISAM tables, this statement is equivalent to using ‘myisamchk –analyze’.
3. Which of the following columns is not returned by ‘OPTIMIZE TABLE’?
a) Table
b) Op
c) Msg_type
d) Msg_txt
Answer
Answer: d [Reason:] ‘OPTIMIZE TABLE’ table catches and throws any errors that occur while copying table statistics from the old file to the newly created file. It returns a result set of columns.
4. REPAIR TABLE does not work for _______
a) MyISAM
b) ARCHIVE
c) InnoDB
d) CSV
Answer
Answer: c [Reason:] ‘REPAIR TABLE’ works for MyISAM, ARCHIVE, and CSV tables. For MyISAM tables, it has the same effect as ‘myisamchk –recover’ tbl_name by default. This statement does not work with views.
5. The binary protocol is easy to use.
a) True
b) False
Answer
Answer: b [Reason:] The binary protocol is more difficult to use because more setup is necessary for transmitting and receiving data values. The binary protocol does not support all statements like ‘USE’.
6. 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.
7. What is a 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.
8. The line which 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.
9. CGI.pm also supports an object oriented style of use.
a) True
b) False
Answer
Answer: a [Reason:] ‘CGI.pm’ supports an object oriented style of use which allows to invoke its functions without importing the names. To do this, a use statement is included and a CGI object is created.
10. There can be more than one column per table with the AUTO_INCREMENT attribute.
a) True
b) False
Answer
Answer: b [Reason:] In MySQL, there can only be one column per table with the AUTO_INCREMENT attribute. It should also have an integer data type. The AUTO_INCREMENT is also allowed for floating point types.
Database MCQ Set 2
1. Symlink is an applicable relocation method for ________
a) entire data directory
b) individual database tables
c) server PID file
d) log files
Answer
Answer: b [Reason:] Symlink is an applicable relocation method for the individual database tables. However, the startup option is better suited relocation method for server PID file and the log files.
2. For which of these is the startup option best suited?
a) entire data directory
b) individual database tables
c) server PID file
d) individual database directories
Answer
Answer: c [Reason:] The startup option is better suited relocation method for server PID file and the log files, while symlink is an applicable relocation method for the individual database tables.
3. Before relocation of database, the server should be stopped if running.
a) True
b) False
Answer
Answer: a [Reason:] The server always looks for the database directories in the data directory. The only way to relocate a database is by the symlink method. The server should be stopped if running.
4. When relocating an individual table, the table to be relocated should be ________
a) MyISAM
b) InnoDB
c) TRANSACTION
d) ENGINE
Answer
Answer: a [Reason:] The relocation of an individual table is supported only under certain limited circumstances. When relocating an individual table, the table to be relocated should be a MyISAM table.
5. What is the special database that always exists after setting up MySQL in a computer?
a) sampdb
b) mysql
c) information_schema
d) readme_db
Answer
Answer: c [Reason:] After installation of MySQL, ‘information_schema’ is the special database that always exists. ‘mysql’ can be seen depending on access rights. It holds the grant tables. ‘sampdb’ and ‘readme_db’ do not exist by default.
6. What is InnoDB in the following code?
CREATE TABLE student ( name CHAR(30), student_id INT, PRIMARY KEY (student_id) ) ENGINE = InnoDB;
a) database name
b) table name
c) reference engine
d) storage engine
Answer
Answer: d [Reason:] ‘InnoDB’ is the name of the ‘storage engine’ for the above table. The ‘ENGINE’ clause is used to specify the name of the storage engine that MySQL should use to handle the table being created. MySQL has several storage engines with its own properties.
7. What is the table name in the following?
INSERT INTO student VALUES('Kyle','M',NULL);
a) student
b) VALUES
c) Kyle
d) M
Answer
Answer: a [Reason:] The ‘INSERT INTO’ clause here inserts a row in the table named ‘student’. The table has three fields. The first field or attribute value in the row/tuple is ‘Kyle’. The second attribute value is ‘M’ and the last attribute is set to NULL.
8. In which file are the statements entered in ‘mysql’ saved?
a) .mysql_queries
b) .queries
c) .mysql_history
d) .history
Answer
Answer: c [Reason:] Statements entered in ‘mysql’ are stored in the file named ‘.mysql_history’. This file is located in the home directory itself. The SQL statements can be directly pasted into this file.
9. Mysql cannot be used to execute script files.
a) True
b) False
Answer
Answer: b [Reason:] Mysql is capable of reading input from a file in batch mode. This is also known as the non-interactive mode. A lot of typing and time can be saved when commands are stored in a file and executed from a file.
10. Which command is used to make a script file ‘run_me.sh’ executable?
a) chmod +e run_me.sh
b) chmod +a run_me.sh
c) chmod +y run_me.sh
d) chmod +x run_me.sh
Answer
Answer: d [Reason:] The command name ‘chmod’ stands for ‘change mode’. It is used to define the way a file can be accessed. To make the script file ‘run_me.sh’ executable, the chmod command is used.
Database MCQ Set 3
1. OS imposes per process limits on the number of open file descriptors.
a) True
b) False
Answer
Answer: a [Reason:] OS imposes per process limits on the number of open file descriptors. If the system makes it difficult to raise the limit, running multiple instances of the server binary is done.
2. The option that supplies the pathname to root directory of MySQL installation is _______
a) –basedir
b) –datadir
c) –port
d) –socket
Answer
Answer: a [Reason:] The option ‘–basedir’ has the purpose to specify the pathname to the root directory of the MySQL installation. The pathname to the data directory is specified by the option ‘–datadir’.
3. The log enabled by –relay-log-index is _______
a) relay log index
b) relay log
c) binary log
d) error log
Answer
Answer: a [Reason:] The system variable ‘time_zone’ represents the default time zone of the MySQL server. By default, this variable is set to ‘SYSTEM’ which means to use the system_time_zone setting.
4. How many options can be used to control LOCAL capability at runtime?
a) 0
b) 1
c) 2
d) 3
Answer
Answer: c [Reason:] At runtime, the server can be started with the ‘–local-infile’ or ‘–skip-local-infile’ options to enable or disable ‘LOCAL’ capability on the server side. It can be enabled at build time too.
5. What is the storage size in bytes required for the MEDIUMINT datatype?
a) 1
b) 2
c) 3
d) 4
Answer
Answer: c [Reason:] The numeric datatypes used in MySQL vary over ranges. There are a variety of INT types in the type specification. TINYINT requires 1, SMALLINT requires 2 bytes and MEDIUMINT requires 3.
6. What is the size of the BIT type?
a) 1
b) 2
c) 3
d) variable
Answer
Answer: d [Reason:] All the numeric datatypes used in MySQL vary over the fields. However the maximum size is fixed. Unlike the other numeric types the size of the BIT datatype depends on the length of the field.
7. What is the synonym for REAL?
a) FLOAT
b) INT
c) DOUBLE
d) BIT
Answer
Answer: c [Reason:] The REAL datatype, by default, is for the DOUBLE type. Floating point types can be defined as UNSIGNED. This eliminates the negative end of the type ranges. MySQL has a variety of default types.
8. Execution on a time activated basis according to a schedule is done by _______
a) Stored program
b) Events
c) Triggers
d) Stored procedures
Answer
Answer: b [Reason:] In MySQL, the events execute on a time activated basis according to a schedule. Triggers are defined to execute when the table is modified via INSERT, DELETE or UPDATE statements.
9. Stored programs degrade database security.
a) True
b) False
Answer
Answer: b [Reason:] Stored programs improve database security because controlled access can be enabled to sensitive data by appropriate selection of the privileges a program has when it executes.
10. The character that the mysql client program recognizes as a statement delimiter is ________
a) :
b) .
c) ;
d) ,
Answer
Answer: c [Reason:] By default, mysql itself recognizes the semicolon as a statement delimiter, so the delimiter must be redefined temporarily to cause mysql to pass the entire stored program definition to the server.
Database MCQ Set 4
1. The number of files that each end of a client/server connection including SSL support use to set up secure communications is ____
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] With a MySQL installation that includes SSL support the server and its clients can communicate securely. Each end of a connection uses three files to set up secure communications.
2. What is the TLS protocol?
a) transparent layer security
b) transport layer security
c) transparent level security
d) transport level security
Answer
Answer: b [Reason:] MySQL supports encrypted connections between clients and the server using the TLS protocol. It is also referred to as SSL but MySQL does not actually use the SSL protocol for encrypted connections.
3. Which grant table scope columns is case insensitive?
a) Host
b) User
c) Password
d) Db
Answer
Answer: a [Reason:] ‘Db’ and ‘Table_name’ values are always treated as case sensitive even though treatment of database and table names in SQL statements depends on the filesystem case sensitivity of the host.
4. The variable that checks for availability of SSL support is ______
a) have_ssl
b) has_ssl
c) avail_ssl
d) ssl_avail
Answer
Answer: a [Reason:] After the SSL-capable server is started, to verify that it supports SSL, connection is established with ‘mysql’ and the following query is issued: SHOW VARIABLES LIKE ‘have_ssl’.
5. MySQL does provides a date type that has an optional time part.
a) True
b) False
Answer
Answer: a [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.
6. ‘fetchrow_hashref()’ returns reference to hash of row values keyed by what?
a) row name
b) column name
c) table name
d) database name
Answer
Answer: b [Reason:] The function ‘fetchrow_hashref()’ returns reference to hash of row values, keyed by the column name. The function ‘fetchrow_arrayref()’ returns a reference to an array of row values.
7. The function that returns a reference to an array of row values is ________
a) fetchrow_array()
b) fetchrow_arrayref()
c) fetch()
d) fetchrow_hashref()
Answer
Answer: b [Reason:] ‘fetchrow_arrayref()’ returns a reference to an array of row values. The function ‘fetchrow_hashref()’ returns reference to hash of row values. ‘fetch()’ is the same as fetchrow_arrayref().
8. The MySQL server is 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.
9. 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.
10. The log in which data changes received from a replication master server are written is _______
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.
Database MCQ Set 5
1. The superuser account of the grant tables in the mysql database is called ________
a) super
b) prime
c) root
d) leaf
Answer
Answer: c [Reason:] The root accounts are superuser accounts intended for administrative purposes. The root accounts have the privileges and to delete all the databases and shutting down the server.
2. Which script initializes the data directory during installation?
a) mysql_install_db
b) mysql_install_dbm
c) mysql_init_db
d) mysql_init_dbm
Answer
Answer: a [Reason:] The data directory is initialized during the installation by ‘mysql_install_db’. If MySQL is installed on Linux from RPM packages then ‘mysql_install_db’ is run automatically.
3. Anonymous accounts have user name ________
a) root
b) blank
c) super
d) prime
Answer
Answer: b [Reason:] Anonymous accounts have user name left blank. The main benefit of removing the anonymous user accounts is it significantly simplifies the task of setting up non-anonymous accounts.
4. For REVOKE statements, the server automatically rereads the grant tables.
a) True
b) False
Answer
Answer: a [Reason:] For the ‘REVOKE’ and ‘DROP USER’ statements, the server automatically re-reads the grant tables and no FLUSH PRIVILEGES statements are needed. Hence the grant tables are re-read.
5. What does the default case sensitivity of database and table names depend on?
a) SQL server
b) Server SQL mode
c) Operating system of machine
d) Does not depend on anything
Answer
Answer: c [Reason:] The default case sensitivity imposes a dependency on the operating system of the machine on which the MySQL server is running. Windows does not treat database and table names as case sensitive unlike Unix.
6. The case sensitive among these is ________
a) Stored function name
b) Stored procedure name
c) Trigger name
d) Event name
Answer
Answer: c [Reason:] The stored functions and stored procedure names in MySQL are not case sensitive. Event names are also not case sensitive. Unlike the standard SQL, the trigger names in MySQL is case sensitive.
7. The variable used to set table alias names as non case sensitive is ________
a) lower_case_table_names
b) lower_case_all
c) lower_case_alias
d) lower_case_aliases
Answer
Answer: a [Reason:] In MySQL, by default the alias names are case sensitive. An alias can hence be specified in any letter case, upper, lower or mixed. If the variable ‘lower_case_table_names’ is non zero, the alias names of tables are not case sensitive.
8. The clause that can be used to sort string values according to a specific collation is ________
a) SORT
b) GROUP
c) FILTER
d) COLLATE
Answer
Answer: d [Reason:] The ‘COLLATE’ operator can be used to sort the string values according to a specific collation. For example, ‘SELECT col FROM tbl ORDER BY col COLLATE latin1_swedish_ci’ sorts by swedish collation.
9. The statement used to find out which character sets are available is ________
a) SHOW CHARACTER SET
b) SHOW COLLATION
c) SHOW CHARACTER SETS
d) SHOW COLLATIONS
Answer
Answer: a [Reason:] It is simple to determine the character sets and collations that are available in MySQL. ‘SHOW CHARACTER SET’ shows the character sets while ‘SHOW COLLATION’ shows the collations.
10. The collations this statement lists are ________
SHOW COLLATION LIKE 'utf8%'
a) names beginning with utf8
b) names ending with utf8
c) names containing utf8% anywhere
d) names ending in utf8%
Answer
Answer: a [Reason:] The character set ‘utf8’ is used for the Unicode character set 8. The ‘LIKE’ keyword does the job of narrowing the search space to refer to only those names that begin with ‘utf8’.
