01
কেন VIEW দরকার?
১৫ মিনিট
বড় employee table — অনেক column। HR শুধু চায়: id, name, department, joining_date।
জিজ্ঞেস
একই SELECT বারবার লিখতে ভালো লাগে? যদি query-কে একটা নাম দিয়ে রাখা যেত?
Complex / Repeated Query → VIEW → Reusable Virtual Table
View = saved SQL query যেটাকে table-এর মতো ব্যবহার করা যায়।
02
Business Stories
১৫ মিনিট
HR
id, name, dept, join — salary/bank hide → HR View
Sales
product, category, sales, date → Sales Report View
Finance
invoice, customer, amount, status → Finance View
Support
customer, email, order status — no finance → Support View
03
TABLE vs VIEW
১৫ মিনিট
DATABASE
├── TABLE → stores actual rows/data
└── VIEW → stores SQL query definition → result when queried
| বিষয় | TABLE | VIEW |
|---|---|---|
| Actual data store? | Yes | Normally no |
| Query definition? | No | Yes |
| Physical rows? | Yes | Derived result |
| SELECT like table? | Yes | Yes |
| Simplify complex SQL? | Not main job | Yes |
| Security help? | Limited | Useful |
সাধারণ View-এ result permanently আলাদা copy হিসেবে থাকে না।
04
Window Analogy
১০ মিনিট
FULL TABLE: Name Email Salary Phone Address Dept JoinDate
VIEW WINDOW: Name | Department | Joining Date
পুরো room না দেখিয়ে প্রয়োজনীয় জানালা — সেটাই View।
05
First CREATE VIEW
১৫ মিনিট
CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2),
joining_date DATE
);
INSERT INTO employees VALUES
(101,'Rahim','IT',60000,'2023-01-10'),
(102,'Karim','HR',50000,'2022-05-15'),
(103,'Jannat','IT',70000,'2024-02-20'),
(104,'Sakib','Sales',55000,'2023-08-12'),
(105,'Nadia','HR',65000,'2021-11-01');
CREATE VIEW employee_basic_view AS
SELECT employee_id, employee_name, department, joining_date
FROM employees;
CREATE VIEW → View Name → AS → SELECT Query
06
Read a View
১০ মিনিট
SELECT * FROM employee_basic_view;
| employee_id | employee_name | department | joining_date |
|---|---|---|---|
| 101 | Rahim | IT | 2023-01-10 |
| 102 | Karim | HR | 2022-05-15 |
| 103 | Jannat | IT | 2024-02-20 |
| 104 | Sakib | Sales | 2023-08-12 |
| 105 | Nadia | HR | 2021-11-01 |
employees → SELECT in VIEW → employee_basic_view → SELECT → Result
View-কে table-এর মতো SELECT করা যায়।
07
কেন Useful?
১০ মিনিট
Without View: long SELECT every time
With View: SELECT * FROM employee_basic_view
Long Query → Create Once → Name → Reuse
Benefit: less repeat · easier reports · dashboards · organization
08
Filtered View
১০ মিনিট
CREATE VIEW it_employees AS
SELECT employee_id, employee_name, department, joining_date
FROM employees
WHERE department = 'IT';
SELECT * FROM it_employees;
101 Rahim IT · 103 Jannat IT
WHERE আগের module — এখানে শুধু View-এর ভিতরে filter রাখা যায়।
09
Hide Sensitive Columns
১০ মিনিট
CREATE VIEW employee_public_view AS
SELECT employee_id, employee_name, department
FROM employees;
Full Table → Hide salary/phone/bank → Public View
View একটা security layer হতে পারে — কিন্তু একাই complete security নয়।
10
View vs Original Table
১০ মিনিট
employees: id name dept salary join
employee_basic_view: id name dept join
(salary নেই — SELECT-এ রাখা হয়নি)
11
Multi-table View
১৫ মিনিট
CREATE VIEW employee_department_view AS
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
SELECT * FROM employee_department_view;
Complex JOIN → VIEW → Simple SELECT → Report
12
Business Reporting View
১০ মিনিট
Raw DB → Complex SQL → Sales View → BI Dashboard → Decision
CREATE VIEW sales_report_view AS
SELECT o.order_id, c.customer_name, p.product_name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;
13
Aggregate Report View
১০ মিনিট
CREATE VIEW department_summary AS
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
SELECT * FROM department_summary;
GROUP BY/COUNT আগের module — এখানে View-এ reusable summary।
14
Name Already Exists
৫ মিনিট
একই নামে View থাকলে সাধারণ CREATE VIEW fail হতে পারে। Safe workflow: check → replace বা drop → recreate।
15
List / Inspect Views
১০ মিনিট
SHOW FULL TABLES
WHERE Table_type = 'VIEW';
SHOW CREATE VIEW employee_basic_view;
কোন object View, এবং তার SQL definition দেখা।
16
CREATE OR REPLACE VIEW
১৫ মিনিট
CREATE OR REPLACE VIEW employee_basic_view AS
SELECT employee_id, employee_name, department, salary, joining_date
FROM employees;
Old View → CREATE OR REPLACE → New Definition → Updated View
নতুন SELECT carefully check করো।
17
DROP VIEW
১০ মিনিট
DROP VIEW employee_basic_view;
DROP VIEW → remove definition
employees TABLE → still exists (data not deleted)
18
DROP VIEW IF EXISTS
৫ মিনিট
DROP VIEW IF EXISTS employee_basic_view;
Scripts/deploy-এ safe — View না থাকলে error এড়ায়।
19
INSERT/UPDATE via View?
১৫ মিনিট
Simple View → may be updatable
Complex View (agg / GROUP BY / DISTINCT / complex JOIN) → often not
এখানে শুধু limitation — সেই topics পুনরায় শেখানো হচ্ছে না।
20
View Security
১০ মিনিট
CREATE VIEW employee_reporting_view AS
SELECT employee_name, department
FROM employees;
Sensitive Base → Restricted View → Reporting Team
(+ proper permissions still required)
21
Data Freshness
১০ মিনিট
Base Table Changes → View Query Runs → Updated Result
Normal View ≈ not a separate stale copy
22
View ≠ Table Copy
১০ মিনিট
TABLE = box of cooked food (data)
VIEW = reusable order/instruction how to serve
23
Detailed Comparison
৮ মিনিট
| Feature | Table | View |
|---|---|---|
| Stores data | Yes | Usually no |
| Query definition | No | Yes |
| SELECT | Yes | Yes |
| Simplify complex SQL | Limited | Yes |
| Hide columns | Not main job | Yes |
| Reflects base changes | Is the data | Usually yes |
24
View vs Saved Query Text
৬ মিনিট
View শুধু নোটপ্যাডে SQL নয় — database object নামসহ, table-এর মতো reference করা যায়।
25
Dashboards
৮ মিনিট
Raw Tables → Complex SQL → Business View → BI Tool → Dashboard
Examples: sales_view · customer_view · finance_view
26
E-commerce Sales View
১০ মিনিট
CREATE VIEW ecommerce_sales_view AS
SELECT o.order_id, c.customer_name, p.product_name, p.category,
o.quantity * p.price AS sales_amount, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;
SELECT * FROM ecommerce_sales_view;
Raw → Multi tables → Logic → Reusable View → Dashboard → CEO
27
Mini Project — Employee Reporting
১৫ মিনিট
employees + departments · ১৫+ employees।
- employee_basic_view
- hr_view (incl. salary)
- public_view (no sensitive)
- department_summary
- management multi-table view
Lifecycle: create → query → inspect → replace → drop → recreate → compare → dashboard-ready.
28
Live Lab Steps
১২ মিনিট
- CREATE DATABASE company_db
- Create tables + insert
- CREATE VIEW basic
- SELECT FROM view
- Filtered view
- Reporting view
- Multi-table view
- SHOW CREATE VIEW
- CREATE OR REPLACE
- DROP VIEW IF EXISTS
29
Common Mistakes
১০ মিনিট
- View = separate data copy ভাবা
- DROP VIEW → table যাবে ভাবা
- Same name recreate conflict
- Base change / deps ignore
- Too many useless views
- Confusing names
- Sensitive cols in public view
- Assume always updatable
- Blind SELECT *
- Over-complex nested views
30
Naming Best Practices
৫ মিনিট
Good: employee_basic_view · sales_report_view · finance_reporting_view
Bad: view1 · test_view · abc · temp123
31
Professional Design
৬ মিনিট
Good
Clear purpose · needed cols · simple · reusable · documented
Bad
Everything · too many cols · heavy logic · hard maintain
32
Performance
৮ মিনিট
Complex Query → VIEW → Still same underlying work
View = organization / reuse / abstraction / access
≠ automatic speedup
Indexes on base tables still matter · nested views hard to debug
Materialized/precompute = অন্য topic (deep dive নয়)
33
Dependency
৮ মিনিট
employees → employee_basic_view → dashboard
Base structure change → may break view/dashboard
Document dependencies in production
34
Industry Workflow
৮ মিনিট
Tables → Data Engineer → View → BI → Dashboard → Analyst → Management
Roles: Engineer access layer · Analyst query · BI connect · DS datasets · DBA objects/perms
35
Activities (১৫)
১০ মিনিট
Act 1 — Table vs View?
Data vs query def
Act 2 — Predict view columns
Only SELECT list
Act 3 — Create basic view
CREATE VIEW … AS SELECT
Act 4 — Query view
SELECT * FROM view
Act 5 — Remove sensitive cols
Omit from SELECT
Act 6 — Reporting view
Business columns only
Act 7 — Filtered view
WHERE in view SQL
Act 8 — Multi-table view
JOIN inside AS
Act 9 — Inspect
SHOW CREATE VIEW
Act 10 — Replace
CREATE OR REPLACE VIEW
Act 11 — Drop
DROP VIEW / IF EXISTS
Act 12 — Find mistake
Missing AS / wrong DROP
Act 13 — Dashboard view
Clean BI-ready cols
Act 14 — When use view?
Reuse/hide/simplify
Act 15 — Mini project
5 employee views
36
Predict the Output (১৫)
১০ মিনিট
Predict: SELECT * FROM it_employees (IT filter)
শুধু IT rows
Predict: New IT emp inserted — query View?
নতুন row দেখা যাবে (normal view)
Predict: DROP VIEW — employees table?
Table থাকে
Predict: View has no salary — why?
SELECT-এ রাখা হয়নি
Predict: CREATE same name again?
Often fails — use OR REPLACE
Predict: Complex GROUP BY view updatable?
Usually not
Predict: Base salary UPDATE — View shows?
Fresh when queried
Predict: SHOW CREATE VIEW shows?
View SQL definition
Predict: SELECT * FROM view — like table?
Yes
Predict: View stores separate copy?
Normally no
Predict: DROP employees — dependent View?
Can break / dependency issue
Predict: Public view purpose?
Hide sensitive cols
Predict: Multi JOIN in view — user writes?
Simple SELECT FROM view
Predict: department_summary rows?
One per department
Predict: Bad name view1?
Avoid — use purpose names
37
Debugging (১০)
১০ মিনিট
Bug: CREATE VIEW v SELECT * FROM t
Missing AS
Bug: DROP employee_view
Need DROP VIEW
Bug: CREATE VIEW from missing table
Dependency fails
Bug: Think DROP VIEW deletes table data
No — only view def
Bug: Recreate same name without replace
Conflict
Bug: SELECT * forever in prod view
Schema change risk
Bug: Assume all views updatable
Aggregates/JOIN may block
Bug: Put salary in public view
Security mistake
Bug: 20 nested views unexplained
Hard to maintain
Bug: Meaningless names abc/temp
Use clear names
38
Interview (৩০)
১২ মিনিট
IV 1. View কী?
Saved query as named DB object · উদা: employee_basic_view
IV 2. কেন?
Reuse · hide cols · simplify · reports
IV 3. vs Table?
Data vs definition
IV 4. Stores data?
Normally no
IV 5. CREATE syntax?
CREATE VIEW name AS SELECT …
IV 6. Query?
SELECT FROM view
IV 7. Modify?
CREATE OR REPLACE VIEW
IV 8. Delete?
DROP VIEW
IV 9. Inspect?
SHOW CREATE VIEW
IV 10. WHERE in view?
Yes
IV 11. JOIN in view?
Yes
IV 12. GROUP BY in view?
Yes
IV 13. Aggregates?
Yes in definition
IV 14. All updatable?
No
IV 15. Non-updatable?
Agg/GROUP/DISTINCT/complex JOIN…
IV 16. Security?
Hide cols + permissions still needed
IV 17. Always faster?
No — abstraction not auto speed
IV 18. Base data change?
Normal view reflects on query
IV 19. Column removed?
View may break — dependency
IV 20. Avoid SELECT *?
Schema drift risk
IV 21. Dependency?
View depends on base objects
IV 22. BI?
Dashboard queries views
IV 23. Analysts?
Clean reusable datasets
IV 24. Engineers?
Access layer
IV 25. DBAs?
Objects, perms, deps
IV 26. Reporting view?
Business report shape
IV 27. Security view?
Restricted columns
IV 28. Nested views?
Can get hard to debug
IV 29. Physical table vs view?
Store vs query
IV 30. Real use?
HR public employee list
39
MCQ (২৫)
১০ মিনিট
MCQ 1. View is? A) always data copy B) saved query object C) index D) PK
B
MCQ 2. CREATE needs? A) AS SELECT B) only DROP C) JOIN only D) LIMIT only
A
MCQ 3. Read view? A) SELECT FROM view B) only INSERT C) only UPDATE D) DROP TABLE
A
MCQ 4. DROP VIEW deletes table data? A) yes B) no C) always D) sometimes PK
B
MCQ 5. OR REPLACE does? A) update definition B) delete DB C) create index D) GRANT
A
MCQ 6. Normal view stores rows? A) usually no B) always yes C) only MySQL 5 D) never SELECT
A
MCQ 7. Hide salary how? A) omit from view SELECT B) DROP table C) UNION D) LIMIT 0
A
MCQ 8. Always faster? A) yes B) no C) only JOIN D) only COUNT
B
MCQ 9. Base UPDATE then View? A) stale forever B) reflects on query C) error always D) needs DROP
B
MCQ 10. SHOW CREATE VIEW? A) definition B) drop data C) create table D) backup
A
MCQ 11. Aggregate view updatable? A) usually no B) always yes C) only DROP D) only TRUNCATE
A
MCQ 12. Missing AS? A) syntax error B) OK C) auto D) creates table
A
MCQ 13. DROP employee_view wrong? A) need DROP VIEW B) OK C) deletes DB D) GRANT
A
MCQ 14. Security complete with view alone? A) no — need permissions B) yes always C) only TRIM D) only LIKE
A
MCQ 15. Good name? A) view1 B) sales_report_view C) abc D) temp
B
MCQ 16. Multi JOIN purpose in view?
Simple SELECT for users
MCQ 17. IF EXISTS on DROP?
Safer scripts
MCQ 18. Dependency means?
View relies on base tables
MCQ 19. SELECT * risk in prod view?
Column changes break consumers
MCQ 20. Table vs View storage?
Table stores data; view stores query
MCQ 21. Filtered IT view shows?
Only IT rows
MCQ 22. Materialized deep dive?
Out of this class
MCQ 23. Dashboard connects to?
Often views like tables
MCQ 24. Focus of class? A) JOIN reteach B) VIEW C) REGEXP D) Python
B
MCQ 25. Mental model?
Saved query · use like table
40
Viva (২০)
৮ মিনিট
Viva 1. View কী?
Saved query object
Viva 2. Table?
না — আলাদা
Viva 3. Data store?
সাধারণত না
Viva 4. কেন?
Reuse/hide/simplify
Viva 5. CREATE?
CREATE VIEW … AS SELECT
Viva 6. Query?
SELECT FROM view
Viva 7. Delete?
DROP VIEW
Viva 8. Hide columns?
হ্যাঁ — omit
Viva 9. Base change?
Reflects on query
Viva 10. All updatable?
না
Viva 11. Faster always?
না
Viva 12. Inspect?
SHOW CREATE VIEW
Viva 13. Replace?
CREATE OR REPLACE
Viva 14. Safe drop?
DROP VIEW IF EXISTS
Viva 15. JOIN in view?
হ্যাঁ
Viva 16. Aggregate view?
হ্যাঁ for reports
Viva 17. Dependency?
Base objects matter
Viva 18. BI?
Dashboard source
Viva 19. Naming?
Purpose-clear
Viva 20. One line?
Saved query used like a table
41
Exercises (২০)
১২ মিনিট
Ex 1 — Basic employee view
CREATE VIEW … AS SELECT id,name,dept,join
Ex 2 — Query it
SELECT * FROM employee_basic_view
Ex 3 — Selected columns
Omit salary
Ex 4 — Filtered
WHERE department='IT'
Ex 5 — Drop
DROP VIEW / IF EXISTS
Ex 6 — Salary report view
Include salary for HR only
Ex 7 — Department view
Filter or project dept
Ex 8 — Multi-table
JOIN employees departments
Ex 9 — Aggregate
department_summary
Ex 10 — Inspect
SHOW CREATE VIEW
Ex 11 — Replace
CREATE OR REPLACE
Ex 12 — Dashboard view
Clean sales cols
Ex 13 — Security view
Public columns only
Ex 14 — Updatable vs not
Simple vs GROUP BY
Ex 15 — Dependency
Rename base col impact
Ex 16–20 — Mini project
5 views + lifecycle
42
Quick Revision + Memory Map
৫ মিনিট
CREATE VIEW · SELECT FROM VIEW · CREATE OR REPLACE VIEW
SHOW CREATE VIEW · DROP VIEW · DROP VIEW IF EXISTS
TABLE = data · VIEW = query definition · SELECT like table
BASE TABLES → SQL LOGIC → VIEW → Analyst / BI / Report → Decision
VIEW = saved SQL query used like a tableVIEW = একটি saved SQL query, যেটাকে table-এর মতো ব্যবহার করা যায়।