Database MCQ Set 1
1. 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.
2. Which keyword is the synonym for DATABASE?
a) TABLE
b) OBJECT
c) DB
d) SCHEMA
Answer
Answer: d [Reason:] In any statement where the word ‘DATABASE’ occurs, the keyword ‘SCHEMA’ can be used as a synonym in place of it. In the literal sense, SCHEMA refers to the structure of the database.
3. It is not required to have an access privilege for a database before selecting it with ‘USE’.
a) True
b) False
Answer
Answer: b [Reason:] In order to select a database as the default database for the MySQL server using the ‘USE’ statement, some access privilege for the database needs to be granted or attained.
4. Which keyword is used to create a database?
a) CREATE
b) SET
c) SETUP
d) LINK
Answer
Answer: a [Reason:] The statement ‘CREATE DATABASE database_name;’ is used to create a database with the name ‘database_name’. A database qualifier should be used to specify the full name of the database.
5. The file created by the server to store the database attributes is ____
a) db.otp
b) dp.zip
c) db.opt
d) db.cls
Answer
Answer: c [Reason:] Whenever a database is created in MySQL, the MySQL server creates a directory with the same name as the database. It creates the file db.opt to store the attributes.
6. To create a database only if it doesn’t already exist, which clause is used?
a) IF EXISTS
b) IF NOT EXISTS
c) CREATE EXISTS
d) EXISTS IF
Answer
Answer: b [Reason:] The ‘CREATE DATABASE’ statement supports many optional values. To create a database named ‘my_db’ only if it doesn’t already exist, we write ‘CREATE DATABASE IF NOT EXISTS my_db’.
7. If COLLATE is given without CHARACTER SET, the first part of the collation name gives the character set.
a) True
b) False
Answer
Answer: a [Reason:] When a database is created, the ‘CHARACTER SET’ and ‘COLLATE’ values are specified. When ‘CHARACTER SET’ is given without ‘COLLATE’, the default collation is used.
8. MySQL stores the database character set and collation attributes in the file ___
a) dp.opt
b) db.opt
c) db.sv
d) db.zip
Answer
Answer: b [Reason:] The file named ‘db.opt’ is of a great significance to MySQL, espectially the MySQL server. It stores the database attributes along with the db charset and collation attributes.
9. Which statement is used to see the definition for an existing database?
a) SHOW CREATE DATABASE
b) SHOW DATABASE
c) SHOW CREATE
d) SHOW CREATE DATABASE TABLE
Answer
Answer: a [Reason:] The statement ‘SHOW CREATE DATABASE’ is invoked to see the definition of an existing database in the MySQL server. It is followed by the qualified name of the database.
10. Which statement makes changes to the database’s global attributes?
a) CHANGE
b) ALTER
c) ALTERNATE
d) UPDATE
Answer
Answer: b [Reason:] The ‘ALTER TABLE’ statement is used to make changes to a database’s global attributes. This statement is followed by the name of the database, character set and collation.
Database MCQ Set 2
1. The system variable in MySQL server that enables to configure the SQL mode is ____
a) sql_config
b) sql_mode
c) sql_server
d) sql_enable
Answer
Answer: b [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.
2. Any client in the client/server architecture of MySQL can change how the server behaves in relation to itself without impact on other clients.
a) True
b) False
Answer
Answer: a [Reason:] The MySQL server system variable ‘sql_mode’ can be set up globally. In this way, individual clients would have the ability to change their connection to the server. This variable can affect several aspects of statement executions.
3. How do the STRICT_ALL_TABLES and STRICT_TRANS_TABLES mode values deal with bad data?
a) reject them
b) accept them
c) change them to the closest legal value and accept
d) change them to the closest legal value and reject
Answer
Answer: a [Reason:] ‘STRICT_ALL_TABLES’ and ‘STRICT_TRANS_TABLES’ are the MySQL server mode values that deal with bad data in a ‘strict’ manner. They do not make any change to the data. It is simply rejected.
4. Which mode tells the server to recognize double quote as an identifier quoting character?
a) ANSI_DQ
b) ANSI_QUOTES
c) ANSI_RECG_QUOTES
d) ANSI_RECG_DQUOTES
Answer
Answer: b [Reason:] ‘ANSI_QUOTES’ is the MySQL server mode value stored in the system variable which directs the MySQL server to treat the double quote character as a quoting character for identifiers. The others are not valid mode values.
5. Which mode tells the server to recognize || as the string concatenation operator?
a) PIPES_AS_CONCAT
b) ORS_AS_CONCAT
c) DOUBLE_PIPE_AS_CONCAT
d) LOGIC_OR_AS_CONCAT
Answer
Answer: a [Reason:] ‘PIPES_AS_CONCAT’, one of the many MySQL server mode values stored in the system variable would direct the server to treat the pipe characters as the standard SQL string concatenation operator rather than the logical or. The rest mode values are invalid.
6. Which of these modes is a composite server mode?
a) COMPOSITE
b) COMPOSITE_MODE
c) COMPOSITE_SERVER
d) ANSI
Answer
Answer: d [Reason:] The ‘ANSI’ mode value is a composite mode. It turns on several other mode values like ANSI_QUOTES, PIPES_AS_CONCAT. This makes the server behave much like standard SQL than the default mode.
7. MySQL server mode values are case sensitive.
a) True
b) False
Answer
Answer: b [Reason:] The MySQL server mode values stored in the system variable are not case sensitive. So it does not make a difference if you accidentally store the mode values in lowercase or uppercase.
8. Which of the following commands sets the SQL mode as TRADITIONAL?
a) –sqlmode=’TRADITIONAL’
b) –sql_mode=’TRADITIONAL’
c) –sql-mode=’TRADITIONAL’
d) –sql.mode=’TRADITIONAL’
Answer
Answer: c [Reason:] When the server starts, the command –sql-mode=’TRADITIONAL’ can be used to set the SQL mode as ‘TRADITIONAL’. This can be stored in an option file or can be directly executed on the command line.
9. Which statement can be used to specify the sql_mode system variable at runtime?
a) SPECIFY
b) SET
c) ASSIGN
d) CHANGE
Answer
Answer: b [Reason:] The ‘SET’ statement can change the SQL mode at runtime. For example, the statement SET sql_mode = ‘ANSI_QUOTES’ can be used by a client in its own session specific SQL mode.
9. Which statement can be used to specify the sql_mode system variable at runtime?
a) SPECIFY
b) SET
c) ASSIGN
d) CHANGE
Answer
Answer: b [Reason:] The ‘SET’ statement can change the SQL mode at runtime. For example, the statement SET sql_mode = ‘ANSI_QUOTES’ can be used by a client in its own session specific SQL mode.
10. When no modes are enabled, what does this statement return?
SELECT @@SESSION.sql_mode;
a) empty value
b) zero
c) a negative value
d) a garbage value
Answer
Answer: a [Reason:] When it is needed to determine the current value of the session SQL mode, the above statement is executed. The returned value consists of a comma-separated list of the modes that are enabled.
Database MCQ Set 3
1. Which of these is a read only variable?
a) error_count
b) big_tables
c) autocommit
d) foreign_key_checks
Answer
Answer: a [Reason:] The session only system variable ‘error_count’ is a read only variable that indicates the number of errors generated by the last statement that can generate errors. The others are writable.
2. Which variable when set to 1 would enable foreign key checking for InnoDB tables?
a) error_count
b) identity
c) foreign_key_checks
d) autocommit
Answer
Answer: c [Reason:] Setting the variable ‘foreign_key_checks’ to 0 or 1 disables or enables the foreign key checking for InnoDB tables. The default is to perform checking. Disabling key checks can also be helpful.
3. The synonym for last_insert_id session variable is _______
a) insert_id
b) identity
c) sql_auto_is_null
d) sql_big_selects
Answer
Answer: b [Reason:] The session only system variable ‘identity’ is a synonym for the ‘last_insert_id’ session variable. Setting ‘last_insert_id’ specifies the value to be returned by the function ‘LAST_INSERT_ID()’.
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 funtion ‘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. What is the embedded server library containing the MySQL server linkable into applications?
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.
6. When building from source, the embedded server library is enabled by what?
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.
7. 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.
8. How many among 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.
9. How many among 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.
10. What returns a string containing an error message?
a) mysql_error()
b) mysql_errno()
c) mysql_sqlstate()
d) mysql_close()
Answer
Answer: a [Reason:] The API call named ‘mysql_error()’ returns a string containing an error message. ‘mysql_close()’ does not return any value at all. ‘mysql_errno()’ returns a MySQL-specific numeric code.
Database MCQ Set 4
1. The slave stores the CHANGE MASTER parameters in the file _______
a) slave.info
b) slave.inf
c) master.info
d) master.inf
Answer
Answer: c [Reason:] The ‘slave’ stores the ‘CHANGE MASTER’ parameters in a file named ‘master.info’ in its data directory in order to record the initial replication status. It updates the file as replication proceeds.
2. Which statement on the master expires binary log files?
a) SHOW SLAVE STATUS
b) PURGE MASTER
c) PURGE SLAVE
d) SHOW MASTER STATUS
Answer
Answer: b [Reason:] The ‘PURGE MASTER’ statement on the master expires binary log files. This can be used after issuing a ‘SHOW SLAVE STATUS’ statement on each of the slaves to determine which log files are no longer needed.
3. Which statement suspends the replication related activity of the slave server?
a) RESUME SLAVE
b) START SLAVE
c) STOP SLAVE
d) GET SLAVE
Answer
Answer: c [Reason:] The ‘STOP SLAVE’ and ‘START SLAVE’ statements suspend and resume the replication-related activity of a slave server. These statements are useful for telling the slave to be quiescent.
4. MySQL Enterprise Backup records details of each backup in _______
a) history_backup
b) backup_history
c) backlog_history
d) history_backlog
Answer
Answer: b [Reason:] The restored data includes the ‘backup_history’ table, where ‘MySQL Enterprise Backup’ records details of each backup. The table allows to perform future incremental backups using ‘–incremental-base=history:last_backup’.
5. In which context is the privilege ‘CREATE TABLESPACE’ applied?
a) Server administration
b) Tables
c) Stored routines
d) Views
Answer
Answer: a [Reason:] The column name associated with the privilege ‘CREATE TABLESPACE’ is ‘Create_tablespace_priv’. The context in which this privilege is being used is the server administration.
6. How many of the following languages can interface to bind to the C API of MySQL?
Perl, PHP, Python
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] The client library provides the means through which the MySQL bindings for other languages can be built on top of the C API. This type of interface exists for Perl, PHP, Python and others.
7. Which language is the core for APIs and communication in MySQL?
a) C
b) C++
c) Python
d) Ruby
Answer
Answer: a [Reason:] In order to provide application development, MySQL provides a client library written in the C programming language. It enables the access of MySQL databases from a C program.
8. PHP scripts are located in the web client document tree.
a) True
b) False
Answer
Answer: b [Reason:] The PHP scripts are different from DBI scripts since PHP scripts are located within the web server document tree while DBI scripts are located in a cgi-bin directory located outside of document tree.
9. In which directory are the DBI scripts located?
a) cgi-inc
b) cgi-bin
c) cgi-usr
d) cgi-perl
Answer
Answer: b [Reason:] DBI scripts are located in a cgi-bin directory located outside of document tree. The PHP scripts are different from DBI scripts since PHP scripts are located within the web server document tree.
10. Which is the stored program associated with a schedule?
a) Trigger
b) Event
c) Stored function
d) Stored procedure
Answer
Answer: b [Reason:] The MySQL version 5.1.6 and above has an event scheduler. It enables to perform time activated database operations. An event is a stored program that is associated with a schedule.
Database MCQ Set 5
1. How many files does each end of a client/server connection including SSL support use to set up secure communications?
a) 0
b) 1
c) 2
d) 3
Answer
Answer: d [Reason:] With a MySQL installation that includes SSL support the server and its clients can communicate securely. Each end of a connection uses three files to set up secure communications.
2. The TLS protocol is _______
a) transparent layer security
b) transport layer security
c) transparent level security
d) transport level security
Answer
Answer: b [Reason:] MySQL supports encrypted connections between clients and the server using the TLS protocol. It is also referred to as SSL but MySQL does not actually use the SSL protocol for encrypted connections.
3. The grant table scope columns that is case insensitive is _______
a) Host
b) User
c) Password
d) Db
Answer
Answer: a [Reason:] ‘Db’ and ‘Table_name’ values are always treated as case sensitive even though treatment of database and table names in SQL statements depends on the filesystem case sensitivity of the host.
4. Which variable checks for availability of SSL support?
a) have_ssl
b) has_ssl
c) avail_ssl
d) ssl_avail
Answer
Answer: a [Reason:] After the SSL-capable server is started, to verify that it supports SSL, connection is established with ‘mysql’ and the following query is issued: SHOW VARIABLES LIKE ‘have_ssl’.
5. MySQL does not provide a date type that has an optional time part.
a) True
b) False
Answer
Answer: a [Reason:] In MySQL, there is no data type provided which has an optional time part. The ‘DATE’ type values never have a time part. The ‘DATETIME’ type values must have a time part in them.
6. What is the best datatype for a column that is expected to store values up to 2 million?
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. The datatype that stores the longest length of strings is ________
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) good
b) bad
c) illegal
d) fare
Answer
Answer: b [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 not invoked automatically by the server.
a) True
b) False
Answer
Answer: b [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. A stored procedure is invoked using the statement ____
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.
