MySQL Interview Questions

SQL (Structured Query Language) is a standardized computer programming language which is used to maintain relational databases and perform data manipulation as well.SQL skills are very crucial in most of the job roles. The following set of MySQL interview questions covers the common MySQL interview questions asked. It will guide you to get a better understanding of the concepts related to SQL, Oracle, Database and Server.

  • 4.6 Rating
  • 20 Question(s)
  • 30 Mins of Read
  • 6165 Reader(s)

Advanced

A stored routine is either a procedure or a function. Stored routines are created with the CREATE PROCEDURE and CREATE FUNCTION statements 

DELIMITER //
CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
DELIMITER ;
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.

MySQL supports two kinds of aliases which are known as column alias and table alias

Sometimes the names of columns are so technical that 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 table alias. Like the column alias, the AS keyword is optional so you can omit it.

  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

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 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';

Beginner

CREATE TABLE [IF NOT EXISTS] table_name(
    column_list
) ENGINE=storage_engine

CREATE TABLE `test`.`car` ( `id` INT(12) NULL AUTO_INCREMENT , `name` VARCHAR(20) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Show create table statement shows the CREATE TABLE statement that creates the named table.

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.

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.

Description

SQL (Structured Query Language) is a standardized computer programming language which is used to maintain relational databases and perform data manipulation as well. It is used to query, insert, update and modify data. 

SQL skills are very crucial in most of the job roles. The following set of MySQL interview questions covers the common MySQL interview questions asked. It will guide you to get a better understanding of the concepts related to SQL, Oracle, Database and Server.

Prepare well with the following MySQL interview questions for freshers as well as experienced to crack your interview. These questions discuss the basic as well as the advanced concepts in MySQL, opening a gateway to your dream job! Start your preparation today!
 

Levels