.: MySQL descriptive QuestionAndAnswer

1. What are MySQL clients?

Answer: The MySQL server is used to persist the data and provide a query interface for it (SQL). The mysql clients purpose is to allow you to use that query interface. The client package also comes with utilities that allows you to easily backup/restore data and administer the server. Here are some useful MySQL clients: 1 mysql — The MySQL Command-Line Tool 2 mysqladmin — Client for Administering a MySQL Server 3 mysqlcheck — A Table Maintenance Program 4 mysqldump — A Database Backup Program 5 mysqlimport — A Data Import Program 6 mysqlshow — Display Database, Table, and Column Information 7 mysqlslap — Load Emulation Client

2. What is storage engines?

A storage Engine is a software module that a database management system uses to create, read, update from a database. There are two types of storage engines in Mysql such as - Transactional and Non-transactional.

3. What are the advantages of Innodb table?

- Support for transactions (giving you support for the ACID property). - Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM. - Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables. - InnoDB is more resistant to table corruption than MyISAM. - Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.

4. What are the difference between primary key and unique?

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 null able within that column. There are two other ways to ensure a record’s uniqueness. Such as- a. Single-field primary keys b. Multiple-field primary keys. A column assigned the UNIQUE attribute will ensure that all values possess distinct values, except that NULL values are repeatable. We typically designate a column as UNIQUE to ensure that all fields within that column are distinct.

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

The GRANT and REVOKE commands are used to manage access privileges. GRANT command needs to assign new privileges to user or group of users where as 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;

6. What is the benefit of using prepare statement?

It is useful when we need to repeatedly execute the query with iteration using different parameters. There are several advantages:

Security

- We don't need to escape anything, just need to bind the parameters.

Correctness

- If you write WHERE $x = 4 you will get a syntax error if $x is null, but WHERE ? = 4 will work.

Performance

- prepared queries can be reused with different parameters, saving rebuilding the string, and also re-parsing on the server. The statement is sent to the SQL server, prepared only once ; and, then, can be executed several times -- which is great for performances (the statement is parsed only once, even if executed lots of times)

Maintainability

- the code is much easier to read. When concatenating strings to create the SQL it's easy to end up with lots of small pieces of SQL and PHP code intermingled. Using prepared statements encourages you to separate the SQL from determining the values of the variables.

7. What are the advantages of stored routine?

Stored routines have a number of advantages, which are: 1. Consistency 2. Performance 3. Security 4. Architecture.

8. What are the benefits of using triggers?

Triggers have many benefits: a. Audit trails: Special logging table that lets us quickly tabulate and display the results to an impatient executive. b. Validation: We can use triggers to validate data before updating the database. c. Referential integrity enforcement: Table relationships remain stable throughout the lifetime of a project

9. What are the advantages of using view?

Answer: - Views can be quite advantages for a number of reasons: - Simplicity: Saving the hassle of repeatedly querying multiple tables to retrieve this information. - Security: Quite certain some information is inaccessible to third parties, such as the SSNs and salaries of employees. - Maintainability: A view abstracts the gory details of a query.

10. What is cursor? Why is it used?

Answer: Iterating through a result set, known as a cursor, it allows us to retrieve each row in the set separately and perform multiple operations on that row without warring about affecting other rows in the set.

11. What is indexing in MySQL? What are the advantages and disadvantages of using indexes?

