Database Reference
In-Depth Information
The following CREATE VIEW command creates a view named Housewares, which consists of the part
number, description, on hand, and price for all rows in the Part table on which the Class is HW:
351
CREATE VIEW Housewares AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class
¼
'
HW
'
;
DATA TYPES
Figure B-5 describes the data types that you can use in a CREATE TABLE command.
Data Type
Description
INTEGER
Stores integers, which are numbers without a decimal part. The valid data range is -2147483648 to
2147483647. You can use the contents of INTEGER ields for calculations.
SMALLINT
Stores integers but uses less space than the INTEGER data type. The valid data range is -32768 to
32767. SMALLINT is a better choice than INTEGER when you are certain that the ield will store
numbers within the indicated range. You can use the contents of SMALLINT ields for calculations.
DECIMAL(
p
,
q
)
Stores a decimal number
p
digits long with
q
of these digits being decimal places. For example,
DECIMAL(5,2) represents a number with three places to the left and two places to the right of the
decimal. You can use the contents of DECIMAL ields for calculations.
CHAR(
n
)
Stores a character string
n
characters long. You use the CHAR type for ields that contain letters and
other special characters and for ields that contain numbers that will not be used in calculations. Because
neither sales rep numbers nor customer numbers will be used in any calculations, for example, both of
them are assigned CHAR as the data type. (Some DBMSs, such as Access, use TEXT rather than
CHAR, but the two data types mean the same thing.)
DATE
Stores dates in the form DD-MON-YYYY or MM/DD/YYYY. For example, May 12, 2013, could be
stored as 12-MAY-2013 or 5/12/2013.
FIGURE B-5
Data types
DELETE ROWS
Use the DELETE command to delete one or more rows from a table. Figure B-6 describes the DELETE
command.
Clause
Description
Required?
DELETE FROM
table name
Indicates the name of the table from which the
row or rows are to be deleted.
Yes
WHERE
condition
Indicates a condition. Those rows for which the
condition is true will be retrieved and deleted.
No (If you omit the WHERE
clause, all rows will be deleted.)
FIGURE B-6
DELETE command
The following DELETE command deletes any row from the OrderLine table on which the part number is
BV06:
DELETE
FROM OrderLine
WHERE PartNum
¼'
BV06
'
;
