Question

3. Show the detailed results achieved by a specific student, say sid= ‘S0002’.

A. For each course taken by the given student, show the course ID, title, name, weighting and mark achieved for each assessment.

B. Show the given student’s final result on each of the courses the student has taken.

An Entity-Relationship model of the database is shown below: Assessment Course 1..*aid {PK) cid PK) title cid FK) name weighting 1..1 records Student Grade sid PK) 1..1 1.sid PK, FK forename surname gender achievesaid (PK, FK) mark

Answer 1

Answer is as follows :

For given scenario of questions we can use JOINS but it leads to complexity so we use simple as follows :

A)

SELECT C.CID, C.title, A.name, A.weighting, G.mark

FROM

STUDENT S , GRADE G , ASSESSMENT A , COURSE C

WHERE

C.cid = A.cid AND

A.aid = G.aid AND

G.sid = S.sid AND

S.sid = 'S0002' ;

B) The final result is calculated by adding all the marks of course id's.

So we can do as follows :

SELECT C.CID, C.title, A.name, A.weighting, SUM(G.mark) AS final_marks

FROM

STUDENT S , GRADE G , ASSESSMENT A , COURSE C

WHERE

C.cid = A.cid AND

A.aid = G.aid AND

G.sid = S.sid AND

S.sid = 'S0002' ;

if there is any query please ask in comments...