3.7 Relational Databases and SQL

A database is a persistent collection of organised storage that can be retrieved through queries

Structured Query Language

Duplicate pieces of data

Storing repeated data in the same table can lead to inconsistencies in the data

A number or date is within a sensible/allowed range i.e. Month between 1 and 12

Data is the right type

Text entered is not too long or too short - for example a password is between 8 and 15 characters

Checks that the data has been entered, i.e. The field has been left blank

Checks the format is correct, for example, a postcode or email is correct

Making sure the user enters the right thing from a list. They can only select an option from a drop-down menu.

*

Select * FROM table1

Select * FROM table1 WHERE Forename = ‘Bob’ • If the value is a string (or varchar, text etc.) then it requires quotation marks.

Select Forename, Surname FROM table1 ORDER BY Surname ASC • ASC = Ascending • DESC = Descending

INSERT INTO table1 (userId, Forename, Surname, Age, Height, MobileNumber) VALUES (1, ‘Bob’, ‘Jones’, 47, 1.87, ‘07834 577780’) • If the value is a string (or varchar, text etc.) then it requires quotation marks.

UPDATE table1 Surname = ‘Rich’ WHERE userId = 1 It is the safest option to use the primary key as if you use another field, then you might change someone else’s details as well.

DELETE FROM table1 WHERE userId = 1

SELECT table1.Forename, table1.Surname, table2.Address FROM table1 INNER JOIN ON table1.userId = table2.userId WHERE userId = 1

A primary key is a field in a database table and acts as a unique identifier for each record.

It is the reference to the primary key of another table. It is used to create a link/relationship between two tables.

A whole number

A number with a fractional component (a decimal)

Date and Time

A string of fixed length (up to 8,000 characters long)