MySQL Interview Questions and Answers

The MySQL aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.

MySQL provides many aggregate functions that are listed below.

• AVG()
• COUNT()
• SUM()
• MIN()
• MAX() etc

In Mysql, joins are used to retrieve data from multiple tables. It is performed whenever two or more tables are joined.

There are three types of MySQL joins:-

• MySQL INNER JOIN
  SELECT columns FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;
• MySQL LEFT JOIN
  SELECT columns FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;
• MySQL RIGHT JOIN
  SELECT columns FROM table_1 RIGHT JOIN table_2 ON table_1.column = table_2.column;

• CHAR can have a maximum of 255 characters, but VARCHAR can hold a maximum of 65,535 characters.
• CHAR field is a fixed length, but VARCHAR is a variable length field.
• CHAR uses static memory allocation, but VARCHAR uses dynamic memory allocation.

We can use SELECT NOW();

SELECT std_id, COUNT(std_id) as cnt FROM Student GROUP by std_id having cnt > 1

SELECT id, name, department FROM Employee where MOD(id,2) = 1

The stored procedure is like a subprogram in a typical computing language which is stored in the database. A stored procedure contains the name, list of parameters, and the SQL statements. All the relational database system works as pillars for stored procedures.

In this example, we are creating a simple procedure called job_data, when this procedure will get executed, all the data from "jobs" tables will get displayed.

DELIMITER //
CREATE PROCEDURE GetAllPages()
BEGIN
    SELECT * FROM pages WHERE title LIKE '%MySQL Interview Questions%';
END //
DELIMITER ;

SELECT * FROM Student where MOD(id,2) = 0

SELECT * FROM 'TableName' WHERE 'status' = 1 LIMIT 10