Essential SQL Knowledge for Data Analyst Interviews Quiz

Test your understanding of the most important SQL concepts, functions, and best practices relevant to data analyst interviews in 2024. This quiz covers key fundamentals, practical scenarios, and optimization techniques.

1. SELECT Statement Fundamentals

   Which SQL keyword is used to retrieve specific columns from a table, for example: SELECT name, age FROM employees?

   1. GET
   2. RETURN
   3. SELECT
   4. SELCT
   5. EXTRACT

2. Understanding INNER JOIN

   When using an INNER JOIN between two tables, which rows are included in the result set?

   1. Rows with NULL values only
   2. Rows from the left table only
   3. Only rows that have matching values in both tables
   4. All rows from both tables
   5. Rows from the right table only

3. Aggregating Data with GROUP BY

   Which SQL clause would correctly group employee records by department to count the number of employees in each department?

   1. COLLECT BY
   2. BUNDLED BY
   3. CLASSIFY BY
   4. GROOP BY
   5. GROUP BY

4. NULL Value Handling

   If you want to replace NULL values in the salary column with 0, which SQL function should you use?

   1. CASE
   2. COALASCE
   3. ISNULL
   4. COALESCE
   5. REPLACE

5. Window Functions

   To assign a unique, sequential integer to each row in a result set starting at 1, which SQL window function should be used?

   1. ROW_NUMBER()
   2. SERIAL()
   3. ROW_NUMBER
   4. RANKS()
   5. ROW_COUNT()

6. Date and Time Manipulation

   Which SQL function correctly returns the current date and time in a query?

   1. NOOW()
   2. DATENOW()
   3. DATE_TIME()
   4. NOW()
   5. CUR_DATE()

7. Detecting Duplicates

   What SQL keyword should you use in the SELECT clause to retrieve only unique values from a column named 'city'?

   1. UNIQUE
   2. EXCEPT
   3. DISTINCT
   4. DIFFERENT
   5. DISTNCT

8. Understanding Indexes

   How does creating an index on a column generally affect query performance on large tables?

   1. It sorts data by default
   2. It reduces disk space by compressing data
   3. It deletes duplicate rows automatically
   4. It optimizes searches by speeding up data retrieval
   5. It always makes queries run slower

9. Subqueries in SQL

   What is a subquery in SQL?

   1. A subqueery is a temporary column
   2. A type of table definition
   3. A summary of results using aggregates
   4. A table join with more than two tables
   5. A query written inside another query

10. Normalization in Databases

    What is the main purpose of normalizing a database?

    1. To split every column into different tables
    2. To make tables look smaller
    3. To minimize data redundancy and improve integrity
    4. To introduce more NULL values
    5. To create duplicate copies of every record