Database MCQ Set 1
1. Numbers prefixed with ‘0x’ are in base ____
a) 8
b) 16
c) 32
d) 64
Answer
Answer: b [Reason:] In MySQL, there are many numeric datatypes. They include integers and floating points. For example, 34, 12.3, -1.3E11. The numbers that are prefixed with ‘0x’ are in hexadecimal.
2. Hexadecimal numbers cannot be used in scientific notation.
a) True
b) False
Answer
Answer: a [Reason:] The scientific notation for real numbers is possible in MySQL. Approximate values are represented as floating point numbers in scientific notation. It consists of a mantissa and exponent.
3. The mantissa in -1.58E5 is ____
a) -1.58
b) 1.58
c) E
d) 5
Answer
Answer: a [Reason:] In MySQL, the scientific notation for real numbers is possible. Some values are represented as floating point numbers in scientific notation consisting of a mantissa and exponent.
4. The BIGINT precision is of bit ___
a) 32
b) 64
c) 128
d) 16
Answer
Answer: b [Reason:] In MySQL, for the expressions containing only exact values that are all integers, the evaluation uses BIGINT (64 – bit) precision. MySQL evaluates expressions using exact/approximate math.
5. The DECIMAL used for expressions containing only exact values with fractional part is of digit precision ___
a) 32
b) 64
c) 65
d) 16
Answer
Answer: c [Reason:] In MySQL, for the expressions containing only exact values and where one or more values have a fractional part, the DECIMAL arithmetic is used with digits of precision equal to 65.
6. If a string is to be converted to a number, it is converted into a _______
a) double precision floating point
b) single precision floating point
c) decimal integer
d) hexadecimal integer
Answer
Answer: a [Reason:] When there is a need to convert a string to a number to evaluate an expression, it is first converted to a double precision floating point value. Then, it can be used to do arithmetic.
7. The bit-field values are prefixed with ____
a) 0b
b) 0bit
c) bit-
d) ‘bit’
Answer
Answer: a [Reason:] In MySQL, the bit-field values can be either written as b’value’ or 0bvalue. Here, value is a sequence of digits zero or/and one. For example, b’1010′ and 0b1010 are decimal value 10.
8. A BIT value in a result set is displayed as a binary string.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, the BIT values in the result set are displayed as binary strings. In order to convert them to integers, either a zero is added or the ‘CAST()’ operator is used to cast them.
9. ‘2341’ is a ______
a) integer
b) floating point
c) hexadecimal
d) string
Answer
Answer: d [Reason:] In MySQL, the string values are written by enclosing them within quotes. Values like ‘Alexander’, ‘Kolkata, India’ and ‘2341’ are strings. ‘2341’ looks like an integer but is a string.
10. If ANSI_QUOTES is enabled, MySQL treats the double quotes as ____
a) identifier-quoting character
b) string-quoting character
c) hexadecimal
d) string
Answer
Answer: a [Reason:] The SQL standard specifies the single quotes so that statements are portable across database engines. If ANSI_QUOTES is enabled, MySQL treats the double quotes as identifier-quoting character.
Database MCQ Set 2
1. The program that copies the databases from one server to another is ______
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. Which privileges are required on the source server to use mysqldbcopy?
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. 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. 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. The function that returns reference to hash of row values is ______
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 best datatype for a column that is expected to store values up to 2 million 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.
7. Which type stores 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.
8. The security context when a user creates a stored program that accesses sensitive data but forgets that other people who can invoke the object have the same access is ____
a) bad
b) good
c) fare
d) illegal
Answer
Answer: a [Reason:] The security context is bad if a user creates a stored program that accesses sensitive data but forgets that other people who can invoke the object have the same access to that data as its definer.
9. Triggers and events are invoked automatically by the server.
a) True
b) False
Answer
Answer: a [Reason:] The triggers and events are invoked automatically by the server, so the concept of invoking user is not applied. Thus, they have no SQL SECURITY characteristic and always execute with definer privileges.
10. How is a stored procedure invoked?
a) INVOKE
b) SEE
c) CALL
d) RETURN
Answer
Answer: c [Reason:] In MySQL, a stored procedure is invoked using the CALL statement. A stored procedure does not have a return value but can modify its parameters. It also returns some result sets.
Database MCQ Set 3
1. Which of these has special automatic update behavior?
a) DATE
b) TIME
c) TIMESTAMP
d) YEAR
Answer
Answer: c [Reason:] The date and time types for representing temporal values are ‘DATE’, ‘TIME’, ‘DATETIME’, ‘TIMESTAMP’ and ‘YEAR’. Each temporal type has a range of valid values and a ‘zero’ value.
2. To disallow zero month or day parts in dates, the option used is _______
a) NO_ZERO_IN_DATE
b) NO_DATE_WITH_ZERO
c) ZERO_IN_DATE
d) DATE_WITH_ZERO
Answer
Answer: a [Reason:] MySQL permits to store the dates where the day or month and day are zero in a DATE or DATETIME column. This is helpful for applications that need to store birthdates for which the exact date is unknown.
3. The mode that does not remove trailing spaces when CHAR values are retrieved is _______
a) PAD_CHAR_TO_FULL_LENGTH
b) TO_FULL_LENGTH_CHAR_PAD
c) CHAR_PAD_TO_FULL_LENGTH
d) PAD_CHAR_TO_LENGTH
Answer
Answer: a [Reason:] When the CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the SQL mode ‘PAD_CHAR_TO_FULL_LENGTH’ is enabled.
4. MySQL automatically converts a date or time value to a number if the value is used in a numeric context.
a) True
b) False
Answer
Answer: a [Reason:] MySQL automatically converts a date or time value into a number if the value is used in a numeric context and vice versa. A ‘zero’ value can also be represented in MySQL.
5. The functions in Perl DBI called?
a) functions
b) procedures
c) methods
d) programs
Answer
Answer: c [Reason:] In 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’.
6. The variable used as a handle to an open file is ________
a) $dbh
b) $sth
c) $fh
d) $h
Answer
Answer: c [Reason:] The variable named ‘$fh’ is a handle to an open file. ‘$h’ is a generic handle and the meaning depends on context. ‘$dbh’ is a handle to a database object. ‘$sth’ is a handle to a query object.
7. Which variable is a handle to a database object?
a) $dbh
b) $sth
c) $fh
d) $h
Answer
Answer: a [Reason:] The variable named ‘$dbh’ is a handle to an open file. ‘$h’ is a generic handle and the meaning depends on context. ‘$dbh’ is a handle to a database object. ‘$sth’ is a handle to a query object.
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 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.
10. Which statement is 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 4
1. The expression 12 DIV 5 evaluates to ______
a) 2.4
b) 2
c) error
d) 0
Answer
Answer: b [Reason:] The ‘DIV’ operator in MySQL is used to perform the integer divisions. The operator ‘/’ performs the quotient of the operands. If result exceeds the 64-bit range, unpredicted results are shown.
2. If the PIPES_AS_CONCAT is disabled, ‘abc’ || ‘xyz’ results in ______
a) 1
b) 0
c) error
d) -1
Answer
Answer: b [Reason:] When the SQL mode PIPES_AS_CONCAT is disabled, the SQL standard ‘||’ operation for string concatenation is not valid in MySQL. Both operands are converted to zero. So the result is zero.
3. ‘abc’ || ‘xyz’, when PIPES_AS_CONCAT is enabled, results in ______
a) 0
b) 1
c) abcxyz
d) xyzabc
Answer
Answer: c [Reason:] If the SQL mode PIPES_AS_CONCAT has been enabled, the SQL standard ‘||’ operation for string concatenation becomes valid in MySQL. Both operands are concatenated to give ‘abcxyz’.
4. The expression ‘2 BETWEEN 2 AND 5’ results in ______
a) True
b) False
c) -1
d) 2
Answer
Answer: a [Reason:] The ‘BETWEEN ……. AND’ clause is used to return a boolean value, if the given operand value lies between the values specified by the ‘AND’ clause. The range endpoints are inclusive.
5. REGEXP takes collation into account.
a) True
b) False
Answer
Answer: b [Reason:] MySQL provides pattern matching based on the ‘REGEXP’ operator and regular expressions that are similar to those used in Unix programs, namely, grep, sed and vi. REGEXP does not take collation into account.
6. The expression ‘HI’ LIKE NULL results in _______
a) True
b) False
c) NULL
d) 0
Answer
Answer: c [Reason:] In MySQL, any pattern matching that is performed with the ‘NULL’ operand, fails. Expressions like: ‘abcdef’ LIKE NULL, NULL LIKE ‘%’, all result into the value NULL. NULL is not used to perform comparisons.
7. If the operands are non binary strings, LIKE compares them according to their collation.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, the LIKE operator compares its operands as binary strings if either operand is a binary string. If the operands are non binary strings, the LIKE operator compares them according to their collation.
8. The number of strings among the following matched with ‘%all%’ is _______
Ball, baller, tall, tallest
a) 1
b) 2
c) 3
d) 4
Answer
Answer: d [Reason:] In MySQL, the wildcard characters may be specified anywhere in a pattern. The ‘%’ wildcard is used to match variable number of characters. Here, the wildcard matches all the four words.
9. Which of the following is the correct order of precedence (high to low)?
a) !, ^, <<, XOR
b) ^, !, <<, XOR
c) !, <<, XOR, ^
d) !, ^, XOR, <<
Answer
Answer: a [Reason:] When MySQL evaluates an expression, it looks at the operators to determine the order in which it should group the terms of the expression. Some operators have higher precedence, and evaluated earlier than others.
10. 0x61 + 0 results in _______
a) 0
b) ‘a’
c) 97
d) arbitrary
Answer
Answer: c [Reason:] The hexadecimal constants are treated as binary strings, unless the context indicates a number. In the string contexts, each pair of hexadecimal digits is converted to a character, then the result is used as a string.
Database MCQ Set 5
1. Which key declares that an index in one table is related to that in another?
a) primary
b) secondary
c) foreign
d) cross
Answer
Answer: c [Reason:] In MySQL, a foreign key is the one which facilitates index relations across tables. It declares that an index in one table is related to that in another and place constraints.
2. Foreign keys cannot handle deletes and updates.
a) True
b) False
Answer
Answer: b [Reason:] A foreign key is the one which declares that an index in one table is related to that in another and place constraints. It is useful for handling deletes and updates along with row entries.
3. Deletion of an employee from table also deletes that employee from another table. This kind of delete is called ______
a) transparent
b) concrete
c) elaborate
d) cascaded
Answer
Answer: d [Reason:] In MySQL, a cascaded delete and update is possible where records can be deleted from multiple tables. These tables are related with the help of foreign keys. Foreign keys make table updates flexible.
4. Which storage engine in MySQL provides foreign key support?
a) TRANSACTION
b) InnoDB
c) MyISAM
d) MEMORY
Answer
Answer: b [Reason:] In MySQL, there are a list of storage engines to choose from. Each storage engine provides its own set of facilities. The foreign key facilities are provided by the InnoDB storage engine.
5. The property of InnoDB that enforces foreign key relationships stay intact is called _______
a) atomicity
b) durability
c) consistency
d) referential integrity
Answer
Answer: d [Reason:] The storage engine responsible for providing foreign key support is InnoDB. It enforces that the rules guarantee the foreign key relationship stays intact with no mismatching of data.
6. Which clause names the parent table and the index columns in the table?
a) REFERENCES
b) ON DELETE
c) CONSTRAINT
d) FOREIGN KEY
Answer
Answer: a [Reason:] In MySQL foreign key definition syntax, there are various components, namely, FOREIGN KEY, CONSTRAINT, REFERENCES and ON DELETE. The REFERENCES clause names the parent table and the index columns in the table.
7. If the storage engine InnoDB is not used, foreign key cannot be used.
a) True
b) False
Answer
Answer: a [Reason:] The foreign key support is exclusively provided by the storage engine named InnoDB. Without its inclusion, foreign keys cannot be used. However it is possible to implement with application logic.
8. Which clause is used to remove a foreign key constraint?
a) REMOVE
b) DELETE
c) DROP
d) EXCLUDE
Answer
Answer: c [Reason:] In MySQL foreign key definition syntax, there are various components. A foreign key links one table to another table in the table. To remove a foreign key constraint, the ‘DROP’ clause is used.
9. Which keyword is used to specify the foreign key after the table is created?
a) SETUP
b) SET
c) ALTER TABLE
d) SPECIFY
Answer
Answer: c [Reason:] When the table has already been created but the foreign key has not been set of foreign key constraints are not specified, the ‘ALTER TABLE – ADD FOREIGN KEY’ clause is used.
10. Which clause in the SQL standard controls how NULL values in a composite foreign key are handled when comparing to a primary key.
a) SET
b) MATCH
c) ON DELETE
d) ON CASCADE
Answer
Answer: b [Reason:] The MATCH clause in the SQL standard controls how NULL values in a composite (multiple-column) foreign key are handled when comparing to a primary key. MySQL essentially implements the semantics defined by MATCH SIMPLE.
