National 5 and Higher
Text
Text values are enclosed in apostrophes (or quotation marks):
SELECT * FROM orders WHERE productname LIKE 'Marzipan'
Number
SELECT * FROM orders WHERE price > 14.95
Date
SQLite stores dates as text fields. They must be stored as ‘yyyy-mm-dd’ eg ‘2021-03-05’
SELECT * FROM orders WHERE date <= '2020-03-28'
Time
SQLite stores times as text fields. They must be stored as ‘hh:mm:ss’
SELECT * FROM orders WHERE time BETWEEN '09:00:00' AND '09:59:59'
Boolean
SQLite stores Boolean values as numbers – 1 for True and 0 for False
SELECT * FROM orders WHERE delivered = 1
Advanced Higher
In Advanced Higher you must use standard SQL types:
| Data Type | Sample | SQL implementation |
Comment |
| integer | 32, -46 |
int |
using a size parameter is optional; it is used to restrict the maximum display width |
| float | 3.14 |
float(size,d) |
the size parameter specifies the total number of digits displayed, while d specifies the number of digits after the decimal point |
| varchar | ABC123D |
varchar(size) |
the size parameter is mandatory, to restrict number of characters possible between 0 and 65535 |
| date | 2019-05-23 |
date |
format is YYYY-MM-DD |
| time | 09:12:47 |
time |
format is YYYY-MM-DD |
