Getting data from database from different tables

Question

I'm creating this mobile portal/application using (ASP + WML) were I need to generate a list of courses taken by a student in certain semester in a certain year.

I have the following tables:

I have from a previous page the following parameters:

string StudentId = Request.Params["StudentId"];
string Year = Request.Params["Year"];
string Semester = Request.Params["Semester"];

• the values of the (Year) is either "2011" or "2012".
• the values of (Semester) is either "First" or "Second" or "Summer".
• the values of (StudentId) is a Number extracted before from id in Students table.

As a sample of the data inside the tables.

I'm stuck here

string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Uni.MDF;" +
            "Integrated Security=True;User Instance=True";
           string queryString =" ??????????????????????????? " ;

I want to get a page to show the courses that the user is registered in (in a given semester). The information will be later passed in a URL to new page where the info will be shown.

Something like:

First Semester  2010
Student : Arin Rizk

Course Name     No. of Credit      Mark
    AAA                3            65
    BBB                3            23
    CCC                3            65
    DDD                3            58
    EEE                3            70

Your GPA for this semster is 3.12

Edit

how I will print the the info in the page later ?

How can I do it? I tried to create a view but I don't know why its not working.

I can't change the tables or the DB structure.

Thank you in advance.

Answer 1

select st.firstname + ' ' + st.lastname,se.year, c.coursename,c.credits,ri.mark 
from students st 
inner join RegisteredIn ri on ri.studentid=st.id
inner join semester se on se.id=ri.semesterid
inner join Courses c on c.id=se.courseid

this query will give you the name, year, coursename, credits and marks.

You need to write another query for gpa calculation or you could calculate on your web server with the data obtained above.