Explore topic-wise InterviewSolutions in .

The correct ANSWER is (b) INNODB

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

The correct option is (d) cascaded

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

Right answer is (b) False

To explain: A foreign KEY is the ONE which declares that an index in one table is related to that in ANOTHER and place constraints. It is USEFUL for handling deletes and updates along with row entries.

Right option is (C) foreign

The explanation is: In MySQL, a foreign key is the ONE which facilitates INDEX relations ACROSS tables. It declares that an index in one table is related to that in another and place constraints.

Right answer is (d) transaction effects are recorded permanently

To EXPLAIN I would say: The ‘durability’ property of a transaction is one of the four elementary characteristics that are EXPECTED for a database transaction to maintain THROUGHOUT. The others are Isolation, Atomicity and CONSISTENCY.

The correct ANSWER is (a) True

For explanation I would say: A transaction can either execute successfully or it can be ROLLED back to the point of initiation. Once a transaction COMPLETES, changes are recorded with COMMIT. If it fails, changes are rolled back.

The CORRECT option is (b) START TRANSACTION

To explain I would say: By default, MySQL runs in the AUTOCOMMIT mode. This means that the changes performed by individual statements or operations on tables are automatically COMMITTED to the database IMMEDIATELY.

The correct option is (c) one transaction does not affect the other

The EXPLANATION: The ‘isolation’ PROPERTY of a transaction is one of the FOUR elementary CHARACTERISTICS that are expected for a database transaction to maintain THROUGHOUT. The others are Atomicity, Consistency and Durability.

The CORRECT CHOICE is (d) Consistency

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

Correct ANSWER is (d) Atomicity

Easiest EXPLANATION: 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 option is (c) commit

To explain I WOULD say: In a TRANSACTION, EITHER all the STATEMENTS are executed SUCCESSFULLY or neither of them are successful. This is facilitated by the commit and rollback capabilities. Commit is made after a transaction.

The correct choice is (b) transactions

The explanation is: In MySQL, a transaction is a SET of SQL statements that are RUN as a single unit. They can be canceled when required, but they can EITHER execute SUCCESSFULLY or neither of them EXECUTES.

The correct OPTION is (a) TRUNCATE

The best explanation: The ‘TRUNCATE’ keyword in MySQL is used to delete all the rows from the table and ALSO FREE the space containing the table. Its syntax is: TRUNCATE TABLE my_table. This deletes all rows from my_table.

The correct OPTION is (b) SET

To explain I would say: The ‘UPDATE’ statement in MYSQL has its own syntax. The ‘SET’ keyword is followed by an assignment LIST which INDICATES the set of columns whose value needs to be updated with a specified value.

The correct OPTION is (a) Data Manipulation Language

The explanation is: The ‘UPDATE’ statement in MySQL is a ‘Data Manipulation Language’ statement. It PERFORMS EDITS on real tables present or loaded directly from the stored database. It is USED in similar lines with ‘DELETE’.

Right answer is (a) MySQL

To explain: In MySQL, ‘Views’ are treated a LITTLE DIFFERENTLY than ORACLE, PostgreSQL and SybaseSQL since they support materialized views and MySQL does not USE the materialized view.

The CORRECT answer is (d) CHECK TABLE

For explanation: A ‘View’ is created in MySQL by WRITING the clause ‘CREATE VIEW’ followed by the view NAME. Once a view is dropped or altered after being created, it can be checked by the ‘CHECK TABLE’ clause.

Right answer is (C) index

For explanation I WOULD say: In MYSQL, ‘Views’ act as virtual tables. It is not possible to CREATE indexes on a view. However, they can be used for the views that are processed USING the merge algorithm.

Correct choice is (d) DROP

To explain I WOULD say: In MySQL, ‘Views’ ACT as virtual tables. Many operations can be PERFORMED on views like the operations on tables. A VIEW can be created, displayed and dropped. They can also be used to PERFORM calculations.

The correct answer is (b) False

Explanation: In MySQL, some ‘Views’ are updatable. The OPERATORS ‘INSERT’, ‘DELETE’ and ‘UPDATE’ can be used. They can be operated on the ‘Views’ to MAKE changes to it. A table can be created and SET as a VIEW.

Right option is (B) JOIN

