Data analysts perform a variety of roles including providing reports using statistical methods and analyzing data, implementing systems for data collection, and developing databases, identifying trends, and interpreting complex data set patterns. SQL is the industry-standard language used by data analysts for providing data insights. In a job interview, SQL being a major component of data analysis features highly in the interrogation. These are some of SQL Query Interview Questions for Data Analyst that are frequently asked.

Data Analyst interview questions and answers for freshers

Consider the following tables

Employee table

employee_id

full_name

manager_id

date_of_joining

city

121

Shanaya Gupta

321

1/31/2014

Bangalore

321

Snehil Aggarwal

986

1/30/2015

Delhi

 

Salary table

employee_id

project

salary

variable

121

P1

8000

500

321

P2

10000

1000

421

P1

12000

0

 

1. Write a query fetching the available projects from salary table.

Upon looking at the employee salary table, it is observable that every employee has a project value correlated to it. Duplicate values also exist, so a unique clause will be used in this case to get distinct values.

SELECT DISTINCT(project) FROM Salary;

 

2. Write a query fetching full name and employee ID of workers operating under manager having ID 986

Take a look at the employee details table, here we can fetch employee details working under the manager with ID 986 using a WHERE clause.

SELECT employee_id, full_name FROM Employee WHERE manager_id=986;

 

3. Write a query to track the employee ID who has a salary ranging between 9000 and 15000

In this case, we will use a WHERE clause, with BETWEEN operator

SELECT employee_id, salary FROM Salary
WHERE salary BETWEEN 9000 and 15000;

 

4. Write a query for employees that reside in Delhi or work with manager having ID 321

Over here, one of the conditions needs to be satisfied. Either worker operating under Manager with ID 321 or workers residing in Delhi. In this scenario, we will require using OR operator.

SELECT employee_id, city, manager_id FROM Employee
WHERE manager_id='321' OR city='Delhi';

 

5. Write a query displaying each employee's net salary added with value of variable

Now we will require using the + operator.

SELECT employee_id, salary+variable AS Net Salary
FROM Salary;

 

6. Write a query fetching employee IDs available in both tables

We will make use of subquery

SELECT employee_id FROM Employee
WHERE employee_id IN (SELECT employee_id FROM Salary);

 

7. Write a query fetching the employee’s first name (string before space) from the employee table via full_name column

First, we will require fetching space character’s location from full_name field, then further extracting the first name out of it.

We will use LOCATE in MySQL, then CHARINDEX in SQL server. MID or SUBSTRING method will be utilized for string before space

Via MID (MySQL)

SELECT MID(full_name, 1, LOCATE(' ', full_name))
FROM Employee;

 

Via SUBSTRING (SQL server)

SELECT SUBSTRING(full_name, 1, CHARINDEX(' ', full_name))
FROM Employee;

 

8. Write a query fetching the workers who have their hands-on projects except for P1

In this case, NOT operator can be used for fetching rows that do not satisfy the stated condition.

SELECT employee_id FROM Salary
WHERE NOT project = 'P1';

 

Also, using not equal to operator

SELECT employee_id FROM Salary
WHERE project <> 'P1';

 

9. Write a query fetching name of employees who have salary equating 5000 or more than that, also equating 10000 or less than that

Over here, BETWEEN will be used in WHERE for returning employee ID of workers whose remuneration satisfies the stated condition, further using it as subquery for getting the employee full name via the table (employee).

SELECT full_name FROM Employee
WHERE employee_id IN
(SELECT employee_id FROM Salary 
WHERE salary BETWEEN 5000 AND 10000);

 

10. Write a query fetching details of the employees who started working in 2020 from employee details table.

For this, we will use BETWEEN for time period ranging 01/01/2020 to 31/12/2020

SELECT * FROM Employee
WHERE date_of_joining BETWEEN '2020/01/01' AND '2020/12/31';

 

Now the year can be extracted from date_of_joining using YEAR function in MySQL

SELECT * FROM Employee
WHERE YEAR(date_of_joining) = '2020';

 

11. Write a query fetching salary data and employee names. Display the details even if an employee's salary record isn’t there.

Here, the interviewer is trying to gauge your knowledge related to SQL JOINS.

Left JOIN will be used here, with Employee table being on the left side of Salary table.

SELECT E.full_name, S.salary
FROM Employee E
LEFT JOIN Salary S
ON E.employee_id = S.employee_id;

 

Advanced SQL, DBMS interview questions

These SQL interview questions for 6 years of experience can help you in your job application.

12. Write a query for removing duplicates in a table without utilizing the temporary table

Inner join along with delete will be used here. Equality of matching data will be assessed further, the rows with higher employee ID will be discarded.

DELETE E1 FROM Employee E1
INNER JOIN Employee E2
WHERE E1.employee_id > E2.employee_id
AND E1.full_name = E2.full_name
AND E1.manager_id = E2.manager_id
AND E1.date_of_joining = E2.date_of_joining
AND E1.city = E2.city;

 

13. Write a query fetching just the even rows in the Salary table

If there’s an auto-increment field, for instance, employee_id, then the below-mentioned query can be used.

SELECT * FROM Salary
WHERE MOD(employee_id,2) = 0;

 

If the above-stated field is absent (auto-increment field), then these queries can be used. Verifying the remainder is 0 when divided with 2, and by using ROW_NUMBER (in SQL server)

SELECT E.employee_id, E.project, E.salary
FROM (
      SELECT *, ROW_NUMBER()
      OVER(ORDER BY employee_id) AS RowNumber
      FROM Salary
     ) E
WHERE E.RowNumber % 2 = 0;

 

Using variable (user-defined) in MySQL

SELECT *
FROM (
           SELECT *, @rowNumber := @rowNumber+1 RowNo
     FROM Salary
     JOIN(SELECT @rowNumber := 0) r
     ) t
WHERE RowNo % 2 = 0;

 

14. Write a query fetching duplicate data from Employee table without referring to employee_id (primary key)

In this case, on all the fields, we will use GROUP BY, further HAVING clause will be used for returning duplicate data that has more than one count.

SELECT full_name, manager_id, date_of_joining, city, COUNT(*)
FROM Employee
GROUP BY full_name, manager_id, date_of_joining, city
HAVING COUNT(*) > 1;

 

15. Write a query creating the same structured empty table as any other

Over here, false WHERE condition will be used.

CREATE TABLE NewTable
SELECT * FROM Salary WHERE 1=0;

 

The above mentioned are some of the most common SQL data analyst interview questions to prepare for entry-level, intermediate and advanced level jobs.

Check the SQL Training Program