외래키, inner join, outer join에 대해 알아보기 전 예시 테이블을 생성하기
1. product 시퀀스 생성
create sequence product_seq
start with 1000
maxvalue 9999
increment by 1
nocycle
nocache;2. sales 시퀀스 생성
create sequence sales_seq
start with 1000
maxvalue 9999
increment by 1
nocycle
nocache;3. product 테이블 생성
create table product (
product_idx number default product_seq.nextval primary key, -- 상품번호
product_name varchar2(100) unique not null, -- 상품이름
product_price number not null -- 상품가격 (단일가격)
);4. sales 테이블 생성
create table sales (
sales_idx number default sales_seq.nextval primary key, -- 매출번호
product_idx number , -- 상품번호 (참조)
sales_Date date default sysdate, -- 매출발생 날짜 및 시간
sales_cnt number not null, -- 구매 개수
sales_total number not null, -- 총 매출 금액
sales_cancel char(1) , varchar2는 가변길이 문자열이고, char는 고정길이 문자열이다.
자바의 char와 달리, 단일 글자가 아니라 문자열임에 주의
5. 제약조건 설정
constraint sales_product_fk -- 제약조건을 설정한다. 이름은 sales_product_fk
foreign key(product_idx) -- 제약조건 종류는 외래키, 현재테이블의 product_idx에 조건을 설정한다
references product(product_idx) -- 외래키가 참조하는 테이블은 product이고, 컬럼은 rpdocut_idx이다
on delete set null -- 참조 대생이 삭제되면, 외래키 컬럼의 값은 null로 설정한다
-- on delete cascade -- 참조 대상이 삭제되면, 해당 레코드를 같이 삭제한다
);6. product에 insert하기
insert into product (product_name, product_price) values ('반팔 티셔츠', 12000);
insert into product (product_name, product_price) values ('후드티', 25000);
insert into product (product_name, product_price) values ('야구모자', 15000);7. sales에 insert하기
insert into sales (product_idx, sales_cnt, sales_total) values (1000, 1, 12000);
insert into sales (product_idx, sales_cnt, sales_total) values (1001, 3, 75000);
insert into sales (product_idx, sales_cnt, sales_total) values (1002, 2, 30000);
insert into sales (product_idx, sales_cnt, sales_total) values (1000, 5, 60000);
insert into sales (product_idx, sales_cnt, sales_total, sales_Date)
values (1001, 2, 50000, '23/04/14');
insert into sales (product_idx, sales_cnt, sales_total, sales_Date)
values (1002, 1, 15000, '23/03/14');
-- 등록되지 않은 상품을 1개 15000원에 판매했음 (product_idx null)
insert into sales (product_idx, sales_cnt, sales_total, sales_Date)
values (null, 1, 15000, '23/03/14'); 8. product 테이블 확인하기
select * from product;
9. sales 테이블 확인하기
select * from sales;
Inner Join과 Outer Join에 대해 확인 해보기
inner join = 교집합
full outer = 합집합
left outer = from에 해당하는 테이블
right outer = join에 해당하는 테이블
📑 문제를 풀면서 inner join과 outer join 알아보기
1) 두개의 테이블을 카티션 프로덕트(곱연산) 형태로 조인하세요
select * from product
join sales
on 1=1;
2) 상품을 기준으로, 매출정보를 inner join 하여 결과를 출력하세요
select * from product P
join sales S
on P.product_idx = S.product_idx;
3) 매출을 기준으로, 상품의 이름을 inner join하여 결과를 출력하세요. 단, 매출의 모든 컬럼과, 상품의 이름, 상품 가격을 join해주세요
select S.*, P.product_name, P.product_price
from sales S
join product P
on S.product_idx = P.product_idx;
4) 3번과 같은 형식으로 full outer join으로 출력해보세요
select S.*, P.product_name, P.product_price
from sales S
full outer join product P
on S.product_idx = P.product_idx;
5) 전체 매출금액의 합계(sales.total)를 구하세요
select sum(sales_total) as 총매출금액
from sales;
6) 등록된 상품(product.idx)에 대한 매출 금액의 합계를 구하세요
select sum(sales_total) from sales
where product_idx is not null; -- != 사용하면 안된다
7) 상품이름 별 매출금액합계 (미등록상품 포함/미포함)
7-1) 등록상품만 구하기 (inner join은 null인 경우 포함하지 않는다)
select
P.product_name as 상품이름,
sum(S.sales_total) as 매출합계
from sales S
join product P
on S.product_idx = P.product_idx
group by P.product_name;
7-2) 등록/미등록 전체 분류 (outer join은 null인 경우도 포함한다)
select
P.product_name as 상품이름,
sum(S.sales_total) as 매출합계
from sales S
left outer join product P
on S.product_idx = P.product_idx
group by P.product_name;
7-3) 미등록상품 매출 구하기
select
P.product_name as 상품이름,
sum(S.sales_total) as 매출합계
from sales S
left outer join product P
on S.product_idx = P.product_idx
where S.product_idx is null
group by P.product_name;
7번 문제를 이클립스와 연동하여 실행하기
- 전체 품목별 매출 합계
- 등록 상품별 매출 합계
- 미등록 상품 매출 합계
- DAO 클래스
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import oracle.jdbc.driver.OracleDriver;
import java.sql.Connection;
import java.sql.DriverManager;
public class ProductSalesDAO {
// JDBC를 사용하여 Oracle 데이터베이스에 연결하고 SQL 쿼리를 실행하는 기능 만들기
Connection conn;
PreparedStatement pstmt;
ResultSet rs;
private String url = "jdbc:oracle:thin:@192.168.1.100:1521:xe";
private String user = "c##itbank";
private String password = "it";
private String className = OracleDriver.class.getName();
public ProductSalesDAO() {
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("잘못된 클래스 이름 예외");
}
}
// 전체 품목별 매출 합계
public ArrayList<HashMap<String, Object>> allResult() {
ArrayList<HashMap<String, Object>> list = new ArrayList<>();
String sql = "select " +
"P.product_name as 상품이름, " +
"sum(S.sales_total) as 매출합계 " +
"from sales S " +
"left outer join product P " +
"on S.product_idx = P.product_idx " +
"group by P.product_name";
try {
conn = DriverManager.getConnection(url, user, password);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("상품이름", rs.getString("상품이름"));
map.put("매출합계", rs.getInt("매출합계"));
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try { if(rs != null) rs.close(); } catch(Exception e) {}
try { if(pstmt != null) pstmt.close(); } catch(Exception e) {}
try { if(conn != null) conn.close(); } catch(Exception e) {}
}
return list;
}
// 등록 상품별 매출 합계
public ArrayList<HashMap<String, Object>> registeredResult() {
ArrayList<HashMap<String, Object>> list = new ArrayList<>();
String sql = "SELECT P.product_name AS 상품이름, " +
"SUM(S.sales_total) AS 매출합계 " +
"FROM sales S " +
"JOIN product P " +
"ON S.product_idx = P.product_idx " +
"GROUP BY P.product_name";
try {
conn = DriverManager.getConnection(url, user, password);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("상품이름", rs.getString("상품이름"));
map.put("매출합계", rs.getInt("매출합계"));
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try { if(rs != null) rs.close(); } catch(Exception e) {}
try { if(pstmt != null) pstmt.close(); } catch(Exception e) {}
try { if(conn != null) conn.close(); } catch(Exception e) {}
}
return list;
}
// 미등록 상품 매출 합계
public ArrayList<HashMap<String, Object>> unregisteredResult() {
ArrayList<HashMap<String, Object>> list = new ArrayList<>();
String sql = "select " +
"P.product_name as 상품이름, " +
"sum(S.sales_total) as 매출합계 " +
"from sales S " +
"left outer join product P " +
"on S.product_idx = P.product_idx " +
"where S.product_idx is null " +
"group by P.product_name";
try {
conn = DriverManager.getConnection(url, user, password);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("상품이름", rs.getString("상품이름"));
map.put("매출합계", rs.getInt("매출합계"));
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try { if(rs != null) rs.close(); } catch(Exception e) {}
try { if(pstmt != null) pstmt.close(); } catch(Exception e) {}
try { if(conn != null) conn.close(); } catch(Exception e) {}
}
return list;
}
}- Main 클래스
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Scanner;
public class Main {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
ProductSalesDAO dao = new ProductSalesDAO();
ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String,Object>>();
int menu;
while(true) {
System.out.println("1. 전체 품목별 매출 합계");
System.out.println("2. 등록 상품별 매출 합계");
System.out.println("3. 미등록 상품 매출 합계");
System.out.println("0. 종료");
System.out.print("선택 >>> ");
menu = Integer.parseInt(sc.nextLine());
switch(menu) {
case 1:
list = dao.allResult();
list.forEach(p -> System.out.println(p));
break;
case 2:
list = dao.registeredResult();
list.forEach(p -> System.out.println(p));
break;
case 3:
list = dao.unregisteredResult();
list.forEach(p -> System.out.println(p));
break;
case 0:
sc.close();
return;
}
} // end of while
} // end of main
}결과
-
전체 품목별 매출 합계
-
등록 상품별 매출 합계
-
미등록 상품 매출 합계
coding is my life