Last-Minute SQL Interview Revision Guide – Part 1 (Keys, Data Types & Constraints)

Getting ready for a SQL interview?

This post will help you revise the most important topics quickly and clearly — without long explanations or confusion. Think of this as your last-minute revision guide. Each question comes with a simple definition and a quick example to make it stick in your mind.

In Last-Minute SQL Interview Revision Guide – Part 1 (Keys, Data Types & Constraints), we’ll focus on the basics — the foundation every SQL developer must know before the interview.

Let’s begin your quick refresh and make sure you walk into the interview confident and ready!

What is SQL?

SQL (Structured Query Language) is a standard language for storing, retrieving, managing, and manipulating data in a relational database. It includes commands for defining structures (DDL), querying data (DQL), modifying data (DML), controlling access (DCL), and transaction management (TCL).

What is a Database?

A database is an organized collection of related data that can be easily accessed, managed, and updated. It allows users to store, retrieve, and manipulate data efficiently using a Database Management System (DBMS).

Example

A bank database stores customer info, account details, and transaction history.
Tables like Customers, Accounts, and Transactions hold related records.

What is a row/record?

A row (or record) in a database table is a single, complete set of related data stored horizontally. Each row represents one entity or instance in the table, with columns defining its attributes.

EmployeeID	FirstName	LastName	Department	Salary
101	Raj	Bhatt	IT	50000

The first row (101, Raj, Bhatt, IT, 50000) is one record representing one employee.

Quick Recall

Row = Record = Horizontal entry
Column = Field = Attribute

What is a column/field?

A column (or field) in a database table is a vertical structure that defines a specific attribute of the table. Each column has a name and data type, and it holds data for that attribute across all rows/records.

EmployeeID	FirstName	LastName	Department	Salary
101	Raj	Bhatt	IT	50000

EmployeeID, FirstName, Department — these are columns/fields.
Each column stores data of the same type for all employees

Quick Recall

Column = Field = Vertical attribute
Row = Record = Horizontal entry

What is a primary key?

A Primary Key (PK) is a column or a combination of columns in a table that uniquely identifies each record. It cannot contain NULL values, and no two rows can have the same primary key value.

EmployeeID	FirstName	LastName	Department	Salary
101	Raj	Bhatt	IT	50000
102	Priya	Sharma	HR	45000

EmployeeID is the primary key because it uniquely identifies each employee.
No two employees can have the same EmployeeID, and it cannot be NULL.

Quick Recall

Primary Key = Unique Identifier for records
Rules: Unique + Non-NULL + One per table

What is a foreign key?

A Foreign Key (FK) is a column (or set of columns) in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity, meaning that a value in the foreign key must exist in the referenced primary key.

Employee Table

EmployeeID	FirstName	LastName	DeptID
101	Raj	Bhatt	1
102	Priya	Sharma	2

Department Table

DeptID	DeptName
1	IT
2	HR

DeptID in the Employees table is a foreign key referencing DeptID in the Departments table.
It ensures that an employee can only belong to a valid department.

Quick Recall

Foreign Key = Links tables + Enforces referential integrity
Can have duplicate values
Can be NULL if the relationship is optional

What is a unique key?

A Unique Key ensures that all values in a column or combination of columns are unique. Unlike a primary key, a table can have multiple unique keys and it can allow one NULL.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

Email is unique, so no two employees can have the same email.

What is a Composite key?

A Composite Key is a combination of two or more columns used together as a primary key to uniquely identify a record.

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Uniqueness is ensured only when OrderID and ProductID are combined.

What is a Constraints?

A Constraint is a rule applied to a column or table to control the type of data allowed, ensuring accuracy, consistency, and integrity in the database.

Common Types of Constraints

Constraint	Purpose
PRIMARY KEY	Uniquely identifies each row
FOREIGN KEY	Ensures relationship integrity between tables
UNIQUE	Prevents duplicate values
NOT NULL	Column must have a value
CHECK	Ensures values meet a specific condition
DEFAULT	Assigns a default value if none is provided

