Chapter 2: “SQL Basics”

Now that we have a basic understanding of the use cases that SQL has, let's dive deeper into how to use it and the basic syntax and functions that it has to offer. At its core, a SQL query describes what you want to retrieve from a database, not how to do it. SQL is declarative, meaning you describe the outcome you want, and the database engine optimizes the execution. The basic operators that SQL utilizes are SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. We will now break down the essential components of a SQL query starting with the SELECT clause.

SELECT essentially helps you choose what you want to display; it specifies the columns that you want to retrieve.

SELECT name, age FROM students;

This returns the name and age columns from the students table. You can also use expressions, aliases, and functions:

SELECT name, age * 12 AS age_in_months FROM students;

This returns the name and age columns, but now the age column has been transformed into months and is renamed into age_in_months.

The FROM clause tells SQL where (what table) to look for the data. This can refer to a single table, but it can also refer to multiple tables when we are using JOINS or subqueries.

SELECT * FROM courses;

This retrieves every column from the courses table.

The WHERE clause filters rows based on a condition. This helps narrow down results.

SELECT * FROM students WHERE age > 18;

This retrieves every column in the students table where their age is greater than 18. You can use comparison operators (=, >, <, !=) and logical operators (AND, OR, NOT).

ORDER BY lets you sort the result set by one or more columns.

SELECT name, age FROM students ORDER BY age DESC;

This selects the name and age of all students by descending order. Use ASC for ascending (default) and DESC for descending.

To perform calculations like counts, averages, or sums across groups of data, use GROUP BY:

SELECT major, COUNT(*) AS student_count FROM students GROUP BY major;

This gives the number of students in each major.

After using GROUP BY, HAVING filters the grouped results:

SELECT major, COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING COUNT(*) > 10;

This only includes majors with more than 10 students.

To eliminate duplicate rows, use DISTINCT:

SELECT DISTINCT major FROM students;

This returns each unique major only once.

These core clauses are the backbone of almost every SQL query:

  • SELECT: choose what to show
  • FROM: pick the table
  • WHERE: filter rows
  • GROUP BY: group data
  • HAVING: filter groups
  • ORDER BY: sort results

Mastering these fundamentals will allow you to answer powerful questions about your data. In the next chapter, we’ll explore how to combine data across tables using joins and subqueries.

Next: Chapter 3 →