Column data types (INT, VARCHAR, DATE, DECIMAL) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SQL query with different data types?

Consider a table products with columns: id INT, name VARCHAR(10), price DECIMAL(5,2), and release_date DATE. The table has one row: (1, 'Gadget', 99.99, '2023-01-15'). What will this query return?

SELECT id, name, price, release_date FROM products;

SQL

SELECT id, name, price, release_date FROM products;

A 1 | Gadget | 99.99 | 2023/01/15

B 1 | Gadget | 99.99 | 2023-01-15

C 1 | Gadget | 100 | 2023-01-15

D 1 | Gadget | 99.9900 | 15-01-2023

💡 Hint

Remember that DECIMAL(5,2) stores two digits after the decimal point exactly, and DATE format is YYYY-MM-DD.

The DECIMAL(5,2) type stores numbers with two decimal places exactly, so 99.99 is stored as is. The DATE type stores dates in 'YYYY-MM-DD' format. VARCHAR(10) stores the string as given. So the output matches option B exactly.

🧠 Conceptual

intermediate

Which data type is best for storing a person's birth date?

You want to store a person's birth date in a database column. Which data type should you choose to store this information correctly and efficiently?

A DATE

B VARCHAR(10)

C INT

D DECIMAL(8,0)

💡 Hint

Think about what kind of data a birth date is and how databases store dates.

The DATE data type is designed to store calendar dates efficiently and allows date functions to be used. VARCHAR stores text, INT stores numbers without date context, and DECIMAL is for precise numbers, not dates.

📝 Syntax

advanced

Identify the syntax error in this table creation statement

Which option contains a syntax error in the SQL statement below?

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(50),
  order_total DECIMAL(7,2),
  order_date DATE
);

SQL

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(50),
  order_total DECIMAL(7,2),
  order_date DATE
);

A CREATE TABLE orders (order_id INT, customer_name VARCHAR(50), order_total DECIMAL(7,2), order_date DATE,);

B CREATE TABLE orders (order_id INT, customer_name VARCHAR(50), order_total DECIMAL(7,2), order_date DATETIME);

C CREATE TABLE orders (order_id INT, customer_name VARCHAR(50), order_total DECIMAL(7,2), order_date DATE);

D CREATE TABLE orders (order_id INT, customer_name VARCHAR(50), order_total DECIMAL(7,2), order_date DATE NOT NULL);

💡 Hint

Check for trailing commas in the column list.

Option A has a trailing comma after the last column definition, which causes a syntax error in SQL. The other options are syntactically correct.

❓ optimization

advanced

Choose the most storage-efficient data type for storing product prices up to 9999.99

You need to store product prices that can be up to 9999.99 dollars. Which data type is the most storage-efficient and appropriate for this use case?

A VARCHAR(10)

B INT

C DECIMAL(7,2)

D DECIMAL(5,2)

💡 Hint

Consider the maximum value and decimal places needed.

DECIMAL(7,2) can store numbers up to 99999.99, which covers 9999.99 easily. DECIMAL(5,2) only stores up to 999.99, which is too small. INT cannot store decimals. VARCHAR wastes space and is not numeric.

🔧 Debug

expert

Why does this INSERT statement fail for a DECIMAL column?

Given a table sales with a column amount DECIMAL(4,2), why does this statement fail?

INSERT INTO sales (amount) VALUES (123.456);

SQL

INSERT INTO sales (amount) VALUES (123.456);

A The syntax of the INSERT statement is incorrect.

B The value 123.456 has more decimal places than allowed by DECIMAL(4,2).

C DECIMAL(4,2) does not allow decimal values, only integers.

D The integer part 123 is too large for DECIMAL(4,2).

💡 Hint

DECIMAL(4,2) means total 4 digits with 2 after decimal point.

DECIMAL(4,2) means total 4 digits, 2 after decimal. So max value is 99.99. The integer part 123 is too large to fit. The decimal places (3) is more than 2, but the main error is integer part size.