Q1. Count the number of students in each course at the University. Print the course name, as well as the number of students.
select course.name,count(*)
from course
inner join student on student.course = course.id
group by course.id ;
4 ROWS
Q2. Is there any subject failed by more than one student? List the subject code as well as the number of failures.
select t0.code,t0.num from (
select code ,count(*) as ‘num’
from studenttakessubject
where result < 50
group by code
) as t0 where t0.num >1;
1 ROWS
Q3. For the students who have completed at least one subject at undergraduate level, how many points does each student need to complete their degree?
select t1.id,t1.sumscore,t2.firstname,t2.lastname,( t2.creditpoints-t1.sumscore) as ‘scoredistance’ from(
select student.id,count(*)*12.5 as ‘sumscore’ from student
inner join course on student.course = course.id
inner join studenttakessubject on studenttakessubject.student = student.id where course.level =’U’ and studenttakessubject.result is not null
group by student.id
) as t1 ,
(
select student.id,student.firstname,student.lastname,course.level,course.creditpoints,studenttakessubject.result from student
inner join course on student.course = course.id
inner join studenttakessubject on studenttakessubject.student = student.id where course.level =’U’ and studenttakessubject.result is not null
) as t2
where t1.sumscore > 0 and t1.id = t2.id group by t1.id;
8 ROWS
Q4. List the student number, lastname, course and GPA of students who have completed more than 4 subjects at undergraduate level?
select *
from(
select studenttakessubject.student,student.firstname, student.lastname, sum(subject.creditpoints * studenttakessubject.result)/sum(subject.creditpoints) as ‘GPA’
from studenttakessubject
inner join subject on
subject.code = studenttakessubject.code
and subject.area = studenttakessubject.area
and subject.yearlevel = studenttakessubject.yearlevel
inner join student on student.id = studenttakessubject.student group by studenttakessubject.student
) as t1 where t1.student in (
select t2.student from(
select t1.student,count(*) as ‘n’ from(
select student
from studenttakessubject
where yearlevel >=1 and yearlevel <= 3
) as t1 group by student
) as t2 where t2.n>4
);
5 ROWS
Q5. Which lecturer awarded the highest mark and what subject(s) was it (print the lecturer’s full name, the mark and the entire subject code e.g. “INFO20003”)?
select lecturer.firstname,lecturer.lastname,MAX(studenttakessubject.result) as ‘MARK’,CONCAT(subject.area,subject.code) as ‘entire subject code’
from studenttakessubject
inner join subject on subject.code = studenttakessubject.code
and subject.area = studenttakessubject.area
and subject.yearlevel = studenttakessubject.yearlevel
inner join lecturer on lecturer.id = subject.lecturer;
1 ROWS
Q6.For each student who has completed COMP10001 print their name, result and their academic grade (H1,H2A etc).
select student.firstname,student.lastname,T0.result,
case when T0.result >=80 AND T0.result <=100 THEN 'H1'
when T0.result >=75 AND T0.result <=79 THEN 'H2A'
when T0.result >=70 AND T0.result <=74 THEN 'H2B'
when T0.result >=65 AND T0.result <=69 THEN 'H3'
when T0.result >=50 AND T0.result <=64 THEN 'P'
when T0.result < 50 THEN 'N'
end academic_grade
from(
select student,result
from studenttakessubject
where area = 'COMP'
and yearlevel = 1
and code = 0001
group by student
) as T0, student
where T0.student = student.id;
5 ROWS
Q7.Find the names of lecturers who teach at both undergraduate and postgraduate level.
select t1.firstname,t1.lastname
from
(
select Lecturer.id,Lecturer.firstname,Lecturer.lastname, subject.area, subject.code
from subject
inner join Lecturer on Subject.lecturer = Lecturer.id
where subject.yearlevel =9
) as t1,
(
select Lecturer.id,Lecturer.firstname,Lecturer.lastname, subject.area, subject.code
from subject
inner join Lecturer on Subject.lecturer = Lecturer.id
where subject.yearlevel <=3 and subject.yearlevel >=1
) as t2
where t1.id = t2.id
group by t1.firstname,t1.lastname;
2 ROWS
Q8. List the lecturers who teach across all study areas.
select t2.firstname,t2.lastname from (
select count(*) as ‘nn’,t1.id,t1.firstname,t1.lastname from(
select lecturer.id,lecturer.firstname,lecturer.lastname,subject.area,count(*)
from subject
inner join lecturer on subject.lecturer = lecturer.id
group by lecturer.id,subject.area
) as t1
group by t1.id
)as t2 where t2.nn >= 3;
1 ROWS
Q9. Have any students from Gilberton suburb enrolled into Bachelor of Science course repeated a subject at undergraduate level?
select t1.student,student.firstname,student.lastname
from(
select student, area,code,year,count(*) as ‘num’
from studenttakessubject
where yearlevel >=1 and yearlevel <=3 and student in
(
select student.id from student
inner join suburb on student.postcode = suburb.postcode
and suburb.name = 'GILBERTON'
inner join course on course.id = student.course
and course.name = 'Bachelor of Science'
)
group by area,code,student
) as t1
inner join student on student.id = student
where t1.num >=2;
0 ROWS
Q10. The Dean has asked you to design a table that will record the student evaluations for each lecturer for each subject he has taught in each academic semester. You are to write the DDL to create the table including all suitable attributes and write the references to the Foreign Keys.
drop table if exists `Evaluations`;
create table `Evaluations`(
`student` mediumint(8) unsigned NOT NULL,
`area` char(4) NOT NULL,
`yearlevel` tinyint(3) unsigned NOT NULL,
`code` char(4) NOT NULL,
`year` year(4) NOT NULL,
`sem` enum(‘1′,’2’) NOT NULL,
`lecturer` mediumint(8) unsigned DEFAULT NULL,
`evaluation` varchar(225) NOT NULL,
PRIMARY KEY (`student`,`area`,`yearlevel`,`code`,`year`,`sem`,`lecturer`),
CONSTRAINT `fk_Evalutions_Lecturer` FOREIGN KEY (`lecturer`) REFERENCES `Lecturer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_Evalutions_Student` FOREIGN KEY (`student`) REFERENCES `Student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_Evalutions_Subject` FOREIGN KEY (`area`, `yearlevel`, `code`) REFERENCES `Subject` (`area`, `yearlevel`, `code`) ON DELETE NO ACTION ON UPDATE NO ACTION
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
0 ROWS