[SQL] 예제 소스 정리 - ORDER BY, JOIN, 서브 쿼리, GROUP BY, HAVING

eomcs-docs\sql\Exam05~10_1.sql

 

 

예제 소스 정리

 

 

eomcs-docs\sql\Exam05.sql

 

 

 

-- # select 테스트 용 테이블 준비

-- 수강생
DROP TABLE IF EXISTS stnt RESTRICT;

-- 강사
DROP TABLE IF EXISTS tcher RESTRICT;

-- 강의
DROP TABLE IF EXISTS lect RESTRICT;

-- 강의실
DROP TABLE IF EXISTS room RESTRICT;

-- 매니저
DROP TABLE IF EXISTS mgr RESTRICT;

-- 수강신청
DROP TABLE IF EXISTS lect_appl RESTRICT;

-- 강의실사진
DROP TABLE IF EXISTS room_phot RESTRICT;

-- 멤버
DROP TABLE IF EXISTS memb RESTRICT;

-- 주소
DROP TABLE IF EXISTS addr RESTRICT;

-- 강의배정
DROP TABLE IF EXISTS lect_tcher RESTRICT;

-- 수강생
CREATE TABLE stnt (
    mno    INTEGER     NOT NULL COMMENT '수강생번호', -- 수강생번호
    work   CHAR(1)     NOT NULL COMMENT '재직여부', -- 재직여부
    acc_no VARCHAR(20) NULL     COMMENT '통장번호', -- 통장번호
    bank   VARCHAR(50) NULL     COMMENT '은행명' -- 은행명
)
COMMENT '수강생';

-- 수강생
ALTER TABLE stnt
    ADD CONSTRAINT PK_stnt -- 수강생 기본키
        PRIMARY KEY (
            mno -- 수강생번호
        );

-- 수강생 유니크 인덱스
CREATE UNIQUE INDEX UIX_stnt
    ON stnt ( -- 수강생
        acc_no ASC, -- 통장번호
        bank ASC    -- 은행명
    );

-- 강사
CREATE TABLE tcher (
    mno    INTEGER     NOT NULL COMMENT '강사번호', -- 강사번호
    acc_no VARCHAR(20) NULL     COMMENT '통장번호', -- 통장번호
    bank   VARCHAR(50) NULL     COMMENT '은행명', -- 은행명
    hr_pay INTEGER     NULL     COMMENT '시강료' -- 시강료
)
COMMENT '강사';

-- 강사
ALTER TABLE tcher
    ADD CONSTRAINT PK_tcher -- 강사 기본키
        PRIMARY KEY (
            mno -- 강사번호
        );

-- 강사 유니크 인덱스
CREATE UNIQUE INDEX UIX_tcher
    ON tcher ( -- 강사
        acc_no ASC, -- 통장번호
        bank ASC    -- 은행명
    );

-- 강의
CREATE TABLE lect (
    lno     INTEGER      NOT NULL COMMENT '강의번호', -- 강의번호
    titl    VARCHAR(255) NOT NULL COMMENT '강의명', -- 강의명
    sdt     DATE         NOT NULL COMMENT '시작일', -- 시작일
    edt     DATE         NOT NULL COMMENT '종료일', -- 종료일
    qnty    INTEGER      NOT NULL COMMENT '최대수용인원', -- 최대수용인원
    pric    INTEGER      NOT NULL COMMENT '강의료', -- 강의료
    nat_sup CHAR(1)      NOT NULL COMMENT '정부지원여부', -- 정부지원여부
    sup_typ VARCHAR(50)  NOT NULL COMMENT '지원타입', -- 지원타입
    dsct    TEXT         NOT NULL COMMENT '설명', -- 설명
    rno     INTEGER      NULL     COMMENT '강의실번호', -- 강의실번호
    mno     INTEGER      NULL     COMMENT '매니저번호' -- 매니저번호
)
COMMENT '강의';

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT PK_lect -- 강의 기본키
        PRIMARY KEY (
            lno -- 강의번호
        );

-- 강의 인덱스
CREATE INDEX IX_lect
    ON lect( -- 강의
        titl ASC -- 강의명
    );

ALTER TABLE lect
    MODIFY COLUMN lno INTEGER NOT NULL AUTO_INCREMENT COMMENT '강의번호';

-- 강의실
CREATE TABLE room (
    rno  INTEGER     NOT NULL COMMENT '강의실번호', -- 강의실번호
    loc  VARCHAR(50) NOT NULL COMMENT '지점명', -- 지점명
    name VARCHAR(50) NOT NULL COMMENT '강의실명', -- 강의실명
    qnty INTEGER     NOT NULL COMMENT '수용인원' -- 수용인원
)
COMMENT '강의실';

-- 강의실
ALTER TABLE room
    ADD CONSTRAINT PK_room -- 강의실 기본키
        PRIMARY KEY (
            rno -- 강의실번호
        );

-- 강의실 유니크 인덱스
CREATE UNIQUE INDEX UIX_room
    ON room ( -- 강의실
        loc ASC,  -- 지점명
        name ASC  -- 강의실명
    );

ALTER TABLE room
    MODIFY COLUMN rno INTEGER NOT NULL AUTO_INCREMENT COMMENT '강의실번호';

-- 매니저
CREATE TABLE mgr (
    mno  INTEGER     NOT NULL COMMENT '매니저번호', -- 매니저번호
    dept VARCHAR(50) NULL     COMMENT '부서', -- 부서
    posi VARCHAR(50) NULL     COMMENT '직위', -- 직위
    fax  VARCHAR(20) NULL     COMMENT '팩스' -- 팩스
)
COMMENT '매니저';

-- 매니저
ALTER TABLE mgr
    ADD CONSTRAINT PK_mgr -- 매니저 기본키
        PRIMARY KEY (
            mno -- 매니저번호
        );

