SQL

“SQL stands for Structured Query Language. It is a database language used to store, retrieve, update, and
manage data in relational databases such as MySQL, Oracle, and SQL Server. SQL is mainly used for
querying data using commands like SELECT, INSERT, UPDATE, and DELETE.”
If they ask further, you can add:
“SQL is considered a domain-specific programming language because it is designed specifically for
database operations.”
Example - SELECT * FROM employees;

Imperative vs declarative
“Imperative programming describes how to perform a task step by step, while declarative programming
describes only the desired result.”
You can say it more clearly like this:
 Declarative:
“Make tea with milk.”
Here, you only specify the result you want.
 Imperative:
“Take milk, add ginger and 2 pieces of cardamom, add 3 spoons of jaggery, boil it at low temperature, then
add tea leaves and cook for 5 minutes.”
Here, you describe the exact steps and procedure.
Then conclude with:
“Declarative tells what is needed, while imperative tells how to do it step by step.”

Before modern DBMS (Database Management Systems), many organizations used a File Processing
System to store data in separate files.
The main problems were:

Problems of File Processing System

1. Data Redundancy
Same data stored multiple times in different files.
Example:
 Employee details stored in payroll file
 Same employee details stored in attendance file
This wastes storage.
2. Data Inconsistency
Because data is duplicated, updates may not happen everywhere.
Example:
 Salary updated in one file
 Old salary still exists in another file
So data becomes inconsistent.

3. Difficult Data Access

Finding information was slow and difficult.
Each new query required a new program.

4. Data Isolation
Data stored in separate files and formats.
Hard to combine information from multiple files.

5. Security Problems
No proper centralized security system.
Anyone with file access could modify data.

6. Integrity Problems
Rules were hard to enforce.
Example:
 Negative salary could accidentally be entered.

7. No Concurrent Access Control

Many users accessing files simultaneously could create conflicts.
Example:
Two clerks updating same record at same time.

8. No Proper Backup & Recovery

If system crashed, recovering data was difficult.
Interview Short Answer
“The file processing system suffered from data redundancy, inconsistency, difficult data access, poor
security, lack of concurrency control, and weak backup/recovery mechanisms. DBMS was introduced to
solve these problems.”

Database Model
A Database Model is the way data is organized, stored, and related inside a database.
It defines:
 How data is structured
 How relationships are maintained
 How users access the data

Types of Database Models

1. Hierarchical Model
Data organized like a tree structure (parent → child).
Example:
 Company → Department → Employee
One parent can have many children.
Advantage
 Fast access
Disadvantage
 Difficult many-to-many relationships

2. Network Model
Data connected like a graph/network.
A child can have multiple parents.
Advantage
 More flexible than hierarchical
Disadvantage
 Complex structure

3. Relational Model
Data stored in tables (rows and columns).
Most popular model today.
Example:
 Student table
 Course table
Used in:
 MySQL
 Oracle
 SQL Server
Advantage
 Easy to use
 Supports SQL

4. Object-Oriented Model
Stores data as objects like OOP languages.
Used for complex applications.

5. NoSQL Model
Used for large-scale and unstructured data.
Types:
 Document databases
 Key-value databases
 Graph databases
Examples:
 MongoDB
 Cassandra

Short Interview Answer

“A database model defines how data is organized and related in a database. Common database models are
hierarchical, network, relational, object-oriented, and NoSQL models.”

What is a Table in RDBMS?

A table in RDBMS (Relational Database Management System) is a way of storing data in the form of
rows and columns.
 Each row represents a record (one entry)
 Each column represents an attribute (field of data)

Example:

Roll No Name Age

1 Rahul 20

2 Priya 21

 Each row = one student record

 Each column = details like name, age

Interview Answer (Best Version)

“In an RDBMS, a table is a collection of related data organized in rows and columns. Each row represents a
record, and each column represents an attribute of that record. Tables are used to store structured data and
are the basic storage unit in a relational database.”

Domain
Simple definition:
A domain defines the type, format, and allowed values for a column in a table.

Example:

Roll No Name Age

1 Rahul 20

Domains:
 Roll No → only positive integers (1, 2, 3…)
 Name → only text/strings (no numbers)
 Age → only valid numbers (e.g., 0–120)

Real-life analogy:
Think of a form:
 Age field → you cannot enter “abc”
 Phone number → must be digits only
