KOSA fullstack 교육(Having 절, RollUP 절, JDBC 4단계)

Having 절

SELECT job, deptno, count(*) 개수, avg(sal) 평균급여, sum(sal) 총급여
FROM emp
GROUP BY job, deptno
ORDER BY 1,2;

-- 부서별 평균 급여가 2000달러 이상인 부서의 부서번호와 평균급여를 검색
-- step 1
-- where절에서는 alias 사용 못함, 그룹함수 사용 못함
-- 아래와 같이 짜면 오류남.
SELECT deptno, avg(sal) AvgSalary
FROM emp
WHERE avg(sal) >= 2000
GROUP BY deptno;

-- step 2
SELECT deptno, avg(sal) AvgSalary
FROM emp                 
GROUP BY deptno
HAVING avg(sal) >= 2000
ORDER BY 1;

Grouping을 한 후

원하는 데이터만 디스플레이 하고싶을때는

HAVING절을 사용해서 한번 더 걸러줄 수 있다.

WHERE절은 Grouping하기 전에 사용되고

HAVING절은 Grouping한 후에 사용된다.

결과적으로 그룹함수는 WHERE절에는 사용할 수 없고, HAVING절에서만 사용된다.

+ HAVING절은 GROUP BY 절 뒤에 오는 것이 의미론적으로 맞다. (오라클은 가능하긴 하다.)

GROUP BY, HAVING 예제

-- 최대급여가 2900 달러가 넘는 부서들 중에서 최대급여를 조회
SELECT deptno, max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal) > 2900
ORDER BY 2 DESC
LIMIT 1;

-- 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 조회
SELECT deptno 부서번호, count(empno) 인원수 , sum(sal) 급여합
FROM emp
GROUP BY deptno
HAVING count(empno) > 4
ORDER BY 1;

-- emp 테이블에서 급여가 최대 2900 이상인 부서에 대해서 부서번호, 평균급여, 급여의 합을 조회
SELECT deptno 부서번호, avg(sal) 평균, sum(sal) 급여합
FROM emp
GROUP BY deptno
HAVING max(sal) >=2900
ORDER BY 1;

-- 10번 혹은 20번부서에서 부서별 급여의 총합이 1만 달러를 넘는 부서의 부서번호, 급여총합 검색 
SELECT deptno 부서번호, sum(sal) 급여총합
FROM emp
GROUP BY deptno
HAVING sum(sal) > 10000 AND deptno IN (10,20)
ORDER BY 1;

-- 부서별 평균급여가 1200달러를 넘는 부서의 평균급여를 검색
-- 단 CLERK 업무 부서는 제외하고 조회
-- HAVING절은 그루핑 한 deptno만 인식하기 때문에 job에 대한 식을 넣으면 안된다. 
SELECT deptno 부서번호, avg(sal) 평균급여
FROM emp
WHERE job <> 'CLERK'
GROUP BY deptno
HAVING avg(sal) > 1200 
ORDER BY 1;

ROLLUP 절

GROUP BY의 확장판
그룹별로 소계를 자동으로 생성해줌
GROUP BY절에 WITH ROLLUP 추가하면 됨

workshop 문제

-- #################### 단일행 함수 실습 ###################
-- 1) 'Adams의 입사일은 87/05/23 이고, 급여는 3000$ 입니다.' 이런 식으로 직원정보를 조회하시오.
-- 알리야스는 Info로 합니다.
       
select  concat(ename,'의 입사일은',  hiredate ,' 이고 급여는 ',  sal , ' $ 입니다.') Info
from emp; 


-- 2) 1987 년도에 입사한 직원의 이름, 입사일을 조회하시오.
select ename, sal, hiredate
from emp
where year(hiredate) = 1987; 



-- ##################  그룹 함수 실습 ######################
-- 1)  30번 부서 월급의 평균(소수2자리 아래 올림), 최고, 최저, 인원수를 구하여 출력하라.
select ceil(avg(sal)*10)/10, max(sal), min(sal), count(sal)
from emp
where deptno=30;

-- 2) 각 부서별 급여의 평균(소수이하 반올림), 최고, 최저, 인원수를 구하여 출력하라.
select round(avg(sal)) 평균, max(sal) 최고, min(sal) 최저, count(sal) 인원수
from emp
group by deptno;


-- 3) 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호,업무명, 인원수를 출력하라.
select deptno, job, count(empno)
from emp
group by deptno, job 
order by deptno;

-- 4) 같은 업무를 하는 사람의 수가 2명 이상인 업무와 인원수를  출력하라.
select job, count(empno)
from emp
group by job
having count(empno)>=2;


-- 5) 각 부서별 평균 월급(소수이하 버림), 전체 월급, 최고 월급, 최저 월급을 구하여 평균 월급이 많은 순으로 출력하라. 
select trunc(avg(sal)) 평균, sum(sal), max(sal), min(sal)
from emp
group by deptno
order by 평균 desc;


-- 6) 각 부서별 같은 업무를 하는 사람의 급여의 합계를 구하여 부서번호,업무명, 급여합계를 출력하라.
-- 단, 부서별 급여합계도 같이 출력하라.