-- 수강신청
CREATE TABLE lect_appl (
    lano INTEGER  NOT NULL COMMENT '수강신청번호', -- 수강신청번호
    lno  INTEGER  NOT NULL COMMENT '강의번호', -- 강의번호
    mno  INTEGER  NOT NULL COMMENT '수강생번호', -- 수강생번호
    rdt  DATETIME NOT NULL COMMENT '신청일', -- 신청일
    stat INTEGER  NULL     COMMENT '진행상태' -- 진행상태
)
COMMENT '수강신청';

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT PK_lect_appl -- 수강신청 기본키
        PRIMARY KEY (
            lano -- 수강신청번호
        );

-- 수강신청 유니크 인덱스
CREATE UNIQUE INDEX UIX_lect_appl
    ON lect_appl ( -- 수강신청
        lno ASC, -- 강의번호
        mno ASC  -- 수강생번호
    );

ALTER TABLE lect_appl
    MODIFY COLUMN lano INTEGER NOT NULL AUTO_INCREMENT COMMENT '수강신청번호';

-- 강의실사진
CREATE TABLE room_phot (
    rpno INTEGER      NOT NULL COMMENT '강의실사진번호', -- 강의실사진번호
    rno  INTEGER      NOT NULL COMMENT '강의실번호', -- 강의실번호
    phot VARCHAR(255) NOT NULL COMMENT '사진' -- 사진
)
COMMENT '강의실사진';

-- 강의실사진
ALTER TABLE room_phot
    ADD CONSTRAINT PK_room_phot -- 강의실사진 기본키
        PRIMARY KEY (
            rpno -- 강의실사진번호
        );

ALTER TABLE room_phot
    MODIFY COLUMN rpno INTEGER NOT NULL AUTO_INCREMENT COMMENT '강의실사진번호';

-- 멤버
CREATE TABLE memb (
    mno      INTEGER      NOT NULL COMMENT '멤버번호', -- 멤버번호
    name     VARCHAR(50)  NOT NULL COMMENT '이름', -- 이름
    tel      VARCHAR(20)  NOT NULL COMMENT '전화', -- 전화
    email    VARCHAR(40)  NOT NULL COMMENT '이메일', -- 이메일
    pwd      VARCHAR(100) NOT NULL COMMENT '암호', -- 암호
    phot     VARCHAR(255) NULL     COMMENT '사진', -- 사진
    ano      INTEGER      NULL     COMMENT '주소번호', -- 주소번호
    det_addr VARCHAR(255) NULL     COMMENT '상세주소', -- 상세주소
    finl_edu VARCHAR(50)  NULL     COMMENT '최종학력', -- 최종학력
    maj      VARCHAR(50)  NULL     COMMENT '전공', -- 전공
    sch_nm   VARCHAR(50)  NULL     COMMENT '최종학교' -- 최종학교
)
COMMENT '멤버';

-- 멤버
ALTER TABLE memb
    ADD CONSTRAINT PK_memb -- 멤버 기본키
        PRIMARY KEY (
            mno -- 멤버번호
        );

-- 멤버 유니크 인덱스
CREATE UNIQUE INDEX UIX_memb
    ON memb ( -- 멤버
        email ASC -- 이메일
    );

-- 멤버 인덱스
CREATE INDEX IX_memb
    ON memb( -- 멤버
        name ASC -- 이름
    );

ALTER TABLE memb
    MODIFY COLUMN mno INTEGER NOT NULL AUTO_INCREMENT COMMENT '멤버번호';

-- 주소
CREATE TABLE addr (
    ano      INTEGER      NOT NULL COMMENT '주소번호', -- 주소번호
    pst_no   VARCHAR(5)   NOT NULL COMMENT '우편번호', -- 우편번호
    bas_addr VARCHAR(255) NOT NULL COMMENT '기본주소' -- 기본주소
)
COMMENT '주소';

-- 주소
ALTER TABLE addr
    ADD CONSTRAINT PK_addr -- 주소 기본키
        PRIMARY KEY (
            ano -- 주소번호
        );

-- 주소 인덱스
CREATE INDEX IX_addr
    ON addr( -- 주소
        pst_no ASC -- 우편번호
    );

ALTER TABLE addr
    MODIFY COLUMN ano INTEGER NOT NULL AUTO_INCREMENT COMMENT '주소번호';

-- 강의배정
CREATE TABLE lect_tcher (
    lno INTEGER NOT NULL COMMENT '강의번호', -- 강의번호
    mno INTEGER NOT NULL COMMENT '강사번호' -- 강사번호
)
COMMENT '강의배정';

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT PK_lect_tcher -- 강의배정 기본키
        PRIMARY KEY (
            lno, -- 강의번호
            mno  -- 강사번호
        );

