mysql descriptive

Chapter 20: Web Services

1.       What are web services?

Web Services are typically application programming interfaces (APIs) or web APIs that are accessed via Hypertext Transfer Protocol (HTTP) and executed on a remote system hosting the requested services.

2.       What are the benefits of using web services?

The benefits of using web services are

·         The ability to treat software as a service

·         Significantly improved Enterprise Application Integration (EAI) processes

·         Global reusability

·         Ubiquitous accessibility.

3.       What is RSS feed?

RSS refers Really Simple Syndication. It offers a formalized means for encapsulating a web site’s content within an XML-based structure, known as a feed.

Chapter 21: Securing Your Web Site

4.       What is data encryption?

Encryption can be defined as the translation of data into a format that is intended to be unreadable by anyone except the intended party.

 

Chapter 25-29: MySQL

5.       What is query caching?

Query caching is one of the MySQL’s greatest speed enhancements. Simple and highly effective when enabled, allows MySQL to store SELECT queries, along with their corresponding results in memory.

6.       What is storage engine?

The storage engines manage data storage and index management for MySQL. The MySQL server communicates with the storage engines through a defined API. Example of some storage engines are: InnoDB, MyISAM, MEMORY etc.

7.       What are the advantages of Innodb table?

The advantages are:

·         It can update-intensive tables

·         Support transactional databases

·         Manage sensitive data

·         Capable of automatically recovering from a crash.

8.       What are the differences between primary key and unique?

a)      Each record of the table is identified by Primary key whereas unique key can’t.

b)      Primary key doesn’t support null value but unique key support repeatable null value.   

                                                                                                   

9.       What are the purposes of GRANT and REVOKE commands?

Answer: The GRANT and REVOKE commands are used to manage access privileges. GRANT command needs to assign new privileges to a user or group of users whereas the REVOKE command is responsible for deleting previously granted privileges from user or group of users.

Example:

mysql> GRANT select, insert ON library.book TO jone@localhost IDENTIFIED BY 'open123';

mysql> REVOKE insert ON library.book TO jone@localhost;

10.   What is the benefit of using prepare statement?

·         To help resolve the issues incurred by repeatedly executed queries, prepared statement is used.

·         To accomplish the repetitive tasks at a significantly lower cost of overhead, and with fewer lines of code.

11.   What are the advantages of stored routine?

·         Consistency –prevent redundant development process.

·         Performance- maintaining queries centrally. 

·         Security- access of sensitive data can be restricted.

12.   What are the benefits of using triggers?

·         Audit trails – able to create an additional special logging table. 

·         Validation – minimum-order threshold can be ensured.

·         Referential integrity Enforcement- table relationship can remain stable throughout the lifetime of the project using trigger.

13.   What are the advantages of using view?

·         Simplicity- prevents repeatedly querying multiple tables to retrieve information.

·         Security- provides security for secure data.

·         Maintainability- it is easier to maintain.

14.   What is cursor? Why is it used?

The pointer which is used to iterate through a result set is called cursor. It allows us to retrieve each row in the set separately and perform multiple operations on that row without affecting other row.

15.   What are the advantages of using indexes?

• Query optimization: database searches can be most efficiently executed when the target data is sorted.
• Uniqueness: guaranteeing uniqueness for each row.
• Text searching: it’s possible to optimize searching against even large amounts of text located in any field indexed as such.

    16.What is transaction?

     A transaction is an ordered group of database operations that are treated as a single unit.

       17.How can you export data in mysql?

We can export data in two ways:

·         mysqldump client is used to export existing table data, table structures, or both from the MySQL server.

·         SELECT * INTO OUTFILE "data.txt"

FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'

FROM company.sales;

16     Difference between before trigger and after?

A before trigger shouldn’t be used to enforce propagation or referential integrity. An after trigger should be used when data is to be propagated or verified against other tables and for carrying out calculations.

17     What is the difference between fetch_row () and fetch_array()?

·         The fetch_row() method is actually capable of retrieving each row of the result set as numerical  array but

·         fetch_array() method is capable to retrieving associative array, a numerical array, or both.

18     What is binding variables?

When a query has been prepared and executed, we can use bind variables to the retrieved fields by using  the bind_result() method.

Answer: Binding variables is a variable to which we can bind any table field to it so that if we update binding variable that will effect to table field directly. However binding can be one or two way. In two way binding we can read-write data to table field with binding variable and one way binding is readonly.

19     What is the difference between MYISAM and InnoDB engine?

The main difference is InnoDB supports transactions. You can do commit and rollback in InnoDB table. Whereas MyISAM doesn't support the transaction, once you issue command, can't rollback.

20     What are the functions of --execute and --force option of mysql?

--execute or -e query: Executes a query without having to actually enter the client interface.

--force or -f: This option causes execution to continue regardless of errors.

21     What are the purposes of INFORMATION_SCHEMA database?

The INFORMATION_SCHEMA offers a solution for using typical SELECT queries to learn more about databases and various server settings.