Explore topic-wise InterviewSolutions in .

Correct answer is (a) table_cache

To EXPLAIN: The ‘table_cache’ STORES the size of the table cache. Increasing the VALUE enables ‘mysqld’ to keep more tables open simultaneously by REDUCING the number of files open and CLOSE operations.

Correct CHOICE is (a) 1GB

The explanation: The ‘max_allowed_packet’ STORES the MAXIMUM SIZE to which the buffer used for client communications can grow. The largest value to which ‘max_allowed_packet’ variable can be set is 1GB.

Correct option is (a) sql_mode

The best explanation: The SYSTEM variable ‘sql_mode’ that indicates the DEFAULT SQL mode is an example of a variable that exists at both the global and SESSION levels. This mode AFFECTS SEVERAL aspects of SQL statement processing.

Right ANSWER is (d) referential integrity

For explanation I WOULD say: 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.

Right choice is (B) INNODB

To elaborate: 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.

Right choice is (d) cascaded

The best EXPLANATION: In MySQL, a cascaded delete and update are 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.

The correct ANSWER is (B) column

The explanation is: 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.

The correct answer is (a) True

Explanation: 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.

Right option is (a) –LOG-bin-INDEX

Easy explanation: 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.

Correct choice is (c) SLOW query log

Easiest explanation: 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.

The CORRECT CHOICE is (a) error log

For explanation: 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.

The correct option is (b) column

For explanation I WOULD SAY: 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.

Right option is (a) IN and NOT IN

To explain I would say: 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.

Correct option is (a) True

The explanation: 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.

The correct answer is (b) False

Easiest explanation: MySQL supports shared MEMORY connections on Windows. This capability is disabled by default. In order to ENABLE it, the server is started with the ‘–shared-memory’ OPTION.

Right choice is (c) GRANT

For explanation I would say: If the named ACCOUNT exists, ‘GRANT’ modifies its privileges. If the account does not exist, ‘GRANT’ CREATES it with the given privileges. It is used to OBTAIN access privileges.

Correct answer is (a) user

The best I can EXPLAIN: The grant table ‘user’ stores the USERS who can connect to the SERVER and their GLOBAL privileges. ‘db’ has the database privileges RECORDS. ‘tables_priv’ has table privileges.

Correct ANSWER is (a) INNER JOIN

Easiest explanation: The joins ‘CROSS JOIN’ and ‘JOIN’ types are exactly similar to the ‘INNER JOIN’. The statements CONTAINING ‘INNER JOIN’ can replace it with ‘CROSS JOIN’ or ‘JOIN’ to GET exactly the same RESULT.

The correct answer is (a) WHERE

To elaborate: SOMETIMES the RESULT of a join is very large and is not desirable. In these cases, the results can be FILTERED with the help of the ‘WHERE’ clause which is followed by a set of conditions.

Correct choice is (d) CARTESIAN

Explanation: In ‘cartesian product’, each row of each table is combined with each row in every other table to PRODUCE all POSSIBLE combinations. This produces a very LARGE number of rows since the number is the product of rows.

Correct answer is (b) STATISTICS

To explain I WOULD SAY: The ‘STATISTICS’ table in the ‘INFORMATION_SCHEMA’ stores information about the table index characteristics. ‘INFORMATION_SCHEMA’ is RESPONSIBLE for STORING database metadata.

Right option is (a) True

The explanation: MySQL supports shared memory connections on Windows. This capability is disabled by DEFAULT. In order to enable it, the SERVER is STARTED with the ‘–shared-memory’ option.

Right OPTION is (b) –bind-address

Best explanation: If the server HOST has more than ONE IP number, the server can be SPECIFIED the one that it should USE when listening for connections by starting it with a ‘–bind-address’ option.

Correct answer is (b) –skip-networking

The EXPLANATION: On all the platforms the SERVER LISTENS on a network port for the TCP/IP connections, unless and until it is started with the –skip-networking option. The DEFAULT port NUMBER is 3306.

The correct OPTION is (a) True

The explanation is: SUPPOSE a table is created using the FOLLOWING statement: ‘CREATE TABLE my_tbl (…..) ENGINE = InnoDB;’. The engine name ‘InnoDB’ USED is always case insensitive.

Right option is (b) my_tbl.frm

To ELABORATE: Every time a table is created in MySQL, it creates a DISK FILE containing the format of the table. It has TWO components, namely, base NAME (here, ‘my_tbl’) and an extension (here, ‘frm’).

Right option is (C) MyISAM

For explanation: MySQL COMES with MULTIPLE storage engines. The DEFAULT storage ENGINE used is ‘MyISAM’. ‘EXAMPLE’ is the stub storage engine, NDB is the storage engine for MySQL Cluster.

Correct option is (C) db.opt

For explanation: 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.

Correct OPTION is (a) CREATE

Best explanation: 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.

The correct CHOICE is (a) True

The explanation is: 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.

The correct choice is (a) –verbose

The explanation is: The STATEMENT ‘mysqld –verbose –help’ is USED for FINDING out all the startup options that are supported by the SERVER. It is possible to run the server manually or AUTOMATICALLY.

Right answer is (a) .ERR

The EXPLANATION is: In Windows, by DEFAULT, the error log is LOCATED in the ‘C:\Program Files\MySQL\MySQL SERVER 5.7\data’ directory. It is the file with a suffix of .err, or may be specified by passing in the ‘–log-error’ option.

Correct choice is (a) SHOW CHARACTER SET

Easiest explanation: It is SIMPLE to determine the character sets and COLLATIONS that are AVAILABLE in MySQL. ‘SHOW CHARACTER SET’ shows the character sets while ‘SHOW COLLATION’ shows the collations.

The correct option is (d) COLLATE

For explanation I would SAY: The ‘COLLATE’ OPERATOR can be used to sort the string values according to a specific collation. For example, ‘SELECT COL FROM tbl ORDER BY col COLLATE latin1_swedish_ci’ SORTS by Swedish collation.

The correct choice is (a) lower_case_table_names

Easy explanation: In MySQL, by default the alias names are case SENSITIVE. An alias can hence be specified in any LETTER case, UPPER, LOWER or MIXED. If the variable ‘lower_case_table_names’ is non zero, the alias names of tables are not case sensitive.

The correct choice is (c) Operating system of MACHINE

To elaborate: 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.

The CORRECT option is (a) True

To elaborate: For the ‘REVOKE’ and ‘DROP USER’ statements, the server automatically re-reads the GRANT tables and no FLUSH PRIVILEGES statements are needed. Hence the grant tables are re-read.

The correct option is (B) blank

To EXPLAIN: Anonymous accounts have user NAME left blank. The main benefit of removing the anonymous user accounts is it significantly simplifies the TASK of SETTING up non-anonymous accounts.

The correct answer is (a) mysql_install_db

To elaborate: The data directory is initialized during the installation by ‘mysql_install_db’. If MYSQL is INSTALLED on LINUX from RPM packages then ‘mysql_install_db’ is RUN AUTOMATICALLY.