SQL for Data Scientists: Advanced Queries

Imagine you are a data scientist working with a school-chain analytics team that tracks exam performance across hundreds of CBSE schools. Nobody hands you one clean spreadsheet with "student name" and "score" sitting in the same row, ready to average. Instead, you get access to a database with a Students table (names, cities, admission numbers) and a completely separate Marks table (which student, which subject, which score) that lives in a different part of the same database. Your very first job, before you touch a single statistic, is to put these two tables back together correctly — and to do it in a way that does not quietly delete students who happen to have missing data. Get this step wrong, and every average, every "top performer" list, every chart you build afterward is silently biased. This is the real starting point of data science with SQL: not writing clever formulas, but reassembling and summarizing data that was never given to you as one table in the first place.

Why One Table Is Never Enough

You have probably already written queries like SELECT name FROM Students WHERE city = 'Pune';. That works perfectly when everything you need lives in one table. But real organisations almost never store data that way, and there is a good reason: if you stored a student's name and city inside every single row of their exam marks, you would repeat "Aarav, Mumbai" nine times if Aarav has nine mark entries. Change Aarav's city once and you would have to update it in nine places, and if you missed even one, your database would contain two different "true" answers for where Aarav lives. Database designers split data into separate tables specifically to avoid this kind of repetition and contradiction — a student's profile lives once in Students, and each exam result lives once in Marks, linked back to the student by an ID number. This is called normalization, and it is exactly why a data scientist's first move on any new dataset is almost always a JOIN: the operation that stitches related tables back together using that shared ID.

Meet the Dataset

We will use one small, fully traceable dataset for this entire chapter so you can verify every number yourself. Here is the schema — two tables connected by student_id:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(20),
    city       VARCHAR(20)
);

