Theory
Building the table, for real
Meera designed her tables on paper in Unit 3. Now she builds one in a real database, and the very first decision for every column is: what kind of value goes here?
Item names are text. Prices are numbers with paise. Quantities are whole numbers. Dates are dates. SQL makes you declare this up front, and picking wrong wastes space or corrupts data.
If this feels familiar, it should: it is exactly the data types idea from BCA104 C, now for database columns. Here is the shop's sales table you will use all unit.
Theory
Labelled containers in the kitchen
Meera's kitchen has containers labelled by content: a jar for sugar, a bottle for oil, a rack for spice boxes. You would not pour oil into the spice rack. A data type is that label on a column: 'this column holds whole numbers', 'this one holds text up to 30 characters'. The label guarantees only the right kind of thing goes in, and the database can store it efficiently.
At a glance
Common SQL data types
| Type | Holds | Note |
|---|---|---|
| INT / INTEGER | Whole numbers | qty, ids |
| FLOAT / DOUBLE / NUMBER | Decimals | NUMBER(6,2) for money |
| CHAR(n) | FIXED-length text | Padded; fixed codes |
| VARCHAR / VARCHAR2(n) | VARIABLE-length text | The usual choice |
| DATE | Dates (sometimes + time) | sale_date |
Practical
The sales table you will use all unit
-- Our running example: Meera's sales table
-- id | item | category | price | qty
-- 1 | Sugar | Grocery | 45 | 20
-- 2 | Tea | Beverage | 120 | 15
-- 3 | Rice | Grocery | 60 | 30
-- 4 | Soap | Personal | 35 | 25
-- 5 | Milk | Dairy | 30 | 40
-- Its column types:
-- id INT
-- item VARCHAR(30)
-- category VARCHAR(20)
-- price INT
-- qty INTTheory
CHAR vs VARCHAR: the key pair
The most-tested distinction is CHAR vs VARCHAR:
- CHAR(n) is fixed length:
CHAR(10)always uses 10 characters, padding shorter values with spaces. Good for values that are always the same length (a state code 'GJ', a fixed PIN). Wastes space otherwise. - VARCHAR(n) is variable length:
VARCHAR(30)reserves up to 30 but stores only what you enter. 'Tea' takes 3, not 30. The usual choice for names and free text.
Fixed vs variable: pad-to-full versus store-what-you-need.
Quiz
Meera stores item names like 'Tea', 'Sugar', 'Toothpaste' (very different lengths). Which type is the better choice?
- VARCHAR, it stores only the actual length of each name
- CHAR, it pads every name to full length
- INT, names are stored as numbers
- DATE, because items change over time
Show the answer
VARCHAR, it stores only the actual length of each name
Item names vary in length, so VARCHAR stores each at its real size ('Tea' = 3 chars), saving space. CHAR would pad every name to the fixed width, wasting space for short names. CHAR only wins when values are always the same length (fixed codes). This CHAR-vs-VARCHAR reasoning is a guaranteed exam question.
Think first
Why type the price column carefully?
Meera stores prices as INT in our example, but many prices have paise (45.50). If she keeps INT, what happens to 45.50? Which type should a money column really use?
Show the answer
INT truncates the decimal, 45.50 would store as 45, losing the paise (exactly the int-truncation from BCA104). For money, use a decimal type: NUMBER(6,2) (Oracle) or DECIMAL(6,2) (MySQL), meaning up to 6 digits with 2 after the point. (Our running table uses whole-rupee prices to keep the arithmetic clean, but real money columns need decimals.) Choosing the type to match the real data is the whole skill.
Watch out
Where marks leak
Swapping CHAR (fixed, padded) and VARCHAR (variable, efficient), the classic pair. Using INT for money (truncates paise), use NUMBER/DECIMAL. Forgetting VARCHAR2 is Oracle's name for VARCHAR. And storing dates as text instead of DATE (loses date arithmetic, the serial-number trick from Unit 2). Match the type to the data, and name the CHAR-vs-VARCHAR difference precisely.
Theory
One idea, three subjects
Data types now appear in your third subject: C variables (BCA104), Excel number formats (Unit 1), and SQL columns. The lesson is universal: computers must know what kind of value they hold. Meera's sales table is now ready to build and fill, which is exactly the next lesson: the CREATE, INSERT, and the whole DDL/DML/DQL command family. You are about to write your first SQL.
Summary
Key takeaways
- Every SQL column declares a data type fixing what it can hold.
- INT for whole numbers; FLOAT/DOUBLE/NUMBER(p,s) for decimals (money needs decimals).
- CHAR(n) is FIXED length (padded); VARCHAR/VARCHAR2(n) is VARIABLE length (efficient, the usual choice).
- DATE stores dates; storing dates as text loses date arithmetic.
- Choosing the right type prevents wasted space and lost data (INT truncates paise).
- Memory hook: labelled containers, CHAR pads to full, VARCHAR stores what you pour in.