Database MCQ Set 1
1. The server is told to accept spaces after function names by _______
a) –sql-mode=SKIP_SPACE
b) –sql-mode=IGNORE_SPACE
c) –sql-mode=SPACE_IGNORE
d) –sql-mode=SPACE_SKIP
Answer
Answer: b [Reason:] The MySQL server is told to accept spaces after function names by starting it with the ‘–sql-mode=IGNORE_SPACE’ option. Individual client programs can request this behavior by using the ‘CLIENT_IGNORE_SPACE’ option for ‘mysql_real_connect()’.
2. Which function returns NULL if expr1 = expr2?
a) CASE
b) IF()
c) IFNULL()
d) NULLIF()
Answer
Answer: d [Reason:] The ‘CASE’ is used for the case operator. The function ‘IF()’ is used for the if/else construct. The function ‘IFNULL()’ is the Null if/else construct. ‘NULLIF()’ returns NULL if expr1 = expr2.
3. The operator that compares sounds is _______
a) MATCH SOUNDS
b) CHECK SOUNDS
c) SOUNDS LIKE
d) SOUNDS SIMILAR
Answer
Answer: c [Reason:] The operator ‘SOUNDS LIKE’ is used to compare sound. The ‘RLIKE’ is a synonym for ‘REGEXP’, which is a pattern matching using regular expressions. There are a lot of other operators for use.
4. The string function that returns the index of the first occurrence of substring is _______
a) INSERT()
b) INSTR()
c) INSTRING()
d) INFSTR()
Answer
Answer: b [Reason:] The string function ‘INSTR()’ is used to return the index of the first occurrence of the substring. The function ‘INSERT()’ is used to insert a substring at the specified position up to the specified number of characters.
5. CGI.pm does not support an object oriented style of use.
a) True
b) False
Answer
Answer: b [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.
6. There cannot be more than one column per table with the AUTO_INCREMENT attribute.
a) True
b) False
Answer
Answer: a [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.
7. What does the AUTO_INCREMENT sequences begin at by default?
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.
8. The one that is not optional is _______
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.
9. The join in which all the rows from the right table appear in the output irrespective of the content of the other table is ________
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.
10. The facility that allows nesting one select statement into another is ________
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.
Database MCQ Set 2
1. What attempts auto recovery based on the contents of its serial log?
a) MyISAM
b) InnoDB
c) Falcon
d) TRANSACTION
Answer
Answer: c [Reason:] When the Falcon storage engine is enabled, it attempts the auto recovery based on the contents of its serial log. If the InnoDB storage engine is enabled, it checks for a variety of problems automatically.
2. How many of these can replace ‘level’ in ‘–myisam-recover = level’?
BACKUP, FORCE, QUICK
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] To enable MyISAM table recovery, the server is started with the ‘–myisam-recover = level’ option. The value of level is a comma-separated list of the following: BACKUP, FORCE, QUICK or DEFAULT.
3. Under which option are index changes not flushed until tables close?
a) –delay-write-key
b) –delay-key-write
c) –write-key-delay
d) –key-write-delay
Answer
Answer: b [Reason:] In MySQL, the MyISAM recovery is important if the server is run with the ‘–delay-key-write’ option. Under this condition, the index changes are not flushed until the tables close.
4. Which option suppresses output unless there are errors in the table?
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 variable that 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.
6. The variable that returns code from operations that return true or false is _______
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.
7. Which statement is used to remove indexes on tables?
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.
8. In PHP, how is the first element accessed if $a represents an array with numeric indices?
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. Which operator is used to access property of an object in PHP?
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.
Database MCQ Set 3
1. The command to move the cursor to the beginning of line in MySQL input editor is?
a) Ctrl-A
b) Ctrl-E
c) Ctrl-D
d) Esc-a
Answer
Answer: a [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.
2. What does Control-_ do in MySQL input editor?
a) copies to clipboard
b) pastes from clipboard
c) undoes last change
d) deletes the current line
Answer
Answer: c [Reason:] Control-_ undoes the last change that was made. This can be repeated. The mysql input editor has a variety of commands to facilitate editing while providing inputs to the command line itself.
3. On UNIX, statements entered in ‘MySQL’ are saved in which file?
a) .mysql_queries
b) .queries
c) .mysql_history
d) .history
Answer
Answer: c [Reason:] Statements entered in ‘MySQL’ are stored in the file named ‘.mysql_history’. This file is located in the home directory itself. The SQL statements can be directly pasted into this file.
4. MySQL can be used to execute script files.
a) True
b) False
Answer
Answer: a [Reason:] MySQL is capable of reading input from a file in batch mode. This is also known as the non-interactive mode. A lot of typing and time can be saved when commands are stored in a file and executed from a file.
5. Suppose run_me.sh is a script file. Which command is used to make it executable?
a) chmod +e run_me.sh
b) chmod +a run_me.sh
c) chmod +y run_me.sh
d) chmod +x run_me.sh
Answer
Answer: d [Reason:] The command ‘chmod +x file_name’ makes a script file executable. mysql supports reading from a script file and executing queries from it. Before a script is run, it is necessary for it to be made executable.
6. To execute the contents of a query file ‘exec.sql’ by feeding it to mysql, which command is used?
a) mysql exec.sql > sampdb
b) mysql sampdb < exec.sql
c) mysql exec.sql
d) mysql exec
Answer
Answer: b [Reason:] mysql queries can be run after placing the queries in a file, and then executing it by feeding it to mysql. By default, mysql prints output in a tab-delimited format when it is running in noninteractive mode.
7. Which option prints output in table-format when MySQL is run interactively?
a) -t
b) -tf
c) -p
d) -pf
Answer
Answer: a [Reason:] MySQL produces output in tab-delimited format when it is run in batch mode. Suppose a table-format output is desired, the -t option is used along with the mysql command. -tf is not a valid option.
8. The clause that enables mapping a short command to a long command is ____
a) map
b) direct
c) label
d) alias
Answer
Answer: d [Reason:] If the shell program permits an alias, a short command can be mapped to a long command. This reduces the time taken by typing long commands frequently. An alias is defined by the keyword ‘alias’ followed by the short and long commands.
9. The connection parameters for setting up MySQL can be stored in an option file to save typing the names every time a connection is established.
a) True
b) False
Answer
Answer: a [Reason:] Every Time MySQL is invoked, connection parameters like hostname, username and password are required. To save typing these parameters all the time, they can be saved in an option file instead and executed when setting up the connection.
10. In UNIX, the name of the option file is ____
a) .my.cnf
b) .my.ini
c) .my.opt
d) .my.opc
Answer
Answer: a [Reason:] Under 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.
Database MCQ Set 4
1. The server sets its default time zone by examining its environment.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL the server sets its default time zone by examining its environment. This is the local time zone of the server host. The time zone can be specified explicitly at server startup.
2. The system variable ‘system_time_zone’ can be reset at runtime.
a) True
b) False
Answer
Answer: b [Reason:] The ‘system_time_zone’ represents the time zone that the server determines to be the server host time zone at startup time. It exists only as a global system variable and cannot be reset at runtime.
3. The variable which represents the default time zone of the MySQL server is _______
a) time_zone
b) system_time_zone
c) date_and_time
d) system_time
Answer
Answer: a [Reason:] The system variable ‘time_zone’ represents the default time zone of the MySQL server. By default, this variable is set to ‘SYSTEM’ which means to use the system_time_zone setting.
4. The number of options that can be used to control LOCAL capability at runtime is _______
a) 0
b) 1
c) 2
d) 3
Answer
Answer: c [Reason:] At runtime, the server can be started with the ‘–local-infile’ or ‘–skip-local-infile’ options to enable or disable ‘LOCAL’ capability on the server side. It can be enabled at build time too.
5. If an error occurs during the transaction the troubleshoot is ______
a) delete
b) rollback
c) commit
d) update
Answer
Answer: b [Reason:] Whenever an error occurs during a transaction, it is generally taken to the state prior to the beginning of transaction execution. This is know as rollback. It is a set of undo operations.
6. The ‘A’ in the ACID property of transactions is ___
a) Availability
b) Accuracy
c) Adjustability
d) Atomicity
Answer
Answer: d [Reason:] All the transaction systems have an important set of characteristics in common. This is known as the ‘ACID’ property of the transaction. It refers to the four elementary characteristics of a transaction.
7. The ‘C’ in the ACID property of transactions is ___
a) Compound
b) Concrete
c) Collision
d) Consistency
Answer
Answer: d [Reason:] The elementary characteristics of a transaction are known as the ‘ACID’ properties. ‘ACID’ is the acronym for the four basic characteristics that a transaction must have for smooth processing.
8. The datatype that means a variable length non binary string is ____
a) VARCHAR
b) BINARY
c) VARBINARY
d) BLOB
Answer
Answer: a [Reason:] In MySQL, there are a wide variety of string datatypes for use. Strings can even hold image and sound data. All the four options are string type names. VARCHAR represents a variable length non binary string.
9. The date and time datatype that stores time value in ‘hh:mm:ss’ format is _____
a) DATE
b) TIME
c) DATETIME
d) TIMESTAMP
Answer
Answer: b [Reason:] MySQL has some variety of date and time datatypes. These datatypes are crucial for representing records in a table. The ‘TIME’ type represents a time value, stored in the ‘hh:mm:ss’ format.
10. The spatial datatype used to store a curve is _______
a) GEOMETRY
b) POINT
c) LINESTRING
d) POLYGON
Answer
Answer: c [Reason:] In MySQL, there are many spatial datatypes available for use. Some examples are GEOMETRY, POINT, LINESTRING and POLYGON. The LINESTRING type is used to represent a curve.
Database MCQ Set 5
1. The log that contains a record of server startups and shutdowns and the messages about exceptional conditions is ________
a) error log
b) general query
c) slow query log
d) binary log
Answer
Answer: a [Reason:] The error log contains a record of server startups and shutdowns and the messages about problems or exceptional conditions. If the server fails to start this log provides the help.
2. The log that identifies statements that may be in need of being rewritten for better performance is ________
a) error log
b) general query
c) slow query log
d) binary log
Answer
Answer: c [Reason:] The purpose of the slow-query log is to help the identification of statements that may be in need of being rewritten for better performance. This helps in query optimizations.
3. The default value in seconds in the system variable ‘long_query_time’ is ________
a) 5
b) 10
c) 20
d) 60
Answer
Answer: b [Reason:] The server maintains a ‘long_query_time’ system variable that defines slow queries (10 seconds by default). If a query takes more than these seconds of real time it is considered slow.
4. The logging option to enable binary log index file is ________
a) –log-bin-index
b) –bin-log-index
c) –index-log-bin
d) –index-bin-log
Answer
Answer: a [Reason:] The ‘–log-bin-index’ is the logging option that enables the binary log index file. ‘–log-error’ enables the error log file. Similarly, –log enables the general log file.
5. Usage of aggregates in WHERE clause is not allowed.
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 that are 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 operations on _______
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. What is the kind of delete when deletion of an employee from table also deletes that employee from another table?
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.
9. The storage engine in MySQL that provides foreign key support is _____
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.
10. What is the property of InnoDB that enforces foreign key relationships stay intact?
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.