select deptno, job, sum(sal)
from emp
group by rollup(deptno, job)
order by deptno;



-- 7) 각 부서별 인원수를 조회하되 인원수가 5명 이상인 부서만 출력되도록 하시오.
select deptno , count(empno) 
from  emp 
group by deptno
having count(empno) >= 5;

-- 8)  각 부서별 최대급여와 최소급여를 조회하시오.
--   단, 최대급여와 최소급여가 같은 부서는 직원이 한명일 가능성이 높기때문에    조회결과에서 제외시킨다.
select deptno ,max(sal), min(sal), count(empno)
from  emp
group by deptno
having max(sal) <> min(sal);


/* 9) 부서가 10, 20, 30 번인 직원들 중에서 급여를 2500 이상 5000 이하를 받는
   직원들을 대상으로 부서별 평균 급여를 조회하시오.
   다, 평균급여가 2000 이상인 부서만 출력되어야 하며, 출력결과를 평균급여가 높은
   부서먼저 출력되도록 해야 한다. */
 select deptno, avg(sal) 
 from emp
 where  deptno in (10,20,30)
 and sal between 2500 and 5000 
 group by deptno 
 having avg(sal) >= 2000 
 order by 2 desc  ;

 -- 10) 각 부서별 인원수를 조회하되 인원수가 5명 이상인 부서만 출력되도록 하시오.
 SELECT deptno, COUNT(empno) 부서별인원수 
 FROM emp 
 GROUP BY deptno 
 HAVING COUNT(empno)>=5;

/*
   11) 부서가 10,20 번인 직원들 중에서 급여를 1500 이상 2500 이하를 받는
   직원들을 대상으로 부서별 평균 급여를 조회하시오.
   다, 평균급여가 2000 이상인 부서만 출력되어야 하며, 출력결과를 평균급여가 높은
   부서면저 출력되도록 해야 한다.
*/

 select deptno, avg(sal) 
 from emp 
 where  deptno in (10, 20)
 and sal between 1500 and 2500 
 group by deptno 
 having avg(sal) >= 2000 
 order by 2 desc

주로 쓰는 데이터 타입

숫자

int
tinyInt
decimal(전체 숫자, 소수점 자리)

날짜

Date
TimeStamp

문자

char (고정 길이)
varchar
Blob

JDBC

Driver: 서버의 정보를 가지고 있는 제품

DAO 프로그램을 통해서 디비 서버에 연결하려면 사전에 반드시 이 Driver가 프로그램 사이드 메모리에 로딩되어져 있어야 가능하다!

java용 드라이버를 다운받아보자

eclipse 의 workspace를 다른 폴더로 경로를 설정해서 열어보면,

다음과 같이 EE 환경이 기본으로 설정되어 있는 것을 볼 수 있다.

하지만 우리는 SE 으로 만들것이기에 java를 선택하고 Open한다.

우선 Driver를 연결해야한다. Project의 Build path를 우클릭해서 Configure Build Path... 를 선택한다.

그 다음 add해서 라이브러리를 만들고, 거기에 AddExternal Class Folder...를 선택하고 아래와같이 연결한다.

다음과 같이 연결한다.

DAO(Database Access Object)

java 프로그램을 통해서 DBMS에 접속

메소드를 통해서 SQL이 실행되도록 한다.

1. MySQL DRIVER를 해당 클래스 메모리에 로딩

2. DB 서버 접속 성공하면 Connection 객체를 반환받고

이후 작업은 Connection 을 통해서 모든 작업이 이뤄진다.

3. 쿼리문을 실행

executeUpdate() --- INSERT/DELETE/UPDATE (DML실행 함수)

executeQuery() --- SELECT

public class JDBCProcessTest1 {
	public static void main(String[] args) {
		//1. DRIVER 로딩
		//forName 함수안에 드라이버 이름을 입력해주면 된다. 
		try {
			Class.forName("com.mysql.cj.jdbc.Driver"); //FQCN(FullNameClass)
			System.out.println("Driver Loading...Success");
			
			//2.디비서버 연결
			String url = "jdbc:mysql://127.0.0.1:3306/kosa?serverTimezone=Asia/Seoul&useSSL=false&useUnicode=true&characterEncoding=UTF-8";
			try {
				Connection conn =DriverManager.getConnection(url, "root", "12341234");
				System.out.println("DB Connect...Success");
				
				//3. PreparedStatement 생성
				PreparedStatement ps = conn.prepareStatement("DELETE * FROM custom WHERE id = 3");
				System.out.println("PreparedStatement Creating...");
			} catch (SQLException e) {
				System.out.println("DB Connect...Fail");
			}
		} catch (ClassNotFoundException e) {
			System.out.println("Driver Loading...Fail");
		}
		
	}
}

하지만 이렇게 직접적으로 상수값을 넣는 것은 아주아주 좋지않다!! 상수로 빼고 깔끔하게 만든 버전은 아래이다.