Best explanation: In MySQL, a ‘VIEW’ can refer to multiple tables. This makes it a lot simpler to execute the queries involving joins. When a selection is MADE from the view, the join is EXECUTED producing the results.

The correct ANSWER is (a) UNION ALL

Easiest explanation: PERFORMING a ‘SELECT’ operation on a ‘MERGE’ table is LIKE performing ‘UNION ALL’. This means that duplicate row results are not REMOVED. ‘SELECT DISTINCT’ is like ‘UNION’ or ‘UNION DISTINCT’.

Correct CHOICE is (B) MERGE

The explanation is: When there is a need to RUN a ‘UNION TYPE’ operation on a MYISAM table that has the same structures, a ‘MERGE’ table is set up for it. After this, the queries are performed on this table.

The correct ANSWER is (a) LIMIT

Easiest explanation: When there is a need to put a limit to the number of rows returned by the ‘UNION’ OPERATION, the STATEMENT ‘LIMIT’ is appended to the ‘SELECT’ queries which are JOINED by the ‘UNION’ operations.

The CORRECT answer is (B) ORDER BY

Easiest explanation: The ‘ORDER BY’ clause is used ALONG with the ‘UNION’ statement to sort a ‘UNION’ result as a whole. It is PLACED after the last ‘SELECT’ statement which is kept in parentheses.

Right option is (b) False

The explanation: When there is a mixture of ‘UNION’ or ‘UNION DISTINCT’ STATEMENTS with the ‘UNION ALL’ statements, any distinct union operation TAKES precedence over any ‘UNION ALL’ operations to its LEFT.

The correct OPTION is (a) ALL

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

The CORRECT answer is (d) DISTINCT

For explanation: The keyword ‘DISTINCT’ used along with ‘UNION’ is SYNONYMOUS with just the ‘UNION’ statement. It produces only the distinct rows from the combination of the TWO tables in the SELECT QUERY.

The CORRECT answer is (d) UNION

The best EXPLANATION: The ‘UNION’ operator is used for combining the results of various ‘SELECT’ queries into one. For EXAMPLE, ‘SELECT a FROM table1 UNION SELECT a FROM table2;’ produces the results from TABLES table1 concatenated with that of table2.

Correct CHOICE is (B) ==

To EXPLAIN: The OPERATORS =, < >,>,>=,<, and<= perform relative VALUE comparisons in MySQL. ‘==’ is not a valid comparison operator in MySQL. Such operators are useful in filtering information from a table.

Correct choice is (a) CORRELATED

The best I can EXPLAIN: An uncorrelated subquery contains REFERENCES to the values from the outer QUERY. So, it is dependent on it. Therefore, a correlated subquery cannot be EXECUTED by itself as a separate statement.

Correct OPTION is (a) True

The best explanation: MySQL has two kinds of subqueries, namely, uncorrelated SUBQUERY and correlated subquery. An uncorrelated subquery does not contain any reference to the VALUES from the outer QUERY.

Correct option is (a) SOME

Explanation: The OPERATORS ‘SOME’, ‘ALL’ and ‘ANY’ perform operations on COLUMNS. They can filter column results. They are used in conjunction with a COMPARISON operator in order to test the RESULT of a column SUBQUERY.

The correct OPTION is (b) column

The best EXPLANATION: 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.

Correct OPTION is (a) IN and NOT IN

The best EXPLANATION: 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.

Right option is (b) False

The best 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 (a) SCALAR

Explanation: The subqueries in MySQL can return different types of information. A scalar query returns a single VALUE. Column subquery return a single column and ROW subquery returns a single row.

The correct answer is (c) subquerying

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

The correct option is (d) RIGHT JOIN

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

Right choice is (b) SELF

To EXPLAIN I would say: JOINING a table to itself in a database is called ‘self-join’. When a self-join is being PERFORMED, the table is being used multiple times within the QUERY and a table name qualifier is unnecessary.

Correct answer is (c) OUTER JOIN

Best EXPLANATION: The ‘inner join’ only DEALS with rows where a match can be found in both tables. The ‘LEFT JOIN’ and ‘RIGHT JOIN’ TYPES are ‘OUTER JOIN’ types which DIFFER from inner joins in this sense.