Infosys Interview – DBMS & SQL Master Guide
What is DBMS?
DBMS (Database Management System) is software used to store, manage, organize, and retrieve data
efficiently.
Why DBMS is Used
• Store data efficiently
• Retrieve data quickly
• Manage large datasets
• Maintain consistency and security
DBMS vs RDBMS
DBMS
General database management system used for storing and managing data.
RDBMS
Relational Database Management System that stores data in structured tables with relationships.
Examples:
• MySQL
• PostgreSQL
• Oracle
Difference Between DBMS and RDBMS
DBMS stores data generally, while RDBMS stores data in relational tables with relationships between
them.
RDBMS supports relational integrity and SQL-based operations more efficiently.
1
Keys in DBMS
Keys are used to uniquely identify records and create relationships between tables.
1. Primary Key
Definition
A primary key is a column or set of columns used to uniquely identify each row in a table.
Important Points
• Must be unique
• Cannot contain NULL values
Example
user_id name
101 Likith
102 Arun
Here, user_id is the primary key.
Why Primary Key is Used
To uniquely identify records.
2. Foreign Key
Definition
A foreign key is a column used to create a relationship between two tables.
2
Example
USERS TABLE
user_id name
101 Likith
BOOKINGS TABLE
booking_id user_id
1 101
Here, booking.user_id is the foreign key because it references the primary key from the users table.
Why Foreign Key is Used
To maintain relationships and consistency between tables.
3. Candidate Key
Definition
A candidate key is a column or set of columns that can uniquely identify rows in a table and can
potentially become the primary key.
Example
user_id email phone
101 a@[Link] 9999999999
All three can uniquely identify a user.
One becomes primary key. Others remain candidate keys.
3
Important Point
All primary keys are candidate keys, but not all candidate keys become primary keys.
4. Super Key
Definition
A super key is a set of one or more columns that can uniquely identify a row in a table.
Example
• user_id
• user_id + name
• user_id + email
All can uniquely identify rows.
Super keys may contain unnecessary extra columns.
Memory Locks
Primary Key = Unique Identifier
Foreign Key = Table Connection
Candidate Key = Possible Primary Key
Super Key = Unique Identifier With Extra Columns
Normalization
Definition
Normalization is the process of organizing data in a database to reduce redundancy and improve
consistency.
4
Why Normalization is Used
• Reduce duplicate data
• Improve consistency
• Reduce anomalies
• Improve organization
1NF – First Normal Form
Each column should contain atomic/single values.
2NF – Second Normal Form
Removes partial dependency.
3NF – Third Normal Form
Removes transitive dependency.
BCNF
A stricter version of 3NF.
Memory Lock
Normalization = Remove Redundancy
ACID Properties
ACID properties ensure reliable database transactions.
A – Atomicity
All or nothing.
5
A transaction either fully completes or fully fails.
C – Consistency
Database should remain valid before and after transactions.
I – Isolation
Transactions should not interfere with each other.
D – Durability
Committed data should remain permanently even after failures.
Memory Lock
A = All or Nothing
C = Correct Data
I = No Interference
D = Permanent Save
Types of SQL Commands
1. DDL
2. DML
3. DCL
4. TCL
5. DQL
DDL – Data Definition Language
Used to define or modify database structure.
6
Commands
• CREATE
• ALTER
• DROP
• TRUNCATE
DML – Data Manipulation Language
Used to manipulate table data.
Commands
• INSERT
• UPDATE
• DELETE
DCL – Data Control Language
Used for permissions and access control.
Commands
• GRANT
• REVOKE
TCL – Transaction Control Language
Used to manage transactions.
Commands
• COMMIT
• ROLLBACK
• SAVEPOINT
DQL – Data Query Language
Used to retrieve/query data.
7
Command
• SELECT
Memory Locks
DDL = Structure
DML = Modify Data
DCL = Permissions
TCL = Transactions
DQL = Fetch Data
DELETE vs DROP vs TRUNCATE
DELETE
Removes selected rows.
• WHERE clause possible
• Structure remains
Example:
DELETE FROM users
WHERE id = 1;
TRUNCATE
Removes all rows quickly.
• WHERE clause not possible
• Structure remains
Example:
TRUNCATE TABLE users;
8
DROP
Completely removes table and structure.
Example:
DROP TABLE users;
Memory Locks
DELETE = Remove Rows
TRUNCATE = Empty Table
DROP = Delete Table Itself
SQL Joins
Joins are used to combine data from multiple tables.
INNER JOIN
Returns only matching records from both tables.
Example:
SELECT [Link], [Link]
FROM users
INNER JOIN bookings
ON users.user_id = bookings.user_id;
LEFT JOIN
Returns all rows from left table and matching rows from right table.
9
RIGHT JOIN
Returns all rows from right table and matching rows from left table.
FULL JOIN
Returns all records from both tables.
Memory Locks
INNER JOIN = Only Matching
LEFT JOIN = All Left
RIGHT JOIN = All Right
FULL JOIN = Everything
Aggregate Functions
Aggregate functions perform calculations on multiple rows and return a single value.
COUNT()
Returns total number of rows.
SELECT COUNT(*) FROM employee;
SUM()
Returns total sum.
SELECT SUM(salary) FROM employee;
10
AVG()
Returns average value.
SELECT AVG(salary) FROM employee;
MAX()
Returns highest value.
SELECT MAX(salary) FROM employee;
MIN()
Returns lowest value.
SELECT MIN(salary) FROM employee;
Memory Locks
COUNT = Count Rows
SUM = Add Values
AVG = Average
MAX = Highest Value
MIN = Lowest Value
GROUP BY
Definition
GROUP BY is used to group rows with similar values for aggregate calculations.
11
Example
SELECT department, SUM(salary)
FROM employee
GROUP BY department;
HAVING
Definition
HAVING is used to filter grouped data after applying GROUP BY.
Example
SELECT department, SUM(salary)
FROM employee
GROUP BY department
HAVING SUM(salary) > 20000;
Difference Between WHERE and HAVING
WHERE filters rows before grouping.
HAVING filters groups after grouping.
Memory Locks
GROUP BY = Make Groups
HAVING = Filter Groups
WHERE = Filter Rows
12
Second Highest Salary Query
Query 1
SELECT MAX(salary)
FROM employee
WHERE salary < (
SELECT MAX(salary)
FROM employee
);
Explanation
Inner query finds highest salary.
Outer query finds maximum salary smaller than highest salary.
Query 2 (Nth Highest Salary)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Explanation
• DISTINCT removes duplicates
• ORDER BY sorts salaries
• OFFSET skips top rows
• LIMIT returns required row
Constraints in SQL
Constraints are rules applied on columns to maintain valid and accurate data.
13
NOT NULL
Column cannot contain NULL values.
UNIQUE
All values must be different.
PRIMARY KEY
Uniquely identifies rows.
FOREIGN KEY
Creates relationships between tables.
CHECK
Ensures values satisfy conditions.
Example:
age INT CHECK(age >= 18)
DEFAULT
Assigns automatic default values.
Example:
city VARCHAR(20) DEFAULT 'Vijayawada'
14
Memory Locks
NOT NULL = No Empty Value
UNIQUE = No Duplicates
PRIMARY KEY = Unique Identifier
FOREIGN KEY = Table Connection
CHECK = Condition Validation
DEFAULT = Automatic Value
Indexing
Definition
Indexing is a technique used to improve the speed of data retrieval operations in a database.
Why Indexing is Used
• Faster searching
• Better query performance
• Reduced retrieval time
Real-Life Example
Book index or phone contacts search.
SQL Example
CREATE INDEX idx_user
ON users(user_id);
15
Disadvantages of Indexing
• Extra storage required
• INSERT/UPDATE slightly slower
Memory Lock
Indexing = Faster Searching
Views in SQL
Definition
A view is a virtual table created from the result of an SQL query.
Why Views are Used
• Simplify complex queries
• Improve security
• Hide sensitive data
SQL Example
CREATE VIEW employee_view AS
SELECT id, name, salary
FROM employee;
Table vs View
TABLE stores actual data.
VIEW is a virtual/query-based table.
16
Memory Lock
View = Virtual Table
NULL vs NOT NULL
NULL
Represents missing or unknown value.
NOT NULL
Ensures value must be entered.
Difference Between NULL and 0
NULL means no value.
0 is an actual numeric value.
Memory Locks
NULL = No Value
NOT NULL = Value Required
UNION vs UNION ALL
UNION
Combines query results and removes duplicates.
UNION ALL
Combines query results without removing duplicates.
17
Important Difference
UNION removes duplicates.
UNION ALL keeps duplicates and is faster.
Memory Locks
UNION = Remove Duplicates
UNION ALL = Keep Duplicates
Final DBMS & SQL Revision Checklist
Must Revise:
• DBMS vs RDBMS
• Keys
• Normalization
• ACID Properties
• SQL Command Types
• DELETE vs DROP vs TRUNCATE
• Joins
• Aggregate Functions
• GROUP BY vs HAVING
• Second Highest Salary Query
• Constraints
• Indexing
• Views
• NULL vs NOT NULL
• UNION vs UNION ALL
Final Important Memory Locks
Primary Key = Unique Identifier
Foreign Key = Table Connection
Normalization = Remove Redundancy
ACID = Reliable Transactions
18
Indexing = Faster Searching
View = Virtual Table
GROUP BY = Make Groups
HAVING = Filter Groups
UNION = Remove Duplicates
UNION ALL = Keep Duplicates
19