Interview Question

SQL Interview Question

Posted on Updated on

MySQL

 

 

 

Concatenate multiple row into a single row :

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr …] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name …]] [SEPARATOR str_val])

mysql>SELECT NAME, reminder_date,STATUS FROM enquiry;

single-row-2

 

 

 

 

 

 


mysql>SELECT id, NAME,GROUP_CONCAT(DISTINCT STATUS ORDER BY reminder_date DESC SEPARATOR ‘,’)AS STATUS FROM enquiry GROUP BY NAME

single-row

 

 Find nth largest Salary in Employee Table:-

Select all Employee rows by Salary descending order.

 mysql>SELECT * FROM employee ORDER BY salary DESC;

employee-table

 

 

 

 

Fetch 4th largest salary holder Employee :-

syntax : 

select * from <table-name> t2 where ( n-1 ) = ( select count(distinct(<t2>.<column-name>)) from <table-name> t2 where t2.salary > t1.salary);

example :-

         mysql>SELECT * FROM employee e1
WHERE  (4-1) = ( SELECT COUNT(DISTINCT(e2.salary)) FROM employee e2 WHERE                                      e2.`salary` >   e1.`salary`)

employee-table-1

 

Advertisements