This lesson covers practical schema changes and views: ALTER TABLE changes an existing object, DROP removes an object, TRUNCATE removes table rows using engine-specific semantics, and a view gives a reusable name to a query. These operations can be destructive or behave differently across database engines.
Key Takeaways at a Glance:
- •A View is a stored SQL query definition that acts as a virtual table without storing data separately on disk.
- •Security & Abstraction: Views restrict user access to sensitive columns (e.g. hiding passwords or salaries) while exposing non-sensitive data.
- •ALTER TABLE modifies table definitions (adding/dropping columns, changing constraints).
- •Materialized Views (PostgreSQL/Oracle) cache the physical result set on disk and require manual REFRESH, whereas standard views run dynamically on query invocation.
In Simple Words (The Mental Model)
A view is like a saved bookmark for a complex query. Instead of typing a 4-table join with 10 filters every single morning, you save it once as a view: 'CREATE VIEW daily_sales AS ...'. From then on, you can treat it like a normal table: 'SELECT * FROM daily_sales'.
Real-World Analogy
Think of looking through a customized window in a bank. The customer only sees the bank teller's window and counter (the View). They cannot see into the vault, security cameras, or employee breakroom behind the wall (hidden base table data).