Y11 CS Unit 10 T5 Databases and SQL

Objectives

• Be able to create a single-table database including:

• Fields, Records and Validation

• Suggest suitable data types for fields, including:

• Text/Alphanumeric

• Boolean, Character, Integer and Real

• Date/Time

• Understand the purpose of primary keys

• Read, understand and complete SQL including:

• SELECT, FROM, WHERE, ORDER BY, SUM and COUNT

Home

Open in new tab

Copy link

Databases and SQL

Unit 10 Advanced programming and databases

Starter

• Previously you saw how data could be stored and

accessed using a file

• Where else can data be stored

and accessed?

• Give some examples from

everyday life that use
such a system

Databases and SQL

Unit 10 Advanced programming and databases

Starter

• Data can be stored in a structured way in databases

• Examples include:

• Social media content

• Internet shopping sites – for products, prices, descriptions etc.

• School student records

• Tax records

Open Ended

Previously you saw how data could be stored and accessed using a file

Where else can data be stored and accessed?

Give 4 examples from everyday life that use such a system

Databases and SQL

Unit 10 Advanced programming and databases

The database concept

• A database is a structured way to store data so that

it can be retrieved using queries

Animal

Length

TopSpeed

Brown bear

2.48

21.7

Elk

1.4

45.1

Lion

2.8

49.7

Pig

0.9

10.9

Record

Field

Animals

Table

Table name Field name

Databases and SQL

Unit 10 Advanced programming and databases

Data types

• A developer is creating a

database for a local shop
which is starting to sell
products online

• What fields do you think

would need to be included
to store the customer
information?

• What data types will

these need to be?

Multiple Choice

A database is a structured way to store data so that it can be retrieved using queries

Which is an example of a Table Name?

• queries

• Brown bear

• 2.48

• Animals

• TopSpeed

Multiple Choice

A database is a structured way to store data so that it can be retrieved using queries

Which is an example of a Field Name?

• Animals

• Elk

• 2.48

• Record

• Length

Multiple Select

A database is a structured way to store data so that it can be retrieved using queries

Which ARE exampleS of a Field value not name?

• Elk

• Lion

• 1.4

• 10.9

• 21.7

Multiple Choice

A database is a structured way to store data so that it can be retrieved using queries

What is a Record?

• Brown bear

• A row of data in a date base table

• A column of data in a table

• I dont know

• Animal

Databases and SQL

Unit 10 Advanced programming and databases

Data types

• Some possible fields the nail salon will need along

with their data type:

• Other data types include ‘character’ and ‘real’

Field names

Data type

FirstName

Text/alphanumeric

LastName

Text/alphanumeric

Mobile

Text/alphanumeric

DateJoined

Date/Time

NumberOfCustomerOrders

Integer

Databases and SQL

Unit 10 Advanced programming and databases

Primary keys

• A primary key is a field, where every value stored in

it will be unique

• It identifies the record

• What is the primary key used in the Customers table?

CustomerID

FirstName

LastName

Mobile

DateJoined

NumberOfCustomer
Orders

1

Grace

Evans

07700 900 452

6/10/2020

17

2

Lily

Thomas

07700 900 218

17/07/2020

23

3

Rosie

Scott

07700 900 213

09/05/2021

11

4

Nadia

Rahman

07700 900 892

26/02/2021

8

Customers

Match

Match the following Field names with a suitable Data type

• Mobile

• DateJoined

• NumberOfCustomerOrders

Text / alphanumeric

Date /Time

Integer

• Text / alphanumeric

• Date /Time

• Integer

Databases and SQL

Unit 10 Advanced programming and databases

Validation

• Validation allows rules to be placed on fields

• One rules is that a mobile number to start with a zero

• The rule for this is:

Like "0*"

• This means the text entered must start with 0 and then have

any other characters after

• What other validation could be carried out on the data?

Databases and SQL

Unit 10 Advanced programming and databases

Validation

• Data will need to be the correct data type (type validation)

• A length check can be used for the firstName, Lastname and

mobile number

• The DateJoined may also be validated

A validation to check a date is later than the 1stJan 2020:
>1/1/2020

Multiple Choice

• What is the primary key used in the
  Customers table?

• FirstName

• CustomerID

• Mobile

• NumberOfCustomerOrders

• KeyID

Open Ended

A length check can be used for the firstName, Lastname and mobile number

Use one of these examples to say exactly what this means - explain fully to a non DB person.

Databases and SQL

Unit 10 Advanced programming and databases

What is SQL?

• SQL stands for Structured Query Language

• It is a language which allows you to create, query

and add data to databases

• SQL is used within most Database Management Systems,

including MySQL, SQL Server and MS Access

• SQL queries can be used within high level programming

languages such as Python, Visual Basic or C#

Databases and SQL

Unit 10 Advanced programming and databases

