SQL Assignment Help: JOINs, GROUP BY and Subqueries Explained

What most SQL assignments are really testing

Most university SQL assignments test four things: reading data from more than one table (JOINs), summarising it (GROUP BY and aggregates), filtering it correctly (WHERE and HAVING) and using one query inside another (subqueries). If you can explain what each one does in plain words, the syntax follows.

The examples below use three simple tables:

students(student_id, name, gpa)
courses(course_id, course_name)
enrollments(student_id, course_id)

1. JOINs: combining tables

A JOIN matches rows from two tables using a shared column.

SELECT s.name, c.course_name
FROM students s
INNER JOIN enrollments e ON e.student_id = s.student_id
INNER JOIN courses c ON c.course_id = e.course_id;

INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table, even with no match, and fills the gaps with NULL. Use LEFT JOIN when the question says "including those with none", for example courses with no students.

2. GROUP BY and aggregates: summarising data

To count students per course:

SELECT c.course_name, COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_name
ORDER BY student_count DESC;

Every column in the SELECT list must either be inside an aggregate such as COUNT, SUM or AVG, or appear in the GROUP BY. Forgetting this is the most common error message students see.

3. WHERE versus HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

SELECT c.course_name, COUNT(e.student_id) AS student_count
FROM courses c
JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_name
HAVING COUNT(e.student_id) > 10;

If the condition uses COUNT, SUM or AVG, it belongs in HAVING.

4. Subqueries: a query inside a query

To find students with a GPA above the average:

SELECT name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students);

The inner query runs first and returns one number. The outer query then uses it. Try writing the inner query on its own first, check it returns what you expect, then wrap it.

Mistakes that cost marks

A missing join condition. Without ON, you get every row paired with every other row, and the result is huge and wrong.
Comparing to NULL with =. Use IS NULL or IS NOT NULL.
Using INNER JOIN when the question needs LEFT JOIN, so rows with no match disappear.
Counting the wrong column. COUNT(*) counts rows, while COUNT(column) skips NULLs.
No sample data. Test every query on a few rows where you already know the right answer.

How to check your own answer

Say in one sentence what the query should return.
Run it on a small table where you can work out the answer by hand.
Check the row count and the totals, not just that it runs.
Read it back and make sure each clause is there for a reason.

Common questions

What is the difference between INNER JOIN and LEFT JOIN? INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table and fills in NULL where the right table has no match.

When should I use HAVING instead of WHERE? Use WHERE to filter rows before they are grouped, and HAVING to filter the groups after an aggregate such as COUNT or SUM.

Why does my GROUP BY query give an error? Usually a column in the SELECT list is neither aggregated nor listed in the GROUP BY. Add it to the GROUP BY or wrap it in an aggregate.

Can you help with a SQL or database assignment? Yes. Our computer science experts can explain the concepts, walk through your queries and give feedback. You can message us on WhatsApp for a quote, with free rework or a refund if the work does not meet your brief.