Click here to Skip to main content
14,175,754 members
   

Database

 
AnswerRe: LinkedServer version Pin
Mycroft Holmes12-Apr-19 12:43
memberMycroft Holmes12-Apr-19 12:43 
QuestionSQL Interview Pin
Member 1417944312-Apr-19 3:38
memberMember 1417944312-Apr-19 3:38 
AnswerRe: SQL Interview Pin
Richard MacCutchan12-Apr-19 6:34
protectorRichard MacCutchan12-Apr-19 6:34 
GeneralRe: SQL Interview Pin
Member 1417944312-Apr-19 22:36
memberMember 1417944312-Apr-19 22:36 
GeneralRe: SQL Interview Pin
Richard MacCutchan12-Apr-19 23:20
protectorRichard MacCutchan12-Apr-19 23:20 
GeneralRe: SQL Interview Pin
Eddy Vluggen16-Apr-19 2:54
mveEddy Vluggen16-Apr-19 2:54 
AnswerRe: SQL Interview Pin
#realJSOP16-Apr-19 2:00
mve#realJSOP16-Apr-19 2:00 
QuestionQuerying from multiple tables at the same time - related to correlated subqueries Pin
Member 142180367-Apr-19 5:12
memberMember 142180367-Apr-19 5:12 
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,
Enrollment_Year DATE,
Course_Current_Status VARCHAR(18),
First_Name TEXT,
Last_Name TEXT,
Gender TEXT,
Date_Of_Birth DATE,
Email TEXT,
CourseCode INTEGER REFERENCES Courses(CourseCode)
);

CREATE TABLE Modules
(
Module_Code INTEGER PRIMARY KEY,
Module_Name TEXT,
Module_Credits INTEGER,
Module_Level INTEGER,
ConvenerID INTEGER REFERENCES Conveners(ConvenerID)
);

CREATE TABLE Enrollment
(
Marks_Obtained INTEGER,
Module_Code INTEGER REFERENCES Modules(Module_Code),
Student_ID INTEGER REFERENCES Students(Student_ID),
Program_Year_When_Enrolled TEXT,
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:

SELECT
Students.Student_ID,
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
FROM
Students LEFT JOIN
Enrollment ON Students.Student_ID=Enrollment.Student_ID
WHERE
Students.Course_Current_Year='Graduated-2017'
GROUP BY
Students.Student_ID
AnswerRe: Querying from multiple tables at the same time - related to correlated subqueries Pin
Mycroft Holmes7-Apr-19 12:08
memberMycroft Holmes7-Apr-19 12:08 
AnswerRe: Querying from multiple tables at the same time - related to correlated subqueries Pin
Eddy Vluggen8-Apr-19 0:44
mveEddy Vluggen8-Apr-19 0:44 
AnswerRe: Querying from multiple tables at the same time - related to correlated subqueries Pin
CHill6011-Apr-19 6:48
protectorCHill6011-Apr-19 6:48 
QuestionHELP WITH SQL QUERY Pin
BrunoPigeon4-Apr-19 17:37
memberBrunoPigeon4-Apr-19 17:37 
AnswerRe: HELP WITH SQL QUERY Pin
Mycroft Holmes4-Apr-19 20:38
memberMycroft Holmes4-Apr-19 20:38 
GeneralRe: HELP WITH SQL QUERY Pin
BrunoPigeon4-Apr-19 20:56
memberBrunoPigeon4-Apr-19 20:56 
GeneralRe: HELP WITH SQL QUERY Pin
GuyThiebaut4-Apr-19 21:06
professionalGuyThiebaut4-Apr-19 21:06 
GeneralRe: HELP WITH SQL QUERY Pin
BrunoPigeon4-Apr-19 21:10
memberBrunoPigeon4-Apr-19 21:10 
GeneralRe: HELP WITH SQL QUERY Pin
GuyThiebaut4-Apr-19 21:20
professionalGuyThiebaut4-Apr-19 21:20 
GeneralRe: HELP WITH SQL QUERY Pin
GuyThiebaut4-Apr-19 21:43
professionalGuyThiebaut4-Apr-19 21:43 
AnswerRe: HELP WITH SQL QUERY Pin
GuyThiebaut4-Apr-19 20:56
professionalGuyThiebaut4-Apr-19 20:56 
GeneralRe: HELP WITH SQL QUERY Pin
BrunoPigeon4-Apr-19 20:58
memberBrunoPigeon4-Apr-19 20:58 
GeneralRe: HELP WITH SQL QUERY Pin
Mycroft Holmes4-Apr-19 21:16
memberMycroft Holmes4-Apr-19 21:16 
GeneralRe: HELP WITH SQL QUERY Pin
GuyThiebaut4-Apr-19 21:24
professionalGuyThiebaut4-Apr-19 21:24 
GeneralRe: HELP WITH SQL QUERY Pin
BrunoPigeon5-Apr-19 0:13
memberBrunoPigeon5-Apr-19 0:13 
GeneralRe: HELP WITH SQL QUERY Pin
GuyThiebaut4-Apr-19 21:17
professionalGuyThiebaut4-Apr-19 21:17 
GeneralRe: HELP WITH SQL QUERY Pin
BrunoPigeon5-Apr-19 0:11
memberBrunoPigeon5-Apr-19 0:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190525.1 | Last Updated 24 May 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid