SQL and Querying 🗃️

Imagine your school has thousands of student records: names, classes, grades, clubs, and attendance. If a teacher wants to find all students in Grade 11 who play a sport and have attendance above $90\%$, searching one file by hand would be slow and error-prone. SQL helps computers answer questions like this quickly and accurately. In this lesson, students, you will learn how SQL and querying work, why they are useful, and how they fit into the IB Computer Science SL Option Topic Bank.

Lesson objectives:

Explain the main ideas and terminology behind SQL and querying.
Apply SQL reasoning to real-world database problems.
Connect SQL to the wider Option Topic Bank in IB Computer Science SL.
Summarize why querying is important in modern information systems.

By the end, you should be able to read simple SQL statements, understand what they do, and explain how they help people retrieve and manage data in many real systems, from school systems to online shopping and social media 📚

What is SQL and why does it matter?

SQL stands for Structured Query Language. It is a standard language used to communicate with relational databases. A database is an organized collection of data. A relational database stores data in tables, where each table represents one type of thing, such as students, courses, or products.

Each table has rows and columns. A row, also called a record, contains information about one item. A column, also called a field, stores one type of data such as $\text{Name}$, $\text{Age}$, or $\text{Price}$.

SQL matters because it lets users:

retrieve specific data,
add new data,
update existing data,
remove data,
and combine data from multiple tables.

For example, a school database might store student details in one table and exam results in another. SQL can connect those tables and answer questions like: “Which students scored above $80\%$ in Mathematics?” or “How many students are enrolled in each course?”

In IB Computer Science SL, SQL is important because it shows how data is stored, searched, and managed in practical systems. It links directly to databases, data modeling, and problem-solving. ✅

Core terminology: tables, keys, and queries

To understand SQL, students, you need the language of databases.

A query is a request for data or an action on data. In SQL, a query is written as a statement.

A primary key is a field that uniquely identifies each record in a table. For example, $\text{StudentID}$ can be a primary key because no two students should have the same ID.

A foreign key is a field in one table that refers to the primary key in another table. This creates a relationship between tables. For example, $\text{StudentID}$ in an exam results table might link to $\text{StudentID}$ in a student table.

A relational database is powerful because it reduces repetition. Instead of storing the student’s name in every exam record, the name can appear once in the student table, while the exam table stores the ID reference. This makes the database easier to update and less likely to contain errors.

A simple example of a table might look like this:

$\text{Students}(\text{StudentID}, \text{Name}, \text{YearGroup})$
$\text{Results}(\text{ResultID}, \text{StudentID}, \text{Subject}, \text{Score})$

Here, $\text{StudentID}$ links the two tables.

This is an important idea in IB because good database design supports reliable querying. If the data structure is poor, queries can become slow, confusing, or inaccurate.

Basic SQL commands and what they do

SQL has several common command types. The most important for querying are part of DQL (Data Query Language), but students often learn them alongside other SQL commands.

1. Selecting data

The $\text{SELECT}$ statement is used to retrieve data.

Example:

$$\text{SELECT Name FROM Students;}$$

This asks the database to show all values in the $\text{Name}$ column from the $\text{Students}$ table.

If you want more than one column:

$$\text{SELECT Name, YearGroup FROM Students;}$$

2. Filtering data

The $\text{WHERE}$ clause is used to filter records.

Example:

$$\text{SELECT Name FROM Students WHERE YearGroup = 11;}$$

This returns only students in Year $11$.

You can also use comparison operators such as $=, >, <, \geq, \leq,$ and $\neq$.

Example:

$$\text{SELECT Name FROM Students WHERE Age > 16;}$$

3. Sorting data

The $\text{ORDER BY}$ clause sorts results.

Example:

$$\text{SELECT Name, Score FROM Results ORDER BY Score DESC;}$$

This sorts scores from highest to lowest. The keyword $\text{DESC}$ means descending order.

4. Combining conditions

SQL can combine conditions with $\text{AND}$, $\text{OR}$, and $\text{NOT}$.

Example:

$$\text{SELECT Name FROM Students WHERE YearGroup = 11 AND Age > 16;}$$

This returns students who are both in Year $11$ and older than $16$.

These commands are the building blocks of many database tasks. They are simple individually, but together they allow very precise questions to be answered. 💡

