Data Types — SQL Questions with Answers

Answer: A — It enforces valid values, determines storage size, and defines how comparisons and sorting behave

A DATE column rejects 'hello' and sorts chronologically; a VARCHAR column would accept anything and sort alphabetically. Types are your first line of data quality.

Data typesIntegrity

Answer: A — INTEGER (INT)

INT typically holds about +/- 2.1 billion. Use BIGINT for larger values and SMALLINT/TINYINT where space matters.

INTNumeric types

Answer: A — CHAR always uses 10 characters (padding with spaces); VARCHAR stores only what you put in

CHAR suits fixed-length codes like country codes; VARCHAR suits names and emails. CHAR padding can cause surprising trailing spaces in comparisons.

CHARVARCHAR

Answer: A — Variable-length text with a MAXIMUM of 50 characters

Inserting a longer string either errors or truncates depending on settings. Choose limits that reflect real data - overly tight limits cause production failures.

VARCHARLength

Answer: A — Precision 10 (total digits) and scale 2 (digits after the decimal point)

So the largest value is 99999999.99. Exceeding the scale rounds; exceeding the precision raises an error.

DECIMALPrecision and scale

Answer: A — DATE

DATE holds year-month-day. DATETIME/TIMESTAMP add the time of day, and TIME stores only the clock portion.

DATETemporal types

Answer: A — PostgreSQL has a real BOOLEAN; MySQL stores it as TINYINT(1) where 1 is true and 0 is false

SQL Server uses BIT. This is why some codebases compare with = 1 and others with = TRUE - both are correct for their platform.

BOOLEANDialects

Answer: A — Long or unbounded strings - articles, descriptions, comments

TEXT has no practical length limit but often cannot be fully indexed and may be stored separately. Use VARCHAR when you have a sensible maximum.

TEXTVARCHAR

Answer: A — Automatically assigns the next unique number to a new row - typically for primary keys

AUTO_INCREMENT in MySQL, SERIAL/GENERATED AS IDENTITY in PostgreSQL, IDENTITY in SQL Server. Gaps in the sequence are normal after failed inserts - do not treat ids as a count.

Primary keysSequences

SELECT CAST('2026-07-04' AS DATE);

Answer: A — CAST(value AS type) - standard SQL

CAST is portable; CONVERT() exists in SQL Server and MySQL with extra formatting options. PostgreSQL also allows the shorthand value::type.

CASTConversion

SELECT data->>'email' FROM users WHERE data->>'plan' = 'pro';

Answer: A — Storing structured documents in a column, with functions to query inside them

PostgreSQL's JSONB and MySQL's JSON support indexing and path queries - useful for flexible attributes. Overusing it, though, sacrifices the integrity relational design gives you.

JSONSemi-structured data

Answer: A — A column restricted to a fixed list of allowed string values

ENUM('small','medium','large') enforces valid values. Changing the list later requires altering the table, so many teams prefer a lookup table plus a foreign key.

ENUMConstraints

status VARCHAR(20) NOT NULL DEFAULT 'pending'

Answer: A — DEFAULT supplies a value when none is given at insert; NULL means no value is stored

Combining NOT NULL with a DEFAULT guarantees a usable value in every row - often better than allowing NULLs you must handle in every query.

DEFAULTNOT NULL

Answer: A — DECIMAL(5,2) - exact and bounded

INT would lose the fraction and text would prevent arithmetic. DECIMAL keeps the value exact and lets you add a CHECK constraint for 0-100.

DECIMALModelling

Answer: A — Binary Large Objects - images, PDFs and other binary data

Storing files in the database is possible but often discouraged: it bloats backups and slows queries. Storing a file PATH or URL is usually better.

BLOBFile storage

Answer: A — As VARCHAR - to preserve leading zeros, plus signs and formatting

Numeric storage destroys leading zeros and cannot hold '+91' or extensions. The rule of thumb: if you never do arithmetic on it, it is not a number.

ModellingVARCHAR

Answer: A — ALTER TABLE t ALTER COLUMN c TYPE new_type (syntax varies by database)

MySQL uses MODIFY COLUMN; PostgreSQL uses ALTER COLUMN ... TYPE. On huge tables this can lock and rewrite the table, so plan such migrations carefully.

ALTER TABLEMigrations

Answer: A — A column holding multiple values of the same type in one field

Convenient for simple lists like tags, but it breaks first normal form. A junction table remains the better choice when you need joins, constraints or per-item data.

ArraysNormalization

Answer: A — DECIMAL with an explicit scale - or an INTEGER count of the smallest unit (paise/cents)

Both approaches avoid binary rounding. Storing integer cents is common in payment systems because every arithmetic operation stays exact.

DECIMALMoney

price DECIMAL(10, 2)   -- 8 digits before the point, 2 after

Answer: A — FLOAT is binary and cannot represent 0.1 exactly, causing rounding errors in totals

DECIMAL(10,2) stores exact values. With FLOAT, summing thousands of prices drifts by fractions of a cent - unacceptable for financial data.

• A standard interview question about numeric precision.

DECIMALFLOATPrecision

Answer: A — TIMESTAMP is timezone-aware and converts to UTC; DATETIME stores the literal value unchanged

TIMESTAMP also has a narrower range (roughly 1970-2038). For global applications, storing UTC timestamps and converting for display is the usual approach.

TIMESTAMPTimezones

Answer: A — Text sorts alphabetically ('10' before '9'), blocks arithmetic, and wastes space

The classic symptom is a report where 100 appears before 20. Note that identifiers like phone numbers and postcodes ARE text - leading zeros matter and you never do arithmetic on them.

Data modellingSorting

Answer: A — A 128-bit globally unique identifier - useful for distributed systems where ids must be generated without a central counter

UUIDs avoid collisions across servers and hide row counts, at the cost of more storage and (for random UUIDs) worse index locality than sequential integers.

UUIDDistributed systems

Answer: A — The database silently converts types to compare them - which can produce surprises and block index use

Comparing a VARCHAR column to a number may force converting EVERY row, ignoring the index. Match types explicitly in your queries and schema.

Implicit conversionPerformance

Answer: A — An id column can exhaust its maximum (about 2.1 billion for INT), causing insert failures

Several large platforms have hit this in production. For high-volume tables (events, logs, messages) choose BIGINT from the start - migrating later is painful.

BIGINTCapacity planning

Answer: A — Collation defines sort order and case/accent sensitivity for text

The same query can return different results on two servers with different collations - a subtle portability issue. It also determines whether 'a' = 'A'.

CollationCharacter sets

Answer: A — Standard behaviour is an error; some configurations truncate with a warning instead

MySQL in strict mode errors, but older/lax modes silently truncated - which quietly corrupted data. Validate input length in the application too.

VARCHARStrict mode

Answer: A — Less storage means more rows per page, smaller indexes and faster scans

On large tables the savings compound: narrower rows mean fewer disk reads and better cache use. Balance this against future growth needs.

PerformanceStorage

SELECT order_date + INTERVAL '7 days' AS due_date FROM orders;

Answer: A — A span of time (e.g. '3 days'), which can be added to or subtracted from dates

PostgreSQL and Oracle support INTERVAL directly; MySQL uses it in expressions like DATE_ADD(d, INTERVAL 3 DAY).

INTERVALDate arithmetic

Answer: A — Types enforce correctness at the database level and are costly to change once a table holds millions of rows

The database is the last line of defence: applications get rewritten, but a well-typed schema keeps rejecting invalid data for years.

DesignData integrity