Writing a query using SQL

• The SQL syntax for querying a database is:

SELECT … (list the fields to be displayed)

FROM … (specify the table name)

WHERE … (list the search criteria)

Databases and SQL

Unit 10 Advanced programming and databases

The SELECT statement

• The table above is named members

• The following SQL statement will select all the records and

fields from the table

SELECT MemberID, FirstName, Surname, Gender, Town
FROM members

MemberID

FirstName

Surname

Gender

Town

1

David

Johnson

M

Ipswich

2

Christine

Bates

F

Woodbridge

3

Jasmine

Hamid

F

Ipswich

4

Peter

Okello

M

Colchester

5

Stephen

Hines

M

Woodbridge

Reorder

The following SQL statement will select all the records and fields from the table

when you put THEM in the correct order

• SELECT MemberID,

• FirstName, Surname, Gender,

• Town FROM members

1

2

3

• 1
• 2
• 3

Databases and SQL

Unit 10 Advanced programming and databases

Using a wild card

• You can use a “wild card” * to mean “all columns”

SELECT *
FROM members

• You can select specific columns in a query:

SELECT FirstName, Surname
FROM members

MemberID

FirstName

Surname

Gender

Town

1

David

Johnson

M

Ipswich

2

Christine

Bates

F

Woodbridge

3

Jasmine

Hamid

F

Ipswich

4

Peter

Okello

M

Colchester

5

Stephen

Hines

M

Woodbridge

Multiple Choice

The WHERE clause is used to select only records satisfying a specified condition:

SELECT Surname , Gender

FROM members

WHERE FirstName = 'Peter’

What is the result of this query?

[ which shows the fields selected, in order and for the correct gender]

Databases and SQL

Unit 10 Advanced programming and databases

The WHERE clause

• The WHERE clause is used to select only records

satisfying a specified condition:

SELECT FirstName, Surname
FROM members
WHERE Town = 'Ipswich’

• What is the result of this query?

MemberID

FirstName

Surname

Gender

Town

1

David

Johnson

M

Ipswich

2

Christine

Bates

F

Woodbridge

3

Jasmine

Hamid

F

Ipswich

4

Peter

Okello

M

Colchester

5

Stephen

Hines

M

Woodbridge

Databases and SQL

Unit 10 Advanced programming and databases

The WHERE clause

SELECT FirstName, Surname
FROM members
WHERE Town = 'Ipswich’

• Query result:
FirstName

Surname

David

Johnson

Jasmine

Hamid

MemberID

FirstName

Surname

Gender

Town

1

David

Johnson

M

Ipswich

2

Christine

Bates

F

Woodbridge

3

Jasmine

Hamid

F

Ipswich

4

Peter

Okello

M

Colchester

5

Stephen

Hines

M

Woodbridge

Databases and SQL

Unit 10 Advanced programming and databases

Operators in the WHERE clause

• You have already used some of these operators

in queries:

= != > < >= <= AND, OR, NOT

• You can use these operators in SQL queries:

SELECT *
FROM members
WHERE Town = 'Colchester' OR Town = ‘Ipswich'

