SQL Interview Question Set - 4 Released

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