Database MCQ Set 1
1. To check how MySQL would execute a SELECT query, which statement is used?
a) TELL
b) SHOW
c) DISPLAY
d) EXPLAIN
Answer
Answer: d [Reason:] In MySQL, by issuing an EXPLAIN statement MySQL displays some information about how it would execute a SELECT query without actually executing it. It is prefixed with the query.
2. To perform analysis of key values by the server, the statement used is ____
a) ANALYZE KEYS
b) ANALYZE TABLE
c) PERFORM ANALYSIS
d) PERFORM TABLE ANALYSIS
Answer
Answer: b [Reason:] In MySQL, for the MyISAM and InnoDB tables, the server can be told to perform an analysis of key values by issuing the ANALYZE TABLE statement. It helps in knowing about query optimization.
3. Which statement is used to verify optimizer operation?
a) ANALYZE
b) VERIFY
c) EXPLAIN
d) SHOW
Answer
Answer: c [Reason:] The EXPLAIN statement in MySQL can tell whether the indexes are being used or not. This information is helpful when different ways of writing a statement need to be tested.
4. Which statement is used to force the optimizer to use tables in a particular order?
a) FORCE INDEX
b) USE INDEX
c) IGNORE INDEX
d) STRAIGHT_JOIN
Answer
Answer: d [Reason:] STRAIGHT_JOIN is used to force the optimizer to use tables in a particular order. The MySQL optimizer by default considers itself free to determine the order in which to scan tables most quickly.
5. Which of these comparisons is slowest?
a) INT/INT
b) INT/BIGINT
c) BIGINT/BIGINT
d) All are of same speed
Answer
Answer: b [Reason:] On comparing indexed columns, identical data types will give better performance than dissimilar types. So an INT/INT or BIGINT/BIGINT comparison is faster than an INT/BIGINT comparison.
6. Which of the following WHERE clauses is faster?
1. WHERE col * 3 < 9 2. WHERE col < 9 / 3
a) 1
b) 2
c) same speed
c) dependent on operating system
Answer
Answer: b [Reason:] For the first variation, MySQL would retrieve the value of ‘col’ for each row, multiply by three, and then compare the result to nine. In this case, no index can be used and hence it is slower.
7. What are the results of the following queries if col is an integer column?
1. SELECT * FROM mytbl WHERE num_col = '4'; 2. SELECT * FROM mytbl WHERE num_col = 4;
a) same
b) different
c) 1 is an error
d) 2 is an error
Answer
Answer: a [Reason:] The first query invokes a type conversion. The conversion operation involves some performance penalty for converting the integer and string to double to perform the comparison.
8. Which system variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table?
a) optimizer_prune_level
b) optimizer_search_depth
c) optimizer_search
d) optimizer_prune
Answer
Answer: a [Reason:] The optimizer_prune_level variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table. This option is kept on by default.
9. Which system variable tells how far into the rest of each incomplete plan the optimizer should look to evaluate whether it should be expanded further?
a) optimizer_prune_level
b) optimizer_search_depth
c) optimizer_search
d) optimizer_prune
Answer
Answer: b [Reason:] In MySQL, the optimizer_search_depth system variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further.
10. The optimizer_prune_level is set by default.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, the optimizer_prune_level is on by default. This variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table.
Database MCQ Set 2
1. Which of the following clauses is used to display information that match a given pattern?
a) LIKE
b) WHERE
c) IS
d) SAME
Answer
Answer: a [Reason:] The ‘LIKE’ clause filters information that match a given pattern. ‘WHERE’ clause selects information that is specified by a condition. ‘IS’ is used to match the exact condition specified.
2. What column names are displayed when this command is executed?
SHOW COLUMNS FROM tbl_name LIKE '%name';
a) suffixed with ‘name’
b) prefixed with ‘name’
c) suffixed with ‘%name’
d) prefixed with ‘%name’
Answer
Answer: a [Reason:] The wildcard ‘%’ is used to indicate that any number of characters can replace it. All column names that end in ‘name’ are displayed. Additional information of columns like type and size are listed.
3. The special database that always exists after setting up MySQL in a computer is ____
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.
4. In the following code, InnoDB is ____
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.
5. Identify the table name in the following statement.
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.
6. What is ‘xyz’ in the following statement?
SELECT abc FROM xyz;
a) row name
b) column name
c) table name
d) database name
Answer
Answer: c [Reason:] The SELECT clause is used to retrieve information from some specified tables. It follows a specified format for information retrieval. Here, ‘abc’ can be a column name. It must be present in the table ‘xyz’.
7. Which operator is used to perform integer divisions in MySQL?
a) /
b)
c) DIV
d) //
Answer
Answer: c [Reason:] The operator ‘DIV’ is used to perform integer divisions in MySQL. ‘//’ is used in languages like Python to do the same. The operator ‘/’ performs floating point divisions and ‘’ is facilitates escape sequences.
8. The NULL value also means _____
a) value equal to zero
b) unknown value
c) negative values
d) a large value
Answer
Answer: b [Reason:] The NULL value in MySQL is a special value. It represents ‘no value’ or an ‘unknown value’. A NULL value can’t be compared like normal known values since it gives undesirable results.
9. What does comparing a known value with NULL result into?
a) zero
b) a positive value
c) a negative value
d) null
Answer
Answer: d [Reason:] In MySQL, NULL is not comparable to other known values. It will result in a NULL when compared to any value. The following statement would result in four NULLs: ‘SELECT NULL = 0, NULL < 0, NULL <> 0, NULL > 0’.
10. Which clause is used to sort query elements?
a) GROUP
b) GROUP BY
c) ORDER
d) ORDER BY
Answer
Answer: d [Reason:] An ‘ORDER BY’ clause specifies the sort order. The data is sorted in ascending order by default. To sort in descending order, the keyword DESC is appended to the ‘ORDER BY’ clause. ‘GROUP BY’ is used to group the query results according to a criteria.
Database MCQ Set 3
1. In Perl DBI, functions are 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’.
2. Which variable is used as a handle to an open file?
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.
3. The variable that is a handle to a database object is ________
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.
4. Which variable returns code from operations that return true or false?
a) $rc
b) $rv
c) $rows
d) $ary
Answer
Answer: a [Reason:] The Perl Non-handle variable ‘$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.
5. The statement to remove indexes on tables is ________
a) DROP INDEX
b) DELETE INDEX
c) REMOVE INDEX
d) FLUSH INDEX
Answer
Answer: a [Reason:] MySQL provides statements to change the structure of tables. To add or remove the indexes on the existing database tables, the ‘CREATE INDEX’ and ‘DROP INDEX’ tables are used.
6. What allows nesting one select statement into another?
a) nesting
b) binding
c) subquerying
d) encapsulating
Answer
Answer: c [Reason:] The ‘subquerying’ support provided by MySQL is a capability that allows writing one ‘SELECT’ statement within parentheses and nesting within another. This allows logically selecting content from tables.
7. The operator that does not perform relative-value comparisons is ________
a) =
b) ==
c) <=
d) >=
Answer
Answer: b [Reason:] The operators = , <> , > , >= , < , and <= perform relative value comparisons in MySQL. ‘==’ is not a valid comparison operator in MySQL. Such operators are useful in filtering information from a table.
8. Which of these operators does not perform relative value comparisons?
a) =
b) ==
c) <=
d) >=
Answer
Answer: b [Reason:] The operators = , <> , > , >= , < , and <= perform relative value comparisons in MySQL. ‘==’ is not a valid comparison operator in MySQL. Such operators are useful in filtering information from a table.
9. The key declares that an index in one table is related to that in another is called _______
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.
10. Foreign keys can handle deletes and updates.
a) True
b) False
Answer
Answer: a [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.
Database MCQ Set 4
1. Perl scripts are text files.
a) True
b) False
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.
2. The shebang line is ________
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.
3. The Perl DBI is ________
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.
4. The default path to perl in Unix is ________
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.
5. 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.
6. Which is the join in which all the rows from the right table appear in the output irrespective of the content of the other table?
a) CARTESIAN JOIN
b) CROSS JOIN
c) INNER JOIN
d) RIGHT JOIN
Answer
Answer: d [Reason:] In a ‘RIGHT JOIN’, the output is produced for every row of the right table, even if it does not exist in the other table. This is the reason why it is called a ‘RIGHT JOIN’. ‘RIGHT JOIN’ and ‘LEFT JOIN’ are a kind of OUTER JOIN.
7. What is the facility that allows nesting one select statement into another?
a) nesting
b) binding
c) subquerying
d) encapsulating
Answer
Answer: c [Reason:] The ‘subquerying’ support provided by MySQL is a capability that allows writing one ‘SELECT’ statement within parentheses and nesting within another. This allows logically selecting content from tables.
8. Which of these operators does not perform relative value comparisons?
a) =
b) ==
c) <=
d) >=
Answer
Answer: b [Reason:] The operators = ,<> , > , >= , < , and >= perform relative value comparisons in MySQL. ‘==’ is not a valid comparison operator in MySQL. Such operators are useful in filtering information from a table.
9. To combine multiple retrievals, which keyword is used to write several SELECT statements between them?
a) COMBINE
b) CONCAT
c) JOIN
d) UNION
Answer
Answer: d [Reason:] The ‘UNION’ operator is used for combining the results of various ‘SELECT’ queries into one. For example, ‘SELECT a FROM table1 UNION SELECT a FROM table2;’ produces the results from tables table1 concatenated with that of table2.
10. Issuing ‘SELECT’ on a MERGE table is like _______
a) UNION
b) UNION ALL
c) UNION DISTINCT
d) JOIN
Answer
Answer: b [Reason:] Performing a ‘SELECT’ operation on a ‘MERGE’ table is like performing ‘UNION ALL’. This means that duplicate row results are not removed. ‘SELECT DISTINCT’ is like ‘UNION’ or ‘UNION DISTINCT’.
Database MCQ Set 5
1. 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.
2. 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.
3. The output from PHP is ____
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.
4. PHP variables are preceded by ____
a) _
b) @
c) $
d) &
Answer
Answer: c [Reason:] All the PHP scripts that use variables have variable names preceded by the dollar sign (‘$’). The variables are signified by this identifier, irrespective of the value it stores.
5. The datatype best suited to store currency values is ____
a) INT
b) FLOAT
c) DOUBLE
d) DECIMAL
Answer
Answer: d [Reason:] Currency is a numeric information. For monetary calculations, FLOAT and DOUBLE are subject to rounding error and may not be suitable. A DECIMAL(M, 2) type is best suited for it.
6. What returns a result to the client?
a) Stored functions
b) Stored procedures
c) Triggers
d) Events
Answer
Answer: a [Reason:] Stored functions return a result from a calculation and can be used in expressions. Stored procedures do not return a result directly but can be used to perform general computations.
7. What is defined to execute when the table is modified only?
a) Stored functions
b) Stored procedures
c) Triggers
d) Events
Answer
Answer: c [Reason:] In MySQL, triggers are associated with a table. They are defined to execute when the table is modified via INSERT, DELETE or UPDATE statements. MySQL supports objects to be stored on server side.
8. Which statement is used To produce a stored function?
a) PRODUCE FUNCTION
b) CREATE FUNCTION
c) PRODUCE PROCEDURE
d) CREATE PROCEDURE
Answer
Answer: b [Reason:] The ‘CREATE FUNCTION’ statement is used to create a stored function in MySQL. The ‘CREATE PROCEDURE’ statement is used to create a stored procedure instead, from which values are not returned.
9. The number of values that can be returned from a given stored function is ________
a) 0
b) 1
c) 2
d) 3
Answer
Answer: b [Reason:] In MySQL, the stored function cannot return multiple values. Instead, multiple stored functions can be written and invoked from within a single statement however. They are different from stored procedures.
10. The statement used to create a trigger is ________
a) CREATE TRIGGER
b) CREATE TRIGGERS
c) PRODUCE TRIGGER
d) PRODUCE TRIGGERS
Answer
Answer: a [Reason:] In order to create a trigger, the CREATE TRIGGER statement is used. The definition indicates the particular type of statement for which the trigger activates and whether it activates before or after the rows are modified.
