Creating Basic SQL Views

The syntax for creating a view is straightforward using the CREATE VIEW statement, followed by the SELECT query that defines the view's structure and content.

CREATE [OR REPLACE] VIEW view_name [(column_list)] AS
SELECT column1, column2, ...
FROM table_name(s)
[WHERE condition]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

- OR REPLACE (optional): Allows you to modify an existing view without dropping it first.
- column_list (optional): Explicitly names the columns in the view. If omitted, the view inherits column names from the SELECT statement.

Once created, you query the view exactly like a table:

SELECT column_name FROM view_name WHERE condition;

Let's create some views for our TokTuk database:

CREATE VIEW VideoPerformanceSummary AS
SELECT
  v.video_id,
  v.title,
  u.username AS creator_username,
  v.views,
  v.upload_date,
  COUNT(CASE WHEN i.interaction_type = 'like' THEN 1 END) AS like_count,
  COUNT(CASE WHEN i.interaction_type = 'comment' THEN 1 END) AS comment_count,
  COUNT(i.interaction_id) AS total_interactions
FROM Videos v
JOIN Users u ON v.user_id = u.user_id
LEFT JOIN Interactions i ON v.video_id = i.video_id
GROUP BY v.video_id, v.title, u.username, v.views, v.upload_date;

When to Use SQL Views

Views are particularly beneficial in these scenarios:

Modifying Data Through Views (Updatable Views)

While primarily for querying, some views allow data modification (INSERT, UPDATE, DELETE) operations that affect the underlying base table(s). However, strict rules apply for a view to be considered "updatable".

-- View showing only essential video details
CREATE VIEW BasicVideoInfo AS
SELECT video_id, title, user_id, description
FROM Videos;

-- UPDATE the title via the view
UPDATE BasicVideoInfo
SET description = 'Updated description for this video.'
WHERE video_id = 101; -- This updates the underlying Videos table

-- This view uses aggregation and is NOT updatable
CREATE VIEW UserVideoCounts AS
SELECT user_id, COUNT(*) as video_count
FROM Videos
GROUP BY user_id;

-- This would cause an ERROR:
-- UPDATE UserVideoCounts SET video_count = 10 WHERE user_id = 1;

Materialized Views (Performance Enhancement)

Unlike standard views which re-run their query each time they are accessed, a Materialized View physically stores the result set of its defining query. It's like a snapshot or a cache of the data.

• Benefit: Querying a materialized view is often much faster than querying a complex standard view, as the results are pre-computed and stored. This is especially useful for complex aggregations or joins over large datasets used frequently in reporting or dashboards.
• Drawback: The data in a materialized view can become stale. It needs to be explicitly refreshed (updated) periodically to reflect changes in the underlying base tables. The refresh process can be resource-intensive.
• Syntax & Features: The syntax (CREATE MATERIALIZED VIEW) and refresh options (REFRESH MATERIALIZED VIEW - manual, scheduled, on commit) vary significantly between database systems (e.g., PostgreSQL, Oracle offer robust support; SQL Server uses Indexed Views; MySQL has limited native support).

-- Example (PostgreSQL Syntax): Create a materialized view
CREATE MATERIALIZED VIEW DailyVideoStats AS
SELECT
  DATE(upload_date) AS stat_day,
  COUNT(*) AS videos_uploaded,
  SUM(views) AS total_views
FROM Videos
GROUP BY stat_day;

-- Query the stored results (fast)
SELECT * FROM DailyVideoStats ORDER BY stat_day DESC;

-- Refresh the stored results later
REFRESH MATERIALIZED VIEW DailyVideoStats;

Materialized views are a performance optimization technique for read-heavy workloads involving complex view logic.

Managing Views

Like other database objects, views can be modified or removed.

• Modifying Views: Use CREATE OR REPLACE VIEW to change the definition of an existing view, or use ALTER VIEW view_name AS ... (syntax varies). Modifying a view changes its underlying query but doesn't affect data in base tables.
• Dropping Views: Use DROP VIEW [IF EXISTS] view_name; to permanently remove a view from the database. Dropping a view does not affect the data in the underlying base tables.

-- Modify the popular_videos view threshold
CREATE OR REPLACE VIEW popular_videos AS
SELECT ... FROM Videos v JOIN Users u ON ...
WHERE v.views > 5000; -- Changed threshold

-- Drop a view that is no longer needed
DROP VIEW IF EXISTS OldUserReportView;

Summary

SQL Views are powerful constructs that offer significant benefits:

• They act as virtual tables defined by stored SELECT queries.
• Standard views don't store data themselves but execute their query when accessed.
• Key Uses: Simplify complex queries, enhance security (row/column level), provide logical data independence, and ensure consistency.
• Views can be updatable under specific conditions (typically single-table, no aggregates/grouping/distinct). Use WITH CHECK OPTION for safety.
• Materialized Views store their results physically for faster query performance but require periodic refreshing.
• Manage views using CREATE [OR REPLACE] VIEW, ALTER VIEW, and DROP VIEW.

Leveraging views effectively demonstrates a solid understanding of database design principles and is a valuable skill for developers, analysts, and administrators.