Freaks City

Relational Schema Design When tracking academic performance, a marks table typically requires references to both learners and courses rather than storing redundant textual data. CREATE TABLE marks ( mark_id INT PRIMARY KEY AUTO_INCREMENT, pupil_ref INT, course_ref INT, mark_value DECIMAL(5,2) ); The pupil_ref and course_ref col ...

Multi-Table Joins Outer Joins Outer joins extend result sets beyond matching rows by preserving unmatched records from one or both sides. LEFT JOIN: Returns all rows from the left table and matched rows from the right; unmatched right-side entries appear as NULL. RIGHT JOIN: Returns all rows from the right table and matched rows from the left; ...

Problem 1: August Practice Statistics for Fudan University Users Context: Calculate the total number of questions practiced and the number of correct answers for users from Fudan University specifically in August. Filtering: Match users where university = '复旦大学' in the user_profile table. Time Constraint: Filter records from August using M ...

Pre-filteirng Joined Tables When joining tables, applying filters before the join ipmroves performance and clarity. Instead of filtering after the join with WHERE, embed the condition in a derived table: SELECT * FROM table_a a LEFT JOIN ( SELECT * FROM table_b WHERE month_value = 1 ) b ON a.identifier = b.foreign_key WHERE a.status = ' ...

Foreign Keys Why Foreign Keys? Before foreign keys, merging every thing into one table caused issues: Unclear focus: Hard to separate employee vs. department data. Redundant storage: Same fields repeated across rows. Poor scalability: Changing one part affected the whole table. Solution: Split into multiple tables (e.g., emp and dep) and use ...

Sorting Query Results The ORDER BY clause controls how rows are returned. It does not alter the stored data, only the presentation. SELECT column_list FROM table_name WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]; Single‑column ordering Sort employees by age in descending order: SELECT name, age FROM employees ORDER BY age DE ...