|I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows:
CREATE TABLE Students
Student_ID INTEGER PRIMARY KEY,
CourseCode INTEGER REFERENCES Courses(CourseCode)
CREATE TABLE Modules
Module_Code INTEGER PRIMARY KEY,
ConvenerID INTEGER REFERENCES Conveners(ConvenerID)
CREATE TABLE Enrollment
Module_Code INTEGER REFERENCES Modules(Module_Code),
Student_ID INTEGER REFERENCES Students(Student_ID),
PRIMARY KEY(Module_Code, Student_ID)
I want to show three columns with my query:
Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks
What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'.
For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks.
What I'm using is as follows:
AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks,
AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_second_year_marks
AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks
Students LEFT JOIN
Enrollment ON Students.Student_ID=Enrollment.Student_ID
General News Suggestion Question Bug Answer Joke Praise Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
Copyright © CodeProject
All Rights Reserved.