Write the following SQL queries in Java

The ID's of all tables reference each other

It must use Subqueries when possible

Here are the tables:

classroom(building, room number, capacity)

department(dept_name, building, budget)

course(course id, title, dept_name, credits)

instructor(ID, name, dept_name, salary)

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

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

student(ID, name, dept_name, tot_cred)

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

advisor(s_ID, i_ID)

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

prereq(course_id, prereq_id)

Here is the Queries

Query 7: Find the first and the last semesters For each student, find the first semester and the last semester in which he/she has taken a course. The key to this query is to find a way to compare three different semesters (Spring, Summer, and Fall) in a year. Combine the semester and the year information in one field in your result. Here is the correct query result for your reference:

import java.io.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.CallableStatement; import java.util.*; import java.lang.String; 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 " + " " + " s.ID, s.name, sum(c.credits*t.grade)/sum(c.credits) " + " " + "from " + " student as s inner join " + " ( " + " select " + " ID, course_id, " + " case " + " when grade='A' then 4.0 " + " when grade='A-' then 3.67 " + " when grade='B+' then 3.33 " + " when grade='B' then 3.0 " + " when grade='B-' then 2.67 " + " when grade='C+' then 2.33 " + " when grade='C' then 2.0 " + " when grade='C-' then 1.67 " + " when grade='D+' then 1.33 " + " when grade='D' then 1.0 " + " when grade='D-' then 0.67 " + " when grade='F' then 0.0 " + " else 0 " + " end as grade " + " from takes " + " ) as t " + " " + " on s.ID = t.ID and t.grade is not null " + " inner join course as c on c.course_id = t.course_id " + " " + "group by s.ID, s.name"; resultSet = statement.executeQuery(query); } public void printGPAInfo() throws IOException, SQLException { System.out.println("******** Query 1 ********"); while (resultSet.next()) { String id = resultSet.getString(1); String name = resultSet.getString(2); String gpa = resultSet.getString(3); System.out.printf("%s %s %s ", id, name, gpa); } } 

 public void findFirstLastSemester() throws SQLException { } public void printFirstLastSemester() throws IOException, SQLException { System.out.println("******** Query 7 ********"); } }

I added queries that I already have done to make it easier, thank you!

id name First_Semester Last_Semester 00128 Zhang Fall 2009 Fall 2009 12345 Shankar Spring 2009 Spring 2010 19991 Brandt Spring 2010 Spring 2010 23121 Chavez Spring 2010 Spring 2010 44553 Peltier Fall 2009 Fall 2009 45678 Levy Fall 2009 Spring 2010 54321 Williams Spring 2009 Fall 2009 55739 Sanchez Spring 2010 Spring 2010 76543 Brown Fall 2009 Spring 2010 76653 Aoi Spring 2009 Spring 2009 98765 Bourikas Fall 2009 Spring 2010 98988 Tanaka Summer 2009 Summer 2010 id name First_Semester Last_Semester 00128 Zhang Fall 2009 Fall 2009 12345 Shankar Spring 2009 Spring 2010 19991 Brandt Spring 2010 Spring 2010 23121 Chavez Spring 2010 Spring 2010 44553 Peltier Fall 2009 Fall 2009 45678 Levy Fall 2009 Spring 2010 54321 Williams Spring 2009 Fall 2009 55739 Sanchez Spring 2010 Spring 2010 76543 Brown Fall 2009 Spring 2010 76653 Aoi Spring 2009 Spring 2009 98765 Bourikas Fall 2009 Spring 2010 98988 Tanaka Summer 2009 Summer 2010