Unit 9

Keywords

• Database (collection of data stored in tables)

• Table has

  • • Field (A field is one piece of information)

    • Record (A record is a set of fields about one object)

• Data types

  • Boolean (One of two options. E.g. Yes/No, True/False)

  • Text/Alphanumeric (Combination of letters/symbols/numbers. E.g "243-H")

  • Date/time (A date and/or a time. E.g. 07/02/2024, 8:30)

  • Integer/Number (Whole number)

  • Real (A number with at least one decimal place. E.g. 45.44)

• Primary Key

  • Unique fields in a database //Unique identifier

  • No repetitions in values // No duplication

  • No null values

• Defining a Database

  • Decide fields

  • Decide data types

  • Primary key

SQL

• Structured Query Language

• Performs:

  • define tables

  • change tables

  • add data

  • search data

  • perform calculations

• Commands

  • SELECT (lists the fields to be displayed)

  • FROM (identifies the table)

  • WHERE (identifies the search criteria and returns all data in the field where WHERE condition is true)

  • ORDER BY (can be ASC-ascending or DESC-descending and will return data in the specified order)

  • SUM (total the values in the given field)

  • COUNT (count records in the given field)

Sample Question (from 2023 November Series)

A model shop wants to set up a database to help with stock control of the model figures available

for sale. The shop wants to store this information about the model figures:

Field 1 – catalogue number, for example MD1234

Field 2 – description, for example ‘small white dog’

Field 3 – number in stock, for example 5

Field 4 – the price of each model, for example 7.40

Field 5 – if the model has already been painted, yes or no.

(a) The shop needs five fields for each record. Give a suitable name and data type for each field.

One mark for each appropriate field name and correct data type

1. CatNo text/alphanumeric

2. Description text/alphanumeric

3. StockLevel integer

4. Price real

5. Painted Boolean

(b) (i) Give the name of the field that should be used for the primary key.

CatNo

(ii) State why this field is used as the primary key.

Each entry in this field is a unique identifier

(c) Structured query language (SQL) is used to query data stored in this database. State what these SQL commands are used for.

• SELECT (lists the fields to be displayed)

• FROM (identifies the table)

• WHERE (identifies the search criteria)

(d) Complete the structured query language (SQL) to return the catalogue number, description, and price of all model figures available for sale whose price is greater than 45000.

SELECT CatNo, Description, Price

FROM model figures

WHERE Price > 45000;