SQL u0026 Power BI Essentials for Data Analyst Interviews Quiz

Test your understanding of key SQL and Power BI concepts commonly asked in data analyst interviews, focusing on query writing, data modeling, and analytics scenarios.

1. Identifying the Second Highest Salary

   Which SQL query would correctly find the second highest salary from an Employee table with a 'salary' column?

   1. SELECT MIN(salary) FROM Employee;
   2. SELECT salary FROM Employee WHERE salary = (SELECT MIN(salary) FROM Employee);
   3. SELECT salary FROM Employee LIMIT 2;
   4. SELECT SECOND(salary) FROM Employee;
   5. SELECT MAX(salary) FROM Employee WHERE salary u003C (SELECT MAX(salary) FROM Employee);

2. Difference Between WHERE and HAVING

   In SQL, what is the main difference between the WHERE and HAVING clauses when filtering query results?

   1. WHERE and HAVING are always interchangeable.
   2. HAVING replaces WHERE in all queries.
   3. WHERE can be used after GROUP BY, HAVING cannot.
   4. WHERE is used for row filtering before grouping, while HAVING filters groups after aggregation.
   5. WHERE only works on numeric columns, HAVING works on text columns.

3. Understanding CTEs

   What is a CTE (Common Table Expression) in SQL, and how is it typically defined?

   1. A custom table that is encrypted for security.
   2. A function that creates indexes automatically.
   3. A permanent database table used for storing metadata.
   4. A code block that only runs in stored procedures.
   5. A temporary result set defined with the WITH keyword, used for organizing complex queries.

4. SQL Joins Overview

   Which join in SQL returns all records from the left table and only matching records from the right table?

   1. UNION JOIN
   2. INNER JOIN
   3. CROSS JOIN
   4. LEFT JOIN
   5. FULL JOIN

5. Handling NULL Values

   What is a common SQL function to replace NULL values with a default, for example, turning NULL ages into 0?

   1. NULLIFY(age, 0)
   2. REPLACE(age, 0)
   3. SETNULL(age, 0)
   4. COALESCE(age, 0)
   5. FILLNULL(age, 0)

6. Union vs. Union All

   When combining two datasets in SQL, which statement correctly describes UNION ALL compared to UNION?

   1. UNION ALL sorts the results automatically.
   2. UNION ALL only works on numeric data.
   3. UNION ALL cannot be used with SELECT statements.
   4. UNION ALL removes duplicates; UNION keeps all rows.
   5. UNION ALL keeps all duplicate rows; UNION removes duplicates.

7. Purpose of Relationships in Power BI

   In Power BI data modeling, why do you create relationships between tables?

   1. To encrypt the dataset for security purposes.
   2. To enable data from different tables to be analyzed together, like linking Sales and Products tables.
   3. To automatically generate DAX measures.
   4. To visualize data in 3D charts.
   5. To increase the loading speed of the dashboard.

8. DirectQuery vs. Import Mode

   Which option correctly describes the difference between DirectQuery and Import Mode in Power BI?

   1. DirectQuery is offline only; Import Mode updates in real-time.
   2. DirectQuery only works with Excel files.
   3. Import Mode supports more data sources than DirectQuery.
   4. DirectQuery creates static reports; Import Mode creates interactive dashboards.
   5. DirectQuery connects to live data sources, while Import Mode loads data into Power BI.

9. Role of Slicers in Power BI

   How does a slicer typically function in a Power BI report?

   1. Filters visualizations based on user selections, such as showing sales by specific years.
   2. Downloads raw data from visuals.
   3. Sorts table columns automatically.
   4. Changes the color theme of the dashboard.
   5. Deletes data from the model.

10. Calculated Column vs. Measure

    What is the main distinction between a calculated column and a measure in Power BI?

    1. Measures can only be used in tables, calculated columns only in charts.
    2. A measure is always stored in the database, but calculated columns are not.
    3. A calculated column is computed row by row in the data model, while a measure calculates results based on filters or aggregations at query time.
    4. There is no difference; the terms are interchangeable.
    5. Calculated columns require DAX, and measures do not.