Connect to the Oracle database Submit the .java file Problem 1 Display the numbe
ID: 3685242 • Letter: C
Question
Connect to the Oracle database
Submit the .java file
Problem 1
Display the number of students.
Use the student.student table.
Problem 2
Display all students with a last name beginning with a Z or Y.
List the students in alphabetical order by their last name.
Format the names as: last name, first name.
For example: Smith, Bill
Use the student.student table.
Problem 3
Display all information about all 4 employees
You must use column names
Use the student.employee table
Problem 4
Display all information about all 4 employees
You must use column indexes
Use the student.employee table
Problem 5
Display the number of students that registered on January 30, 2007
Use an alias of “Registered on Jan 30, 2007”
You must use column names
Explanation / Answer
import java.sql.*;
class OracleCon{
public static void main(String args[]){
try{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
//step2 create the connection object
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
//step3 create the statement object
Statement stmt=con.createStatement();
//step4 execute query
ResultSet rs=stmt.executeQuery("select count(student_id) from Student");
while(rs.next())
System.out.println(rs.getInt(1));
rs = stmt.executeQuery("SELECT last_name FROM Student" +" WHERE last_name LIKE 'z%'OR'y%' ");
while(rs.next()){
//Retrieve by column name
String last = rs.getString("last");
//Display values
System.out.println(", Last: " + last);
}
rs = stmt.executeQuery("SELECT last_name FROM Student" +
" ORDER BY last_name ASC");
while(rs.next()){
//Retrieve by column name
String first = rs.getString("first_name");
String last = rs.getString("last_name");
//Display values
System.out.println(", Last: " + last);
System.out.print(", First: " + first);
}
rs = stmt.executeQuery( "SELECT * FROM Employee where student.id=Employee.id");
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first_name");
String last = rs.getString("last_name");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs = stmt.executeQuery( "SELECT * FROM Student where registered='30/01/2007'");
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first_name");
String last = rs.getString("last_name");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs = stmt.executeQuery( "SELECT registered AS Registered FROM Student");
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first_name");
String last = rs.getString("last_name");
String date=rs.getString("Registered");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
System.out.println("date"+date);
}
//step5 close the connection object
con.close();
}catch(Exception e){ System.out.println(e);}
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.