Explore topic-wise InterviewSolutions in .

The CORRECT choice is (a) CREATE ROUTINE

Easy EXPLANATION: The ‘CREATE ROUTINE’ privilege must be GIVEN for the database in order to create a stored function or procedure. Stored functions and procedures always belong to a particular database.

Correct answer is (b) sampdb.PI()

To EXPLAIN: When a stored FUNCTION is DEFINED with the same NAME as a built in function, the function name should be qualified with the database name when it is invoked. The function ‘PI()’ is built in.

Right choice is (a) 0

Best explanation: In MySQL, UNLIKE the stored functions, the stored PROCEDURES cannot RETURN values. They can be used to PERFORM calculations or produce the result sets PASSED back to the clients.

The correct CHOICE is (c) TRADITIONAL

Best explanation: The ‘TRADITIONAL’ mode is used to enable the strict mode and all of the additional restrictions. It is done by the command SET sql_mod = ‘TRADITIONAL’; The other SQL modes have other FUNCTIONS.

Right answer is (B) ERROR_FOR_DIVISION_BY_ZERO

For explanation I WOULD SAY: To enable the check for divide by zero errors int all the storage engines, the SQL MODE NAMED ‘ERROR_FOR_DIVISION_BY_ZERO’ can be enabled. This is done by using SET sql_mode = ‘mode_name’.

The correct option is (d) SHOW WARNINGS

Easiest explanation: In MYSQL, the default value conversions are reported as warnings for INSERT, REPLACE and UPDATE STATEMENTS. The SHOW WARNINGS command is USED after executing one of those statements to see the warning messages.

Correct option is (a) True

The explanation is: A PROCEDURE that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this STATEMENT with a version of MYSQL different from the one used to create the table can damage the table.

Correct answer is (a) 0

The explanation is: With a slow shutdown InnoDB performs a full purge and change buffer MERGE before SHUTTING down. This ENSURES that data files are fully PREPARED in case of file format differences.

The correct choice is (a) inplace

To EXPLAIN I WOULD say: The LOGICAL upgrade involves exporting existing data from the old MySQL version using MYSQLDUMP, installing the new MySQL version, loading the dump file into the new MySQL version.

Correct answer is (B) logical

To ELABORATE: The in-place upgrade involves SHUTTING down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the EXISTING data directory, and running mysql_upgrade.

Right answer is (C) ;

The explanation: By default, mysql itself recognizes the semicolon as a statement DELIMITER, so the delimiter must be REDEFINED temporarily to cause mysql to PASS the entire stored program DEFINITION to the server.

Correct OPTION is (b) False

The best I can explain: Stored PROGRAMS improve database SECURITY because controlled ACCESS can be enabled to sensitive DATA by appropriate selection of the privileges a program has when it executes.

The correct ANSWER is (b) Events

For explanation: In MySQL, the events EXECUTE on a time ACTIVATED basis according to a schedule. Triggers are defined to execute when the table is modified via INSERT, DELETE or UPDATE statements.

Correct answer is (c) DOUBLE

Easy explanation: The REAL datatype, by default, is for the DOUBLE TYPE. FLOATING point TYPES can be defined as UNSIGNED. This eliminates the NEGATIVE end of the type ranges. MySQL has a variety of default types.

The CORRECT choice is (d) variable

The BEST I can explain: All the numeric datatypes used in MYSQL vary over the fields. However, the MAXIMUM size is fixed. Unlike the other numeric types, the size of the BIT datatype depends on the length of the FIELD.

The correct answer is (C) 3

The EXPLANATION is: The numeric DATATYPES used in MySQL VARY over ranges. There are a variety of INT types in the type SPECIFICATION. TINYINT requires 1, SMALLINT requires 2 bytes and MEDIUMINT requires 3.

The correct choice is (a) RELAY log index

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

Right CHOICE is (a) –basedir

Easiest explanation: The option ‘–basedir’ has the purpose to specify the pathname to the root directory of the MySQL installation. The pathname to the data directory is SPECIFIED by the option ‘–datadir’.

The correct choice is (c) LINESTRING

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

The CORRECT answer is (b) TIME

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

Correct choice is (a) VARCHAR

Best EXPLANATION: In MySQL, there is a wide variety of STRING datatypes for use. Strings can even HOLD image and SOUND data. All four options are string type names. VARCHAR represents a variable length non BINARY string.

Right choice is (d) Consistency

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

The correct choice is (d) Atomicity

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

Correct choice is (B) rollback

