Climate Conference Analysis

-- This is the data I am using for my analysis:
--Climate Conference Project
--Database: https://www.db-fiddle.com/f/pxMci1sLL945KHKYvpe9W6/1

  CREATE TABLE vips (
     member_id INTEGER PRIMARY KEY
   , last_name TEXT
   , first_name TEXT
   , event INTEGER
   , provides_feedback_to INTEGER
   , association TEXT
   , assoc_type TEXT
   , member_since INTEGER
   );
   
CREATE TABLE reservations ( 
    member_id INTEGER
  , hotel INTEGER
  , welcome_rsvp BIT
  , dinner_rsvp BIT
  );
 
 
CREATE TABLE events (
    event_id INTEGER PRIMARY KEY
  , event_name TEXT
  , event_type TEXT
  );
  

CREATE TABLE hotels (
    hotel_id INTEGER PRIMARY KEY
  , hotel_name TEXT
  , address TEXT
  );


INSERT INTO vips VALUES
(2814, 'Fletcher', 'David', 5, 3742, 'Anishinabek Educational Institute', 'education', 2004)
,(1944, 'Kim', 'Jiho', 4, 4230, 'Appalachian State', 'education', 1991)
,(3889, 'Kowalski', 'Alex', 9, 3460, 'Brigg & McKinnnon AEC', 'private', 2015)
,(3775, 'Al-Hassan', 'Amina', 6, 3891, 'California Department of Water Resources', 'government', 2013)
,(4003, 'Chang', 'Nathan', 8, 3960, 'Chang Engineers, LLC', 'private', 2017)
,(3490, 'Anderson', 'Shanae', 2, 4315, 'Chicago Department of Public Health', 'government', 2008)
,(3077, 'Murphy', 'Aisling', 3, 2731, 'Climate Impacts on Farming Institute', 'research', 2006)
,(3960, 'Ivanov', 'Dmitri', 6, 2814, 'Cooperative Institute for Climate, Ocean, and Ecosystem Studies', 'research', 2016)
,(4402, 'Bennett', 'Leah', NULL, NULL, 'Denver Climate Research Foundation', 'research', 2024)
,(3891, 'Roy', 'Harper', 4, 3077, 'Environmental Science Associates', 'private', 2015)
,(4386, 'Silva', 'Thiago', NULL, NULL, 'Everglades Research Institute', 'research', 2023)
,(4297, 'Chukwu', 'Ifeoma', 9, 2716, 'Georgia State University', 'education', 2020)
,(3189, 'Martinez', 'Isabella', 8, 2011, 'Iowa State University', 'education', 2007)
,(3491, 'Gonzalez', 'Victor', 5, 3047, 'McGill University', 'education', 2008)
,(3742, 'Brown', 'Timothy', 3, 3490, 'Michigan State University', 'education', 2012)
,(4361, 'Riley', 'Daniel', 6, 1944, 'Minnesota Water Board', 'education', 2023)
,(1934, 'Garcia', 'Mateo', NULL, NULL, 'Oklahoma Department of Wildlife Conservation', 'government', 1990)
,(2011, 'Patel', 'Anika', 7, 3491, 'Renewable Energy Research Center, Honolulu', 'research', 1995)
,(4315, 'Shepherd', 'Priya', 10, 4003, 'Tech517', 'private', 2022)
,(1738, 'Johnson', 'Denny', NULL, NULL, 'Texas Department of Conservation and Natural Resources', 'government', 1987)
,(4230, 'Parker', 'Moss', 2, 3719, 'University of California, Davis', 'education', 2019)
,(2731, 'Rivera', 'Juan', 1, 4297, 'University of Montana', 'education', 2003)
,(2091, 'Haines', 'Keisha', 11, 3189, 'University of North Carolina', 'education', 1998)
,(2551, 'Nguyen', 'Linh', 2, 2091, 'University of Pennsylvania', 'education', 2002)
,(3047, 'Singh', 'Aarav', 4, 2551, 'US Department of Fish and Wildlife', 'government', 2005)
,(4112, 'Fernandez', 'Lucia', NULL, NULL, 'US Department of Fish and Wildlife', 'government', 2018)
,(3719, 'Denton', 'K. L.', 11, 3889, 'US Department of Housing and Urban Development', 'government', 2011)
,(3460, 'Chen', 'Beverly', 7, 4361, 'US Department of Natural Resources', 'government', 2008)
,(2716, 'Rodriguez', 'Sofia', 7, 3775, 'US Department of Renewable Energy', 'government', 2003)
,(2800, 'Lee', 'Min-Jun', NULL, NULL, 'US Department of Renewable Energy', 'government', 2004)
;


INSERT INTO reservations VALUES
 (3491,  1, 1, 1)
