MySQL Interview Questions and Answers Database

This is a frequently asked question in MYSQL interview questions for freshers.  

PDO is PHP Data Objects. 

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.

The disabled_storage_engines configuration option defines which storage engines cannot be used to create tables or tablespaces. By default, disabled_storage_engines is empty (no engines disabled), but it can be set to a comma-separated list of one or more engines.

Expect to come across this popular question in MYSQL questions for interview.  

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set
To display the available character sets run the below query:

>SHOW CHARACTER SET;

MySQL is capable of doing these things 

1. Store strings using a variety of character sets.

2. Compare strings using a variety of collations.

3. Mix strings with different character sets or collations in the same server, the same database, or even the same table.

4. Enable specification of character set and collation at any level.

Latin1 is the default character set in MySQL.

A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (,). A consequence of this is that SET member values should not themselves contain commas.

SELECT orderNumber,

       requiredDate
FROM orders
WHERE requiredDate BETWEEN '2018-01-01' AND '2018-01-31';

The query selects orders whose required dates are in January 2018. The data type of the requireDate column is DATE, therefore, MySQL has to convert the literal strings: '2018-01-01' and '2018-01-31'into TIMESTAMP values before evaluating the WHERE condition.

SELECT orderNumber,
       requiredDate
FROM orders
WHERE requiredDate BETWEEN  CAST('2018-01-01' AS DATETIME)
                        AND CAST('2018-01-31' AS DATETIME);

>CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

And inserting the values 

>INSERT INTO myset (col) VALUES
 ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

what will be the output of this query?

>SELECT col FROM myset;

| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |

For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value appears once, with elements listed according to the order in which they were specified at table creation time.

It's no surprise that this one pops up often in MYSQL basic interview questions.  

MySQL supports two kinds of aliases which are known as column alias and table alias. Sometimes the names of columns are so technical that they make the query’s output very difficult to understand. To give a column a descriptive name, you use a column alias.

SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;

To give a column an alias, you use the AS keyword followed by the alias. If the alias contains space, you must quote it. You can use an alias to give a table a different name. You assign a table an alias by using the AS keyword as the following syntax.

table_name AS table_alias

The alias for the table is called a table alias. Like the column alias, the AS keyword is optional, so you can omit it.

A common question in MYSQL interview questions for experienced, don't miss this one.  

1. INNER JOIN: Returns rows when there is a match in both joined tables
2. LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.
3. RIGHT JOIN: Returns all rows from the right table, even if there are no matches in the left table.
4. FULL JOIN: It combines the results of both left and right outer joins.

For INNER joins,  the order doesn't matter. The queries will return the same results, as long as we change your selects from SELECT * to SELECT a.*, b.*, c.*.

For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters.

First, outer joins are not commutative, so a LEFT JOIN b is not the same as b LEFT JOIN a

One of the most frequently posed MYSQL query interview questions, be ready for it.  

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.

A user account can be granted roles, which grants to the account the privileges associated with each role. This enables the assignment of sets of privileges to accounts and provides a convenient alternative to granting individual privileges, both for conceptualizing desired privilege assignments and implementing them.

CREATE ROLE and DROP ROLE enable roles to be created and removed.

CREATE ROLE ‘admin’, ‘devops’;
DROP ROLE ‘admin’, ‘devops’;

GRANT and REVOKE enable privilege assignment and revocation for user accounts and roles.

GRANT ALL ON db1.* TO ‘admin’@’localhost’;
REVOKE INSERT ON *.* FROM ‘admin’@’localhost’;

SHOW GRANTS displays privilege and role assignments for user accounts and roles.

SHOW GRANTS FOR 'admin'@'localhost';