6월25일 - 오라클 SQL1

문제 ) sungjuk테이블 전체목록을 sno 칼럼순으로 정렬해서 출력하시오

전에 했던 jdbc0621 Test07_SelectAll.java 참고

String url     ="jdbc:oracle:thin:@localhost:1521:xe"; //본인 ip, port listener 1521, xe 무료배포용
String user    ="java0514";
String password="1234";
String driver  ="oracle.jdbc.driver.OracleDriver"; //ojdbc6.jar 							
	
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
		
try{
	Class.forName(driver);
	con= DriverManager.getConnection(url, user, password); //get이기때문, 리턴형이 있다. 
	System.out.println("오라클 DB 서버 연결 성공!");
		
	StringBuilder sql=new StringBuilder();
	sql.append(" SELECT sno,uname,kor,eng,mat,aver,addr,wdate ");
	sql.append(" FROM sungjuk ");
	sql.append(" ORDER BY sno ");

    pstmt=con.prepareStatement(sql.toString());
	rs=pstmt.executeQuery(); //select문 ,리턴형, sql값이 담긴다.
		if(rs.next()){ //cursor가 있나?
			System.out.println("자료있음!");
			do{
				System.out.print(rs.getInt("sno")+" ");
				System.out.print(rs.getString("uname")+" ");
				System.out.print(rs.getInt("kor")+" ");
				System.out.print(rs.getInt("eng")+" ");
				System.out.print(rs.getInt("mat")+" ");
				System.out.print(rs.getInt("aver")+" ");
				System.out.print(rs.getString("addr")+" ");
				System.out.print(rs.getString("wdate")+" ");
				System.out.println();					
			}while(rs.next());				
		}else{
			System.out.println("자료없음!");
		}//if end			
			
}catch(Exception e){
	System.out.println("실패!"+e);
}finally{
	//자원반납(순서주의~!)
	try{
		if(rs!=null){rs.close();}
	}catch(Exception e){}
			
	try{
		if(pstmt!=null){pstmt.close();}
	}catch(Exception e){}
			
	try{
		if(con!=null){con.close();}
	}catch(Exception e){}
}//try end		

 

출력결과

분석 -> 

date의 자료형을 string형으로 하면 date와 입력한 시간의 정보까지 다 출력이 된다. 

                      date형으로 하면 date의 정보만 출력이 된다.

 

문제 ) sungjuk테이블 전체 행갯수를 출력하시오

String url     ="jdbc:oracle:thin:@localhost:1521:xe"; //본인 ip, port listener 1521, xe 무료배포용
String user    ="java0514";
String password="1234";
String driver  ="oracle.jdbc.driver.OracleDriver"; //ojdbc6.jar 							
	
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
		
try{
	Class.forName(driver);
	con= DriverManager.getConnection(url, user, password); //get이기때문, 리턴형이 있다. 
	System.out.println("오라클 DB 서버 연결 성공!");
		
	StringBuilder sql=new StringBuilder();
	sql.append(" SELECT count(*) as cnt ");
	sql.append(" FROM sungjuk ");
			
	pstmt=con.prepareStatement(sql.toString());
	rs=pstmt.executeQuery(); //select문 ,리턴형, sql값이 담긴다.
	if(rs.next()){ //cursor가 있나?
		System.out.println("자료있음!");
		do{
			System.out.println("전체 행갯수:"+rs.getInt("cnt"));
			System.out.println("전체 행갯수:"+rs.getInt(1));
			System.out.println();					
		}while(rs.next());				
	}else{
		System.out.println("자료없음!");
	}//if end			
			
}catch(Exception e){
	System.out.println("실패!"+e);
}finally{
	//자원반납(순서주의~!)
	try{
		if(rs!=null){rs.close();}
	}catch(Exception e){}
			
	try{
		if(pstmt!=null){pstmt.close();}
	}catch(Exception e){}
			
	try{
		if(con!=null){con.close();}
	}catch(Exception e){}
}//try end		

 

출력결과

 

문제) 주소가 서울인 행들의 국영수 평균값을 출력하시오

String url     ="jdbc:oracle:thin:@localhost:1521:xe"; //본인 ip, port listener 1521, xe 무료배포용
String user    ="java0514";
String password="1234";
String driver  ="oracle.jdbc.driver.OracleDriver"; //ojdbc6.jar 							
	
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
		
try{
	Class.forName(driver);
	con= DriverManager.getConnection(url, user, password); //get이기때문, 리턴형이 있다. 
	System.out.println("오라클 DB 서버 연결 성공!");
		
	String keyword="Seoul";
			
	StringBuilder sql=new StringBuilder();
	sql.append(" SELECT avg(kor) as avg_kor, avg(eng) as avg_eng, avg(mat) as avg_mat ");
	sql.append(" FROM sungjuk ");
	sql.append(" WHERE addr=? ");
			
	pstmt=con.prepareStatement(sql.toString());
	pstmt.setString(1, keyword);
			
	rs=pstmt.executeQuery(); //select문 ,리턴형, sql값이 담긴다.
			
	if(rs.next()){ //cursor가 있나?
		System.out.println("자료있음!");
		do{
			System.out.println("avg_kor : "+rs.getInt("avg_kor")+"");
			System.out.println("avg_eng : "+rs.getInt("avg_eng")+"");
			System.out.println("avg_mat : "+rs.getInt("avg_mat")+"");
			System.out.println();					
		}while(rs.next());				
	}else{
		System.out.println("자료없음!");
	}//if end			
			
}catch(Exception e){
	System.out.println("실패!"+e);
}finally{
	//자원반납(순서주의~!)
	try{
		if(rs!=null){rs.close();}
	}catch(Exception e){}
			
	try{
		if(pstmt!=null){pstmt.close();}
	}catch(Exception e){}
			
	try{
		if(con!=null){con.close();}
	}catch(Exception e){}
}//try end	

출력결과