Updated user story queries

1.1

SELECT TAs.Name, TAs.OfficeHours

FROM TAs

JOIN Courses ON Courses.TAID = TAs.TAID

WHERE Courses.ProfessorID = 1;

1.2

SELECT Schedules.Date, Schedules.TimeSlot, Courses.CourseName, TAs.Name AS TAName

FROM Schedules

JOIN Courses ON Schedules.CourseID = Courses.CourseID

LEFT JOIN TAs ON Courses.TAID = TAs.TAID

WHERE Schedules.ProfessorID = 1 AND Schedules.Date BETWEEN '04/2/2024' AND '04/9/2024';

1.3

SELECT Professors.Name AS ProfessorName, Professors.OfficeHours AS ProfessorOfficeHours, TAs.Name AS TAName, TAs.OfficeHours AS TAOfficeHours

FROM Professors

LEFT JOIN Courses ON Professors.ProfessorID = Courses.ProfessorID

LEFT JOIN TAs ON Courses.TAID = TAs.TAID

WHERE Professors.ProfessorID = 1;

1.4

INSERT INTO Communications (SenderID, ReceiverID, Timestamp, Content)

VALUES (10, 7, CURRENT_TIMESTAMP, 'Can I schedule an appointment during your office hours?');

2.1

For seeing all ungraded submissions:

SELECT *

FROM Submissions s

JOIN Assignments a ON s.AssignmentID = a.AssignmentID 

WHERE s.Grade IS NULL; 

For putting in a grades for an ungraded submissions:

UPDATE Submissions SET GRADE = %s 

WHERE SubmissionID = %s AND AssignnmentID = %s

2.2 

SELECT *

FROM Schedule s JOIN DayTimeWorked dtw ON dtw.ScheduleID = s.ScheduleID

JOIN Employees e ON e.EmployeeID = s.EmployeeID;

2.3

For getting all submissions:

SELECT *

FROM Submissions;

For changing grade on any submission:

UPDATE Submissions SET Grade=%s, GradedBy=%s, GradedOn=%s WHERE SubmissionID=%s;

2.4

SELECT dp.PostContent AS Content, DiscussionPostAnswers AS Reply

FROM DiscussionPosts dp

JOIN DiscussionPostAnswers da ON dp.PostID=da.PostID;

For seeing all discussion posts:

SELECT *

FROM DiscussionPosts;

For seeing post replies to discussion posts:

SELECT *

FROM DiscussionPostAnswers;

Finally, to actually post the reply: 

INSERT INTO DiscussionPostAnswers (PostID, EmployeeID, DiscussionPostAnswer, TimePosted)

VALUES (%s, %s, %s, %s);

3.1 

SELECT dtw.Day AND dtw.Timeslot AS OfficeHours, e.EmployeeFirstName AND e.EmployeeLastName as Name

FROM DayTimeWorked dtw 

JOIN Schedule s ON dtw.ScheduleID = s.ScheduleID 

JOIN Employees e ON s.EmployeeID = e.EmployeeID

ORDER BY OfficeHours ASC;

3.2

SELECT * 

FROM FeedbackSurveys 

ORDER BY CRN; 

3.3

SELECT dp.PostTitle AND dp.PostContent as posts, e.FirstName AND e.LastName as sender, dpa.DiscussionPostAnswer as answer 

FROM DiscussionPost dp

JOIN DiscussionPostAnswers dpa ON dp.PostID = dpa.PostID 

JOIN Employees e ON dpa.EmployeeID = e.EmployeeID

WHERE answer IS NOT NULL;

3.4

SELECT * 

FROM Assignments a 

JOIN Submissions s ON a.AssignmentID = s.AssignmentID

ORDER BY a.CourseCRN; 

4.1 

SELECT * 

FROM Enrollments

ORDER BY CRN ASC;

4.2

SELECT Feedback, CRN

FROM FeedbackSurveys

ORDER BY CRN ASC;

4.3

SELECT CRN, AVG(FinalGrade) as Average_Grade

FROM Enrollments

GROUP BY CRN;

4.4

SELECT se.Email as EmailAddress, c.Department as Department

FROM StudentEmails se

JOIN Enrollments e ON se.StudentID = e.StudentID

JOIN CourseSelections cs ON e.CRN = cs.CRN

JOIN Courses c ON cs.CourseID = c.CourseID

ORDER BY Department ASC;