1 Graphical User Interface Alternatives and SQL
Using SQL for querying is
important—even for the beginner—because of SQL's power and flexibility.
Using SQL, it is also possible to create tables, insert data into them,
update data in them, and delete data from them. These operations can be
carried out through the graphical user interface (GUI) of systems such
as Microsoft Access. For the beginner, using the graphical user
interface for these purposes is intuitive and straightforward. In other
words, for simple tasks, using a GUI is easier than using SQL. This book
does not cover any of the details specific to Microsoft Access. If the
reader wants to experiment with the GUI in Access, Microsoft's Help and
other resources are available, and it is not difficult to become
familiar with the GUI.
This book also does not cover
all of the aspects of SQL. Nevertheless, it strives to be complete in
the following sense: If you want to create and use the example database
using only SQL, everything necessary to do so is explained. You can
download the complete database from the Jones and Bartlett web page for
this book. Alternatively, you could create the database from scratch.
How to do so using SQL is explained in the following sections.
2 The Keywords CREATE TABLE
Schemas were given that defined the tables in the example database. The SQL keywords CREATE TABLE,
when combined with such a definition, form a special kind of query that
can be run just like the queries of the previous chapters. Such a query
creates a table rather than retrieving data from a table.
Example Queries 1 through 5 contain the CREATE TABLE
statement for each of the tables in the example database. These queries
include the SQL syntax for specifying primary and foreign keys.
Example Query 1
CREATE TABLE Actor
(actorID TEXT(5),
lastName TEXT(24),
firstName TEXT(24),
middleName TEXT(24),
suffix TEXT(6),
gender TEXT(1),
birthDate DATE,
deathDate DATE,
CONSTRAINT ActorPK PRIMARY KEY (actorID));
Example Query 2
CREATE TABLE Movie
(movieID TEXT(3),
title TEXT(36),
year NUMBER,
company TEXT(50),
totalNoms NUMBER,
awardsWon NUMBER,
DVDPrice CURRENCY,
discountPrice CURRENCY,
CONSTRAINT MoviePK PRIMARY KEY (movieID));
Example Query 3
CREATE TABLE Quote
(quoteID TEXT(4),
quoteText TEXT(255),
CONSTRAINT QuotePK PRIMARY KEY (quoteID));
Example Query 4
CREATE TABLE Role
(roleID TEXT(5),
roleName TEXT(36),
gender TEXT(1),
actorID TEXT(5),
movieID TEXT(3),
CONSTRAINT RolePK PRIMARY KEY (roleID),
CONSTRAINT ActorFK FOREIGN KEY (actorID) REFERENCES Actor
(actorID),
CONSTRAINT MovieFK FOREIGN KEY (movieID) REFERENCES Movie
(movieID));
Example Query 5
CREATE TABLE RoleQuote
(roleID TEXT(5),
quoteID TEXT(4),
CONSTRAINT RoleQuotePK PRIMARY KEY (roleID, quoteID),
CONSTRAINT RoleFK FOREIGN KEY (roleID) REFERENCES Role
(roleID),
CONSTRAINT QuoteFK FOREIGN KEY (quoteID) REFERENCES Quote
(quoteID));
3 Constraints and the Keywords CONSTRAINT, PRIMARY KEY, FOREIGN KEY, and REFERENCES
Specifying keys in
tables imposes constraints on which data the key columns can contain. In
SQL, keys can be defined as instances of constraints on the table. The
last line of the definition of the Actor table in Example Query 6-1 is
representative of how a primary key can be defined:
CONSTRAINT ActorPK PRIMARY KEY (actorID)
To specify a primary key, you give the keyword CONSTRAINT,
followed by a name for the constraint. The constraint is named so that
it can be referred to later and deleted and replaced if necessary. The
name is followed by the keywords PRIMARY KEY. Finally, the column that will serve as the primary key is given inside parentheses.
The fourth line in the
RoleQuote table definition in Example Query 6-5 shows how a concatenated
primary key—a key based on more than one column—can be defined. This
line is similar in form to the definition of a one-column primary key.
The difference is that two primary key columns are included inside the
parentheses, separated by commas:
CONSTRAINT RoleQuotePK PRIMARY KEY (roleID, quoteID)
The fifth line of the RoleQuote table definition in Example Query 6-5 is representative of how a foreign key can be defined:
CONSTRAINT RoleFK FOREIGN KEY (roleID) REFERENCES Role
(roleID)
To specify a foreign key, you use the keyword CONSTRAINT,
followed by a name for the constraint. The constraint is named so that
it can be referred to later and deleted and replaced if necessary. It is
followed by the keywords FOREIGN KEY.
The column in the RoleQuote table that is the foreign key column,
roleID, is given in parentheses; it is followed by the keyword REFERENCES.
Next, the name of the corresponding primary key table, Role, is given.
The primary key column of that table, which also happens to be named
roleID, is given in parentheses. The RoleQuote table definition
illustrates the fact that a column may be both a foreign key and part of
the primary key at the same time.
Unfortunately, although the Microsoft Access documentation indicates that the FOREIGN KEY
definition syntax works, depending on your installation and version,
you may find that when you try to execute these statements you get an
error message. If that is the case, then you must remove the foreign key
constraints from the commands before running them successfully. If you
were creating a database of your own that required foreign key
constraints, you could still add them through the graphical user
interface, even if it was not possible to do so using SQL.
4 The Keywords INSERT INTO, VALUES, UPDATE, SET, and DELETE FROM
Once a table has been created,
you can insert data into it using SQL. Example Query 6 is a
representative command that would enter suitable data into the Actor
table. In this insertion query, the keywords INSERT INTO are followed by the name of the table, Actor. This name is followed by the keyword VALUES
and a pair of parentheses. Inside the parentheses, the data values for a
row in the table are given in the same order as the columns of the
table. Text values are surrounded by single quotes, and date values are
enclosed by a pair of # symbols. If there is no value for a column, the keyword NULL is used.
Example Query 6
INSERT INTO Actor VALUES('00001', 'Hanks', 'Tom', NULL, NULL,
'M', #7/9/1956#, NULL);
It is also possible to change
the data in a row using an SQL query. Example Query 7 would change
Tom Hanks' last name in the Actor table. In this update query, the
keyword UPDATE is followed by the name of the table, and the keyword SET
is followed by the name of the column to change and the new value. The
rest of the query uses syntax that you have seen before. The WHERE
keyword allows you to specify which row to change. This very powerful
syntax makes it possible to change more than one row at a time. You need
to be careful if you use it, however: Once the change has been made, it
can't be undone.
Example Query 7
UPDATE Actor
SET lastName = 'Hunks'
WHERE lastName = 'Hanks';
It is also possible to delete
a row from a table using an SQL query. Example Query 8 would delete
the row for Tom Hanks from the Actor table. In this deletion query, the
keywords DELETE and FROM are followed by the name of the table to change. The WHERE
keyword allows you to specify which row to delete. This very powerful
syntax makes it possible to delete more than one row at a time. You need
to be careful if you use it, however: Once the deletion has been made,
it can't be undone.
If you want to update or
delete many records at a time, then SQL is much easier to use than a
graphical user interface. Conversely, if you want to work with only one
row at a time, it can be more convenient to use a GUI.
Example Query 8
DELETE FROM Actor
WHERE lastName = 'Hanks';