,(4361,  1, 1, 1)
,(3047,  2, 1, 1)
,(3775,  1, 1, 1)
,(4003,  3, 0, 1)
,(4402,  2, 1, 1)
,(3891,  3, 1, 1)
,(2011,  1, 1, 1)
,(4808,  2, 1, 1)
,(3490,  1, 0, 1)
,(3189,  1, 1, 1)
,(1944,  1, 0, 1)
,(2716,  2, 0, 1)
,(2551,  2, 1, 1)
,(4386,  2, 0, 1)
,(2814,  3, 1, 1)
,(1738,  2, 0, 1)
,(3960,  1, 1, 1)
,(4315,  3, 1, 0)
,(4722,  2, 1, 1)
,(2731,  3, 1, 1)
,(2091,  1, 1, 0)
,(3889,  3, 1, 1)
,(3742,  2, 0, 1)
,(3719,  2, 0, 1)
,(3460,  2, 1, 1)
,(3077,  2, 0, 1)
,(4420,  1, 1, 0)
,(2800,  2, 0, 1)
,(1934,  3, 1, 1)
;


INSERT INTO events VALUES
  (1, 'Climate Change Communication: Engaging the Public and Policy Makers', 'Keynote')
, (2, 'Climate Change and Public Health: Risks and Responses', 'Panel')
, (3, 'Climate Change and Agriculture: Ensuring Food Security', 'Panel')
, (4, 'Biodiversity and Ecosystem Services in a Changing Climate', 'Panel')
, (5, 'Indigenous Knowledge and Climate Change Adaptation', 'Panel')
, (6, 'The Role of Oceans in Climate Change: Impacts and Solutions', 'Panel')
, (7, 'Climate Change and Renewable Energy: Innovations and Challenges', 'Panel')
, (8, 'Climate Justice and Equity: Addressing Disproportionate Impacts', 'Panel')
, (9, 'The Economic Impact of Climate Change: Costs and Opportunities', 'Panel')
, (10, 'Technological Innovations for Climate Monitoring and Assessment', 'Workshop')
, (11, 'Urban Resilience: Designing Climate-Smart Cities', 'Workshop')
, (12, 'The Role of Policy in Combating Climate Change', 'Workshop')
;


INSERT INTO hotels VALUES
   (1, 'Hyatt Downtown', '392 South Main Street, Cityville, PA')
 , (2, 'Marriott', '1240 Marshall Avenue, Cityville, PA')
 , (3, 'Four Season''s', '900 Winter Road, Cityville, PA')
 ;

--1. Which vips are registered to attend the Keynote event? (LEFT JOIN)
SELECT vips.last_name, vips.first_name FROM vips
LEFT JOIN events ON events.event_id = vips.event
WHERE events.event_type = 'Keynote';

-- 2. Which guests returned a RSVP for dinner? (INNER JOIN)
SELECT vips.last_name, vips.first_name
FROM vips
INNER JOIN reservations
ON vips.member_id = reservations.member_id
WHERE reservations.dinner_rsvp = 1
ORDER BY vips.last_name;

-- 3. Which guests will be staying at the Four Seasons hotel? (Multiple JOINs)
SELECT vips.last_name, vips.first_name
FROM vips
LEFT JOIN reservations ON vips.member_id = reservations.member_id
JOIN hotels ON reservations.hotel = hotels.hotel_id
WHERE hotels.hotel_name = 'Four Season''s'
ORDER BY vips.last_name;

-- 4. Which guests will be staying at the same hotel and also have RSVPs for dinner? (Subquery) 
SELECT vips.last_name, vips.first_name, hotels.hotel_name AS hotel
FROM vips
JOIN reservations ON vips.member_id = reservations.member_id
JOIN hotels ON reservations.hotel = hotels.hotel_id
WHERE reservations.dinner_rsvp = 1 AND reservations.hotel IN (
SELECT reservations.hotel
FROM reservations
JOIN hotels ON reservations.hotel = hotels.hotel_id
WHERE reservations.dinner_rsvp = 1
GROUP BY reservations.hotel
HAVING COUNT(reservations.hotel) > 1)
ORDER BY hotels.hotel_name, vips.last_name;

-- 5. Which event is the most popular with government vips? (Custom Query)
SELECT events.event_name, COUNT(vips.member_id) AS number_gov_vips
FROM vips
JOIN events ON vips.event = events.event_id
WHERE vips.assoc_type = 'government';

-- 6. Who has been a vip the longest and who is the newest vip member? (Custom Query)
SELECT vips.last_name, vips.first_name, (2025 - vips.member_since) AS membership_years
FROM vips
WHERE (2025 - vips.member_since) = (SELECT MAX(2025 - member_since) FROM vips)
OR (2025 - vips.member_since) = (SELECT MIN(2025 - member_since) FROM vips)
ORDER BY (2025 - vips.member_since) DESC;

-- 7. How many VIPs are registered for each event? (Custom Query)
SELECT
CASE WHEN events.event_name IS NULL
THEN 'Not Selected'
ELSE events.event_name
END AS event,
COUNT(vips.last_name) AS number_attending
FROM vips
LEFT JOIN events ON vips.event = events.event_id
GROUP BY events.event_name
ORDER BY (CASE WHEN events.event_name IS NULL THEN 1 ELSE 0 END), event;