Page 168 - IT-802_class_12
P. 168
1.4.4 Creating a Table
A table is a collection of organised data in the form of rows and columns. It is also known as a relation. The CREATE
TABLE command is used to create a table in SQL.
Syntax:
CREATE TABLE Table_Name
(
COLUmN_NAmE1 DATA_TypE (SizE), COLUmN_NAmE2 DATA_TypE (SizE), COLUmN_NAmE3
DATA_TypE (SizE),
………………………
COLUmN_NAmEN DATA_TypE (SizE)
);
The name of the table you want to construct is put after the keyword CREATE TABLE. The column definition is then
written between parenthesis and comprises the column name, its data type, and any optional constraints. As many
columns as you need may be added to the table, separated by a comma (,).
Some of the commonly used data types are as follows:
Data type Description
It specifies character type data of length n, where n can be any number between
0 and 255.
CHAR(n) It is of fixed length, which means, declaring CHAR (10) implies reserving spaces
for 10 characters. If data does not have 10 characters (for example, city has four
characters), MySQL fills the remaining 6 characters with spaces padded on the right.
It specifies character type data of length n where n could be any value from 0 to
65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, declaring
VARCHAR(n) VARCHAR (30) means a maximum of 30 characters can be stored but the actually
allocated bytes will depend on the length of the entered string. So city in VARCHAR
(30) will occupy the space needed to store 4 characters only.
It specifies an integer value. The range of values allowed in integer type
INT or INTEGER
is 2147483648 to 2147483647. Each INT value occupies 4 bytes
FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
DOUBLE Specifies a normal-size floating point number.
The DATE type is used for dates in the ‘YYYY-MM-DD’ format. YYYY is the 4-digit
DATE year, MM is the 2-digit month and DD is the 2-digit date. The supported range is
‘1000-01- 01’ to ‘9999-12-31’.
The TIME type is used for dates in the ‘‘HH:MM: SS’’ format. HH is the 2-digit hour,
TIME MM is the 2-digit month and DD is the 2-digit date. The supported TIME values may
range from ‘-838:59:59’ to ‘838:59:59’.
166 Touchpad Information Technology-XII

