Learning check for A3.3.2

The SQL for this learning activity can be found at this link. Please quote it in to your database. 

SQL Practice Worksheet

Database: Science Fiction Video Games

In this activity you will write SQL queries to answer questions about a database containing information about science-fiction video games.

The database contains the following tables:

studios

  • studio_id
  • studio_name
  • country
  • founded_year

species

  • species_id
  • species_name
  • homeworld

games

  • game_id
  • title
  • release_year
  • studio_id
  • genre
  • rating

characters

  • character_id
  • character_name
  • species_id
  • game_id
  • role

missions

  • mission_id
  • mission_name
  • game_id
  • difficulty
  • reward_credits

 

Part 1 — Basic Data Retrieval

1. List all games

A gaming journalist wants a simple list of all video game titles in the database.

Write a query to display:

  • the title of every game

Concepts:
SELECT, FROM

 

2. View basic game information

A reviewer wants to see basic information about each game.

Display:

  • title
  • release year
  • genre

Concepts:
SELECT

 

 

3. Sort games by release year

A historian studying the evolution of sci-fi games wants the games listed in chronological order.

Display:

  • title
  • release_year

Sort from oldest to newest.

Concepts:
ORDER BY, ASC

 

 

4. Sort games by rating

A gamer wants to see the highest rated games first.

Display:

  • title
  • rating

Sort by rating from highest to lowest.

Concepts:
ORDER BY, DESC

 

 

Part 2 — Filtering Data

5. Games released after 2015

A store owner wants to know which games were released recently.

Display:

  • title
  • release_year

Only show games released after 2015.

Concepts:
WHERE

6. Games released within a time period

A historian is studying games released during the golden era of sci-fi games.

Display:

  • title
  • release_year

Show games released between 2010 and 2020.

Concepts:
BETWEEN

7. Find games of a specific genre

A player wants to find strategy games.

Display:

  • title
  • genre

Concepts:
WHERE

 

8. Find games matching a name pattern

A marketing analyst wants to find games whose titles include the word “Star”.

Display:

  • title

Concepts:
LIKE, %

 

Part 3 — Logical Conditions

9. Games with high ratings

A reviewer wants games that have ratings above 8 AND were released after 2018.

Display:

  • title
  • rating
  • release_year

Concepts:
AND

 

10. Games in multiple genres

A gamer is interested in RPG OR Strategy games.

Display:

  • title
  • genre

Concepts:
OR

 

11. Games that are not shooters

A parent wants to see games that are NOT in the Shooter genre.

Display:

  • title
  • genre

Concepts:
NOT

 

Part 4 — Distinct Values

12. Unique genres

A designer wants to know what genres exist in the database.

Display:

  • genre

Each genre should appear only once.

Concepts:
DISTINCT

 

Part 5 — Working With Multiple Tables

13. Games and their studios

A journalist wants to know which studio made each game.

Display:

  • game title
  • studio name

Concepts:
JOIN

 

14. Characters and their species

A lore researcher wants to know the species of each character.

Display:

  • character_name
  • species_name

Concepts:
JOIN

 

15. Characters and their games

A wiki editor wants a list of characters and the game they appear in.

Display:

  • character_name
  • title

Concepts:
JOIN

]

Part 6 — Aggregation

16. Count the number of games per genre

A game publisher wants to know how many games exist in each genre.

Display:

  • genre
  • number_of_games

Concepts:
GROUP BY

 

17. Average rating per genre

A critic wants to know which genres tend to receive higher ratings.

Display:

  • genre
  • average rating

Concepts:
GROUP BY

 

Part 7 — Filtering Groups

18. Genres with many games

A market analyst wants to find genres that have more than 5 games.

Display:

  • genre
  • number_of_games

Concepts:
GROUP BY, HAVING

 

19. Studios with multiple games

A publisher wants to identify studios that have developed more than 3 games.

Display:

  • studio_name
  • number_of_games

