iClass Gyansetu

    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.

    Check the SQL Training Program