The BEST I can EXPLAIN: Whenever an error occurs during a transaction, it is generally TAKEN to the state prior to the beginning of transaction execution. This is known as rollback. It is a SET of undo operations.

The correct answer is (c) 2

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

The correct choice is (a) time_zone

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

Correct option is (b) False

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

The correct OPTION is (a) True

Easiest EXPLANATION: 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 the server startup.

Right answer is (a) abcd and 1633837924

To explain: In the NUMERIC contexts, each HEXADECIMAL number constant is treated as a number. WITHOUT adding a zero to it, a hexadecimal literal is treated as a string. HENCE it is displayed as abcd.

Correct answer is (c) 65536

For explanation I would say: In MySQL, STRING values are specified using the standard SQL notation x’VAL’. Here, val is pairs of HEXADECIMAL digits (‘0’ through ‘ 9 ’ and ‘ a ’ through ‘ f ’). x’FFFF’ is 65536 in decimal.

Correct ANSWER is (c) NO_BACKSLASH_ESCAPES

Explanation: To turn off the SPECIAL meaning of backslash in MySQL, and treat it as an ordinary character, the SQL mode named NO_BACKSLASH_ESCAPES is enabled. The ESCAPE sequence is TREATED as characters.

The correct choice is (c) 2

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

Correct choice is (a) KEY cache

Best explanation: For the INDEX processing, ‘MyISAM’ manages its own key cache, which is the most important configurable resource for the MyISAM storage engine. It is USED for index BASED retrievals and sorts.

Correct option is (b) –enable-local-infile

The EXPLANATION: At build TIME LOCAL CAPABILITY for the client library can be ENABLED or disabled by default. The ‘–enable-local-infile’ or ‘–disable-local-infile’ option is used when running CONFIGURE.

Correct answer is (a) True

Explanation: The ‘LOCAL’ capability for the ‘LOAD DATA’ STATEMENT does not need to be ENABLED explicitly. It can be controlled at BUILD time and at RUNTIME. This can be achieved in two ways.

The correct CHOICE is (c) 65

The best I can explain: In MySQL, for the expressions CONTAINING only EXACT VALUES and where ONE or more values have a fractional part, the DECIMAL arithmetic is used with digits of precision equal to 65.

The CORRECT CHOICE is (b) 64

Easy explanation: In MYSQL, for the expressions containing only exact values that are all integers, the evaluation USES BIGINT (64 – bit) precision. MySQL evaluates expressions using exact/approximate math.

The correct OPTION is (a) -1.58

The explanation is: In MYSQL, the SCIENTIFIC notation for real numbers is possible. Some VALUES are REPRESENTED as floating point numbers in scientific notation consisting of a mantissa and exponent.

Correct choice is (a) table_cache

To explain I would say: 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 option is (a) key cache

The BEST explanation: For the index processing, ‘MyISAM’ manages its own key cache, which is the most important CONFIGURABLE RESOURCE for the MyISAM STORAGE engine. It is USED for index based retrievals and sorts.

Right answer is (d) MEMORY

Easiest EXPLANATION: The configuration options for the storage ENGINE ‘MEMORY’ are always built. The runtime options for ‘MEMORY’ are also always enabled. For the others, runtime options are explicitly enabled byt SPECIFICATIONS.

Correct choice is (d) MyISAM

To explain I would say: The configuration options for the storage ENGINE ‘MyISAM’ are ALWAYS BUILT. The runtime options for MyISAM are also always enabled. For the OTHERS, runtime options are explicitly enabled.

Right option is (a) True

The BEST explanation: In MySQL, a full-text search is based on the FULLTEXT indexes. A FULLTEXT index can be created both for a single column and for MULTIPLE columns. SEARCHING is POSSIBLE across columns.

Right OPTION is (a) MyISAM

For explanation: Full-text searches are based on the FULLTEXT indexes. They can be created for MyISAM tables only. In MySQL, a full text search ENABLES to look for WORDS or PHRASES without using pattern-matching operations.

The CORRECT answer is (c) Query EXPANSION

The explanation is: The query expansion search occurs in two PHASES. The natural language search is applied in the first phase. Query expansion search is ONE of the three modes of fulltext searching.

Correct option is (a) ALL

Easiest explanation: The KEYWORD ‘ALL’ used along with ‘UNION’ is not SYNONYMOUS with just the ‘UNION’ statement. It PRODUCES the duplicate ROWS, if they EXIST, from the combination of the two tables in the SELECT query.