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 4: Find the prereq for each course

For each course, list the name of the course and the name of its prereq course. You need to include those courses that don't have any prereqs (Leave the prereq field empty for those courses). 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 findPrereq() throws SQLException { } public void printPrereq() throws IOException, SQLException { System.out.println("******** Query 4 ********"); } 

}

The methods being worked on is findPrereq() and printPrereq(), thank you!

course prereq Intro to Biology Genetics Intro to Biology Computational Biology Intro. to Biology Intro to Computer Science Game Design Intro to Computer Science Robotics Intro to Computer Science Image Processing Intro to Computer Science Database System Concepts Intro to Computer Science Intro. to Digital Systems Physical Principles Investment Banking World History Music Video Production Physical Principles