DBMS & SQL Cheat Sheet (Simplified Notes)
1. Problems with File System:
- Duplicate data, inconsistency, hard to access, no security.
Example: Rahul's age 20 in one file, 22 in another.
2. What is DBMS?
- Software to store/manage data. Examples: MySQL, Oracle, MongoDB.
3. Views of Data:
- Physical: storage details.
- Logical: tables & relations.
- View: what user sees (can hide sensitive info).
4. Schema vs Instance:
- Schema = design (Student table).
- Instance = actual data (1,Ravi).
5. ACID Properties:
- Atomicity (all-or-nothing)
- Consistency (rules followed)
- Isolation (transactions don't clash)
- Durability (data stays safe after crash)
6. SQL Basics:
- DDL: create/alter/delete tables.
- DML: insert/update/delete data.
- DQL: select data.
7. Create Table Example:
create table Product(
Pname varchar(20) primary key,
Price float not null,
Category varchar(20) check(Category in ('Gadget','Photography','Household')),
Manufacturer varchar(20)
);
8. Insert Example:
insert into Product values("Gizmo",19.99,"Gadgets","GizmoWorks");
9. Select Queries:
- Select all: select * from Product;
- Specific cols: select Pname, Price from Product;
- With condition: select * from Product where Price > 100;
10. LIKE Operator:
- 'P%' -> starts with P
- '%Touch' -> ends with Touch
- '%o%' -> contains 'o'
- '_o%' -> 2nd letter is 'o'
11. Aggregate Functions:
- sum(price), avg(price), max(price), min(price), count(*)
Example: select avg(price) from Product;
12. Ordering:
select * from Product order by Price desc;
13. Practice (COMPDTLS table):
- All companies: select * from COMPDTLS;
- Japan companies: select * from COMPDTLS where Country='Japan';
- Max stock price: select max(StockPrice) from COMPDTLS;
- Country ending with 'a': select CompName from COMPDTLS where Country like '%a';
