Informatics Practices · Ch 7 — Introduction to Structured Query Language (SQL)
Data type of Attribute
Data type of Attribute
A data type indicates the type of data value that an attribute can have — and choosing it is more consequential than it looks, because the data type of an attribute decides which operations can be performed on that attribute's data. Arithmetic, for instance, can be performed on numeric data but not on character data: a column of marks stored as numbers can be totalled, while the same digits stored as text cannot.
MySQL's commonly used data types fall into three families: numeric types, date and time types, and string (character and byte) types. The specific types a Class XI student works with are these (the book lists them as Table 8.1):
| Data type | What it stores |
|---|---|
| CHAR(n) | Fixed-length character data of length n, where n can be any value from 0 to 255 |
| VARCHAR(n) | Variable-length character data, where n can be any value from 0 to 65535 |
| INT | An integer value, occupying 4 bytes |
| FLOAT | Numbers with decimal points, occupying 4 bytes |
| DATE | A calendar date in 'YYYY-MM-DD' format |
The details of each deserve attention, because exam questions turn on them.
CHAR(n) is fixed length. Declaring CHAR(10) reserves space for 10 characters no matter what is stored. If the actual data is shorter — the word 'city' has only four characters — MySQL fills the remaining 6 character positions with spaces, padded on the right. The declared width is always fully occupied.
VARCHAR(n) stores character data too, but it is a variable-length type, and its limit is far larger: n can range from 0 to 65535. Declaring VARCHAR(30) means at most 30 characters can be stored, but the bytes actually allocated depend on the length of the string entered. So 'city' placed in a VARCHAR(30) column occupies only the space needed for its 4 characters — nothing is padded.
The CHAR-versus-VARCHAR contrast is the classic comparison: same kind of data, opposite storage behaviour. Ask yourself which attributes genuinely have a fixed length — a code that is always exactly the same number of characters suits CHAR, while names and addresses of unpredictable length suit VARCHAR.
INT holds integer values. Each INT value occupies 4 bytes of storage, which fixes its range: from -2147483648 to 2147483647. When values larger than this range are needed, the type to use is BIGINT, which occupies 8 bytes instead. …
| Data type | Description |
|---|---|
| CHAR(n) | Specifies character type data of length n where n could be any value from 0 to 255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve 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. |
| VARCHAR(n) | 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 (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string. So 'city' in VARCHAR (30) will occupy the space needed to store 4 characters only. |
| INT | INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of values allowed in integer type are -2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes. |
| FLOAT | Holds numbers with decimal points. Each FLOAT value occupies 4 bytes. |