Year 11 · Computer Science · Spring 1 · Paper 2 practice: algorithms, programming, logic, databases

Logic circuits and SQL

Exam practicePresent ▶

What this lesson covers

10.3 Expressions and circuits

Cambridge IGCSE Computer Science 0478 · Topic 10: Boolean logic · Paper 2

9.3 SQL: SELECT, FROM, WHERE, AND, OR

Cambridge IGCSE Computer Science 0478 · Topic 9: Databases · Paper 2

9.4 SQL: ORDER BY, SUM and COUNT

Cambridge IGCSE Computer Science 0478 · Topic 9: Databases · Paper 2

You will learn to

Draw a logic circuit from an expression, and write an expression from a circuit.
Complete a truth table for a circuit, using working columns.
Write SQL queries with SELECT, FROM, WHERE, AND, OR and ORDER BY.
Use SUM and COUNT in a query, and predict the exact output.

Success criteria

I can turn four circuits into expressions, and four expressions into circuits.
I can complete a 3-input truth table in the standard order.
I can write six SQL queries and check them with the SQL toy.

The syllabus points for these sections

Write a logic expression from a problem statement.
Draw a logic circuit from an expression, without simplifying.
Write a logic expression from a circuit.
Write a logic expression from a truth table using the 1 rows.
Explain what each part of a SELECT query does.
Use comparison operators in a WHERE condition.
Combine conditions with AND and OR.
Predict the exact output of a query.
Sort query output with ORDER BY ASC and DESC.
Total a numeric field with SUM.
Count records with COUNT.
Complete and write full queries for exam scenarios.

Key words

Say each word, then read what it means. Every one is in the glossary too.

Logic gate: An electronic component that takes one or two inputs and gives one output by a fixed rule.
Truth table: A table that lists every possible input combination and the output for each one.
Logic circuit: Two or more logic gates joined together to give one output.
Logic expression: A written description of a circuit using gate names, such as X = A AND NOT B.
NOT: A one-input gate whose output is the opposite of its input; also called an inverter.
AND: A gate whose output is 1 only when both inputs are 1.
OR: A gate whose output is 1 when at least one input is 1.
NAND: NOT AND: the output is 0 only when both inputs are 1.
NOR: NOT OR: the output is 1 only when both inputs are 0.
XOR (EOR): Exclusive OR: the output is 1 when the two inputs are different.
Input: A signal going into a gate or circuit, such as a sensor reading of 1 or 0.
Output: The single result signal that comes out of a gate or circuit.
Sum of products: An expression made by AND-ing the inputs of each row with output 1, then OR-ing those groups together.
Database: An organised, structured store of data that a computer can search and sort.
Table: A grid of data made of rows (records) and columns (fields).
Field: One column of a table; it holds one item of data about every record, such as Minutes.
Record: One row of a table; it holds all the data about one thing, such as one bike hire.
Field name: The heading at the top of a column, such as HireID; it is not a record.
Data type: The kind of data a field can hold, such as integer, real or Boolean.
Text / alphanumeric: A data type for letters, digits and symbols, such as a name or a phone number.
Integer: A data type for whole numbers, such as 55.
Real: A data type for numbers with a decimal part, such as 5.50.
Date/time: A data type for a date, a time, or both, such as 05/03/2026.
SQL: Structured Query Language: the language used to ask a database for data.
Query: A question to the database, written in SQL, that returns matching data.
ORDER BY: The SQL clause that sorts the output, ascending (ASC) or descending (DESC).
SUM: An SQL function that adds up the values in a numeric field.
COUNT: An SQL function that counts how many records meet a condition.

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

10.3 Expressions and circuits

From a statement to an expression

A logic expression describes a circuit in words, like `X = (A AND B) OR NOT C`.

Read the statement and turn each condition into a letter = 1 or letter = 0. "is on" or "is open" means the letter itself. "is off" or "is not" means NOT and the letter.

Join the conditions with the words the statement uses: "and" → AND, "or" → OR. Use brackets to keep each group together.

e.g.Example

Statement: a garden pump P switches on if the soil is dry (D = 1) and it is not raining (R = 0), or if the timer is on (T = 1). soil is dry → D not raining → NOT R timer is on → T Expression: P = (D AND NOT R) OR T

!Watch out

"R = 0" in a statement means you must write NOT R. Writing R on its own is the opposite of what the statement says.

From an expression to a circuit

Draw one gate for every operator in the expression. Start with the brackets, then work outwards to the final gate.

Every NOT is its own gate. NAND, NOR and XOR are drawn as single gates, not built from others.

Draw exactly what is written. Do not simplify, even if you can see a shorter circuit. Label every input and the output.

e.g.Example

X = (A AND B) OR NOT C Operators: AND, NOT, OR → 3 gates 1. A and B go into an AND gate. 2. C goes into a NOT gate. 3. The AND output and the NOT output go into an OR gate. 4. The OR output is X.

