Mini Challenge 7 — NULL Handling, String Functions, and SUBSTRING
title: “Mini Challenge 7 — Topics 19-20 + Preview” part: 1 topic_number: 0 slug: “mini-challenge-07” difficulty: “Beginner” prerequisites: “null-handling, string-functions-upper-lower, substring” —
Mini Challenge 7 — NULL Handling, String Functions, and SUBSTRING
Overview
Master NULL handling and string manipulation in realistic scenarios.
Challenge 1: Clean Email List
Some employees might not have email addresses. Create a report showing:
- first_name
- last_name
- email (if NULL, show ‘No Email Provided’)
- email_status: ‘Has Email’ or ‘Missing Email’
Sort by last_name.
Hint
Use IFNULL or COALESCE for email, CASE WHEN for email_status.Challenge 2: Standardized Department Names
Create a report with department names in three formats:
- Original department_name
- uppercase_name (all caps)
- lowercase_name (all lowercase)
- initials (first letter of each word, uppercase)
Sort alphabetically by original name.
Hint
Use UPPER(), LOWER(). For initials, you might need SUBSTRING and CONCAT (advanced: or use string functions with LOCATE for multi-word names).Challenge 3: Find Incomplete Records
Find all employees who have NULL values in any column EXCEPT employee_id. Show employee_id, first_name, last_name, and which field is NULL (use a text description).
Hint
Use WHERE clauses with IS NULL for each possible column, UNION results or use CASE WHEN.Challenge 4: Project Name Abbreviations
Create 3-letter abbreviations for all project names using the first 3 characters, in uppercase. Show project_name and abbreviation. Sort by abbreviation.
Hint
Use SUBSTRING to get first 3 characters, UPPER to capitalize.Challenge 5: Conditional Email Generation
Generate email addresses for employees:
- If they have an email column (even if NULL), use it
- If email is NULL, generate one as: first 3 letters of first name + last name + ‘@company.com’, all lowercase
Show first_name, last_name, and generated_email.
Hint
Use COALESCE with CONCAT, LOWER, and SUBSTRING.Answer Key
Challenge 1 Answer
```sql SELECT first_name, last_name, IFNULL(email, 'No Email Provided') AS email, CASE WHEN email IS NULL THEN 'Missing Email' ELSE 'Has Email' END AS email_status FROM employees ORDER BY last_name; ``` **Expected output:** | first_name | last_name | email | email_status | |------------|-----------|-------|--------------| | Jack | Anderson | No Email Provided | Missing Email | | David | Brown | [email protected] | Has Email | | Sam | Clark | No Email Provided | Missing Email | | ... | ... | ... | ... | Clean report handling missing emails.Challenge 2 Answer
```sql SELECT department_name, UPPER(department_name) AS uppercase_name, LOWER(department_name) AS lowercase_name, UPPER(SUBSTRING(department_name, 1, 1)) AS initials FROM departments ORDER BY department_name; ``` **Expected output:** | department_name | uppercase_name | lowercase_name | initials | |-----------------|----------------|----------------|----------| | Business Development | BUSINESS DEVELOPMENT | business development | B | | Customer Support | CUSTOMER SUPPORT | customer support | C | | Data Science | DATA SCIENCE | data science | D | | Engineering | ENGINEERING | engineering | E | | ... | ... | ... | ... | **Note:** Getting initials from multi-word names (like "Business Development" → "BD") requires more advanced string functions (SUBSTRING_INDEX, LOCATE). The simple version above just gets the first letter. **Advanced version for multi-word initials:** ```sql SELECT department_name, CONCAT( UPPER(SUBSTRING(department_name, 1, 1)), IFNULL(UPPER(SUBSTRING(department_name, LOCATE(' ', department_name) + 1, 1)), '') ) AS initials FROM departments ORDER BY department_name; ``` This gets first letters of first two words.Challenge 3 Answer
```sql SELECT employee_id, first_name, last_name, CASE WHEN first_name IS NULL THEN 'Missing first name' WHEN last_name IS NULL THEN 'Missing last name' WHEN department_id IS NULL THEN 'Missing department' WHEN hire_date IS NULL THEN 'Missing hire date' WHEN salary IS NULL THEN 'Missing salary' WHEN email IS NULL THEN 'Missing email' ELSE 'Complete record' END AS missing_field FROM employees WHERE first_name IS NULL OR last_name IS NULL OR department_id IS NULL OR hire_date IS NULL OR salary IS NULL OR email IS NULL ORDER BY employee_id; ``` **Expected output:** | employee_id | first_name | last_name | missing_field | |-------------|------------|-----------|---------------| | 1 | Alice | Johnson | Missing email | | 3 | Carol | Williams | Missing email | | ... | ... | ... | ... | Identifies incomplete employee records. **Note:** If your sample data has no NULLs except email, only those will show.Challenge 4 Answer
```sql SELECT project_name, UPPER(SUBSTRING(project_name, 1, 3)) AS abbreviation FROM projects ORDER BY abbreviation; ``` **Expected output:** | project_name | abbreviation | |--------------|--------------| | API Development | API | | CRM System | CRM | | Data Pipeline | DAT | | Marketing Campaign | MAR | | Mobile App | MOB | | Security Audit | SEC | | Website Redesign | WEB | Three-letter project abbreviations.Challenge 5 Answer
```sql SELECT first_name, last_name, COALESCE( email, CONCAT( LOWER(SUBSTRING(first_name, 1, 3)), LOWER(last_name), '@company.com' ) ) AS generated_email FROM employees ORDER BY last_name; ``` **Expected output:** | first_name | last_name | generated_email | |------------|-----------|-----------------| | Jack | Anderson | [email protected] | | David | Brown | [email protected] | | Sam | Clark | [email protected] | | Eve | Davis | [email protected] | | ... | ... | ... | Email addresses, using existing or generating new ones. **How it works:** COALESCE returns the first non-NULL value. If email exists, use it. Otherwise, generate one from name parts.Key Takeaways
• Use IFNULL/COALESCE to handle NULL values gracefully • IS NULL and IS NOT NULL for NULL checks (never use = NULL) • UPPER() and LOWER() normalize text case • SUBSTRING extracts portions of strings • CONCAT builds strings from parts • Combine these functions for powerful data transformations
Part 1 Mini Challenges Complete!
You’ve practiced all foundational skills through realistic scenarios. Ready for Part 2?
Up Next
Part 2: CASE WHEN (Conditional Logic) → part2_02_case_when.md