-- 수강생
ALTER TABLE stnt
    ADD CONSTRAINT FK_memb_TO_stnt -- 멤버 -> 수강생
        FOREIGN KEY (
            mno -- 수강생번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 강사
ALTER TABLE tcher
    ADD CONSTRAINT FK_memb_TO_tcher -- 멤버 -> 강사
        FOREIGN KEY (
            mno -- 강사번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT FK_mgr_TO_lect -- 매니저 -> 강의
        FOREIGN KEY (
            mno -- 매니저번호
        )
        REFERENCES mgr ( -- 매니저
            mno -- 매니저번호
        );

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT FK_room_TO_lect -- 강의실 -> 강의
        FOREIGN KEY (
            rno -- 강의실번호
        )
        REFERENCES room ( -- 강의실
            rno -- 강의실번호
        );

-- 매니저
ALTER TABLE mgr
    ADD CONSTRAINT FK_memb_TO_mgr -- 멤버 -> 매니저
        FOREIGN KEY (
            mno -- 매니저번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT FK_stnt_TO_lect_appl -- 수강생 -> 수강신청
        FOREIGN KEY (
            mno -- 수강생번호
        )
        REFERENCES stnt ( -- 수강생
            mno -- 수강생번호
        );

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT FK_lect_TO_lect_appl -- 강의 -> 수강신청
        FOREIGN KEY (
            lno -- 강의번호
        )
        REFERENCES lect ( -- 강의
            lno -- 강의번호
        );

-- 강의실사진
ALTER TABLE room_phot
    ADD CONSTRAINT FK_room_TO_room_phot -- 강의실 -> 강의실사진
        FOREIGN KEY (
            rno -- 강의실번호
        )
        REFERENCES room ( -- 강의실
            rno -- 강의실번호
        );

-- 멤버
ALTER TABLE memb
    ADD CONSTRAINT FK_addr_TO_memb -- 주소 -> 멤버
        FOREIGN KEY (
            ano -- 주소번호
        )
        REFERENCES addr ( -- 주소
            ano -- 주소번호
        );

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT FK_tcher_TO_lect_tcher -- 강사 -> 강의배정
        FOREIGN KEY (
            mno -- 강사번호
        )
        REFERENCES tcher ( -- 강사
            mno -- 강사번호
        );

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT FK_lect_TO_lect_tcher -- 강의 -> 강의배정
        FOREIGN KEY (
            lno -- 강의번호
        )
        REFERENCES lect ( -- 강의
            lno -- 강의번호
        );

 

 

 

eomcs-docs\sql\Exam06.sql

 

 

-- # select 테스트 용 데이터 준비

/* 강의실 데이터 준비 */
insert into room(rno, loc, name, qnty) values(1, '강남', '501', 30);
insert into room(rno, loc, name, qnty) values(2, '강남', '502', 30);
insert into room(rno, loc, name, qnty) values(3, '강남', '503', 30);
insert into room(rno, loc, name, qnty) values(4, '종로', '301', 30);
insert into room(rno, loc, name, qnty) values(5, '종로', '302', 30);
insert into room(rno, loc, name, qnty) values(6, '종로', '303', 30);
insert into room(rno, loc, name, qnty) values(7, '서초', '301', 30);
insert into room(rno, loc, name, qnty) values(8, '서초', '302', 30);
insert into room(rno, loc, name, qnty) values(9, '서초', '501', 30);
insert into room(rno, loc, name, qnty) values(10, '서초', '601', 30);

/* 학생 데이터 입력 */
insert into memb(mno, name, tel, email, pwd)
values(100, 's100', '111-1111', 's100@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(101, 's101', '111-1111', 's101@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(102, 's102', '111-1111', 's102@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(103, 's103', '111-1111', 's103@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(104, 's104', '111-1111', 's104@', sha2('1111', 224));

insert into stnt(mno, work, acc_no, bank)
values(100, 'N', '1000', '비트은행');
insert into stnt(mno, work, acc_no, bank)
values(101, 'Y', '1001', '비트은행');
insert into stnt(mno, work, acc_no, bank)
values(102, 'N', '1002', '캠프은행');
insert into stnt(mno, work, acc_no, bank)
values(103, 'Y', '1003', '우리은행');
insert into stnt(mno, work, acc_no, bank)
values(104, 'N', '1004', '국민은행');

/* 강사 데이터 입력 */
insert into memb(mno, name, tel, email, pwd)
values(200, 't200', '111-1111', 't200@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(201, 't201', '111-1111', 't201@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(202, 't202', '111-1111', 't202@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(203, 't203', '111-1111', 't203@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(204, 't204', '111-1111', 't204@', sha2('1111', 224));

insert into tcher(mno, hr_pay, acc_no, bank)
values(200, 10000, '2000', '신한은행');
insert into tcher(mno, hr_pay, acc_no, bank)
values(201, 20000, '2001', '농협');
insert into tcher(mno, hr_pay, acc_no, bank)
values(202, 15000, '2002', '기업은행');
insert into tcher(mno, hr_pay, acc_no, bank)
values(203, 25000, '2003', '우리은행');
insert into tcher(mno, hr_pay, acc_no, bank)
values(204, 30000, '2004', '국민은행');

/* 매니저 데이터 입력 */
insert into memb(mno, name, tel, email, pwd)
values(300, 'm300', '111-1111', 'm300@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(301, 'm301', '111-1111', 'm301@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(302, 'm302', '111-1111', 'm302@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(303, 'm303', '111-1111', 'm303@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(304, 'm304', '111-1111', 'm304@', sha2('1111', 224));

insert into mgr(mno, dept, posi)
values(300, '시설관리', '주임');
insert into mgr(mno, dept, posi)
values(301, '시설관리', '대리');
insert into mgr(mno, dept, posi)
values(302, '회계', '과장');
insert into mgr(mno, dept, posi)
values(303, '교육', '주임');
insert into mgr(mno, dept, posi)
values(304, '교육', '과장');

/* 200번 강사는 교육팀 과장이기도 하다.*/
insert into mgr(mno, dept, posi)
values(200, '교육', '과장');

/* 강의 데이터 입력 */
insert into lect(lno, titl, sdt, edt, qnty, pric, nat_sup, sup_typ, dsct)
values(1, '자바프로그래밍', '2018-1-1', '2018-5-30', 30, 100, 'Y', '미취업자', 'ok');
insert into lect(lno, titl, sdt, edt, qnty, pric, nat_sup, sup_typ, dsct)
values(2, 'IoT프로그래밍', '2018-3-1', '2018-7-30', 30, 200, 'Y', '미취업자', 'ok');
insert into lect(lno, titl, sdt, edt, qnty, pric, nat_sup, sup_typ, dsct)
values(3, '윈도우프로그래밍', '2018-5-1', '2018-10-30', 30, 300, 'Y', '노동부', 'ok');


/* 강의에 매니저 배정 */ 
update lect set mno=303 where lno=1;
update lect set mno=304 where lno=3;

/* 강의에 강의실 배정 */
update lect set rno=1 where lno=1;
update lect set rno=4 where lno=2;

/* 수강신청 데이터 입력 */
insert into lect_appl(lano, lno, mno, rdt) values(1, 1, 100, '2017-11-2');
insert into lect_appl(lano, lno, mno, rdt) values(2, 1, 101, '2017-11-3');
insert into lect_appl(lano, lno, mno, rdt) values(3, 1, 102, '2017-11-4');
insert into lect_appl(lano, lno, mno, rdt) values(4, 2, 104, '2017-12-6');
insert into lect_appl(lano, lno, mno, rdt) values(5, 2, 100, '2017-12-7');
insert into lect_appl(lano, lno, mno, rdt) values(6, 3, 101, '2017-10-8');
insert into lect_appl(lano, lno, mno, rdt) values(7, 3, 102, '2017-11-9');
insert into lect_appl(lano, lno, mno, rdt) values(8, 3, 104, '2017-11-11');

 

 

 

eomcs-docs\sql\Exam07_1.sql

 

 

distinct 와 all

 

/* 모든 데이터를 가져온다.*/
select all loc from room;

/* all은 생략할 수 있다*/
select loc from room;

/* 중복 값을 한 개만 추출할 때 distinct 를 붙인다.*/
select distinct loc from room;  

/* 컬럼이 2 개 이상일 때 
    그 컬럼들의 값이 중복될 경우만 한 개로 간주한다.*/
select distinct loc, name from room;

 

 

 

eomcs-docs\sql\Exam07_2.sql

 

 

order by

 

/* 기본 인덱스 컬럼을 기준으로 정렬한다.*/
select rno, loc, name
from room;

/* 이름의 오름 차순(ascending)으로 정렬하기 */
select rno, loc, name
from room
order by name asc;

/* asc는 생략 가능하다. */
select rno, loc, name
from room
order by name;

/* 이름의 내림 차순(desceding)으로 정렬하기 */
select rno, loc, name
from room
order by name desc;

/* 이름은 오름차순, 지점명도 오름차순으로 정렬하기*/
select rno, loc, name
from room
order by name asc, loc asc;

/* 이름은 오름차순, 지점명은 내림차순으로 정렬하기*/
select rno, loc, name
from room
order by name asc, loc desc;

/* 지점명은 오름차순으로, 이름은 오름차순  정렬하기*/
select rno, loc, name
from room
order by loc asc, name asc;

select rno, name
from room
order by loc asc, name asc;

-- 실행 순서: from -> where -> select -> order by
select 
  concat(name,'-',loc) as class_name
from 
  room
where 
  loc <> '강남'
order by 
  class_name; -- select 절에 있는 컬럼 또는 테이블 컬럼

select 
  concat(name,'-',loc) as class_name
from 
  room
where 
  loc <> '강남'
order by 
  loc desc; -- select 절에 있는 컬럼 또는 테이블 컬럼

 

 

 

eomcs-docs\sql\Exam07_3.sql

 

 

as 로 컬럼에 별명(라벨명) 붙이기

 

/* 출력 라벨명을 변경하기
   => 라벨명을 지정하지 않으면 컬럼명이 라벨명이 된다*/
select rno as room_no, loc as location, name
from room;

/* as 생략 가능 */
select rno room_no, loc location, name
from room;

/* 라벨명에 공백을 넣고 싶으면 '' 안에 작성한다.*/
select rno 'room no', loc location, name
from room;

/* 복잡한 형식으로 출력할 경우 라벨명(별명)을 부여한다.
    예) 강의실명(지점명)*/
select concat(name, '(', loc, ')')
from room;

select concat(name, '(', loc, ')') title
from room;

select count(*)
from room;

select count(*) cnt
from room;

select count(*) cnt
from room
where loc='서초';

/* count()를 호출할 때 컬럼 이름을 지정하면 
   해당 컬럼의 값이 null 이 아닌 데이터만 카운트한다. */
select count(mno) cnt
from lect;

 

 

 

eomcs-docs\sql\Exam07_4.sql

 

 

union 과 union all

 

/* select 결과 합치기
   union : 중복 값 자동 제거*/
select distinct bank from stnt
union
select distinct bank from tcher;

/* union all: 중복 값 제거 안함*/
select distinct bank from stnt
union all
select distinct bank from tcher;

/* 차집합
   mysql 은 차집합 문법을 지원하지 않는다.
   따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 한다.
*/
select distinct bank
from stnt
where not bank in (select distinct bank from tcher);

/* 교집합
   mysql 은 교집합 문법을 지원하지 않는다.
   따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 한다.
*/
select distinct bank
from stnt
where bank in (select distinct bank from tcher);

 

 

 

eomcs-docs\sql\Exam08_1.sql

 

 

조인

 

=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
=> 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인
3) JOIN ~ ON
4) OUTER JOIN

 

 

1) CROSS 조인(=Cartesian product)

두 테이블의 데이터를 1:1로 모두 연결한다.

create table board1 (
  bno int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file1 (
  fno int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);

alter table attach_file1
  add constraint attach_file1_fk foreign key (bno) references board1 (bno);

insert into board1 values(1, '제목1', '내용');
insert into board1 values(2, '제목2', '내용');
insert into board1 values(3, '제목3', '내용');

insert into attach_file1 values(101, 'a1.gif', 1);
insert into attach_file1 values(102, 'a2.gif', 1);
insert into attach_file1 values(103, 'c1.gif', 3);

select bno, title from board1;
select fno, filepath, bno from attach_file1;

-- bno 컬럼이 두 테이블에 모두 존재한다.
-- 따라서 어떤 테이블의 컬럼인지 지정하지 않으면 실행 오류!
select
  bno, title, content, fno, filepath
from board1 cross join attach_file1;

-- select 컬럼이 두 테이블에 모두 있을 경우,
-- 컬럼명 앞에 테이블명을 명시하여 구분하라!
select board1.bno, title, content, fno, filepath, attach_file1.bno
from board1 cross join attach_file1;

-- cross join 고전 문법
select board1.bno, title, content, fno, filepath, attach_file1.bno
from board1, attach_file1;

-- 컬럼명 앞에 테이블명을 붙이면 너무 길다.
-- 테이블에 별명을 부여하고 그 별명을 사용하여 컬럼을 지정하라.
select b.bno, title, content, fno, filepath, a.bno
from board1 as b cross join attach_file1 as a;

-- as는 생략 가능
select b.bno, title, content, fno, filepath, a.bno
from board1 b cross join attach_file1 a;

-- 고전 문법
select b.bno, title, content, fno, filepath, a.bno
from board1 b, attach_file1 a;

 

 

 

eomcs-docs\sql\Exam08_2.sql

 

 

2) NATURAL 조인

같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다.

select b.bno, title, content, fno, filepath, a.bno
from board1 b natural join attach_file1 a;

-- 고전 문법
select b.bno, title, content, fno, filepath, a.bno
from board1 b, attach_file1 a
where b.bno = a.bno;

/* natural join 의 문제점
 * 가. 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때 연결되지 못한다.
   나. 상관 없는 컬럼과 이름이 같을 때 잘못 연결된다.
   다. 같은 이름의 컬럼이 여러 개 있을 경우 잘못 연결된다.
        모든 컬럼의 값이 일치할 경우에만 연결되기 때문이다. */


-- 가. 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때:
--
create table board2 (
  no int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file2 (
  fno int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);

alter table attach_file2
  add constraint attach_file2_fk foreign key (bno) references board2 (no);

insert into board2 values(1, '제목1', '내용');
insert into board2 values(2, '제목2', '내용');
insert into board2 values(3, '제목3', '내용');

insert into attach_file2 values(101, 'a1.gif', 1);
insert into attach_file2 values(102, 'a2.gif', 1);
insert into attach_file2 values(103, 'c1.gif', 3);

-- natural join의 기준이 되는 같은 이름을 가진 컬럼이 양 테이블에 존재하지 않는다.
-- => cross join 처럼 실행된다.
select no, title, content, fno, filepath, bno
from board2 b natural join attach_file2 a;

-- 고전 문법 : 
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에
-- 실행 결과는 정상적으로 나온다.
select no, title, content, fno, filepath, bno
from board2 b, attach_file2 a
where b.no = a.bno;


-- 나. 같은 이름을 가진 컬럼이 있지만 서로 상관(PK와 FK 관계)이 없는 컬럼일 때:
--
create table board3 (
  no int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file3 (
  no int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);

alter table attach_file3
  add constraint attach_file3_fk foreign key (bno) references board3 (no);

insert into board3 values(1, '제목1', '내용');
insert into board3 values(2, '제목2', '내용');
insert into board3 values(3, '제목3', '내용');

insert into attach_file3 values(1, 'a1.gif', 1);
insert into attach_file3 values(2, 'a2.gif', 1);
insert into attach_file3 values(3, 'c1.gif', 3);

-- board3의 no와 attach_file3의 no는 PK/FK 관계가 아니다.
-- 그럼에도 불구하고 이름이 같기 때문에 이 컬럼을 기준으로 데이터를 연결한다.
select b.no, title, content, a.no, filepath, bno
from board3 b natural join attach_file3 a;

-- 고전 문법 : 
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에
-- 실행 결과는 정상적으로 나온다.
select b.no, title, content, a.no, filepath, bno
from board3 b, attach_file3 a
where b.no = a.bno;


-- 다. 같은 이름을 가진 컬럼이 여러개 있을 때:
--
create table board4 (
  bno int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file4 (
  fno int primary key auto_increment,
  title varchar(255) not null,
  bno int not null
);

alter table attach_file4
  add constraint attach_file4_fk foreign key (bno) references board4 (bno);

insert into board4 values(1, '제목1', '내용');
insert into board4 values(2, '제목2', '내용');
insert into board4 values(3, '제목3', '내용');

insert into attach_file4 values(1, 'a1.gif', 1);
insert into attach_file4 values(2, 'a2.gif', 1);
insert into attach_file4 values(3, 'c1.gif', 3);

-- board4와 attach_file4에 같은 이름을 가진 컬럼이 여러개 있다.
-- 해당 컬럼들의 값이 같을 때만 두 테이블의 데이터를 연결한다.
-- 따라서 실행 결과 데이터는 없을 것이다.
select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b natural join attach_file4 a;

-- 고전 문법 : 
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에
-- 실행 결과는 정상적으로 나온다.
select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b, attach_file4 a
where b.bno = a.bno;

 

 

 

eomcs-docs\sql\Exam08_3.sql

 

 

3) JOIN ~ USING

같은 이름을 가진 컬럼이 여러개 있을 경우 USING을 사용하여 컬럼을 명시할 수 있다.

select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b join attach_file4 a using (bno);

-- join ~ using 의 한계
-- => 두 테이블에 같은 이름의 컬럼이 없을 경우 연결하지 못한다.

create table board5 (
  no int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file5 (
  fno int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);

alter table attach_file5
  add constraint attach_file5_fk foreign key (bno) references board5 (no);

insert into board5 values(1, '제목1', '내용');
insert into board5 values(2, '제목2', '내용');
insert into board5 values(3, '제목3', '내용');

insert into attach_file5 values(1, 'a1.gif', 1);
insert into attach_file5 values(2, 'a2.gif', 1);
insert into attach_file5 values(3, 'c1.gif', 3);

-- 두 테이블의 데이터를 연결할 때 기준이 되는 컬럼의 이름이 같지 않다.
-- 이런 경우 using을 사용할 수 없다.
select no, title, content, fno, filepath, bno
from board5 b join attach_file5 a using (bno);

 

 

 

eomcs-docs\sql\Exam08_4.sql

 

 

4) JOIN ~ ON

조인 조건을 on에 명시할 수 있다.

select no, title, content, fno, filepath, bno
from board5 b join attach_file5 a on b.no = a.bno;

-- 조건에 일치하는 경우에만 두 테이블의 데이터를 연결한다.
-- 이런 조인을 'inner join' 이라 부른다.
-- SQL 문에서도 inner join 이라 기술할 수 있다.
-- 물론 inner를 생략할 수도 있다.
select no, title, content, fno, filepath, bno
from board5 b inner join attach_file5 a on b.no = a.bno;

/* [inner] join ~ on 의 문제점
   => 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만
        두 테이블의 데이터가 연결된다.
   => 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다.
   => 위 SQL의 실행 결과를 보라!
      첨부파일이 없는 2번 게시글은 결과에 포함되지 않는다.
*/

 

 

5) OUTER JOIN

조인 조건에 일치하는 데이터가 없더라도 두 테이블 중에서 한 테이블의 데이터를
결과로 포함시키는 명령이다.

--    문법:
--         select 컬럼명, 컬럼명, ...
--         from 테이블1 t1 [left/right] outer join 테이블2 t2 on t1.컬럼=t2.컬럼
--    left outer join => 왼쪽 테이블의 데이터는 반드시 포함시키라는 뜻이다.
--    right outer join => 오른쪽 테이블의 데이터를 반드시 포함시키라는 뜻이다.
--
select no, title, content, fno, filepath, bno
from board5 b left outer join attach_file5 a on b.no = a.bno
order by no desc;

-- 실무
-- 1) 여러 테이블을 조인하여 컬럼을 projection 할 때
--    각 컬럼이 어떤 테이블의 컬럼인지 명시한다.
-- 2) 컬럼을 나열할 때 한 줄에 한 컬럼을 나열한다.
select
  b.no,
  b.title,
  b.content,
  a.fno,
  a.filepath,
  a.bno
from board5 b
  left outer join attach_file5 a on b.no = a.bno
order by
  no desc;



-- [inner join의 문제점 예1]

-- 1) 전체 강의 목록
select lno, titl, rno, mno from lect;

-- 2) 전체 강의실 목록
select rno, loc, name from room;

-- 3) 강의 정보를 출력할 때 센터 이름과 강의실 이름도 함께 출력해보자
--    강의 테이블(lect)에서 강의명을 가져오고,
--    강의실 테이블(room)에서 지점명과 강의실명을 가져오자.
select
    l.lno,
    l.titl,
    l.rno,
    r.rno,
    r.loc,
    r.name
from lect l 
    inner join room r on l.rno=r.rno;
/* inner join의 문제는 위의 경우처럼
   강의실이 아직 지정되지 않은 강의의 경우 강의실 테이블의 데이터와 연결하지 못해
   결과로 출력되지 않는 문제가 있다. */


-- [inner join의 문제점 예2]
-- 1) 모든 강의장 이름을 출력하라.
--    단 강의장에 강의가 배정된 경우 그 강의 이름도 출력하라.
--
select
  r.rno,
  r.name,
  r.loc,
  l.titl
from room r 
    inner join lect l on r.rno = l.rno;

-- 위의 경우 처럼 만약 기준 컬럼의 값과 일치하는 데이터가 없어서
-- 다른 테이블의 데이터와 연결되지 않았다 하더라도
-- 결과로 뽑아내고 싶다면 outer join을 사용하라!
-- 즉 아직 강의실이 배정되지 않은 강의 데이터도 출력하고 싶을 때
-- 출력하고 싶은 테이블을 바깥쪽 테이블로 지정하라!
--
select
    l.lno,
    l.titl,
    r.rno,
    r.loc,
    r.name
from lect l 
    left outer join room r on l.rno=r.rno;
-- 왼쪽 테이블인 lect를 기준으로 room 데이터를 연결한다.
-- 만약 lect와 일치하는 데이터가 room에 없더라도
-- lect 데이터를 출력한다!

select
    l.lno,
    l.titl,
    r.rno,
    r.loc,
    r.name
from lect l 
    right outer join room r on l.rno=r.rno;
-- 오른쪽 테이블인 lect를 기준으로 lect 데이터를 연결한다.
-- 만약 room과 일치하는 데이터가 lect에 없더라도
-- room 데이터를 출력한다!

 

 

 

eomcs-docs\sql\Exam08_5.sql

 

 

조인 연습

요구사항:
   모든 멤버의 번호와 이름을 출력하라!
   단 학생의 경우 재직여부도 출력하라!

-- 1) 모든 멤버 데이터 출력하기
select mno, name
from memb;