1. 드라이버 로딩
2. 디비연결 후 Connection 반환 받는다.
3. PreparedStatement 생성
4. 쿼리문 실행
1) ?에 해당하는 부분에 각각 값을 바인딩
2) executeUpdate() | excuteQuery()로 실행

public class JDBCProcessTest2 {
	public static final String DRIVER_NAME="com.mysql.cj.jdbc.Driver";
	public static final String URL = "jdbc:mysql://127.0.0.1:3306/kosa?serverTimezone=Asia/Seoul&useSSL=false&useUnicode=true&characterEncoding=UTF-8";
	public static final String USER="root";
	public static final String PASS="12341234";

	
	public JDBCProcessTest2() {
		try {
			Connection conn = DriverManager.getConnection(URL,USER,PASS);
			System.out.println("디비 서버 연결 성공..!!");
			String query = "INSERT INTO custom (id, name, address) VALUES(?,?,?)";
			PreparedStatement ps = conn.prepareStatement(query);
			System.out.println("PreparedStatement 생성...!!");
			ps.setInt(1,4);
			ps.setString(2, "아이유");
			ps.setString(3, "종료");
			int row = ps.executeUpdate();
			System.out.println(row + "ROW INSERT OK ~~!");
		}catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public static void main(String[] args) {
		new JDBCProcessTest2();
	}
	
	//main보다 먼저 실행되어야 하기 때문에 static 함수로 만든다.
	static {
		try {
			Class.forName(DRIVER_NAME);
			System.out.println("드라이버 로딩 성공");
		}catch(ClassNotFoundException e){
			System.out.println("드라이버 로딩 실패...");
		}
	}

}

이 코드에서, executeUpdate 함수 반환형이 int이다. 왜 숫자로 반환될까?

실패한다면 실패한 라인이 없기 때문에 0이고
성공했다면 성공한 줄의 개수만큼 반환한다.

해당 코드는 서버에 관련된 실제 정보가 코드에 그대로 노출되어져 있다.

프로그램에서 사용되고 있는 상수값은 별도로 분리해야 보안 측면에서나 재사용성에서나 더 좋은 코드로 만들 수 있다.

	public static final String DRIVER_NAME="com.mysql.cj.jdbc.Driver";
	public static final String URL = "jdbc:mysql://127.0.0.1:3306/kosa?serverTimezone=Asia/Seoul&useSSL=false&useUnicode=true&characterEncoding=UTF-8";
	public static final String USER="root";
	public static final String PASS="12341234";

파일 분리, 즉 모듈화를 해야한다.

properties 파일은 어디에 있을까?

Map

HashMap
HashTable
- Properties(String key - String value)

어떤 파일로 분리하는 게 좋을까?

-> 템플릿으로만 이루어져 있는, 필드가 존재할 수 없는 인터페이스가 적합하다!

그 다음 구조적으로 DAO 클래스를 만들어보자.

DAO Class

public class CustomDAO {
	public CustomDAO() throws SQLException{
		//디비서버 연결
		Connection connection = DriverManager.getConnection(ServerInfo.URL, ServerInfo.USER, ServerInfo.PASS);
		System.out.println("서버 연결");
		
		// PreparedStatement 생성
		String queryString = "INSERT INTO custom(id,name,address) VALUES(?,?,?)";
		PreparedStatement ps = connection.prepareStatement(queryString);
		
		//쿼리문 실행
		ps.setInt(1, 6);
		ps.setString(2,  "쿠로미");
		ps.setString(3, "제주도");
		System.out.println(ps.executeUpdate() + "row 등록");
	}
}

DAO Class를 실행시키는 Test코드

public class JDBCProcessTest3 {

	public static void main(String[] args) {
		try {
			new CustomDAO();
		}catch(SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	static {
		try {
			Class.forName(ServerInfo.DRIVER_NAME);
		}catch(ClassNotFoundException e) {
			System.out.println(e.getMessage());
		}
	}

}

'KOSA FullStack 교육 > DB' 카테고리의 다른 글

KOSA fullstack 교육 (JOIN) (1)	2025.05.23
KOSA fullstack 교육(JDBC 4단계 과정) (0)	2025.05.22
KOSA fullstack 교육(변환함수, 날짜함수, case문, 그룹함수) (2)	2025.05.21
KOSA fullstack 교육 (IN, LIKE 연산자, 단일 행 함수, 숫자함수, 날짜함수) (1)	2025.05.21
KOSA fullstack 교육 (SELECT, ORDER BY, DISTINCT, IFNULL, LIMIT) (0)	2025.05.16

로미와 개발

KOSA fullstack 교육(Having 절, RollUP 절, JDBC 4단계)

Having 절

ROLLUP 절

주로 쓰는 데이터 타입

JDBC

'KOSA FullStack 교육 > DB' 카테고리의 다른 글

티스토리툴바

KOSA fullstack 교육(Having 절, RollUP 절, JDBC 4단계)

Having 절

ROLLUP 절

주로 쓰는 데이터 타입

JDBC

'KOSA FullStack 교육 > DB' 카테고리의 다른 글

'KOSA FullStack 교육/DB' Related Articles

티스토리툴바