\What is MySQL?
        MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to access, manage and manipulate data stored in the database. It is a popular choice for web developers due to its reliability, scalability, and ease of use.


What are the different data types in MySQL?
        MySQL supports a wide range of data types including:
  • Numeric types: INT, BIGINT, FLOAT, DOUBLE, DECIMAL, etc.
  • Date and time types: DATE, TIME, DATETIME, TIMESTAMP, YEAR, etc.
  • String types: CHAR, VARCHAR, BLOB, TEXT, ENUM, SET, etc.
  • Spatial data types: GEOMETRY, POINT, LINESTRING, etc.

What is normalization in MySQL?
        Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. There are several levels of normalization, known as normal forms, including First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and so on. Each normal form has a set of rules that must be followed in order to ensure that the data is organized efficiently and accurately.

What is a foreign key in MySQL?
        A foreign key is a column or set of columns in a table that refers to the primary key of another table. It is used to establish a relationship between two tables in a relational database, and is typically used to enforce referential integrity. In MySQL, foreign keys are created using the FOREIGN KEY constraint.

What is a stored procedure in MySQL?
        A stored procedure is a precompiled SQL statement that is stored in the database and can be called by other programs or applications. It is a powerful feature of MySQL that can help improve performance and reduce network traffic by allowing complex SQL statements to be executed on the server rather than on the client. Stored procedures are created using the CREATE PROCEDURE statement.

What is indexing in MySQL?
        Indexing is the process of creating an index on one or more columns in a database table. An index is a data structure that allows faster retrieval of data based on the values in the indexed columns. Indexing can significantly improve query performance, especially on large tables. In MySQL, indexes are created using the CREATE INDEX statement.

What is the difference between INNER JOIN and OUTER JOIN in MySQL?
        INNER JOIN returns only the rows that have matching values in both tables being joined, while OUTER JOIN returns all the rows from one table and the matching rows from the other table. There are three types of OUTER JOIN: LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

How do you backup and restore a MySQL database?
        To backup a MySQL database, you can use the mysqldump utility, which creates a text file containing the SQL statements needed to recreate the database. To restore a backup, you can use the mysql utility to execute the SQL statements in the backup file. Alternatively, you can use a graphical tool such as phpMyAdmin to backup and restore databases.

How do you optimize a MySQL database?
        There are several ways to optimize a MySQL database, including:
  • Indexing: creating indexes on frequently queried columns
  • Query optimization: optimizing SQL queries to reduce the number of database accesses and improve performance
  • Table partitioning: splitting large tables into smaller, more manageable pieces
  • Caching: using a caching mechanism to store frequently accessed data in memory for faster access
  • Regular maintenance: performing regular maintenance tasks such as optimizing tables, analyzing queries, and cleaning up unused data.

What is the difference between INNER JOIN and OUTER JOIN in MySQL?
        INNER JOIN is used to retrieve only the matching records between two tables, while OUTER JOIN is used to retrieve all the records from both tables, even if there are no matches.

What is the difference between CHAR and VARCHAR data types in MySQL?
        CHAR data type is fixed-length and can hold up to 255 characters, while VARCHAR data type is variable-length and can hold up to 65,535 characters. VARCHAR is generally used when the length of the data is unknown or when it is likely to change.

What is normalization in MySQL?
        Normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. It involves breaking down a large table into smaller tables and establishing relationships between them.

What is a stored procedure in MySQL?
        A stored procedure is a precompiled set of SQL statements that are stored in the database server. It can be called from an application or from within another SQL statement. Stored procedures can improve performance and security by reducing the amount of data sent between the client and server.

What is an index in MySQL?
        An index is a data structure that improves the speed of data retrieval operations on a table. It is created on one or more columns of a table and allows the database to quickly find the required data without having to scan the entire table.

What is the difference between GROUP BY and ORDER BY in MySQL?
        GROUP BY is used to group the results of a query by one or more columns, while ORDER BY is used to sort the results of a query by one or more columns. GROUP BY is usually used with aggregate functions like SUM or COUNT, while ORDER BY is used to sort the results in ascending or descending order.

What is a foreign key in MySQL?
        A foreign key is a field in one table that refers to the primary key of another table. It is used to establish a relationship between the two tables and ensure data integrity.

What is the difference between a view and a table in MySQL?
        A view is a virtual table that is based on the result of a SELECT statement, while a table is a physical storage area for data. A view does not contain any data of its own but instead retrieves data from one or more tables.

