Structured query language (SQL)

Welcome to the World of SQL!

Ever wondered how Spotify remembers your favorite songs, or how a game like Fortnite keeps track of your skins and V-Bucks? It all happens in a database. To talk to these databases, we use a special language called SQL (pronounced 'sequel' or 'S-Q-L'), which stands for Structured Query Language.

In this chapter, we are going to learn how to ask a database questions, add new information, and even change or delete data. Don't worry if this seems tricky at first—SQL is actually very similar to English!

1. Understanding Relational Databases

Before we write code, we need to know what we are working with. A relational database is a way of storing data in tables that are linked together.

Key Database Terms:
• Table: A collection of data organized into rows and columns (like a spreadsheet).
• Record: A single row in a table. It represents one "thing" (e.g., one student or one product).
• Field: A single column in a table. It represents one "attribute" (e.g., a Surname or a Price).
• Data Type: Each field has a type, like Integer (whole numbers) or String (text).
• Primary Key: A unique field that identifies every record (like your Student ID number). No two records can have the same Primary Key!
• Foreign Key: A Primary Key from one table that appears in another table to link them together.

Real-World Analogy: Imagine your school's database. One table holds Students (Name, DOB, StudentID). Another table holds Classes (ClassID, Subject, Teacher). By putting the StudentID into the Classes table, we "link" the student to their lessons. This link is the Foreign Key.

Did you know? Using relational databases helps eliminate data redundancy (storing the same thing twice) and data inconsistency (having two different addresses for the same person). It keeps everything tidy!

Quick Review:
- Tables = The whole collection.
- Records = The horizontal rows.
- Fields = The vertical columns.
- Primary Key = The unique ID.

2. Retrieving Data: The SELECT Statement

To get data out of a database, we use the SELECT command. This is called a "query."

The Basic Structure:
SELECT (Which fields do you want?)
FROM (Which table is it in?)
WHERE (What are the search criteria?)
ORDER BY (How should we sort the results?)

Memory Aid: Silly Frogs Wear Orange
(SELECT, FROM, WHERE, ORDER BY)

Step-by-Step Example:
Imagine a table called Games with fields: Title, Genre, Price, Rating.
If we want to see the titles of all games that cost less than £20, we would write:
SELECT Title
FROM Games
WHERE Price < 20;

Sorting your results:
Use ORDER BY at the end. You can use ASC for ascending (A-Z, 1-10) or DESC for descending (Z-A, 10-1).
SELECT Title, Price
FROM Games
ORDER BY Price DESC;

Working with Two Tables:
In your exam, you might need to get data from two tables. We do this by matching the Primary Key of one to the Foreign Key of the other in the WHERE clause.
Example: SELECT Students.Name, Grades.Score
FROM Students, Grades
WHERE Students.StudentID = Grades.StudentID;

Key Takeaway: SELECT picks the columns, FROM picks the table, and WHERE filters the rows!

3. Adding New Data: INSERT INTO

When you sign up for a new social media account, the app uses an INSERT command to add you to their database.

How to write it:
INSERT INTO table_name (column1, column2...)
VALUES (value1, value2...);

Example: Adding a new game to our list.
INSERT INTO Games (Title, Genre, Price)
VALUES ('Super Space Explorer', 'Sci-Fi', 15.99);

Common Mistake to Avoid: Make sure the values are in the same order as the columns you listed! If Title is first, the game's name must be first in the values list. Also, remember that Strings (text) need 'single quotes' around them, but Integers and Reals do not.

4. Changing and Deleting Data

Sometimes data changes (like a game going on sale) or needs to be removed (like a user deleting their account).

Updating Data

We use UPDATE to change existing records.

Structure:
UPDATE table_name
SET column = new_value
WHERE condition;

Example: Changing the price of 'Battle Arena' to £10.
UPDATE Games
SET Price = 10
WHERE Title = 'Battle Arena';

Deleting Data

We use DELETE to remove a record entirely.

Structure:
DELETE FROM table_name
WHERE condition;

Example: Removing a game that is no longer for sale.
DELETE FROM Games
WHERE Title = 'Old Game 2010';

DANGER ZONE! Always remember the WHERE clause when using UPDATE or DELETE. If you forget it, you might accidentally update every row in the table or delete everything! Imagine setting the price of every game in the store to £0 by mistake!

Quick Review:
- INSERT = Create new data.
- UPDATE = Change existing data.
- DELETE = Remove data.

Summary: Your SQL Toolkit

You now have the tools to manage a database! Remember these four commands and you'll be an SQL pro:
1. SELECT: To find and display data.
2. INSERT: To add new records.
3. UPDATE: To edit records.
4. DELETE: To remove records.

Keep practicing by writing out your own queries for a "Student" or "Music" table. The more you write them, the more natural they will feel. You've got this!