SQL Basics: Core Commands Every CS Student Should Know

Why Every CS Student Ends Up Learning SQL

Whether your degree track is data science, backend development, or systems, at some point a course requires you to query a relational database — and SQL (Structured Query Language) is the language every relational database understands, from a first-semester assignment to a production system handling millions of rows. Unlike most programming languages, SQL is declarative: you describe what data you want, not the steps to fetch it. That's exactly why it reads almost like English once the core commands click.

SELECT: Getting Data Out

Every query starts with what columns you want and from which table:

SELECT name, gpa FROM students;

Want everything? Use *, though naming columns explicitly is better practice once tables get wide:

SELECT * FROM students;

WHERE: Filtering Rows

WHERE narrows results to rows matching a condition — this is the command students reach for most and often the one they get subtly wrong:

SELECT name, gpa FROM students WHERE gpa > 3.5;

Combine conditions with AND / OR, and use parentheses when mixing both — SQL evaluates AND before OR by default, and skipping the parentheses is a classic source of a query that "runs fine but returns the wrong rows":

SELECT name FROM students
WHERE major = 'Computer Science' AND (gpa > 3.5 OR year = 4);

JOIN: Combining Tables

Real databases split data across multiple related tables — a students table and a separate enrollments table, linked by a shared ID — rather than repeating information. JOIN is how you query across that split:

SELECT students.name, courses.title
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.id;

The most common assignment mistake: using INNER JOIN when the question actually requires LEFT JOIN — for example, "list every student and their enrolled courses, including students enrolled in nothing" silently drops those students under an INNER JOIN.

GROUP BY and Aggregate Functions

GROUP BY collapses rows sharing a value into groups, almost always paired with an aggregate function:

SELECT major, AVG(gpa) AS average_gpa
FROM students
GROUP BY major;

The rule that trips people up: every column in SELECT must either be inside an aggregate function or listed in GROUP BY. SELECT major, name, AVG(gpa) ... GROUP BY major fails in strict SQL engines because name is neither aggregated nor grouped — the database can't decide which student's name to show for a group of many.

HAVING vs. WHERE

WHERE filters rows before grouping; HAVING filters groups after aggregation — a distinction that only matters once a query needs both:

SELECT major, AVG(gpa) AS average_gpa
FROM students
WHERE year = 4
GROUP BY major
HAVING AVG(gpa) > 3.0;

This reads as: "among fourth-years, group by major, then keep only majors averaging above 3.0." Trying to put AVG(gpa) > 3.0 in WHERE instead throws an error — aggregates don't exist yet at the row-filtering stage.

ORDER BY and LIMIT

Sort results and cap how many rows come back:

SELECT name, gpa FROM students
ORDER BY gpa DESC
LIMIT 10;

DESC for highest-first, ASC (the default) for lowest-first.

Putting It Together

A single query combining everything above — the top 5 majors by average GPA among students with at least 10 people in the major:

SELECT major, AVG(gpa) AS average_gpa, COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING COUNT(*) >= 10
ORDER BY average_gpa DESC
LIMIT 5;

Read it top to bottom in execution order — filter rows, group them, filter groups, sort, then limit — and most SQL queries stop feeling like a puzzle.

Where This Goes Wrong on Real Assignments

Database assignments rarely fail because a student doesn't know SELECT exists — they fail on join direction (inner vs. left), the WHERE vs. HAVING split, or a GROUP BY clause missing a column that's technically required. If a query is returning almost-right results — missing rows, duplicated rows, or an aggregate that doesn't match a manual count — that's usually one clause away from correct, and exactly the kind of debugging the programming help service can walk through with you, line by line, until the logic clicks rather than just handing back a fixed query.