What is a trigger in MySQL?
        A trigger is a special type of stored procedure that is automatically executed when certain events occur, such as an insert, update, or delete operation on a table. It can be used to enforce data validation rules, perform audit trails, or automate certain tasks.

What is normalization in MySQL?
        Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing a table into smaller tables and defining relationships between them to ensure data consistency and improve query performance.

What is a join in MySQL?
        A join in MySQL is used to combine rows from two or more tables based on a related column between them. There are different types of joins such as inner join, outer join, left join, and right join, each with a different way of combining rows.



What is the difference between CHAR and VARCHAR data types in MySQL?
        CHAR and VARCHAR are both character data types in MySQL, but the main difference between them is the way they store data. CHAR is a fixed-length data type that requires a specific number of bytes for each row, while VARCHAR is a variable-length data type that only requires the amount of storage needed for the data entered.

How do you optimize a MySQL database?
        To optimize a MySQL database, you can perform the following tasks:
  • Use indexes to speed up queries
  • Optimize table structure and use appropriate data types
  • Tune server settings such as buffer size and query cache size
  • Use stored procedures and triggers to reduce application code
  • Use replication to distribute load across multiple servers

What is a stored procedure in MySQL?
        A stored procedure in MySQL is a set of SQL statements that are stored in the database and can be called by name. Stored procedures can be used to simplify complex queries, reduce network traffic, and improve performance by reducing the amount of code sent to the database.

How do you backup and restore a MySQL database?
        To backup a MySQL database, you can use the mysqldump command to create a file containing the SQL statements needed to recreate the database. To restore a MySQL database, you can use the mysql command to execute the SQL statements in the backup file.

What is the difference between MyISAM and InnoDB storage engines in MySQL?
        MyISAM and InnoDB are both storage engines in MySQL, but the main difference between them is the way they handle data. MyISAM is a non-transactional storage engine that is suitable for read-intensive applications, while InnoDB is a transactional storage engine that supports ACID transactions and is suitable for write-intensive applications.

What is a primary key in MySQL?
        A primary key in MySQL is a unique identifier for each row in a table. It is used to enforce data integrity by ensuring that each row has a unique identifier and can be accessed quickly using an index.

What is the difference between UNION and UNION ALL in MySQL?
        UNION and UNION ALL are both used to combine the results of two or more SELECT statements in MySQL, but the main difference between them is that UNION removes duplicate rows while UNION ALL does not. UNION is used when you want to combine distinct rows from multiple tables, while UNION ALL is used when you want to combine all rows from multiple tables, including duplicates.


What is the difference between CHAR and VARCHAR data types in MySQL?
        CHAR and VARCHAR are both string data types in MySQL. The main difference between the two is the way they store data.
  • CHAR is a fixed-length data type, which means that it will always occupy the same amount of storage space regardless of the length of the data stored in it. For example, if you define a CHAR(10) column, it will always take up 10 bytes of storage space, even if you store only 5 characters in it.
  • VARCHAR, on the other hand, is a variable-length data type, which means that it will occupy only the required amount of storage space for the data stored in it. For example, if you define a VARCHAR(10) column and store only 5 characters in it, it will occupy only 5 bytes of storage space.

What is the difference between INNER JOIN and OUTER JOIN in MySQL?
        INNER JOIN and OUTER JOIN are both used to combine data from multiple tables in MySQL. The main difference between the two is the way they handle NULL values.
  • INNER JOIN returns only the rows that have matching values in both tables. If a row in one table does not have a corresponding matching row in the other table, it will not be included in the result set.
  • OUTER JOIN, on the other hand, returns all rows from one table and the matching rows from the other table. If a row in one table does not have a corresponding matching row in the other table, the result set will contain NULL values for the missing data.

What is a subquery in MySQL?
        A subquery is a query that is nested inside another query. It is also known as a nested query or inner query.
        In MySQL, a subquery is enclosed in parentheses and is usually placed in the WHERE clause of a SELECT statement. The result of the subquery is then used in the main query to filter the data returned.
        For example, the following SQL statement uses a subquery to find all the customers who have placed an order in the past 30 days:
SELECT * 
FROM customers 
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > DATE_SUB(NOW(), INTERVAL 30 DAY));

MySQL coding round interview questions and answers

1. Write a query to find the second highest salary from an employee table.

SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

