When you're working with SQL in university databases, especially when creating basic queries, it's important to know that even tiny mistakes can lead to annoying problems. The keywords SELECT, FROM, and WHERE are really important in SQL, and using them incorrectly can cause big issues.

Common Mistakes in SQL

One of the most common mistakes is not paying attention to syntax, which means the way the code is written. SQL is very strict; if you don’t follow the right format, your code can break. For example, using the wrong keyword or forgetting to add a comma can ruin the whole query.

Also, think about how you use SELECT. Some students might write:

SELECT * FROM students WHERE enrollment_date = '2023-09-01';

While this looks easy, using the asterisk (*) gets all the columns, which can be slow if the table is big. A better choice is to list only the columns you need:

SELECT student_id, first_name, last_name FROM students WHERE enrollment_date = '2023-09-01';

Choosing specific columns can speed things up, especially with large university databases.

Getting the FROM Clause Right

Another mistake to watch out for is using the FROM clause incorrectly. Make sure the table name is correct. If you spell it wrong, you’ll get an error. For example, this will fail:

SELECT * FROM studens;

That's just a tiny spelling mistake! Always check your table names against your data guide.

Using WHERE to Filter

The WHERE clause can help you filter results, but if used incorrectly, it can give you wrong data. A common mistake is forgetting about null values. For instance, if you run:

SELECT * FROM students WHERE graduation_date = '2023-05-15';

You might miss students who haven't graduated yet or who don’t have a graduation date. A better way is:

SELECT * FROM students WHERE graduation_date IS NOT NULL AND graduation_date = '2023-05-15';

This way, you make sure your results are accurate.

Misusing Logical Operators

If you use logical operators incorrectly, you might get unexpected results. For example:

SELECT * FROM students WHERE major = 'Computer Science' OR major = 'Mathematics' AND GPA > 3.0;

Without parentheses, this could give incorrect results. It may return all Mathematics majors, regardless of their GPA, but only Computer Science students with a GPA over 3.0. To fix this, you should add parentheses:

SELECT * FROM students WHERE major = 'Computer Science' OR (major = 'Mathematics' AND GPA > 3.0);

This makes it clear what you're asking for.

Understanding Data Types

Pay attention to data types when making comparisons in the WHERE clause. Students often forget to match types. For example, if you run:

SELECT * FROM courses WHERE course_number = 101;

If course_number is a string in the database, this won’t work. The correct query would be:

SELECT * FROM courses WHERE course_number = '101';

Always check that your comparisons match the data types.

Avoiding Ambiguity

Ambiguities can happen, especially when joining tables. If two tables have the same column name, it can get confusing. For example, if you join a students table with an enrollments table:

SELECT * FROM students JOIN enrollments ON students.student_id = enrollments.student_id;

This could lead to confusion about which student_id you're talking about. Instead, be clear:

SELECT students.student_id, students.first_name, enrollments.course_id 
FROM students 
JOIN enrollments ON students.student_id = enrollments.student_id;

Case Sensitivity in String Comparisons

Another mistake new learners make is not thinking about case sensitivity in string comparisons. In some databases, like PostgreSQL, this really matters. For example:

SELECT * FROM students WHERE last_name = 'Smith';

This won’t give you the same results as:

SELECT * FROM students WHERE last_name = 'smith';

Using functions like LOWER() can help, although it might slow things down:

SELECT * FROM students WHERE LOWER(last_name) = 'smith';

Handling Duplicates

When there are duplicates, many forget to remove them from their results. Using SELECT DISTINCT can help with this:

SELECT DISTINCT major FROM students;

This gives a clear list of majors without repetition.

Performance Tips

When you write your queries, think about performance. Avoid using calculations in the WHERE clause because it can slow things down. Instead of writing:

SELECT * FROM students WHERE YEAR(enrollment_date) = 2023;

You could rewrite it like this:

SELECT * FROM students WHERE enrollment_date >= '2023-01-01' AND enrollment_date < '2024-01-01';

This way, the database can work more efficiently.

Documenting Your Queries

Finally, don’t forget to write comments in your code. This can help you and others understand your queries later:

-- Get students who graduated in May 2023
SELECT * FROM students WHERE graduation_date BETWEEN '2023-05-01' AND '2023-05-31';

This will help future collaborators understand your thinking.

Keep Learning

Working with SQL is not just about avoiding mistakes; it's also about improving your skills. Practicing and using resources like SQL tutorials or workshops can give you helpful tips. Staying updated on SQL news can also help you get better.

By avoiding these common mistakes, you’ll get better at writing effective SQL queries. Paying attention to details in SELECT, FROM, and WHERE will give you a strong base for writing advanced queries later. With time and practice, you can become great with SQL. In databases, attention to detail is key, and avoiding these errors is the first step to mastering SQL!