Introduction to Querying Data
In the previous chapters of A.3 Databases, we looked at how to design and organize a database. But once that data is stored, it isn't very useful unless we can get it back out! Querying is the process of asking the database a specific question to retrieve exactly the information you need.
Whether you are searching for a specific song on a streaming app or a teacher is looking up your grades, a database query is happening behind the scenes. In this chapter, we will learn how these queries are structured and how to use them effectively.
What is a Query?
A query is a formal request for data from a database. Instead of looking through thousands of rows of data manually, we use a specialized language to tell the database exactly what to show us. The most common language used for this is SQL (Structured Query Language).
Analogy: Imagine a giant library. A database is the library itself. A query is like asking the librarian: "Can you show me the titles of all science fiction books written after 2010, sorted by the author's name?"
The Core Parts of a Query
To write a successful query, you usually need three main components. Don't worry if the syntax looks strange at first; it follows a very logical pattern.
1. The SELECT Clause
This tells the database which columns (fields) you want to see. If you want everything, you can use the asterisk symbol \( * \), which acts as a wildcard meaning "all columns."
2. The FROM Clause
This tells the database which table the data is stored in. Remember, databases often have many tables (like 'Students', 'Classes', and 'Grades').
3. The WHERE Clause
This is where you set your criteria. It filters the data so you only see the rows (records) that meet your specific conditions.
Quick Review:
SELECT = Fields (Columns)
FROM = Table
WHERE = Criteria (Filters)
Filtering with Logic
To make the WHERE clause powerful, we use logical operators. These should look familiar if you have studied A.1 Computer Fundamentals or basic programming!
AND: Both conditions must be true. (e.g., Price < \( 10 \) AND Category = 'Books')
OR: At least one condition must be true. (e.g., Color = 'Red' OR Color = 'Blue')
NOT: Excludes certain data. (e.g., NOT Status = 'Shipped')
Comparison Operators: We use symbols like \( = \) (equal to), \( < \) (less than), \( > \) (greater than), and \( <> \) or \( != \) (not equal to).
Common Mistake: Forgetting to put text values in single quotes. For example, WHERE Name = Smith will cause an error. It should be WHERE Name = 'Smith'.
Sorting Your Results
Once the database finds the data, you often want it in a specific order. We use the ORDER BY clause for this.
By default, most databases sort in ascending order (A to Z, or smallest to largest). You can specify the direction using these keywords:
- ASC: Ascending order (default).
- DESC: Descending order (largest to smallest).
Example: SELECT Name FROM Students ORDER BY Grade DESC; (This shows names with the highest grades first).
Answering Bigger Questions: Aggregate Functions
Sometimes you don't want to see a list of individual records. Instead, you might want a summary of the data. For this, we use Aggregate Functions.
COUNT(): Counts how many rows match your criteria. (e.g., How many students are in Grade 12?)
SUM(): Adds up the values in a numeric column. (e.g., What is the total value of all orders?)
AVG(): Calculates the average of a numeric column. (e.g., What is the average score on the exam?)
MIN() / MAX(): Finds the smallest or largest value in a column.
Did you know? You can give these calculated results a "nickname" using the AS keyword. This is called aliasing. For example: SELECT AVG(Price) AS AveragePrice FROM Products;
Connecting Tables (Joining)
Because relational databases spread data across multiple tables to avoid redundancy (as discussed in Relational Database Design), we often need to "join" them back together in a query.
We do this by matching the Primary Key of one table with the Foreign Key of another. This is usually done with an INNER JOIN.
Step-by-Step Example:
1. We have a Students table and a Clubs table.
2. They are linked by a field called StudentID.
3. Our query would look like: SELECT Students.Name, Clubs.ClubName FROM Students INNER JOIN Clubs ON Students.StudentID = Clubs.StudentID;
Key Takeaway: Joining is the "glue" that lets us retrieve related information from different parts of the database system.
Summary Checklist for Success
When you are writing or analyzing a query for your exam, ask yourself these questions:
- What columns do I need? (This goes in SELECT).
- What table(s) am I using? (This goes in FROM).
- Are there specific conditions? (This goes in WHERE).
- Do I need to sort the result? (This goes in ORDER BY).
- Am I looking for a total or average? (Use Aggregate Functions).
Don't worry if this seems tricky at first! The more you practice writing these "questions" to the database, the more natural the logic becomes.