SQL is a non-procedural language that is, it allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.
The non-procedural nature of SQL is one of the principle characteristics of all 4GLs - Fourth Generation Languages - and contrasts with 3GLs (eg, C, Pascal, Modula-2, COBOL, etc) in which the user has to give particular attention to how data is to be accessed in terms of storage method, primary/secondary indices, end-of-file conditions, error conditions (eg ,Record NOT Found), and so on.
The DML component of SQL comprises four basic statements:
SELECT to retrieve rows from tables
UPDATE to modify the rows of tables
DELETE to remove rows from tables
INSERT to add new rows to tables.
Sections 4.1 through 4.5 illustrate with examples the data retrieval capabilities of the SELECT statement as well as introducing more general features of SQL such as arithmetic and logical expressions which are equally as applicable to the UPDATE, DELETE and INSERT statements which are dealt with separately in section 4.6.
Each section contains examples which illustrate the various options available with each statement. You are advised to try out these examples for yourself. In addition, at the end of each section there are a number of exercise queries designed to test your understanding of the section.
Before proceeding further you should now refer to the appendices which explain how to access to your local database facilities.
If you have any difficulties at this point ask your tutor for help.
4.1 Selecting Columns.
The simplest form of the SELECT statement consists of just two
clauses.
SELECT column-list
FROM table-list ;
4.1.1 Specifying Required Column List.
Example 4.1.1 - List full details of all Aircraft.
In order to list all the details (columns) recorded for each aircraft
type we will list the name of each column in the Aircraft table
as follows.
SELECT AircraftType, ADescription, NoSeats
FROM Aircraft ;
Result:
For those queries in which all columns of a particular table are
to be retrieved the column-list may be replaced by an asterisk.
Hence:
SELECT *
FROM Aircraft ;
gives the same result as before.
Example 4.1.2 - List the Names and Addresses of all Passengers.
This query requires the selection of individual columns from the
Passenger table and so we simply list the columns required as
follows:
SELECT Name, Address
FROM Passenger ;
Result:
4.1.2 Removing Duplicate Rows.
Example 4.1.3 - List all Flight No's with tickets issued
against them.
Flights for which tickets have been issued are recorded in the
Itinerary table. The following simple query lists Flight No's
from the Itinerary table.
SELECT FlightNo
FROM Itinerary ;
Result:
31 rows retrieved
However, a given FlightNo will appear as many times as the number
of tickets issued for that flight, therefore the result of this
query contains duplicate rows. To remove duplicates from the result
of this sort of query SQL provides the DISTINCT function which
is used as follows.
SELECT DISTINCT FlightNo
FROM Itinerary ;
Result:
23 rows retrieved
4.1.3 Arithmetic Expressions.
SQL allows arithmetic expressions to be included in the SELECT
clause. An arithmetic expression consists of a number of column
names and values connected by any of the following operators:
+ Add
- Subtract
* Multiply
/ Divide
When included in the SELECT clause the results of an expression
are displayed as a calculated table column.
Example 4.1.4 - Assuming Tariffs are recorded excluding
VAT, calculate and list Tariffs inclusive of VAT.
The are a number of different ways of calculating Fare prices
inclusive of VAT (17.5%). The following are all equivalent and
valid arithmetic expressions for calculating VAT inclusive values
within the SELECT clause.
i) Price + Price * .175
ii) Price + Price * 17.5 / 100
iii) Price * 1.175
SELECT RouteNo, FareType, Price, Price*1.175
FROM Tariff ;
Result:
See next page.
To obtain a column name for the result column enter
the following statement:
SELECT RouteNo, FareType, Price, Price*1.175 as vat_price
FROM Tariff ;
(The result-column name must not have embedded spaces and it does
not have quotes around it.)
Ingres applies standard operator precedence ie:
Multiply (*) and divide (/) are evaluated before plus (+) and
minus (-).
Multiply and divide are equal in precedence and plus and minus
are equal in precedence.
Operators of equal precedence are evaluated in order of appearance
from left to right. Hence:
6 + 4 / 2 ... evaluates to 8 (6+2) and not 5 (10/2).
The order of evaluation may be controlled by the use of brackets.
If in the above example, we had wanted the + operator to be evaluated
first then the following use of brackets would make the expression
(6 + 4) / 2 ... evaluates to 5.
4.1.4 Aggregate (Group) Functions.
SQL provides a number of special functions called aggregate functions
which may be included in the SELECT clause. Each of these functions
operates over a named column and returns (calculated from all
selected rows) a single value.
AVG(column-name) returns the average value
COUNT(column-name) returns the number of
non-null values
COUNT(DISTINCT column-name) returns the number of distinct
values
COUNT(*) returns the number of rows
MAX(column-name) returns highest value
MIN(column-name) returns the lowest value
SUM(column-name) calculates the total of values
Example 4.1.5 - List the average aircraft seating capacity.
SELECT AVG(NoSeats)
FROM Aircraft ;
Result:
Example 4.1.6 - Count the number of Routes flown by the
airline.
SELECT COUNT(*)
FROM Route ;
Result:
Aggregate functions return a single valued result, ie a calculated
column with only one row. As such these functions cannot be included
in the SELECT clause with columns which are multi-valued, ie resulting
in a two or more rows. On this basis the following query is
illegal and would not be processed by the DBMS.
SELECT AircraftType, AVG(NoSeats)
FROM Aircraft ;
Later on when we have extended our consideration of SELECT to
accommodate slightly more complicated queries we will see examples
of how to use aggregate functions with "group columns"
in the SELECT clause.
Exercise 1.
Give the SQL required to
1. List the full details of all Airports.
2. List the distinct AircraftTypes assigned to flights.
3. List all Tickets with TicketDate appearing in the left most
column.
4. From amongst all of the different aircraft types find the largest
seating capacity. (You are not asked to find which particular
aircraft type this seating capacity belongs to.)
5. Calculate how many different AircraftTypes are allocated to
timetabled flights by querying the flights table.
6. Format the query given in Example 4.1.4 so that the expression
price*1.175 is given a result-column name of vat_price
4.2 Selecting Rows.
In the previous section we were concerned only with listing one
or more columns from a table. In this type of query all rows of
the queried table are returned. More usually however, we will
only be interested in one or a small number of rows from a given
table, those which satisfy a particular condition, for example
if we wanted to find the number of seats on a McDonnell Douglas
DC9 Jet then only the one row in the Aircraft table which records
this type of aircraft would be of interest.
In order to select specific rows from a table we use the WHERE
clause which is placed after the FROM as follows.
SELECT column-list
FROM table-list
WHERE conditional-expression ;
4.2.1 Specifying Selection Conditions.
When you use a WHERE clause, you are specifying a condition for
SELECT to test when processing each row of the table. Only those
rows which test True against the condition are listed in the result.
Example 4.2.1 - Find the Seating Capacity on a DC9.
SELECT NoSeats
FROM Aircraft
WHERE AircaftType = 'DC9' ;
Result:
This illustrates the most commonly used form of the WHERE clause
in which the expressions consists of three elements:
Comparison Operators.
Any of the following comparison operators may be used:
= equal to
<> (!=) not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
A number of special comparison operators are also provide:
BETWEEN Compares a range of values.
IN Tests against values in a list.
ANY Used in conjunction with comparison
ALL operators to test against values in a list.
LIKE Compares a character pattern.
Constants.
Where a constant contains one or more alphabetic characters it
is distinguished from a column-name by placing the constant between
apostrophes.
List of Values.
See section 4.2.3 Matching a Value in a List.
Example 4.2.2 - List Descriptions of Aircraft with more
than 50 seats.
SELECT ADescription, NoSeats
FROM Aircraft
WHERE NoSeats > 50 ;
Result:
Expressions formed from these comparison operators are known as
logical expressions because they evaluate to one of two possible
logic states True or False.
In the above example, if a given row has a value in the Noseats
column which is greater than 50 then the expression will evaluate
to true, if the value is less than or equal to 50 the expression
will then evaluate to false. Only rows which cause the WHERE clause
expression to evaluate to true are listed in the result.
4.2.2 Selecting Rows From a Range.
The BETWEEN operator provides a convenient way of selecting rows
with a column value which is within a specified range.
Example 4.2.3 - List the names and addresses of passengers
with Pid's in the range 25 to 35 inclusive.
SELECT Pid, Name, Address
FROM Passenger
WHERE Pid BETWEEN 25 AND 35 ;
Result:
If required the NOT operator may be used to negate the result
of the BETWEEN comparison. Use of the NOT operator in this case
would specify passengers whose Pid's are less than 25 or greater
than 35, ie
SELECT Pid, Name, Address
FROM Passenger
WHERE Pid NOT BETWEEN 25 AND 35 ;
Result:
4.2.3 Matching a Value in a List.
The IN operator permits the selection of rows with a column value
which matches any value from a set of values.
Example 4.2.4 - Find the Tickets (No's) issued on Flight
Nos BD54, BD80, BD412, BD582, or BD332.
SELECT TicketNo, FlightNo
FROM Itinerary
WHERE FlightNo IN ('BD54','BD80','BD412', 'BD582', 'BD332') ;
Result:
(See the equivalent boolean expression in Example 4.2.8)
The NOT operator may be used to negate this result, ie
SELECT TicketNo, FlightNo
FROM Itinerary
WHERE FlightNo NOT IN ('BD54','BD80','BD412', 'BD582','BD332'
);
Result:
Note equivalencies between the IN and ANY operators:
= ANY (...) = IN (...)
!= ANY (...) = NOT IN (...)
4.2.4 Matching a Character Pattern.
The comparison operators = and IN match exact values, however
there are occasions when an exact value for comparison is not
known or where or a partial match only is all that is required.
In these situations the LIKE operator allows rows to be selected
that partially match a given pattern of characters.
The LIKE operator recognises two special character symbols
% which represents any sequence of zero or more characters
_ (underscore) which represents any single character.
Example 4.2.5 List the description of all Routes using Birmingham.
For this query we are interested in any Route which includes the
character constant 'Birmingham' anywhere within its description.
SELECT *
FROM Route
WHERE RDescription LIKE '%Birmingham%' ;
Result:
Example 4.2.6 - Find the telephone numbers of all passengers
with a surname of either 'Miller' or 'Millar'.
SELECT Name, TelNo
FROM Passenger
WHERE Name LIKE '%Mill_r' ;
Result:
4.2.5 Compound Expressions with Boolean Operators.
So far we have only considered the WHERE clause composed of single
logical expressions. There are many occasions however when selections
need to be based on several conditions. There may be a number
of alternative conditions by which rows can be selected or there
may be a number of conditions which must be satisfied by all selected
rows.
Individual logical expressions may be combined within the WHERE
clause through the use of the two Boolean operators:
AND
OR
Expressions are then combined as follows:
WHERE logical-exp AND logical-exp AND ...
logical-exp OR logical-exp ...
Example 4.2.7 - List the names and addresses of passengers
with Pid's in the range 25 to 35 inclusive.
SELECT Pid, Name, Address
FROM Passenger
WHERE Pid >= 25 AND Pid <= 35 ;
Result:
Logical Operator Precedence.
Comparison operators (=, <, IN, LIKE, etc) are evaluated first,
then the AND operators then the OR operators. As with arithmetic
operators brackets may be used to change the order of evaluation.
Contents of brackets being evaluated first.
If we take the query from Example 4.2.7 and consider three rows
from the Passenger table with values in the Pid column of 36,
24 and 30 respectively, then given the above precedence rules,
they would be evaluated as summarised in the table below.
Pid Pid >=25 Pid <= 35 AND -- Result --
36 true false false not selected
24 false true false not selected
30 true true true selected
As can be observed, for the AND operator to evaluate to true both
of the tested conditions must also be true.
The action of the AND and OR operators are summarised in the following
tables in which 1 represents true and 0 represents false.
AND-Operator OR-Operator
0 AND 0 = 0 0 OR 0 = 0
1 AND 0 = 0 1 OR 0 = 1
0 AND 1 = 0 0 OR 1 = 1
1 AND 1 = 1 1 OR 1 = 1
Example 4.2.8 - Find the Tickets (No's) issued on Flights
BD54, BD80, BD412, BD582, or BD332.
In Example 4.2.4 we used the IN operator for this query; this
is the equivalent SQL using the OR operator.
SELECT TicketNo, FlightNo
FROM Itinerary
WHERE FlightNo = 'BD54'
OR FlightNo = 'BD80'
OR FlightNo = 'BD412'
OR FlightNo = 'BD582'
OR FlightNo = 'BD332' ;
Result:
The WHERE clause may be contain a combination of different types
of expressions as illustrated in the following example.
Example 4.2.9 - List the names and addresses of passengers
who either have Pid's in the range 25 to 35 inclusive or have
a surname of 'Smith'.
SELECT Pid, Name, Address
FROM Passenger
WHERE Pid >= 25 AND Pid <= 35
OR Name LIKE '%Smith' ;
Result
4.2.6 Arithmetic Expressions.
SQL allows arithmetic expressions to be included as part of the
WHERE clause.
Example 4.2.10 - List the Tariffs available on Route No
9 (Birmingham-Brussels) which when VAT (@17.5%) is added cost
more than $170.00
SELECT FareType, Price, Price*1.175
FROM Tariff
WHERE RouteNo = 9
AND (Price*1.175) > 170 ;
Result:
Exercise 2.
Give the SQL required to:
1. List the FlightNo's on the itinerary for Ticket 100030.
2. Calculate the total ticket price for Ticket 100030 given the
Tariff for each flight is $50.00. Assign a suitable title to your
result-column.
3. List full details of flights where the allocated aircraft is
either a DC9 or a 737.
4. List full details of all flights from HROW (Heathrow), EMID
(East Midlands) or BIRM (Birmingham) where the allocated aircraft
is an DC9.
5. List details of aircraft with seat capacities between 50 and
120.
6. List the FlightNo's and service details of flights from HROW
(Heathrow) to BIRM (Birmingham) with departure times after 07:00
and before 15:00.
7. List the names and addresses of all passengers whose names
start with an initial of 'A' and have surnames of either 'Smith'
or 'Smithson'.
4.3 Ordering the Rows of the Result.
The relational database model places no significance on the order
in which rows appear in a table. As such the order in which rows
will appear in a given query result should be considered to be
indeterminate. Where a particular order is a required the ORDER
BY clause must be used.
SELECT column-list
FROM table-list
WHERE conditional-expression
ORDER BY order-columns ;
When used the ORDER BY clause must appear as the last clause in
the SELECT statement.
The column or columns on which the result is to be ordered must
appear in the column-list of the SELECT clause.
Columns in the ORDER BY clause may be of type numeric, character
or date.
4.3.1 Ordering on a Single Column.
Example 4.3.1 - List in descending order of FlightNo
the Tickets (No's) and FlightNo's for Flights BD54, BD80, BD412,
BD582, or BD332.
SELECT TicketNo, FlightNo, FlightDate
FROM Itinerary
WHERE FlightNo IN ('BD54','BD80','BD412', 'BD582', 'BD332')
ORDER BY FlightDate DESC ;
Result:
4.3.2 Ordering on Multiple Columns.
As well as being able to order the results of a query by a single
column we can specify a list of columns on which we wish to order
the result. The first column on the list provides the primary
order, the second column is the ordered within the first and so
on.
Example 4.3.2 - List, in ascending order of FlightDate within
TicketNo - TicketNos, FlightNo's and FlightDates for TicketNos
in the range 100010 to 100020.
SQL> SELECT TicketNo, FlightNo, FlightDate
2 FROM Itinerary
3 WHERE TicketNo BETWEEN 100010 AND 100020
4 ORDER BY TicketNo, FlightDate ;
Result:
Example 4.3.3 - List in descending order of FlightDate within
ascending TicketNo, TicketNo's, FLightNo's and their FlightDates
for Ticket Nos in the range 100010 to 100020.
SELECT TicketNo, FlightNo, FlightDate
FROM Itinerary
WHERE TicketNo BETWEEN 100010 AND 100020
ORDER BY TicketNo, FlightDate DESC ;
Result:
4.3.3 Ordering on Calculated (Result) Columns.
In addition to being able to order the result of a query by a
given table column it is also possible to order by the values
of an expression which appears within the SELECT clause, ie by
a calculated column.
Example 4.3.4 - List the Tariffs available on Route No 9
(Birmingham-Brussels) which when VAT (17.5%) is added cost more
than $170.00.
If we wished to Order the result of this query in descending order
of the calculated VAT inclusive column the SQL would appear as
follows.
SELECT FareType, Price*1.175 As VatPrice
FROM Tariff
WHERE RouteNo = 9
AND (Price*1.175) > 170
ORDER BY VatPrice DESC;
Result:
Exercise 3.
Give the SQL required to:
1. Modify the query given for Example 4.3.1 so that the result
is listed in ascending order by FlightNo.
2. List full details of flights from BIRM (Birmingham) to BRUS
(Brussels) in descending order by DepTime.
3. List full details of all flights in ascending order of Service
within descending order of Deptime.
4.4 Grouping the Rows of the Result.
We noted in section 4.1.4 that an aggregate (group) column could
not be mixed in the SELECT clause with multi-valued columns. There
are however, occasions when it is useful to be able to list several
groups within a single query. To enable this the SELECT statement
has an optional GROUP BY clause which is included immediately
after the WHERE clause (or after the FROM clause if the WHERE
clause is not present).
SELECT column-list
FROM table-list
WHERE conditional-expression
GROUP BY group-columns ;
4.4.1 Using the GROUP BY Clause.
Example 4.4.1 - Count the number of flights on the Itineraries
of TicketNo's 100010 to100020 inclusive.
SELECT TicketNo, COUNT(FlightNo)
FROM Itinerary
WHERE TicketNo BETWEEN 100010 AND 100020
GROUP BY TicketNo ;
Result:
4.4.2 Using the HAVING Clause.
In the same way that you can select specific rows through the
use of the WHERE clause, you can also
select specific groups with the HAVING clause.
The HAVING clause compares some property of the group with a constant
value. If a group fulfils the condition of the logical expression
in the HAVING clause it is included in the result.
SELECT column-list
FROM table-list
WHERE conditional-expression
GROUP BY group-columns
HAVING group-conditional-expression ;
Example 4.4.2 - List TicketNo's with itineraries of 4 or
more flights.
SELECT TicketNo, COUNT(FlightNo)
FROM Itinerary
GROUP BY TicketNo
HAVING COUNT(FlightNo) >= 4 ;
Result:
Exercise 4.
Give the SQL required to:
1. The SQL given in Example 4.4.2 counts the FlightNo column.
However, as there is one row for each flight in a given Ticket's
itinerary it is not necessary for the SQL to specify any particular
column. Modify the SQL given to reflect this fact.
2. List Routes (RouteNo) with less than 4 timetabled flights.
3. List the most popular FareType based on currently recorded
Itineraries. Your query should list each FareType and its number
of occurrences.
4.5 Joining Tables.
All of the queries considered up to this point have been based
on single tables, ie resulting in one or more columns selected
from a single table named within the FROM clause.
Where queries are to be based on the columns of two or more tables
the required results are obtained by joining these tables together.
The joined tables are specified in the FROM clause, and are "linked"
to each other by one or more common columns; ie columns containing
a range of values which each table has in common. The WHERE clause
is then be used to specify the conditions between these common
columns on which the required rows will be selected.
4.5.1 Equi-Joins.
Example 4.5.1 - Find the seating capacity of the aircraft
allocated to flight BD80.
The type of aircraft (AircraftType) allocated to a particular
flight (FlightNo) is recorded in the Flight table whilst the seating
capacity for each type of aircraft is recorded in the Aircraft
table.
Inspection of the Aircraft and Flight tables reveals that they
are related to each other via the common column, AircraftType.
Hence we can join the Aircraft table and the Flight table, selecting
rows, where FlightNo in the Flight table is BD80 and AircraftType
in the Flight table is equal to AircraftType in Aircraft table.
The following SQL illustrates the required join; known as an equi-join
because the comparison operator in the join condition is = (equals).
SELECT NoSeats
FROM Aircraft, Flight
WHERE FlightNo = 'BD80'
AND Aircraft.AircraftType = Flight.AircraftType ;
Result:
We are not restricted to joins involving only two tables here
is an example of a query which needs to join three tables to obtain
the required result.
Example 4.5.2 - List the description and seating capacity
of the aircraft allocated to the flights on TicketNo 100010.
SELECT ADescription, NoSeats
FROM Aircraft, Flight, Itinerary
WHERE TicketNo = 100010
AND Itinerary.FlightNo = Flight.FlightNo
AND Flight.AircraftType = Aircraft.AircraftType;
Result:
4.5.2 Table References and Table Aliases (Correlation Names).
When joined tables have a common column-name you must make it
clear which table is being referenced by prefixing the column
with its table-name. The column NoSeats in the previous examples
is not prefixed because this column-name appears only in the Aircraft
table and is therefore unambiguous.
The need to use a table-name prefix leads to fairly long column-name
references. To avoid this, tables may be allocated an abbreviated
name (an alias) within the FROM clause by which the table will
be referenced elsewhere within the SELECT statement. The FROM
clause then has the following general form
... FROM table-name alias, ... , table-name alias,
...
where the alias appears immediately after the table-name (separated
by a space).
Using table aliases the previous example could be rewritten as:-
SELECT ADescription, NoSeats
FROM Aircraft A, Flight F, Itinerary I
WHERE TicketNo = 100010
AND I.FlightNo = F.FlightNo
AND F.AircraftType = A.AircraftType;
Result:
4.5.3 Non-Equi-Joins.
Example 4.5.3 - A passenger requests details of alternative
flights departing earlier than his/her currently booked Flight
(BD659 18:25 Birmingham->Brussels).
The objective of this query is to find alternative flights which
serve the same route as that served by BD659 with the same departure
airport but with earlier departure times.
The conditional expression for the join used in this query uses
the inequality operator > {greater than} and therefore represents
a non-equi-join.
SELECT E.FromAirport, E.FlightNo, E.DepTime
FROM Flight F, Flight E
WHERE F.FlightNo = 'BD659'
AND F.FromAirport = E.FromAirport
AND F.RouteNo = E.RouteNo
AND F.DepTime > E.DepTime ;
Result:
4.5.4 Joining A Table to Itself.
Table aliases used with example 4.5.2 to abbreviate table-names
also allow you to give a table a double alias thereby allowing
you to join a table to itself as though it were two separate tables.
This is a very useful facility where a query needs to compare
rows within the same table. Example 4.5.3, as well as illustrating
a non-equi-join also makes use of a self join with two aliases
of the Flight table. This allows alternative flights to be found
which serve the same route as the given (currently booked) flight.
Here is another example of the use of table aliases to enable
a "self join".
Example 4.5.4 - To fly from Heathrow (HROW) to Brussels
(BRUS) passengers must undertake their journey in two stages.
List the FlightNo's, Airport's, Departure and Arrival times for
inter-connecting flights from Heathrow to Brussels.
SELECT DISTINCT B.FlightNo, B.FromAirport,B.DepTime, B.ArrTime
FROM Flight A, Flight B
WHERE A.FromAirport = 'HROW'
AND A.ToAirport = B.FromAirport
AND B.ToAirport = 'BRUS';
Result:
Exercise 5.
Give the SQL required to:
1. List the full name of the airport from which flight BD275 departs.
2. List the full description and seating capacity of the aircraft
allocated to flight BD582.
3. List the description and seating capacity of all the aircraft
allocated to flights on the Itineraries of Tickets issue to 'R
H Miller'.
4. List the FareTypes and Descriptions of the Fares available
on RouteNo 4 (Heathrow - Edinburgh).
5. Calculate the total ticket price for Ticket number 100010 using
Price from the Tariff table.
6. List the full name of the airport from which flight BD257 departs
and the full name of the airport at which flight BD257 arrives.
(Hint each flight record refers to two Airport records therefore
the Airport table should appear twice in the FROM clause. Use
aliases to differentiate the first Airport reference from the
second.)
7. List for the flights on all East-Midlands Routes the FlightNo,
service provided, aircraft used, and seatingcapacity.
( Hint use LIKE to obtain RouteNo's and join Route and Flight
tables on RouteNo. How are you going to find the seating capacity
for each flight?)
4.6 Modifying the Contents of Database Tables.
SQL provides three statements with which you can modify the contents
of database tables.
UPDATE which modifies data in existing rows of a table.
INSERT which inserts new rows into a table.
DELETE which deletes existing rows from a table.
If you only have SELECT privileges granted on the tables of
the "airline" database the DBMS will prevent you from
executing any of the UPDATE, INSERT, or DELETE examples given
in this section.
To give you practice in the use of these statements, examples
and exercises are provided at the end of Section 5 which will
give you an opportunity to define and manipulate your own tables.
As owner, of these tables you will automatically have select,
insert, update and delete privileges.
4.6.1 Updating Rows.
The UPDATE statement consists of three clauses
UPDATE tablename
SET column-assignment-list
WHERE conditional-expression ;
where the column-assignment-list lists the columns to be
updated and the values they are to be set to and takes the general
form:
column-name = value, column-name = value, ...
where value may either be a constant or a column-expression which
returns a value of the same type as column-name.
The WHERE clause is optional. When used, the WHERE clause specifies
a condition for UPDATE to test when processing each row of the
table. Only those rows which test True against the condition are
updated with the new values.
Example 4.6.1 - Increase the price of the Standby Single
(STS) Tariff on Route 13 (East Midlands-Paris) by $5.00.
UPDATE Tariff
SET Price = Price + 5
WHERE RouteNo = 13
AND FareType = 'STS' ;
Without the WHERE clause the UPDATE statement updates all rows
of the table.
Example 4.6.2 - Increase the price of Tariffs on all Routes
by 10 percent.
UPDATE Tariff
SET Price = Price * 1.1 ;
4.6.2 Inserting Rows.
The INSERT statement has two distinct variations the first, and
simplest, inserts a single row into the named table.
INSERT INTO tablename [( column-list )]
VALUES ( constant-list ) ;
Example 4.6.3 - Let us assume that two new types of aircraft
are to be brought into service by the airline company; the Shorts-360
and Fokker-Friendship F24.
The type code (AircraftType) for these aircraft will be 'S60'
and 'F24' respectively. The 'S60' has a seating capacity of 36
and the 'F24' has a seating capacity of 48.
INSERT INTO Aircraft
VALUES ('S60', 'Shorts-360', 36);
INSERT INTO Aircraft
VALUES ('F24', 'Fokker-Friendship', 48);
(Note if the input values match the order and number of columns
in the table then column-list can be omitted.)
4.6.3 Inserting Rows Copied from Another Table.
The INSERT statement may also be used in conjunction with a SELECT
statement query to copy the rows of one table to another.
The general form of this variation of the INSERT statement is
as follows:
INSERT INTO tablename [( column-list )]
SELECT column-list
FROM table-list
WHERE conditional-expression ;
where the SELECT statement replaces the VALUES clause.
Only the specified columns of those rows selected by the query
are inserted into the named table.
The columns of the table being copied-from and those of the table
being copied-to must be type compatible.
If the columns of both tables match in type and order then the
column-list may be omitted from the INSERT clause.
Example 4.6.4 - Copy Passenger records with Pids in the
range 91 - 94 to an archive table APassenger.
INSERT INTO APassenger
SELECT *
FROM Passenger
WHERE Pid BETWEEN 91 AND 94 ;
(Note the APassenger table must be in existence at the time this
statement is executed.)
4.6.4 Deleting Rows.
The general form of the DELETE statement is
DELETE FROM tablename
WHERE conditional-expression
The DELETE statement removes those rows from a given table that
satisfy the condition specified in the WHERE clause.
Example 4.6.5 - Delete rows from the Passenger table for
Pid's 91, 92, 93, and 94.
DELETE
FROM Passenger
WHERE Pid IN (91, 92, 93, 94) ;
Example 4.6.6 - Remove all Archived passengers records from
the 'live' Passenger table.
This example of DELETE employs a sub-query as a part of the WHERE
clause to compile a list of Pid's from the "archived passenger
table", APassenger.
Where rows in the Passenger table are found with a Pid value which
matches a value in the list they are removed by the DELETE statement.
(See section 6.1 on sub-queries later on.)
DELETE
FROM Passenger
WHERE Pid IN
( SELECT Pid
FROM APassenger ) ;
The WHERE clause is optional, when omitted all rows of the named
table are removed. For example
DELETE
FROM Aircraft ;
would delete all rows from the aircraft table.
4.6.5 Using Rollback, Savepoint and Commit.
Table updates, deletions and insertions are not made permanent
until you end your session and exit from the DBMS. Whilst your
database tables are in this uncommitted state you can see the
changes you have made as if they were permanent but other users
who may have privileges to access your tables cannot. Further,
at any time during the period that your tables are in an uncommitted
state you may reinstate the changed tables to their state prior
to those changes by using the rollback statement which
is simply entered as:
rollback ;
In addition it is possible to limit how much of your work Ingres
will rollback by issuing savepoints between the consecutive
SQL statements of a given transaction. Savepoints are issued with
a name (which may begin with a number) which is then used
in conjunction with rollback to identify the point in the transaction
to rollback to.
(A transaction commences on the execution of your first SQL statement
and includes all subsequent SQL statements issued up to the point
where you issue a commit or at the point where you end
your session.) So for example:
INSERT INTO Aircraft
VALUES ('S60', 'Shorts-360', 36);
savepoint 1;
INSERT INTO Aircraft
VALUES ('F24', 'Fokker-Friendship', 48);
rollback 1; /*undoes the last insert only*/
rollback; /*undoes the whole transaction*/
At any time during an SQL session your uncommitted database changes
may be committed to the database by issuing the commit
statement as follows:
commit ;
Having committed your pending database changes all users
will then be able to see those changes.
Committed changes cannot be undone.
aircra adescription noseat
DC9 Advanced Turbo Prop 48
ATP McDonnel Douglas Jet 120
737 Boeing 737-300 Jet 300
name address
J Millar Englewood Cliffs
J D Ullman 1 Microsoft Way
A Smithson 16 Bedford St
D Etheridge 4 Maylands Avenue
E Simon 8 Cherry Street
D N Hamer 1 St Paul's
D E Avison Churchyard
G B Davis 5 Chancery Lane
C Evans 25 Allenby Road
A N Smith 63 Kew Green
T Pittman 81 Digby Crescent
J Peters The Little House
K E Kendall 31 Lucas Road
R H Miller 11 Rosedale Avenue
155 Kingston Road
flight
BD80
BD95
BD80
BD95
BD82
BD54
BD776
BD655
BD652
BD775
BD772
BD655
BD652
BD412
BD419
BD412
BD419
BD224
BD255
BD256
BD275
BD412
BD582
BD589
BD332
BD51
BD774
BD659
BD658
BD771
BD54
flight
BD224
BD225
BD256
BD275
BD332
BD412
BD419
BD51
BD54
BD582
BD589
BD652
BD655
BD658
BD659
BD771
BD772
BD774
BD775
BD776
BD80
BD82
BD95
routen farety price col4
3 BUR $117.00 $137.48
3 SDR $158.00 $185.65
3 SDS $79.00 $92.83
4 SDR $162.00 $190.35
4 SBS $49.00 $57.58
6 BUR $117.00 $137.48
6 SBS $42.00 $49.35
6 KFS $53.00 $62.28
7 SDR $128.00 $150.40
8 SDS $74.00 $86.95
9 PXR $153.00 $179.78
9 EUR $181.00 $212.68
9 APR $95.00 $111.63
11 KFS $59.00 $69.33
13 EXR $121.00 $142.18
14 SDR $110.00 $129.25
14 SBS $33.00 $38.78
15 SBS $33.00 $38.78
col1
156.00
col1
10
noseat
120
adescription noseat
McDonnel Douglas Jet 120
Boeing 737-300 Jet 300
pid name address
26 J Millar Englewood Cliffs
28 J D Ullman 1 Microsoft Way
29 A Smithson 16 Bedford St
30 D Etheridge 4 Maylands Avenue
34 E Simon 8 Cherry Street
pid name address
10 D N Hamer 1 St Paul's
20 D E Avison Churchyard
21 G B Davis 5 Chancery Lane
24 C Evans 25 Allenby Road
90 A N Smith 63 Kew Green
91 T Pittman 81 Digby Crescent
92 J Peters The Little House
93 K E Kendall 31 Lucas Road
94 R H Miller 11 Rosedale Avenue
155 Kingston Road
ticketno flight
100001 BD80
100002 BD80
100011 BD54
100021 BD412
100022 BD412
100041 BD412
100051 BD582
100052 BD332
100100 BD54
ticketno flight
100001 BD95
100002 BD95
100010 BD82
100012 BD776
100012 BD655
100012 BD652
100012 BD775
100020 BD772
100020 BD655
100020 BD652
100021 BD419
100022 BD419
100030 BD224
100030 BD255
100030 BD256
100030 BD275
100051 BD589
100100 BD51
100100 BD774
100100 BD659
100100 BD658
100100 BD771
routeno rdescription
9 Birmingham-Brussels
14 Heathrow-Birmingham
name telno
J Millar 061 343 881
R H Miller 0638 4672
pid name address
26 J Millar Englewood Cliffs
28 J D Ullman 1 Microsoft Way
29 A Smithson 16 Bedford St
30 D Etheridge 4 Maylands Avenue
34 E Simon 8 Cherry Street
ticketno flight
100001 BD80
100002 BD80
100011 BD54
100021 BD412
100022 BD412
100041 BD412
100051 BD582
100052 BD332
100100 BD54
pid name address
26 J Millar Englewood Cliffs
28 J D Ullman 1 Microsoft Way
29 A Smithson 16 Bedford St
30 D Etheridge 4 Maylands Avenue
34 E Simon 8 Cherry Street
90 A N Smith 81 Digby Crescent
faretype price col3
PXR $153.00 $179.78
EUR $181.00 $212.68
ticketno flight flightdate
100100 BD54 02/10/94
100052 BD332 09/09/94
100051 BD582 07/09/94
100011 BD54 12/08/94
100022 BD412 07/08/94
100001 BD80 05/08/94
100002 BD80 05/08/94
100021 BD412 02/08/94
100041 BD412 02/08/94
ticketno flight flightdate
100010 BD82 10/08/94
100011 BD54 12/08/94
100012 BD655 15/08/94
100012 BD776 15/08/94
100012 BD652 20/08/94
100012 BD775 20/08/94
100020 BD655 20/08/94
100020 BD772 20/08/94
100020 BD652 23/08/94
ticketno flight flightdate
100010 BD82 10/08/94
100011 BD54 12/08/94
100012 BD652 20/08/94
100012 BD775 20/08/94
100012 BD655 15/08/94
100012 BD776 15/08/94
100020 BD652 23/08/94
100020 BD655 20/08/94
100020 BD772 20/08/94
faretype vatprice
EUR $212.68 PXR $179.78
ticketno col2
100010 1
100011 1
100012 4
100020 3
ticketno col2
100012 4
100030 4
100100 6
noseat
300
adescription noseat
Boeing 737-300 300
Jet
adescription noseat
Boeing 737-300 300
Jet
fromai flight deptime
BIRM BD651 07:30
BIRM BD655 15:00
BIRM BD657 17:30
flight fromai deptime arrtime
BD651 BIRM 07:30 09:35
BD655 BIRM 15:00 17:05
BD657 BIRM 17:30 19:35
BD659 BIRM 18:25 20:30
Return to Contents
Go on to Chapter 5 SQL Data Definition Language
© Copyright 1997 CHL. All rights reserved.