SQL: Database Maintenance

---

Unit 1. SQL
Overview

1. SQL Overview

       1.1
Relational Concepts


       1.2 Course Tables


       1.3 SQL Concepts


       1.4 Unit 1 Summary

This unit presents introductory information that is helpful while you're
learning SQL — IBM's Structured Query Language.

In this unit, you will learn concepts about relational databases, and you will
see the data used in this course. You will also be introduced to some general
topics about SQL.



After completing this unit, you should be able to:

• Describe how data is stored
  in a relational database
• Recall the general format of
  the tables used in this course
• Explain the functions of SQL
  and its three components

Topic 1.1: Relational
Concepts

*Columns and Rows

In a relational database model, the data is stored in one or more tables.


Each table consists of a specific number of columns and some number of
unordered rows.

One row of a sample table named STUDENT
is displayed below.

Examine the following table
STUDENTNO	FIRSTNAME	MIDINIT	LASTNAME	MAJOR	SEX
S10	GERALD	A	ATTRIX	MATH	M

*Example Table

Examine the following table
STUDENTNO	FIRSTNAME	MIDINIT	LASTNAME	MAJOR	SEX
S10	GERALD	A	ATTRIX	MATH	M
S20	PHYLISS	A	LUTZ	PHYSICS	F
S30	DELBERT	A	SPRAY	MATH	M
S40	JAMES	E	SHUE		M
S50	BEVERLY	A	BOGGS	HISTORY	F
S60	DAVE	I	HICKS	MATH	M
S70	JACKIE	I	RICTER	BUSINESS	F
S80	MARGE	E	GILHOULY		F
S90	IRMA	A	SPRAY	HOME ECON	F

Above is the complete table. As you can see, the table consists of six columns:
STUDENTNO, FIRSTNAME, MIDINIT, LASTNAME,
MAJOR and SEX.


It also consists of nine rows, one for each student. The next section goes over
this table and the others used in this course.





*Column Characteristics

Columns of a table have the following characteristics:

• Appear vertically when
  displayed on a report
• Contain the same kind of
  data
• Have names which are used as
  column titles when they are displayed on a report

*Row Characteristics

Rows of a table have the following characteristics:

• Appear horizontally when
  displayed on a report
• Contain one or more columns
  of data
• Unlike columns, they have no
  names

*How Large Can a Table Be?

The tables you work with in this course are relatively small for ease of use
and reference. However, this does not mean you are limited to working with
small tables.


A table can be virtually any number of rows with one or more columns.

There is a limitation, but it is unlikely to be reached under normal
conditions.

A table with zero rows is called an empty table.





*The Relational Database Model

Relational databases are based on a model formulated by Dr. E. F. Codd of IBM in 1968. He used very formal terms to describe
the concepts you just saw.


Here are the equivalent terms he used:

Examine the following table
Formal Relational Term	Informal Equivalents
Relation	Table
Tuple	Row or record
Attribute	Column or field

So you can see where the terms relational model and relational database come
from. Tables were called relations.

In this course, you see the less formal terms — table, row, and column.

Topic 1.2: Course Tables

*Example Tables

Throughout this course several tables are used for examples and exercises. This
section describes each and shows the table entries.

Throughout this course, the tables may be modified slightly to illustrate a
point. Also, due to the size of the screen, only portions are displayed at times.





*The STUDENT
Table

The STUDENT table contains
student information. Each row describes one student. The columns are:

Examine the following table
STUDENTNO	- student number	FIRSTNAME	- first name
MIDINIT	- middle initial	LASTNAME	- last name
MAJOR	- major subject	SEX	- M or F

Examine the following table
STUDENTNO	FIRSTNAME	MIDINIT	LASTNAME	MAJOR	SEX
S10	GERALD	A	ATTRIX	MATH	M
S20	PHYLISS	A	LUTZ	PHYSICS	F
S30	DELBERT	A	SPRAY	MATH	M
S40	JAMES	E	SHUE		M
S50	BEVERLY	A	BOGGS	HISTORY	F
S60	DAVE	I	HICKS	MATH	M
S70	JACKIE	I	RICTER	BUSINESS	F
S80	MARGE	E	GILHOULY		F
S90	IRMA	A	SPRAY	HOME ECON	F

*The INSTRUCT
Table

The INSTRUCT table contains
information about instructors. Each row describes one instructor. The columns
are:

Examine the following table
INSSN	- social security no.	DEPTNO	- department no.
IFIRSTNAME	- first name	IMIDINIT	- middle initial
ILASTNAME	- last name	SALARY	- salary
EXEMPTIONS	- no. of exemptions

Examine the following table
INSSN	DEPTNO	IFIRSTNAME	IMIDINIT	ILASTNAME	SALARY	EXEMPTIONS
334838999	MTH	ELIZABETH	A	SONS	35000	3
393209494	MTH	ALYCE	P	SEEGERS	40000	3
382949898	ENG	STEWART	I	GOTZ	31000	4
893993999	ENG	JOHN	A	RABBET	41000	0
998329399	HST	THOMAS	A	SEEGERS	40000	2

*The GRADE
Table

The GRADE table contains numeric
and character data for grades. Each row is for one grade. The columns are:


GRADE    -
character grade value         NUMGRADE - numeric grade value

Examine the following table
GRADE	NUMGRADE
A	4.0000
B	3.0000
C	2.0000
D	1.0000
F	0.0000

*The CLASS
Table

The CLASS table contains data
about specific classes. Each row defines one class. The columns are:

CLASSNO   - class
number         CLASSNAME - class name


CLASSCOST - cost for class
        COURSEHRS - course hours

Examine the following table
CLASSNO	CLASSNAME	CLASSCOST	COURSEHRS
E01	ENGLISH 101	500.00	4
E02	ENGLISH 102	450.00	3
M01	MATH 101	600.00	3
M02	MATH 102	700.00	3
M03	MATH 103	800.00	3
P01	PHYSICS 101	600.00	3
H01	HISTORY	510.00	4

*The SECTION

Table

The SECTION table contains information
about each section that is taught for a specific class. Each row describes one
section. The columns are:

Examine the following table
CLASSNO	- class number	SECTIONNO	- section number
INSSN	- instructor's social security no.	ROOMNO	- room number

Examine the following table
CLASSNO	SECTIONNO	INSSN	ROOMNO
E01	001	893993999	301
E01	002	893993999	302
E02	001	382948398	205
M01	001	334838999	389
M02	001	334838999	239
M02	002	393209494	222
M03	001	393209494	203
P01	001	393209494	105
H01	001	998329399	333

*The ROSTER

Table

The ROSTER
table contains data that identifies each student signed up for a specific section
of a class. Each row describes one class for a student. The columns are:

Examine the following table
CLASSNO	- class number	SECTIONNO	- section number
STUDENTNO	- student number	GRADE	- grade

Examine the following table
CLASSNO	SECTIONNO	STUDENTNO	GRADE
E01	001	S20	A
E01	001	S30	B
E01	001	S70	B
E01	002	S80	A
E01	002	S10	C
E01	002	S40	B
E02	001	S50	C
E02	001	S60	A
E02	001	S90	D
M01	001	S40	A
M01	001	S70	D
M02	001	S90	B
M02	001	S80	F
M02	001	S10	C
M02	001	S30	B
M02	002	S20	C
M02	002	S60	D
M02	002	S80	A
M02	002	S90	A
M02	002	S50	F
M03	001	S10	D
M03	001	S40	D
M03	001	S70	D
M03	001	S60	C
M03	001	S90	A
H01	001	S10	C
H01	001	S40	B
H01	001	S70	B
H01	001	S80	B
H01	001	S30	B
P01	001	S10	B
P01	001	S20	B
P01	001	S30	B
P01	001	S40	B
P01	001	S50	B
P01	001	S60	B
P01	001	S70	B
P01	001	S80	B
P01	001	S90	B

