MySQL Interview Questions and Answers

SELECT CONCAT(first_name, ' ', middle_name, '', last_name) as name FROM `customers`;

These are most commonly used storage engines in MySQL are MyISAM and InnoDB.

Difference between MyISAM and InnoDB

• MyISAM no longer supports transactions, however, InnoDB supports transactions.
• MyISAM helps Table-level Locking, however, InnoDB supports Row-level Locking.
• MyISAM helps full-text search, however InnoDB does not.
• MyISAM is designed for the need for speed but InnoDB is designed for the most performance.
• MyISAM does now not aid overseas keys, but InnoDB helps foreign keys.
• We can use commit and rollback with InnoDB however not in MyISAM.
• MyISAM does no longer assist ACID (Atomicity, Consistency, Isolation, and Durability) however InnoDB supports the ACID property.
• In the InnoDB table, the AUTO_INCREMENT field is a section of the index but is now not in MyISAM.
• MyISAM stores its tables, data, and indexes in the disk area with the usage of a separate table name.FRM, desk name.MYD and table name.MYI however InnoDB stores its tables and indexes in a tablespace.

SELECT COUNT(*) FROM customers WHERE created_at BETWEEN '2022-03-24 17:32:05' AND '2022-03-25 17:32:05';

• Don't use "sp_" while creating a stored procedure.
• Don't use "*" while you are using the SELECT command. Also avoid unnecessary columns in SELECT clause.
• You can use table aliases while writing queries.
• Avoid the usage of the wildcard (%) at the starting of a predicate.
• DISTINCT and UNION need to be used solely if it is necessary.

SELECT * FROM customers WHERE name LIKE 'A%';

SELECT name, email, min(salary) as minSalary, max(salary) as maxSalary from employee;

SELECT name, salary FROM Employee AS emp1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee emp2 WHERE emp2.salary > emp1.salary);

SELECT * FROM `customers` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';