Answer: Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like primary key index, unique index, normal index also known as ("non-unique index", ordinary index, index without constraints") and full-text index. Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well.

Advantages of MySQL indexes

Generally speaking, MySQL indexing into database gives you three advantages:

Query optimization:

Indexes make search queries much faster.

Uniqueness:

Indexes like primary key index and unique index help to avoid duplicate row data.

Text searching:

Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.

Disadvantages of MySQL indexes

When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on. Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size. Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.

12. What is transaction?

Answer: A transaction is an ordered group of database operations that are treated as a single unit. Successful transaction will be committed and unsuccessful transaction will be rolled back.

13. How can you export data in mysql?


mysqldump -u root -p database_name>path_drive:file_name.sql

14. Difference between before trigger and after trigger?

Before trigger when validating or modifying data that we intend to insert or update. On the otherhand, an after trigger should be used when data is to be propagated or verified against other tables and for carrying out calculations.

16. What is the difference between fetch_row () and fetch_array()?

fetch_row(): fetch_row()function retrieves an entire row of data from result set, placing the values in an indexed array. fetch_array():This function is really just an enhanced version of fetch_row(), offering the opportunity to retrieve each row of the result set as an associative array, a numerically indexed array, or both.

17. What is binding variables?

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 read only.

18. What is the difference between MYISAM and InnoDB engine?

MYISAM 1. MyISAM handles nontransaction-safe tables. 2. MyISAM is very easy to setup because have not referential integrity. 3. MyISAM have FULLTEXT search indexes. 4. MyISAM does not support refrential integrity constraints or does not contain data integrity check. InnoDB 1. InnoDB handles transaction-safe(ACID compliant) tables and has commit and rollback capabilities to protect user data. 2. Its setup is complex because of referential integrity 3. It does not provide FULLTEXT search indexes. 4. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

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

--execute: Executes a query without having to actually enter the client interface. We can execute multiple Queries with this option by separating each with a semicolon. --force: When we used non-interactive mode mysql can read and execute queries found in a text file.

20. What are the purposes of INFORMATION_SCHEMA database?

INFORMATION_SCHEMA offers a solution for using typical SELECT queries to learn more about databases and various server settings. Consisting of 16 tables, it’s possible to learn about practically every aspect of installation.

21. Write the examples for the following mysql commands on the table.

a) SHOW b) CREATE c) INSERT d) UPDATE e) SELECT f) ALTER g) DESC h) DESCRIBE i) DROP j) DELETE

22. Name all the MySQL data types and attributes.

Answer: MySQL supported data type attributes are: 1. AUTO_INCREMENT 2. BINARY 3. DEFAULT 4. INDEX 5. NATIONAL 6. NOT NULL 7. NULL 8. PRIMARY KEY 9. UNIQUE 10. ZEROFILL

23. Classify the MySQL datatypes.

a) Data types in Date and time category: DATE, DATETIME, TIME, TIMESTAMP, YEAR b) Data types in Numeric category: BOOL/BOOLEAN, INGINT, INT, MEDIUMINT, SMALLINT, TINYINT, DECIMAL, DOUBLE, FLOAT c) Data types in String category: CHAR, VARCHAR, LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMTEXT, BLOG, TEXT, TINYBLOB, TINYTEXT, ENUM, SET

24. Briefly describe the following Engines:

a. MyISAM b. InnoDB c. MEMORY Answer: MyISAM: 1. MyISAM become MYSQL's default storage engine as of version 3.23. 2. MyISAM tables are operating system independent. 3. Capable of sorting more data, but at a cost of less storage space than counterpart. 4. Three MyISAM formats: static, dynamic, and compress are available. InnoDB: 1. Robust transactional storage engine 2. Working with large data stores. 3. It has been available to MySQL users since version 3.23 and effective solution for transactional applications. MEMORY: 1. To attain the fastest response time possible, the logical storage media is system memory. 2. Sorting table data in memory does indeed offer impressive performance. 3. If the mysqld daemon crashes, all MEMORY data will be lost.

25. Name all storage engines that are available in MySQL.

Answer: 1. MyISAM 2. IBMDB2I 3. InnoDB 4. MEMORY 5. MERGE 6. FEDERATED 7. ARCHIVE 8. CSV 9. EXAMPLE 10. BLACKHOLE

26. What is sub-query?

A subquery is a query within another query, also known as a nested query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries are used with the SELECT, INSERT, UPDATE, and DELETE statements.

27. What is data encryption?

Data encryption is the act of changing electronic information into an unreadable state by using algorithms or ciphers.

28. Briefly describe mysqladmin client

mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more

29. What is query caching?

Answer: Query caching, available with the version 4.0.1 release, greatly improves the performance of selection queries by storing query results in memory and retrieving those results directly, rather than repeatedly querying the database for the same result set.

30. When to use MyISAM and InnoDB?

Advantages

MyISAM:

- Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables. - Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. - Full-text indexing. - Especially good for read-intensive (select) tables.

InnoDB:

Disadvantages

MyISAM Limitations:

- No foreign keys and cascading deletes/updates - No transactional integrity (ACID compliance) - No rollback abilities - Row limit of 4,284,867,296 rows (232) - Maximum of 64 indexes per row

InnoDB Limitations:

