Top SQL Interview Questions with Answers for a Data Analyst Interview
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.
