--
Rationale: For queries where specialization is not specified, set 'general' as the default specialization and 'MS' as the level.
User: What courses do I need to take?
Target: SELECT summary(sheet_requirements), sheet_path, sheet_url FROM programs WHERE 'general' = specialization AND 'MS' = level;
--
Rationale: For queries related to a specific specialization track, match the specialization field with ENUM and the level to 'MS'.
User: What do I need to take for the AI track?
Target: SELECT answer(sheet_requirements, 'what do I need to take for the AI depth requirement?'), sheet_path, sheet_url FROM programs WHERE 'AI' = specialization AND 'MS' = level;
--
Rationale: For queries about requirements look in the programs table and match the specialization field with ENUM and the level to 'MS'.
User: How do I complete the significant implementation requirement?
Target: SELECT answer(sheet_requirements, 'how do I complete the significant implementation requirement?'), sheet_path, sheet_url FROM programs WHERE 'general' = specialization AND 'MS' = level;
--
Rationale: You can order by the average hours spent for finding courses with low workload.
User: I am a sophomore studying computer science. How can I complete the significant implementation requirement as quickly as possible?
Target: SELECT title, course_codes, description, average_hours_spent FROM courses WHERE significant_implementation_requirement = TRUE ORDER BY average_hours_spent;
--
Rationale: Meaning of "course number" is course code.
User: Show me a computer systems course numbered above 200.
Target: SELECT title, course_codes FROM courses WHERE EXISTS (SELECT 1 FROM unnest(courses.course_codes) as course_code(course_code) WHERE course_code LIKE 'CS %' AND substring(course_code FROM 'CS ([0-9]+)')::int > 200) AND answer(description, 'is this a computer systems course?') = 'Yes' LIMIT 1;
--
Rationale: Finding courses that meet sepecific requirements by looking at the defined ENUM.
User: What courses fulfill the computer organization and systems requirement again?
Target: SELECT title, course_codes, description FROM courses WHERE 'computer_organization_and_systems'::foundation = ANY(foundations_requirement);
--
Rationale: When the user asks for more courses, show the next 5 courses.
User: Show me some NLP courses.
Target: SELECT title, course_codes, description FROM courses WHERE answer(description, 'is this an NLP course?') = 'Yes';
Agent: Here are some courses focused on NLP: CS 124: From Languages to Information, CS 224U: Natural Language Understanding, CS 224N: Natural Language Processing with Deep Learning, CS 224C: NLP for Computational Social Science, and CS 324H: History of Natural Language Processing.
User: Please show me some more.
Target: SELECT title, course_codes, description FROM courses WHERE answer(description, 'is this an NLP course?') = 'Yes' OFFSET 5;
--
Rationale: When the user asks for courses offered in a specific quarter, match the season field with ENUM.
User: I need a list of computer organization and systems foundation courses offered this spring.
Target: SELECT courses.title, courses.course_codes, offerings.days, offerings.start_time, offerings.end_time, offerings.instructor_names FROM courses JOIN offerings ON courses.course_id = offerings.course_id WHERE 'computer_organization_and_systems' = ANY(courses.foundations_requirement) AND offerings.season = 'spring';
--
Rationale: Finding courses for breadth requirement and filtering based on the instructor.
User: Show courses for the learning and modeling requirement that are taught by Mike Genesereth this winter.
Target: SELECT courses.title, courses.course_codes, offerings.days, offerings.start_time, offerings.end_time, offerings.instructor_names FROM courses JOIN offerings ON courses.course_id = offerings.course_id WHERE 'learning_and_modeling' = ANY(breadth_requirement) AND offerings.season = 'spring' AND 'Michael Genesereth' = ANY(offerings.instructor_names);
--
Rationale: Checking courses filtered by the time the lecture starts.
User: What computer security courses start after 3pm?
Target: SELECT courses.title, courses.course_codes, offerings.days, offerings.start_time, offerings.end_time, offerings.season FROM courses JOIN offerings ON courses.course_id = offerings.course_id WHERE answer(description, ‘is this a computer security course?’) = ‘Yes’ AND offerings.start_time > '15:00';
--
Rationale: Use of rating and how to compare courses.
User: How do CS 227B, CS 151, and CS 148 compare in ratings?
Target: SELECT title, ROUND(AVG(average_rating::NUMERIC), 2) AS avg_rating FROM courses JOIN ratings ON courses.course_id = ratings.course_id  WHERE course_codes && ARRAY['CS 227B', 'CS 151', 'CS 148'] GROUP BY title ORDER BY avg_rating DESC;
--
Rationale: Use summary over reviews to summarize student opinions on a course.
User: What do students think about CS 148?
Target: SELECT summary(ratings.reviews) FROM ratings JOIN courses ON ratings.course_id = courses.course_id WHERE 'CS 148' = ANY(courses.course_codes);
--
Rationale: If the question cannot be answered directly by using any of the columns, then search the reviews column.
User: Does CS 224N have a final exam?
Target: SELECT answer(reviews, 'does this course have a final exam?') FROM courses WHERE 'CS 224N' = ANY(course_codes);
--
Rationale: User is asking for Specific Instructors
User: There are also some professors I would like to take courses from. Give me courses taught by Professor Monica Lam or Dan Jurafsky.
Target: SELECT DISTINCT title, course_codes, instructor_names FROM courses JOIN ratings ON courses.course_id = ratings.course_id WHERE ARRAY['Monica Lam', 'Dan Jurafsky'] && ratings.instructor_names;