PRIMARY KEY → uniquely identifies each employee.
UNIQUE → no duplicate emails.
NOT NULL → FirstName, LastName, DeptID must have a value.
CHECK → Salary must be greater than 0.
DEFAULT → DeptID defaults to 1 if not provided.
FOREIGN KEY → links DeptID to Departments table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Salary DECIMAL CHECK(Salary > 0),
    DeptID INT NOT NULL DEFAULT 1,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Quick Recall

PRIMARY KEY = Unique + Non-NULL
FOREIGN KEY = Links tables
UNIQUE = No duplicate values
NOT NULL = Must have a value
CHECK = Custom condition
DEFAULT = Automatic value if none provided

What is a Candidate Key?

A Candidate Key is a column or a set of columns in a table that can uniquely identify each row. Every table can have one or more candidate keys, but only one is chosen as the primary key.

Employee Table

EmployeeID	Email	PhoneNumber	FirstName	LastName
101	[email protected]	9876543210	Raj	Bhatt
102	[email protected]	9876543211	Priya	Sharma

EmployeeID → unique
Email → unique
PhoneNumber → unique
All three can act as candidate keys, but we usually pick EmployeeID as the primary key.

Quick Recall

Candidate Key = Potential Primary Key
Must be unique
Cannot contain NULL values
Table can have multiple candidate keys

What is a surrogate key?

A Surrogate Key is a system-generated unique identifier for a table row, often an auto-increment integer. It is used when no natural or candidate key is suitable or when we want a simpler primary key unrelated to business data.

EmployeeID is a surrogate key:

Generated automatically
Uniquely identifies each employee
Not derived from business data like Email or PhoneNumber

Quick Recall

Surrogate Key = Artificial / System-generated
Always unique
Usually numeric and auto-incremented
Independent of business data

What is a Natural key?

A Natural Key is a column or a set of columns that uniquely identifies a record based on real-world, meaningful data. It differs from a surrogate key because it is derived from the actual data, not generated by the system.

Employee Table

Email	FirstName	LastName	DeptID
[email protected]	Raj	Bhatt	1
[email protected]	Priya	Sharma	2

Email can serve as a natural key because it is unique and meaningful in real life.

Quick Recall

Natural Key = Real-world data
Must be unique
Meaningful outside the database
Cannot be NULL

What is Alternate Key ?

An Alternate Key is a candidate key that was not selected as the primary key but can still uniquely identify records in a table. it serves as a backup unique identifier.

Employee Table

Employee ID	Email	Phone Number
101	[email protected]	9876543210
102	[email protected]	9876543211

EmployeeID → Primary Key
Email → Alternate Key
PhoneNumber → Alternate Key (can also be used if needed)

Quick Recall

Alternate Key = Candidate Key not chosen as Primary Key
Always unique
Cannot be NULL (if required)

What is Super Key ?

A Super Key is a column or combination of columns that can uniquely identify each row in a table. It may include extra attributes beyond what is strictly necessary to ensure uniqueness.

Employee Table

EmployeeID	Email	PhoneNumber	DeptID
101	[email protected]	9876543210	1
102	[email protected]	9876543211	2

EmployeeID → Super Key
(EmployeeID, Email) → Super Key (includes extra column but still uniquely identifies rows)
(Email, PhoneNumber) → Super Key (also uniquely identifies rows)

Quick Recall

Super Key = Any combination of columns that uniquely identifies a record
May contain extra columns beyond the minimum needed
Primary Key is a minimal Super Key

What is a schema ?

A Schema in SQL is a logical container or namespace that groups related database objects like tables, views, stored procedures, functions, and indexes.

It helps organize the database and manage security and ownership efficiently.

CREATE SCHEMA HR;
GO

CREATE TABLE HR.Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

HR is the schema name.
Employees is the table inside that schema.
Together they form HR.Employees — a fully qualified table name.

Quick Recall

A database can have multiple schemas.
A schema can contain multiple objects.
Helps separate data by module, purpose, or user.
Default schema in SQL Server is usually dbo.

Is a schema the same as a database?

No, a database is a collection of data and objects, while a schema is a logical grouping inside the database that organizes those objects.

What is a data type?

A data type in SQL defines the kind of value that a column, variable, or parameter can hold. It ensures data consistency, memory efficiency, and validation of data stored in a database.

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Salary DECIMAL(10,2),
    JoiningDate DATETIME
);

