SQL Essentials for Data Analyst Interviews Quiz

Test your understanding of key SQL concepts and skills commonly assessed in data analyst interviews. This quiz covers joins, indexes, window functions, query optimization, and more.

1. Finding the Second Highest Salary

   Which SQL query would correctly find the second highest salary from a table named 'Employees' with a column 'Salary'?

   1. SELECT MIN(Salary) FROM Employees;
   2. SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC LIMIT 1 OFFSET 1;
   3. SELECT MAX(Salary) FROM Employees WHERE Salary u003C (SELECT MAX(Salary) FROM Employees);
   4. SELECT Salary FROM Employees ORDER BY Salary ASC LIMIT 2;
   5. SELECT COUNT(Salary) FROM Employees;

2. Types of SQL Joins

   What is the difference between an INNER JOIN and a LEFT JOIN in SQL?

   1. INNER JOIN returns only matching rows, LEFT JOIN returns all left table rows with matched right table rows if available.
   2. LEFT JOIN is faster than INNER JOIN.
   3. INNER JOIN returns all records from both tables, LEFT JOIN returns only left table records.
   4. INNER JOIN ignores NULL values, LEFT JOIN replaces NULL with zeros.
   5. LEFT JOIN combines every record from both tables regardless of match.

3. Identifying Duplicates

   Which SQL clause combination helps you find duplicate rows in a table based on a 'Name' column?

   1. WHERE Name = NULL
   2. GROUP BY Name HAVING COUNT(*) u003E 1
   3. ORDER BY Name DESC
   4. LIMIT 2 OFFSET 1
   5. SELECT DISTINCT Name

4. Improving Query Performance

   What is an effective way to optimize SQL queries for better performance?

   1. Using indexes on frequently filtered columns
   2. Running queries during busy hours
   3. Storing dates as text values
   4. Adding more columns to SELECT *
   5. Using as many JOINs as possible

5. Understanding Window Functions

   Which SQL keyword is required to use a window function like ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary)?

   1. WINDOW_CALL
   2. GROUPED BY
   3. OVER
   4. NUM_ROW
   5. HAVING

6. Removing Duplicate Records

   Which statement removes duplicate records from a table while keeping only unique rows?

   1. SELECT FROM DISTINCT TableName;
   2. DELETE FROM TableName WHERE rowid NOT IN (SELECT MIN(rowid) FROM TableName GROUP BY column1, column2);
   3. REMOVE DUPLICATE TableName;
   4. CLEAR DUPLICATES TableName;
   5. UPDATE unique TableName;

7. WHERE vs. HAVING Clause

   What is the main difference between WHERE and HAVING clauses in SQL?

   1. HAVING cannot use aggregation functions
   2. HAVING is used before GROUP BY
   3. WHERE sorts records, HAVING deletes them
   4. WHERE filters rows before aggregation, HAVING filters groups after aggregation
   5. WHERE works only with dates, HAVING with numbers

8. Handling NULL Values

   How can you replace NULL values in a column with a default value, for example, converting NULLs to zero in SQL?

   1. Using the COALESCE(column, 0) function
   2. REPLACE column WITH 0
   3. DEFAULT(column, 0)
   4. WHERE column = NULL
   5. SKIP NULLS column

9. Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()

   If two employees have the same salary, how do RANK() and DENSE_RANK() differ when used in an SQL query?

   1. ROW_NUMBER() and RANK() are identical in all cases
   2. RANK() leaves a gap after ties, while DENSE_RANK() does not
   3. RANK() does not handle ties, but DENSE_RANK() does
   4. Both functions ignore duplicate values
   5. DENSE_RANK() always skips numbers, RANK() does not

10. Cumulative Sum Example

    Which SQL window function can be used to get a running cumulative sum of sales per month?

    1. SUM(Sales) OVER (ORDER BY Month)
    2. COUNT(Sales) GROUP BY Month
    3. TOTAL SUM SALES
    4. JOIN Sales ON Month
    5. SELECT SUM(Sales) FROM SalesTable