-- 2) 학생 데이터를 가져와서 연결하기
select mno, name, work
from memb natural join stnt;

-- 3) join ~ using으로 연결하기
select mno, name, work
from memb join stnt using(mno);

-- 4) 고전 문법으로 연결하기
select memb.mno, name, work
from memb, stnt
where memb.mno=stnt.mno;

-- 5) inner join ~ on 연결하기
select memb.mno, name, work
from memb inner join stnt on memb.mno=stnt.mno;

-- 6) inner 생략하기
select memb.mno, name, work
from memb join stnt on memb.mno=stnt.mno;

-- 7) 테이블에 별명 부여하기
select m.mno, name, work
from memb m join stnt s on m.mno=s.mno;

/*
안타깝게도 위의 SQL문은 학생 목록만 출력한다.
왜? 
- memb테이블의 데이터와 stnt 테이블의 데이터를
  연결할 때 mno가 같은 데이터만 연결하여 추출하기 때문이다.
해결책!
- 상대 테이블(stnt)에 연결할 대상(데이터)이 없더라도
  select에서 추출하는 방법 
*/
-- 8) outer join ~ on 으로 연결하기
select m.mno, name, work
from memb m left outer join stnt s on m.mno=s.mno;

 

 

 

eomcs-docs\sql\Exam08_6.sql

 

 

