pythonassignmenthelp.com

Posted on Apr 24

The SQL Query Mistakes That Cost Me Points in My Database Assignment

#database #sql #programming #beginners

You're staring at your assignment, and the clock is moving way too fast. You wrote your SQL queries, but when you run them, either nothing happens, or your professor's feedback comes back with red marks all over. "Incorrect output," "missing rows," "syntax error"—I've been there, and honestly, it's not just frustrating, it can feel totally unfair. The thing is, SQL looks simple, but it's packed with little traps, especially when you're learning. Most students lose points not because they're lazy, but because SQL is a bit sneaky about what it expects.

I want to walk you through the mistakes that cost me points (and how you can avoid them), show you real examples, and give you the confidence to tackle that next database assignment without the dread.

Why SQL Trips Up So Many Students

When I first learned SQL, I thought it was just about asking questions to a database. But SQL has its own logic, and if you miss a detail—even something as small as a missing comma or a misunderstanding of NULL—it can throw off your whole query. Professors love to give questions that test your understanding of these details. It's not just about writing queries that run, but queries that give the right output.

Let me show you some real-world examples that tripped me up and my students.

Problem Example 1: Getting the Wrong Number of Rows

Suppose you have a table of students and you want to find all students who got an 'A' in any course.

Table: Grades

student_id	course_id	grade
1	CS101	B
2	CS101	A
1	CS201	A
3	CS101	C

You might write:

-- Find students who got an 'A' in any course
SELECT student_id
FROM Grades
WHERE grade = 'A';

What happens?

This query returns all student_id values where the grade is 'A'. But if a student got more than one 'A', their ID appears multiple times.

Output:

student_id
2
1

If student 1 had two 'A's, you'd see 1 twice.

How to fix it:

-- Use DISTINCT to get each student only once
SELECT DISTINCT student_id
FROM Grades
WHERE grade = 'A';

The DISTINCT keyword removes duplicate rows, so each student appears only once.

Why does this matter?

If the assignment says "list all students," professors expect each student once—duplicates lose points. I learned this the hard way after forgetting DISTINCT and getting marked down.

Problem Example 2: The NULL Trap

Imagine a table called Books:

id	title	author
1	Database Magic	Alice Smith
2	Null in Action	(null)
3	SQL Patterns	Bob Lee

You want to find books where the author is unknown.

I used to write:

-- Find books with no author listed
SELECT title
FROM Books
WHERE author = '';

But... this query returns nothing. That's because in SQL, a missing value is NULL, not an empty string.

The correct way:

-- Find books with a NULL author
SELECT title
FROM Books
WHERE author IS NULL;

IS NULL checks for missing values. = doesn't work with NULLs!

Why does this matter?

If you compare author = NULL, SQL always returns false. This is a classic way to get zero results by accident, and it's a super common mistake on assignments.

Problem Example 3: Filtering After Aggregation

Suppose you have a table Sales:

id	product	quantity
1	Apple	10
2	Orange	5
3	Apple	7

You want to find products that have total sales greater than 10.

A student might write:

-- This doesn't work as expected
SELECT product, SUM(quantity) as total_quantity
FROM Sales
WHERE SUM(quantity) > 10
GROUP BY product;

This gives an error:

"aggregate functions are not allowed in WHERE"

Why?

In SQL, WHERE filters rows before grouping. You can't use an aggregate function like SUM() inside a WHERE clause.

The fix: Use HAVING

-- Correct approach: filter after aggregation using HAVING
SELECT product, SUM(quantity) as total_quantity
FROM Sales
GROUP BY product
HAVING SUM(quantity) > 10;

HAVING filters groups after aggregation. Use it for things like SUM, COUNT, etc.

What does this return?

product	total_quantity
Apple	17

Oranges are filtered out because their total quantity is only 5.

If you're stuck on a similar SQL/Database project, this resource has helped students work through these concepts with more practice examples and explanations.

Common Mistakes Students Make

I've seen these trip up students in office hours, and honestly, I made them myself:

1. Mixing up WHERE and HAVING

Remember:

Use WHERE to filter rows before grouping.
Use HAVING to filter groups after aggregation.

If you write WHERE COUNT(*) > 1, you'll get an error. It must be HAVING COUNT(*) > 1.

2. Forgetting to Alias Columns After Aggregation

If you use something like SUM(quantity) in your SELECT, but later try to reference it (say, in ORDER BY), SQL might not recognize it unless you give it an alias:

SELECT product, SUM(quantity) AS total_sales
FROM Sales
GROUP BY product
ORDER BY total_sales DESC;

Here, total_sales is the alias. Without it, you might get confusing errors or have to repeat the expression.

3. Thinking SQL Is Like Python or Java

This one got me all the time. SQL is declarative, not procedural. That means you tell it what you want, not how to do it step by step. If you find yourself thinking in "for loops" or "if statements", try to reframe your query in terms of "give me all the rows where...".

Key Takeaways

Always use DISTINCT if you want unique rows—especially in SELECT statements where duplicates can lose points.
Remember, IS NULL is the only way to check for missing values in SQL. = and != don't work with NULL.
Use HAVING for filtering after aggregation (SUM, COUNT, etc.), and WHERE for filtering before grouping.
Alias your columns with AS when using aggregate functions to make your queries readable (and avoid errors).
Read the question carefully—professors often hide requirements in the assignment wording.

You might feel stuck, but every SQL query you write gets you closer to really understanding how databases think. The mistakes you make now are the ones you'll never forget. Keep practicing, and you’ll be surprised at how quickly it starts to click.

Want more SQL/Database tutorials and project walkthroughs? Check out https://pythonassignmenthelp.com/programming-help/database.

DEV Community