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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started