Data Analysis, Databases, and Pattern Recognition
Relational basics
Rows are tuples, columns are attributes. A primary key is unique and non-null; a foreign key references another table's primary key and enforces referential integrity. Normalization: 1NF = atomic values; 2NF = no partial dependency on part of a composite key; 3NF = no transitive dependency. ACID means atomicity, consistency, isolation, durability.
SQL
Clause order: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY. WHERE filters rows before aggregation; HAVING filters groups after aggregation. INNER JOIN keeps matches only; LEFT JOIN keeps all left-side rows with NULLs where unmatched. Aggregates (COUNT, SUM, AVG, MIN, MAX) ignore NULLs, and NULL = NULL evaluates to unknown, so use IS NULL. Indexes speed reads and slow writes.
Analysis and patterns
Mean is outlier-sensitive; median is robust. Correlation does not imply causation, and watch for sampling bias and Simpson's paradox. Regex basics: ^ start anchor, $ end anchor, . any character, * zero or more, + one or more, ? optional, [abc] character class, \d digit, \w word character, \s whitespace.
Traps
COUNT(*) versus COUNT(column) with NULLs; joins multiplying rows and inflating SUM; swapping HAVING and WHERE; assuming a well-formed query returns sorted rows without ORDER BY.
Worked example
SELECT dept, COUNT() FROM emp GROUP BY dept HAVING COUNT() > 5; - returns only departments with more than five employees. Rule of thumb: if the condition mentions an aggregate, it belongs in HAVING, not WHERE.