--
-- 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 500 results.
--
-- DO NOT MODIFY OR DELETE ANY OTHER LINES!
--

--------------------------------------------------------------------------------
-- DBLP Queries
\c dblp
--------------------------------------------------------------------------------

--
\echo QUERY #1
\echo
-- Find all publications since Jan 1, 2000 that have the words 'K-12 education'
-- anywhere in the title (i.e., the user typed that into a simple search box).
--
-- Schema: year, title, dblp_type, booktitle, journal, volume, issnum, pages
--  Order: dblp_mdate, dblp_key



--
\echo QUERY #2
\echo
-- Search for publications that have a title similar to "Principles of Database
-- Management". Set the similarity threshold to 0.5 before running your query.
-- For full credit, you must use the string "Principles of Database Management"
-- only once in your query.
--
-- Schema: dblp_key, year, title, similarity
--  Order: similarity DESC, dblp_key



--
\echo QUERY #3
\echo
-- List the coauthors of Jennifer Widom who have written more publications
-- than she has.
--
-- You must write the query in a generic way that would work for any author.
-- Hint: The only table you need is auth. Use subqueries to do the counting.
--
-- Schema: coauthor
--  Order: coauthor



--
\echo QUERY #4
\echo
-- Write a recursive query that lists athors related to Chris Mayfield (in the
-- sense of coauthorship) along with their collaborative distance: 1 = coauthor,
-- 2 = coauthor's coauthor, etc.
--
-- Hint #1: In the initial query (the non-recursive part), select all coauthors
-- of Chris Mayfield with a depth of 1 and a path of ARRAY[coauthor, Mayfield].
-- Hint #2: Since the results are limited to 500 rows, the max depth you will
-- need to compute is 3.
--
-- Schema: author, min_depth
--  Order: min_depth, author



--------------------------------------------------------------------------------
-- JMU Queries
\c jmudb
--------------------------------------------------------------------------------

--
\echo QUERY #5
\echo
-- List all courses that have had more students enrolled than 2.5 times the
-- room capacity. Ignore classrooms that have a capacity of zero (unlimited).
--
-- Schema: term int4, subject text, number int4, suffix text, section text,
--         room text, room_cap integer, enrolled integer, enrl_cap integer
--  Order: term, subject, number, suffix, section, room



--
\echo QUERY #6
\echo
-- For each instructor, count the total number of students they have taught in
-- CS courses over the past three academic years. Be careful not to count the
-- same section of students more than once.
--
-- Schema: instructor text, students bigint
--  Order: sum descending, instructor



--
\echo QUERY #7
\echo
-- List all sections in Spring 2022 with more that 100 students enrolled, and
-- rank them by course (i.e., the section with the most students is rank #1).
--
-- Schema: subject, number, nbr, enrolled, rank
--  Order: subject, number, nbr



--
\echo QUERY #8
\echo
-- Rank departments in Spring 2022 by their total enrollment, i.e., who has the
-- most students enrolled across all sections. (Hint: two nested subqueries)
--
-- Schema: subject, total, rank
--  Order: subject


