/* example queries for student-course database */

/* students who have earned an 'A' */
SELECT DISTINCT name, studentNumber
FROM gradeReport, student
WHERE gradeReport.studentNumber = student.studentNumber
AND grade='A';

/* students who have earned a non-'A' */
SELECT DISTINCT name, studentNumber
FROM (gradeReport NATURAL JOIN student)
WHERE NOT grade='A';


/* straight 'A' students */
SELECT DISTINCT name, studentNumber
FROM (gradeReport NATURAL JOIN student)
WHERE studentNumber NOT IN (
	SELECT studentNumber 
	FROM gradeReport 
	WHERE NOT grade='A');

/* students who got an 'A' in Sheaf Theory */
SELECT DISTINCT name, student.studentNumber
FROM student, gradeReport, section, course
WHERE  student.studentNumber = gradeReport.studentNumber
AND gradeReport.sectionID = section.sectionID
AND section.courseNumber = course.courseNumber
AND grade='A' AND courseName='Sheaf Theory';

SELECT DISTINCT name, student.studentNumber
FROM student NATURAL JOIN gradeReport NATURAL JOIN section NATURAL JOIN course
WHERE grade='A' AND courseName='Sheaf Theory';


/* INCOMPLETE */
/* students who have graduated, ie who have 1000 hours in courses they
	passed (what if they passed the same course several times?) */
SELECT name, student.studentNumber, sum(creditHours)
FROM student NATURAL JOIN gradeReport NATURAL JOIN section NATURAL JOIN course
WHERE NOT grade='N'
GROUP BY student.name, student.studentNumber;


/* course with the highest failure rate */


/* courses with deepest prerequisite chain */


/* sections with no failures */

/* students eligible to take Sheaf Theory, but have not yet passed it */