Section A: Core Concepts & Syntax
1
A database table named
Products contains the following fields:
ProductID, ProductName, Price, Category.
Fill in the missing SQL keywords to complete the query that finds all products with a price greater than 10 in the "Toys" category.
SELECT ProductID, ProductName
___________ Products
WHERE Price > 10
___________ Category = "Toys"
[2]
2
(a) State the character used as a wildcard in SQL to select
all fields (columns).
[1]
(b) State the character used as a wildcard in SQL to match
part of a string (e.g. searching for names starting with "A").
[1]
3
A student has written the following SQL statement to find a student with the ID of 505. The field
StudentID is an
Integer data type.
SELECT "FirstName", "LastName"
FROM Students
WHERE StudentID = "505"
Identify
two specific syntax errors in this statement that would prevent it from working or cause it to lose marks in an exam.
[2]
Section B: Logic & Prediction
4
Table: GameCharacters
| ID | CharName | Class | Level | IsActive |
|---|
| 1 | Thor | Warrior | 50 | True |
| 2 | Loki | Mage | 45 | False |
| 3 | Hulk | Warrior | 48 | True |
| 4 | Odin | Mage | 100 | True |
Consider the following SQL query:
SELECT CharName, Level
FROM GameCharacters
WHERE (Class = "Warrior" OR Level > 90) AND IsActive = True
List the names (
CharName) of the characters that will be output by this query.
[3]
Turn over
5
Using the same
GameCharacters table, write down the single SQL condition (the part after
WHERE) needed to find all characters whose name ends with the letter 'i'.
[2]
Section C: Coding & Refinement
6
A library database uses a table called
Books. The fields are:
BookID (Integer),
Title (String),
Author (String),
YearPublished (Integer),
Available (Boolean).
Write an SQL statement to select the
Title and
Author of all books that meet
all of the following criteria:
- Published before the year 2000.
- Are currently Available (True).
- Written by the author "J.K. Rowling".
[5]
7
An array
data stores student marks:
data = [ ["Ali", 85], ["Bea", 92], ["Caz", 70] ]
(a) Write the SQL statement that would perform the equivalent action of the following Python code:
for row in data:
if row[1] >= 80:
print(row[0])
Assume the table is called
Students, and fields are
Name and
Score.
[3]
(b) Explain
one advantage of using a Database (SQL) over a 2D Array (Text File) for storing 10,000 student records.
[2]
8
A user inputs:
Smith"; DROP TABLE Students; -- into a search box.
(a) State the name of this specific type of security attack.
[1]
(b) State
one method a programmer can use to prevent this.
[1]
END OF QUESTION PAPER