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 6: Write a stored procedure to get the head count First you define a stored procedure in database_4 named getNumbers. This procedure takes department name as input, and returns the number of instructors and the number of students in the department as outputs. In other words, getNumbers() has one input parameter and two output parameters. Test this procedure in MySQL to make sure it functions properly. Next, your application program asks the user to enter a department name, and the program should return the number of instructors and the number of students in the department accordingly. Here is a snapshot of the sample output

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 findHeadCounts() throws SQLException { System.out.println("******** Query 6 ********"); }

 }

The method I am working on is findHeadCounts(), thank you!

******** Query 6 ******** Please enter the department name: Comp. Sci. Comp. Sci. Department has 3 instructors. Comp. Sci. Department has 4 students