Creating a table from data in two or more tables

Question

User-226101726 posted

Im creating a gradebook to print reports for my school and im unable to complete two tasks using SQL.

Task one

I have a student table, Class table, ClassSubjects table ( This allows me to enter the subjects assigned to that class),

a classStudent table( allows me to enter the students in a specific class), and a subjects table. I want to be able to create, add and update 

in anew table, all the students from a specific class and all the subjects the do based on the subjects assigned to that class.

The system should be anle to do this automatically without me entering them one by one seeing all the data is there already

so that later I can add the grades for an exam for each subject for each student based on the class they r in.

The second is to display the data like a gradebok and be able to add the grades in the gradebook and all the table are updated.

Any Suggestions anyone please HELP 

Answer 1

User1553110397 posted

Please try this

select c.Classname,std.[Student Name],subjectname,0 as Mark ,'' asGrade into gradebok

from

dbo.Class c

inner

join

ClassStudent cs

on

c

.ClassID =cs.ClassID

inner

join

dbo

.Student std

on

std

.StudentID = cs.StudentID

inner

join

dbo

.ClassSubject csub

on

csub

.ClassID = cs.ClassID

inner

join

dbo

.[Subject] sub

on

csub

.SubjectID = sub.SubjectID

--where className='2 Group'

order

by std.[Student Name]

select

c.Classname,std.[Student Name],subjectname,0 as Mark ,'' as Grade

into

gradebok

from

dbo

.Class c

inner

join

ClassStudent cs

on

c

.ClassID =cs.ClassID

inner

join

dbo

.Student std

on

std

.StudentID = cs.StudentID

inner

join

dbo

.ClassSubject csub

on

csub

.ClassID = cs.ClassID

inner

join

dbo

.[Subject] sub

on

csub

.SubjectID = sub.SubjectID

--where className='2 Group'

order

by std.[Student Name]

You will get the required output on querying gradebok table.