3.7- Relational Databases and SQL

Database

A structured way of storing data so it can be retrieved using queries

Table

A collection of related data

Record

A collection of fields about the same person, thing, item or object in a database

Field

A collection of fields about the same person, thing, item or object in a database

Primary Key

A field that stores unique data for each record in a table. These are normally an ID Number

Foreign Key

A field in a table that references a primary key of
another table

Database Data Types

Flat File Database

A single table of data stored inside a single text file. These are often stored using A CSV format

Relational Database

A database that contains multiple tables that are linked together. They allow us to design tables that reduce inconsistencies and eliminate data redundancy.

Relationship

A link between 2 tables. There are 3 types of relationships:

SQL (Structured Query Language)

A language that allows you to create, query, update and delete data to and from databases

Writing a Query SQL

SELECT (The fields that you want to be displayed)

FROM (The table name)

WHERE (The search criteria)

SELECT Field1, Field2, Field3
FROM Table1
WHERE x=y

Database Reference (Customer)

The SELECT Statement (Quering Data)

Allows you to select the records from a table using the fields of the table. You can either type all the fields that you require or use a wildcard

Example:

SELECT Name, Address,PhoneNo

FROM customer

Wildcard

Allows you to select all the columns of data

Example:

SELECT *

FROM customer

The WHERE Clause

Allows you to select only records satisfying a specified condition

Example:

SELECT Name, PhoneNo

FROM customer

WHERE age = 42

WHERE Clause Operators

ORDER BY Statement

Allows you to sort a query into ascending (ASC) or descending(DESC) order

Example:

SELECT *

FROM customer

ORDER BY Name ASC

The INSERT Statement (Inserting Data in A Table)

Allows you to enter a record into a table

Example:

INSERT INTO customer (CustID, Name, Address, PhoneNo, Age)

VALUES (1006, “Geoffrey”, “Manchester”, 782104, 63)

The UPDATE Statement (Updating Data in A Table)

Allows you to update a record (or records) in a table

Example:

UPDATE customer

SET Name = “Simon”

WHERE CustomerID = 5

The DELETE Statement (Deleting Data in A Table)

Allows you to delete a record (or records) from a table

Example:

DELETE FROM customer

WHERE name = “Simon”