Database MCQ Set 1
1. Triggers enable to enforce data integrity constraints.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, a trigger can examine or change new data values to be inserted or used to update a row in a table. This enables the enforcement of the data integrity constraints.
2. Which statement is used to create a trigger?
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.
3. For which of the following are triggers not supported?
a) delete
b) update
c) insert
d) views
Answer
Answer: d [Reason:] In MySQL, the triggers are run only after the table modifications like insert, update and delete are run. Triggers are not supported for views. In order to create a trigger, the CREATE TRIGGER statement is used.
4. Which statement is used to remove a trigger?
a) REMOVE
b) DELETE
c) DROP
d) CLEAR
Answer
Answer: c [Reason:] In order to delete a trigger, the DROP TRIGGER statement is used. The DROP TRIGGER construct is used by writing the phrase ‘DROP TRIGGER’ followed by the scheme name specification.
5. Before MySQL 5.1.6 which privilege was required to create and drop triggers?
a) PRIVILEGE
b) TRIGGER
c) SUPER
d) MACRO
Answer
Answer: c [Reason:] The privilege to create and drop triggers is version specific in MySQL. Before MySQL 5.1.6, the SUPER privilege was needed. Access control is more correctly handled post this version.
6. The TRIGGER privilege is used for the table to be able to create and drop triggers for it.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, because a trigger is associated with a table, the TRIGGER privilege is a must for that table to be able to create and drop triggers for it. The SUPER privilege was used pre 5.1.6.
7. What is abc in the following statement?
CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;
a) trigger name
b) table name
c) trigger statement
d) update statement
Answer
Answer: a [Reason:] In MySQL, the trigger creation construct is the ‘CREATE TRIGGER’ construct. It specifies the trigger name, the type of statement for which it is activated, and the table name and statement.
8. What is def in the following statement?
CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;
a) trigger name
b) table name
c) trigger statement
d) update statement
Answer
Answer: b [Reason:] The trigger creation construct in MySQL is the ‘CREATE TRIGGER’ construct. It specifies components like the trigger name, the type of statement for which it is activated, and the table name and statement.
9. What is ghi in the following statement?
CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;
a) trigger name
b) table name
c) trigger statement
d) update statement
Answer
Answer: c [Reason:] In MySQL, the trigger creation construct is the ‘CREATE TRIGGER’ construct. It specifies components like the trigger name, the type of statement for which it is activated, and the table name and statement.
10. What is def in the following statement?
DECLARE abc HANDLER FOR def ghi;
a) action
b) condition value
c) statement
d) null
Answer
Answer: b [Reason:] The ‘DECLARE HANDLER’ statement is used to declare a handler. When a condition whose value matches def, MySQL will execute ghi and either continue or exit the current code block.
Database MCQ Set 2
1. The embedded server library containing the MySQL server linkable into applications is ___
a) libmysqla
b) libmysqlb
c) libmysqlc
d) libmysqld
Answer
Answer: d [Reason:] MySQL includes an embedded server library, libmysqld, containing the MySQL server in a way that can be linked into applications. This enables the production of MySQL-based standalone applications.
2. When building from source, the embedded server library is enabled by ___
a) –with-embedded-server
b) –with-server-embedded
c) –with-embedded-library
d) –with-library-embedded
Answer
Answer: a [Reason:] While building from the source, the embedded server library is enabled by using the option ‘–with-embedded-server’ while running ‘configure’. This applies equally to MySQL 5.0 and 5.1.
3. An embedded application includes the server side only.
a) True
b) False
Answer
Answer: b [Reason:] In MySQL, an embedded application includes both a client side and a server side, in order that it can process one group of options for the client, and another group for the server.
4. Converting a client/server application to embedded server is simpler.
a) True
b) False
Answer
Answer: a [Reason:] Writing an application that uses the embedded server is different from writing one that operates in a client/server context. A program that is written as a client/server application can easily be converted to use the embedded server.
5. Which of the options executes all SQL statements in a SQL script irrespective of the number of errors?
a) –run
b) –ensure
c) –force
d) –violent
Answer
Answer: c [Reason:] If SQL queries in a file are run using mysql in batch mode, mysql either quits after the first error. If the –force option is specified all the queries are executed indiscriminately.
6. Which clause is used to display information that matches a given pattern?
a) WHERE
b) IS
c) SAME
d) LIKE
Answer
Answer: d [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.
7. Comparing a known value with NULL results into ___
a) null
b) zero
c) a positive value
d) a negative value
Answer
Answer: a [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′.
8. Which is the command to move the cursor to the beginning of line in mysql input editor?
a) Ctrl-E
b) Ctrl-D
c) Esc-a
d) Ctrl-A
Answer
Answer: d [Reason:] Control-A moves the cursor to the beginning of the line. Ctrl-E moves the cursor to the end of the line. Control-D deletes the character under the cursor whereas Esc-a is not valid.
9. The name of the option file in Unix is ____
a) .my.cnf
b) .my.ini
c) .my.opt
d) .my.opc
Answer
Answer: a [Reason:] In Unix an option file is set up by creating a file named ‘~/.my.cnf’ in the home directory. ‘C:my.ini’ is the option file that is setup in Windows. An option file stores the connection parameters.
10. System variable in MySQL server that enables to configure the SQL mode is ____
a) sql_mode
b) sql_config
c) sql_server
d) sql_enable
Answer
Answer: a [Reason:] The system variable ‘sql_mode’ is used by the MySQL server to configure the SQL mode. It has an impact on various aspects of the SQL statement executions. The other variables do not exist in the server.
Database MCQ Set 3
1. Using indexes optimizes query performance.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, there are many techniques available for optimizing queries. Indexing is the most useful. Generally the one thing that makes the most difference is the proper use of indexes.
2. The maximum number of indexes on MyISAM table is ____
a) 0
b) 1
c) 2
d) more than 1
Answer
Answer: d [Reason:] In MySQL, for a MyISAM table, there can be more than one indexes. The data rows of the MyISAM table are stored in a data file and the index values are stored in a separate index file.
3. The data rows of a MyISAM table are stored in ____
a) data file
b) index file
c) log file
d) error file
Answer
Answer: a [Reason:] The data rows of the MyISAM table are stored in a data file and the index values are stored in a separate index file. In MySQL, for a MyISAM table, there can be more than one indexes.
4. In a single table (with 10 columns) join query the number of values needed to examine per column is ____
a) 1
b) 10
c) 0
d) 2
Answer
Answer: b [Reason:] For indexing in the context of performing joins, in a single table query, the number of values that are needed to be examined per column is the number of rows in the table.
5. Suppose tbl_1 is a table consisting of 8 rows and tbl_2 is a table consisting of 6 rows, the number of combinations through which the search is performed is ____
SELECT tbl_1.i1, tbl_2.i2 FROM tbl_1 INNER JOIN tbl_2 WHERE tbl_1.i1 = tbl_2.i2;
a) 14
b) 2
c) 1
d) 48
Answer
Answer: d [Reason:] In MySQL, when the tables are unindexed, the searches are run through all the possible combinations. In this case, the search space is 8 x 6 = 48, the product of the number of rows.
6. How many of the following shared tablespaces is bound by the OS file size limit?
MyISAM, InnoDB, TRANSACTION
a) 0
b) 1
c) 2
c) 3
Answer
Answer: b [Reason:] In MySQL, all the MyISAM tables shared tablespace files size if bound by the operating system file size limits. For InnoDB tables shared tablespaces, it is not bound by this factor.
7. If a column contains 7 values and 6 distinct values, the cardinality is ____
a) 7
b) 6
c) 13
d) 1
Answer
Answer: b [Reason:] In MySQL, the cardinality of a column is the number of distinct values that it contains. A column that contains the values 1, 2, 3, 4, 4, 5, 6 has a cardinality of six and not seven.
8. For a table having 4 columns, the number of columns to which hash function is applied when using hash indexes is ____
a) 1
b) 2
c) 3
d) 4
Answer
Answer: d [Reason:] In MySQL, for the hash indexes, a hash function is applied to each of the column values. The resulting hash values are stored in the index and used to perform lookups on it.
9. The slow query log can be used to identify queries that may be performing badly.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, the slow query log is used to identify queries that may be performing badly. This log can help find the queries that might benefit from indexing. The slow query log is written as text.
10. The slow query log is written as ____
a) numbers
b) text
c) image
d) graph
Answer
Answer: b [Reason:] In MySQL, the slow query log is written as text, so it is viewable with any file display program. The mysqldumpslow utility can also be used to summarize the contents of the same.
Database MCQ Set 4
1. Multiple statement execution is enabled by default.
a) True
b) False
Answer
Answer: b [Reason:] The ‘multiple-statement’ execution is not enabled by default, so the server must be told that it is intended to be used. There are two ways to tell the server to enable the execution.
2. Which option enables multiple-statement execution?
a) CLIENT_MULTI_STATEMENTS
b) CLIENT_STATEMENTS_MULTI
c) MULTI_STATEMENTS_CLIENT
d) MULTI_CLIENTS_STATEMENTS
Answer
Answer: a [Reason:] There are two ways to enable the multiple-statement execution. The first is to add the ‘CLIENT_MULTI_STATEMENTS’ option in the flags argument to ‘mysql_real_connect()’ at connect time.
3. Which of these is preferred when stored procedures are not being used?
CLIENT_MULTI_STATEMENTS, mysql_set_server_option()
a) CLIENT_MULTI_STATEMENTS
b) mysql_set_server_option()
c) any of the two
d) neither of the two
Answer
Answer: c [Reason:] If the program does not use stored procedures anyone is suitable. If the program uses stored procedures and invokes a ‘CALL’ statement that returns a result set, the first method is better.
4. mysql_next_result() returns a status.
a) True
b) False
Answer
Answer: a [Reason:] The function ‘mysql_next_result()’ returns a status and initiates retrieval of the next set if more results are available. The status is zero if more results are available and -1 if not.
5. Which option executes all SQL statements in a SQL script irrespective of the number of errors?
a) –ensure
b) –force
c) –violent
d) –run
Answer
Answer: b [Reason:] If SQL queries in a file are run using mysql in batch mode, mysql either quits after the first error. If the –force option is specified all the queries are executed indiscriminately.
6. The number of attributes in the following table is ________
CREATE TABLE employee ( emp_name CHAR(30), emp_id INT );
a) 1
b) 2
c) 3
d) 30
Answer
Answer: b [Reason:] The name of the table created is ’employee’. It has two attributes, namely, ’emp_name’ and ’emp_id’. The attributes are the columns in a table. emp_name is of type string and emp_id is of type integer.
7. To see the table structure which command is issued?
a) VIEW tbl_name;
b) SELECT TABLE tbl_name;
c) SELECT tbl_name;
d) DESCRIBE tbl_name;
Answer
Answer: d [Reason:] The ‘DESCRIBE’ command is issued to see the structure of the table ‘tbl_name’. It shows the structure in the format: Field-Type-Null-Key-Default-Extra. The ‘VIEW’ and ‘SELECT’ commands are used to see the contents of the table.
8. Which character is illegal in naming an unquoted identifier in SQL?
a) .
b) _
c) $
d) 2
Answer
Answer: a [Reason:] An identifier is used to refer to a database or its elements. These elements can be entire tables or attributes. The names of these identifiers follow some set of rules, so they have a set of legal characters.
9. Which statement is valid if ‘`sampledb`’ is a database and ‘`tbl`’ is a table in it?
a) SELECT * FROM `sampledb.member`
b) SELECT * FROM `sampledb`.`member`
c) SELECT * FROM `member`.`sampledb`
d) SELECT * FROM `member.sampledb`
Answer
Answer: b [Reason:] When quotes is being used to refer to a qualified name, the individual identifiers are quoted within the name separately. So, the database name `sampledb` and table name `tbl` are quoted separately.
10. The default case sensitivity of the database and table names depends on _____
a) Server SQL mode
b) Operating system of machine
c) Does not depend on anything
d) SQL server
Answer
Answer: b [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.
Database MCQ Set 5
1. The module that links DBI to the web is ________
a) CGI.pn
b) CGI.pem
c) CGI.pm
d) CGI.po
Answer
Answer: c [Reason:] The CGI.pm module is used to write scripts that use the ‘Common Gateway Interface’ protocol. It defines how a web server communicates with other programs. It provides an easy link.
2. CGI stands for ________
a) Computer Gateway Interface
b) Common Gateway Interface
c) Computer Generated Interface
d) Common Generated Interface
Answer
Answer: b [Reason:] The ‘CGI.pm’ module is so called because it helps write scripts that use the ‘Common Gateway Interface’ protocol which defines how a web server communicates with other programs.
3. Which line is written at the top of the script to write a Perl script using CGI.pm?
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.
4. 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.
5. 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.
6. By default, the AUTO_INCREMENT sequences begin at ____
a) 0
b) 1
c) -1
d) 2
Answer
Answer: b [Reason:] The AUTO_INCREMENT column attribute provides unique numbers for column identification. AUTO_INCREMENT sequences normally begin at 1 and increase monotonically like 1, 2, 3, and so on.
7. What does the expression ’12 DIV 5′ evaluate 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.
8. What does ‘abc’ || ‘xyz’ result in if the PIPES_AS_CONCAT is disabled?
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.
9. The AUTO_INCREMENT column attribute is best used with ________
a) FLOAT
b) INT
c) CHARACTER
d) DOUBLE
Answer
Answer: b [Reason:] The AUTO_INCREMENT is a column attribute and it is best used with the integer datatypes. It automatically assigns monotonically increasing values to the AUTO_INCREMENT columns.
10. 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.