Working with multiple tables: joins and relationships

Real databases often store related data in separate tables. To get useful information, SQL uses joins.

A join combines rows from two or more tables based on a related field.

Example: suppose one table stores students and another stores results. If you want each student’s name with their score, SQL can join the tables using $\text{StudentID}$.

A typical join query looks like this:

$$\text{SELECT Students.Name, Results.Subject, Results.Score}$$

$$\text{FROM Students INNER JOIN Results}$$

$$\text{ON Students.StudentID = Results.StudentID;}$$

An INNER JOIN returns only rows where the matching key exists in both tables.

Why is this useful? Because it supports structured, efficient storage. A school does not need to repeat student names in every results row. Instead, the database links tables using keys. This reduces duplication and helps prevent inconsistency. For example, if a student changes their name, it only needs to be updated in one table.

Joins are a strong example of database reasoning in IB Computer Science SL because they show how design choices affect what queries can do. A well-designed relational database makes information easier to retrieve and analyze.

Real-world examples and IB-style problem solving

SQL is used in many places:

online stores to find products, orders, and customers,
hospitals to manage patient records,
libraries to track books and borrowers,
schools to store attendance and grades,
streaming platforms to recommend shows based on viewing data.

Let’s look at a realistic school example.

Suppose a database table $\text{Results}$ contains $\text{StudentID}$, $\text{Subject}$, and $\text{Score}$. If the school wants a list of students who scored at least $85$ in Science, the query could be:

$$\text{SELECT StudentID, Score FROM Results WHERE Subject = 'Science' AND Score >= 85;}$$

This query uses a filter to answer a specific question. In an exam or practical task, you should be able to identify:

the table being used,
the columns needed,
the condition in the $\text{WHERE}$ clause,
and the meaning of the result.

Now consider a more complex example. A school may want the average score for each subject. SQL can group data using $\text{GROUP BY}$.

Example:

$$\text{SELECT Subject, AVG(Score) FROM Results GROUP BY Subject;}$$

Here, $\text{AVG}$ is an aggregate function. It calculates the mean of the values in a column.

This type of query shows how SQL is not only about finding one record. It can also summarize large amounts of data. That is especially useful in analytics, reporting, and decision-making.

When solving IB-style questions, students, think carefully about the data problem first. Then choose the correct SQL structure. Good reasoning matters more than memorizing isolated syntax.

Common mistakes and how to avoid them

Many students make predictable errors when learning SQL.

One common mistake is confusing $\text{WHERE}$ and $\text{HAVING}$. The $\text{WHERE}$ clause filters rows before grouping, while $\text{HAVING}$ filters grouped results. If a question asks for subjects with an average score above $70$, the query may need $\text{HAVING}$ after $\text{GROUP BY}$.

Another mistake is forgetting that text values usually need quotation marks, like $\text{'Science'}$, while numbers do not.

Students also sometimes choose the wrong join type. An $\text{INNER JOIN}$ only shows matching records, while other joins can include unmatched records too. The exact join matters because it changes the final result.

A final issue is unclear reading of the question. Before writing SQL, identify whether the task is to retrieve, sort, filter, combine, or summarize data. This step saves time and reduces errors.

Conclusion

SQL is a powerful language for working with relational databases. It allows users to query data, filter results, sort information, join tables, and summarize records. In IB Computer Science SL, SQL and querying are important because they show how data is organized and accessed in real systems. They also connect to broader ideas in the Option Topic Bank, including data handling, database design, and problem-solving. For students, mastering SQL means being able to turn a real question into a precise database query. That skill is useful in school, work, and everyday technology 🌟

Study Notes

SQL means Structured Query Language.
A relational database stores data in tables made of rows and columns.
A query is a request for data or an action on data.
$\text{SELECT}$ retrieves data from a table.
$\text{WHERE}$ filters records using conditions.
$\text{ORDER BY}$ sorts results.
$\text{AND}$, $\text{OR}$, and $\text{NOT}$ combine conditions.
A primary key uniquely identifies each row.
A foreign key links one table to another.
Joins combine related data from multiple tables.
Aggregate functions such as $\text{AVG}$ summarize data.
SQL is widely used in schools, hospitals, businesses, and online systems.
In IB Computer Science SL, SQL supports database reasoning, querying, and data analysis.