Explore topic-wise InterviewSolutions in .

Right option is (c) MEDIUMINT

To explain I would say: In order to facilitate the use of the code WRITTEN for SQL IMPLEMENTATIONS (reusability of code) from other VENDORS, MySQL maps the DATA types from the other vendor types to the APPROPRIATE MySQL type.

The correct CHOICE is (b) MEDIUMINT UNSIGNED

To elaborate: Since the range starts from the positive value 1, it is best to KEEP the DATATYPE as UNSIGNED. The maximum value is of the order of 10^5, so a MEDIUMINT datatype is WELL suited.

The correct option is (a) 0

Explanation: In MySQL, the ‘AUTO_INCREMENT’ values are a SEQUENCE of unique values ASSIGNED to the columns such that they INCREASE monotonically. They begin with the value 1 and INCREMENT by a unit.

Correct answer is (a) True

The best EXPLANATION: The ‘AUTO_INCREMENT’ columns must be NOT NULL. If the NOT NULL is omitted, MySQL ADDS it automatically. AUTO_INCREMENT column VALUES BEGIN with 1 and increase monotonically after it.

Correct ANSWER is (d) TEXT

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

The correct CHOICE is (d) BIGINT

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

Correct choice is (B) False

The best I can explain: 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.

Right OPTION is (d) DECIMAL

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

Correct answer is (b) INT

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

The correct choice is (c) 97

The best explanation: The hexadecimal constants are TREATED as binary strings unless the context indicates a NUMBER. In the STRING contexts, each pair of hexadecimal digits is converted to a character, then the RESULT is used as a string.

Right choice is (a) !, ^, <<, XOR

Best explanation: When MySQL EVALUATES an expression, it LOOKS at the operators to determine the order in which it should group the terms of the expression. Some operators have higher PRECEDENCE, and evaluated earlier than OTHERS.

Right answer is (c) NULL

The BEST explanation: In MYSQL, any pattern matching that is performed with the ‘NULL’ operand, fails. Expressions LIKE: ‘abcdef’ LIKE NULL, NULL LIKE ‘%’, all result into the value NULL. NULL is not USED to perform comparisons.

Correct option is (a) True

Easiest explanation: The ‘BETWEEN ……. AND’ clause is USED to RETURN a boolean value, if the GIVEN operand value lies between the values specified by the ‘AND’ clause. The RANGE endpoints are INCLUSIVE.

Right option is (c) abcxyz

To explain I would SAY: If the SQL mode PIPES_AS_CONCAT has been enabled, the SQL standard ‘||’ operation for STRING concatenation becomes valid in MySQL. Both OPERANDS are CONCATENATED to GIVE ‘abcxyz’.

Correct choice is (b) 0

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

Correct CHOICE is (b) 2

The BEST I can explain: 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.

Correct OPTION is (b) 1

Explanation: In the MYISAM TABLES, whenever a table is truncated with the ‘TRUNCATE TABLE’ clause, the ‘AUTO_INCREMENT’ counter again GETS reset. It begins with the value ONE and increases monotonically.

Right ANSWER is (B) 13

The explanation: In the MyISAM tables, AUTO_INCREMENT sequences normally are monotonic. The VALUES in an automatically GENERATED series are strictly INCREASING. They are not reused when rows are deleted.

Correct choice is (b) 1

To elaborate: When the row containing the LARGEST value in an AUTO_INCREMENT column is deleted, that value is REUSED the next time a NEW value is GENERATED. In this case the SEQUENCE number is 1.

Correct option is (d) 101

To elaborate: If the NEW value is larger than the current NEXT sequence number, then the sequence is RESET to CONTINUE with the next value after that for the following rows. In this WAY, “bumping up” the counter is done.

The CORRECT CHOICE is (a) True

Easiest EXPLANATION: The ‘LAST_INSERT_ID()’ is tied only to AUTO_INCREMENT values that are GENERATED during the current CONNECTION to the server. It is not affected by AUTO_INCREMENT tied with the other clients.

Right choice is (b) 0

Easy EXPLANATION: The ‘LAST_INSERT_ID()’ FUNCTION RETURNS zero when no ‘AUTO_INCREMENT’ value has been generated during the current connection with the server. It is TIED to the current connection.

The correct CHOICE is (a) LAST_INSERT_ID()

EASIEST explanation: The value of most recently generated SEQUENCE number can be obtained by CALLING the LAST_INSERT_ID() function. This enables to reference the AUTO_INCREMENT value in the subsequent statement.

Right ANSWER is (b) 1

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

Right answer is (a) True

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

Right choice is (a) True

To ELABORATE: The SQL mode ‘STRICT_ALL_TABLES’ TURNS on the stricter checking of inserted or the UPDATED data values. This can ALSO be done with the ‘STRICT_TRANS_TABLES’ mode for transactionals.

Right choice is (b) NO_ZERO_DATE

For explanation: The ‘NO_ZERO_DATE’ PREVENTS the entry of the ‘zero’ DATE value in strict mode. In MySQL, to suppress errors, the IGNORE KEYWORD is used with INSERT or UPDATE statements.

The correct ANSWER is (d) IGNORE

To elaborate: In MySQL, to suppress errors, IGNORE keyword is used with INSERT or UPDATE statements. With the IGNORE clause, statements that would RESULT in an error DUE to invalid values result only in a WARNING.

Correct option is (b) ALLOW_INVALID_DATES

The best explanation: In MYSQL, it is also possible to SELECTIVELY weaken the strict mode at some places. If the ALLOW_INVALID_DATES SQL mode is enabled, MySQL doesn’t perform full checking of the DATE parts.

The CORRECT answer is (C) TRADITIONAL

To explain: 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

To EXPLAIN: 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’.

Correct option is (d) SHOW WARNINGS

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

Right choice is (a) True

To elaborate: For the numeric or TIME columns, the values that are outside the LEGAL range are CLIPPED to the nearest endpoint of the range. The resulting VALUE is stored. This is the method to handle DEFAULTS for NUMERICS.

Correct option is (a) True

The best I can explain: MySQL works with the SPATIAL values in three formats. They are the internal FORMAT, the Well-Known TEXT (WKT) and the Well-Known Binary(WKB) formats for the spatial DATATYPES.

The correct OPTION is (a) True

To elaborate: BLOB or TEXT columns sometimes can be indexed, depending on the STORAGE engine. MEMORY tables do not support BLOB and TEXT indexes. The MEMORY engine does not support BLOB or TEXT.

Right CHOICE is (b) 65535

Best explanation: The allowable range of M for VARCHAR(M) SYNTACTICALLY is 1 to 65535, but the effective number of the maximum CHARACTERS is less than 65535 since MySQL has a maximum row SIZE of 65,535 BYTES.

Right ANSWER is (c) 64

Explanation: The SET type specification is very useful for storing a collection a ordered and DISTINCT elements. The STORAGE size REQUIRED for this datatype is variable and dependent on the cardinality.

The correct answer is (C) DOUBLE

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

Correct option is (d) variable

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

Right option is (c) 3

Easy explanation: 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 (b) ±1.7976931348623157E+308

The explanation: In MySQL, all the datatypes have their own RANGES. Data TYPES give an idea of the kind of values and the ranges that a variable is allowed to STORE. The maximum NON ZERO value for DOUBLE is ±1.7976931348623157E+308.

Correct option is (a) 16 bit

For explanation I would say: In MySQL, the numeric DATATYPES are many. A numeric datatype USED in MySQL has its own RANGE in terms of bits. The SMALLINT datatype stores signed and unsigned VALUES of 16 bits.