=> 다음의 결과가 출력될 수 있도록 수강 신청 데이터를 출력하시오!
     수강신청번호, 강의명, 학생명, 재직여부, 수강신청일, 강의실명, 매니저명, 직위

/* 1단계: 수강신청 데이터를 출력 */
select la.lano, la.lno, la.mno, la.rdt
from lect_appl la;

/* 2단계: 수강신청한 학생의 번호 대신 이름을 출력 */
select la.lano, la.lno, m.name, la.rdt
from lect_appl la
     inner join memb m on la.mno=m.mno;

/* 3단계: 수강 신청한 학생의 재직 여부 출력
 * => inner join 에서 inner는 생략 가능
 */
select la.lano, la.lno, m.name, s.work, la.rdt
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno;

/* 4단계: 수강신청한 강의 번호 대신 강의명을 출력 */
select la.lano, l.titl, m.name, s.work, la.rdt, l.rno
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno;

/* 5단계: 강의실 이름을 출력한다.
 * => 강의실 번호는 lect 테이블 데이터에 있다.
 * => 강의실 이름은 room 테이블 데이터에 있다.
 */
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, l.mno
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno;

/* 6단계: 매니저 이름을 출력
 * => 매니저 번호는 lect 테이블에 있다.
 * => 매니저 이름은 memb 테이블에 있다.
 */
