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

Data type	Description	Example
CHAR(n)	Char string of fixed length n	ProductCode CHAR(6)
VARCHAR(n)	Character string variable length, max. n	Surname VARCHAR(25)
BOOLEAN	TRUE or FALSE	ReviewComplete BOOLEAN
INTEGER, INT	Integer	Quantity INTEGER
FLOAT	Number with a floating decimal point	Length FLOAT (10,2) (maximum number of digits is 10 with max. 2 after decimal point)
DATE	Stores Day, Month, Year values	HireDate DATE
TIME	Stores Hour, Minute, Second	RaceTime TIME
CURRENCY	Formats numbers in the currency used in your region	EntryFee CURRENCY

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”