SQL and Querying

Welcome, students 👋 In this lesson, you will explore SQL and querying, a core part of working with databases in computer science. SQL is the language used to communicate with relational databases, and querying is the process of asking a database for specific information. These skills matter in real life every day, from searching a school results system to finding products on an online store.

By the end of this lesson, you should be able to:

• Explain the main ideas and terminology behind SQL and querying.
• Use SQL to select, filter, sort, and combine data.
• Understand how SQL supports accurate and efficient data retrieval.
• Connect SQL and querying to the wider Option Topic Bank in IB Computer Science HL.
• Use examples and reasoning to solve database problems.

What SQL Is and Why It Matters

SQL stands for Structured Query Language. It is a special language used to work with relational databases, which store data in tables. A table has rows and columns. Each row is one record, and each column is one field or attribute. For example, a school database might have a table called Students with columns such as StudentID, Name, YearGroup, and TutorGroup.

SQL is important because it lets users ask precise questions of large amounts of data. Imagine a school office needs a list of all students in Year $12$ who take Computer Science. Instead of searching manually, the database can return the answer with a query. This saves time, reduces mistakes, and makes data handling much more reliable 📊.

In IB Computer Science, SQL fits into the broader study of how data is stored, organised, accessed, and used. It also links with database design, data integrity, normalization, and security. When you understand SQL, you are better prepared to reason about how software systems manage information.

A key idea is that SQL is declarative. This means you describe what result you want, not exactly how the database should get it. For example, instead of writing a step-by-step search process, you write a query such as SELECT Name FROM Students WHERE YearGroup = 12; and the database engine decides the most efficient way to run it.

Core SQL Terminology

To use SQL well, you need to know a few essential terms:

• Database: A structured collection of related data.
• Table: A set of data arranged in rows and columns.
• Row: One complete record in a table.
• Column: One type of data in a table.
• Primary key: A field that uniquely identifies each row.
• Foreign key: A field that links one table to another.
• Query: A request for data from the database.
• Result set: The output returned by a query.

Suppose a Students table uses StudentID as the primary key. No two students should have the same StudentID. This helps the database keep records unique and avoids confusion. If another table called Enrollments stores StudentID as a foreign key, then each enrollment record can be matched to the correct student.

This linking between tables is one of the biggest strengths of relational databases. It avoids unnecessary duplication and helps keep data consistent. For example, if a student changes tutor group, you update it in one place rather than in many copied records.

Writing Basic Queries

The most common SQL command is SELECT. It is used to retrieve data from one or more tables. A simple query looks like this:

$$

SELECT Name, YearGroup FROM Students;

$$

This means: return the Name and YearGroup columns for every student in the table.

If you want all columns, you can use the wildcard *:

$$

SELECT * FROM Students;

$$

However, using * is not always the best choice because it may return more data than you need. In real systems, it is better to select only the columns required. This makes queries clearer and can improve performance.

To filter results, use WHERE:

$$

SELECT Name FROM Students WHERE YearGroup = 12;

$$

This returns only students in Year $12$. The WHERE clause is very powerful because it lets you narrow down results using conditions. You can compare values with operators such as $=$, $<$, $>$, $\leq$, and $\geq$.

For example:

$$

SELECT Name FROM Students WHERE Grade $\geq 7$;

$$

This query returns students whose grade is $7$ or higher. That kind of condition is useful in academic systems, sales systems, and booking systems.

Sorting, Searching, and Combining Conditions

Often, you want results in a useful order. SQL uses ORDER BY to sort data:

$$

SELECT Name, Grade FROM Students ORDER BY Grade DESC;

$$

DESC means descending order, from largest to smallest. If you want ascending order, you can use ASC, or simply leave it out because ascending is often the default.

When filtering, you may need more than one condition. SQL uses AND, OR, and NOT for this:

$$

SELECT Name FROM Students WHERE YearGroup = 12 AND Grade $\geq 7$;

$$

This returns students who are in Year $12$ and have a grade of at least $7$. Both conditions must be true.

You can also use OR:

$$

SELECT Name FROM Students WHERE TutorGroup = 'A' OR TutorGroup = 'B';

