[취성패] 자바 프로그래밍 - 1, 2일차 학습일지(SQL)

- 체온 측정, 내일 배움 카드로 출석 체크 후 강의실 입장

- OT는 미리 받았으므로 바로 진행

- 현재 수강 인원은 21/25명, 강의실 수용 가능 인원 6명(코로나..)

- 기초 상담지, 사전평가 자가진단, 사전능력 평가지, 체크리스트 작성

 

---

----- DB SQL 활용 수업 ------

web : pc, 모바일 상관없이 web 브라우저만 있으면 작동함

UI 웹 : HTML, 자바 script

DB : SQL문 구축

    - 오라클 : 오라클사 / 대형회사에서 사용

    - MS-SQL : 마이크로소프트사 / 작은 스타트업 등에서 사용

    - MY-SQL : 오라클사 / free버전(무료, 오픈소스)

SQL : standard query language

    - 데이터를 조회(검색), 입력, 수정, 삭제

 

    [기능에 따른 분류]

- 데이터 정의어(DDL) : 테이블이나 관계의 구조를 생성하는 데 사용 -> create(생성), alter(변경), drop(삭제) 등

- 데이터 조작어(DML) : 테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용 -> select(검색), insert(삽입), delete(삭제), update(수정) 등

  (select문은 특별히 질의어 query라고 함)

- 데이터 제어어(DCL) : 데이터의 사용 권한을 관리하는 데 사용 -> grant, revoke 등

데이터 정의어, 데이터 조작어 주요 명령어

 

연산자

    - 비교 연산자를 가장 많이 씀 : =, >, <    -> 결과는 boolean 값으로 나옴(참, 거짓)

    - 산술 연산자는 프로그램 때 많이 씀 : +, -

    - 논리연산 : And, Or

    - 일항연산

    - 이항연산 : price < 20000;

    - 삼항연산 : A ? B : C;

                    price between 10000 and 20000;

값 - 숫자값 : 0, 1, 2..

    - 논리값 : boolean(불린) - True, False

 

[table]

행 → (row)	↓ 열(column)

행 한 줄을 tuple(튜플)이라고 함

---

오라클에서 오라클 11g ex 버전 windows 64용 설치

                SQL developer 설치

 

SQL Developer

 

샘플 데이터베이스 설치

- 계정 2개 만들기 : 시스템계정, 마당계정

계정 생성 창

 

[시스템 계정 워크시트]

/* 시스템 계정 */

DROP USER madang CASCADE; 
CREATE USER madang IDENTIFIED BY madang DEFAULT TABLESPACE users TEMPORARY

/* ID : madang, PW : madang -> 비활성화 상태 */
TABLESPACE temp PROFILE DEFAULT; 
GRANT CONNECT, RESOURCE TO madang;

/* madang에게 connect, resource 권한 부여 */
GRANT CREATE VIEW, CREATE SYNONYM TO madang; 
ALTER USER madang ACCOUNT UNLOCK;

/*수정 : madang 유저의 계정을 unlock해서 활성화 시키겠다. */
/* 여기서부터는 마당 계정으로 접속 */ 
conn madang/madang;   -> coon : 데이터베이스 접속