Concepts:
JOIN, GROUP BY, HAVING

 

Challenge Questions

20. Difficult missions

A player wants to see the most difficult missions with large rewards.

Display:

  • mission_name
  • difficulty
  • reward_credits

Only show missions where:

  • difficulty is Hard
  • reward is greater than 5000

Sort by reward highest first.

Concepts:
WHERE, AND, ORDER BY

 

21. Which games have the most characters?

A lore analyst wants to know which games contain the largest number of characters.

Display:

  • game title
  • number_of_characters

Concepts:
JOIN, GROUP BY, ORDER BY

 

22. Which species appear most often?

A biology researcher studying alien races wants to know which species appear in the most characters.

Display:

  • species_name
  • number_of_characters

Concepts:
JOIN, GROUP BY, ORDER BY

SQL Practice Worksheet — Markscheme

General Marking Guidance

  • Award marks for correct SQL structure and logic.
  • Accept equivalent SQL syntax where appropriate.
  • Do not penalize for capitalization differences.
  • Minor column ordering differences should still receive full marks if the query is logically correct.

 

Part 1 — Basic Data Retrieval

Question 1

List all games.

Correct query example:

SELECT title
FROM games;

Markscheme

Award 1 mark for:

  • Selecting title from the games table

Total: 1 mark

Question 2

Display title, release year, and genre.

Example answer

SELECT title, release_year, genre
FROM games;

Markscheme

Award 1 mark for:

  • Selecting the correct fields from the correct table

Total: 1 mark

Question 3

Sort games from oldest to newest.

Example answer

SELECT title, release_year
FROM games
ORDER BY release_year ASC;

Markscheme

Award 1 mark for:

  • Correct query retrieving title and year

Award 1 mark for:

  • Correct use of ORDER BY release_year ASC

Total: 2 marks

Question 4

Sort by rating highest to lowest.

Example answer

SELECT title, rating
FROM games
ORDER BY rating DESC;

Markscheme

Award 1 mark for:

  • Correct fields selected

Award 1 mark for:

  • Correct ORDER BY rating DESC

Total: 2 marks

Part 2 — Filtering Data

Question 5

Games released after 2015.

Example answer

SELECT title, release_year
FROM games
WHERE release_year > 2015;

Markscheme

Award 1 mark for:

  • Correct use of WHERE

Award 1 mark for:

  • Correct condition (release_year > 2015)

Total: 2 marks

Question 6

Games between 2010 and 2020.

Example answer

SELECT title, release_year
FROM games
WHERE release_year BETWEEN 2010 AND 2020;

Markscheme

Award 1 mark for:

  • Correct use of BETWEEN

Award 1 mark for:

  • Correct range values

Total: 2 marks

Question 7

Strategy games.

Example answer

SELECT title, genre
FROM games
WHERE genre = 'Strategy';

Markscheme

Award 1 mark for:

  • Correct WHERE condition filtering by genre

Total: 1 mark

Question 8

Games containing “Star”.

Example answer

SELECT title
FROM games
WHERE title LIKE '%Star%';

Markscheme

Award 1 mark for:

  • Correct use of LIKE

Award 1 mark for:

  • Correct use of % wildcard

Total: 2 marks

Part 3 — Logical Conditions

Question 9

Ratings above 8 AND released after 2018.

Example answer

SELECT title, rating, release_year
FROM games
WHERE rating > 8 AND release_year > 2018;

Markscheme

Award 1 mark for:

  • Correct first condition

Award 1 mark for:

  • Correct use of AND

Total: 2 marks

Question 10

RPG OR Strategy games.

Example answer

SELECT title, genre
FROM games
WHERE genre = 'RPG' OR genre = 'Strategy';

Markscheme

Award 1 mark for:

  • Correct OR logic

Total: 1 mark

Question 11

Not shooter games.

Example answer

SELECT title, genre
FROM games
WHERE NOT genre = 'Shooter';

Accept:

WHERE genre != 'Shooter'

