Structured query language (SQL)

Welcome to the World of SQL!

Ever wondered how Spotify remembers your playlists, or how your school keeps track of thousands of students? They use databases! In this chapter, we are going to learn SQL (Structured Query Language), which is the special language used to "talk" to these databases. Don't worry if it looks like code at first—it’s actually very similar to English!

3.7.1 Relational Databases: The Big Picture

A database is simply an organized collection of data. A relational database is a type of database that stores data in multiple tables which are linked together.

How Data is Organized

Think of a database table like a spreadsheet for a sports club:

• Table: The entire collection of data about one subject (e.g., a "Students" table).
• Record: A single row in the table. It contains all the information about one specific item (e.g., everything about one student).
• Field: A single column in the table. It stores one specific piece of information (e.g., "Date of Birth" or "Surname").
• Data Type: Each field has a type, such as Integer (whole numbers), Real (decimals), or String (text). This tells the computer what kind of data to expect.

The "Keys" to Success

To keep everything organized, we use special fields called "Keys":

• Primary Key: A unique field that identifies every record. Just like your fingerprint or your School ID number, no two records can have the same Primary Key.
• Foreign Key: This is a field in one table that links to the Primary Key of another table. This is how we "connect" the tables!

Why bother with Relational Databases?

If we put everything in one giant table, we would end up typing the same thing over and over again. This causes two big problems that relational databases solve:
1. Data Redundancy: This is when the same data is stored more than once. Relational databases stop this by splitting data into separate tables.
2. Data Inconsistency: If you have a person's address written in five different places and they move house, you might forget to update one. By using linked tables, you only change it once, keeping the data "consistent."

Quick Review:

• Tables have Records (rows) and Fields (columns).
• Primary Keys are unique IDs.
• Foreign Keys link tables together.

Key Takeaway: Relational databases use linked tables to save space (reduce redundancy) and prevent mistakes (data inconsistency).

3.7.2 Structured Query Language (SQL)

Now that we know how data is stored, how do we get it out? We use SQL.

Retrieving Data (The SELECT Statement)

When you want to find information, you "query" the database. Use this handy mnemonic to remember the order: Silly Frogs Watch Oranges!

• SELECT: Which fields (columns) do you want to see?
• FROM: Which table are you looking at?
• WHERE: What is the search criteria? (Like a filter).
• ORDER BY: Do you want it sorted? Use ASC for A-Z or DESC for Z-A.

Example Query:
Imagine a table called Students. We want to find the names of students in Year 11, sorted by their name.
SELECT FirstName, Surname
FROM Students
WHERE YearGroup = 11
ORDER BY Surname ASC

Did you know? In your exam, you might see a star symbol (*). In SQL, SELECT * means "Select EVERYTHING." It’s a great shortcut!

Modifying Data

Sometimes we need to add, change, or delete data. Here is how we do it:

1. Adding New Data (INSERT)

When a new student joins the school, we use INSERT INTO.
INSERT INTO Students (StudentID, FirstName, Surname)
VALUES (105, 'Alex', 'Smith')

2. Changing Existing Data (UPDATE)

If Alex Smith changes their name, we use UPDATE.
UPDATE Students
SET Surname = 'Jones'
WHERE StudentID = 105

3. Removing Data (DELETE)

If a student leaves, we use DELETE.
DELETE FROM Students
WHERE StudentID = 105

Warning! Always be careful with the WHERE clause in UPDATE and DELETE. If you forget it, you might accidentally change or delete every single person in your database! Yikes!

Working with Two Tables

In the AQA exam, you might need to pull data from two tables at once. You do this by making sure the WHERE clause links the Primary Key of one table to the Foreign Key of the other.

SELECT Students.FirstName, Classes.ClassName
FROM Students, Classes
WHERE Students.ClassID = Classes.ClassID

Quick Review:

• SELECT finds data.
• INSERT adds data.
• UPDATE changes data.
• DELETE removes data.
• WHERE is the filter that makes your query specific.

Key Takeaway: SQL is a logical language. Just remember SELECT, FROM, WHERE and you are 90% of the way there! Practice writing them out by hand, as that is how you'll do it in the exam.