SQL Query Interview Questions | Basic and Advanced Levels

Q1. SQL QUERY TO FIND SECOND HIGHEST SALARY OF EMPLOYEE

Ans: There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery:

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

Q2. GET FIRST_NAME,LAST_NAME FROM EMPLOYEE TABLE

Ans: Select first_name, Last_Name from employee

Q3. SQL QUERY TO FIND MAX SALARY FROM EACH DEPARTMENT.

Ans: You can find the maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

These questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case, you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well.  Here is the query

SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

In this query, we have used RIGHT OUTER JOIN because we need the name of the department from Department table which is on the right side of JOIN clause, even if there is no reference of dept_id on Employee table.

Q4. GET FIRST_NAME FROM EMPLOYEE TABLE USING ALIAS NAME “EMPLOYEE NAME”

Ans: Select first_name Employee Name from employee

Q5. WRITE SQL QUERY TO DISPLAY THE CURRENT DATE.

Ans: SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.

SELECT GetDate();

Q6. GET FIRST_NAME FROM EMPLOYEE TABLE IN UPPER CASE

Ans: Select upper(FIRST_NAME) from EMPLOYEE

Q7. WRITE AN SQL QUERY TO CHECK WHETHER DATE PASSED TO QUERY IS THE DATE OF GIVEN FORMAT OR NOT.

Ans: SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL function and it may not work on Oracle, MySQL or any other database but there would be something similar.

SELECT  ISDATE(‘1/08/13’) AS “MM/DD/YY”;

It will return 0 because passed date is not in correct format

Q8. GET FIRST_NAME FROM EMPLOYEE TABLE IN LOWER CASE

Ans: Select lower(FIRST_NAME) from EMPLOYEE

Q9. WRITE AN SQL QUERY TO PRINT THE NAME OF THE DISTINCT EMPLOYEE WHOSE DOB IS BETWEEN 01/01/1960 TO 31/12/1975.

Ans: This SQL query is tricky, but you can use BETWEEN clause to get all records whose date fall between two dates.

SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Q10. GET UNIQUE DEPARTMENT FROM EMPLOYEE TABLE

Ans: select distinct DEPARTMENT from EMPLOYEE

Q11. SELECT FIRST 3 CHARACTERS OF FIRST_NAME FROM EMPLOYEE

Ans: Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee

SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,1,3) from employee

MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select substring(FIRST_NAME,1,3) from employee

Q12. GET POSITION OF 'O' IN NAME 'JOHN' FROM EMPLOYEE TABLE

Ans: Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,’o’) from employee where first_name=’John’

SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX(‘o’,FIRST_NAME,0) from employee where first_name=’John’

MySQL Server Equivalent of Oracle INSTR is LOCATE, Query: Select LOCATE(‘o’,FIRST_NAME) from employee where first_name=’John’

GET FIRST_NAME FROM EMPLOYEE TABLE AFTER REMOVING WHITE SPACES FROM RIGHT SIDE

select RTRIM(FIRST_NAME) from employee

GET FIRST_NAME FROM EMPLOYEE TABLE AFTER REMOVING WHITE SPACES FROM LEFT SIDE