Solution - Like and Wildcards in SQL

Understanding database challenges begins with navigation fundamentals. When searching for users with Gmail addresses, your first obstacle is identifying the correct table. Database familiarity develops over time—much like learning a new city's geography. Initially, you won't remember which streets lead where or what amenities exist in each neighborhood. Similarly, you'll need frequent references to your database schema until table names and relationships become second nature.

This learning curve intensifies when working with databases designed by other developers. Their naming conventions and organizational logic may differ significantly from your preferences, requiring additional time to internalize the structure. However, this initial investment in understanding pays dividends as your queries become more efficient and accurate.

For comprehensive database visualization, Entity Relationship (ER) diagrams provide invaluable oversight. Rather than opening individual tables sequentially, double-clicking the tables section in your ER diagram reveals all tables with their columns simultaneously. This bird's-eye view enables rapid relationship identification and column scanning without the screen real estate limitations of multiple open windows.

ER diagrams serve as your database roadmap, displaying table relationships and column structures in a unified interface. This visualization proves essential for complex queries involving multiple table joins or when exploring unfamiliar database schemas. The ability to see interconnections at a glance reduces query development time and minimizes relationship errors.

All modern database management systems support ER diagram generation, though implementation varies. DBeaver excels in simplicity—double-clicking generates comprehensive diagrams instantly. SQL Server Management Studio requires manual creation: right-click to create new diagrams and select desired tables individually. While DBeaver offers the most streamlined approach, every professional database tool provides relationship visualization capabilities, recognizing their fundamental importance for database comprehension and query optimization.

Examining our ER diagram reveals that user email addresses reside in the users table's email column. For Gmail user identification, we need all user data filtered by email domain. This requires selecting all columns from the users table where email contains "@gmail.com"—displaying complete user profiles exclusively for Gmail addresses.

Our training database contains merely 100 users, facilitating learning but misrepresenting production environments. Real-world databases typically contain thousands, hundreds of thousands, or millions of user records. This scale difference creates exponentially more opportunities for data variation and edge cases that can derail poorly constructed queries.

Consider potential false positives when using wildcard searches for "gmail": addresses like "huntingmail.com" or "workingmailbox.com" contain "gmail" but aren't Gmail addresses. These variations become increasingly problematic as dataset size grows. Each wildcard functions as a precision bomb—potentially including far more results than intended.

Wildcards live up to their name by matching unexpectedly broad patterns. The fundamental principle: use minimum necessary wildcards. Avoid adding wildcards "just in case"—this approach inevitably captures unintended results. Your goal should be maximum precision with minimum wildcard usage, not comprehensive wildcard coverage.

For Gmail address identification, the safest approach specifies "%@gmail.com" without trailing wildcards. This pattern captures any characters before the Gmail domain while ensuring exact domain matching. This method eliminates false positives from domains containing "gmail" as a substring while maintaining complete Gmail address coverage.

Production databases amplify wildcard risks through increased data diversity. Always validate results for false positives—unintended matches that satisfy your pattern but fail your actual requirements. Treat wildcards with respect; use them only when necessary, not as default solutions. The larger your dataset, the more critical this precision becomes.

When filtering orders by multiple states—Florida or Texas—SQL provides several syntactic approaches. The IN ('FL', 'TX') operator offers the most efficient code, checking if the ship_state value exists within the specified list. This approach minimizes typing and enhances readability compared to multiple OR conditions.

Alternatively, traditional OR logic works identically: ship_state = 'FL' OR ship_state = 'TX'. Both methods produce identical results with equivalent performance. The IN operator simply reduces code verbosity, particularly valuable when checking against larger value sets. Choose based on team coding standards and personal preference—both approaches are professionally acceptable.

Note that IN operates exclusively with equality matching—wildcards aren't supported. For pattern matching across multiple values, use separate LIKE conditions connected with OR operators. This distinction becomes crucial when building complex filtering logic involving both exact matches and pattern matching.

Retrieving the five most recent New York orders demonstrates SQL's structured syntax requirements. First, filter for New York orders using the WHERE clause. Then apply ORDER BY created_at DESC to sort by creation date in descending order (newest first). Finally, LIMIT 5 restricts results to the top five records. This clause order is mandatory—LIMIT must follow ORDER BY in SQL's structured syntax.

Multi-condition filtering, such as products containing "plate" priced above $20, requires the AND operator. Begin with the products table, applying WHERE price > 20 AND title ILIKE '%plate%'. The greater-than operator handles price filtering, while ILIKE with wildcards manages case-insensitive text matching. Consider whether "more than $20" includes exactly $20—business requirements often assume inclusive ranges despite imprecise language.

Condition order flexibility allows logical query building. You might filter by price first to reduce the dataset, then apply text matching. SQL's ability to execute partial queries by selecting specific lines enables iterative development—test individual conditions before combining them. This approach proves invaluable for debugging complex queries and verifying intermediate results.

Excluding products containing "rubber" demonstrates negative filtering with NOT operators. Start by identifying rubber products using WHERE title ILIKE '%rubber%', verify the results capture intended items, then add NOT: WHERE title NOT ILIKE '%rubber%'. This positive-then-negative approach prevents typos and logic errors from producing empty result sets.

The NOT operator can precede either the column name or the LIKE operator with identical results. However, establishing the positive case first ensures your pattern matching works correctly before inversion. This methodology becomes essential with wildcards, where subtle errors can drastically alter results.

Handling spelling variations like "gray" versus "grey" presents multiple solution approaches. The IN ('gray', 'grey') method provides maximum clarity and precision. Alternative approaches using LIKE 'gr_y' (underscore for single character) or LIKE 'gr%y' (percentage for multiple characters) work in small databases but introduce unnecessary risks.

Code clarity trumps character savings. The wildcard approaches lack intuitive meaning and could match unintended words if the language evolves or data diversity increases. The IN operator explicitly declares your intentions, making the code self-documenting and maintainable. Your future self and teammates will appreciate the explicit clarity over clever shortcuts.

Remember that you're writing code for humans, not just computers. Even solo developers benefit from clear code—you'll revisit your queries weeks or months later without the original context. Prioritize explicit, readable solutions over abbreviated clever tricks. Use double dashes (--) for comments when additional explanation helps clarify business logic or edge case handling.

Filtering line items by return status demonstrates exact matching for controlled vocabulary fields. Status values typically derive from dropdown menus or predefined lists, ensuring consistent spelling and capitalization. Simply use WHERE status = 'Returned' without wildcards or case-insensitive matching, as the data integrity is guaranteed by the application layer.

For analyzing the most expensive returns by total value (price × quantity), you'll need calculated columns and additional sorting logic. This type of analysis often requires SQL aggregate functions and custom column creation to derive meaningful business insights from transactional data.