2. Write a query to count the number of employees in each department.

SELECT department, COUNT(*) FROM employee GROUP BY department;

3. Write a query to find the average salary of employees in each department.

SELECT department, AVG(salary) FROM employee GROUP BY department;

4. Write a query to find the names of all employees who have a salary greater than the average salary of their department.

SELECT e.name FROM employee e INNER JOIN (SELECT department, AVG(salary) AS avg_salary FROM employee GROUP BY department) AS d ON e.department = d.department WHERE e.salary > d.avg_salary;


5. Write a query to find the department with the highest average salary.

SELECT department, AVG(salary) AS avg_salary FROM employee GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1;

6. Write a query to find the top 5 employees with the highest salary.

SELECT name, salary FROM employee ORDER BY salary DESC LIMIT 5;

7. Write a query to find the names of all employees who do not have a manager.

SELECT name FROM employee WHERE manager_id IS NULL;

8. Write a query to find the names of all employees who have a manager.

SELECT e.name FROM employee e INNER JOIN employee m ON e.manager_id = m.id;

9. Write a query to find the department with the highest total salary.

SELECT department, SUM(salary) AS total_salary FROM employee GROUP BY department ORDER BY SUM(salary) DESC LIMIT 1;

10. Write a query to find the names of all employees who have worked for more than 5 years.

SELECT name FROM employee WHERE DATEDIFF(CURRENT_DATE, hire_date) >= 1825;

11. Write a query to find the top 5 customers with the highest total order value.

SELECT c.customer_name, SUM(o.order_value) as total_order_value 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_name 
ORDER BY total_order_value DESC 
LIMIT 5;

11. Write a query to find the list of customers who have never placed an order.

SELECT c.customer_name 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
WHERE o.customer_id IS NULL;

12. Write a query to find the list of customers who have placed orders in all the available categories.

SELECT c.customer_name 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN order_items oi ON o.order_id = oi.order_id 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY c.customer_name 
HAVING COUNT(DISTINCT p.category_id) = (SELECT COUNT(*) FROM categories);

13. Write a query to find the top 3 products with the highest sales in each category.

SELECT c.category_name, p.product_name, SUM(oi.quantity) as total_sales 
FROM categories c 
JOIN products p ON c.category_id = p.category_id 
JOIN order_items oi ON p.product_id = oi.product_id 
GROUP BY c.category_name, p.product_name 
HAVING total_sales >= ALL(SELECT SUM(oi2.quantity) 
                          FROM order_items oi2 
                          JOIN products p2 ON oi2.product_id = p2.product_id 
                          WHERE p2.category_id = p.category_id 
                          GROUP BY p2.product_name 
                          ORDER BY SUM(oi2.quantity) DESC 
                          LIMIT 3) 
ORDER BY c.category_name, total_sales DESC 
LIMIT 3 * (SELECT COUNT(*) FROM categories);

14. Write a query to find the list of customers who have placed at least 3 orders and the total value of those orders is greater than $500.

SELECT c.customer_name, COUNT(o.order_id) as total_orders, SUM(o.order_value) as total_order_value 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_name 
HAVING total_orders >= 3 AND total_order_value > 500;

15. Write a query to retrieve the top 5 employees based on their salary.

SELECT employee_name, salary FROM employees ORDER BY salary DESC LIMIT 5;

16. Write a query to find the average salary of all employees in the "Sales" department.

SELECT AVG(salary) FROM employees WHERE department = 'Sales';


17. Write a query to delete all employees who have been with the company for less than 1 year.

DELETE FROM employees WHERE DATEDIFF(CURRENT_DATE, hire_date) < 365;

18. Write a query to retrieve the employee with the highest salary in each department.

SELECT e.employee_name, e.salary, e.department
FROM employees e
INNER JOIN (
   SELECT department, MAX(salary) as max_salary
   FROM employees
   GROUP BY department
) max_salaries
ON e.department = max_salaries.department AND e.salary = max_salaries.max_salary;

19. Write a query to retrieve the top 5 highest paid employees, excluding the CEO.

SELECT employee_name, salary 
FROM employees 
WHERE employee_name <> 'CEO' 
ORDER BY salary DESC 
LIMIT 5;

20. Write a query to find the products that have not been ordered in the last month.

SELECT *
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id
  FROM order_items
  WHERE order_id IN (
    SELECT order_id
    FROM orders
    WHERE order_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
  )
);