MySQL query asked in interviews?

Concat two columns in SELECT query -

• SELECT employeeid, CONCAT(firstname, lastname) AS employee_name FROM employee;
• Concat by adding space -> CONCAT(firstname, ' ', lastname) AS employee_name

Write a query to find even records in MYSQL.

•   SELECT * FROM EMPLOYEE WHERE id IN(SELECT id FROM EMPLOYEE WHERE id%2 = 0);

Write a query to find odd records in MYSQL.

• SELECT * FROM EMPLOYEE WHERE id IN(SELECT id FROM EMPLOYEE WHERE id%2 <> 0);

Get duplicate records from table

• SELECT name, count(name) FROM employee GROUP BY name HAVING(name) > 1

Get 4th Highest salary with limit function and without using limit functions

• SELECT name,email from users ORDER BY DESC LIMIT 4,1

Finding the highest salary

• select MAX(Salary) from Employee;

Finding the 2nd highest salary

• Query-1
• SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
• Query-2
• select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

Finding the nth highest salary

• SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1        

If table is contain column as Active which is having data as 1, 0, 1, 0 update this data  with 0, 1, 0, 1 with single query statement

• UPDATE emp SET status = 1 - status