Intermediate Level

What are Storage Engines in Mysql?

Storage engines are Mysql components, that can handle the operations
for different table types to store and manage information in a database.
• InnoDB
• MyISAM
• MEMORY
• MERGE
• EXAMPLE
• CSV
• ARCHIVE etc

How to change the database engine in MySQL?

ALTER TABLE EnterTableName ENGINE = EnterEngineName;

In Mysql, what is the default storage engine?

InnoDB

How to use GROUP_CONCAT with different SEPARATOR in MySQL?

SELECT id, GROUP_CONCAT(department SEPARATOR ' ') AS
department FROM employee group by id;

What is the difference between BETWEEN and IN operators in
Mysql?

Between operator is used to select a range of data between two values. It
can be texts, numbers, and dates, etc
Syntax: Select * from TABLENAME where FIELDNAME between
VALUE1 and VALUE2

IN operator is used to check for a value in the given set of values.
Syntax: Select * from TABLENAME where FIELDNAME IN
('VALUE1','VALUE2',...)

What is difference between function and procedure in MySQL?

Function
- The function returns the single value which is anyhow mandatory.
- They only work with a statement: Select
- In function, transactions are not possible
- Error handling is not possible

Procedure
- The procedure returns zero or N values.
- They can work with multiple statements: Insert, Update, Delete, Select.
- In the procedure, transactions are possible.
- With try-catch, error handling is possible.

What is the difference between CHAR and VARCHAR in MySQL?

• 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.

How we can get the current date in MySQL?

We can useSELECT NOW();

Write a query to find duplicate rows In table?

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

How to display odd rows in Employee table in Mysql?

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

What are the stored procedures in MySQL? Also, write an example?

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.

Example

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

Write a query to display even rows in student table using MySQL?

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

How to display top 10 rows in Mysql?

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

Write a query to fetch duplicate records from a table using MySQL?


SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary
GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;

Write a query to fetch common records between two tables using
MySQL?

Using INTERSECT

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

What is the difference between having and where clause in Mysql?

WHERE term is used for filtering rows, and it applies to every row
but HAVING term is used to filter groups.
WHERE can be used without the GROUP BY but HAVING clause cannot be
used without the GROUP BY.

What is constraints? Also explain the different types of constraints?

These are the set of rules applied to columns on the table. It is used to
bound the type of data that can go into a table.
Types of constraints
• PRIMARY KEY
• FOREIGNKEY
• UNIQUE:
• Not NULL


Explore the Data Science Course in Pune
Watch the video on SQL Interview Question Set - 4