views.sql

-- Type: Horizontal view 
-- Description: Displays basic class information by showing only class names and their duration
CREATE OR REPLACE VIEW class_name_duration AS
SELECT 
class_name,
duration_minutes
FROM classes;

-- Type: Vertical view
-- Description: Shows classes with high capacity, including only those that can accommodate more than 15 participants
CREATE OR REPLACE VIEW popular_classes AS
SELECT *
FROM classes
WHERE capacity > 15;

-- Purpose: Shows classes with duration exactly 60 minutes
-- Type: Mixed view 
CREATE OR REPLACE VIEW standard_duration_minutes AS 
SELECT  
class_name,
duration_minutes
FROM classes
WHERE duration_minutes = 60;

-- Purpose: Combines classes with their scheduled occurrences
-- Type: View with JOIN
CREATE OR REPLACE VIEW classes_schedule AS 
SELECT 
c.class_name,
o.start_datetime
FROM classes AS c 
JOIN class_occurrences AS o ON c.class_id = o.class_id;

-- Purpose: Shows classes that start in the evening (after 17:00)
-- Type: View with JOIN and filtering
CREATE OR REPLACE VIEW evening_classes AS 
SELECT 
c.class_name,
o.start_datetime
FROM classes AS c
JOIN class_occurrences AS o 
ON c.class_id = o.class_id 
WHERE EXTRACT(HOUR FROM o.start_datetime) >= 17;

-- Purpose: Combines past and upcoming class occurrences
-- Type: View with UNION
CREATE OR REPLACE VIEW past_upcoming_classes AS
SELECT
    c.class_name,
    o.start_datetime,
    'past' AS occurrence_status
FROM classes AS c
JOIN class_occurrences AS o ON c.class_id = o.class_id
WHERE o.start_datetime < CURRENT_TIMESTAMP

UNION

SELECT
    c.class_name,
    o.start_datetime,
    'upcoming' AS occurrence_status
FROM classes AS c
JOIN class_occurrences AS o ON c.class_id = o.class_id
WHERE o.start_datetime >= CURRENT_TIMESTAMP;

-- Purpose: Shows only morning classes based on classes_schedule view
-- Type: View based on another view
CREATE OR REPLACE VIEW morning_classes_only AS
SELECT *
FROM classes_schedule
WHERE EXTRACT(HOUR FROM start_datetime) < 12;

-- Purpose: Allows updates only for morning classes (before 12:00)
-- Type: Updatable view with CHECK OPTION
CREATE OR REPLACE VIEW morning_classes_check AS 
SELECT c.class_name,
       o.start_datetime
FROM classes AS c 
JOIN class_occurrences AS o ON c.class_id = o.class_id 
WHERE EXTRACT(HOUR FROM o.start_datetime) < 12
WITH CHECK OPTION;