Introduction to SQL Queries

Welcome to the world of SQL! If you have already learned about how databases are structured with fields and records, you are ready for the next step: querying. A "query" is simply a way of asking the database a question. Imagine you have a massive spreadsheet with thousands of rows; SQL (Structured Query Language) is the tool you use to instantly find exactly what you are looking for.

In this chapter, we will focus on how to write and understand SQL scripts for single-table databases. Don't worry if it looks like "code" at first—SQL is designed to read very much like a normal English sentence!


1. The Core "Big Three" Commands

Almost every SQL query you write for your exam will start with these three keywords in this specific order: SELECT, FROM, and WHERE.

SELECT: "Which columns do I want?"

The SELECT command tells the database which fields (columns) you want to see in your results. For example, if you only want to see the names of students, you would use SELECT StudentName.

FROM: "Which table am I looking in?"

The FROM command identifies the table where the data is stored. For this syllabus, we only look at one table at a time. For example, FROM StudentsTable.

WHERE: "What are my search criteria?"

The WHERE command acts like a filter. It tells the database to only show records (rows) that meet a certain condition. For example, WHERE Grade = "A".

Quick Example:
SELECT Name, Age
FROM Members
WHERE Age > \(15\)

This would show only the Name and Age of every person in the "Members" table who is older than \(15\).


2. Adding Logic: AND & OR

Sometimes, one filter isn't enough. We use AND and OR to combine conditions in our WHERE clause.

  • AND: Both conditions must be true. (e.g., WHERE Color = "Red" AND Size = "Large")
  • OR: At least one of the conditions must be true. (e.g., WHERE Grade = "A" OR Grade = "B")

Memory Tip: Think of AND as a "strict" boss who wants everything to match, and OR as a "relaxed" boss who is happy if just one thing matches!


3. Sorting Your Data: ORDER BY

By default, database results can look a bit messy. We use ORDER BY to sort our results into a specific sequence. In your exam, you must use the specific keywords ASCENDING or DESCENDING.

  • ORDER BY ... ASCENDING: Sorts from smallest to largest (\(1\) to \(10\)) or alphabetical order (\(A\) to \(Z\)).
  • ORDER BY ... DESCENDING: Sorts from largest to smallest (\(10\) to \(1\)) or reverse alphabetical order (\(Z\) to \(A\)).

Example:
SELECT Title, Price
FROM Books
ORDER BY Price DESCENDING

This shows all books, with the most expensive ones at the top of the list.


4. Performing Calculations: SUM & COUNT

Sometimes we don't want to see the raw data; we want a total. SQL provides built-in functions for this:

SUM

The SUM function adds up all the values in a numeric field.
Example: SELECT SUM(Price) FROM Orders (This gives you the total cost of all orders added together).

COUNT

The COUNT function tells you how many records match your search. It doesn't add the values; it just counts the rows.
Example: SELECT COUNT(StudentID) FROM Students WHERE Grade = "A" (This tells you exactly how many students got an A).

Common Mistake: Don't confuse these! Use SUM for totals (like money) and COUNT for "how many" (like number of people).


5. Step-by-Step: How to Read an SQL Script

If you see an SQL script in an exam and need to figure out what the output will be, follow these steps:

  1. Look at FROM: Which table am I looking at?
  2. Look at WHERE: Cross out any rows in the table that don't meet these rules.
  3. Look at SELECT: Which columns am I actually allowed to show in my final answer?
  4. Look at ORDER BY: Do I need to rearrange my final list?

Key Takeaway: The SELECT line determines what your final table headings will be. If a field isn't in the SELECT list, don't include it in your final output table!


6. Summary Table of SQL Keywords

Use this table as a quick reference for your revision:

Keyword Purpose
SELECT Chooses the fields (columns) to display.
FROM Specifies the table name.
WHERE Filters records based on a condition.
AND / OR Combines multiple conditions in a WHERE clause.
ORDER BY Sorts the results.
ASCENDING Sorts Low to High (\(A \to Z\)).
DESCENDING Sorts High to Low (\(Z \to A\)).
SUM Adds up the values in a column.
COUNT Counts the number of records (rows).

Quick Review Tip

In the exam, you might be asked to complete a script by filling in the blanks. Always check the table definition provided in the question to make sure you spell the field names exactly as they are written (e.g., if the field is called Stud_ID, do not write StudentID!).

Note: For more information on how databases are set up before they are queried, see the "Database structure and data types" chapter.