Column data types (INT, VARCHAR, DATE, DECIMAL) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define an integer column named 'age'.

SQL

CREATE TABLE people (age [1]);

Drag options to blanks, or click blank then click option'

AVARCHAR(100)

BINT

CDATE

DDECIMAL(5,2)

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR for numeric data.

Using DATE for age.

The INT data type is used to store whole numbers like age.

2fill in blank

medium

Complete the code to define a column 'name' that stores text up to 50 characters.

SQL

CREATE TABLE users (name [1]);

Drag options to blanks, or click blank then click option'

AINT

BDATE

CVARCHAR(50)

DDECIMAL(10,2)

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT for text data.

Using DATE for names.

VARCHAR(50) stores variable-length text up to 50 characters.

3fill in blank

hard

Fix the error in the code to define a 'birthdate' column storing dates.

SQL

CREATE TABLE employees (birthdate [1]);

Drag options to blanks, or click blank then click option'

ADATE

BINT

CDECIMAL(8,2)

DVARCHAR(10)

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR for dates.

Using INT for dates.

The DATE type stores calendar dates like birthdate.

4fill in blank

hard

Fill both blanks to define a 'price' column with decimal numbers having 7 digits total and 2 after the decimal point.

SQL

CREATE TABLE products (price [1]([2], 2));

Drag options to blanks, or click blank then click option'

ADECIMAL

B50

C7

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT for price.

Using wrong precision number.

DECIMAL(7, 2) stores numbers with 7 digits total and 2 decimal places.

5fill in blank

hard

Fill all three blanks to create a table 'orders' with columns: 'order_id' as integer, 'customer_name' as text up to 100 chars, and 'order_date' as date.

SQL

CREATE TABLE orders (order_id [1], customer_name [2]([3]), order_date DATE);

Drag options to blanks, or click blank then click option'

AINT

BVARCHAR

C100

DDECIMAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using DECIMAL for order_id.

Using INT for customer_name.

order_id is INT, customer_name is VARCHAR(100), order_date is DATE.

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