Column data types (INT, VARCHAR, DATE, DECIMAL) in SQL - Step-by-Step Execution

Concept Flow - Column data types (INT, VARCHAR, DATE, DECIMAL)

Define Table

↓

Choose Column Name

↓

Select Data Type

↓

Set Constraints (optional)

↓

Create Table with Columns

↓

Insert Data matching types

↓

Query Table to see stored data

This flow shows how to define columns with specific data types when creating a table, then insert and query data matching those types.

Execution Sample

SQL

CREATE TABLE Products (
  ProductID INT,
  ProductName VARCHAR(20),
  ReleaseDate DATE,
  Price DECIMAL(6,2)
);

INSERT INTO Products VALUES (1, 'Pen', '2024-01-15', 1.50);

Creates a table with columns of different data types and inserts one row of matching data.

Execution Table

Step	Action	Details	Result
1	Create Table	Define ProductID as INT	Column ProductID created with INT type
2	Create Table	Define ProductName as VARCHAR(20)	Column ProductName created with VARCHAR(20) type
3	Create Table	Define ReleaseDate as DATE	Column ReleaseDate created with DATE type
4	Create Table	Define Price as DECIMAL(6,2)	Column Price created with DECIMAL(6,2) type
5	Insert Data	Insert (1, 'Pen', '2024-01-15', 1.50)	Row inserted successfully
6	Query Data	SELECT * FROM Products	Returns: 1 | Pen | 2024-01-15 | 1.50

💡 All steps completed successfully; data types enforced on insert and query

Variable Tracker

Variable	Start	After Step 5	Final
ProductID	undefined	1	1
ProductName	undefined	'Pen'	'Pen'
ReleaseDate	undefined	'2024-01-15'	'2024-01-15'
Price	undefined	1.50	1.50

Key Moments - 3 Insights

Why must ProductName be VARCHAR(20) and not INT?

Because ProductName stores text (like 'Pen'), VARCHAR allows characters, while INT only stores numbers. See execution_table rows 2 and 5 where the type is set and data inserted.

What happens if we try to insert a date in wrong format?

The database will reject it or error because ReleaseDate expects DATE format 'YYYY-MM-DD'. This is shown in execution_table row 5 where correct format is used.

Why is Price DECIMAL(6,2) instead of INT?

Price can have cents (decimal places), so DECIMAL(6,2) stores numbers with 2 digits after decimal. INT would only store whole numbers. See execution_table rows 4 and 5.

Visual Quiz - 3 Questions

Look at the execution_table, what data type is assigned to the ReleaseDate column?

A INT

B VARCHAR(20)

C DATE

D DECIMAL(6,2)

💡 Hint

Check Step 3 in the execution_table where ReleaseDate is defined.

At which step is the row with ProductID = 1 inserted?

A Step 5

B Step 6

C Step 3

D Step 2

💡 Hint

Look for the Insert Data action in the execution_table.

If we changed ProductName to INT type, what would happen when inserting 'Pen'?

A Insert succeeds with 'Pen' stored as text

B Insert fails due to type mismatch

C Insert converts 'Pen' to 0 automatically

D Insert stores 'Pen' as NULL

💡 Hint

Refer to key_moments about data type mismatch on insert.

Concept Snapshot

Column data types define what kind of data a column holds.
INT stores whole numbers.
VARCHAR(n) stores text up to n characters.
DATE stores calendar dates in 'YYYY-MM-DD' format.
DECIMAL(p,s) stores numbers with precision p and scale s (digits after decimal).
Data types ensure data is stored correctly and prevent invalid data.

Full Transcript

This visual execution shows how to create a SQL table with columns of different data types: INT for whole numbers, VARCHAR for text, DATE for dates, and DECIMAL for precise decimal numbers. Each step defines a column with its type. Then a row is inserted with matching data types. Finally, a query returns the stored data. The variable tracker shows how each column's value changes from undefined to the inserted data. Key moments clarify why each data type is chosen and what happens if data does not match the type. The quiz tests understanding of data types and their enforcement during insert and query.