Hello, I am working on a java program for sql query statements but I am stuck on one of the problems and can't seem to get it right, I have some of it wrote but I am not sure if I am on the right path. I will paste the tables there are in the database and what columns those tables hold. Also I will paste the starting code of it. Thank you!

advisor(s_ID, i_ID)

classroom(building, room_numbers_capacity)

course(`course_id`, `title`, `dept_name`, `credits`)

department(`dept_name`, `building`, `budget`)

instructor(`ID`, `name`, `dept_name`, `salary`)

prereq(`course_id`, `prereq_id`)

section(`course_id`, `sec_id`, `semester`, `year`, `building`, `room_number`, `time_slot_id`)

student(`ID`, `name`, `dept_name`, `tot_cred`)

takes(`ID`, `course_id`, `sec_id`, `semester`, `year`, `grade`)

teaches(`ID`, `course_id`, `sec_id`, `semester`, `year`)

time_slot(`time_slot_id`, `day`, `start_hr`, `start_min`, `end_hr`, `end_min`)

Here is the code I have that isn't complete yet

public class MyQuery { private Connection conn = null; private Statement statement = null; private ResultSet resultSet = null; public MyQuery(Connection c)throws SQLException { conn = c; // Statements allow to issue SQL queries to the database statement = conn.createStatement(); } 

 public void findFall2009Students() throws SQLException { String query = "select distinct name from student natural join takes where semester = \'Fall\' and year = 2009;"; resultSet = statement.executeQuery(query); } public void printFall2009Students() throws IOException, SQLException { System.out.println("******** Query 0 ********"); System.out.println("name"); while (resultSet.next()) { // It is possible to get the columns via name // also possible to get the columns via the column number which starts at 1 String name = resultSet.getString(1); System.out.println(name); } }

 public void findGPAInfo() throws SQLException { String query = "SELECT " + " grade, " + " CASE grade when 'A' then grade = 4.0 " + " when 'A-' then grade = 3.67 " + " when 'B+' then grade = 3.33 " + " when 'B' then grade = 3.0 " + " when 'B-' then grade = 2.67 " + " when 'C+' then grade = 2.33 " + " when 'C' then grade = 2.0 " + " when 'C-' then grade = 1.67 " + " when 'D+' then grade = 1.33 " + " when 'D' then grade = 1.0 " + " when 'D-' then grade = 0.67 " + " when 'F' then grade = 0.0 " + " ELSE 0 END AS grade FROM takes"; resultSet = statement.executeQuery(query); } public void printGPAInfo() throws IOException, SQLException { System.out.println("******** Query 1 ********"); while (resultSet.next()) { String name = resultSet.getString(1); System.out.println(name); } } public void findMorningCourses() throws SQLException { } public void printMorningCourses() throws IOException, SQLException { System.out.println("******** Query 2 ********"); } 

}

Query 1: Calculate GPA for each student First you need to turn letter grade into numeric grade: A: 4.0; A-: 3.67; B+:3.33; B: 3; B-:2.67, C+: 2.33; C: 2; C-: 1.67; D+: 1.33; D: 1; D-: 0.67; F: 0 The GPA is calculated with the following formula (Don't include null grade in the calculation): GPA - sum(numerical grade credits) /sum(credits) Here is the correct query result for your reference: id name GPA 00128 Zhang 3.858571 12345 Shankar 3.428571 19991 Brandt 3.000000 23121 Chavez 2.330000 44553 Peltier 2.670000 45678 Levy 2.029091 54321 Williams 3.500000 55739 Sanchez 3.670000 76543 Brown 4.000000 76653 Aoi 2.000000 98765 Bourikas 2.240000 98988 Tanaka 4.000000 Query 2: Find all the course sections offered in the morning. Ignore those sections having zero enrollments. Include all the course sections which have start_hr