4.12.1: Review Questions
- Page ID
- 58299
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\ket}[1]{\left| #1 \right>}\)
\(\newcommand{\bra}[1]{\left< #1 \right|}\)
\(\newcommand{\braket}[2]{\left< #1 \vphantom{#2} \right| \left. #2 \vphantom{#1} \right>}\)
\(\newcommand{\braopket}[3]{\left< #1 \vphantom{#2}\vphantom{#3} \right| #2 \vphantom{#1}\vphantom{#3} \left| #3 \vphantom{#1}\vphantom{#2} \right>}\)
\(\newcommand{\qmvec}[1]{\mathbf{\vec{#1}}}\)
\(\newcommand{\op}[1]{\hat{\mathbf{#1}}}\)
\(\newcommand{\expect}[1]{\langle #1 \rangle}\)
\(\newcommand{\dfn}[1]{\emph{\textbf{#1}}}\)
Review Questions
Multiple Choice
-
What does the IS NULL operator check in SQL?
-
a) Whether a value equals zero b) Whether a value is an empty string c) Whether a field has no data (absence of value) d) Whether a value is negative
-
-
An analyst replaces all missing ages with the average age of the dataset. This is an example of:
-
a) Listwise deletion b) Mean imputation c) CAST conversion d) Fuzzy matching
-
-
A product price stored as the text string "19.99" causes AVG() to fail. The fix is:
-
a) Use TRIM() b) Use CAST(product_price AS DECIMAL) c) Use IS NULL d) Delete the column
-
Short Answer
-
Explain the difference between NULL, zero, and an empty string. Why does this distinction matter in SQL and analytics?
-
Describe three strategies for handling missing data. For each, give a scenario where it is the most appropriate choice and explain the trade-offs.
-
What is the difference between MCAR, MAR, and MNAR? Why does understanding the missingness mechanism matter for choosing a handling strategy?
-
A customer table has duplicate records with slight name variations ("Robert Smith" vs "Bob Smith"). Describe a process to detect and resolve these duplicates.
True/False
-
True or False: Replacing all missing values with zero is always a safe imputation strategy.
-
True or False: A surrogate key (auto-increment ID) prevents logical duplicates where two records represent the same real-world entity.
-
True or False: Data profiling should be performed before cleaning to understand the scope of quality issues.
Answer Key:
1-c, 2-b, 3-b, 4. NULL=absence of data, zero=a numeric value, empty string=a text value with no characters. SQL treats them differently: NULL is excluded from aggregations, zero is included, empty string is a valid value. Comparing with = doesn't work for NULL (must use IS NULL). 5. Deletion: few missing records, MCAR. Imputation: need complete dataset, MAR. Flagging: missingness is informative (MNAR). Trade-offs: deletion loses data, imputation introduces bias, flagging adds complexity. 6. MCAR: safe to delete without bias. MAR: can impute using related variables. MNAR: hardest — deletion/imputation both introduce bias; may need domain-specific handling. 7. Profile by email/phone to find same contact info → fuzzy match on name fields (Soundex, Levenshtein distance) → manual review of flagged pairs → merge records keeping most complete data → document resolution rules. 8. False, 9. False, 10. True.