7 Relational databases and structured query language (SQL)

7 Relational databases
and structured query language (SQL)
Learning objectives:
■ Be able to use SQL to retrieve ■ 7.2 Structured Query Language
data from a relational Querying a database
database, using the commands e main purpose of storing data in a database is to enable applications to
• SELECT interrogate the database for information. is interrogation is called querying
• FROM the database.

• WHERE Structured Query Language (SQL)

• ORDER BY...ASC | Structured Query Language (SQL) can be used to query a database. It is a
DESC simplified programming language.

20
■ Be able to use SQL to insert Retrieving data from a single table

20
data into a relational database Table 7.2.1 shows data for the Student table with structure
using the command:
nd
Student (StudentId, StudentName, Gender)
e following query, expressed in SQL, will retrieve all of the data in the
INSERT INTO table_name
Bo
(column1, column2, ...) Student table
VALUES (value1, value2, ...)
R

SELECT *
■ Be able to use SQL to edit FROM Student;
rK

and delete data in a relational

e wildcard character * matches the attribute/field list
database using the commands:
D

StudentId, StudentName, Gender

ht

UPDATE table_name
ig

SET column1 = value1, Student

StudentId Gender
Name
yr

column2 = value2, ...

WHERE condition 1 Ames M
op

2 Baloch F
C

DELETE FROM table_name 3 Cheng F

WHERE condition 4 Dodds M
5 Groos M
6 Smith F
Table 7.2.1 Table Student

e ANSI/ISO SQL standard requires that a semicolon is used at the end of

the SQL statement but some systems relax this requirement. When writing
SQL the convention is to use upper case for the SQL commands.
If we wanted just the data for StudentName we would refine the query as
follows
SELECT StudentName
FROM Student;

330
7 Relational databases and structured query language (SQL)

We could refine the search even further by adding a WHERE clause that applies a search condition as follows
SELECT StudentName
FROM Student
WHERE Gender = 'F';
e result set that would be returned when this query is applied to table Student would be as follows
Baloch
Cheng
Smith
because only these rows of the table match the search condition Gender = 'F'.
Gender = 'F' is actually called a predicate because it evaluates to either TRUE or FALSE.
If we also wanted the values of StudentId returned then the query would be
SELECT StudentId, StudentName

20
FROM Student
WHERE Gender = 'F';

20
Questions
1 nd
Write an SQL query that returns the names of all students in Table 7.2.1 who are male.
Bo
R

Retrieving data from multiple tables

rK

Table 7.2.2 shows data in table form for the Ward table with WardName NurseInCharge NoOfBeds
D

structure Victoria Sister Bunn 30

ht

Ward (WardName, NurseInCharge, NoOfBeds) Aylesbury Sister Moon 40

ig

Table 7.2.2 Table Ward

Table 7.2.3 shows data in table form for the Patient table with
yr

structure PatientId Surname WardName

op

Patient (PatientId, Surname, WardName) 1 Bond Aylesbury

C

2 Smith Victoria
e two tables are linked via a shared or common attribute
3 Jones Aylesbury
WardName. e existence of an attribute common to both tables is
4 Biggs Victoria
not enough to join data from the corresponding tables correctly, as
Table 7.2.3 Table Patient
the following SQL query demonstrates
SELECT [Link], [Link], Victoria Sister Bunn 1

[Link] Victoria Sister Bunn 2

FROM Ward, Patient;

Victoria Sister Bunn 3
Victoria Sister Bunn 4
e part of the query [Link] references the WardName
Aylesbury Sister Moon 1
attribute in table Ward and the part [Link] references
Aylesbury Sister Moon 2
PatientId attribute in table Patient.
Aylesbury Sister Moon 3
e FROM Ward, Patient part joins both relations without regard Aylesbury Sister Moon 4
for the way that the data is actually linked via matching values of the
shared attribute, WardName. e result set returned by the query is Table 7.2.4 Result set ignoring
shown in Table 7.2.4. relationship between Ward and Patient

331
 7.2 Structured Query Language

When the search condition

WHERE [Link] = [Link]

is added to the SQL query, we are able to exclude values that are not linked by the attribute WardName and to
include only those that are. is SQL query will return the result set that
corresponds to the real world situation shown in Table 7.2.5. Aylesbury Sister Moon 1
Victoria Sister Bunn 2
SELECT [Link], [Link], [Link]
Aylesbury Sister Moon 3
FROM Ward, Patient Victoria Sister Bunn 4
WHERE [Link] = [Link];
Table 7.2.5 Result set taking
e two relations have been joined on their common attribute, WardName, i.e. account of relationship between
where the value of WardName is the same in both tables. Ward and Patient
Writing the query as follows would return the same result set because dropping
the table name prefix before NurseInCharge and PatientId in the SELECT part of the SQL query is allowed where