select
  la.lano,
  l.titl,
  m.name member_name,
  s.work,
  la.rdt,
  r.name room_name,
  m2.name manager_name
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno
        left outer join memb m2 on l.mno=m2.mno;

/* 7단계: 매니저의 직위 출력
 * => 매니저 번호는 lect 테이블 있다.
 * => 매니저 직위는 mgr 테이블에 있다.
 */
select
  la.lano,
  l.titl,
  m.name snm,
  s.work,
  la.rdt,
  r.name rnm,
  m2.name mnm,
  mr.posi
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno
        left outer join memb m2 on l.mno=m2.mno
        left outer join mgr mr on l.mno=mr.mno;

 

 

 

eomcs-docs\sql\Exam09_1.sql

 

 

서브 쿼리

 

=> 쿼리문 안에 쿼리문을 실행하는 기법
=> 성능 문제를 생각하면서 사용해야 한다.

/* join이용하여 데이터를 추출한 방법 */
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, m2.name, mr.posi
from lect_appl la
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno
        inner join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno
        left outer join memb m2 on l.mno=m2.mno
        left outer join mgr mr on l.mno=mr.mno;

/* select 절에 서브쿼리 사용하기 */

/* 수강신청 데이터를 출력 */
select
    la.lano,
    la.lno,
    la.mno,
    la.rdt
