Question
I need assistance with these SQL Queries on MS SQL Server Management Studio. They all use the following database: use master; go if DB_ID('exam1') IS
I need assistance with these SQL Queries on MS SQL Server Management Studio. They all use the following database:
use master; go if DB_ID('exam1') IS NOT NULL drop database exam1; go create database exam1; go use exam1 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');
The questions are:
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
*/
---
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.
HINT 1: the output contains one row with five columns.
-----
Write one SELECT statement to solve the question given on top of Query 12.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.
HINT 1: the output contains one row with five columns.
---
Write one SELECT statement to solve the question given on top of Query 12.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.
HINT 1: the output contains one row with five columns.
---
Among students who meet the same conditions as given on top of Query 12.1, who has the second smallest StuID value? Write one SELECT statement to display this student's StuID and GPA.
HINT 1: In a set of integers {5, 807, -3, 42}, the smallest is -3 and the second smallest is 5. HINT 2: OFFSET (a hint only, it's not necessary to use it since there are other ways can be used) HINT 3: the output contains one row with two columns
---
Thank you! Any help would be great
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