CREATE TABLE Marks (
    mark_id    INT PRIMARY KEY,
    student_id INT,
    subject    VARCHAR(20),
    score      INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

Notice something on purpose: Zara has only one subject recorded, and Ishaan has zero rows in Marks at all — perhaps his Science paper is still being evaluated. Real school and hospital and transaction databases are full of students, patients, and customers with incomplete records exactly like this. A chapter that only shows you "clean" joins where every row matches perfectly would be teaching you a fantasy version of SQL. Ishaan is in this dataset deliberately, and you will see exactly what happens to him under two different kinds of JOIN.

INNER JOIN: Reassembling Data Living in Two Tables

An INNER JOIN combines two tables by matching rows where a condition is true, and keeps only the rows where a match was found on both sides. Here, we match every row in Marks to the one row in Students whose student_id is equal:

SELECT s.name, m.subject, m.score
FROM Students s
INNER JOIN Marks m ON s.student_id = m.student_id;

Read the ON clause as the matching rule: "glue a Students row to a Marks row only when their student_id values agree." SQL walks through every row of Marks (9 rows), looks up the Students row with the same student_id, and produces one combined output row per match. Aarav's two mark rows each get glued to the single Students row for Aarav, so Aarav's name appears twice in the output — once next to Math, once next to Science. Trace it fully: Aarav/Math/88, Aarav/Science/91, Diya/Math/72, Diya/Science/78, Kabir/Math/95, Kabir/Science/89, Meera/Math/60, Meera/Science/68, Zara/Math/65. That is exactly 9 rows — one for every row that existed in Marks — because every single mark row found a matching student. Ishaan produces nothing at all: he has no row in Marks, so there is nothing for his Students row to match against, and INNER JOIN simply never mentions him.

The Silent Trap: Joining Without a Condition

Here is a genuine beginner mistake that is easy to make by accident, especially when copying old-style comma-separated table syntax: writing a join and forgetting the ON condition entirely.

SELECT s.name, m.subject
FROM Students s, Marks m;

Without an ON (or an equivalent WHERE condition tying the tables together), SQL has no matching rule to follow, so it does the only thing it can: it pairs every row of Students with every row of Marks, regardless of whether they belong together. This is called a Cartesian product, or cross join. With 6 students and 9 marks rows, that is 6 × 9 = 54 output rows — Meera paired with Kabir's Math score, Ishaan paired with Aarav's Science score, all nonsense combinations that have no basis in reality. The query will run without any error message, which is exactly what makes this mistake dangerous: a data scientist who does not check row counts can build an entire analysis on top of 54 fabricated pairings instead of 9 real ones and never notice, because SQL never complains. The habit that protects you: after any join, always sanity-check the row count against what you expect before trusting the output.

LEFT JOIN: Keeping the Rows That Don't Match

INNER JOIN quietly erased Ishaan from the result. For a report card, that might be acceptable — no marks, nothing to show. But for a data scientist computing a class average or a "how many students have we assessed so far" count, silently dropping Ishaan is a genuine analytical error: it makes it look like every student has been assessed, when one has not. This is the single most common way beginner data analysis goes wrong — an innocent-looking JOIN throws away exactly the rows that matter most, the ones representing missing or incomplete data.

LEFT JOIN fixes this by keeping every row from the left-hand table no matter what, filling in NULL (SQL's marker for "no value") wherever no match exists on the right:

SELECT s.name, m.subject, m.score
FROM Students s
LEFT JOIN Marks m ON s.student_id = m.student_id;

Trace it: every row Students appears on the left, so all 6 students are guaranteed to appear at least once in the output. The same 9 matched rows from before are produced exactly as they were, and Ishaan now gets one extra output row — Ishaan / NULL / NULL — instead of vanishing. Total output: 9 matched rows + 1 unmatched row for Ishaan = 10 rows. Nothing about the 9 real matches changed; the only difference from INNER JOIN is that the unmatched student is now visible, flagged with NULLs, instead of silently missing. That visibility is the entire point: a NULL is a signal you can filter on, count, or investigate. A missing row is invisible and easy to forget about entirely.

Counting What's Missing: COUNT(*) versus COUNT(column)

Once you have the LEFT JOIN result, one line of SQL tells you exactly how much data is missing — a question every data scientist asks about a new dataset within the first five minutes:

SELECT COUNT(*) AS total_rows,
       COUNT(m.score) AS rows_with_marks
FROM Students s
LEFT JOIN Marks m ON s.student_id = m.student_id;

COUNT(*) counts rows, full stop — it does not look inside any column, so it returns 10, the total number of rows the LEFT JOIN produced. COUNT(m.score) behaves differently: it counts only the rows where m.score is not NULL, so Ishaan's NULL row is skipped and it returns 9. The gap between these two numbers — 10 - 9 = 1 — tells you precisely how many students have missing marks, without you having to scroll through the data by eye. On a real dataset of forty thousand students this exact pattern, COUNT(*) minus COUNT(column), is how a data scientist profiles data quality before doing anything else with it.

GROUP BY: Turning Rows into Summaries

So far every query has returned one output row per input row. A data scientist usually wants the opposite: collapse many rows into one summary number per category. Think of GROUP BY as sorting your 9 mark rows into physical buckets — one bucket labelled "Math", one labelled "Science" — and then asking a question about the contents of each bucket separately, rather than about all 9 rows mixed together.

SELECT subject, AVG(score) AS avg_score, COUNT(*) AS n
FROM Marks
GROUP BY subject;

Trace the Math bucket by hand: it contains 88, 72, 95, 60, 65 — five scores. Sum them: 88 + 72 = 160, +95 = 255, +60 = 315, +65 = 380. Divide by the count of 5: 380 ÷ 5 = 76.0. Now the Science bucket: 91, 78, 89, 68 — four scores. Sum them: 91 + 78 = 169, +89 = 258, +68 = 326. Divide by 4: 326 ÷ 4 = 81.5. The query returns exactly two rows — Math with average 76.0 and count 5, Science with average 81.5 and count 4 — because GROUP BY subject collapsed nine raw rows into one summary row per distinct subject value. This is precisely what a pandas groupby().mean() does in Python; SQL's GROUP BY is the same idea, just running one layer closer to where the data actually lives, before it is ever pulled into your analysis notebook.

HAVING versus WHERE: The Misconception That Breaks Queries

A very natural instinct, once you know WHERE filters rows, is to try filtering on an aggregate the same way:

SELECT subject, AVG(score) AS avg_score
FROM Marks
WHERE AVG(score) > 80
GROUP BY subject;

This looks reasonable but fails, and understanding why teaches you something fundamental about the order SQL actually works in. WHERE filters individual raw rows before any grouping or averaging has happened — at the moment WHERE runs, SQL is still looking at 9 separate mark rows one at a time, and there is no such thing yet as "the average of this subject" for any single row to be compared against. In MySQL this raises error 1111, "Invalid use of group function," because you are asking a per-row filter to evaluate a per-group calculation that does not exist at that stage of execution. The fix is HAVING, which filters after grouping and aggregating are complete:

SELECT subject, AVG(score) AS avg_score
FROM Marks
GROUP BY subject
HAVING AVG(score) > 80;

Now the sequence is: group the 9 rows into Math and Science buckets, compute AVG(score) for each bucket (76.0 and 81.5), and only then keep the buckets whose average clears 80. Math's 76.0 fails the test and is dropped; Science's 81.5 passes. The query correctly returns exactly one row: Science, 81.5. The rule to memorize is simple once you see the mechanism behind it: WHERE filters the rows going into the grouping; HAVING filters the groups coming out of it.

Subqueries: Letting the Data Set Its Own Threshold

A hardcoded number like "score above 80" is a guess. A data scientist usually wants a threshold that is defined by the data itself, so that it stays correct even after new marks are added tomorrow. A subquery — a complete SELECT statement nested inside another one — does exactly this. First, check what the overall class average actually is:

SELECT AVG(score) FROM Marks;

Sum all 9 scores: 88+91+72+78+95+89+60+68+65. Adding step by step: 88+91=179, +72=251, +78=329, +95=424, +89=513, +60=573, +68=641, +65=706. Divide by 9: 706 ÷ 9 = 78.44 (78.444…, repeating). Now use that average as a live threshold instead of typing 78.44 by hand:

SELECT s.name, m.subject, m.score
FROM Students s
JOIN Marks m ON s.student_id = m.student_id
WHERE m.score > (SELECT AVG(score) FROM Marks);

The inner query runs first and produces the single number 78.44, which the outer query then treats as an ordinary threshold. Trace all 9 scores against 78.44: 88 clears it, 91 clears it, 72 does not, 78 does not (78 is just under 78.44 — a genuinely close call worth noticing, since eyeballing "78 is basically high 70s, surely above average" would give you the wrong answer here), 95 clears it, 89 clears it, 60 and 68 and 65 do not. Four rows survive: Aarav/Math/88, Aarav/Science/91, Kabir/Math/95, Kabir/Science/89 — meaning exactly two students, Aarav and Kabir, have at least one score above the class average.

A second style of subquery uses IN to hand a whole list of values to the outer query, rather than a single number:

SELECT name
FROM Students
WHERE student_id IN (
    SELECT student_id FROM Marks WHERE score > 90
);

The inner query scans Marks for scores above 90 — only 91 and 95 qualify — and returns their student_ids, 1 and 3. The outer query then simply selects the names of students whose id is in that list: Aarav (id 1) and Kabir (id 3). Notice this arrives at the same two names as the average-threshold query above. That is not a coincidence you should ignore — it is a useful cross-check. Two structurally different questions ("who beats the class average in some subject" and "who has ever scored above 90") converged on the same answer because Aarav and Kabir really are the consistent high performers in this small dataset. When two independent queries on the same data agree, that agreement is itself evidence your logic is sound — a habit worth carrying into every real analysis you run.

Why This Belongs in a Data Scientist's Toolkit

Every technique in this chapter maps directly onto the first hour of a real data science task, not just abstract database theory. Before you can plot a single chart or train a single model, you almost always have to: (1) reassemble data spread across multiple tables with JOIN, because production databases are normalized, not flattened for your convenience; (2) decide, using LEFT JOIN and NULL-counting, whether "missing" students or transactions are being silently dropped or honestly represented, because a biased sample produces a biased average no matter how correct your later statistics are; (3) collapse raw rows into per-category summaries with GROUP BY, because nobody can read meaning out of nine thousand raw rows, only out of a short summary table; and (4) define thresholds relative to the live data with subqueries instead of hardcoding numbers that go stale the moment new data arrives. There is also a practical performance reason data scientists reach for SQL before Python: GROUP BY and AVG run inside the database engine, right next to where the data is stored, so only the small summarized result — two rows, in our Math/Science example, not nine thousand raw rows — ever needs to travel across the network into your pandas notebook. Doing the heavy aggregation in SQL first, and only pulling the compact result into Python for modelling or plotting, is standard practice once a table grows from nine rows to nine million.

The Cost of Getting the Join Wrong

Common Mistakes to Watch For

• Writing a join with no ON condition and getting a Cartesian product — always check that your row count matches what you expect (rows in the smaller matching table, not the product of both table sizes).
• Using INNER JOIN by default and never checking whether unmatched rows exist. If you have not deliberately chosen INNER over LEFT, you may be silently discarding real data.
• Filtering an aggregate with WHERE instead of HAVING. If your filter mentions AVG(), SUM(), COUNT(), or another aggregate function, it belongs in HAVING, after GROUP BY.
• Comparing a raw score against a hardcoded number when the honest comparison is against the dataset's own average, computed by a subquery — hardcoded thresholds go stale; subquery thresholds update themselves.
• Trusting COUNT(*) alone to mean "how many students have data." Compare it against COUNT(column) on the specific column you care about to see how much is actually missing.

Check Your Understanding

1. Using the Students and Marks tables above, how many rows does SELECT s.name, m.score FROM Students s INNER JOIN Marks m ON s.student_id = m.student_id; return, and which one student is guaranteed to be completely absent from the output?
2. Rewrite that same query as a LEFT JOIN. How many rows does it return now, and what two values appear in the extra row?
3. A classmate writes SELECT subject FROM Marks WHERE AVG(score) > 75 GROUP BY subject; and it fails. Name the clause that is wrong, explain in one sentence why SQL cannot evaluate it at that point in the query, and rewrite it correctly.
4. Using GROUP BY subject on the Marks table, which subject has the higher average, and what are the two average values (show your addition and division)?
5. Write a subquery that finds the names of every student who has at least one score strictly above 90. State the two intermediate values the inner query produces before naming the final answer.

Answer key: (1) 9 rows; Ishaan is absent because he has no row in Marks to match against. (2) 10 rows; the extra row holds Ishaan's name paired with NULL for subject and NULL for score. (3) The WHERE clause is wrong — WHERE filters individual rows before grouping happens, so no per-group AVG() exists yet for it to compare against; the fix is SELECT subject FROM Marks GROUP BY subject HAVING AVG(score) > 75;. (4) Science is higher: Math sums to 380 across 5 scores for an average of 76.0, Science sums to 326 across 4 scores for an average of 81.5. (5) The inner query SELECT student_id FROM Marks WHERE score > 90 produces the two ids 1 and 3; the outer query then returns the names Aarav and Kabir.