from lect_appl la;

/* => 1단계: 수강신청 데이터를 출력 */
select
  la.lano,
  la.lno,
  la.mno,
  date_format(la.rdt, '%m/%d/%Y') reg_dt
from lect_appl la;

/* => 2단계 : 서브 쿼리를 이용하여 강의명을 가져오기
   - 단, 컬럼 자리에 사용할 때는 결과 값이 한 개여야 한다.
   - 결과 값이 여러 개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류이다.
   - 또한 컬럼 개수도 한 개여야 한다.
*/
select
    la.lano,
    (select titl from lect where lno=la.lno) as lect_title,
    la.mno,
    la.rdt
from lect_appl la;

/* => 3단계 : 서브 쿼리를 이용하여 학생명을 가져오기 */
select
    la.lano,
    (select titl from lect where lno=la.lno) as lect_title,
    (select name from memb where mno=la.mno) as stud_name,
    la.rdt
from lect_appl la;

 

 

from 절에 서브쿼리 사용하기

/* 0단계 : 강의 정보를 가져온다. */
select
    l.lno,
    l.titl,
    l.rno,
    l.mno
from lect l;

/* 1단계 : 강의 상세 정보를 가져오는 select를 준비한다.
    => 서브 쿼리를 이용하여 강의실 이름과 매니저 이름, 직위 정보를 가져오기 */
select
    l.lno,
    l.titl,
    (select name from room where rno=l.rno) as room_name,
    (select name from memb where mno=l.mno) as manager_name,
    (select posi from mgr where mno=l.mno) as manager_posi
from lect l;

/* 2단계: 위에서 준비한 select 결과를 가상 테이블로 사용하여
             기존의 lect_appl 테이블과 조인한다.*/
select
    la.lano,
    /*(select titl from lect where lno=la.lno) as lect_title,*/
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    lec.manager_name,
    lec.manager_posi
from lect_appl la
    join (select
                l.lno,
                l.titl,
                (select name from room where rno=l.rno) as room_name,
                (select name from memb where mno=l.mno) as manager_name,
                (select posi from mgr where mno=l.mno) as manager_posi
            from lect l) as lec on la.lno=lec.lno;

/* lect_appl 테이블 대신에 서브 쿼리의 결과를 테이블로 사용할 수 있다. */
select
    la2.lano,
    la2.rdt,
    la2.sname,
    la2.work,
    l2.titl,
    l2.rname,
    l2.mname,
    l2.posi
