Video summary

Solve 70 SQL Questions in 3 hrs | Ultimate SQL Practice | Master SQL

Main summary

Key takeaways

Educational

Main ideas / lessons

  • Purpose of the video: Practice 70 SQL questions end-to-end by:

    1. Reading each prompt
    2. Writing the SQL query
    3. Validating the result

  • Why manual practice matters: Even if people use AI tools (ChatGPT/DeepSeek/copilots), practicing queries yourself:

    • improves understanding of indexes and which columns matter
    • strengthens skills in selecting fields, joining tables, and producing data in the required format
    • prepares you for “old-school” interviews where you must write SQL without assistance

  • Learning approach used throughout: For each question, the speaker repeatedly breaks the task into smaller parts, such as:

    • choosing the right base table
    • applying filters (WHERE)
    • applying patterns (LIKE)
    • handling null logic (IS NULL, IS NOT NULL)
    • combining tables using joins
    • aggregating with COUNT, SUM, AVG, MAX, MIN
    • using GROUP BY + HAVING for grouped conditions
    • using subqueries when needed
    • using window functions (notably LAG) when comparing with previous rows/dates
    • using CASE expressions for conditional logic, derived columns, and scoring/counting

Methodology / instruction-style content (SQL building checklist)

Writing a basic query

  1. Identify the table(s) referenced.
  2. Start with FROM <table>.
  3. Add filters with WHERE when conditions exist.
  4. Pick required outputs using SELECT <columns>.

String filtering

  • Starts with: WHERE column LIKE 'C%'
  • Ends with: WHERE column LIKE '%s'
  • Contains: WHERE column LIKE '%pattern%'

Null handling

  • “Does not have allergies” → WHERE allergies IS NULL
  • “Allergies present” → WHERE allergies IS NOT NULL

Range conditions (inclusive)

WHERE weight >= 100 AND weight <= 120

Updates

UPDATE patients
SET allergies = 'NK'
WHERE allergies IS NULL

Concatenation (full name formatting)

  • Use: CONCAT(first_name, ' ', last_name)
  • Optionally alias it.

Joining two tables

  • General form: JOIN <other_table> ON <key_matching_condition>
  • Example idea: patients.province_id = province_names.province_id

Aggregations

  • Counts/sums: SELECT COUNT(*) ...
  • Average/max/min: AVG(), MAX(), MIN()

Unique values

  • Use: SELECT DISTINCT <expression>

Grouped uniqueness (unique first names appearing once)

  • Use GROUP BY first_name
  • Then filter groups using HAVING COUNT(*) = 1

Filtering after grouping

  • Always use HAVING for conditions on aggregates, not WHERE.

Subqueries for “row matching max”

  • Compute MAX(height) in an inner query
  • Match outer rows where height = (inner query result)

Multi-value matching

  • Use IN (...) for a set of patient IDs.

Window function for previous-row comparison

  • First aggregate per day/date
  • Then compute changes using:
    • LAG(...) OVER (ORDER BY date_column)

Conditional derived fields

Use CASE WHEN ... THEN ... ELSE ... END for:

  • gender mapping (“M”→“male”, “F”→“female”)
  • insurance yes/no
  • on-time/late/not-shipped categories
  • obesity classification
  • ordering priority (e.g., Ontario first)
  • cost/score computations

SQL concepts exercised (mapped to what the video did)

Easy section (frequent foundations)

  • SELECT, WHERE, IS NULL
  • LIKE patterns (starts/contains)
  • numeric comparisons and inclusive ranges
  • UPDATE
  • CONCAT
  • JOIN (e.g., province names)
  • aggregates: COUNT(*)
  • nested query: MAX(height)
  • IN clause
  • filtering admissions by same-day condition

Medium section (more advanced querying)

  • DISTINCT (unique birth years)
  • GROUP BY + HAVING (unique first names)
  • pattern constraints with length logic (name starting/ending with certain letter and total length)
  • multi-table joins for diagnosis-based filters
  • ordering by derived sort keys:
    • alphabetical vs length-based sorting
  • two-column totals in one row:
    • conditional counts / case-based counting of male vs female
  • finding duplicates:
    • GROUP BY first_name, last_name + HAVING COUNT(*) > 1
  • converting and formatting values:
    • unit conversions (cm→feet, kg→pounds)
    • rounding via ROUND
    • gender expansion with CASE
  • “latest admission” per patient:
    • GROUP BY patient_id + HAVING admission_date = MAX(admission_date)
  • complex combined admissions/date statistics:
    • max/min/avg admissions per day using a derived grouped table (subquery)

Hard section (complex logic, windowing, and multi-join reasoning)

  • Binning/grouping with intervals (weight groups using floor math)
  • Calculated boolean flags (obesity using a BMI-like formula) and correction via proper CASE branching
  • Multi-join with additional diagnosis constraints
  • “patients without admissions” using NOT IN subquery
  • Insurance/cost computation:
    • conditional CASE per patient ID parity and sum of costs
  • Comparing male vs female counts per province:
    • grouped counts with CASE-based male counting
  • Highly-specific filters with multiple constraints:
    • complex LIKE + month extraction from dates + odd/even ID + city constraint
  • Percentage calculation with rounding:
    • male percentage = (male_count / total_count) * 100, rounded to nearest 0.01
  • Day-to-day admissions change using LAG
  • Custom ordering priority using CASE in ORDER BY
  • Doctor-wise admissions per year:
    • join doctors + admissions
    • group by doctor and year
    • count total admissions
  • Final large multi-join arithmetic:
    • company discount loss per year using joins between orders → order details → products
    • computing unit_price * quantity * discount

Speakers / sources featured

  • Speaker: Akan (video host/instructor)
  • Platform/source used for questions: sqlpractice.com
  • Additional resources mentioned:
    • Discord server created by the speaker
    • Topmate link for one-on-one meetings
    • LinkedIn page for connection
    • Tools referenced (not as data sources): ChatGPT, DeepSeek, copilots, cursors/agents

Databases in exercises

  • Hospital database: patients, admissions, doctors, province-related tables
  • Northwind database: customers, orders, products, suppliers, categories, employees, etc.

Original video