INT → stores integers
VARCHAR(100) → stores variable-length text
DECIMAL(10,2) → stores numeric values with precision
DATETIME → stores date and time

Quick Recall

Data types help SQL Server understand how much space to allocate.
They prevent invalid data from being inserted (e.g., text in numeric columns).
Must be chosen carefully based on data nature and performance.

Common SQL Server Data Type Categories

Category	Examples	Description
Numeric	INT, BIGINT, DECIMAL, FLOAT	Store numbers
Character/String	CHAR, VARCHAR, TEXT	Store text
Date/Time	DATE, DATETIME, TIME	Store date/time values
Binary	BINARY, VARBINARY	Store binary data (e.g., images)
Boolean	BIT	Store true/false (0/1)
Special	UNIQUEIDENTIFIER, XML, JSON	Store unique IDs or structured data

Why are data types important?

They define storage format, prevent invalid data, and improve database performance by optimizing memory usage.

What is the difference between CHAR and VARCHAR?

CHAR is a fixed-length data type, meaning it always stores the same amount of space, even if the data is shorter.

VARCHAR is a variable-length data type, meaning it uses only the space required for actual data.

CREATE TABLE Example (
    Code CHAR(10),
    Name VARCHAR(10)
);

INSERT INTO Example VALUES ('A1', 'A1');

Column	Stored Value	Storage Behavior
Code (CHAR)	'A1 '	Occupies 10 bytes (fixed)
Name (VARCHAR)	'A1'	Occupies only 2 bytes (actual length)

Key Differences Between CHAR and VARCHAR

Feature	CHAR	VARCHAR
Storage Type	Fixed-length	Variable-length
Performance	Faster for fixed-size data	Better for variable-size data
Storage Space	Always uses full defined length	Uses only required space
Padding	Pads with spaces to match defined length	No padding
Use Case	Ideal for codes, status, gender, etc.	Ideal for names, addresses, descriptions
Example	CHAR(5) always = 5 bytes	VARCHAR(5) = actual data length only

Which one is better, CHAR or VARCHAR?

Use CHAR when data length is constant (like country codes or gender).
Use VARCHAR when data length varies (like names or addresses).

What is INT?

INT (short for Integer) is a numeric data type in SQL used to store whole numbers (both positive and negative) without decimal places. It is commonly used for IDs, counts, or quantities.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT,
    Salary DECIMAL(10,2)
);

EmployeeID → stores integer IDs like 101, 102.
Age → stores whole numbers like 25, 30.

Property	Description
Type	Integer (whole number)
Storage Size	4 bytes
Range (Signed)	-2,147,483,648 to 2,147,483,647
Range (Unsigned)	0 to 4,294,967,295 (in MySQL)
Default	Signed
Decimal Allowed?	No (use DECIMAL or FLOAT if needed)

When Int?

For auto-incrementing primary keys
For counting or numbering (like quantities, employee numbers, or order IDs)
When decimal precision is not required

What’s the difference between INT, BIGINT, SMALLINT, and TINYINT?

They all store whole numbers but differ in storage size and range —TINYINT (1 byte), SMALLINT (2 bytes), INT (4 bytes), BIGINT (8 bytes).Choose based on how large the numbers can grow.

What is Decimal?

DECIMAL is a fixed-point numeric data type in SQL used to store exact numeric values with a defined precision (total digits) and scale (digits after the decimal point). It is used where accuracy is crucial — like in financial or accounting data.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10,2)
);

Property	Description
Type	Exact numeric (fixed-point)
Syntax	DECIMAL(p, s) or NUMERIC(p, s)
Precision (p)	Total number of digits (1–38 in SQL Server)
Scale (s)	Digits after the decimal point
Storage Size	Depends on precision (5–17 bytes)
Accuracy	Exact (no rounding errors like FLOAT)
Alias	NUMERIC (same behavior in SQL Server)

When to Use DECIMAL

For currency values (salary, product price, tax amount)
When precision is mandatory — financial, banking, accounting systems
When you need consistent and predictable rounding

What’s the difference between DECIMAL and FLOAT?

DECIMAL stores exact values with fixed precision — ideal for money. FLOAT stores approximate values using binary representation — better for scientific or large-scale calculations where minor precision loss is acceptable.