from (
        select
            la.lano,
            la.lno,
            la.rdt,
            m.name sname,
            s.work
        from lect_appl la
            inner join memb m on la.mno=m.mno
            inner join stnt s on la.mno=s.mno) la2
     inner join (
        select
            l.lno,
            l.titl,
            r.name rname,
            m.name mname,
            mr.posi
        from lect l
            left outer join room r on l.rno=r.rno
            left outer join memb m on l.mno=m.mno
            left outer join mgr mr on l.mno=mr.mno
     ) l2 on la2.lno=l2.lno;

/* from 절에서 반복적으로 사용하는 서브 쿼리가 있다면,
 * 차라리 가상 테이블인 view로 정의해놓고 사용하는 것이 편하다.
 */
create view lect2 as
select
    l.lno,
    l.titl,
    (select name from room where rno=l.rno) as room_name,
    l.mno as manager_no,
    (select name from memb where mno=l.mno) as manager_name,
    (select posi from mgr where mno=l.mno) as manager_posi
from lect l;

/* 위의 질의문을 view를 사용하여 다시 작성해보자! */
select
    la.lano,
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    lec.manager_name,
    lec.manager_posi
from lect_appl la
    join lect2 lec on la.lno=lec.lno;

 

 

where 절에 서브쿼리 사용하기

/* 과장 또는 대리 매니저가 담당하고 있는 수강 신청만 추출하기 */
select
    la.lano,
    /* (select titl from lect where lno=la.lno) as lect_title, */
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    /* lec.manager_no, */
    lec.manager_name,
    lec.manager_posi
from lect_appl la
    join lect2 as lec on la.lno=lec.lno
where
    lec.manager_no in (select mno from mgr where posi in ('과장', '주임'));

 

 

서브쿼리 예

-- 서브쿼리 예1 : select 절에 서브쿼리를 둘 때 + where 절에 서브쿼리를 둘 때
select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) student_name,
  (select work from stnt where mno=la.mno) student_work,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date,
  ifnull((select name from room where rno=(select rno from lect where lno=la.lno)), '') room_name,
  ifnull((select name from memb where mno=(select mno from lect where lno=la.lno)), '') mgr_name,
  ifnull((select posi from mgr where mno=(select mno from lect where lno=la.lno)), '') mgr_posi
from lect_appl la;


-- 서브쿼리 예2 : from 절에 서브쿼리를 둘 때
-- 1) 강의 정보
select
  l.lno lect_no,
  l.titl lect_title,
  ifnull((select name from room where rno=l.rno), '') room_name,
  ifnull((select name from memb where mno=l.mno), '') mgr_name,
  ifnull((select posi from mgr where mno=l.mno), '') mgr_posi
from
  lect l;

-- 2) 수강생 정보
select
  s.mno std_no,
  (select name from memb where mno=s.mno) std_name,
  s.work std_work
from
  stnt s;

-- 3) 수강신청 정보
select
  la.lano,
  la.lno,
  la.mno,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date
from lect_appl la;

-- 4) 수강신청 정보 + 강의 정보 + 수강생 정보
select
  la.lano,
  le.lect_no,
  le.lect_title,
  le.room_name,
  le.mgr_name,
  le.mgr_posi,
  st.std_no,
  st.std_name,
  st.std_work,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date
from lect_appl la
  inner join (
      select
        l.lno lect_no,
        l.titl lect_title,
        ifnull((select name from room where rno=l.rno), '') room_name,
        ifnull((select name from memb where mno=l.mno), '') mgr_name,
        ifnull((select posi from mgr where mno=l.mno), '') mgr_posi
      from
        lect l
    ) le on la.lno=le.lect_no
  inner join (
    select
      s.mno std_no,
      (select name from memb where mno=s.mno) std_name,
      s.work std_work
    from
      stnt s
    ) st on la.mno=st.std_no;

 

 

 

eomcs-docs\sql\Exam10_1.sql

 

 

데이터를 특정 컬럼을 기준으로 그룹으로 묶어 질의하기

 

=> group by ~ having ~

/* 각 지점별 강의실 수 구하기*/
-- 1단계: 강의실 목록 구하기
select
    r.rno,
    r.loc,
    r.name
from
    room r;

-- 2단계: 지점정보를 저장한 컬럼을 기준으로 그룹으로 묶는다.
select
    r.rno, -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
    r.loc,
    r.name -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
from
    room r
group by
    r.loc;

-- 3단계: group by를 통해 데이터를 그룹으로 묶은 경우
--        개별 항목의 값을 나타내는 컬럼의 값은 의미가 없기 때문에 제거한다.
select
    r.loc
from
    room r
group by
    r.loc;

-- 4단계: 그룹으로 묶은 경우 그룹 관련 함수를 사용할 수 있다.
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc;

-- 5단계: group by의 결과에서 최종 결과를 선택할 조건을 지정하고 싶다면
--       having절을 사용한다.

-- 예1) having 절에서 집합 함수 사용
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc
having
    count(*) > 3; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 예2) having 절에서 group by 조건 컬럼 사용
select
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.loc = '강남'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 예3) having 절에서 select 절 컬럼 사용
select
    count(*) as cnt
from
    room r
group by
    r.loc
having
    cnt > 3; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

select
    r.name,
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.name = '302'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 오류) select 절에 선언되지 않은 컬럼을 지정할 수 없다.
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.name = '302'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 실행 순서: from --> where --> group by --> select --> having --> order by
-- => MySQL이 아닌 다른 DBMS에서 실행하여 실행 순서를 확인할 필요가 있음!
select
    'okok' as test,
    r.loc as location,
    count(*) as cnt
from
    room r
where 
    r.loc <> '강남'
group by
    r.loc
having
    cnt > 2 and test = 'okok'
order by 
    cnt desc;