DBMS Concepts & SQL - Simple Explanation
1. Why DBMS is Needed?
- Old file systems had problems like duplication, difficulty in searching data, and inconsistency.
- Example: If a student s name is stored in two files differently, confusion arises. DBMS solves this.
2. What is DBMS?
- A software that stores, organizes, and manages data efficiently.
- Example: Banking systems use DBMS to track customer accounts.
3. Types of DBMS:
- Oracle, MySQL, SQL Server, PostgreSQL, MongoDB.
4. Levels of Abstraction:
- Physical: How data is stored in memory (like a hard disk).
- Logical: Relationship between tables (like Students and Courses).
- View: What the user sees (like hiding salary info for privacy).
5. Schema & Instance:
- Schema: Structure/design of database (like blueprint of a house).
- Instance: Actual data at a moment (like people living in that house).
6. ACID Properties:
- A: Atomicity All-or-nothing.
Example: Money transfer if debit succeeds but credit fails, rollback.
- C: Consistency Data must follow rules.
Page 1
DBMS Concepts & SQL - Simple Explanation
Example: Balance cannot be negative.
- I: Isolation Transactions don t interfere.
Example: Two ATMs withdrawing at same time won t clash.
- D: Durability Data is safe after commit.
Example: Even after a crash, saved transfer remains.
7. SQL (Structured Query Language):
- DDL: Create, alter, delete tables.
- DML: Insert, update, delete records.
Example Queries:
- Create Table:
CREATE TABLE Product(Pname VARCHAR(20) PRIMARY KEY, Price FLOAT, Category
VARCHAR(20));
- Insert Data:
INSERT INTO Product VALUES('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');
- Select Data:
SELECT * FROM Product;
- Conditions:
SELECT Pname, Price FROM Product WHERE Category = 'Gadgets';
- Aggregates:
Page 2
DBMS Concepts & SQL - Simple Explanation
SELECT AVG(Price) FROM Product;
8. LIKE Operator (pattern matching):
- % for any characters, _ for single character.
Example: SELECT * FROM Product WHERE Pname LIKE 'P%' (names starting with P).
9. Ordering Results:
SELECT * FROM Product ORDER BY Price DESC;
10. Practice Exercise:
- Create table COMPDTLS (Company details with name, date, stock price, country).
- Write queries like:
- Show companies from Japan.
- Find max stock price.
- Show company names ending with 'a'.
Page 3
