Database MCQ Set 1
1. How is the binary log rotated?
a) FLUSH LOGS
b) ROTATE LOGS
c) FLUSH BINLOG
d) ROTATE BINLOG
Answer
Answer: a [Reason:] MySQL supports incremental backups. The server should be started with the ‘–log-bin’ option to enable binary logging. The binary log files provide information to replicate changes to the database.
2. How is a delimited-text data file reloaded?
a) mysqlexport
b) mysqlimport
c) mysqlexpand
d) mysqltransfer
Answer
Answer: b [Reason:] In order to reload a delimited-text data file, use ‘LOAD DATA INFILE’ or ‘mysqlimport’. MySQL supports the incremental backups when the server is started with the ‘–log-bin’ option.
3. Which program performs logical backups?
a) mysqlimport
b) mysqldump
c) myslqpit
d) mysqllogic
Answer
Answer: b [Reason:] The ‘mysqldump’ performs logical backups. It produces a set of SQL statements that are executed to reproduce the original database object definitions. It dumps one or more MySQL databases for backup.
4. The option that suppresses output unless there are errors in the table is _______
a) –silent
b) –wild
c) –suppress
d) –noout
Answer
Answer: a [Reason:] In MySQL, the ‘–silent’ option suppresses the output unless there are errors in the tables. The ‘cron’ jobs typically generate a mail message if a job produces any output at all.
5. What is the return value from operations returning a row count?
a) $rc
b) $rv
c) $rows
d) $ary
Answer
Answer: c [Reason:] The Perl Non-handle variable ‘$ary’ is an array or list representing a row of values returned by a query. ‘$rc’ returns code from operations that return true or false. ‘$rv’ returns value from operations that return an integer. ‘$rows’ returns value from operations that return a row count.
6. Functions are called and pointers to structures are used while using DBI API.
a) True
b) False
Answer
Answer: a [Reason:] While using the DBI API functions are called and pointers to structures are used. The functions are called “methods,” pointers are called “references,” pointer variables are called “handles”.
7. The mode of search in which the search string is parsed into words and the search looks for rows is ____
a) Boolean mode
b) Natural language
c) Query expansion
d) Cross mode
Answer
Answer: b [Reason:] In MySQL, a full text search capability is provided, which enables to look for words or phrases without using pattern-matching operations. There are three kinds of full text searches.
8. 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.
9. 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.
10. The place where the server stores its databases and status files is called 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.
Database MCQ Set 2
1. The grant table that stores users who can connect to the server and their global privileges is ________
a) user
b) db
c) tables_priv
d) procs_priv
Answer
Answer: a [Reason:] The grant table ‘user’ stores the users who can connect to the server and their global privileges. ‘db’ has the database privileges records. ‘tables_priv’ has table privileges.
2. The stored-routine privileges are contained in ________
a) user
b) db
c) tables_priv
d) procs_priv
Answer
Answer: d [Reason:] ‘procs_priv’ stores stored-routine privileges. The grant table ‘user’ stores the users who can connect to the server and their global privileges. ‘db’ has the database privileges records.
3. The statement used to obtain access privileges to an account is ________
a) OBTAIN
b) GET
c) GRANT
d) NEED
Answer
Answer: c [Reason:] If the named account exists, ‘GRANT’ modifies its privileges. If the account does not exist, ‘GRANT’ creates it with the given privileges. It is used to obtain the access privileges.
4. MySQL does not support the shared-memory connections on Windows.
a) True
b) False
Answer
Answer: b [Reason:] MySQL supports shared memory connections on Windows. This capability is disabled by default. In order to enable it, the server is started with the ‘–shared-memory’ option.
5. Usage of aggregates in WHERE clause is disallowed.
a) True
b) False
Answer
Answer: a [Reason:] The usage of aggregates inside ‘WHERE’ clauses is not allowed. For example, the following statement will not work : ‘SELECT * FROM my_table WHERE attribute_name = MAX(attribute_name)’, because the MAX value is not known yet.
6. The operators used when a subquery returns multiple rows to be evaluated in comparison to the outer query are _______
a) IN and NOT IN
b) EXISTS and NOT EXISTS
c) OUTER JOIN and INNER JOIN
d) LEFT JOIN and RIGHT JOIN
Answer
Answer: a [Reason:] When there is a need to evaluate multiple rows in comparison to the outer query, the ‘IN’ and ‘NOT IN’ operators are used. They are used for testing whether a comparison value is present in a set of values.
7. The ALL subquery performs the operation _______
a) row
b) column
c) table
d) database
Answer
Answer: b [Reason:] The operators ‘ALL’ and ‘ANY’ are used to perform operations on columns. They are used in conjunction with a comparison operator in order to test the result of a column subquery.
8. 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.
9. 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.
10. 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.
Database MCQ Set 3
1. Which program copies the databases from one server to another?
a) mysqldbcopy
b) mysqlcopydb
c) mysqlflushdb
d) mysqldbflush
Answer
Answer: a [Reason:] The utility program ‘mysqldbcopy’ is capable of copying databases from one server to another server. It can also prepare copies to make transfers on the same servers. This can be done simply by running the program.
2. To use ‘mysqldbcopy’ which privileges are required on the source server?
a) CREATE
b) INSERT
c) UPDATE
d) SELECT
Answer
Answer: d [Reason:] To use the utility program ‘mysqldbcopy’, the user must have SELECT privileges for the database(s) on the source server and have CREATE, INSERT, UPDATE on the destination server.
3. The program that performs logical backups is _______
a) mysqlimport
b) mysqldump
c) myslqpit
d) mysqllogic
Answer
Answer: b [Reason:] The ‘mysqldump’ performs logical backups. It produces a set of SQL statements that are executed to reproduce the original database object definitions. It dumps one or more MySQL databases for backup.
4. In ‘mysqldump’ which option is used 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. Which function returns reference to hash of row values?
a) fetchrow_array()
b) fetchrow_arrayref()
c) fetch()
d) fetchrow_hashref()
Answer
Answer: d [Reason:] The function ‘fetchrow_hashref()’ returns reference to hash of row values. ‘fetchrow_arrayref()’ returns a reference to an array of row values. ‘fetch()’ is the same as fetchrow_arrayref().
6. The function ‘fetchrow_hashref()’ returns reference to hash of row values keyed by ________
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. Which function returns reference to array of row values?
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 poorly configurable.
a) True
b) False
Answer
Answer: b [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 can be easily run on the same machine.
a) True
b) False
Answer
Answer: b [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. Which is the log in which data changes received from a replication master server are written?
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 4
1. To perform a delete on a single-table, how is the name of a column qualified?
a) qualification not necessary
b) column name
c) table name
d) database name
Answer
Answer: a [Reason:] In MySQL, performing the operations ‘UPDATE’ and ‘DELETE’ on a single-table does not require qualifying the column name of the table in the database with the table name of the database.
2. What is xyz in the following statement?
DELETE FROM xyz WHERE abc = 5;
a) column name
b) table name
c) row name
d) database name
Answer
Answer: b [Reason:] The operation being performed in the statement is the ‘DELETE’. The table name is ‘xyz’ and column name is ‘abc’. When this statement is executed, rows having abc value equal to 5 get deleted.
3. A multiple-table delete can apply any join.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, a multiple table ‘DELETE’ operation can be performed on the tables combined using any kind of ‘JOIN’ operation. The syntax also allows for deleting rows from multiple tables at once.
4. What is abc in the following statement?
DELETE FROM xyz WHERE abc = 5;
a) column name
b) table name
c) row name
d) database name
Answer
Answer: a [Reason:] Here, the operation that is being performed in the statement is the ‘DELETE’ operation. The table name is ‘xyz’ and column name is ‘abc’. The rows having abc value equal to 5 get deleted.
5. What is x in the following statement?
DELETE FROM x USING x LEFT JOIN y ON x.col = y.col;
a) column name
b) table name
c) server name
d) database name
Answer
Answer: b [Reason:] The ‘DELETE’ operation is being performed in the statement. The table names are ‘x’ and ‘y’. The column name is ‘col’. The rows from left join of x and y get deleted according to the condition given.
6. Qualifying the name of column with the table name is not necessary in single-table updates.
a) True
b) False
Answer
Answer: a [Reason:] When it comes to single table ‘UPDATE’ and ‘DELETE’ operations, the qualification of a column name with the table name is not necessary. So the table name qualifier can be omitted.
7. What is the value of val2?
UPDATE t SET val1 = val1 + 2, val2 = val1;
a) previous val1
b) updated val1
c) unchanged
d) val1 + 1
Answer
Answer: b [Reason:] The second assignment here (val2 = val1) sets the value of val2 to the updated val1, that is, previous val1 incremented by two. ‘t’ is the name of the table here. ‘val1’ and ‘val2’ are columns.
8. UPDATE statement is a DML statement. What does DML stand for?
a) Data Manipulation Language
b) Data Manipulation Level
c) Data Markup Language
d) Data Markup Level
Answer
Answer: a [Reason:] The ‘UPDATE’ statement in MySQL is a ‘Data Manipulation Language’ statement. It performs edits on real tables present or loaded directly from the stored database. It is used in similar lines with ‘DELETE’.
9. Which keyword in the UPDATE statement is used to assign values to columns?
a) ASSIGN
b) SET
c) MARK
d) GET
Answer
Answer: b [Reason:] The ‘UPDATE’ statement in MySQL has its own syntax. The ‘SET’ keyword is followed by an assignment list which indicates the set of columns whose value needs to be updated with a specified value.
10. Which keyword is used to delete all the rows from the table?
a) TRUNCATE
b) REMOVE
c) DELETE ALL
d) CLEAR
Answer
Answer: a [Reason:] The ‘TRUNCATE’ keyword in MySQL is used to delete all the rows from the table and also free the space containing the table. Its syntax is: TRUNCATE TABLE my_table. This deletes all rows from my_table.
Database MCQ Set 5
1. Which of these is not an exact numeric type?
a) SMALLINT
b) DECIMAL
c) NUMERIC
d) REAL
Answer
Answer: d [Reason:] MySQL supports all the standard SQL numeric data types. These are the exact numeric data types like INTEGER, SMALLINT, DECIMAL and NUMERIC and the approximate numeric data types like FLOAT, REAL and DOUBLE PRECISION.
2. How many of the following do not support ‘BIT’ data type?
MyISAM, MEMORY, NDB
a) 0
b) 1
c) 2
d) 3
Answer
Answer: a [Reason:] The BIT data type stores the bit values. It is supported for the MyISAM, MEMORY, InnoDB and NDB tables. MySQL supports all the standard SQL numeric data types, both the exact and approximate.
3. The statement that suspends the replication related activity of the slave server is _______
a) RESUME SLAVE
b) START SLAVE
c) STOP SLAVE
d) GET SLAVE
Answer
Answer: c [Reason:] The ‘STOP SLAVE’ and ‘START SLAVE’ statements suspend and resume the replication-related activity of a slave server. These statements are useful for telling the slave to be quiescent.
4. The minimum value stored by signed TINYINT is _______
a) -256
b) -128
c) 0
d) 128
Answer
Answer: b [Reason:] MySQL supports the SQL standard integer types INTEGER, or INT, and SMALLINT. As an extension to this standard, MySQL also supports the integer types TINYINT, MEDIUMINT and BIGINT.
5. MySQL uses security based on ACL. What does it stand for?
a) Access Control Language
b) Access Control Lists
c) Automatic Control Lists
d) Automatic Control Language
Answer
Answer: b [Reason:] MySQL uses security based on Access Control Lists (ACLs) for all connections, queries and other operations that users can attempt to perform. There is support for SSL encrypted connections.
6. On Unix, ‘mysql’ client writes a record of executed statements to a history file.
a) True
b) False
Answer
Answer: a [Reason:] On Unix the mysql client writes a record of the executed statements to a history file. By default this file is named as ‘.mysql_history’. It is created in the home directory.
7. A password applies locally to an account.
a) True
b) False
Answer
Answer: b [Reason:] In the MySQL privilege system, a password applies globally to an account. A password cannot be explicitly associated with a specific object such as a database, table, or routine.
8. If $a represents an array with numeric indices in PHP, the first element accessed by ________
a) $a[1].
b) $a[0].
c) $a.1
d) $a.0
Answer
Answer: b [Reason:] If $a represents an array with numeric indices, its elements are accessed as $x[0], $x[1], and so on. In general terms, each element at the position i is accessed and used as $a[i – 1].
9. Arrays can have associative indices in PHP.
a) True
b) False
Answer
Answer: a [Reason:] The PHP arrays can even have both numeric and associative elements. For example, $x[1] and $x[“large”] can both be the elements of the same array. $x can be an object, whose elements are accessed by the arrow ‘->’ operated.
10. The operator used in PHP to access property of an object is ____
a) .
b) *
c) ->
d) @
Answer
Answer: c [Reason:] If $a represents an object, the properties it has can be accessed as $a->property-name. For instance, $a->white, $a->black, $a->blue, $a->red can be the properties of $a and accessed in this way.
