OCR J277 Databases

SQL Lesson 1: The Golden Trio

Retrieving specific data using standard SQL syntax.

Lesson Progress

0/5
01 Review of Previous Learning

DO NOW: Spot the Python Errors

Open the blank Google Doc I have shared with you. Review the screenshot of the Python code below. There are 4 errors hidden inside (2 Easy, 2 Hard).

Task: Fix the code and paste your corrected, working version into your Google Doc.

1original_price = 50
2discount_percent = 20
3final_price = original_price - discount_percent
4if final_price = 30:
5print("Great deal!")
6else
7 print("Still too expensive.")
Reveal Answers & Feedback
Difficulty Error Type Location Description
Easy Indentation Line 5 Block Error: The print statement must be indented to sit inside the if block.
Easy Syntax Line 6 Missing Colon: The else statement is missing the : at the end.
Hard Concept / Logic Line 4 Assignment vs Comparison: A single = assigns a value. To check equality, you must use ==.
Hard Concept / Maths Line 3 Flat Rate vs Percentage: The code just subtracts 20. It needs to calculate the percentage: original_price - (original_price * (discount_percent / 100)).

The Corrected Code:

original_price = 50
discount_percent = 20
final_price = original_price - (original_price * (discount_percent / 100))
if final_price == 30:
    print("Great deal!")
else:
    print("Still too expensive.")
02 Introducing the New Objective & KWs

Context & Keywords

Learning Objective

To write standard SQL statements to retrieve specific data from a database table.

Tier 2/3 Vocabulary:

Database Click to enlarge

A digital filing system that stores lots of information so it's easy to search and manage.

See visual
A digital filing cabinet
Click to close
Table Click to enlarge

A grid of rows and columns that stores data about one specific topic (like a register).

See visual
ID
Name
Age
1
John
15
2
Sue
16
Click to close
Record Click to enlarge

One complete horizontal row. It holds all the information about a single person or item.

See visual
ID
Name
Age
1
John
15
2
Sue
16
Click to close
Field Click to enlarge

A single vertical column or category of data (e.g., a column just for 'Surnames').

See visual
ID
Name
Age
1
John
15
2
Sue
16
Click to close
Query / SQL Click to enlarge

A Query is a question we ask the database to find data. We write these using Structured Query Language.

See visual

SELECT Name

FROM tblStudents

Click to close
Syntax Click to enlarge

The strict spelling and grammar rules required so the computer understands your code.

See visual
Age == 16
Age = 16
Click to close
03 Chunk 1: SELECT & FROM

Building the Trio: SELECT and FROM

To pull specific data from a database, we use SQL. Look at the tblStudents table below as our example data.

StudentID FirstName Surname Age Subject
1 John Smith 15 Computing
2 Sue Jones 16 History

SELECT chooses the columns. FROM chooses the table. The asterisk * is our wildcard for "all fields".

SELECT FirstName, Subject
FROM tblStudents

Check for Understanding

I want to write a query that pulls ONLY the Surnames from this table. Write the two lines of SQL required to do this.
(Answer: SELECT Surname / FROM tblStudents)

Interactive Practice: Chunk 1

  • Open your overall master worksheet (Google Doc/Word) and the chunk1.html file in your browser.
  • Crucial Step: Enter your First and Last Name in the top Registration Zone and click "Lock Name & Start".
  • Work to complete the 15 data retrieval questions.
  • Once you score 15/15, the bottom Screenshot Zone will turn green. Take a screenshot of this zone and paste it into Section 1 of your master worksheet.
04 Chunk 2: The WHERE Clause

The WHERE Clause & Syntax Traps

The WHERE clause adds a filter/condition to our query to pull specific records rather than all of them.

CRITICAL EXAM TRAPS:

  • Think back to our DO NOW! In Python, we used == to check if something matched. In SQL, we only use a single = sign.
  • Text strings MUST have single quotation marks around them (e.g., 'Sci-Fi'). Numbers do not need quotes.

Check for Understanding: Spot the Error

I have run this query, but it caused a syntax error. Write the correct, fixed query on your Mini Whiteboards and hold them up.

SELECT * FROM tblStudents WHERE Subject = Computing
Reveal Fix
SELECT * FROM tblStudents WHERE Subject = 'Computing'

Interactive Practice: Chunk 2

  • Open the chunk2.html file in your browser.
  • Register your name again to unlock the questions.
  • Work through the 16 questions. Pay close attention to the strict spelling, capitalisation of table names, and the placement of single quotes!
  • Upon reaching a perfect score (16/16), screenshot the green zone at the bottom and paste it into Section 2 of your overall master worksheet.
05 Consolidation

Plenary: End of Lesson Quiz

To finish the lesson, you will complete a short multiple-choice and short-answer quiz to check your understanding of today's key concepts: Databases, Tables, Records, Fields, and the SELECT, FROM, and WHERE clauses.

Lesson Assessment

Task: Complete the final Google Form quiz to evaluate your learning.

Open in Google Forms