15 Mysql Descriptive Question for exam and interview
1. What is storage engines?
Ans. A “storage engine”
is a handler that manages a certain kind of table.
13. What are the
advantages of Innodb table?
Ans. Advantages of Innodb
table are:
• Update-intensive
tables: The InnoDB storage engine is particularly adept at handling multiple
simultaneous update requests.
• Transactions: The
InnoDB storage engine is the only standard MySQL storage engine that supports transactions,
a requisite feature for managing sensitive data such as financial or user
registration information.
• Automated crash
recovery: Unlike other storage engines, InnoDB tables are capable of automatically
recovering from a crash. Although MyISAM tables can also be repaired after a
crash, the process can take significantly longer.
2. What are the difference between primary key and unique?
Ans. Primary key: The PRIMARY KEY attribute is
used to guarantee uniqueness for a given row. No values residing in a column
designated as a primary key are repeatable or nullable within that column.
Unique: A column assigned the UNIQUE attribute
will ensure that all values possess distinct values, except that NULL values
are repeatable.
3. What are the purposes of GRANT and REVOKE commands?
Ans. The GRANT and REVOKE commands are used to manage
access privileges. The GRANT and REVOKE commands offer a great deal of granular
control over who can work with practically every conceivable aspect of the
server and its contents, from who can shut down the server to who can modify
information residing within a particular table column.
4. What
is the benefit of using prepare statement?
Ans. Prepared statements eliminate overhead and inconvenience
when working with queries intended for repeated execution, as is so often the
case when building database-driven web sites. Prepared statements
also offer another important security-related
feature in that they prevent SQL injection attacks.
5. What are the advantages of stored routine?
Ans. Stored routines have
a number of advantages, the most prominent of which are highlighted here:
1. Consistency 2. Performance
3. Security 4. Architecture
6. What are the benefits of using triggers?
Ans. The benefits of using
triggers are:
- Preventing corruption
due to malformed data.
- Enforcing business
rules, such as ensuring that an attempt to insert information about a
product into the product table includes the identifier of a manufacturer whose
information already resides in the manufacturer table.
- Ensuring database
integrity by cascading changes throughout a database, such as removing all
products associated with a manufacturer that you’d like to remove from the
system.
7. What are the advantages of using view?
Ans. The advantages of using view are:
1. Simplicity 2. Security 3. Maintainability
8. What is cursor? Why is it used?
Ans. Operating in a fashion similar to an array
pointer, a cursor gives us the ability to swiftly navigate database result
sets. It allows us to retrieve each row in the set separately and perform multiple
operations on that row without worrying about affecting other rows in the set.
We use it, because when using cursors you’ll need to keep the following restrictions in
mind:
Server-side, Read-only, Asensitive, Forward-only.
9. What are the advantages of using indexes?
Ans. The advantages of
using indexes are Query optimization, Uniqueness, Text searching.
22. What is transaction?
Ans. A transaction is an ordered group of
database operations that are treated as a single unit. A transaction is deemed successful
if all operations in the group succeed, and is deemed unsuccessful if even a
single operation fails.
10. Difference between before trigger and after trigger?
Ans. Before trigger: before trigger is used when
validating or modifying data that you intend to insert or update. A before
trigger shouldn’t be used to enforce propagation or referential integrity,
because it’s possible that other before triggers could execute after it,
meaning the executing trigger may be working with soon-to-be-invalid data.
After trigger: An after trigger should be used
when data is to be propagated or verified against other tables, and for
carrying out calculations, because we can be sure the trigger is working with
the final version of the data.
11. What are the five items of the $_FILES Array?
Ans. The items of the
$_FILES Array are:
- $_FILES['userfile']['error']
- $_FILES['userfile']['name']
- $_FILES['userfile']['size']
- $_FILES['userfile']['tmp_name']
- $_FILES['userfile']['type']
12. What is binding variables?
Ans. After a query has been prepared and
executed, you can bind variables to the retrieved fields by using the bind_result()
method. Its prototype follows:
class mysqli_stmt {
boolean bind_result(mixed &var1 [, mixed
&varN])
}
13. What is the difference between MYISAM and InnoDB engine?
Ans. MYISAM: MyISAM became MySQL’s default
storage engine as of version 3.23.1 It resolves a number of deficiencies suffered
by its predecessor (ISAM). The MyISAM storage engine is particularly adept when
applied to the following scenarios:
1. Select-intensive tables & 2. Insert-intensive
tables.
InnoDB: InnoDB is a robust transactional storage
engine released under the GNU General Public License (GPL) that has been under
active development for over a decade. InnoDB tables are ideal for the following
scenarios, among others:
1. Update-intensive tables, 2. Transactions &
3. Automated crash recovery.
14. What are the functions of --execute and --force option of mysql?
Ans. –execute option: This option executes a
statement and quit.
--force option: This option used to continue even
if an SQL error occurs.
15. What are the purposes of INFORMATION_SCHEMA database?
Ans. INFORMATION_SCHEMA is
more portable than the various SHOW statements, which are entirely MySQL-specific.
0 comments:
Post a Comment