need help with this two quieries in sql. students(sid,name,age,gpa) // sid is th

Question

need help with this two quieries in sql.

students(sid,name,age,gpa) // sid is the primary key

courses(cid,deptid, description) // cid,deptid is the primary key

professors(ssn,name,address,phone,deptid) // ssn is the primary key

enrollment(sid,cid,section,grade) cid makes reference to the courses table. // sid, cid, and section is the primary key

teaches(cid,section,ssn). cid, section makes reference to the enrollment table // cid, section is the primary key

Write a query that produces the following table:

Deptid

Sps

%A

%B

%C

%D

%F

cs

math

Where SPS is the average number of students in each section and column %A has the percentage of students that got an A, and so on, over all the courses offered by each department. Assume a small section have less than 30 students, medium sections have at least 30 but less than 80, and large sections have at least 80 students.

Deptid

Sps

%A

%B

%C

%D

%F

cs

math

Explanation / Answer

As here the SPS calculation is Average number of students in each section. I think the metrics is wrong based on available information from schema. How we can get the average on count there is no Sum on any metics.

Below Query can be used to calculate the remaining table colimn other than SPS.

Query:

DECLARE @TotalStudent INT
Select @TotalStudent = Select count(sid) From students
Select
DeptId As Deptid,
(Sum(ACount)/@TotalStudent)*100 AS A%,
(Sum(BCount)/@TotalStudent)*100 AS B%,
(Sum(CCount)/@TotalStudent)*100 AS C%,
(Sum(DCount)/@TotalStudent)*100 AS D%,
(Sum(ECount)/@TotalStudent)*100 AS E%,
(Sum(FCount)/@TotalStudent)*100 AS F%
From
Select distinct
enroll.sid AS StudentId,
course.did AS DeptId,
enroll.section AS Section,
CASE WHEN(enroll.grade = 'A') THEN 1 ELSE 0 END AS ACount,
CASE WHEN(enroll.grade = 'B') THEN 1 ELSE 0 END AS BCount,
CASE WHEN(enroll.grade = 'C') THEN 1 ELSE 0 END AS CCount,
CASE WHEN(enroll.grade = 'D') THEN 1 ELSE 0 END AS DCount,
CASE WHEN(enroll.grade = 'E') THEN 1 ELSE 0 END AS ECount,
From enrollment enroll
inner join courses course ON course.cid = enroll.cid

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.