SQL CREATE VIEW
The syntax that saves a query under a name.
What & Why
CREATE VIEW takes any valid SELECT statement and saves it under a name, permanently, until dropped.
See How It Works
BUSINESS QUESTION
Marketing wants to define the active-campaign view introduced in the previous lesson.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
-- Run this reviewed DDL outside the read-only lesson sandbox:
-- CREATE VIEW public.active_campaign_summary AS
SELECT
id AS campaign_id,
name,
channel,
spend,
start_date,
end_date
FROM marketing.campaigns
WHERE status = 'active'
ORDER BY campaign_id;RESULT — rows exposed by the active-campaign SELECT
| campaign_id | name | channel | spend | start_date | end_date |
|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 |
All representative campaigns currently have active status.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario