Please enable JavaScript.
Coggle requires JavaScript to display documents.
4.5 UNIT 4 T.5 Defining and updating tables using SQL (Altering a table…
4.5
UNIT 4 T.5 Defining and updating tables using SQL
Common Data Types
CHAR(n) :check:
Char string of fixed length n
ProductCode CHAR(6)
VARCHAR(n) :check:
Character string variable length, max. n
Surname VARCHAR(25)
BOOLEAN :check:
TRUE or FALSE
ReviewComplete BOOLEAN
INTEGER, INT :check:
Integer
Quantity INTEGER
FLOAT :check:
Number with a floating decimal point
Length FLOAT (10,2)
(maximum number of digits is 10 with max. 2 after decimal point)
DATE :check:
Stores Day, Month, Year values
HireDate DATE
TIME::check: :
Stores Hour, Minute, Second
RaceTime TIME
CURRENCY :check:
Formats numbers in the currency used in your region
EntryFee CURRENCY
DATA TYPE
DESCRIPTION
EXAMPLE
Altering a table structure
The ALTER TABLE statement is used to ADD, DELETE or MODIFY columns in an existing table.
To Add+ a new column:
ALTER
TABLE tblProduct
ADD
QtylnStock INTEGER
To Delete- a column:
ALTER
TABLE tblProduct
DROP
QtylnStock
To Change the data type of a column:
ALTER
TABLE tblproduct
MODIFY
COLUMN Description VARCHAR(30) NOT NULL
Inserting Date Using SQL
The
INSERT INTO
statement is used to insert a new record into a table, for example the Product table, which is defined as shown below:
•ProductID CHAR (4) NOT NULL PRIMARY KEY,
Description VARCHAR (20) NOT NULL,
Price CURRENCY
Insert a new record for ID A345, “Pink rabbit”, £7.50:
•INSERT INTO Product (ProductID, Description, Price)
VALUES (“A345”, “Pink Rabbit”, 7.50)
Note that you do not need to specify the field names in the top line if data is being added to all fields
Updating data using SQL
The
UPDATE
statement is used to update a record in a table, for example the Product table:
ProductID CHAR(4) NOT NULL PRIMARY KEY,
Description VARCHAR(20) NOT NULL,
Price CURRENCY
To update record for product ID A345, changing the description to “Blue Rabbit” and the price to £8.25:
UPDATE
Product
SET
Description ="Blue Rabbit", Price=8.25
WHERE
ProductID ="A345"
Deleting a record using SQL
The
DELETE
statement is used to delete a record in a table, for example the Product table:
ProductID CHAR(4) NOT NULL PRIMARY KEY,
Description VARCHAR(20) NOT NULL,
Price CURRENCY
To delete record for product ID A345:
DELETE
FROM Product
WHERE
ProductID = "A345"