In this section: Example: |
A table is a rectangular array of data elements that can be viewed as either a collection of rows or a collection of columns. Every table is managed by a DBMS and accessed using the server.
The columns of a table have an inherent order; its rows do not. Every table must have at least one column but the number of rows can be zero. A table with no rows is said to be empty. If R is the number of rows in a table, and C is the number of columns, each column would have R values, and each row would consist of a sequence of C values arranged in column order.
CREATE is used to bring transient tables or views into existence, INSERT is used to insert a row or a block of rows into a table or view, and DROP is used to explicitly remove transient tables and views from the environment. Since the lifetime of a transient table is limited by the duration of a user session (that period of time during which the application maintains an active connection with the server), DROP is seldom required.
Each column of a table has an associated data type. A data type determines the range of values (domain) that can be assigned to the elements of a column and the manner in which the elements can be manipulated and displayed.
Columns can be created to contain null values. When a data element is null, we can infer that either a value has not been assigned to it, its value is unknown, or the column is nonapplicable. The existence of null values impacts system behavior in various ways. Comparisons involving null values, for example, are always false (except comparisons using the null predicate). Column functions, like SUM, operating on particular columns, ignore null values. But COUNT(*) counts every row, even if a row contains null values.
Every column of a table has a name. The name must begin with an alphabetic character (a-z). The rest of the name can contain numbers and the following special characters only:
#
$
@
.
-
CREATE TABLE creates a new database table
CREATE TABLE <table-name> (<column-definition> [,<column- definition>]*)
[<default-clause>] [<constraint>]
constraint ::= NOT NULL [UNIQUE]
default-clause ::= DEFAULT {<literal> | NULL}
default ::= <literal> | NULL
column-definition ::= <column-name> <data-type>
data-type ::= <string-data-type> | <numeric-data-type>
numeric-data-type ::= NUMERIC[<pr-sc>] | {DECIMAL | DEC} [<pr-sc>]
| INTEGER | INT | SMALLINT | FLOAT | REAL | DOUBLE PRECISION
pr-sc ::= (<precision>[,<scale>])
where:
CHAR(n) | Fixed-length alphanumeric, where n is less than 254. |
DATE | Date data types are used to store calendar dates. |
INTEGER | Four-byte binary integer. |
DECIMAL(p,s) | Packed decimal containing p digits with an implied number s of decimal points. |
REAL | Four-byte, single-precision floating point. |
FLOAT | Eight-byte, double-precision floating point. |
The server creates a table when it receives a CREATE TABLE request. It requires that the table name be unique (for example, that it not conflict with the name of an existing table) and that it conform to the lexical constraints imposed on short identifiers (for example, that it consist of the number of characters supported, which typically is eight or less characters).
The number of possible columns in a table is 254. Any server data type can be assigned to a given column.
CREATE TABLE PERSON
(
PID CHAR(8) NOT NULL,
FIRST_NAME CHAR(20),
LAST_NAME CHAR(20),
ADDRESS CHAR(40),
CITY CHAR(40),
STATE CHAR(2),
DOB,
DATE,
FLOOR,
INT,
SALARY,
DECIMAL,
)
iWay Software |