What is Date?

The DATE data type in SQL is used to store calendar dates (year, month, and day) without any time information. It is ideal for storing birthdays, joining dates, deadlines, and other date-only information.

Storage

Uses 3 bytes per value in SQL Server.
Valid range: 0001-01-01 to 9999-12-31.

Usage Scenarios

When only the date is important, without time:
Birth dates
Employee joining dates
Order or delivery dates
Subscription expiry dates

Comparison Table

Data Type	Stores	Example	Storage
DATE	Date only	2025-10-10	3 bytes
DATETIME	Date + time	2025-10-10 12:30:45	8 bytes
DATETIME2	Date + time with higher precision	2025-10-10 12:30:45.1234567	6–8 bytes

What is NULL?

In SQL, NULL represents a missing, unknown, or undefined value in a table column. It does not mean zero (0), empty string (''), or false — it simply means “no data.

Important Notes

Concept	Description
NULL ≠ NULL	Two NULLs are not equal because both represent unknown values.
Use IS NULL	To check if a value is null.
Aggregates	Functions like COUNT(), SUM(), AVG() ignore NULLs.
Default values	If no value is provided for a column that allows NULLs → SQL stores NULL automatically.

Difference Between Empty and NULL

-- Empty string
INSERT INTO Employees (EmployeeID, Name, Email)
VALUES (2, 'Ravi', '');

-- NULL value
INSERT INTO Employees (EmployeeID, Name, Email)
VALUES (3, 'Amit', NULL);

'' = “known empty” (a blank string)

NULL = “unknown” (no value at all)

NULL = No Value / Unknown Value
Always check with IS NULL or IS NOT NULL
Use ISNULL() or COALESCE() to handle it gracefully

What is NOT NULL?

The NOT NULL constraint in SQL ensures that a column cannot have a NULL value. It means the column must always contain a valid value — no blanks, no unknowns.

NOT NULL is a column-level constraint used to enforce data integrity by making sure every row has a value for that column.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NULL
);

INSERT INTO Employees (EmployeeID, Name, Email)
VALUES (1, 'Raj', NULL);  -- Valid (Email allows NULL)

INSERT INTO Employees (EmployeeID, Name)
VALUES (NULL, 'Ravi');    -- Error (EmployeeID cannot be NULL)

EmployeeID and Name must have values (cannot be NULL).
Email can be NULL (optional data).
Apply NOT NULL to important fields such as:
Primary identifiers (ID, OrderNo)
Mandatory details (Name, Price, DateOfBirth)
Prevents incomplete or invalid data entries.

What is a default value?

The DEFAULT constraint in SQL is used to assign an automatic value to a column when no value is specified during an INSERT operation. It ensures that every record has a valid value — even if the user doesn’t explicitly provide one.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    JoiningDate DATE DEFAULT GETDATE(),
    IsActive BIT DEFAULT 1
);
INSERT INTO Employees (EmployeeID, Name)
VALUES (1, 'Raj');

If JoiningDate isn’t provided → SQL uses current date (GETDATE()).
If IsActive isn’t provided → SQL uses 1 (active).

Important Notes

DEFAULT does not override NULLs — if you explicitly insert NULL, it stays NULL.
DEFAULT ensures consistent, non-empty data.
Automatically inserts a preset value when no value is provided.
Improves data quality and reduces manual input.

We’ve reached the end of Last-Minute SQL Interview Revision Guide – Part 1 (Keys, Data Types & Constraints) . Here, we focused on the core concepts that form the foundation of SQL knowledge .These are the building blocks that every SQL developer must know. Mastering them will make it easier to tackle advanced topics in upcoming interviews.

Remember: Revision is more effective when it’s short and consistent. Go through these notes, try out a few queries, and you’ll be confident in explaining them during interviews.

Thank you for taking the time to read this post. I hope it has given you a clear and concise understanding of SQL Keys, Data Types, and Constraints. Mastering these concepts is essential for interview success, as they form the foundation of relational database design and querying. By revising these definitions, examples, and best practices, you ensure that you are well-prepared, confident, and ready to answer both basic and advanced SQL questions in interviews.

Happy revising, and may your next SQL interview be a success!!!