SQL in MS Access 1. By Last Name, First Name order, what is the GPA for each stu

Question

SQL in MS Access

1. By Last Name, First Name order, what is the GPA for each student?

2. Which course appears to be the hardest based off of GPA?

StudentGrade OnlineCourse CourseID URL EnrollmentID CourseID StudentID Grade Course CourseID Title Credits DepartmentID Person PersonID LastName FirstName HireDate EnrollmentDate TraditionalCourse CourselD Location Days cTime Courselnstructor CourseID PersonID 0O Department DepartmentID Name Budget StartDate Administrator OfficeAssignment InstructorID Location Timestamp

Explanation / Answer

Run the script to create tables:=

CREATE TABLE Person
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
HireDate date(),
EnrollmentDate date()
);

Insert values in person table

INSERT INTO Person VALUES (101, 'Nagawade', 'Rekha', '2015-12-31', '2016-12-31);   

INSERT INTO Person VALUES (101, 'Jadhav', 'Tanvi', '2015-12-31', '2016-12-31);   

CREATE TABLE courses(CourseId NUMBER(5,0) NOT NULL,Title VARCHAR2(15) NOT NULL, Credits VARCHAR2(25),DepartmentId int FOREIGN KEY REFERENCES Department(DepartmentId));

INSERT INTO courses VALUES (101, 'DBMS I', 'a',1 );    

INSERT INTO courses VALUES (101, 'OOP I', 'S',2);

1. By Last Name, First Name order, what is the GPA for each student?

SELECT   LastName, FirstName,gpa
from Person p1

WHERE     gpa =
          (
          SELECT max(gpa)
          from Person p2
          WHERE p1.grade = p2.grade
          );

          FirstName LastName     GPA
          --------       ----------        ----------
           Rekha       Nagawade      3.45
           Tanvi              Jadhav        3.5
       

2,Which course appears to be the hardest based off of GPA?

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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