- No full text indexing (Below-5.6 mysql version) - Cannot be compressed for fast, read-only

31. Discuss the stored routine's advantages and disadvantages.

Answer:

Advantages of Stored Routine:

• Consistency • Performance • Security • Architecture

Disadvantages of Stored Routine

• Performance • Capabilities • Maintainability • Portability

32. Discuss the two variants of stored routine.

Stored procedures:

Stored procedures support executing of SQL commands such as SELECT, INSERT, UPDATE, and DELETE. They also can set parameters that can be referenced later from outside of the procedure.

Stored functions:

Stored functions support execution only of the SELECT command, accept only input parameters, and must return one and only one value.

33. Write the general syntax of creating, executing and deleting a stored procedure.

Create Syntax Procedure:


CREATE PROCEDURE procedure_name([parameter[, ...]])
routine_body

Create Syntax Function:


CREATE FUNCTION function_name([parameter[, ...]])
RETURN type
routine_body

Executing Procedure:


CALL procedure_name([parameter[, ...]]);

Executing Function:


SELECT function_name([parameter[, ...]]);

Deleting Stored Routine:


DROP (PROCEDURE | FUNCTION) [IF EXISTS] routine_name

34. Write the general syntax of IF-ELSEIF-ELSE in stored procedure. Why use triggers?

General Syntax:


IF condition THEN statement_list
[ELSEIF condition THEN statement_list]
[ELSE statement_list]
END IF

Purpose of using triggers

a. Audit trails: Special logging table that lets us quickly tabulate and display the results to an impatient executive. b. Validation: We can use triggers to validate data before updating the database. c. Referential integrity enforcement: Table relationships remain stable throughout the lifetime of a project

35. Write the general syntax of creating and deleting a view. Write the life cycle of a MYSQL cursor.

General syntax of creating VIEW


CREATE VIEW view_name AS select_statement

General syntax of deleting View


DROP VIEW [IF EXISTS]  view_name;

Life cycle of a MySQL cursor in-this-tutorial-you

36. How to create, open, use and close a cursor? Write the general syntax

Answer: Syntax to create cursor:


DECLARE cursor_name  CURSOR FOR  select_statement;

Syntax to open cursor:


OPEN cursor_name;

Syntax to use cursor:


FETCH cursor_name INTO varname1 [, varname2...]

Syntax to close cursor:


CLOSE calc_bonus;

Q1.

Create two tables as follows: 1. Manufacturer (id (auto increment), name (varchar(50)), contact (text). 2. Product (id (auto increment), name (varchar(50)), price (double), manufacturer_id (INT(10)).

Answer:

my_db.sql


drop table if exists manufacturer;
create table manufacturer(
  id int(10) primary key auto_increment,
  name varchar(50),  
  contact text
);
 
insert into manufacturer(name,contact)values('APCL','08340830');
insert into manufacturer(name,contact)values('ISL',2345454');
insert into manufacturer(name,contact)values('IDB','9390033');
 
drop table if exists product ;
create table product (
  id int(10) primary key auto_increment,
  name varchar(50),  
  price double, 
  manufacturer_id int(10)
);
 
insert into product(name,price,manufacturer_id)values('Camera',5000,1);
insert into product(name,price,manufacturer_id)values('PC',20000,1);
insert into product(name,price,manufacturer_id)values('HRM',100000,2);
insert into product(name,price,manufacturer_id)values('ERP',3000000,2);
insert into product(name,price,manufacturer_id)values('Car',900000,3);

Q.2

a. Create an after delete trigger which will delete record(s) from the product table when any corresponding manufacturer_id are deleted from the manufacturer table. Answer: my_trigger.sql


delimiter //
drop trigger if exists ad_manufacturer//
create trigger ad_manufacturer after delete on manufacturer
for each row
begin
  delete from product where manufacturer_id=old.id;
end;
//
delimiter ;

b. Create a form to select manufacturer id and write php script to delete the record from Manufacturer table of selected manufacturer.

Answer


coming soon...

Q3.

a. Create a form and write php script to insert data into Manufacturer table. b. Create another form to insert product info in the product table and the form will contain a combo box which allows you to select available manufacture from manufacturer table.

Q4.

a. Create a view to display all records from the product table where price is greater than 5000. b. Write php script to display the data from the view.

Tuesday, January 19, 2016