/* 접속 ID/PW */
CREATE TABLE Book ( 
bookid NUMBER(2) PRIMARY KEY, 
bookname VARCHAR2(40), 
publisher VARCHAR2(40), 
price NUMBER(8) 
); 
CREATE TABLE Customer ( 
custid NUMBER(2) PRIMARY KEY, 
name VARCHAR2(40), 
address VARCHAR2(50), 
phone VARCHAR2(20) 
); 
CREATE TABLE Orders ( 
orderid NUMBER(2) PRIMARY KEY, 
custid NUMBER(2) REFERENCES Customer(custid), 
bookid NUMBER(2) REFERENCES Book(bookid), 
saleprice NUMBER(8) , 
orderdate DATE 
); /* Book, Customer, Orders 데이터 생성 */ 
INSERT INTO Book VALUES(1, '축구의 역사', '굿스포츠', 7000); 
INSERT INTO Book VALUES(2, '축구아는 여자', '나무수', 13000); 
INSERT INTO Book VALUES(3, '축구의 이해', '대한미디어', 22000); 
INSERT INTO Book VALUES(4, '골프 바이블', '대한미디어', 35000); 
INSERT INTO Book VALUES(5, '피겨 교본', '굿스포츠', 8000); 
INSERT INTO Book VALUES(6, '역도 단계별기술', '굿스포츠', 6000); 
INSERT INTO Book VALUES(7, '야구의 추억', '이상미디어', 20000); 
INSERT INTO Book VALUES(8, '야구를 부탁해', '이상미디어', 13000); 
INSERT INTO Book VALUES(9, '올림픽 이야기', '삼성당', 7500); 
INSERT INTO Book VALUES(10, 'Olympic Champions', 'Pearson', 13000); 
INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스타', '000-5000-0001'); 
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001'); 
INSERT INTO Customer VALUES (3, '장미란', '대한민국 강원도', '000-7000-0001'); 
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001'); 
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전', NULL);  /* null : 공백 */
INSERT INTO Orders VALUES (1, 1, 1, 6000, TO_DATE('2014-07-01','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (2, 1, 3, 21000, TO_DATE('2014-07-03','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (3, 2, 5, 8000, TO_DATE('2014-07-03','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (4, 3, 6, 6000, TO_DATE('2014-07-04','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (5, 4, 7, 20000, TO_DATE('2014-07-05','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (6, 1, 2, 12000, TO_DATE('2014-07-07','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (7, 4, 8, 13000, TO_DATE( '2014-07-07','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (8, 3, 10, 12000, TO_DATE('2014-07-08','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (9, 2, 10, 7000, TO_DATE('2014-07-09','yyyy-mm-dd')); 
INSERT INTO Orders VALUES (10, 3, 8, 13000, TO_DATE('2014-07-10','yyyy-mm-dd')); 
CREATE TABLE Imported_Book ( 
bookid NUMBER, 
bookname VARCHAR(40), 
publisher VARCHAR(40), 
price NUMBER(8) 
); 
INSERT INTO Imported_Book VALUES(21, 'Zen Golf', 'Pearson', 12000); 
INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000); 
COMMIT;

 

 

---

[SQL 문을 작성할 때 주로 사용하는 명령어]

<Tip> SQL Plus에서 사용하는 명령어에 관한 자세한 설명은 여기를 눌러 참고

 

• 데이터베이스 접속 : conn 
  •  conn scott/tiger : scott 계정에 비밀번호 tiger로 접속
• 명령어 실행 : run, /
  • run : 바로 전에 실행했던 명령어를 다시 실행
  • / : run과 같은 의미
• 명령어 찾기 : list
  • list : 마지막에 수행했던 명령어를 출력한다. 직전 명령줄이 길 때 편리함
• 메모장을 이용하여 명령어 작성 및 실행하기 : ed <파일이름>, run <파일이름>
  • ed test : test.sql 이름의 파일이 메모장을 이용하여 작성할 수 있도록 열림
  • start test : test.sql 이름에 저장된 명령어 스크립트가 실행됨
  • @ test : start test와 같은 의미
• 출력 모양을 조절하는 명령 : column
  • column bookname format a20 : bookname을 길이 20의 문자 포맷으로 출력
  • column price format 999999 : price를 길이 6개의 숫자 포맷으로 출력

---

 

[마당계정 접속]

마당서점의 데이터

SQL 문의 내부적 실행 순서

Q. 김연아 고객의 전화번호를 찾으시오.

select phone
from customer
where name like '김연아';

 

질의

- 모든 도서의 이름과 가격을 검색하시오

select bookname, price from book;

 

- 모든 도서의 가격과 이름을 검색하시오

select price, bookname from book;

 

- 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오

select bookid, bookname, publisher, price from book;

-- 또는

select * from book;

 

- 도서 테이블에 있는 모든 출판사를 검색하시오

select publisher from book;

 

- 도서 테이블에 있는 모든 출판사를 검색하시오(중복 제거)

select distinct publisher from book;

(ex. select distinct publisher, price -> publisher에만 distinct 적용됨)

 

- 가격이 20,000원 미만인 도서를 검색하시오

select * from book where price < 20000;

 

- 가격이 10,000원 이상 20,000원 이하인 도서를 검색하시오

select * from book where price between 10000 and 20000;

-- 또는

select * from book where price >= 10000 and price <= 20000;

 

- 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오

select * from book where publisher in ('굿스포츠', '대한미디어');

-- 또는

select * from book where publisher like '굿스포츠' or publisher like '대한미디어';

-> 문자열 찾을 때는 like '문자열'

 

- 출판사가 '굿스포츠' 혹은 '대한미디어'가 아닌 도서를 검색하시오

select * from book where publisher not in ('굿스포츠', '대한미디어');

 

- '축구의 역사'를 출간한 출판사를 검색하시오

select bookname, publisher from book where bookname like '축구의 역사';

- 도서이름에 '축구'가 포함된 출판사를 검색하시오

select bookname, publisher from book where bookname like '%축구%';

 

- 도서이름의 왼쪽 두 번째 위치에 '구'라는 문자열을 갖는 도서를 검색하시오

select * from book where bookname like '_구%';

  -> % 여러 글자 대신함, _ 한 글자 대신함

 

- 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오

select * from book where bookname like '%축구%' and price >= 20000;

 

- 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오

select * from book where publisher = '굿스포츠' or publisher = '대한미디어';

 

- 도서를 이름순으로 검색하시오

select * from book order by bookname;

- 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오

select * from book order by price, bookname;

 

- 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다

select * from book order by price desc, publisher asc;

-> 오름차순 : ASC / 숫자(0 1 2 ..) -> 영어(A B C ..) -> 한글(ㄱ ㄴ ㄷ ..), 생략 시 오름차순

    내림차순 : DESC

 

- 고객이 주문한 도서의 총 판매액을 구하시오

select sum(saleprice) from orders;

 

- 고객이 주문한 도서의 총 판매액을 구하시오(열 이름은 총매출로 한다)

select sum(saleprice) as 총매출 from orders;

-> 글자를 띄어쓰면 안됨! 총 매출로 나타내고 싶을 때는 "총 매출" (큰 따옴표로 둘러싸서)로 써야 함

 

- 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오

select sum(saleprice) as "총 판매액" from orders where custid = 2;

-> 고객 번호가 아닌 이름으로 주문하고싶을 때

select sum(saleprice) from orders, customer where orders.custid = customer.cusitd and name = '김연아';

select sum(saleprice) from orders, customer where orders.custid = customer.cusitd and name like '김연아';

 

- 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오

select sum(saleprice) as "총 판매액", avg(saleprice) as 평균값, min(saleprice) as 최저가, 
max(saleprice) as 최고가
from orders;

 

- 마당서점의 도서 판매 건수를 구하시오

select * from orders;

 

- 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오

select custid, count(*) as 도서수량, sum(saleprice) as 총액 from orders group by custid;

-> group by로 튜플을 그룹으로 묶은 후 slect 절에는 맨 앞에 그룹으로 묶은 속성, 집계함수만 나올 수 있다.

 

★ 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오

(단, 두 권 이상 구매한 고객만 구한다)

select custid, count(*) from orders where saleprice >= 8000 group by custid having count(*) >=2;

-> where ~ group by ~ having : 먼저 8000원 이상 도서를 구매한 고객만 걸러서 거기서 그룹을 지음

-> group by절은 where절 뒤에 나와야 함!

 

[연습문제 풀이]

1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오

 

(1) 도서번호가 1인 도서의 이름

select bookname from book where bookid = 1;

 

(2) 가격이 20,000원 이상인 도서의 이름

select bookname from where book price >= 20000;

 

마당서점의 데이터 구상도

 

(3) 박지성의 총 구매액

select sum(saleprice) from orders where custid =1;

select sum(saleprice) from orders, customer where orders.custid = customer.custid and name like '박지성';

 

(4) 박지성이 구매한 도서의 수

select count(*) from orders where custid =1;

select count(*) from orders, customer where orders.custid = customer.custid and name = '박지성';

 

2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL문을 작성하시오

(1) 마당서점 도서의 총 개수

select count(*) from book;

(2) 마당서점 도서를 출고하는 출판사의 총 개수

select count(distinct publisher) from book;

(3) 모든 고객의 이름, 주소

select name, address from customer;

 

(4) 2014년 7월 4일 ~ 7월 7일 사이에 주문받은 도서의 주문번호

select orderid from order where orderdate between '20140704' and '20140707';

select orderid from order where orderdate >= '2014-07-04' and orderdate <= '2014-07-07';

-> 날짜는 ' ' 사이에 넣어주면 됨. '2014 07 04', '2014-07-04', '2014/07/04', '2014 7 4'

 

(5) 2014년 7월 4일 ~ 7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호

select orderid from order where orderdate not between '20140704' and '20140707';

select orderid from order where orderdate < '2014-07-04' or orderdate > '2014-07-07';

 

(6) 성이 '김'씨인 고객의 이름과 주소

select name, address from customer where name like '김%';

select name, address from customer where name = '김%';

(7) 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소

select name, address from customer where name like '김%아';

select name, address from customer where name = '김%아';

-> 이름이 3글자가 넘는 경우도 있을 수 있음

 

 

-----------------------------------------------------------------------------

 

컴활 1급을 준비하면서 SQL문이 익숙해서 이해하는게 수월했고 재미있었다.

다만 원격 훈련으로 진행되다보니 수강생들 쪽에 여러가지 문제들이 생겨서 강사님이 그걸 해결해 주시느라고

몇시간씩을 쏟다보니 수업시간은 7시간이지만 진짜로 수업을 들은 시간은 .. 정말 얼마 안된다는거...

이틀 내내 이러니까 조금 화가 났고 노트북도 너무 오래된 나머지

한 문장을 실행시키면 무려 3분간 컴퓨터가 정지된다..

얼른 코로나가 잠잠해져서 대면 교육을 받고싶다.