20
there is no ambiguity as to what is intended.

20
SELECT [Link] , NurseInCharge, PatientId
FROM Ward, Patient
nd
WHERE [Link] = [Link];
Bo

Questions
R

2 Write the SQL query that returns from Tables 7.2.2 and 7.2.3 the name of the nurse in charge of the ward,
rK

surnames of all patients in this ward and the ward name.

D
ht

Ordering the result set returned by a query

ig

We can order a result set returned by a query in ascending or descending order with the keyword ORDER BY
yr

qualified by one of the keywords ASC or DESC. If the qualifier is omitted then ASC is assumed. For example, we
op

can place the result set returned in ascending SELECT [Link], NurseInCharge, PatientId
C

order on WardName by the query opposite. FROM Ward, Patient

Table 7.2.6 shows the outcome of applying this WHERE [Link] = [Link]
query to the Ward and Patient tables. ORDER BY [Link] ASC;

Questions Aylesbury Sister Moon 1

Aylesbury Sister Moon 3
3 Write the SQL query that returns the names of both nurses and their Victoria Sister Bunn 2
patients, from Tables 7.2.2 and 7.2.3, ordered in descending patient Victoria Sister Bunn 4
name order. Table 7.2.6 Result set ordered
on WardName in ascending
alphabetic order

332
7 Relational databases and structured query language (SQL)

Relational or comparison operators for Comparison

search condition Description
Operator
Table 7.2.7 shows comparison operators that may be used
= Equal to
in SQL queries.
< Less than
Table 7.2.8 shows the outcome of applying this query to > Greater than
the Patient table. <= Less than or equal to
SELECT PatientId, Surname >= Greater than or equal to
FROM Patient <> Not equal to
WHERE PatientId <> 2; Table 7.2.7 Comparison operators for SQL queries

Table Country has the structure 1 Bond

Country (Name, Capital, Population, Area) 3 Jones
4 Biggs
Table 7.2.9 shows some data for table Country.

20
Table 7.2.8 Result set for
e result set returned when the following SQL query
PatientId <> 2

20
SELECT Name, Capital, Population
Name Capital Population Area
FROM Country
WHERE (Population < 7000000); Argentina
nd Buenos Aires 32 300 003 2777815
Bo
Bolivia La Paz 7 300 000 1098575
is applied to this Country table with attributes Brazil Brasilia 150 400 000 8511196
R

Name, Capital, Population, Area is shown below Canada Ottawa 26 500 000 9976147
rK

Chile Santiago 13 200 000 756943

El Salvador San Salvador 5300000
Colombia Bagota 33 000 000 1138907
Guyana Georgetown
D

800000
Cuba Havana 10 600 000 114524
ht

Ecuador Quito 10 600 000 455502

El Salvador San Salvador
ig

5 300 000 20865

Guyana Georgetown 800 000 214969
yr
op

Table 7.2.9 Table Country showing some values

C

Questions
45 Write the SQL query that returns the patient surnames from Table 7.2.3, for which the patient identifier is
less than or equal to 3. Order the result set in descending order of patient identifier (PatientId is the patient
identifier).

5 What result set is returned when this SQL query is applied to the data in Table 7.2.9?
SELECT Capital, Population, Area
FROM Country
WHERE (Population > 32000000);

333
 7.2 Structured Query Language

Deleting data in a single table

e DELETE statement is used to delete rows of a table.
DELETE FROM table_name
WHERE some_column = some_value;

e WHERE clause specifies which row or rows should be deleted. If the WHERE clause is omitted, all rows will
be deleted!
For example referencing Table 7.2.9,
DELETE FROM Country
WHERE Capital = 'Brasilia';
deletes the row Brazil, Brasilia, 150400000, 8511196.

Questions
6 Write the SQL statement to delete the row with BorrowerId 3 in BorrowerId Surname Initial

20
1 Smith K
the Borrower table shown in Table 7.2.10.
2 Barnes W

20
3 Minns M
7 Write the SQL statement to delete the row(s) with
nd
Population > 15000000 in the Country table shown in Table 7.2.9. Table 7.2.10 Table showing some
values for the table Borrower
Bo

Inserting data in a single table

R

e INSERT INTO statement inserts a new row into a table. It is possible to write this statement in two forms.
rK

