SQL Queries for Interview

Basic SQL Queries asked in Interviews

This is an company_employee table data on which I will perform all the basic SQL queries.

1. To get Highest salary details from each department

SELECT * FROM company_employee ce WHERE salary=(SELECT MAX(salary) FROM company_employee WHERE dept_id = ce.dept_id);

Or

SELECT NAME, desg, dept_id, MAX(salary) FROM company_employee GROUP BY dept_id;

2. To get Highest salary of first 5 employees

SELECT * FROM company_employee ORDER BY salary DESC LIMIT 5;

3. To get 2nd Highest salary of an employee

SELECT NAME, desg, dept_id, MAX(salary) FROM company_employee WHERE salary NOT IN (SELECT MAX(salary) FROM company_employee);

4. To get current date in MySQL

5. Query to find out the records whose salary is in the given range

SELECT DISTINCT * FROM company_employee WHERE salary BETWEEN 40000 AND 100000;

6. To find an employees whose salary is equal or greater then the given limit

SELECT * FROM company_employee WHERE salary>=200000;

7. To find employees whose name starts with G

SELECT * FROM company_employee WHERE NAME LIKE 'G%';

8. To find out all the employees whose names contains the word "KUMAR" regardless of whether it was stored.

SELECT * FROM company_employee WHERE UPPER(NAME) LIKE '%KUMAR%';

9. To get year from current date

SELECT YEAR(CURDATE()) AS "Year";

Updated table data with duplicate records

10. To find duplicate field values based on single column in a table.

SELECT NAME, COUNT(NAME) FROM company_employee GROUP BY NAME HAVING COUNT(NAME) > 1;

Or

SELECT NAME FROM company_employee GROUP BY NAME HAVING COUNT(NAME) > 1

11. To find duplicate field values based on multiple column in a table.

SELECT NAME, COUNT(NAME), desg, COUNT(desg), salary, COUNT(salary)  FROM company_employee GROUP BY NAME, desg, salary

HAVING COUNT(NAME) > 1 AND COUNT(desg) >1 AND COUNT(salary)>1;

12 . To Get All duplicate records

SELECT * FROM company_employee WHERE NAME IN (SELECT NAME FROM company_employee GROUP BY NAME HAVING COUNT(NAME) > 1 )ORDER BY NAME

13. Delete all duplicates records keeping the lowest id record

DELETE ce1 FROM company_employee ce1, company_employee ce2 WHERE

ce1.emp_id > ce2.emp_id AND ce1.name = ce2.name;

DELETE ce1 FROM company_employee ce1, company_employee ce2 WHERE

ce1.emp_id < ce2.emp_id AND ce1.name = ce2.name;