SQL Data Cleaning and Processing Essentials Quiz

Test your foundational knowledge of SQL data cleaning and preprocessing concepts, including handling duplicates, joins, window functions, grouping, and aggregate queries. Perfect for interview preparation and understanding key SQL techniques for structured data management.

1. Understanding Joins

   Which SQL JOIN type returns all rows from the left table and matching rows from the right table, inserting NULLs where there is no match?

   1. RIGHT JOIN
   2. LEFT JOIN
   3. FULL JOIN
   4. INNER JOIN

   Explanation: LEFT JOIN returns all records from the left table and includes matched records from the right table, inserting NULLs for non-matching rows. INNER JOIN only includes rows present in both tables. FULL JOIN brings all rows from both tables, filling NULLs for missing matches in either table. RIGHT JOIN does the opposite of LEFT JOIN, returning all rows from the right table.

2. Finding Duplicates

   What SQL clause is typically used with GROUP BY to filter only groups that appear more than once, useful for identifying duplicates?

   1. HAVING
   2. WHERE
   3. LIMIT
   4. ORDER BY

   Explanation: HAVING is used to filter groups formed by GROUP BY, so it's ideal for finding duplicates when combined with COUNT. WHERE filters individual rows before grouping, not groups themselves. ORDER BY sorts results but does not filter groups. LIMIT restricts the number of results returned and isn't used for grouping.

3. GROUP BY vs ORDER BY

   Which clause should you use to arrange the result set in ascending or descending order based on a column's values?

   1. SELECTION
   2. HAVIN
   3. GROUP BY
   4. ORDER BY

   Explanation: ORDER BY sorts query results by the specified columns in ascending (default) or descending order. GROUP BY is used for aggregating data and does not guarantee sort order. 'HAVIN' is a misspelling of HAVING, which filters groups, and SELECTION is not a valid SQL clause.

4. Purpose of Window Functions

   Which of the following best describes a window function in SQL?

   1. Deletes duplicate records in a table
   2. Aggregates rows into a single summary row
   3. Performs calculations across a set of rows related to the current row, without collapsing the result set
   4. Sorts rows alphabetically using a specific column

   Explanation: Window functions perform row-wise calculations that can access data from other rows in their window, enabling tasks like running totals or rankings. Traditional aggregation collapses rows into summary results. Deleting duplicates and sorting are not the core purpose of window functions.

5. Calculating Median

   To correctly compute the median in SQL for a numeric column, which two key window functions might you need?

   1. MIN() and MAX()
   2. ROW_NUMBER() and COUNT()
   3. RANK() and SUM()
   4. SUM() and AVG()

   Explanation: ROW_NUMBER() assigns a unique sequential number to rows, and COUNT() gives the total count, both essential for determining the middle value(s) for median calculation. SUM() and AVG() are for totals and averages, RANK() is for ranking, and MIN()/MAX() find lowest and highest values but do not directly help to locate the median.

6. Identifying NULL Values

   Which SQL operator allows you to filter rows where a column value is missing or undefined?

   1. LIKE
   2. GROUPED
   3. UNIQUE
   4. IS NULL

   Explanation: IS NULL is used to test whether a column contains a missing or undefined value. LIKE is for pattern matching in strings. GROUPED is not a valid SQL operator, and UNIQUE is commonly used in constraints, not filtering.

7. Aggregating Data

   If you want to count the number of employees in each department, which SQL clause should you use to group records by department?

   1. GROUPED
   2. HAVIG
   3. GROUP BY
   4. ORDERED BY

   Explanation: GROUP BY collects rows sharing the same value in a specified column, enabling you to aggregate, such as counting employees per department. 'ORDERED BY' and 'GROUPED' are not valid SQL clauses, and 'HAVIG' is a misspelling of HAVING.

8. Removing Duplicates

   What combination of SQL keywords helps you retrieve only unique rows from a table?

   1. SELECT DISTINCT
   2. SELECT UNION
   3. SELECT OFF
   4. SELECT REPEAT

   Explanation: SELECT DISTINCT removes duplicate rows from a query's result set. UNION combines result sets from two queries, but does not remove duplicates unless UNION DISTINCT is used. SELECT REPEAT and SELECT OFF are not valid SQL commands.

9. Splitting Column Values

   Which SQL string function is commonly used to extract part of a string, such as separating first and last names?

   1. SUBSTRING
   2. PRIMARY
   3. LINK
   4. AGGREGATE

   Explanation: SUBSTRING or similar functions extract a portion of a string, helpful for splitting data like names. PRIMARY is not a string function, AGGREGATE refers to summary operations, and LINK is unrelated to string extraction.

10. Standardizing Data

    If you want to make all text in a column lowercase for consistency, which SQL function should you use?

    1. LOWER()
    2. UCASE()
    3. COUNT()
    4. UPPER()

    Explanation: LOWER() converts all letters in a string to lowercase, helping to standardize data entries. UCASE() is a non-standard synonym for UPPER() and usually capitalizes text, not lowercases it. COUNT() is used for counting rows, not changing text case.