💾 I.
Database Management System (DBMS) Concepts
Question Answer
A Database is an organized collection of structured data, typically stored
electronically in a computer system.
What is a Database,
and what is a DBMS?
A DBMS (Database Management System) is a software system used for
creating, retrieving, updating, and managing data in a database. Examples:
MySQL, PostgreSQL, Oracle, MongoDB.
SQL (Relational): Uses structured, tabular schema (tables, rows, columns).
Guarantees ACID properties. Best for complex queries, high-integrity
transactional systems (banking).
What is the
difference between
SQL and NoSQL?
NoSQL (Non-Relational): Uses dynamic schemas (documents, key-value,
graphs). Focuses on horizontal scalability and high availability (BASE model).
Best for large volumes of unstructured data (social media, IoT).
ACID properties ensure that database transactions are processed reliably. *
Atomicity: The transaction is treated as a single, indivisible unit; it either
completes entirely or is rolled back completely ("all or nothing"). *
Consistency: The transaction must bring the database from one valid state to
What are the ACID
another, adhering to all defined rules and constraints. * Isolation: Concurrent
properties?
transactions do not interfere with each other. The result must be the same as
if they were executed sequentially. * Durability: Once a transaction is
committed, the changes are permanent and survive system failures (e.g.,
power loss).
Normalization is a systematic process of organizing data in a relational
database to minimize redundancy (data duplication) and improve data
integrity. It involves decomposing large tables into smaller, related tables and
defining relationships between them using keys.
Explain
Normalization.
The goal is to eliminate insertion, update, and deletion anomalies. The
common forms are 1NF, 2NF, and 3NF.
🔑 II. Relational Keys and Constraints
Question Answer
A Primary Key is a column or a group of columns that uniquely identifies every row
What is a
in a table. * Rule 1: It must contain unique values. * Rule 2: It cannot contain NULL
Primary Key?
values.
A Foreign Key is a field in one table that uniquely identifies a row of another table. It
What is a
establishes a link/relationship between the two tables, enforcing referential
Foreign Key?
integrity.
A Unique Key constraint ensures that all values in a column are different. Unlike a
What is a
Primary Key, a table can have multiple Unique Keys, and a Unique Key column can
Unique Key?
accept one NULL value (in most systems).
A NULL value represents data that is missing or unknown. It is not the same as an
What is a
empty string ('') or a zero (0). You use the IS NULL or IS NOT NULL operators to check
NULL value?
for it in SQL.
💬 III. SQL Commands and Clauses
Question Answer
* DQL (Data Query Language): Retrieval (e.g., SELECT). * DDL (Data
Classify the main
Definition Language): Schema definition (CREATE TABLE, ALTER, DROP,
types of SQL
TRUNCATE). * DML (Data Manipulation Language): Data changes (INSERT,
Commands (DQL, DDL,
UPDATE, DELETE). * TCL (Transaction Control Language): Manage
DML, TCL).
transactions (COMMIT, ROLLBACK, SAVEPOINT).
DELETE (DML): Removes rows based on a WHERE clause. It is slower,
generates logs, and can be rolled back.
Difference between
TRUNCATE (DDL): Removes all rows from a table quickly. It is faster, does not
DELETE, TRUNCATE,
generate full logs, and cannot be rolled back (implicitly commits).
and DROP?
DROP (DDL): Removes the entire table structure (schema, data, and
constraints) from the database.
* WHERE is used to filter individual rows before any grouping occurs. It
Difference between cannot use aggregate functions (COUNT, SUM, etc.). * HAVING is used to
WHERE and HAVING? filter groups after the GROUP BY clause has been executed. It must use
aggregate functions.
What are Aggregate Functions that perform a calculation on a set of values and return a single
Functions? summary value. Examples: COUNT(), SUM(), AVG(), MAX(), MIN().
🔗 IV. SQL Joins
Question Answer
What is a SQL JOIN,
A JOIN is used to combine rows from two or more tables based on a related
and what are the
column between them (typically the Foreign Key linking to the Primary Key).
main types?
* INNER JOIN: Returns only the rows that have matching values in both tables.
Explain the
Records that don't have a match in the other table are excluded. * LEFT JOIN
difference between
(or LEFT OUTER JOIN): Returns all rows from the left table, and the matching
INNER JOIN and
rows from the right table. If there is no match in the right table, the result
LEFT JOIN?
columns from the right side will be NULL.
📝 V. Common SQL Query Questions
Query Task SQL Solution
Find the second-highest SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC LIMIT
salary. 1 OFFSET 1;
Find all employees with SELECT Name, COUNT(Name) FROM Employees GROUP BY Name
duplicate names. HAVING COUNT(Name) > 1;
Find customers who have SELECT [Link] FROM Customers C LEFT JOIN Orders O ON
NOT placed an order. [Link] = [Link] WHERE [Link] IS NULL;