Preview

The whole resource, exactly as a class sees it. Answers and marking are held back.

SQL

SQL stands for Structured Query Language. An SQL query retrieves selected data from a database table. In a table, each column is a field and each row is a record. Field and table names must match those shown in the question.

OCR J277 requires SELECT, FROM and WHERE. These tasks use written SQL and markdown tables so the focus remains on the query itself rather than a Python database library.

SELECT names the field or fields to return.
FROM names the table containing the data.
WHERE filters records using a condition. It is only needed when the query must filter the records.

Consider this table named Books:

BookID	Title	Genre	Copies
101	Orbit	Science fiction	4
102	Wild Paths	Nature	2
103	Deep Sea	Nature	6
104	Code Club	Computing	3

Multiple choice1 mark

Which clause names the table from which data is retrieved?

AWHERE
BSELECT
CORDER
DFROM

Building a query

SELECT Title, Copies
FROM Books
WHERE Genre = 'Nature'

SELECT names the field or fields to return. Separate several fields with commas. FROM names the table. WHERE filters records using a condition. String values such as 'Nature' need quotation marks; numerical values such as 3 do not. SQL uses one equals sign, =, when testing equality.

Fill in the blanks3 marks

Choose from the answer bank to complete the SQL query. It must return the title and number of copies for books in the Nature genre: label 1 Title, Copies label 2 Books label 3 Genre = 'Nature'

WHERE
Books
SELECT
IF
FROM
Title

Multiple choice1 mark

Which query returns the Title of every book with more than 3 copies?

ASELECT Title FROM Books WHERE Copies > 3
BSELECT Books FROM Title IF Copies > '3'
CFROM Books SELECT Title WHERE Copies = 3
DSELECT Title WHERE Books Copies > 3

All fields and more than one condition

An asterisk after SELECT is a wildcard meaning all fields:

SELECT *
FROM Books

This returns every field for every record. It is shorter and clearer than listing every field name when the question asks for all fields.

A WHERE clause can contain more than one condition:

AND requires both conditions to be true.
OR requires at least one condition to be true.

For example, this query returns all fields for Nature books that have at least four copies:

SELECT *
FROM Books
WHERE Genre = 'Nature' AND Copies >= 4

Read the wording carefully. Both normally suggests AND; either normally suggests OR.

Fill in the blanks2 marks

Choose from the answer bank to complete the query. It must return all fields for Nature books with at least four copies: SELECT label 1 FROM Books WHERE Genre = 'Nature' label 2 Copies >= 4

*
Title
AND
OR
WHERE

Multiple choice1 mark

A record should be returned when its Genre is Nature or it has at least four copies. Which operator should join the two conditions?

AAND
BOR
CFROM
DSELECT

Written answer4 marks

Write an SQL query to return Title and Genre from Books for the record whose BookID is 104.

Use SELECT, FROM and WHERE. Separate selected fields with a comma. BookID is numerical.

Students type their answer here.

Written answer4 marks

Write an SQL query to return all fields from Books for Nature books with at least four copies.

Use the wildcard for all fields and join the two conditions with the operator that requires both to be true.

Students type their answer here.

Written answer4 marks

State the titles returned by SELECT Title FROM Books WHERE Genre = 'Nature' OR Copies >= 4, then explain why Code Club is not returned.

Apply each side of the condition to every record. With OR, a record is returned when either condition is true.

Students type their answer here.

Multiple choice1 mark

Which corrections are needed in SELECT Title Copies FROM Books WHERE Genre = Nature?

AReplace FROM with IF
BPut the table name in quotes
CAdd a comma between fields and quotes around 'Nature'
DRemove WHERE

Written answer4 marks

The query should return the Title of each book with more than two copies. Write the corrected query: SELECT Name FROM Books IF Copies > '2'.

Use the requested field, the correct filtering keyword and a numerical value without quotation marks.

Students type their answer here.

Review

Build a query in the order SELECT fields, FROM table, then WHERE condition when filtering is needed. Use * after SELECT when all fields are required. Separate named fields with commas, quote string values and leave numerical values unquoted. Use AND when both conditions must be true and OR when either may be true.

For OCR J277, concentrate on retrieving data with SELECT, FROM and WHERE. Do not add Python or unsupported SQL commands to an examination response.