Tutorial:Programming
How To Write SQL
We explain how to use Structured Query Language to interrogate databases and to construct
both simple and complex queries. This concludes our two part article.
By Pan Pantziarka
V
ery often you would not want
to note that the DELETE query does
SELECT LastName, EmpNo
to issue DELETE commands
not do anything to the structure of the
FROM UserTable
on a single table in isolation.
table itself, it deletes data only. To de
For example, if we delete one of the
lete a table, or part of a table, then you
If we wanted to look at the entire
software packages in the Software ta
have to use the DROP clause of an
table we could have used an asterisk as
ble we may be left with a record in the
ALTER TABLE query.
a wildcard for the entire table:
UserSoftware table which refers to it
(this record would be termed an or
Populating
SELECT *
phan record in database parlance).
FROM UserTable
Where such relationships between ta
Rather than list a long sequence of
bles occur then the delete operation
SQL commands to populate the tables
You might want to concatenate the
needs to delete records in both tables
used for this article, the data is listed in
LastName and FirstName fields to
at once.
Figures 11, 12, 13 and 14 and it will be
produce a single column with entries
Some database systems, including
referred to later on in this article. Note
such as Jones, Terry , Patel, Bharat
Access, allow relationships between
that only those fields which will be of
and so on. This too is possible using the
tables to be established including set
interest have been listed, for example,
simplest
of
SELECT
commands,
ting a cascading delete option,
the ScreenSize and DateOfPurchase
though we have to create an alias col
which means that deleting a record in
fields in the PC table have not been
umn to contain the result which in
one table will cause the deletion of
included.
this instance we'll call FullName. SQL
matching records in the linked tables.
can then use the AS keyword to assign
In such a case you need only issue the
Querying
the result of an expression to the alias.
DELETE (or UPDATE) command to
The full query would be SELECT Last
the primary table (in our case Soft
Having designed, built and popu
Name & , & FirstName AS Full
ware), and the deletion or change will
lated our database with some sample
Name FROM UserTable.
ripple through to the linked tables (the
data, we can now move on to what is
You can even add fixed text or ex
UserSoftware table in this example).
considered the heart of SQL the SE
pressions to SELECT queries to give
It is worth noting that the DELETE
LECT statement. This is the command
results which are immediately read
query will delete an entire record or
that queries the database and which
able, as shown in the query and results
group of records. If you want to delete
provides real value to any database.
listed in Figures 15 and 16.
a single field or group of fields without
Although the SELECT statement can
Now, consider what happens if we
destroying the record then you should
become extremely complex, the basic
issue a simple query such as:
use an UPDATE query and set the
syntax is relatively straightforward:
fields to Null to over write the data
SELECT LastName
you wish to delete. It is also important
SELECT columns
FROM UserTable
FROM table(s)
WHERE criteria
SQL will return a simple listing of
the last names in the UserTable, and,
So, to query the UserTable to pro
as you can see by looking at the data in
duce a list of surnames and employee
our table, it will contain duplicates as
numbers we could issue the command:
there are three Joneses listed in our
SELECT UserTable.LastName, UserTable.FirstName, UserTable.PCType, PC.Disk
FROM UserTable INNER JOIN PC ON UserTable.PCType = PC.PCTypeName
WHERE (((UserTable.Dept)="Development"));
Figure 17 The WHERE clause.
Figure 18 Select query with inner join.
Issue 109 (July/August 1999) page 19
File: T1125.7
PC Network Advisor
Next page >
New! The best sites for quality inkjet printer cartridges and the best sites for cheap inkjet cartridges