Climate Conference Analysis

  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')
 ;

--All VIPs and the names of the hotels they reserved, if any.
SELECT v.first_name || " " || v.last_name AS vip_name, h.hotel_name
FROM vips v
LEFT JOIN reservations r
ON v.member_id = r.member_id
LEFT JOIN hotels h
ON r.hotel = h.hotel_id;

--Names of all VIPs who have RSVP’d “yes” for the welcome event and the name of the hotel they’re staying in.
SELECT v.first_name || " " || v.last_name AS vip_name, h.hotel_name
FROM vips v
INNER JOIN reservations r
ON v.member_id = r.member_id
INNER JOIN hotels h
ON r.hotel = h.hotel_id
WHERE r.welcome_rsvp = 1;


--Each VIP's full name, their associated event name, and the hotel name if they made a reservation.
SELECT v.first_name || " " || v.last_name AS vip_name, e.event_name, h.hotel_name
FROM vips v
LEFT JOIN events e 
ON v.event = e.event_id
LEFT JOIN reservations r 
ON v.member_id = r.member_id
LEFT JOIN hotels h 
ON r.hotel = h.hotel_id;

--All pairs of VIPs where one provides feedback to the other, showing both names.
SELECT v1.first_name || " " || v1.last_name AS feedback_giver,
v2.first_name || " " || v2.last_name AS feedback_receiver
FROM vips v1
JOIN vips v2
ON v1.provides_feedback_to = v2.member_id;

--Names of VIPs who are staying at the same hotel as the VIP with the earliest “member_since” year.
SELECT v.first_name || " " || v.last_name AS vip_name
FROM vips v
LEFT JOIN reservations r
ON v.member_id = r.member_id
WHERE r.hotel = (SELECT r2.hotel
                 FROM vips v2
                 LEFT JOIN reservations r2
                 ON v2.member_id = r2.member_id
                 WHERE r2.hotel IS NOT NULL
                 ORDER BY v2.member_since ASC
                 LIMIT 1);

--All events that have at least one VIP associated with them.
SELECT event_name, COUNT(member_id) number_of_vips
FROM events e
LEFT JOIN vips v
ON e.event_id = v.event
GROUP BY event_name
HAVING COUNT(member_id) > 0;


--All associations and count how many VIPs belong to each.
SELECT association, COUNT(member_id)
FROM vips 
GROUP BY association;