Creating a SQL Database:
Before you can store data in a database, you must first define the structure of the data.
Suppose you want to expand the sample database by adding a table of data about the
products sold by your company. For each product, the data to be stored includes:
• a three-character manufacturer ID code,
• a five-character product ID code,
• a description of up to thirty characters,
• the price of the product, and
• the quantity currently on hand.
This SQL CREATE TABLE statement defines a new table to store the products data:
CREATE TABLE PRODUCTS
(MFR_ID CHAR(3),
PRODUCT_ID CHAR(5),
DESCRIPTION VARCHAR(20),
PRICE MONEY,
QTY_ON_HAND INTEGER)
Table created.
Although more cryptic than the previous SQL statements, the CREATE TABLE statement
is still fairly straightforward. It assigns the name PRODUCTS to the new table and specifies
the name and type of data stored in each of its five columns.
Once the table has been created, you can fill it with data. Here's an INSERT statement
for a new shipment of 250 size 7 widgets (product ACI-41007), which cost $225.00
apiece:
INSERT INTO PRODUCTS (MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE,
QTY_ON_HAND)
VALUES ('ACI', '41007', 'Size 7 Widget', 225.00, 250)
1 row inserted.
Finally, if you discover later that you no longer need to store the products data in the
database, you can erase the table (and all of the data it contains) with the DROP TABLE
statement:
DROP TABLE PRODUCTS
Table dropped.