?Why?

Cambridge marks one gate at a time, checking each gate has the right inputs. A simplified circuit has different gates, so it loses marks even if it works.

!Watch out

Every gate except NOT needs exactly two inputs. If one input, such as B, feeds two gates, branch its wire and mark the join with a dot.

From a circuit to an expression

Start at the inputs on the left. Write the expression for the first gates' outputs.

Move right one gate at a time. Put each earlier part in brackets and join it with the next gate's name.

Finish at the output on the right.

A	B	C	A NOR B	B AND C	X
0	0	0	1	0	1
0	0	1	1	0	1
0	1	0	0	0	0
0	1	1	0	1	1
1	0	0	0	0	0
1	0	1	0	0	0
1	1	0	0	0	0
1	1	1	0	1	1

e.g.Example

Circuit: A and B go into a NOR gate. B and C go into an AND gate. Both outputs go into an OR gate, output X. NOR gate → (A NOR B) AND gate → (B AND C) OR gate → X = (A NOR B) OR (B AND C)

!Watch out

Brackets matter. Without them, `A NOR B OR B AND C` could be read in several ways. Always put each gate's output in brackets.

From a truth table to an expression

Look only at the rows where the output is 1.

For each of those rows, write the three inputs joined by AND. Put NOT in front of any input that is 0 in that row.

Then join all these groups with OR. This is called sum of products. It is long, but always correct.

Try the toy: type the final expression for X from the example. Its truth table should match the one below.

A	B	C	X
0	0	0	0
0	0	1	0
0	1	0	0
0	1	1	1
1	0	0	0
1	0	1	1
1	1	0	1
1	1	1	0

e.g.Example

Output is 1 in three rows: A=0, B=1, C=1 → (NOT A AND B AND C) A=1, B=0, C=1 → (A AND NOT B AND C) A=1, B=1, C=0 → (A AND B AND NOT C) X = (NOT A AND B AND C) OR (A AND NOT B AND C) OR (A AND B AND NOT C)

?Why?

Each bracket is 1 for exactly one row and 0 for every other row. OR-ing them gives 1 in exactly the rows you need.

There is something to try for this step in the IGCSE library.

9.3 SQL: SELECT, FROM, WHERE, AND, OR

The shape of a query

SQL (Structured Query Language) is the language used to ask a database for data. A question written in SQL is called a query.

`SELECT` lists the fields you want to see. `FROM` names the table. `WHERE` gives the condition a record must meet.

The output is a new, smaller table. It has only the chosen fields, and only the records that meet the condition.

Symbol	Meaning	Example
=	equal to	Station = 'Sol'
<>	not equal to	BikeType <> 'E'
>	greater than	Minutes > 30
<	less than	Cost < 1.00
>=	greater than or equal to	Minutes >= 30
<=	less than or equal to	Cost <= 2.00

SELECT HireID, Station
FROM BikeHire
WHERE Minutes > 30;

?Why?

Text values go inside single quotes, like `'Sol'`, so SQL knows where the text starts and ends. Numbers and TRUE/FALSE have no quotes.

Predicting output: one condition

Use the same three moves every time. First, filter: go down the table and tick each record that meets the WHERE condition.

Second, pick columns: keep only the fields named after SELECT, in the order they are listed.

Third, sort, if there is an ORDER BY. With no ORDER BY, keep the records in table order.

HireID	Minutes
H02	40
H05	55
H08	42

e.g.Example

Filter: Minutes > 30 H01 25 ✗ · H02 40 ✓ · H03 12 ✗ · H04 18 ✗ H05 55 ✓ · H06 30 ✗ · H07 8 ✗ · H08 42 ✓ Pick columns HireID and Minutes. The output is in the table below.

SELECT HireID, Minutes
FROM BikeHire
WHERE Minutes > 30;

!Watch out

H06 has exactly 30 minutes. 30 is not greater than 30, so H06 is not in the output. Read `>` and `>=` very carefully.

AND: both conditions must be true

AND joins two conditions. A record is shown only if both conditions are true.

AND makes the output smaller, because each record has to pass two tests.

HireID	Cost
H01	2.50
H08	4.20

e.g.Example

Records at Sol: H01 (E), H04 (M), H08 (E) H01: Sol ✓ and E ✓ → shown H04: Sol ✓ and M ✗ → not shown H08: Sol ✓ and E ✓ → shown

SELECT HireID, Cost
FROM BikeHire
WHERE Station = 'Sol' AND BikeType = 'E';

OR: at least one condition must be true

OR joins two conditions. A record is shown if either condition is true, or both.

OR makes the output bigger, because a record has two chances to pass.

Write the full condition on both sides of OR. The field name must appear twice.

HireID	Station
H02	Retiro
H03	Atocha
H06	Retiro
H07	Atocha

