3 Replies - 481 Views - Last Post: 07 April 2019 - 10:08 AM Rate Topic: -----

#1 hadi110   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 07-April 19

Queries from multiple tables - using subqueries

Posted 07 April 2019 - 08:23 AM

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)
);
:code:
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_third_year_marks
      SUM (avg_third_year_marks*2/3 +avg_second_year_marks*1/3)
FROM 
      Students LEFT JOIN
      Enrollment ON Students.Student_ID=Enrollment.Student_ID
WHERE 
      Students.Course_Current_Year='Graduated-2017'
GROUP BY 
      Students.Student_ID
:code:

This post has been edited by modi123_1: 07 April 2019 - 09:42 AM
Reason for edit:: In the future please use the [code] tag button in the editor


Is This A Good Question/Topic? 0
  • +

Replies To: Queries from multiple tables - using subqueries

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15318
  • View blog
  • Posts: 61,430
  • Joined: 12-June 08

Re: Queries from multiple tables - using subqueries

Posted 07 April 2019 - 08:51 AM

You did not ask a question or explain how those queries are notbeork8ng correctly.
Was This Post Helpful? 0
  • +
  • -

#3 hadi110   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 07-April 19

Re: Queries from multiple tables - using subqueries

Posted 07 April 2019 - 08:56 AM

I wanted to ask how should I be structuring my query to extract the required data i.e. columns having student_id, average second year marks, average third year marks and overall marks which would be 1/3 of the second year marks and 2/3 of the third year marks having the WHERE restriction that Students.Course_Current_Year = 'Graduated-2017'
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15318
  • View blog
  • Posts: 61,430
  • Joined: 12-June 08

Re: Queries from multiple tables - using subqueries

Posted 07 April 2019 - 10:08 AM

Subqueries still need to follow the structure of a query. A SELECT and FROM are needed.

Also - things like 'current year' or 'year when enrolled' should be straight numbers. Not text like "Graduated-2017".

It is also best practice to lead with your key columns for indexing and readability.

As for your function start with getting what you need averaged first and then think about how to wedge that into your query.

AVG is an aggregate function. It needs a 'GROUP BY'

Example input:

Module_Code Student_ID  Marks_Obtained Program_Year_When_Enrolled
----------- ----------- -------------- --------------------------
1           1           10             2017
2           1           9              2017
3           1           1              2017
4           1           5              2017
5           1           5              2018
1           2           5              2017



Think about what you want out and that typically leads to the grouping. You need a student id, a year, and the average of 'marks'.

So something like this:

Student_ID  Program_Year_When_Enrolled avg_per_year
----------- -------------------------- ------------
1           2017                       6
2           2017                       5
1           2018                       5

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1