Topic 1.3: SQL Concepts

*SQL Features

In this section, you will learn a little bit about the features and
capabilities of SQL. This information provides a foundation for the following
units which cover specific uses of SQL.



*What Is SQL?

SQL is a high-level language that allows you to access and maintain data stored
in relational databases, such as DB2 and SQL/DS. Products like FOCUS, SAS, and
Oracle, which create and maintain relational tables, also use SQL. SQL is one
interface to products in these relational systems.

SQL is designed for programmers, system administrators and data processing
professionals. Yet, it is especially nice for users who have little training in
computer programming. With SQL you specify what you want rather than how to get
it. You do not have to be concerned with how the data is stored, or how it is
retrieved.



*Three Components


SQL is rapidly becoming the industry standard for fourth generation query
languages. SQL is easy to learn and easy to use.

Power and simplicity are both features of SQL. You can use single SQL
statements to perform functions that require many lines of conventional code.

SQL has these three basic components:

• Data Definition Language
  (DDL)
• Data Manipulation Language
  (DML)
• Authorization Language

*Data Definition Language

DDL (Data Definition Language) is used to define new tables. The columns to be
included in your table are defined using the CREATE
statement. Here is a sample CREATE
statement for the STUDENT table
used in this course.


CREATE TABLE STUDENT

    (STUDENTNO CHAR(3) NOT NULL,

     FIRSTNAME
VARCHAR(20),

     MIDINIT
CHAR(1),

     LASTNAME
VARCHAR(25) NOT NULL,

     MAJOR
CHAR(15),

     SEX CHAR(1)
NOT NULL)

As you can see, the table consists of 6 columns: STUDENTNO, FIRSTNAME, MIDINIT, LASTNAME, MAJOR and SEX.


The other entries will be discussed in a later unit.



*DDL Statements

DDL statements permit you to create, change, and delete various components of
the relational database. In fact, you can define and modify the database online
without special utility programs.

Thus, you can interactively

• Create and manipulate tables
• Test SQL statements you plan
  on using in programs

and not affect the ongoing
processes and data already established in your database.





*Data Manipulation Language

DML (Data Manipulation Language) has two basic functions:

• Creating reports
• Maintaining tables

One of the simplest, and most
common report requests is to view a complete table. All you need is the name of
the table.

Let's look at a sample report request.



*Example DML Statement

For example, the statement below requests a report of all columns and all rows
for the table named STUDENT.


SELECT * FROM STUDENT

Examine the following table
STUDENTNO	FIRSTNAME	MIDINIT	LASTNAME	MAJOR	SEX
S10	GERALD	A	ATTRIX	MATH	M
S20	PHYLISS	A	LUTZ	PHYSICS	F
S30	DELBERT	A	SPRAY	MATH	M
S40	JAMES	E	SHUE		M
S50	BEVERLY	A	BOGGS	HISTORY	F
S60	DAVE	I	HICKS	MATH	M
S70	JACKIE	I	RICTER	BUSINESS	F
S80	MARGE	E	GILHOULY		F
S90	IRMA	A	SPRAY	HOME ECON	F

*The SELECT
Statement

SELECT * is the simplest
retrieval request. Other functions of the SELECT
statement include:

• Limiting the request to
  specific columns
• Limiting the request to
  specific rows
• Ordering the rows presented
  on the request
• Developing new column values
  through calculations
• Accumulating numeric column
  values within specific groupings
• Combining data from two or
  more tables through a common key

Note that the SELECT
statement is covered in other courses in this series. Information presented in
those courses will not be repeated here.



*Table Maintenance

The other function of DML is to maintain tables.

Here's a simple request to create a new row in the STUDENT table.


INSERT INTO STUDENT

   VALUES ('S02', 'RALPH',
'A', 'LARSON'    , 'PHYSICS', 'M')

This request adds the new row containing the values listed in parentheses.
You'll learn how to code this type of request later in the course.





*Authorization Language

The function of the Authorization Language is to provide effective control over
the database resources.

Security in relational databases like DB2 and SQL/DS can be summarized in one
sentence: Nothing can be done unless you have been given the authority.

Authority is controlled by the data administration area through SQL commands.




GRANT and REVOKE

Although you need authority to do anything with the
database, it is a relatively simple matter to obtain it.

Authority is given through the GRANT
command. For example, the command below gives user PROG1 authority to create a table in the database named SKILLS.


GRANT CREATETAB ON DATABASE SKILLS TO PROG1

Authority may also be removed with the REVOKE
command. Here's an example removing CREATE
authority for tables in database SKILLS
from PROG1.

REVOKE CREATETAB ON DATABASE SKILLS FROM
PROG1






*SQL Implementations

You can use SQL statements interactively; for example, via IBM's Query Management
Facility (QMF) or an interactive facility provided with the relational database

You can also use SQL statements embedded in application programs, such as APL2,
BASIC, COBOL, PL/I, FORTRAN, LISP, or assembler language programs

These various implementations of SQL, however, are semantically and
syntactically the same. What you learn here can be carried to any
SQL-based database management system.



*Syntax Rules

When coding SQL statements, follow these syntax rules:

• Variables and keywords must be
  separated by one or more spaces.
• Parentheses are often
  required around a keyword's values. Commas must separate values in a list
  (e.g. column names, table names, data values).
• Spacing around commas,
  parentheses and operators (=,>,+,*,etc.) is
  for readability. Spaces are not required.
• Character strings are
  sequences of characters that begin and end with a string delimiter. A
  string delimiter is either '
  
  (apostrophe) or "
  (quotes) depending on the host language and the character chosen
  when the database system is installed. Use the same character (' or ")
  for the beginning and ending delimiter.

   INSERT INTO GRADE VALUES ('E01',0.000)


   INSERT INTO GRADE VALUES('E01',0.000)

   INSERT INTO GRADE VALUES ( 'E01', 0.000 )

*Equivalent Statements

Above are three valid codings of the same statement
showing the syntax rules.

Spaces are required between each keyword or variable (INSERT, INTO,

GRADE, VALUES). In the VALUES portion, parentheses are needed
around the values and commas must separate the values. The character string E01 is delimited by apostrophes. Spaces
around parentheses and commas are optional.





*Course Conventions

In this course, you will see a few conventions followed for readability or
emphasis. These conventions are

• Each clause is entered on a
  separate line, for example
  
  
  
  SELECT * FROM ROSTER
  
        WHERE
  STUDENTNO = 'S10'
  
  
  
  
  rather than
  
  
  
  SELECT * FROM ROSTER WHERE STUDENTNO =
  'S10'
• Information coded between the
  symbols < > is
  optional.
• ... indicates information
  that repeats.

Topic 1.4: Unit 1 Summary

In this unit, you learned the following:

• Databases based on a
  relational model store data in one or more tables.
• Each table in a relational
  database consists of a specific number of columns and some number of
  unordered rows.
• Columns in a relational
  database appear vertically in a report, contain the same kind of data, and
  have names that are used as column titles.
• Rows in a relational
  database appear horizontally in a report, contain one or more columns of
  data, and do not have names.
• In formal terms, tables are
  named relations, columns are called attributes, and rows are known as tuples.
• Structured Query Language
  (SQL) is a high-level language that allows you to access and maintain data
  stored in relational databases, such as DB2 and SQL/DS. Products like
  FOCUS, SAS and Oracle, which create and maintain relational tables, also
  use SQL.
• DDL (Data Definition
  Language) allows you to define new tables in the database. You can define
  and modify the database online without special utility programs.
• DML (Data Manipulation
  Language) is used to create reports and maintain tables.
• The Authorization Language
  provides control of database resources.
• When coding SQL statements,
  follow these syntax rules:
  
  1) Variables and keywords must be separated by one or more spaces.
  
  2) Parentheses are often required around a keyword's values. Commas must
  separate values in a list.
  
  3) Spacing around commas, parentheses and operators is for readability and
  is not required.
  
  
  4) Character strings are sequences of characters that begin and end with ' or ".
  Use the same character ('
  or ") for the
  beginning and ending delimiter.

Unit 2. Designing Tables

2. Designing Tables

       2.1
One to Many Relationships

       2.2 Many to Many Relationships

       2.3 Unit 2 Summary

In this unit, you will learn some fundamentals about table design. You will
learn how tables in a database are linked in one of two types of relationships,
and you will learn about primary and foreign keys.



After completing this unit, you should be able to:

• Recognize tables that have
  a one to many relationship
• Establish a many to many
  relationship between two tables by using another table that connects their
  key values

*Breaking Up the Data


To start this subject, look now at some fundamental issues regarding the design
of tables. As you recall, a relational database consists of one or more tables.
A table consists of a specific number of columns and some number of unordered
rows.

Your first step is to break the data for your application into major entities
or tables. Your sample database is a simple example that describes information
about students, classes, and instructors in a college.

For this reason, your database consists of three major tables:

STUDENT, CLASS, and INSTRUCTOR



*Table Relationships


You need to consider the relationships between these tables. All relationships in
a relational database are established through values stored in the tables.

That is, no implied link between tables is automatically established and
maintained by the system.

These relationships are established by matching key values from one table with values
from another. You'll see more about this in a minute.

Topic 2.1: One to Many
Relationships

*One to Many

First of all, a student typically takes many classes. This is called a one to many relationship.


This relationship is expressed through the ROSTER
table. Each row in the ROSTER
table represents one student in one class.

Look at some values from the tables to make this clearer.



*Primary Key

Examine the following table
STUDENTNO	FIRSTNAME	MIDINIT	LASTNAME	MAJOR	SEX
S10	GERALD	A	ATTRIX	MATH	M
S20	PHYLISS	A	LUTZ	PHYSICS	F
S30	DELBERT	A	SPRAY	MATH	M
S40	JAMES	E	SHUE		M
S50	BEVERLY	A	BOGGS	HISTORY	F
S60	DAVE	I	HICKS	MATH	M
S70	JACKIE	I	RICTER	BUSINESS	F
S80	MARGE	E	GILHOULY		F
S90	IRMA	A	SPRAY	HOME ECON	F

Each row in the STUDENT table
contains all the information required to describe one student.


Every table has a primary key. A primary key is a column or a
combination of columns that distinguishes a specific row from all other rows.



*Entity Integrity

Examine the following table
STUDENTNO	FIRSTNAME	MIDINIT	LASTNAME	MAJOR	SEX
S10	GERALD	A	ATTRIX	MATH	M
S20	PHYLISS	A	LUTZ	PHYSICS	F
S30	DELBERT	A	SPRAY	MATH	M
S40	JAMES	E	SHUE		M
S50	BEVERLY	A	BOGGS	HISTORY	F
S60	DAVE	I	HICKS	MATH	M
S70	JACKIE	I	RICTER	BUSINESS	F
S80	MARGE	E	GILHOULY		F
S90	IRMA	A	SPRAY	HOME ECON	F

The column STUDENTNO is the
primary key of the STUDENT
table.


To qualify as a primary key, a column or columns must be unique and cannot be NULL. This requirement is called entity
integrity.



*The Link between Tables

Examine the following table
CLASSNO	SECTIONNO	STUDENTNO	GRADE
E01	001	S20	A
E01	001	S30	B
E01	001	S70	B
E02	001	S50	C
E02	001	S60	A
E02	001	S90	D
M01	001	S40	A
M01	001	S70	D
M02	001	S90	B

Here is a part of the ROSTER
table. Each row in the ROSTER
table represents a student in a given class.

Notice that the student number is stored in this table as well. It is required
to connect the student to the classes he is taking.

Since STUDENTNO is the primary
key of another table, it is called a foreign key in this table.




*Foreign Keys

A foreign key references a primary key in another table or the same
table. It is the stored "redundant" data of the foreign key that
drives join processing.

Tables may have any number of foreign keys. As with primary keys, foreign keys
may be a composite of several columns.

Foreign keys may be NULL.




Note: In SQL/DS foreign keys are not allowed to reference the same
table.









*Parents and Dependents

In this relationship, STUDENT is
termed the parent while ROSTER
is called dependent. In essence, the foreign key is dependent upon the primary
key value.


You ensure valid references from one table to another via referential integrity
provided by the database system.



*Referential Constraints

A referential constraint formalizes the relationship between the primary
key and the foreign key. Each referential constraint involves a set of rules
applied by the database.

These rules ensure that each foreign key value matches a primary key value
regardless of the changes for either the parent table or the dependent table.



*The Purpose of Referential Constraints

You can define the referential constraints when you
create a new table or alter an existing one. You'll see how later in this
course.


The referential constraints applied to these tables make sure that

• You cannot update a student
  number in ROSTER (the
  dependent table).
• If you want to insert
  student information in ROSTER,
  the student number must exist in STUDENT
  
  first.

*Another One to Many Example

There is a one to many relationship between the CLASS and ROSTER

tables as well.

The CLASS table displays below.
Its primary key is CLASSNO.
Since CLASSNO is stored as a
foreign key in the ROSTER table,
you can use it to find all the students who have taken a given class.

Examine the following table
CLASSNO	CLASSNAME	CLASSCOST	COURSEHRS
E01	ENGLISH 101	500.00	4
E02	ENGLISH 102	450.00	3
M01	MATH 101	600.00	3
M02	MATH 102	700.00	3
M03	MATH 103	800.00	3
P01	PHYSICS 101	600.00	3
H01	HISTORY	510.00	4

Topic 2.2: Many to Many
Relationships

*A Different Relationship

By using the ROSTER

table, you can find all the students that are taking a specific class. You can also
find all the classes a specific student is taking.

For this reason, there is a many to many relationship between the CLASS and the STUDENT tables established through the ROSTER table.

Note in the diagram above, ROSTER
is dependent to both the STUDENT

and CLASS tables.



*A Sample Request

The sample request below uses the ROSTER
table to combine information from the STUDENT
table with information from the CLASS

table. The request creates a report that shows the students that are taking
each class.

SELECT CLASSNAME, SECTIONNO, FIRSTNAME,
LASTNAME

FROM CLASS, ROSTER, STUDENT





   ORDER BY CLASSNAME,
SECTIONNO, LASTNAME

The WHERE
clause does the actual connection. Notice it matches the primary key of the CLASS table (CLASSNO) and the primary key of the STUDENT table (STUDENTNO) with the corresponding columns in the ROSTER table to connect the students
enrolled in each class.






*Many to Many

A many to many relationship exists between the CLASS and INSTRUCT
tables. That is, an instructor may teach many classes, and a class may be
taught by many instructors.

It's obvious that an instructor teaches many classes, but why can a class be
taught by many instructors?

This is due to a specific class being taught more than once.


For example, English 101 may be taught three different times, by three
different instructors, in three different rooms.





*Establishing the Relationship

The SECTION table establishes
the relationship between instructor and classes and it contains the room
assignments.

There is one row in this table for every class occurrence represented on the
database. For example, English 101, (CLASSNO=E01)
has two sections. Math 102, (CLASSNO=M02)
also has two sections.


The only descriptive information stored in this table is the room number.

Topic 2.3: Unit 2 Summary

In this unit, you learned the following:

• Relationships between
  relational tables are established through values stored in the tables.
  These relationships are established by matching key values from one table
  with values from another.
• A primary key is a column
  or a combination of columns that uniquely identifies one row in a table.
• When a primary key is
  stored in another table it is called a foreign key.
• A referential constraint
  formalizes the relationship between a primary key in the parent table and
  the foreign key in a dependent table.
• Two types of relationships
  may exist between two tables: one to many, such as one student taking
  several classes; and many to many, such as one instructor teaching many
  classes and a class being taught by many instructors.
• The best way to establish a
  many to many relationship between two tables is to use an associated table
  that contains the values of the keys required to connect them.

Unit 3. Creating Tables

3. Creating Tables

       3.1
The CREATE Statement

              3.1.1 Table and Column Names

              3.1.2
Data Types

              3.1.3
Null Characteristics

       3.2 Assigning Keys

       3.3 Unit 3 Summary

The previous unit discussed various concepts about designing tables. With these
concepts in mind, you're now ready to see how you create tables with SQL.

In this unit, you will learn about the CREATE
statement used to establish a new table in a database.



After completing this unit, you should be able to:

• Create a new table in a database
• Assign primary and foreign
  keys as part of the CREATE
  statement

Topic 3.1: The CREATE
Statement

*The Statement Format

The general format of the CREATE

statement is

CREATE TABLE table-name

(column-name1  datatype1  <
NOT NULL <WITH DEFAULT> >,

  column-name2  datatype2  <

NOT NULL <WITH DEFAULT> >...)

Note that the symbols < > enclose optional coding.

This statement establishes

• The table name
• All of the columns you want
  in the table
• The type and length of the
  columns contained in the table

Let's discuss all of these
elements in detail, starting with names for tables and columns.

Topic 3.1.1: Table and
Column Names

*The Naming Rules

First, table names and column names follow the same
rules.

• They are limited to 18
  characters.
• They must start with A - Z,
  #, @, or $.
• The remaining characters may
  be any combination of letters, underscore (_), and numbers.
• Blanks and special
  characters may be included in the name. In order to avoid problems, limit
  special character usage to the underscore.
• If a table name is
  unqualified, (not prefixed with a 1 to 8 character name), your
  authorization id is assigned as the prefix.
• The 1 to 8 character prefix
  is not counted as part of the 18 character size limit of a table name.

*Valid Names

Listed below are valid unqualified names for tables and columns.

STUDENT

STUDENT_TABLE

STUDENTNO

STUDENT_NUMBER

If these names are used as table names your
authorization id is used as a prefix.


These are examples of qualified table names.

PROD.STUDENT

DSN8.TEMPL

CYST32.STUDENT_NUMBER

These names are not prefixed by your authorization id.




*Using Special Characters

When referring to names with special characters, you
must enclose the name in quotes (").


For example, a column name of WEEKLY SALARY
must be enclosed in quotes — "WEEKLY
SALARY" — when referred to.

Generally, it is more effective to assign the name using an underscore — WEEKLY_SALARY. Using the underscore avoids
the need of quotes.

Topic 3.1.2: Data Types

*Nature and Use of Data Types

Here is the general format of the CREATE
statement again.

CREATE TABLE table-name

(column-name1    <

NOT NULL <WITH DEFAULT> >,

  column-name2    <
NOT NULL <WITH DEFAULT> >...)

Let's now discuss the nature and use of data types.



*Specifying the Type of Data


You define a column in your table to hold a specific
type of data. The database system allows you to use only that type of data in
the column. For example, you cannot put character data, such as an address, in
a column defined for integers, like salary.

The three basic types of data are character, numeric, and date/time. Look at
each and see the data types you can assign.



*Character

Character data types are used for character strings, that is, alphanumeric (EBCDIC) characters.

Examine the following table
CHAR(x)	Assigned to fixed-length strings x is the length of the string
VARCHAR(x)	Assigned to variable-length strings x is the maximum length of the string

*Numeric

Numeric data types are used for numeric data only.

Examine the following table
SMALLINT	For integers up to 32,767; preferable to INTEGER whenever practical
INTEGER	For VERY LARGE integers (whole numbers with no decimals) up to 2 billion
DECIMAL(x,y)	For numbers with a fixed number of places after the decimal point x is the number of numeric digits (known as significant digits), maximum is 31; y is the number of decimal places

All three numeric data types allow both positive and negative values.



Note: Another numeric data type — FLOAT
— can be used for extremely large numbers.





*Date/Time

Date/Time data types are used for date and time representations.

Examine the following table
DATE	Assigned to three-part values representing a year, month and day (yyyy-mm-dd) according to the Gregorian calendar
TIME	Assigned to three-part values representing the time of day in hours, minutes and seconds (hh.mm.ss)
TIMESTAMP	Assigned to seven-part values representing a date and time by year, month, day, hour, minute, second and microsecond (yyyy-mm-dd-hh.mm.ss.nnnnnn)

For more detailed information on character, numeric and date/time data types,
refer to the reference guides for your database system.









*Choosing a Data Type

To help you understand when to use each data type,
let's look at some examples.


You usually need to decide whether the column should be numeric format or
character format.

In general, if a column is used for descriptive data, always use CHAR or VARCHAR
even if the column contains only numbers.

For example, social security number contains only numbers, but CHAR(9) is the data type to use. The only time
you need to assign a numeric format is when a column is used in calculations.




CHAR or VARCHAR?

After deciding on character format for a column, you must decide between CHAR or VARCHAR.
But, how do you know which one to use?

A column defined as CHAR is
always stored at the same length.


A column defined as VARCHAR may
vary in length from one occurrence to another. A VARCHAR format saves space for large fields stored on the
database if there is a large difference in size from one occurrence to another.

Since a VARCHAR is variable
length there is more processing overhead.



*How to Decide

Here are some simple rules to help you decide which
format to use.

• Use CHAR for all columns less than 20
  characters.
• Use VARCHAR for all columns that are
  longer than 256.
• For columns between 20 and
  256 use VARCHAR
  
  only if there can be a variance of at least 15 characters between
  occurrences.

*Numeric for Calculations

If you are planning to use the column for calculations, you must assign a
numeric format. Which format to use depends on the number of significant
digits (total number of digits) required and whether it is a whole number.



*Choosing a Number Format


Here are some simple rules to help you make your
decision about numeric columns.

• If the column requires more
  than 15 significant digits, use FLOAT.
  
• If the column requires less
  than 16 significant digits and has a decimal place, use DECIMAL.
• If the column is to contain
  only whole numbers, use SMALLINT
  for columns with less then 5 significant digits; use INTEGER for those greater than 4.
• If the column is to contain
  a whole number, but it needs between 10 and 15 significant digits, use DECIMAL(n,0) where n is the number of significant digits.

FLOAT (rather than REAL or DOUBLE
PRECISION) makes porting your databases to other hardware easier.
For more information on REAL and
DOUBLE PRECISION data types, see
your SQL manuals.

Topic 3.1.3: Null
Characteristics

NULL or NOT NULL

Here is the general format of the CREATE
statement again.


CREATE TABLE table-name

(column-name1  datatype1  ,

  column-name2  datatype2  ...)

The last decision you have to make for each column is what type of NULL characteristic to assign.


You have three choices. Let's look at these.



*The Options

Examine the following table
Code nothing (default)	This indicates that the column can contain null values. If you choose this option the database keeps track of null values with a flag character associated with the column. If no value is entered for this column when a row is created, the null indicator is set.
NOT NULL	This indicates the field must always be assigned a value when a row is created.
NOT NULL WITH DEFAULT	If a value is not assigned to this column a default value is assigned. The defaults are blanks if the data type is CHAR; zero for all numeric types; a zero length for VARCHAR columns; the current time for TIME; the current date for DATE; the current timestamp for TIMESTAMP.

*Some Examples

Here are some examples of how to use the various null characteristics.

Examine the following table
Code nothing	Use when you want to distinguish a field having no value entered from a value of blank or zero. An example is the column MAJOR in the STUDENT table. This allows you to tell when a student enrolls without selecting a major.
NOT NULL	Use for any column that is a key. This ensures no row is created without the column being assigned a value. Examples are part numbers, social security numbers, account numbers, and so on.
NOT NULL WITH DEFAULT	Use for columns that don't need a value, but you don't want to incur the overhead of the null indicator.

Topic 3.2: Assigning Keys

*Format for Adding Keys

Now let's add to the general format of the CREATE
statement the information for assigning primary and foreign keys.

Here is the general format with the additions for these keys.


CREATE TABLE table-name

(column-name1  datatype1  <
NOT NULL <WITH DEFAULT> >,

  column-name2  datatype2  <
NOT NULL <WITH DEFAULT> >


...







Look first at assigning a primary key.



*Primary Key Clause

Use the primary key clause to assign a primary key when the table is created.
Each table can have only one primary key.

Indicate the column or columns that distinguish a specific row from all other
rows in the table. You place the column names within parentheses, for example:

PRIMARY KEY (INSSN)


This coding is appropriate for the INSTRUCT
table where the instructor's social security number is the primary key.





*Foreign Key Clause

Use the foreign key clause to assign any foreign keys when the table is
created. A table can have multiple foreign keys.

Here is the general format of the foreign key clause.





Indicate the column or columns that make up the foreign key within parentheses
following FOREIGN KEY. Identify
the parent table involved in this referential constraint in the table-name
position. The ON DELETE phrase
indicates how to handle the dependent column when the parent is deleted. Let's
see what options you have.




ON DELETE Options

Three delete rules can be followed when a row is
deleted from the parent table in a relationship.

Examine the following table
RESTRICT	Prevents deletion of the parent until all dependent rows are deleted (default coding).
CASCADE	Deletes all dependent rows (in SQL/DS, only one level of cascade is supported).
SET NULL	Sets to null values all columns of foreign keys in each dependent row that can contain nulls.

*Creating the INSTRUCT
Table

Here's the CREATE statement that
defines the INSTRUCT table.


CREATE TABLE INSTRUCT

  (INSSN      CHAR(9)       NOT
NULL,

   DEPTNO     CHAR(3)       NOT
NULL,

   IFIRSTNAME CHAR(20),

   IMIDINIT   CHAR(1)       NOT
NULL WITH DEFAULT,

   ILASTNAME  VARCHAR(30)   NOT
NULL,


   SALARY     DECIMAL(10,2)
NOT NULL,

   EXEMPTIONS
SMALLINT      NOT NULL,

   PRIMARY KEY (INSSN))

INSSN, DEPTNO, IFIRSTNAME
and IMIDINIT are assigned CHAR formats because they contain text
type data and are small in size. ILASTNAME

is assigned a VARCHAR format
because it is large enough to warrant it. SALARY
requires two decimal positions so DECIMAL
is used. EXEMPTIONS
is a numeric field, less than 4 digits in length, so SMALLINT is appropriate.




*Creating the CLASS
Table

Here's the CREATE statement that
defines the CLASS table.

CREATE TABLE CLASS

  (CLASSNO      CHAR(3)      NOT NULL,


   CLASSNAME    VARCHAR(50)  NOT
NULL,

   CLASSCOST    DECIMAL(8,2)
NOT NULL,

   COURSEHRS    SMALLINT     NOT
NULL,

   PRIMARY KEY (CLASSNO))




*Other Optional Clauses


There are other optional clauses and parameters that can be used with the CREATE TABLE
statement, but their explanation is beyond the scope of this course. Some
examples are:

Examine the following table
IN database-name	Specifies the database in which the table is created.
OBID integer	Required if the database for the table is defined as ROSHARE READ.
CHECK column-name	Used to define a value for one or more columns of a table. The optional clause CONSTRAINT may be used with the CHECK clause.

For more information on these and other parts of CREATE TABLE, see your SQL manuals.

Now try your hand at creating a small table in the practice that follows.











 CREATE TABLE GRADE

      (GRADE CHAR(1) NOT
NULL,


       NUMGRADE SMALLINT NOT NULL,

       PRIMARY KEY (GRADE))

*The Completed Statement

Above you see the complete CREATE
statement. As a result, the table is defined on the database.

Now you are ready to add data to the table. You will learn about that in the
next unit.

Topic 3.3: Unit 3 Summary

In this unit, you learned the following:

• Use the CREATE statement to establish a new
  table in your database. The general format of the CREATE statement is
  
  
  
  
  CREATE TABLE table-name
  
  (column-name1  datatype1  <
  NOT NULL <WITH DEFAULT> >,
  
    column-name2  datatype2  <
  
  NOT NULL <WITH DEFAULT> >...)
• The CREATE statement establishes the
  table name, all of the columns you want in the table, and the type and
  length of the columns contained in the table.
• Names for tables and
  columns must follow certain rules.
• Each column in the table is
  defined to hold a specific type of data. The data types are character,
  numeric, and date/time.
• Character data types are
  for alphanumeric character strings.
• Numeric data types hold
  numeric data only. This data type is for columns that are going to be used
  in calculations.
• Date/time data types
  represent date and time values.
• One of three NULL characteristics can be assigned
  to each column.
• Add the primary key clause
  to the general format of the CREATE
  
  statement when you assign a primary key. The format for this clause is
  
  
  
  PRIMARY KEY (column-name1<, column-name2,
  ...>)
• Add the foreign key clause
  to the general format of the CREATE
  
  statement when you want to assign a foreign key. The format for this
  clause is
  
  
  
  FOREIGN KEY (column-name1<, column-name2,
  ...>)
  
    REFERENCES table-name
  
  <ON DELETE delete-rule>
• The ON DELETE phrase in the foreign key
  clause indicates how to handle the dependent column when the parent is
  deleted.

Unit 4. Inserting Rows

4. Inserting Rows

       4.1
The INSERT Statement

       4.2
Handling Large Tables

       4.3 Inserting Dates and Times

       4.4 Unit 4 Summary

The previous unit showed you how to use the CREATE

statement to define tables for your relational database. Once you have the
tables defined, you need to fill them with data.

You use the INSERT statement to
add data to a table. This unit covers the details about using the INSERT statement.



After completing this unit, you should be able to:

• Use the INSERT statement to add data to a
  table
• Handle large tables by
  loading data from an existing table
• Use proper date and time
  formats in INSERT
  statements

Topic 4.1: The INSERT
Statement

*The Statement Format

Here is the general format of the INSERT
statement. This format inserts one row into a table.

INSERT INTO table-name  VALUES (value1, value2, value3, ...)





*An Example Statement

This example INSERT statement
inserts one row into the ROSTER
table.

INSERT INTO ROSTER  VALUES('E01',
'001', 'S10', 'A')

Notice that the ROSTER data
above is enclosed in quotes. All character data must be enclosed in quotes,
while numeric data is not. Numeric data can also contain decimal points and
signs. For example, -325.99 is valid for numeric data.





*Constraints Are Enforced

If you are inserting data into a table that is part of
a referential constraint, the database system enforces these references.

Parent Tables

• The primary key value must
  not be null.
• The primary key value must
  not duplicate an existing primary key.

Dependent Tables

• The foreign key value must
  match an existing primary key value or be null.

Thus, you cannot insert data
about S15 in the ROSTER table unless S15 already exists in the STUDENT table.






*Adding a Row

Here's an example that adds a row to the CLASS
table. The CREATE statement is
included to show the column names that pertain to the values.

CREATE TABLE CLASS

  (CLASSNO      CHAR(3)      NOT NULL,


   CLASSNAME    VARCHAR(50)  NOT
NULL,

   CLASSCOST    DECIMAL(8,2)
NOT NULL,

   COURSEHRS    SMALLINT     NOT
NULL,

   PRIMARY KEY (CLASSNO))

This is the INSERT statement.


INSERT INTO CLASS

  VALUES('G01',
'INTRODUCTION TO WORLD GEOGRAPHY', 442.50, 4)



*Column Order Must Match

The values are entered in the same order as the columns are listed in the CREATE statement. Thus, G01 is the value for CLASSNO; INTRODUCTION
TO WORLD GEOGRAPHY is for CLASSNAME;
etc.


CREATE TABLE CLASS

  (CLASSNO      CHAR(3)      NOT NULL,

   CLASSNAME    VARCHAR(50)  NOT
NULL,

   CLASSCOST    DECIMAL(8,2)
NOT NULL,

   COURSEHRS    SMALLINT     NOT
NULL,


   PRIMARY KEY (CLASSNO))

This is the INSERT statement.

INSERT INTO CLASS

  VALUES('G01',
'INTRODUCTION TO WORLD GEOGRAPHY', 442.50, 4)







NOT NULL Columns


If the column was assigned an attribute of NOT
NULL, you must assign it a value in the INSERT.

If the column allows nulls, it is not necessary to assign a value. One way to
not assign a value is to use the word NULL
in place of a value.



*Assigning NULL

In the example INSERT

statement below, no value is assigned to INSSN
or ROOMNO because these columns
allow null values.

CREATE TABLE SECTION

  (CLASSNO     CHAR(3)    NOT
NULL,

   SECTIONNO   CHAR(3)    NOT
NULL,


   INSSN       CHAR(9),

   ROOMNO      SMALLINT,

   PRIMARY KEY (SECTIONNO),

   FOREIGN KEY
(CLASSNO)  REFERENCES CLASS,

   FOREIGN KEY (INSSN)    REFERENCES
INSTRUCT)

INSERT INTO SECTION VALUES('E01', '001',
NULL, NULL)




NULL Keyword

Here's another example of assigning NULL
values. This is the CREATE
statement for the STUDENT table.

CREATE TABLE STUDENT


  (STUDENTNO  CHAR(3)     NOT
NULL,

   FIRSTNAME  VARCHAR(20),

   MIDINIT    CHAR(1),

   LASTNAME   VARCHAR(25)
NOT NULL,

   MAJOR      CHAR(15),

   SEX        CHAR(1)     NOT
NULL,


   PRIMARY KEY (STUDENTNO))

The following INSERT adds one
row to the STUDENT table.

INSERT INTO STUDENT

  VALUES('S35',
'PAUL', NULL, 'SMITH', NULL, 'M')

The keyword NULL was entered for
the columns MIDINIT and MAJOR.








*Listing the Columns

An alternative to using the word NULL is to list the columns you are
entering by name.

In this case, the general format of the INSERT
statement is modified slightly.


INSERT INTO table-name

(column-name1, column-name2, ...)

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

You may list the columns in any order. Each column must belong to the table
named. The column may occur only once.



*Omitting the Null Values

This example adds a row to the STUDENT table with no values assigned to
the columns MIDINIT and MAJOR.


CREATE TABLE STUDENT

  (STUDENTNO  CHAR(3)     NOT
NULL,

   FIRSTNAME  VARCHAR(20),

   MIDINIT    CHAR(1),

   LASTNAME   VARCHAR(25)
NOT NULL,

   MAJOR      CHAR(15),


   SEX        CHAR(1)     NOT
NULL,

   PRIMARY KEY (STUDENTNO))







Topic 4.2:
Handling Large Tables

*Alternatives for Larger Tables

Inserting data for any sizeable table using the method
just discussed is a tedious job, to say the least. Adding one row at a time is
only used to add a small number of rows to an existing table, or to create
small tables.


If you are building a large table, here are three better alternatives.

• Load the values from one or
  more existing tables.
• Load the values from an
  existing file by using a load utility provided for this purpose.
• Use a full-screen input
  product, such as DBEDIT.

You'll see the first method
next. To learn about the other two approaches, see the guides and references
for your database system.



*Loading Data from an Existing Table

To use rows from existing tables to add rows to
another table you use a special form of the INSERT
statement. Here is the syntax.

INSERT INTO table-name


  SELECT column-list

    FROM table-name2

      WHERE condition

For example, you can create a table named STUDENT2
by

1) Issuing a CREATE statement
that uses the same column definitions as the STUDENT

table.

2) Then, issuing the following INSERT
statement:

INSERT INTO STUDENT2 SELECT * FROM STUDENT.

You create STUDENT2 by selecting
all the columns from the existing table STUDENT.





*Selecting a Subset of Data

You are not limited to using the SELECT * option with the INSERT statement. You can use any form of
the SELECT statement.


For example, the INSERT
statement below creates a table with all columns for students where MATH is in the MAJOR column.

INSERT INTO STUDENT2 SELECT * FROM STUDENT
WHERE MAJOR = 'MATH'

The next INSERT statement
selects only certain columns from the STUDENT

table for all students.

INSERT INTO STUDENT2 SELECT FIRSTNAME,
LASTNAME, MAJOR FROM STUDENT









  INSERT INTO STUGRADES

       SELECT   FIRSTNAME,
LASTNAME, CLASSNAME, ROSTER.GRADE, NUMGRADE

            FROM     STUDENT,
CLASS, ROSTER, GRADE

                 WHERE
(STUDENT.STUDENTNO = ROSTER.STUDENTNO)


                   AND
(CLASS.CLASSNO = ROSTER.CLASSNO)

                   AND
(ROSTER.GRADE = GRADE.GRADE)

*The Completed Statement

Above you see the entire INSERT
statement to load values from four tables into the STUGRADES table.

The WHERE

clause indicates the conditions to satisfy before selecting data from the rows.

Topic 4.3: Inserting
Dates and Times

*More Flexibility

You just learned about inserting numeric and character
data into tables.

The basic rule was that character data must be enclosed in quotes and numeric
data must not. Very simple. The data goes into the
table just as it appears in the VALUES

list.

Inserting dates and times into tables is just as easy, but you have more
flexibility in how you specify the data to be inserted.



*Date Formats

Date values to be inserted into tables can be in any one of the valid date
formats — USA, EUR, ISO
or JIS. SQL can recognize the
different date formats.


For example, here is an INSERT
statement for a table named CRUISES
that contains information about cruises.

INSERT INTO CRUISES (
CRUISE_NAME, , DEPARTURE_TIME )

VALUES ( 'BERMUDA', '', '05:40 PM' )

Here, DEPARTURE_DATE is given in

USA format. Notice, too,
that the date value, like character data, is enclosed in quotes.



*Standard Date Formats

Note that the standard date formats are:

Examine the following table
Date Type	Date Format	Valid INSERT Value	Another Valid INSERT Value
USA	MM/DD/YYYY	02/09/1999	2/9/1999
EUR	DD.MM.YYYY	09.02.1999	9.2.1999
ISO JIS	YYYY-MM-DD	1999-02-09	1999-2-9

Leading zeroes for month and day can be omitted from INSERT values, but the year portion of a
date must always be entered in full.





*Time Formats

Like date values, time values to be inserted into tables can be in any one of
the valid time formats — USA, EUR, ISO,
or JIS. SQL can recognize the
different formats.


For example:

INSERT INTO CRUISES (CRUISE_NAME,
DEPARTURE_DATE, DEPARTURE_TIME)

VALUES ( 'BERMUDA', '02/22/1999', '05:40 PM'
)

Here, DEPARTURE_TIME is given in
USA format. Notice, too,
that the time value, like character data, is enclosed in quotes.



*Standard Time Formats


Look at the standard time formats.

Examine the following table
Time Type	Time Format	Valid INSERT Value	Another Valid INSERT Value
USA	HH:MM AM or PM	01:05 PM	1:05 PM
EUR ISO	HH.MM.SS	13.05.00	13.05
JIS	HH:MM:SS	13:05:00	13:05

Leading zeroes can be omitted for the HOUR

value only. The SECOND value is
optional in all formats. And remember, USA
format is based on 12 hours, not 24 hours.

Topic 4.4: Unit 4 Summary

In this unit, you learned the following:

• Use the INSERT statement to add data to an
  existing table. The general format of the INSERT
  statement adds one row.
  
  
  
  INSERT INTO table-name
  
  
    VALUES (value1, value2,
  value3, ...)
• If you are inserting data
  into a table that is part of a referential constraint, the database system
  enforces these references. For parent tables, the primary key value must
  not be null and must not duplicate an existing primary key. For dependent
  tables, the foreign key value must match an existing primary key value or
  be null.
• You can code the INSERT statement using only the
  values that are to be added in the table. In this case, the values must be
  in the same order as the columns are listed in the CREATE statement for the table.
• If a column allows nulls, it
  is not necessary to assign a value. One way to not assign a value is to
  use the word NULL in place
  of a value.
• You can code the INSERT statement listing column names
  for the columns being added in the table. In this case, the columns may
  occur in any order. Any columns not listed are not assigned a value.
• To build large tables, you
  can load values from one or more existing tables. To use rows from
  existing tables, you use this special form of the INSERT statement.
  
  
  
  
  INSERT INTO table-name
  
        SELECT
  column-list
  
              FROM
  table-name2
  
                  WHERE
  condition
  
  
  
  
  The SELECT clause
  determines which columns are selected. You indicate the tables with the FROM clause. The WHERE clause specifies the conditions
  to satisfy before selecting any data.
• Date values can be inserted into
  tables using any of the valid date formats — USA,
  EUR, ISO or JIS. Date values must be enclosed in quotes. Leading
  zeroes can be dropped from the DAY
  
  and MONTH values.
• Time values can be inserted
  into tables using any of the valid time formats — USA,
  EUR, ISO or JIS. Time values must be enclosed in quotes. Leading
  zeroes can be dropped only from the HOUR
  
  part of a time value. The SECOND
  part of a time value is optional.

Unit 5. Updating and Deleting Rows

5. Updating and Deleting Rows


       5.1
The UPDATE Statement

       5.2
The DELETE Statement

       5.3 Maintaining and Using Tables

       5.4 Unit 5 Summary

From time to time, you will need to update or delete data held in your tables.
In this unit, you will learn the SQL statements necessary to perform these
tasks.

You will also learn how to grant and revoke authority for others to use tables
that you have created, and how to drop a table from the database.

Finally, you will see how to add columns to a table, and how to create synonyms
for tables you work with.




After completing this unit, you should be able to:

• Update data in a table
• Delete data from a table
• Use the GRANT, REVOKE, ALTER,
  CREATE SYNONYM, and DROP statements to maintain SQL
  tables that you have created

Topic 5.1: The UPDATE
Statement

*The Statement Format

To update existing values in one or more rows of a
table use the UPDATE statement.
Here is the general format.

UPDATE table-name


  SET column-name=value
<,column-name2=value2>...

   < WHERE search-condition
>

Note that the < > symbols enclose optional coding.


The WHERE
clause specifies the row(s) to be updated. Every row that satisfies the
search condition has the named columns updated with the values indicated.

If you omit the WHERE clause,
all rows in the table are updated. Let's look at an example.



*An Example UPDATE

The following statement changes the salary for the
instructor with social security number 334-83-8999 to $20,000.00.


UPDATE INSTRUCT SET SALARY = 20000 WHERE
INSSN = ‘334838999'

Remember, the WHERE statement
defines the rows to be updated.

If you eliminate the WHERE
clause from this example, all instructors have their salaries set at
$20,000.00.







*Updating Multiple Columns


You can also update more than one column in a row. For
example, this UPDATE changes the
instructor's last name and salary.

UPDATE INSTRUCT

   SET SALARY = 20000,
LASTNAME = 'HAWG'

      WHERE
INSSN = ‘334838999'

If the column does not have the NOT NULL
attribute, you can assign a null value. For example, this update changes middle
initial to NULL.


UPDATE INSTRUCT

   SET IMIDINIT = NULL

      WHERE
INSSN = ‘334838999'





*Updating Multiple Rows

You can also update more than one row in the table.
The database system updates any row which satisfies the WHERE clause.


Here is an example that assigns the same value to the GRADE column of all rows for English 101 (CLASSNO = E01), Section 1 (SECTIONNO = 001).

UPDATE ROSTER

   SET GRADE = 'C'

      WHERE
CLASSNO = 'E01' AND SECTIONNO = '001'




*Using Computed Values

The assigned value may also be a computed value. This
example increases the salary of all instructors by 8 percent.

UPDATE INSTRUCT

     SET SALARY =
SALARY * 1.08

Now, practice building an entire SQL update request.









  UPDATE STUDENT


       SET MAJOR = NULL

            WHERE
STUDENTNO = 'S10'

*The Completed Statement

Above is the complete UPDATE
request.

One last item to discuss about updating rows is the considerations that apply
when updating tables that are part of a referential constraint.



*Constraints Are Enforced

When updating a parent table...

• The updated value for a
  primary key must be unique and not null.
• All dependent rows must be
  updated or deleted before the parent is updated.

When updating a dependent table...

• The updated value for a
  foreign key must have a matching primary key in the parent table or be
  null.

*Updating Date and Time

You just learned how to use the UPDATE statement to alter the values in numeric
and character columns.


The basic rule was that character data must be enclosed in quotes and numeric
data must not. Very simple. The columns that get
updated are those specified in the SET
list. The rows that get updated are limited to those meeting conditions
specified in the WHERE clause.

Updating date and time columns is just as easy, but you have more flexibility
in how you specify the data to be inserted.



*Dates

The values for updating a date column can be in any
one of the valid date formats — USA, EUR, ISO,
or JIS. SQL can recognize the
different date formats.


For example:

UPDATE     CRUISES

  SET      

  WHERE    CRUISE_NAME
= 'BERMUDA'

Here, DEPARTURE_DATE is being
updated for a cruise to Bermuda. Note that,
like character data, date values are enclosed in quotes.




*Standard Date Formats

If you'll recall, the standard date formats are:

Examine the following table
Date Type	Date Format	Valid INSERT Value	Another Valid INSERT Value
USA	MM/DD/YYYY	02/09/1999	2/9/1999
EUR	DD.MM.YYYY	09.02.1999	9.2.1999
ISO JIS	YYYY-MM-DD	1999-02-09	1999-2-9

Leading zeroes for month and day can be omitted from update values, but the
year portion of a date must always be entered in full.





*Times

Time values, like date values, for updating columns can be in any one of the
valid time formats — USA, EUR, ISO,
or JIS. SQL can recognize the
different formats.

For example:


UPDATE     CRUISES

  SET      

  WHERE    CRUISE
= 'BERMUDA'

Here, DEPARTURE_TIME is given in
USA format. Notice, too,
that the time value, like character data, is enclosed in quotes.




*Standard Time Formats

Let's look at the standard time formats again.

Examine the following table
Time Type	Time Format	Valid INSERT Value	Another Valid INSERT Value
USA	HH:MM AM or PM	01:05 PM	1:05 PM
EUR ISO	HH.MM.SS	13.05.00	13.05
JIS	HH:MM:SS	13:05:00	13:05

Leading zeroes can be omitted for the HOUR
value only. The SECOND value is optional
in all formats. And remember, USA
format is based on 12 hours, not 24 hours.

Topic 5.2: The DELETE
Statement

*Correcting Mistakes

You can see that it is not difficult to update tables,
but it is also very easy to make invalid updates:

• If your WHERE clause is not specific enough
• If you mistype a value (SQL
  does not catch typos)
• If you update one table,
  but forget to update another one that contains the same columns

What if you want to delete a row? You use the SQL DELETE statement. Let's go over its format
and use.



*The Statement Format

Here is the format of the DELETE

statement.

DELETE FROM table-name  < WHERE search-condition >

This statement deletes all rows in the named table that satisfy the search
condition.

If you leave off the WHERE
clause, all rows are deleted. This example deletes all rows for English 101
Section 002 from the ROSTER

table.

DELETE FROM ROSTER WHERE CLASSNO = 'E01' AND
SECTIONNO = '002'



DELETE Rules

When deleting from a parent table, the effect on the dependent table is
determined by the DELETE rule
chosen when the dependent was created. Recall the three possible DELETE rules:

Examine the following table
RESTRICT	The parent row cannot be deleted if any associated dependent rows exist (default rule).
SET NULL	The parent row can be deleted; the foreign key of all associated dependent rows is set to null values.
CASCADE	The parent row can be deleted; the delete operation cascades through all associated dependent rows.

For dependent tables, no deletion rules apply.

Topic 5.3: Maintaining
and Using Tables

*You Create It, You Own It

When you create a table, it belongs to you, and you
are the only one allowed to access it. If you want others to share your table,
you must grant them access.

For example, this statement only allows SELECTs to be run against your
table by user A333.


GRANT SELECT ON TABLE STUDENT  TO A333



*Granting Privileges

You could also grant INSERT, DELETE, UPDATE
and other privileges.


To grant everything, use GRANT ALL.

To grant a privilege to EVERYBODY,
you grant TO PUBLIC:

GRANT ALL PRIVILEGES ON TABLE STUDENT
TO PUBLIC

The keyword PRIVILEGES

is optional. PUBLIC means to all
Ids.





*Revoking Privileges

As the creator of a table you GRANT privileges and you REVOKE them.


To take away someone's privileges:

REVOKE ALL ON STUDENT FROM PUBLIC

This example sweepingly revokes everybody's privileges — except your own, of
course.



*Adding Columns

In addition to granting and revoking privileges to others who use your table,
you can make one change to the format of the table: you can add new columns to
a table.

Use the SQL statement, ALTER, to
add a column to a table.




*The ALTER
Statement

ALTER TABLE STUDENT ADD RELIGION VARCHAR(20)

This statement adds a 20-character column called RELIGION to the table.

To enter values in the new column, you issue SQL UPDATE statements.






*Creating a Synonym

Assume you frequently use the INSTRUCT
table. If that name is too long or is not meaningful to you, you can create a
synonym, which you can think of as the table's nickname.

CREATE SYNONYM INS FOR INSTRUCT

From now on, you can reference INSTRUCT
as INS:


SELECT * FROM INS

A synonym can be as short as one character.



*Once They Have Outlived Their Usefulness...

When you no longer need a TABLE
or SYNONYM you can DROP it.


Destroying tables is far easier than creating them. To completely remove a
table from the database, enter the following statement.

DROP TABLE table-name



*Dropping a Table

When a table is dropped, the CREATE
statement is deleted, as well as all the rows. This differs from the DELETE statement which only deletes the
rows, leaving the table definition in effect.


In other words, if a DROP is
issued, you need to issue a CREATE
statement to re-establish the table definition.

Additionally, when you drop a table you delete the corresponding referential
constraints in which the table is a parent or a dependent.

Topic 5.4: Unit 5 Summary

In this unit, you learned the following:

• Use the SQL UPDATE statement to update existing
  values in one or more rows of a table. Here is the general format of the UPDATE statement.
  
  
  
  
  UPDATE table-name
  
    SET column-name=value
  <,column-name2=value2>...
  
     < WHERE search-condition
  
  >
• With the SET clause you can update two or more
  columns, assign NULL for
  columns that don't have the NOT NULL
  attribute, and assign computed values.
• The WHERE clause specifies the rows to be
  updated. If you omit the WHERE
  
  clause, all rows are updated.
• When updating a parent table,
  the updated value for a primary key must be unique and not null, and all
  dependent rows must be updated or deleted before the parent is updated.
  When updating a dependent table, the updated value for a foreign key must
  have a matching primary key in the parent table or be null.
• Use the SQL DELETE statement to delete rows from
  a table. Here is the format of the DELETE
  statement.
  
  
  
  
  DELETE FROM table-name  <
  WHERE search-condition >
• The WHERE clause specifies the rows to be
  deleted. If you omit the WHERE
  clause, all rows are deleted.
• When deleting from a parent
  table, the effect on the dependent table is determined by the DELETE rule chosen when the dependent
  was created. For dependent tables, no deletion rules apply.
• To allow others to share
  your table, use the GRANT
  statement.
• The REVOKE statement takes away someone's
  privileges.
• Use the SQL statement ALTER to add a column to a table.
• The CREATE SYNONYM statement allows you
  to create a synonym, which you can think of as the table's nickname.
• To completely remove a
  table from the database, use the SQL DROP
  statement. This is the format for the DROP
  
  statement.
  
  
  
  DROP TABLE table-name
• When you drop a table, you
  delete all rows in the table, the CREATE
  statement which defined the table, and corresponding referential
  constraints in which the table is a parent or dependent.