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.

Practice

1. Which SQL data type is best suited to store a person's full name?

easy

A. DECIMAL

B. INT

C. DATE

D. VARCHAR

Solution

1. Step 1: Understand the data type purpose

   VARCHAR is used to store text or strings, like names.

2. Step 2: Match data type to data

   A person's full name is text, so VARCHAR fits best.

3. Final Answer:

   VARCHAR -> Option D

4. Quick Check:

   Text data = VARCHAR [OK]

Hint: Use VARCHAR for any text data like names or addresses [OK]

Common Mistakes:

• Choosing INT for text
• Using DATE for names
• Using DECIMAL for text

2. Which of the following is the correct way to declare a column for storing a price with two decimal places in SQL?

easy

A. price DATE(10,2)

B. price DECIMAL(10,2)

C. price INT(10,2)

D. price VARCHAR(10,2)

Solution

1. Step 1: Identify the data type for decimal numbers

   DECIMAL is used for numbers with fixed decimal places, like prices.

2. Step 2: Check syntax correctness

   DECIMAL(10,2) means total 10 digits, 2 after decimal point, which is correct.

3. Final Answer:

   price DECIMAL(10,2) -> Option B

4. Quick Check:

   Decimal numbers = DECIMAL(precision, scale) [OK]

Hint: Use DECIMAL(precision, scale) for money or precise decimals [OK]

Common Mistakes:

• Using VARCHAR for numbers
• INT with decimals
• DATE for numeric values

3. Given the table definition:
CREATE TABLE Orders (OrderID INT, OrderDate DATE, CustomerName VARCHAR(50), TotalAmount DECIMAL(8,2));
What will be the result of this insert?
INSERT INTO Orders VALUES (101, '2024-05-10', 'Alice', 123.456);

medium

A. The TotalAmount will store 123.46, rounding to 2 decimals

B. The TotalAmount will store 123.456 exactly

C. The insert will fail due to decimal precision error

D. The TotalAmount will store 123

Solution

1. Step 1: Understand DECIMAL(8,2) behavior

   DECIMAL(8,2) stores numbers with 2 decimal places, rounding if needed.

2. Step 2: Analyze the inserted value 123.456

   Since only 2 decimals allowed, 123.456 rounds to 123.46.

3. Final Answer:

   The TotalAmount will store 123.46, rounding to 2 decimals -> Option A

4. Quick Check:

   DECIMAL rounds to defined decimals [OK]

Hint: DECIMAL rounds extra decimals to defined scale [OK]

Common Mistakes:

• Expecting exact 3 decimals stored
• Thinking insert fails
• Assuming truncation without rounding

4. You have this table creation statement:
CREATE TABLE Employees (ID INT, JoinDate VARCHAR(10));
Why might storing dates as VARCHAR cause problems?

medium

A. Because VARCHAR does not enforce date format, leading to inconsistent data

B. Because INT is better for dates

C. Because VARCHAR cannot store text

D. Because DATE type is slower than VARCHAR

Solution

1. Step 1: Understand VARCHAR vs DATE for dates

   VARCHAR stores text without checking if it is a valid date.

2. Step 2: Identify problems with VARCHAR for dates

   Data can be inconsistent or invalid, causing errors in date operations.

3. Final Answer:

   Because VARCHAR does not enforce date format, leading to inconsistent data -> Option A

4. Quick Check:

   Use DATE type to enforce valid dates [OK]

Hint: Use DATE type to keep dates consistent and valid [OK]

Common Mistakes:

• Thinking INT stores dates
• Believing VARCHAR can't store text
• Assuming DATE is slower

5. You want to create a table to store product details including:
- Product ID as a whole number
- Product name up to 100 characters
- Release date
- Price with up to 7 digits total and 2 decimals

Which is the best SQL column definition for the Price column?

hard

A. Price VARCHAR(7)

B. Price INT

C. Price DECIMAL(7,2)

D. Price DATE

Solution

1. Step 1: Understand the requirements for Price

   Price needs to store numbers with 2 decimal places and up to 7 digits total.

2. Step 2: Match data type to requirements

   DECIMAL(7,2) fits perfectly: 7 digits total, 2 after decimal point.

3. Step 3: Eliminate wrong options

   INT stores whole numbers only, VARCHAR stores text, DATE is for dates.

4. Final Answer:

   Price DECIMAL(7,2) -> Option C

5. Quick Check:

   Use DECIMAL for precise decimal numbers [OK]

Hint: Use DECIMAL(total_digits, decimals) for prices [OK]

Common Mistakes:

• Using INT for prices with decimals
• Using VARCHAR for numbers
• Confusing DATE with price