Data Manipulation

This section looks at the SQL DML statements, namely:
n SELECT – to query data in the database;
n INSERT – to insert data into a table;
n UPDATE – to update data in a table;
n DELETE – to delete data from a table.
Owing to the complexity of the SELECT statement and the relative simplicity of the other
DML statements, we devote most of this section to the SELECT statement and its various
formats. We begin by considering simple queries, and successively add more complexity
to show how more complicated queries that use sorting, grouping, aggregates, and also
queries on multiple tables can be generated. We end the chapter by considering the
INSERT, UPDATE, and DELETE statements.
We illustrate the SQL statements using the instance of the DreamHome case study
shown in Figure 3.3, which consists of the following tables:
Branch (branchNo, street, city, postcode)
Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo,
branchNo)
Client (clientNo, fName, lName, telNo, prefType, maxRent)
PrivateOwner (ownerNo, fName, lName, address, telNo)
Viewing (clientNo, propertyNo, viewDate, comment)
Literals
Before we discuss the SQL DML statements, it is necessary to understand the concept of
literals. Literals are constants that are used in SQL statements. There are different forms
of literals for every data type supported by SQL (see Section 6.1.1). However, for simplicity,
we can distinguish between literals that are enclosed in single quotes and those that
are not. All non-numeric data values must be enclosed in single quotes; all numeric data
values must not be enclosed in single quotes. For example, we could use literals to insert
INSERT INTO PropertyForRent(propertyNo, street, city, postcode, type, rooms, rent,
ownerNo, staffNo, branchNo)
VALUES (‘PA14’, ‘16 Holhead’, ‘Aberdeen’, ‘AB7 5SU’, ‘House’, 6, 650.00,
‘CO46’, ‘SA9’, ‘B007’);
The value in column rooms is an integer literal and the value in column rent is a decimal
number literal; they are not enclosed in single quotes. All other columns are character
strings and are enclosed in single quotes.