SQL : Join of three tables

Question

I am new to SQL and am learning joins of tables now.

I am stuck at joining three tables.

(I have given the rows I have inserted to the tables also for your reference.)

My tables are

--Table1

    create table sql_students(
    stu_studentid int not null primary key,
    stu_name varchar(100) not null,
    stu_regnnumber bigint unique not null
    )

--Rows inserted to Table1

insert into sql_students (stu_studentid,stu_name,stu_regnnumber) values (1,'John',194300)
insert into sql_students (stu_studentid,stu_name,stu_regnnumber) values (2,'Joy',959595)
insert into sql_students (stu_studentid,stu_name,stu_regnnumber) values (3,'Lucy',474848)

--Table2

create table sql_exam(
exa_examid bigint not null primary key,
exa_name varchar(100) not null,
exa_maxmark decimal(5,2) not null,
exa_minmarkreqdforpass decimal(5,2) not null,
exa_examscheduletime datetime not null
)

--Rows inserted into Table2

insert into sql_exam(exa_examid,exa_name,exa_maxmark,exa_minmarkreqdforpass,exa_examscheduletime) values (1,'Maths',100,40,'2012-10-10 10:00')
insert into sql_exam(exa_examid,exa_name,exa_maxmark,exa_minmarkreqdforpass,exa_examscheduletime) values (2,'English',75,35,'2012-10-11 10:00')

--Table3

create table sql_studentmarks(
stm_studentid int foreign key references sql_students(stu_studentid),
stm_examid bigint foreign key references sql_exam(exa_examid),
stm_mark decimal(5,2)
)

--Rows inserted into Table3
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (1,1,80)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (2,1,90)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (3,1,40)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (1,2,70)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (2,2,60)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (3,2,17)

I need guidelines to get

1. All students who passed all exams
2. All students who attended all exams
3. All students and their mark difference in Maths and English.

Thanks in advance.

Answer 1

For #1

SELECT s.stu_name 
FROM sql_studentmarks AS m 
JOIN sql_students AS s ON m.stm_studentid = s.student_id 
JOIN sql_exam AS x ON COUNT(m.stm_examid) = COUNT (x.stm_examid)
WHERE m.stm_mark >= x.exa_minmarkreqdforpass GROUP BY s.stu_studentid;

For #2 you can base on the exam count, perhaps creating a var in the select statement:

SELECT s.stu_name 
FROM sql_studentmarks AS m 
JOIN sql_students AS s ON m.stm_studentid = s.stu_studentid 
RIGHT OUTER JOIN sql_exam AS x ON COUNT(m.stm_examid) = COUNT (x.stm_examid)
AND m.stm_examid IS NOT NULL
GROUP BY s.stu_student_id;

FOR #3 use the examples from above, simple joins will do:

SELECT s.stu_name, x.exa_name, m.stm_mark 
FROM sql_studentmarks AS m 
JOIN sql_students AS s ON m.stm_studentid = s.stu_studentid 
JOIN sql_exam x ON  m.stm_examid = x.exa_examid AND s.stu_studentid = x.exa_studentid;

Answer 2

The code below shows you the first two queries. Your joins are OK.. Try these in SQL Fiddle and then see if you can work the third query out

Query #1: (You were very close, just needed the distinct keyword)

select  stm_studentid,stu_name  
from sql_exam 
join sql_studentmarks on exa_examid=stm_examid 
                         and stm_mark>exa_minmarkreqdforpass 
inner join sql_students on stu_studentid=stm_studentid
group by stm_studentid,stu_name
having count(*) = (select count(*) from sql_exam)

Query #2: -

• The group by lets you count the number of exams taken.
• The having lets you compare the count to number of exams available

select stu_name,count(*) as NumExamsTaken
from sql_studentmarks
join sql_students on stu_studentid=stm_studentid
group by stu_name
having count(*) = (select count(*) from sql_exam)

Query #3:
This should get you started

 select  stu_studentid,stu_name,
            MG.stm_mark as MathGrade,
            EG.Stm_mark as EnglishGrade
    from sql_students
    join sql_exam MATH on MATH.exa_name='MATHS'
    join sql_exam ENG on ENG.exa_name='ENGLISH'
    join sql_studentmarks MG on MG.stm_studentid=stu_studentid
                            and MG.stm_examid=MATH.exa_examId
    join sql_studentmarks EG on EG.stm_studentid=stu_studentid
                            and EG.stm_examid=ENG.exa_examId