Databases & SQL
Information often needs to be organized, searchable, and structured. To do this, we use Databases and search them using Structured Query Language (SQL).
Learn SQL for OCR J277
Watch this step-by-step guide to mastering the three essential SQL commands and spotting "SQL Injection" vulnerabilities.
- Database Anatomy (Records & Fields)
- SELECT / FROM Wildcards
- WHERE Clause & Text Filtering
- SQL Injection (Paper 1 Security)
Core Terminology
Before writing queries, you must understand the anatomical structure of a database. Click the cards below to reveal their definitions and visual diagrams.
A digital filing system that stores lots of information so it's easy to search and manage.
A grid of rows and columns that stores data about one specific topic (like a register).
One complete horizontal ROW. It holds all the information about a single person or item.
A single vertical COLUMN or category of data (e.g., a column just for 'Surnames').
The strict spelling and grammar rules required so the computer understands your query.
A question we ask the database to find data. We write these queries using SQL.
SELECT Name
FROM tblStudents
Building the Query: SELECT & FROM
To pull specific data from a database, we use SQL string sentences. We will look at a table called tblStudents as our example.
| StudentID | FirstName | Surname | Age | Subject |
|---|---|---|---|---|
| 1 | John | Smith | 15 | Computing |
| 2 | Sue | Jones | 16 | History |
SELECT chooses the specific Columns/Fields. FROM chooses the Table. The asterisk * is our wildcard for "all fields".
FROM tblStudents
Filters & Syntax Traps
The WHERE clause adds a filter/condition to our query to pull specific records rather than all of them. But there are major EXAM TRAPS you must avoid!
The Equals Trap
In Python, we used == to check if something matched. In SQL syntax, we only ever use a single = sign.
The Quotes Trap
Text strings MUST have quotes around them (e.g., 'History' or "History"). Numbers DO NOT need quotes.