SELECT FirstName, Surname
FROM members
WHERE (Town = 'Colchester' OR Town = ‘Ipswich') AND
Gender = 'F'

Databases and SQL

Unit 10 Advanced programming and databases

SELECT queries

• Look at the following table named ‘Dogs’

• Write SQL queries to find:

• All the names and breeds of female dogs
• All fields for dogs older than four (including those aged four)

DogID

Name

Breed

Colour

Gender

Age

1

Coco

Labrador

Brown

M

3

2

Milly

Spaniel

Black

F

5

3

Sasha

Retriever

Golden

F

4

4

Mark

Labrador

Black

M

3

5

Marlee

Retriever

Golden

F

2

6

Alfie

Spaniel

Brown

M

6

7

Georgie

Labrador

Brown

M

4

Databases and SQL

Unit 10 Advanced programming and databases

SELECT queries

• Dogs table

• All the names and breeds of female dogs

• SELECT Name, Breed FROM Dogs WHERE Gender = 'F'

• All fields for dogs older than four (including those

aged four)

• SELECT * FROM Dogs WHERE Age >= 4

DogID

Name

Breed

Colour

Gender

Age

1

Coco

Labrador

Brown

M

3

2

Milly

Spaniel

Black

F

5

3

Sasha

Retriever

Golden

F

4

4

Mark

Labrador

Black

M

3

5

Marlee

Retriever

Golden

F

2

6

Alfie

Spaniel

Brown

M

6

7

Georgie

Labrador

Brown

M

4

Open Ended

Write SQL query to find:

All the colours, age and breeds of male dogs

Open Ended

Write SQL query to find:

All the breeds of female dogs age over 5

Open Ended

Write SQL query to find:

All the names (just the name) of dogs that are black

Starts like this

SELECT Name

FROM Dogs

Databases and SQL

Unit 10 Advanced programming and databases

Sorting: the ORDER BY keyword

• ORDER BY allows a query to sort data by ascending

or descending order

• For ascending order

SELECT * FROM members
ORDER BY Surname ASC

• For descending order

SELECT * FROM members
ORDER BY Surname DESC

Databases and SQL

Unit 10 Advanced programming and databases

Sorting

• What will the result be for the following query?

SELECT FirstName, Surname
FROM members
ORDER BY Town ASC

MemberID

FirstName

Surname

Gender

Town

1

David

Johnson

M

Ipswich

2

Christine

Bates

F

Woodbridge

3

Jasmine

Hamid

F

Ipswich

4

Peter

Okello

M

Colchester

5

Stephen

Hines

M

Woodbridge

Databases and SQL

Unit 10 Advanced programming and databases

Result of sorting

• Result after execution of the statement:

SELECT FirstName, Surname, Town
FROM members
ORDER BY Town ASC

FirstName

Surname

Town

Peter

Okello

Colchester

David

Johnson

Ipswich

Jasmine

Hamid

Ipswich

Christine

Bates

Woodbridge

Stephen

Hines

Woodbridge

Databases and SQL

Unit 10 Advanced programming and databases

SUM and COUNT

• SQL is often used to count records or find the sum of

a particular field

• What do you think the results of the following

queries are?

SELECT COUNT(Quantity)
FROM Receipts

SELECT SUM(Cost)
FROM Receipts

ReceiptID

Product

Quantity

Cost

1

Apples

6

£2.20

2

Cheese

1

£2.50

3

Bread

2

£1.50

4

Pears

6

£1.80

Receipts

Databases and SQL

Unit 10 Advanced programming and databases

SUM and COUNT

• What do you think the results of the following

queries are?

SELECT COUNT(Quantity) FROM Receipts

Result:

SELECT SUM(Cost)
FROM Receipts

Result:

ReceiptID

Product

Quantity

Cost

1

Apples

6

£2.20

2

Cheese

1

£2.50

3

Bread

2

£1.50

4

Pears

6

£1.80

Receipts

COUNT(Quantity)

15

SUM(Cost)

£8.00

Databases and SQL

Unit 10 Advanced programming and databases

Plenary

• Look at the table called

Animals on the right

• In pairs create SQL

statements for the following:

• Find all animal names in

alphabetical order

• Find all animal names and weights that are over 1000 kg

• Find all animals, including all fields that are over 2 m

Animal

Height_m

Weight_kg

Rhino

1.8

2000

Giraffe

5.5

1800

Emu

1.8

55

Llama

1.7

200

Sea lion

2.4

360

Animals

Databases and SQL

Unit 10 Advanced programming and databases

Animal

Height_m

Weight_kg

Rhino

1.8

2000

Giraffe

5.5

1800

Emu

1.8

55

Llama

1.7

200

Sea lion

2.4

360

Plenary

• All animal names in alphabetical order

SELECT Animal FROM Animals
ORDER BY Animal ASC

• All animal names and weights

that are over 1000 kg
SELECT Animal, Weight_kg FROM
Animals WHERE Weight_kg > 1000

• All animals, including all fields that are over 2 m

SELECT * FROM Animal WHERE Height_m > 2

Animals

Multiple Choice

How many records would this output?

SELECT Animal

FROM Animals

ORDER BY Animal ASC

• 3

• 5

• 4

• 0

• 6

Multiple Choice

How many records would this output?

SELECT Animal, Weight_kg

FROM Animals

WHERE Weight_kg > 2000

• 3

• 5

• 4

• 0

• 6

Multiple Choice

How many records would this output?

SELECT * FROM Animals

WHERE Height_m < 2

• 3

• 5

• 4

• 0

• 6

Multiple Choice

How many records would this output?

SELECT * FROM Animals

WHERE Height_m >= 2.4

• 3

• 5

• 4

• 0

• 2

Multiple Choice

How many records would this output?

SELECT * FROM Animals

WHERE Animals = "S*"

• 3

• 5

• 1

• 0

• 2

Multiple Choice

How many records would this output?

SELECT * FROM Animals

WHERE Height_m > 1.8

or Weight_kg >= 1800

• 3

• 5

• 1

• 0

• 2

Multiple Choice

How many records would this output?

SELECT * FROM Animals

WHERE Height_m = 5.5

AND Weight_kg = 1800

• 3

• 5

• 1

• 0

• 2

Multiple Choice

How many records would this output?

SELECT * FROM Animals

WHERE Height_m = 2000

AND Weight_kg = 1.8

• 3

• 5

• 1

• 0

• 2

Databases and SQL

Unit 10 Advanced programming and databases

Worksheet 5

• Now complete Worksheet 5