That restriction is the domain

Interview Answer (Best):

“In RDBMS, a domain is the set of permissible values that an attribute can have. It defines the type, format,
and constraints of data that can be stored in a column.”

1. Attribute (Column)
An attribute is a column in a table that represents a property of data.
Example:
 Name, Age, Salary are attributes

2. Tuple (Row)
A tuple is a single row (record) in a table.
Example:
 (1, Rahul, 20) → one student record

3. Relation (Table)
A relation is a table in RDBMS consisting of rows and columns.
Example:
 Student table = Relation

4. Degree of a Table
Number of columns (attributes) in a table.
Example:
| Roll No | Name | Age |
→ Degree = 3

5. Cardinality
Number of rows (records) in a table.
Example:
 100 students → Cardinality = 100
6. Primary Key
A column that uniquely identifies each row.
Rules:
 Must be unique
 Cannot be NULL
Example:
 Roll No

7. Foreign Key
A column that creates a link between two tables.
Example:
 Student table (Roll No)
 Marks table (Roll No as foreign key)

8. Null Value
Represents missing or unknown data.
Example:
 Phone number not provided

9. Schema
The structure/design of a database.
Example:
 Table name, columns, data types

10. Instance
The actual data stored at a specific time.

Interview Ready Answer (Short):

“In RDBMS, data is stored in tables made of rows and columns. Columns are attributes, rows are tuples, and
tables are called relations. Other important concepts include domain (valid values), primary key (unique
identifier), and foreign key (relationship between tables).”
🔑 PRIMARY KEY (Detailed Explanation)

✔️Definition (Best Interview Answer)

A Primary Key is a column (or set of columns) in a table that uniquely identifies each record. It does not
allow duplicate values or NULL values.

✔️Key Properties

 Must be unique
 Cannot be NULL
 Only one primary key per table (but can be composite)
 Ensures entity integrity

✔️Types of Primary Key

1. Simple Primary Key

 One column (e.g., Roll_No)
2. Composite Primary Key
 Combination of columns
Example:

Student_ID Course_ID Marks

 (Student_ID + Course_ID) together uniquely identify record

✔️Why Primary Key is important?

 Avoid duplicate records

 Fast data retrieval
 Maintains data integrity

❓ Follow-up Questions (Primary Key)

Q1: Can a primary key be NULL?

❌ No, it cannot be NULL because it must uniquely identify every row.

Q2: Can a table have multiple primary keys?

❌ No, but it can have a composite primary key (multiple columns together).
Q3: Can primary key be changed?
✔️Yes, but it is not recommended because it affects relationships.

Q4: Is primary key always a number?

❌ No, it can be number, string, or combination—but must be unique.

🔗 FOREIGN KEY (Detailed Explanation)

✔️Definition (Best Interview Answer)

A Foreign Key is a column in one table that refers to the primary key of another table. It is used to create
a relationship between two tables.

✔️Key Properties

 Can have duplicate values

 Can contain NULL
 Refers to a primary key in another table
 Ensures referential integrity

✔️Why Foreign Key is used?

 Connects two tables

 Prevents invalid data entry
 Maintains consistency between tables

❓ Follow-up Questions (Foreign Key)

Q1: Can foreign key accept NULL?

✔️Yes, unless restricted by NOT NULL constraint.

Q2: Can foreign key have duplicate values?

✔️Yes, many rows can refer to the same primary key.

Q3: What happens if we delete parent record?

Depends on rules:
 ON DELETE CASCADE → child records also deleted
  RESTRICT → deletion not allowed
 SET NULL → foreign key becomes NULL

Q4: Can a foreign key exist without primary key?

❌ No, it must reference a valid primary key in another table.

🔥 PRIMARY KEY vs FOREIGN KEY (Quick Comparison)

Feature Primary Key Foreign Key

Purpose Uniquely identify record Link two tables

NULL allowed No Yes

Duplicate values No Yes

Number per table One Many

Dependency Independent Depends on Primary Key

🎯 Final Interview Answer (Ready to Speak)

“A Primary Key is a column that uniquely identifies each record in a table and does not allow NULL or
duplicate values. A Foreign Key is a column that creates a relationship between two tables by referencing the
primary key of another table. Primary key ensures entity integrity, while foreign key ensures referential
integrity.”