Defining and updating tables using SQL

Creating a new table

SQL can be used to define and create new tables as well as search them. An example of using SQL to make a table

CREATE TABLE tblProduct
(
ProductID CHAR(4) NOT NULL PRIMARY KEY,
Description VARCHAR(20) NOT NULL,
Price CURRENCY
) 

Any primary keys must be defined and cannot be left blank

 Common data types

Altering a table structure

to alter a table the ALTER TABLE statement is used this allows you to add, delete or modify columns in an existing table

ALTER TABLE tblProduct
ADD QtyInStock INTEGER

Delete a column

ALTER TABLE tblProduct
DROP QtyInStock

Change data types

ALTER TABLE tblProduct
MODIFY COLUMN Desciption VARCHAR (30) NOT NULL

Defining linked tables

to create a links between tables you need to define the foreign keys.

CREATE TABLE ProductComponent
( 
ProductID    CHAR(4) NOT NULL,
CompID    CHAR(6) NOT NULL,
Quantity    INTEGER,
FOREIGN KEY     ProductID REFERENCES Product(ProductID),
FOREIGN KEY     CompID REFERENCES Component(CompID),
PRIMARY KEY     (ProductID, CompID)
)

Inserting data using SQL

The INSERT INTO statement is used to insert a record into a table.

Product table

ProductID    CHAR(4) NOT NULL PRIMARY KEY,
Description    VARCHAR(20) NOT NULL,
Price    CURRENCY

Insert a record

insert a new record for ID A345, “Pink rabbit”, £7.50:

INSERT INTO Product (ProductID, Description, Price),
VALUES (“A345”, “Pink Rabbit”, 7.50)

Field names do not need to be specified is it is being added to all fields.

Deleting a record

To delete a record use the DELETE statement

To delete record for product ID A345:

DELETE FROM Product
WHERE ProductID = “A345”