e first form does not specify the column names where the data will be inserted, only their values:
D

INSERT INTO table_name

ht

VALUES (value1, value2, value3, ...);

ig

e second form specifies both the column names and the values to be inserted:
yr

INSERT INTO table_name (column1, column2, column3, ...)

op

VALUES (value1, value2, value3, ...);

C

In the first form, a value of the correct data type must be supplied for every attribute of the table and the order of
the supplied values must be the same as the corresponding columns in the table.
In the second form, a value for every specified column must be supplied and each value must match in data type the
corresponding specified column, i.e. value1 corresponds to column1, value2 to column2, etc. e value Null will be
inserted for any columns not referenced.
WardName NurseInCharge NoOfBeds
For example, for table Ward, Table 7.2.2, reproduced here Victoria Sister Bunn 30
First form: Aylesbury Sister Moon 40
INSERT INTO Ward VALUES ('Gresham', 'Mr Oonga', 20); Table 7.2.2 Table Ward
is first form creates a new row in Table 7.2.2 with values
'Gresham', 'Mr Oonga', 20
Second form:
INSERT INTO Ward (WardName, NurseInCharge) VALUES ('Savernake', 'Sister Teng');
is second form creates a new row in Table 7.2.2 with values 'Savernake', 'Sister Teng', Null

334
7 Relational databases and structured query language (SQL)

Questions

8 Write the SQL statement to add a new row to the Ward table (Table 7.2.2) for ward 'Amersham',
containing 25 beds. e nurse in charge is 'Sister Brody'.

9 Write the SQL statement to add a new row to the Country table (Table 7.2.9) for 'UK', 'London'.

Updating data in a single table

e UPDATE statement is used to update an existing row of a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

20
For example,

20
UPDATE Ward
SET NurseInCharge = 'Mr Ali', NoOfBeds = 25
WHERE WardName = 'Victoria'; nd
Bo
Questions
R

10 Write the SQL statement to update the row of the Country table (Table 7.2.9) for 'UK' to add population
rK

64100000, area 243610. Assume that an insert statement has inserted 'UK', 'London' already as in Q9.
D
ht

SQL Tutorials
ig

SQL tutorials are available at [Link]

yr

It is also possible to explore SQL locally by first installing a database engine and then a tool which supports the
op

execution of SQL against a database accessed

C

through the database engine.

SQLite is a self-contained, server-less, zero
configuration, transactional SQL database engine.
e code for SQLite is public domain and is
thus free for use for any purpose, commercial or
private. It can be obtained from
[Link]
An easier route to using SQLite is to download
DB Browser for SQLite from
[Link] is application
takes care of the installation of both the SQLite
database engine and an interface for executing
SQL - see Figure 7.2.1.
Figure 7.2.1 DB Browser for SQLite
335
 7.2 Structured Query Language

After installing DB Browser for SQLite, launch the application. e user interface for DB Browser for SQLite is
shown in Figure 7.2.2.

20
20
Figure 7.2.2 DB Browser for SQLite user interface

nd
Download the [Link], [Link], [Link] and [Link] databases from
Bo
[Link]/aqacs/[Link].
Open [Link] database using the Open Database button. Figure 7.2.3 shows that the opened database
R

consists of two tables Patient and Ward.

rK

e data stored in the Ward table is revealed by executing the SQL query
D

SELECT * FROM Ward;

ht
ig

Executes all the

yr

SQL statements
op

in the SQL
window
C

Executes the SQL statement that starts in the current line

Figure 7.2.3 Execute SQL tab

336
7 Relational databases and structured query language (SQL)

Figure 7.2.4 shows the result of executing the SQL query

SELECT [Link], NurseInCharge, PatientId
FROM Ward, Patient
WHERE [Link] = [Link]
Cursor in this line
ORDER BY [Link] ASC; and execute current
line icon clicked
Tasks
1 Try all the SQL examples in this chapter in DB
Browser for SQLite.

20
20
nd
Bo
R

Figure 7.2.4 Querying Ward and Patient tables

rK

In this chapter you have covered:

■ How to use SQL to retrieve data from a relational database, using the commands
D

• SELECT
ht

• FROM
ig

• WHERE
yr

• ORDER BY...ASC | DESC

op

■ Using SQL to insert data into a relational database by using the command
C

INSERT INTO table_name

(column1, column2, ...)
VALUES (value1, value2, ...)

■ Using SQL to edit and delete data in a relational database by using the commands

UPDATE table_name
SET column1 = value1,
column2 = value2, ...
WHERE condition

DELETE FROM table_name

WHERE condition

337