Database MCQ Set 1
1. For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by ________
a) –multiple-transaction
b) –single-transaction
c) –double-transaction
d) –no-transaction
Answer
Answer: b [Reason:] For InnoDB tables it is possible to perform an online backup that takes no locks on tables using the option ‘–single-transaction’ to ‘mysqldump’. The ‘mysqldump’ can make backups.
2. To reload a delimited text data file use ________
a) mysqldump
b) mysqld
c) mysqlimport
d) mysqlnaive
Answer
Answer: c [Reason:] A way to create text data files along with files containing ‘CREATE TABLE’ statements for the backed up tables is to use ‘mysqldump’ with –tab. To reload a delimited text data file ‘mysqlimport’ is used.
3. Replication enables data from one MySQL database server to be copied to one or more MySQL database servers.
a) True
b) False
Answer
Answer: a [Reason:] Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default.
4. SBR replication is ________
a) Statement based
b) Row based
c) Column based
d) Table based
Answer
Answer: a [Reason:] There are two main kinds of replication format: Statement Based Replication (SBR) replicates entire SQL statements and Row Based Replication (RBR) replicates only the changed rows.
5. The library file that contains various portability macros and definitions is ________
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer
Answer: b [Reason:] The ‘my_sys.h’ header file contains a variety of portability macros and definitions required for structures and functions. These structures and functions are used by the client library.
6. The header that should be included first is ________
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer
Answer: a [Reason:] The file ‘my_global.h’ takes care of including several other header files that are likely to be generally useful, like ‘stdio.h’. It also includes Windows compatibility information.
7. Which of the following use ‘NULL’ to indicate failure?
mysql_init(), mysql_real_connect()
a) 0
b) 1
c) 2
d) 3
Answer
Answer: c [Reason:] Both of the client library routines named ‘mysql_init()’ and ‘mysql_real_connect()’ return a pointer to the connection handler in order to indicate success and NULL to indicate failure.
8. Which of the following does not return a value?
mysql_close(), mysql_init, mysql_real_connect
a) 0
b) 1
c) 2
d) 3
Answer
Answer: b [Reason:] It is not necessary that every Application Programming Interface call would return a value. For example, the client routine named ‘mysql_close()’ returns void, that is, it does not return a value.
9. 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.
10. The most restrictive among the following 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.
Database MCQ Set 2
1. To perform consistency checks on a MyISAM table the statement is _______
a) CHECK TABLE
b) REPAIR TABLE
c) ANALYZE TABLE
d) OPTIMIZE TABLE
Answer
Answer: a [Reason:] In order to perform consistency checks or table repairs on a MyISAM table a ‘CHECK TABLE’ or ‘REPAIR TABLE’ statement is issued. The ‘mysqlcheck’ program can also be invoked.
2. The statement that performs key distribution analysis and stores the distribution for the named tables is _______
a) CHECK TABLE
b) ANALYZE TABLE
c) CHECKSUM TABLE
d) OPTIMIZE TABLE
Answer
Answer: b [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. The column not returned by ‘OPTIMIZE TABLE’ is _______
a) Table
b) Msg_txt
c) Op
d) Msg_type
Answer
Answer: b [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. For which table does ‘REPAIR TABLE’ not work?
a) MyISAM
b) ARCHIVE
c) CSV
d) InnoDB
Answer
Answer: d [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. 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.
6. 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.
7. Which of these is not optional?
SELECT select_list FROM table_list WHERE row_constraint GROUP BY grouping_columns;
a) select_list
b) table_list
c) row_constraint
d) grouping_columns
Answer
Answer: a [Reason:] Given above was a basic syntax of the SELECT statement. Everything in the syntax is optional except the ‘select_list’ option. All the others are free to be omitted, and will work fine.
8. The basic operation of PHP is to interpret a script.
a) True
b) False
Answer
Answer: a [Reason:] The elementary operation of PHP is interpreting a script in order to produce a web page. This is sent to a client. A PHP script generally contains a mix of HTML and executable code.
9. The PHP interpreter switches mode continuously.
a) True
b) False
Answer
Answer: a [Reason:] As soon as the PHP interpreter comes across a special opening tag it switches from ‘text copy’ mode to ‘PHP code’ mode. It starts interpreting the file as PHP code to be executed.
10. How is the output from PHP generated?
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.
Database MCQ Set 3
1. Which type of database management system is MySQL?
a) Object-oriented
b) Hierarchical
c) Relational
d) Network
Answer
Answer: c [Reason:] MySQL is a ‘relational’ DBMS. It is efficient at relating data in two different tables and joining information from them. Hierarchical and Network DBMS are based on parent-child relationships of records. Object-oriented DBMS use objects to represent models.
2. What is data in a MySQL database organized into?
a) Objects
b) Tables
c) Networks
d) File systems
Answer
Answer: b [Reason:] Since MySQL is an RDBMS, it’s data is organised in tables for establishing relationships. A table is a collection of rows and columns, where each row is a record and columns describe the feature of records.
3. MySQL is freely available and is open source.
a) True
b) False
Answer
Answer: a [Reason:] MySQL is free and open source. It’s source code is available for use and is freely downloadable. It includes the MySQL Server, the world’s most popular open source database, and MySQL Cluster, a real-time, open source transactional database.
4. What represents an ‘attribute’ in a relational database?
a) Table
b) Row
c) Column
d) Object
Answer
Answer: c [Reason:] Each column in a table represents a feature (attribute) of a record. Table stores the information for an entity whereas a row represents a record. Object has no relevance in an RDBMS.
5. What represents a ‘tuple’ in a relational database?
a) Table
b) Row
c) Column
d) Object
Answer
Answer: b [Reason:] Each row in a table represents a record. A tuple is a collection of attribute values that makes a record unique. A tuple is a unique entity whereas attribute values can be duplicate in the table.
6. How is communication established with MySQL?
a) SQL
b) Network calls
c) A programming language like C++
d) APIs
Answer
Answer: a [Reason:] SQL is the standard language for RDBMS systems like MySQL. SQL queries facilitate quick information retrieval from tables and other elementary operations required to maintain an RDBMS system.
7. What does ‘name’ represent in the following code snippet?
CREATE TABLE student ( name CHAR(30), roll_num INT, address CHAR(30), phone CHAR(12) );
a) A table
b) A row
c) A column
d) An object
Answer
Answer: c [Reason:] ‘name’, ‘roll_num’, ‘address’ and ‘phone’ are the attributes in the table ‘student’. The CREATE TABLE construct in SQL creates a table, assigns a name to it and its attributes, and specifies the type of fields used in the table.
8. Which is the MySQL instance responsible for data processing?
a) MySQL client
b) MySQL server
c) SQL
d) Server daemon program
Answer
Answer: b [Reason:] MySQL uses the client-server architecture. The MySQL server program runs on the machine where databases are stored. SQL is a query language used for querying the tables and information retrieval.
9. The MySQL server used in its client/server architecture is ___
a) mysqla
b) mysqlb
c) mysqlc
d) mysqld
Answer
Answer: d [Reason:] mysqld is the MySQL server program. It serves the incoming client requests by accessing the database. The others are not valid MySQL programs. MySQL implements a client/server architecture wherein mysqld is the server program.
10. In MySQL databases, the structure representing the organizational views of the entire databases is ______
a) Schema
b) View
c) Instance
d) Table
Answer
Answer: a [Reason:] The schema in a database gives a blueprint of the structure. A view is an object that can be generated with a query. A table is a collection of records. An instance is analogous to a class object.
Database MCQ Set 4
1. Case sensitivity in SQL statements does not vary for different operating systems of the machine on which the server is running.
a) True
b) False
Answer
Answer: b [Reason:] The case sensitivity rules in SQL statements are different for different statement elements, depend on what is being referred to and also on the machine’s operating system on which the server is running.
2. What is true about the following SQL statement?
SELECT * FROM table_1;
a) invalid
b) display contents of table_1
c) improper case being used
d) display only column names in table_1
Answer
Answer: b [Reason:] The keywords and function names are not case sensitive in SQL. They can be named in any letter case. So, the above statement is correct and legal. It retrieves information from the table ‘table_1’.
3. The default case sensitivity of database and table names depends 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.
4. Which of the following is case sensitive?
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.
5. Which variable is used to set table alias names as non-case sensitive?
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.
6. Which case does InnoDB store database names in?
a) lower
b) upper
c) mixed
d) random
Answer
Answer: a [Reason:] In InnoDB, all the database names as well as all the table names are stored in lowercase internally. There is no random case assignment to the database elements when the engine is set as InnoDB.
7. Regardless of whether a database or table name is case sensitive on the system, it
must be referred to using the same lettercase throughout a given query.
a) True
b) False
Answer
Answer: a [Reason:] A database or table name must be referred to using the same lettercase throughout a given query. This is regardless of the fact whether it is case sensitive on the system or not.
8. Which of the following is case sensitive in MySQL?
a) Event names
b) Logfile group names
c) Column names
d) Indexes
Answer
Answer: b [Reason:] Column names and indexes are not case sensitive on any platform and neither are column aliases. Unlike the standard SQL, the names of the log file groups are always case sensitive.
9. Which property determines whether a database object is case sensitive?
a) COLLATION
b) ATOMICITY
c) COLLABORATION
d) NORMALIZATION
Answer
Answer: a [Reason:] The way to determine whether a database object or a database is case sensitive or not is to check the ‘COLLATION’ property. The result would be either CI for insensitive or CS for sensitive.
10. What is AI in terms of database collation?
a) Accent Insensitive
b) Augment Insensitive
c) Articulate Insensitive
d) Addition Insensitive
Answer
Answer: a [Reason:] The collation in database can also be specified in the join or comparison conditions. The value ‘AI’ means ‘Accent Insensitive’. ‘CS’ is case sensitive and ‘CI’ is case insensitive.
Database MCQ Set 5
1. 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.
2. Unicode support is provided in MySQL.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, Unicode character set support is provided by the utf8 and ucs2 character sets. There are further additional character sets available as of MySQL version 6.0.4.
3. Which clause can be used to sort string values according to a specific collation?
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.
4. Which statement is used to find out which character sets are available?
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.
5. Which collations does this statement list?
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’.
6. Which statement is used to show the server’s current character set and collation settings?
a) SHOW CONSTANTS
b) SHOW CONSTRAINTS
c) SHOW VARIABLES
d) DISP VARIABLES
Answer
Answer: c [Reason:] The statement ‘SHOW VARIABLES LIKE ‘character_set_%” displays a table consisting of two columns, ‘Variable_name’ and ‘Value’. Replacing characer_set_% with collation_% shows the collation variables.
7. What does UTF stand for int utf8?
a) Universal Transformation Format
b) Unicode Transformation Format
c) Universal Transformation Formula
d) Unicode Transformation Formula
Answer
Answer: b [Reason:] In the utf8 character set in MySQL, the characters are represented in one, two or three bytes. ‘UTF’ stands for ‘Unicode Transformation Format’. Unicode support prior to MySQL 6.0 was different.
8. Prior to MySQL 6.0, utf8 was _____
a) 3 bytes
b) 4 bytes
c) 8 bytes
d) 9 bytes
Answer
Answer: a [Reason:] ‘UTF’ stands for ‘Unicode Transformation Format’. Unicode support prior to MySQL 6.0 was different from that post MySQL 6.0. The older utf8 was of three bytes.
9. Post MySQL 6.0, utf8 was _____
a) 3 bytes
b) 4 bytes
c) 5 bytes
d) 6 bytes
Answer
Answer: b [Reason:] Unicode support prior to MySQL 6.0 was different from that post MySQL 6.0. The older utf8 was of three bytes. The newer utf8 is now of four bytes. This is applicable to MySQL 6.0 and above.
10. What is generally used as 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.
