Download DBMS SQL Commands Notes | DDL, DML, DCL, TCL + Examples + Interview Questions and more Summaries Database Management Systems (DBMS) in PDF only on Docsity!
SQL IMPORTANT COMMANDS + INTERVIEW QNA
DBMS
Prepared by: SAMIKSHA Date created:
Jan 11, 2026
- Introduction to DBMS
A Database Management System (DBMS) is software that allows users to store, retrieve, update, and manage data efficiently. It provides data security, consistency, and controlled access.
1.Advantages of DBMS
Reduced data redundancy Improved data integrity Data security and authorization Backup and recovery Concurrency control
2.Characteristics of DBMS
Data independence Concurrency control Data security Backup and recovery Integrity constraints Multi-user access
3.DBMS Architecture
Three-Level Architecture
- External Level โ User views
- Conceptual Level โ Logical structure
- Internal Level โ Physical storage
โก Provides data abstraction and independence.
4.Data Models
A data model defines how data is stored and accessed.
Types of Data Models
Hierarchical Model Network Model Relational Model Object-Oriented Model
The Relational Model is the most widely used.
5.Relational Model
Data stored in tables (relations) Rows โ tuples Columns โ attributes
Properties
Each table has a primary key No duplicate rows Atomic values
6.Keys in DBMS
Keys uniquely identify records.
Types of Keys
Primary Key Candidate Key Super Key Foreign Key Composite Key Alternate Key
Used to modify table structure.
sql
ALTER TABLE Student ADD age INT
ALTER TABLE Student MODIFY name VARCHAR(100)
DROP
Deletes table permanently.
sql
DROP TABLE Student;
TRUNCATE
Deletes all records but keeps table structure.
sql
TRUNCATE TABLE Student;
3.2 DML โ Data Manipulation Language
Used to insert, update, and delete records.
INSERT
sql
INSERT INTO Student VALUES (1, 'Rahul', 88, 20);
UPDATE
sql
UPDATE Student SET marks = 90 WHERE id = 1;
DELETE
sql
DELETE FROM Student WHERE marks < 40;
3.3 DQL โ Data Query Language
Used to retrieve data.
SELECT with WHERE
sql
SELECT name, marks FROM Student WHERE marks > 75;
ORDER BY
sql
SELECT * FROM Student ORDER BY marks DESC;
DISTINCT
sql
SELECT DISTINCT marks FROM Student;
3.4 TCL โ Transaction Control Language
Used to manage transactions.
COMMIT ROLLBACK SAVEPOINT
sql
SAVEPOINT sp
ROLLBACK TO sp
COMMIT
3.5 DCL โ Data Control Language
Used to control access.
sql
SELECT s.name, c.course
FROM Student s
INNER JOIN Course c
ON s.id = c.sid;
- Aggregate Functions
Used to perform calculations.
COUNT() SUM() AVG() MIN() MAX()
sql
SELECT AVG(marks) FROM Student;
- GROUP BY and HAVING
sql
SELECT dept, COUNT(*)
FROM Employee
GROUP BY dept
HAVING COUNT(*) > 3;
Difference:
WHERE โ filters rows
HAVING โ filters groups
- Subqueries
A query inside another query.
sql
SELECT name
FROM Student
WHERE marks > (SELECT AVG(marks) FROM Student);
- Normalization
Process of minimizing redundancy.
Normal Forms
1NF โ Atomic values
2NF โ No partial dependency
3NF โ No transitive dependency
- Indexing
Used to speed up data retrieval.
sql
CREATE INDEX idx_marks ON Student(marks);
Types:
Primary Index Secondary Index Clustered Index
3NF โ No transitive dependency BCNF โ Stronger than 3NF
15.functional dependency
A relationship between attributes.
Example:
sql
A โ B
A determines B.
16.Deadlock in DBMS
A situation where transactions wait indefinitely for resources.
Conditions
Mutual exclusion Hold and wait No preemption Circular wait
17.Concurrency Control
Ensures correctness in multi-user environments.
Techniques:
Locking Timestamp ordering Two-phase locking (2PL)
18.Recovery Techniques
Used after system failure.
Log-based recovery Checkpoints Shadow paging
19.DBMS vs RDBMS
DBMS RDBMS
No relationships User relationships
Less secure More secure
Small data Large data
- DBMS Interview Questions &
Answers
Q1. What is DBMS?
Software for managing databases.
Q2. Difference between DELETE and TRUNCATE?
DELETE removes rows selectively; TRUNCATE removes all rows.
Q3. What is normalization?
Process of reducing redundancy.
Q4. What is a primary key?
Unique identifier for records.
Q5. What is indexing?
Technique to improve query performance.
27.Advantages of DBMS
Reduced redundancy
Improved integrity