$$

This returns students in tutor group A or B. Quotation marks are used for text values in SQL.

A common real-world example is an online shop. A query might find all products that are in stock and cheaper than a certain amount:

$$

SELECT ProductName FROM Products WHERE InStock = TRUE AND Price < 20.00 ORDER BY Price ASC;

$$

This helps customers find affordable items quickly 🛒.

Working with Multiple Tables

Real databases often split information across multiple tables. This is where joins become important. A join combines rows from two or more tables using a related field.

For example, a school might have:

• Students(StudentID, Name)
• Enrollments(StudentID, Subject)

To find which subjects each student takes, the database can join the tables on StudentID:

$$

SELECT Students.Name, Enrollments.Subject

FROM Students

JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

$$

This query matches each student with their enrollment records. Joins are essential because many real systems store data in normalized tables to reduce repetition. Normalization is the process of organizing data to reduce duplication and improve consistency.

If you think about a music streaming app, one table might store songs and another might store playlists. A join can link them so the system knows which songs are in which playlist.

There are different types of joins, but the most common in introductory SQL is the inner join, which returns only matching rows from both tables. If there is no matching key, the row is not included.

Aggregates, Grouping, and Analysis

SQL can do more than just list records. It can also summarize data using aggregate functions. Common aggregate functions include:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

For example, to count how many students are in the table:

$$

SELECT COUNT(*) FROM Students;

$$

To find the average grade:

$$

SELECT AVG(Grade) FROM Students;

$$

If you want to analyze data by category, use GROUP BY.

$$

SELECT YearGroup, COUNT(*)

FROM Students

GROUP BY YearGroup;

$$

This gives the number of students in each year group. A school administrator might use this information to plan classes, staffing, or rooms.

Sometimes you want to filter grouped results. SQL uses HAVING for this, not WHERE. WHERE filters rows before grouping, while HAVING filters groups after grouping. For example:

$$

SELECT YearGroup, COUNT(*)

FROM Students

GROUP BY YearGroup

$HAVING COUNT(*) \geq 30;$

$$

This returns only year groups with $30$ or more students.

SQL in the IB Computer Science HL Context

In IB Computer Science HL, SQL and querying are not just about memorizing syntax. You must also reason about how the database behaves, what the query returns, and why the query is written that way. This means reading queries carefully and predicting results from sample data.

A typical exam-style skill is tracing a query. Suppose a table contains the following data:

• Alice, Year $12$, Grade $8$
• Ben, Year $12$, Grade $6$
• Chloe, Year $11$, Grade $9$

The query:

$$

SELECT Name FROM Students WHERE YearGroup = 12 AND Grade $\geq 7$;

$$

returns only Alice, because she is in Year $12$ and has a grade of at least $7$. Ben does not meet the grade condition, and Chloe does not meet the year condition.

Another important HL skill is evaluating the quality of queries. A good query is:

• correct
• efficient
• easy to understand
• able to support accurate decision-making

For example, a query used in a hospital database must return the right patient data and avoid mixing records. That is why unique keys, joins, and clear conditions are so important.

Conclusion

SQL and querying are key tools for working with relational databases. They let users ask questions, retrieve data, sort results, combine tables, and analyze information. In IB Computer Science HL, this topic connects to database design, data integrity, and practical problem-solving. Understanding SQL helps you explain how systems store and use data in the real world, from schools to shops to apps. Keep practicing with sample tables and queries, students, because strong database skills come from careful reading, accurate logic, and lots of examples ✅.

Study Notes

• SQL stands for Structured Query Language.
• SQL is used to interact with relational databases made of tables, rows, and columns.
• A primary key uniquely identifies each row in a table.
• A foreign key links one table to another.
• SELECT retrieves data from a table.
• WHERE filters rows using conditions.
• ORDER BY sorts query results.
• AND, OR, and NOT combine or change conditions.
• Joins combine related data from multiple tables.
• Aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().
• GROUP BY organizes data into groups for analysis.
• HAVING filters grouped results.
• SQL is declarative: you describe the result you want.
• SQL connects to IB Computer Science HL through database design, query tracing, and data reasoning.