Structured Query Language (SQL)

Welcome to the World of SQL!

In this chapter, we are going to learn Structured Query Language, better known as SQL (often pronounced like the word "sequel").

Imagine a massive library with millions of books. If you wanted to find every book written by a specific author in 1994, it would take you weeks to look through the shelves manually. SQL is like a super-powered librarian. You give it a specific instruction, and it finds, adds, or changes the information in a relational database in a split second. Whether you're using Instagram, checking your bank balance, or playing an online game, SQL is likely working behind the scenes to manage that data.

Don't worry if it seems like a lot of code at first. SQL is designed to be very similar to English. Once you learn the basic "sentences," you'll be able to talk to any database with ease!

1. Defining the Database: Creating Tables

Before we can store data, we need a place to put it. In a relational database, data is stored in tables. Using SQL to set up these tables is known as Data Definition.

The CREATE TABLE Statement

To make a new table, we use the CREATE TABLE command. You have to tell the database the name of the table and what kind of data each column (attribute) will hold.

Example: Creating a table for Students
CREATE TABLE Students (
  StudentID INTEGER PRIMARY KEY,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  DateOfBirth DATE,
  ClassID INTEGER FOREIGN KEY REFERENCES Classes(ClassID)
);

Wait, what are those extra words?

• INTEGER: This tells the database the column will store whole numbers.
• VARCHAR(20): This stands for "Variable Character." It’s used for text (like names). The number in brackets is the maximum length.
• DATE: Used for calendar dates.
• PRIMARY KEY: This is the unique ID for every row. No two students can have the same StudentID. It’s like your fingerprint!
• FOREIGN KEY: This is a link to a Primary Key in another table. It’s how we create "relationships" between tables.

Quick Review: To build a table, you need a name, column names, and data types for those columns.

Key Takeaway: CREATE TABLE defines the structure of your data before you ever start typing in actual names or details.

2. Retrieving Data: The "Big Three"

The most common thing you will do with SQL is ask the database questions. This is called "querying." Almost every query uses the "Big Three" commands: SELECT, FROM, and WHERE.

The Mnemonic: S.F.W. (Stay For Water)

SELECT — Which columns do you want to see?
FROM — Which table are they in?
WHERE — What are the specific rules for the data you want?

Example: Finding the names of all students with the last name 'Smith'
SELECT FirstName, LastName
FROM Students
WHERE LastName = 'Smith';

Using the Wildcard (*)

If you want to see every column in a table without typing them all out, use the asterisk * symbol. Think of it as a "Select All" button.

SELECT * FROM Students;

Sorting Your Results

You can use ORDER BY to sort your list alphabetically or numerically. Add ASC for ascending (A-Z) or DESC for descending (Z-A).
Example: SELECT * FROM Students ORDER BY LastName ASC;

Joining Tables Together

In the AQA syllabus, you need to know how to get data from multiple tables. Since tables are linked by keys, we can "join" them in the WHERE clause.

Analogy: If you have a list of 'CustomerIDs' and a separate list of 'Orders,' you join them together using the ID they both share to see which person bought which item.

Example: Seeing Student names AND their Class names
SELECT Students.FirstName, Classes.ClassName
FROM Students, Classes
WHERE Students.ClassID = Classes.ClassID;

Did you know? SQL was originally called SEQUEL (Structured English Query Language), but the name was changed because of a trademark dispute. Most people still pronounce it "sequel" though!

Key Takeaway: Use SELECT to choose columns, FROM to choose tables, and WHERE to filter the results and join tables together.

3. Managing Data: Insert, Update, and Delete

Once your table is built, you need to be able to add new information, fix mistakes, or remove data that is no longer needed.

INSERT: Adding New Rows

When a new student joins the school, we add them using INSERT INTO.

INSERT INTO Students (StudentID, FirstName, LastName)
VALUES (105, 'Amina', 'Hussain');

UPDATE: Changing Existing Data

If Amina changes her last name, we don't delete her; we just UPDATE her record. Be careful! Always use a WHERE clause with UPDATE, or you might accidentally change every single person in the database to have the same name!

UPDATE Students
SET LastName = 'Khan'
WHERE StudentID = 105;

DELETE: Removing Data

If a student leaves the school, we use DELETE. Like with UPDATE, always use WHERE so you don't delete your entire database by accident!

DELETE FROM Students
WHERE StudentID = 105;

Common Mistake Alert! Students often forget to put single quotes around text values (like 'Smith' or 'Khan'). Numbers do not need quotes, but text and dates always do!

Key Takeaway: INSERT adds new records, UPDATE modifies existing ones, and DELETE removes them. All three rely on WHERE to make sure you're targeting the right data.

4. Quick Summary Table for Revision

Command — What it does
• CREATE TABLE — Sets up a new table with columns and data types.
• SELECT — Picks which columns you want to view.
• FROM — Specifies which table(s) the data is in.
• WHERE — Filters data and joins multiple tables together.
• ORDER BY — Sorts the results (ASC or DESC).
• INSERT INTO — Adds a brand new row of data.
• UPDATE — Changes data that is already in the table.
• DELETE — Permanently removes a row from the table.

Final Encouragement

SQL is one of the most practical skills you can learn in Computer Science. It follows very logical rules. If your code isn't working, check your commas, your quotes, and make sure your table names are spelled correctly. You've got this!