-Person (Name, ID, Address, DOB - Instructor (InstructorID, Rank, Salary) - Stud

Question

-Person (Name, ID, Address, DOB

- Instructor (InstructorID, Rank, Salary)

- Student (StudentID, Classification, GPA, MentorID, CreditHours)

-Course (CourseCode, CourseName, PreReq)

-Offering (CourseCode, SectionNo, InstructorID)

- Enrollment (CourseCode, SectionNo, StudentID, Grade)

Execute an SQL query that selects names of seniors, names of their mentors, and GPA in descending order of GPA-values. Then write java code to go through the result set of the query and print the names of the students, name of their mentors, and their GPA, for top 5 (or more) seniors. Note that this list may contain less than 5 distinct GPA-values and more than 5 students. This is because some students may have the same GPA.

Explanation / Answer

SQL:

SELECT sn.Name,mn.Name,s.GPA

FROM Student s,Person sn,Person mn
WHERE s.StudentID=sn.ID
AND s.MentorID=mn.ID
ORDER BY s.GPA desc;

// Query1.java: Query an mSQL database using JDBC.

import java.sql.*;

/**
* A JDBC SELECT (JDBC query) example program.
*/
class Query1 {

public static void main (String[] args) {
try {
String url = "jdbc:msql://200.210.220.1:1114/Demo";
Connection conn = DriverManager.getConnection(url,"","");
Statement stmt = conn.createStatement();
ResultSet rs;

rs = stmt.executeQuery(" SELECT * FROM
           (SELECT sn.Name as StudentName,mn.Name as MentorName,s.GPA,RANK() OVER (PARTITION BY sn.ID order by s.GPA DESC) as RNK
                                       FROM Student s,Person sn,Person mn
                                   WHERE s.StudentID=sn.ID
                                       AND s.MentorID=mn.ID) WHERE RNK<=5");
while ( rs.next() ) {
String studentName = rs.getString("StudentName");
String mentorName = rs.getString("MentorName");
double gpa = rs.getDouble("GPA");
System.out.println(studentName+","+MentorName+","+gpa);
}
conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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