--
-- Name: TYPE YOUR NAME HERE
--
-- Write your queries below each comment. Please use good style (one clause
-- per line, JOIN syntax, indentation) and make sure all queries end with a
-- semicolon. When necessary, limit the output to the first 200 results.
--
-- DO NOT MODIFY OR DELETE ANY OTHER LINES!
--

--------------------------------------------------------------------------------
-- Part 1: air Queries
\c air
--------------------------------------------------------------------------------

--
\echo QUERY #1
\echo
-- List all last names in the database that start with the letter M. Use the
-- lower() function to convert all last names to lowercase. Show how many times
-- each last name is used. Note: last names are in more than one table.
--
-- Schema: count, last_name
--  Order: count (descending), last_name


--
\echo QUERY #2
\echo
-- Find all the passengers who have flown on the same flight with passenger
-- 5784983 (Jairo School). Define having flown on the same flight as having
-- a boarding pass issued on the same flight, since we don't have the precise
-- data about who boarded.
--
-- Schema: flight_id, passenger_id, first_name, last_name
--  Order: flight_id, passenger_id


--
\echo QUERY #3
\echo
-- Show the passengers who have flown out of Dulles (IAD) but have never flown
-- out of Orlando (MCO).
--
-- Schema: passenger_id, first_name, last_name
--  Order: passenger_id


--
\echo QUERY #4
\echo
-- Find all the bookings which cost over $1985 that begin in a city in the UK
-- (country code 'GB'), and whose initial flight has not yet departed (in terms
-- of the database, not today's actual date).
--
-- Schema: booking_id, booking_name, price, city
--  Order: price (descending), booking_id


--
\echo QUERY #5
\echo
-- Find the passengers who have not been issued a boarding pass, but whose
-- booked itinerary includes a domestic flight from Dhaka (depart airport
-- DAC, arrive in Bangladesh BD).
--
-- Schema: first_name, last_name, iso_country
--  Order: passenger_id


--
\echo QUERY #6
\echo
-- For each airport, find the percentage of flights that have departed over 10
-- minutes late. Note that in order to avoid integer division, you must cast at
-- least one of the values to a float. For example: CAST(late_flights AS float)
--
-- Schema: departure_airport, percentage (calculated value)
--  Order: departure_airport


--------------------------------------------------------------------------------
-- Part 2: jmudb Queries
\c jmudb
--------------------------------------------------------------------------------

--
\echo QUERY #7
\echo
-- What undergraduate CS courses were offered in Fall 2023? Show the instructor
-- and enrollment for each section. Do not include independent study and senior
-- honors thesis.
--
-- Schema: subject, number, title, section, instructor, enrolled
--  Order: number, section


--
\echo QUERY #8
\echo
-- For each subject, how many sections with more than 15 students enrolled are
-- being taught in Spring 2024? Be careful not to count the same section twice!
--
-- Schema: subject, count
--  Order: count (descending), subject


--
\echo QUERY #9
\echo
-- List all courses from Spring 2020 to Fall 2021 that had more students
-- enrolled than 1.5 times the room capacity. Ignore classrooms that have a
-- capacity of zero (unlimited).
--
-- Schema: term, subject, number, suffix, section, room,
--         room_cap, enrolled, enrl_cap
--  Order: term, subject, number, suffix, section, room


--
\echo QUERY #10
\echo
-- For each instructor, count the total number of students they have taught
-- in CS courses over the past three academic years.
--
-- Schema: instructor, students (calculated value)
--  Order: students (descending), instructor