SELECT HireID, Station
FROM BikeHire
WHERE Station = 'Retiro' OR Station = 'Atocha';

!Watch out

`WHERE Station = 'Retiro' OR 'Atocha'` is wrong and loses the mark. Each side of OR must be a complete condition: `Station = 'Atocha'`.

9.4 SQL: ORDER BY, SUM and COUNT

ORDER BY: sorting the output

ORDER BY sorts the output by one field. It always comes last in the query.

ASC means ascending: smallest to largest, A to Z, oldest date to newest.

DESC means descending: largest to smallest, Z to A, newest date to oldest.

HireID	Minutes
H02	40
H04	18
H07	8

e.g.Example

Filter BikeType = 'M': H02 (40), H04 (18), H07 (8) Sort by Minutes, largest first: 40, 18, 8

SELECT HireID, Minutes
FROM BikeHire
WHERE BikeType = 'M'
ORDER BY Minutes DESC;

?Why?

If you leave out ASC or DESC, the database sorts in ascending order. Writing ASC anyway makes your answer clear to the examiner.

SUM: adding up a field

SUM adds up all the values in a numeric field, for the records that meet the condition.

The output is one single number, not a list of records.

Only use SUM on integer or real fields. You cannot add up text.

e.g.Example

Members (Member = TRUE): H01, H03, H04, H06 Costs: 2.50 + 1.20 + 0.90 + 3.00 2.50 + 1.20 = 3.70 3.70 + 0.90 = 4.60 4.60 + 3.00 = 7.60 Output: 7.60

SELECT SUM(Cost)
FROM BikeHire
WHERE Member = TRUE;

!Watch out

Put the field name inside the brackets: `SUM(Cost)`. Writing `SUM Cost` or `SUM(Member)` will not earn the mark.

COUNT: how many records match

COUNT counts how many records meet the condition. It does not add the values.

The output is one single whole number.

Here we write `COUNT(HireID)`. You may also see `COUNT(*)`, which counts every matching record in the same way.

e.g.Example

Electric bikes: H01, H03, H05, H06, H08 Count them: 1, 2, 3, 4, 5 Output: 5

SELECT COUNT(HireID)
FROM BikeHire
WHERE BikeType = 'E';

?Why?

SUM answers "how much?". COUNT answers "how many?". Read the question for those words to choose the right one.

Putting it all together

A full query can use WHERE with AND, then ORDER BY. Work through it with the three moves: filter, pick columns, sort.

Then test yourself in the toy below. Its BikeHire table has different fields: BikeID, Type, Station, Hours and Cost. Type a query, predict the output on paper first, then press Run query.

Try the example buttons, then change `ASC` to `DESC`, or `AND` to `OR`. Watch how the output changes.

Station	Minutes	Cost
Moncloa	55	5.50
Retiro	40	2.00
Sol	42	4.20

e.g.Example

Filter: non-members (H02, H05, H07, H08) whose cost is 2.00 or more H02 2.00 ✓ · H05 5.50 ✓ · H07 0.40 ✗ · H08 4.20 ✓ Pick columns: Station, Minutes, Cost Sort by Station A to Z: Moncloa, Retiro, Sol

SELECT Station, Minutes, Cost
FROM BikeHire
WHERE Cost >= 2.00 AND Member = FALSE
ORDER BY Station ASC;

There is something to try for this step in the IGCSE library.

Writing a query from a question

Exam questions describe what is needed in words. Turn each part of the sentence into one line of SQL.

"Show the hire ID and station" → SELECT. "from the bike-hire table" → FROM. "of hires under 20 minutes or from Moncloa" → WHERE. "highest hire ID first" → ORDER BY … DESC.

Copy field names and the table name exactly as they appear in the table.

HireID	Station
H07	Atocha
H05	Moncloa
H04	Sol
H03	Atocha

SELECT HireID, Station
FROM BikeHire
WHERE Minutes < 20 OR Station = 'Moncloa'
ORDER BY HireID DESC;

!Watch out

A query that mixes AND and OR can be read two ways. SQL does AND first. If you must mix them, put brackets around the part that should be done first.

Study it

Open the study page and go one step at a time.

Computer Science topic 7

The steps for this lesson

Topic 9, step 2: NOT, AND, OR (Play with logic gates)
Topic 18, step 9: SQL (Ask the database)

Try it

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.

Logic circuits and SQL

What this lesson covers

You will learn to

Success criteria

Key words

The explanation

From a statement to an expression

From an expression to a circuit

From a circuit to an expression

From a truth table to an expression

The shape of a query

Predicting output: one condition

AND: both conditions must be true

OR: at least one condition must be true

ORDER BY: sorting the output

SUM: adding up a field

COUNT: how many records match

Putting it all together

Writing a query from a question

Study it

Algorithm design and problem-solving

Programming

Databases

Boolean logic

Logic gates

Data, spreadsheets and AI

The steps for this lesson

Try it

Check yourself