Markscheme

Award 1 mark for:

  • Correct exclusion of Shooter genre

Total: 1 mark

Part 4 — DISTINCT

Question 12

Unique genres.

Example answer

SELECT DISTINCT genre
FROM games;

Markscheme

Award 1 mark for:

  • Correct use of DISTINCT

Total: 1 mark

Part 5 — JOIN

Question 13

Games and studios.

Example answer

SELECT games.title, studios.studio_name
FROM games
JOIN studios
ON games.studio_id = studios.studio_id;

Markscheme

Award 1 mark for:

  • Correct join between tables

Award 1 mark for:

  • Correct join condition

Total: 2 marks

Question 14

Characters and species.

Example answer

SELECT characters.character_name, species.species_name
FROM characters
JOIN species
ON characters.species_id = species.species_id;

Markscheme

Award 1 mark for:

  • Correct join

Award 1 mark for:

  • Correct join condition

Total: 2 marks

Question 15

Characters and games.

Example answer

SELECT characters.character_name, games.title
FROM characters
JOIN games
ON characters.game_id = games.game_id;

Markscheme

Award 1 mark for:

  • Correct join

Award 1 mark for:

  • Correct join condition

Total: 2 marks

Part 6 — Aggregation

Question 16

Count games per genre.

Example answer

SELECT genre, COUNT(*) AS number_of_games
FROM games
GROUP BY genre;

Markscheme

Award 1 mark for:

  • Correct use of COUNT

Award 1 mark for:

  • Correct use of GROUP BY

Total: 2 marks

Question 17

Average rating per genre.

Example answer

SELECT genre, AVG(rating)
FROM games
GROUP BY genre;

Markscheme

Award 1 mark for:

  • Correct use of AVG

Award 1 mark for:

  • Correct grouping

Total: 2 marks

Part 7 — HAVING

Question 18

Genres with more than 5 games.

Example answer

SELECT genre, COUNT(*) AS number_of_games
FROM games
GROUP BY genre
HAVING COUNT(*) > 5;

Markscheme

Award 1 mark for:

  • Correct GROUP BY

Award 1 mark for:

  • Correct HAVING condition

Total: 2 marks

Question 19

Studios with more than 3 games.

Example answer

SELECT studios.studio_name, COUNT(*) AS number_of_games
FROM games
JOIN studios
ON games.studio_id = studios.studio_id
GROUP BY studios.studio_name
HAVING COUNT(*) > 3;

Markscheme

Award 1 mark for:

  • Correct join

Award 1 mark for:

  • Correct grouping and HAVING

Total: 2 marks

Challenge Questions

Question 20

Hard missions with reward > 5000.

Example answer

SELECT mission_name, difficulty, reward_credits
FROM missions
WHERE difficulty = 'Hard'
AND reward_credits > 5000
ORDER BY reward_credits DESC;

Markscheme

Award 1 mark for:

  • Correct filtering conditions

Award 1 mark for:

  • Correct sorting

Total: 2 marks

Question 21

Games with most characters.

Example answer

SELECT games.title, COUNT(characters.character_id) AS number_of_characters
FROM characters
JOIN games
ON characters.game_id = games.game_id
GROUP BY games.title
ORDER BY number_of_characters DESC;

Markscheme

Award 1 mark for:

  • Correct join and count

Award 1 mark for:

  • Correct grouping and ordering

Total: 2 marks

Question 22

Most common species.

Example answer

SELECT species.species_name, COUNT(*) AS number_of_characters
FROM characters
JOIN species
ON characters.species_id = species.species_id
GROUP BY species.species_name
ORDER BY number_of_characters DESC;

Markscheme

Award 1 mark for:

  • Correct join

Award 1 mark for:

  • Correct grouping and ordering

Total: 2 marks

 

Total Marks (example allocation): 39 marks

Last Edited: March 16 2026 (2 months ago)
Categories:
Theme A: Concepts of computer science Databases A3.3 Database programming