Page 249 - PYTHON-12

CHARACTER VARCHAR(x) This datatype is used to store variable length alphanumeric data. For
(variable length) or example, address of a student can be declared as VARCHAR(25) to store
VARCHAR2(x) the address up to 25 characters long. The advantage of using this datatype
is that VARCHAR will not leave unused spaces. It releases the unused
memory spaces.
DATE DATE This datatype is used to store a date in ‘yyyy/mm/dd’ format. It stores year,
month and date values. DATE values can be compared with each other
only. The date values to be entered are to be enclosed in { } or with single
quotation marks.
TIME TIME This datatype is used to store time in hh:mm:ss format. It stores hour,
minute and second values. For example, a time of day can be taken as
12:30:45p.m. where 12 means hours, 30 means minutes and 45 refers to
seconds.
BOOLEAN (logical) BOOLEAN This datatype is used for storing logical values, either true or false. In both
upper and lower case, T or Y stands for logical true and F or N stands for
logical false. The fields with Boolean (logical) datatype can be compared
only with other logical columns or constants.
BLOB/RAW/ LONG BLOB This datatype can store data up to a maximum length of 65535 characters.
RAW or RAW BLOBs are “Binary Large Objects” and used to store a large amount of
or data such as images, animations, clips or other types of files. For example,
LONG RAW image raw(2000);
MEMO/LONG MEMO This datatype allows storing characters or remarks up to 2 GB per record.
or LONG

Table 12.2 Difference between CHAR and VARCHAR datatypes
CHAR VARCHAR
1. CHAR datatype provides fixed length memory storage. 1. VARCHAR datatype provides variable length
It specifies a fixed length character string. memory storage. It specifies a variable length string
(changeable).
2. The CHAR datatype can store a maximum of 0 to 255 2. The VARCHAR datatype can store a maximum number
characters. up to 65,535.
3. CHAR datatype is used when the data entries in a 3. VARCHAR datatype is used when the data entries in a
column are expected to be of the same size. column are expected to vary considerably in size.
4. CHAR(x) will take x characters of storage even if you 4. VARCHAR(x) will take only the required storage for the
enter less than x characters to that column. actual number of characters entered to that column.
5. If a value entered is shorter than its length x, then 5. No blanks are added if the length is shorter than the
blanks are added. maximum length, x.
6. CHAR datatype takes memory space of 1 byte per 6. VARCHAR takes up memory space of 1 byte per
character. character, +2 bytes to hold variable length information.
7. Search operation is faster with CHAR datatype 7. Search operation works slower in VARCHAR datatype
column. column as compared to CHAR type.
8. For example, name char(10); 8. For example, name varchar(10);
name="anu"; name="anu";
name field occupies 0 bytes, with the first three bytes then name occupies only 3+2=5 bytes, the first three
with values and the rest with blank data. bytes for value and the other two bytes for variable
length information.

CTM: While defining datatype for columns or attributes in a relation, two points should be kept in mind:
1. When using fixed length data in columns like phone number, area code, use character datatype.
2. When using variable length data in columns like name, address, designation, use varchar datatype.

12.13 SQL COMMANDS Relational Database and SQL

SQL provides a predefined set of commands that help us to work with relational databases. Before
discussing these commands, we must be familiar with the conventions and basic terminologies
related to SQL. Throughout this chapter, the words keyword, clause and statement have been used.
12.19