create table Student (StuID char(3) primary key, GPA decimal(3, 2)); insert into
ID: 3725457 • Letter: C
Question
create table Student
(StuID char(3) primary key,
GPA decimal(3, 2));
insert into Student
values ('s1', 3.66), ('s2', 2.87), ('s3', 2.91), ('s4', 4), ('s5', 3.5);
create table ProjAssignment
(Pno char(3),
StuID char(3),
constraint pk_PA primary key (Pno, StuID),
constraint fk_1 foreign key (StuID) references Student(StuID));
insert into ProjAssignment
values ('p1', 's5'), ('p1', 's2'), ('p1', 's1'), ('p2', 's2'),
('p3', 's1'), ('p4', 's1'), ('p5', 's2'), ('p5', 's5');
Find students who either are never assigned to a project or have a GPA of 3.5 or lower.
The output must include five columns in the following order:
1. TotalStudents: the total number of students who meet the above conditions
2. AverageGPA: the average of the satisfied students
3. HighestGPA: the highest GPA of the satisfied students
4. LowestGPA: the lowest GPA of the satisfied students
5. GPA_difference: the difference of the highest and lowest GPA of the satisfied students
Query 1 (
Write one SELECT statement to solve the above question. This statement must use a table join
with no subqueries and no set operators. Except the value of 3.5, no other hard coded data
values are allowed in the statement.
Query 2
Write one SELECT statement to solve the question given on top of Query 1. This statement
must must use a subquery with no joins and no set operators. Except the value of 3.5, no other
hard coded data values are allowed in the statement.
Query 3
Write one SELECT statement to solve the question given on top of Query 1. This statement
must use a set operator and may use table joins or subqueries or both. Except the value of
3.5, no other hard coded data values are allowed in the statement.
Explanation / Answer
Query 1
Select count(StuID) as TotalStudents ,avg(GPA) as AverageGPA,max(GPA) as HighestGPA,min(GPA) as LowestGPA,Max(GPA)-min(GPA) as GPA_Difference from Student , ProjAssignment where Student.StuID <> ProjAssignment .StuID and GPA <=3.5;
Query 2
Select count(StuID) as TotalStudents ,avg(GPA) as AverageGPA,max(GPA) as HighestGPA,min(GPA) as LowestGPA,Max(GPA)-min(GPA) as GPA_Difference from Student where Student.StuID NOT IN (Select StuID from ProjAssignment ) and GPA <=3.5;
Query 3
Select count(StuID) as TotalStudents ,avg(GPA) as AverageGPA,max(GPA) as HighestGPA,min(GPA) as LowestGPA,Max(GPA)-min(GPA) as GPA_Difference from Student , ProjAssignment where Student.StuID IN(Select StuID from Student MINUS Select StuID from ProjAssignment ) and GPA <=3.5;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.