eXERD로 ERD(Entity Relationship Diagram) 데이터 모델링을 진행하였다. 데이터베이스에서 ERD는 효율적으로 데이터 관계를 확인하기 위해서 필수로 수행해야 하는 작업이지만, 해커톤 기간임을 고려하여 최대한 간단하게 작성하였다.
해커톤에서 다른 개발자들의 데이터 모델링을 확인해 보니 eXERD보다는 ERDCloud를 사용하는 듯했다. 하나의 ERD에 많은 사람들이 접속할 수 있다고 해서 바꿔 보려고 한다.
데이터 모델링 이후에는 DBeaver로 DDL(데이터 정의어), DML(데이터 조작어)을 작성하였다. DDL은 데이터베이스의 구조를 정의하는 데 사용되며, 테이블 생성, 변경, 삭제 등의 작업을 포함한다. 그리고 DML은 데이터를 검색, 삽입, 수정, 삭제하는 작업을 다루며, 테이블의 내용을 조작하는 데 사용한다.
ERD 데이터 모델링
테이블 이름, 논리 이름, 물리 이름, 데이터 타입, 널 허용, 주석(값 예시)을 작성하였고, 기본키와 외래키를 표시하였다.
최대한 테이블에 NULL값을 없게 하려고 했지만 등록일처럼 나중에 데이터가 입력됨으로써 데이터가 완성되는 경우가 있어서 일부 컬럼에 NULL값을 허용하였다.
ANSI-SQL
더보기
DDL (Data Definition Language - 데이터 정의어)
데이터베이스 구조를 정의하거나 수정, 삭제하는 명령어 집합으로, 테이블, 뷰, 사용자, 인덱스 등 데이터베이스 오브젝트의 구조를 생성 및 관리한다.
데이터베이스 관리자, 담당자, 일부 프로그래머들이 이를 사용한다.
- CREATE: 새로운 데이터베이스 오브젝트 생성
- DROP: 데이터베이스 오브젝트 삭제
- ALTER: 데이터베이스 오브젝트 수정
DML (Data Manipulation Language - 데이터 조작어)
데이터를 추가, 수정, 삭제, 조회하는 명령어 집합으로, CRUD 작업을 수행한다.
이는 데이터베이스 관리자, 담당자뿐만 아니라 프로그래머들에게도 필수적으로 사용되는 명령어 집합이다.
- SELECT: 데이터 조회(읽기)
- INSERT: 데이터 추가(생성)
- UPDATE: 데이터 수정
- DELETE: 데이터 삭제
DCL (Data Control Language - 데이터 제어어)
계정 관리, 보안 관리, 트랜잭션 관리 등과 관련된 명령어 집합이다.
데이터베이스 관리자, 담당자, 프로그래머들이 사용한다.
- COMMIT: 트랜잭션의 변경 사항을 영구적으로 저장
- ROLLBACK: 트랜잭션의 변경 사항을 취소하고 이전 상태로 회귀
- GRANT: 데이터베이스 객체에 대한 권한을 부여
- REVOKE: 데이터베이스 객체에 대한 권한을 취소
DQL (Data Query Language - 데이터 조회어)
DML 중에서 SELECT 문을 따로 부르는 표현이다.
데이터베이스로부터 정보를 검색하고 추출하는 데 사용된다.
TCL (Transaction Control Language - 트랜잭션 제어어)
DCL 중에서 COMMIT 및 ROLLBACK 문을 따로 부르는 표현이다.
트랜잭션의 처리를 제어하고 변경 사항을 관리한다.
DDL
DELETE TABLE
/* DELETE TABLE */
DELETE FROM tblUserCoupon;
DELETE FROM tblCoupon;
DELETE FROM tblUserQuest;
DELETE FROM tblQuest;
DELETE FROM tblCategoryDetail;
DELETE FROM tblShop;
DELETE FROM tblUser;
DROP TABLE
/* DROP TABLE */
DROP TABLE tblUserCoupon;
DROP TABLE tblCoupon;
DROP TABLE tblUserQuest;
DROP TABLE tblQuest;
DROP TABLE tblCategoryDetail;
DROP TABLE tblShop;
DROP TABLE tblUser;
DROP SEQUENCE
/* DROP SEQUENCE */
DROP SEQUENCE user_coupon_seq;
DROP SEQUENCE coupon_seq;
DROP SEQUENCE user_quest_seq;
DROP SEQUENCE quest_seq;
DROP SEQUENCE shop_category_detail_seq;
DROP SEQUENCE shop_seq;
DROP SEQUENCE user_seq;
CREATE SEQUENCE
/* CREATE SEQUENCE */
CREATE SEQUENCE user_coupon_seq;
CREATE SEQUENCE coupon_seq;
CREATE SEQUENCE user_quest_seq;
CREATE SEQUENCE quest_seq;
CREATE SEQUENCE shop_category_detail_seq;
CREATE SEQUENCE shop_seq;
CREATE SEQUENCE user_seq;
CREATE TABLE
/* CREATE TABLE */
/* 회원 테이블 */
CREATE TABLE tblUser (
user_seq NUMBER PRIMARY KEY, /* 유저번호 */
name VARCHAR2(500) NOT NULL, /* 이름 */
id VARCHAR2(500) NOT NULL, /* 아이디 */
pw VARCHAR2(500) NOT NULL, /* 비밀번호 */
tel VARCHAR2(100) NOT NULL, /* 전화번호 */
address VARCHAR2(500) DEFAULT '미기재' NOT NULL, /* 주소 */
registration_date DATE DEFAULT SYSDATE NOT NULL, /* 가입일 */
lv CHAR(1) DEFAULT '1' NOT NULL, /* 회원등급 */
status CHAR(1) DEFAULT 'T' NOT NULL /* 활동상태 */
);
/* 가맹점 */
CREATE TABLE tblShop (
shop_seq NUMBER PRIMARY KEY, /* 가맹점번호 */
name VARCHAR2(500) NOT NULL, /* 가맹점명 */
corporate_registration_num VARCHAR2(500) NOT NULL, /* 사업자번호 */
tel VARCHAR2(500) NOT NULL, /* 전화번호 */
address VARCHAR2(2000) NOT NULL, /* 주소 */
lat VARCHAR2(500) NOT NULL, /* 위도 */
lng VARCHAR2(500) NOT NULL, /* 경도 */
registration_date DATE, /* 등록일 */
img VARCHAR2(500), /* 이미지 */
user_seq NUMBER NOT NULL, /* 회원번호 */
FOREIGN KEY (user_seq) REFERENCES tblUser(user_seq)
);
/* 가맹정세부카테고리 */
CREATE TABLE tblCategoryDetail (
shop_category_detail_seq NUMBER PRIMARY KEY, /* 세부카테고리번호 */
category_detail VARCHAR2(500) NOT NULL, /* 세부카테고리명 */
shop_seq NUMBER NOT NULL, /* 가맹점번호 */
FOREIGN KEY (shop_seq) REFERENCES tblShop(shop_seq)
);
/* 퀘스트 */
CREATE TABLE tblQuest (
quest_seq NUMBER PRIMARY KEY, /* 퀘스트번호 */
name VARCHAR2(500) NOT NULL, /* 퀘스트명 */
description VARCHAR2(2000) NOT NULL, /* 설명 */
reward VARCHAR2(500) NOT NULL, /* 보상 */
capacity NUMBER NOT NULL, /* 일일제한인원 */
start_date DATE NOT NULL, /* 퀘스트시작일 */
end_date DATE NOT NULL, /* 퀘스트종료일 */
registration_date DATE, /* 등록일 */
img VARCHAR2(500), /* 이미지 */
status CHAR(1) NOT NULL, /* 등록여부 */
shop_seq NUMBER NOT NULL, /* 가맹점번호 */
FOREIGN KEY (shop_seq) REFERENCES tblShop(shop_seq)
);
/* 회원수행퀘스트 */
CREATE TABLE tblUserQuest (
user_quest_seq NUMBER PRIMARY KEY, /* 수행퀘스트번호 */
completion_date DATE NOT NULL, /* 퀘스트수행일 */
status CHAR(1) NOT NULL, /* 수행여부 */
registration_date DATE, /* 등록일 */
quest_seq NUMBER NOT NULL, /* 퀘스트번호 */
user_seq NUMBER NOT NULL, /* 회원번호 */
FOREIGN KEY (quest_seq) REFERENCES tblQuest(quest_seq),
FOREIGN KEY (user_seq) REFERENCES tblUser(user_seq)
);
/* 쿠폰 */
CREATE TABLE tblCoupon (
coupon_seq NUMBER PRIMARY KEY, /* 쿠폰번호 */
name VARCHAR2(500) NOT NULL, /* 쿠폰명 */
description VARCHAR2(2000) NOT NULL, /* 설명 */
registration_date DATE NOT NULL, /* 등록일 */
img VARCHAR2(500), /* 이미지 */
shop_seq NUMBER NOT NULL, /* 가맹점번호 */
FOREIGN KEY (shop_seq) REFERENCES tblShop(shop_seq)
);
/* 회원보유쿠폰 */
CREATE TABLE tblUserCoupon (
user_coupon_seq NUMBER PRIMARY KEY, /* 보유쿠폰번호 */
start_date DATE NOT NULL, /* 사용가능시작일 */
end_date DATE NOT NULL, /* 사용가능종료일 */
registration_date DATE DEFAULT SYSDATE NOT NULL, /* 등록일 */
used_date DATE, /* 사용일 */
status CHAR(1) NOT NULL, /* 사용여부 */
coupon_seq NUMBER NOT NULL, /* 쿠폰번호 */
user_seq NUMBER NOT null, /* 회원번호 */
FOREIGN KEY (coupon_seq) REFERENCES tblCoupon(coupon_seq),
FOREIGN KEY (user_seq) REFERENCES tblUser(user_seq)
);
DML
/* DML */
/* 회원 테이블 */
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박하령', 'Park', 'park123!', '010-1234-5678', '서울시 노원구 공릉동 123번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '안희수', 'An', 'an123!', '010-5678-1234', '서울시 성북구 안암동 456번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '최익준', 'Choi', 'choi123!', '010-9876-5432', '광주시 북구 첨단과기로 789번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이승원', 'Lee', 'lee123!', '010-4321-8765', '수원시 권선구 호매실동 101번지', SYSDATE, '1', 'F');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '스팍스', 'Sparcs', 'sparcs123!', '010-3223-8725', '대전시 유성구 대학로 291번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '대재미', 'Daejaeme', 'daejaeme123!', '010-3423-8725', '대전시 서구 청사로 189번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박영미', 'yeong', 'yeongmi12*', '010-4567-8901', '대전시 서구 둔산동 101번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김재민', 'jaemi', 'jaemin12(', '010-0123-4567', '대전시 중구 대흥동 707번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김민지', 'minja', 'minji12)', '010-1234-5678', '대전시 유성구 신성동 123번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박민수', 'minsu', 'minsu12!', '010-2345-6789', '대전시 유성구 궁동 456번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이현주', 'hyunj', 'hyunjoo12@', '010-6789-0123', '대전시 서구 둔산동 303번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) values
(user_seq.nextVal, '강민지', 'minj', 'minju12#', '010-3456-7890', '대전시 유성구 장대동 789번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '최민석', 'mincse', 'minseok12$', '010-7890-1234', '대전시 대덕구 송촌동 404번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이지수', 'jisd5o', 'jisoo12%', '010-8901-2345', '대전시 대덕구 오정동 505번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김현석', 'hyu3ns', 'hyunseok1^', '010-9012-3456', '대전시 대덕구 비래동 606번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김지영', 'ji2yo', 'jiyoung1&', '010-5678-9012', '대전시 서구 도마동 202번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김민수', 'minsu', 'minsu12*', '010-4567-8901', '서울시 강서구 화곡동 101번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박서준', 'seoj', 'seojun12!', '010-5678-9012', '서울시 강서구 등촌동 202번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이성수', 'seong', 'seongsu12@', '010-1234-5678', '대전시 중구 문화동 808번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김지현', 'Ki1m', 'kim12#', '010-1111-1111', '대전시 유성구 관평동 123번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이현주', 'Lee41', 'lee12$', '010-2222-2222', '대전시 유성구 구암동 456번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박정우', 'Par31zk', 'park12%', '010-3333-3333', '대전시 서구 둔산동 789번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '최승우', 'Choi5', 'choi1^', '010-4444-4444', '대전시 중구 대흥동 101번지', SYSDATE, '1', 'F');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김민서', 'Kivnm', 'kim12&', '010-5555-5555', '대전시 유성구 노은동 202번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이하림', 'Leex', 'lee123#', '010-6666-6666', '대전시 대덕구 송촌동 303번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박시우', 'Park79', 'park12$', '010-7777-7777', '대전시 유성구 전민동 404번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '최민준', 'Choi', 'choi12@', '010-8888-8888', '대전시 서구 가수원동 505번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이서윤', 'Leexcv3', 'lee12#', '010-9999-9999', '대전시 대덕구 대화동 606번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김시우', 'Kimxc', 'kim12$', '010-1234-5678', '대전시 유성구 홍도동 707번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박지민', 'Park', 'park12%', '010-5678-1234', '대전시 유성구 송촌동 808번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김주원', 'Kimvc', 'kim12^', '010-9876-5432', '대전시 서구 대사동 909번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이서빈', 'Lee22', 'lee12&', '010-4321-8765', '대전시 중구 둔산동 1010번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박지훈', 'Park62', 'park12@', '010-3223-8725', '대전시 대덕구 가장동 1111번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이서진', 'Leeb09', 'lee12#', '010-3423-8725', '대전시 유성구 용운동 1212번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김다솜', 'Kim452', 'kim12$', '010-1111-1111', '대전시 대덕구 비래동 1313번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김시현', 'Ki25m', 'kim123!', '010-1111-1111', '대전시 중구 은행동 2828번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이서아', 'Leecxvn', 'lee123!', '010-2222-2222', '대전시 대덕구 비래동 2929번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박서준', 'Parkxc', 'park123!', '010-3333-3333', '대전시 유성구 송촌동 3030번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이지은', 'leejieun', 'Leejieun12*', '010-1111-2222', '서울시 강남구 역삼동 123번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박수진', 'sujinpark', 'SujinPark12@', '010-2222-3333', '서울시 서초구 반포동 456번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김태희', 'kimtaehee', 'KimTaehee12!', '010-3333-4444', '경기도 고양시 일산동구 장항동 789번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김민지', 'minjikim', 'MinjiKim12*', '010-4444-5555', '부산시 해운대구 우동 101번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '정우성', 'woosungjeong', 'Woosung123@', '010-5555-6666', '대구광역시 수성구 만촌동 202번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이지아', 'leejia', 'LeeJia12!', '010-6666-7777', '인천시 남동구 구월동 303번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이정훈', 'junghoonlee', 'Junghoon123*', '010-7777-8888', '광주광역시 남구 대인동 404번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '최준영', 'joonyoungchoi', 'Joonyoung12@', '010-8888-9999', '대전시 동구 가오동 505번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김현준', 'hyunjoonkim', 'Hyunjoon12!', '010-9999-0000', '울산광역시 남구 신정동 606번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이수진', 'soojinlee', 'SoojinLee12@', '010-0000-1111', '서울시 송파구 거여동 707번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박태민', 'taeminpark', 'TaeminPark12#', '010-1111-2222', '경기도 수원시 장안구 정자동 808번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김유진', 'youjinkim', 'YoujinKim12$', '010-2222-3333', '서울시 강북구 번동 909번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이정은', 'jeongeunlee', 'JeongeunLee12%', '010-3333-4444', '서울시 강서구 방화동 1010번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박승민', 'seungminpark', 'SeungminPark12^', '010-4444-5555', '서울시 마포구 서교동 1111번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김하윤', 'hayunkim', 'HayunKim12&', '010-5555-6666', '경기도 성남시 분당구 야탑동 1212번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이준우', 'junwoolee', 'JunwooLee12*', '010-6666-7777', '경기도 용인시 수지구 풍덕천동 1313번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박다영', 'dayoungpark', 'DayoungPark12#', '010-7777-8888', '서울시 송파구 잠실동 1414번지', SYSDATE, '2', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김건우', 'geonwookim', 'GeonwooKim12@', '010-8888-9999', '서울시 강동구 성내동 1515번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이유진', 'yoojinlee', 'YoojinLee12$', '010-9999-0000', '경기도 부천시 원미구 상동 1616번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박현우', 'hyunwoopark', 'HyunwooPark12%', '010-0000-1111', '경기도 안산시 단원구 와동 1717번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김민준', 'minjoonkim', 'MinjoonKim12&', '010-1111-2222', '서울시 강남구 역삼동 1818번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이주원', 'juwonlee', 'JuwonLee12*', '010-2222-3333', '서울시 서초구 반포동 1919번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김지원', 'jiwonkim', 'JiwonKim12@', '010-3333-4444', '경기도 고양시 일산동구 장항동 2020번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박지훈', 'jihunpark', 'JihunPark12#', '010-4444-5555', '경기도 부천시 소향동 2121번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이지우', 'jiwooLee', 'JiwooLee12$', '010-5555-6666', '인천광역시 부평구 삼산동 2222번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김지훈', 'jihunkim', 'JihunKim12%', '010-6666-7777', '서울시 강남구 신사동 2323번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박대전', 'daejunpark', 'DaejunPark12@', '010-3333-4444', '대전시 대덕구 송촌동 2828번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '최유성', 'yuseongchoi', 'YuseongChoi12*', '010-4444-5555', '대전시 유성구 신성동 2929번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이대전', 'daejeonlee', 'DaejeonLee12@', '010-5555-6666', '대전시 서구 가수원동 3030번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김나성', 'yuseongkim', 'YuseongKim12!', '010-6666-7777', '대전시 대덕구 송촌동 3131번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김영희', 'daejeon1', 'Daejeon12@', '010-1234-5678', '대전시 서구 가수원동 1010번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이철수', 'daejeon2', 'Daejeon12!', '010-2345-6789', '대전시 유성구 노은동 2020번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '박영진', 'daejeon3', 'Daejeon12#', '010-3456-7890', '대전시 유성구 장대동 3030번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '김미숙', 'daejeon4', 'Daejeon12$', '010-4567-8901', '대전시 중구 대흥동 4040번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '정민서', 'daejeon5', 'Daejeon12%', '010-5678-9012', '대전시 서구 둔산동 5050번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이도현', 'daejeon6', 'Daejeon12^', '010-6789-0123', '대전시 중구 문화동 6060번지', SYSDATE, '3', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이준석', 'juns22', 'Jun33*', '010-6666-7777', '용인시 수지구 풍덕천동 1313번지', SYSDATE, '1', 'T');
INSERT INTO tblUser (user_seq, name, id, pw, tel, address, registration_date, lv, status) VALUES
(user_seq.nextVal, '이상민', 'sm1999', 'sangmin99*', '010-6666-7777', '대전시 중구 문화동 1213번지', SYSDATE, '1', 'T');
SELECT * FROM tblUser;
COMMIT;
/* 가맹점 테이블 */
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, 'K STUDIO', '123-456-789', '010-1234-5678', '대전시 유성구 대덕대로 123번길 45', '36.350411', '127.384548', TO_DATE('2024-01-17', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/1.jpg', 3);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '국립중앙과학관 창의나래관', '456-789-012', '010-2345-6789', '대전시 서구 계룡로 78', '36.350879', '127.383895', TO_DATE('2024-01-17', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/2.jpg', 14);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '길드커피', '789-012-345', '010-3456-7890', '대전시 중구 보문로 45', '36.327217', '127.426311', TO_DATE('2024-01-18', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/3.jpg', 15);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '다온폴스튜디오', '012-345-678', '010-4567-8901', '대전시 동구 동서대로 67', '36.328729', '127.434181', TO_DATE('2024-01-18', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/4.jpg', 16);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 시에스타펜션', '345-678-901', '010-5678-9012', '대전시 유성구 장대로 1234', '36.374552', '127.386922', TO_DATE('2024-01-18', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/5.jpg', 18);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 엑스포 아쿠아리움', '678-901-234', '010-6789-0123', '대전시 서구 둔산로 789', '36.350626', '127.387477', TO_DATE('2024-01-19', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/6.jpg', 19);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 하늘정원 게스트하우스', '901-234-567', '010-7890-1234', '대전시 유성구 대학로 123', '36.365569', '127.362646', TO_DATE('2024-01-19', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/7.jpg', 21);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 시립박물관', '234-567-890', '010-8901-2345', '대전시 동구 대전로 456', '36.344121', '127.431502', TO_DATE('2024-01-20', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/8.jpg', 22);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 썬복싱클럽', '567-890-123', '010-9012-3456', '대전시 중구 은행로 789', '36.328123', '127.439889', TO_DATE('2024-01-20', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/9.jpg', 25);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 엑스포시민광장 스케이트장', '890-123-456', '010-0123-4567', '대전시 동구 선화로 901', '36.343634', '127.394028', TO_DATE('2024-01-21', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/10.jpg', 17);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '대전 예술의전당', '123-456-789', '010-1234-5678', '대전시 유성구 대덕대로 123번길 45', '36.350411', '127.384548', TO_DATE('2024-01-21', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/11.jpg', 30);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '도산', '456-789-012', '010-2345-6789', '대전시 서구 계룡로 78', '36.350879', '127.383895', TO_DATE('2024-01-22', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/12.jpg', 31);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '동화울수변공원', '789-012-345', '010-3456-7890', '대전시 중구 보문로 45', '36.327217', '127.426311', TO_DATE('2024-01-22', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/13.jpg', 32);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '등갈비여관', '012-345-678', '010-4567-8901', '대전시 동구 동서대로 67', '36.328729', '127.434181', TO_DATE('2024-01-22', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/14.jpg', 37);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '땡큐베리머치', '345-678-901', '010-5678-9012', '대전시 유성구 장대로 1234', '36.374552', '127.386922', TO_DATE('2024-01-23', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/15.jpeg', 38);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '마음을 담다', '678-901-234', '010-6789-0123', '대전시 서구 둔산로 789', '36.350626', '127.387477', TO_DATE('2024-01-23', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/16.jpg', 48);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '미락전골칼국수', '678-901-234', '010-6789-0123', '대전시 서구 둔산로 789', '36.350626', '127.387477', TO_DATE('2024-01-23', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/17.jpg', 52);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '빈센트 발', '234-567-890', '010-8901-2345', '대전시 동구 대전로 456', '36.344121', '127.431502', TO_DATE('2024-01-24', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/18.jpg', 53);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '예지네 펜션', '901-234-567', '010-7890-1234', '대전시 유성구 대학로 123', '36.365569', '127.362646', TO_DATE('2024-01-24', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/19.jpg', 55);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '원잇투메종탄방', '567-890-123', '010-9012-3456', '대전시 중구 은행로 789', '36.328123', '127.439889', TO_DATE('2024-01-25', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/20.jpg', 57);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '장태산들꽃펜션', '890-123-456', '010-0123-4567', '대전시 동구 선화로 901', '36.343634', '127.394028', TO_DATE('2024-01-26', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/21.jpg', 59);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '장태산의아침펜션', '123-456-789', '010-1234-5678', '대전시 유성구 대덕대로 123번길 45', '36.350411', '127.384548', TO_DATE('2024-01-27', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/22.jpg', 63);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '카라멜', '456-789-012', '010-2345-6789', '대전시 서구 계룡로 78', '36.350879', '127.383895', TO_DATE('2024-01-28', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/23.jpg', 66);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '카페모먼트', '789-012-345', '010-3456-7890', '대전시 중구 보문로 45', '36.327217', '127.426311', TO_DATE('2024-01-28', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/24.jpg', 67);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '킥핏클럽', '012-345-678', '010-4567-8901', '대전시 동구 동서대로 67', '36.328729', '127.434181', TO_DATE('2024-02-01', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/25.jpg', 68);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '퓨어앤필라테스', '345-678-901', '010-5678-9012', '대전시 유성구 장대로 1234', '36.374552', '127.386922', TO_DATE('2024-02-01', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/26.jpeg', 69);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '프렐류드', '678-901-234', '010-6789-0123', '대전시 서구 둔산로 789', '36.350626', '127.387477', TO_DATE('2024-02-02', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/27.jpeg', 70);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '한국조폐공사 화폐박물관', '901-234-567', '010-7890-1234', '대전시 유성구 대학로 123', '36.365569', '127.362646', TO_DATE('2024-02-03', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/28.jpg', 71);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '한꿈이 물놀이장', '234-567-890', '010-8901-2345', '대전시 동구 대전로 456', '36.344121', '127.431502', TO_DATE('2024-02-03', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/29.jpg', 73);
INSERT INTO tblShop (shop_seq, name, corporate_registration_num, tel, address, lat, lng, registration_date, img, user_seq)
VALUES (shop_seq.nextVal, '화랑', '567-890-123', '010-9012-3456', '대전시 중구 은행로 789', '36.328123', '127.439889', TO_DATE('2024-02-04', 'YYYY-MM-DD'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/30.jpg', 74);
SELECT * FROM tblShop;
COMMIT;
/* 가맹정세부카테고리 */
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실내', 1);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '전시', 2);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '카페', 3);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실내', 4);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '펜션', 5);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '전시', 6);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '펜션', 7);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '전시', 8);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실내', 9);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실외', 10);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '전시', 11);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '식당', 12);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실외', 13);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '식당', 14);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '카페', 15);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '카페', 16);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '식당', 17);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '전시', 18);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '펜션', 19);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '카페', 20);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '펜션', 21);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '펜션', 22);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '식당', 23);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '카페', 24);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실내', 25);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실내', 26);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '소품샵', 27);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '박물관', 28);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '실외', 29);
INSERT INTO tblCategoryDetail (shop_category_detail_seq, category_detail, shop_seq) VALUES (shop_category_detail_seq.nextVal, '식당', 30);
SELECT * FROM tblCategoryDetail;
COMMIT;
/* 퀘스트 */
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '국립중앙과학관 퀘스트', '국립중앙과학관 창의나래관 전시관람 완료', '쿠폰', 500, '2024-02-01', '2024-02-29', '2024-01-23', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/2.jpg', 'T', 2);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '카페모먼트 퀘스트', '카페 모먼트에서 만원 이상 구매 완료', '쿠폰', 100, '2024-02-01', '2024-02-07', '2024-01-26', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/24.jpg', 'F', 24);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '길드커피 퀘스트', '길드커피에서 오천원 이상 구매 완료', '쿠폰', 250, '2024-02-07', '2024-02-14', '2024-01-26', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/3.jpg', 'F', 3);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '다온폴스튜디오 퀘스트', '다온폴스튜디오에서 2만원 이상 구매 완료', '쿠폰', 30, '2024-02-01', '2024-03-01', '2024-01-26', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/4.jpg', 'F', 4);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '빈센트 발 퀘스트', '빈센트 발 : The Art of Shadow(대전) 관람 완료', '쿠폰', 100, '2024-02-07', '2024-04-15', '2024-01-26', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/18.jpg', 'T', 18);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '다온풀스튜디오 퀘스트', '폴댄스 3개월 수업 완료', '쿠폰', 30, '2024-02-01', '2024-06-30', '2024-01-27', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/4.jpg', 'T', 4);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '화랑 퀘스트', '화랑에서 3만원 이상 결제 완료', '쿠폰', 50, '2024-02-03', '2024-02-17', '2024-01-27', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/30.jpg', 'T', 30);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '프렐류드 퀘스트', '프렐류드에서 2만원 이상 결제 완료', '쿠폰', 30, '2024-02-03', '2024-02-17', '2024-01-27', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/27.jpeg', 'T', 27);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '대전 엑스포시민광장 스케이트장 퀘스트', '엑스포시민광장 스케이트장 개장 이벤트 참여 완료', '쿠폰', 150, '2024-02-01', '2024-02-29', '2024-01-27', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/10.jpg', 'T', 10);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '한국조폐공사 화폐박물관 퀘스트', '총 4개의 상설전시관 관람 완료', '쿠폰', 500, '2024-02-03', '2024-03-04', '2024-01-28', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/28.jpg', 'T', 28);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '카페모먼트 퀘스트', '카페 모먼트에서 만원 이상 구매 완료', '쿠폰', 100, '2024-02-08', '2024-02-15', '2024-02-03', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/24.jpg', 'T', 24);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '길드커피 퀘스트', '길드커피에서 오천원 이상 구매 완료', '쿠폰', 300, '2024-02-15', '2024-02-29', '2024-02-11', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/3.jpg', 'T', 3);
INSERT INTO tblQuest (quest_seq, name, description, reward, capacity, start_date, end_date, registration_date, img, status, shop_seq)
VALUES (quest_seq.nextVal, '카페모먼트 퀘스트', '카페 모먼트에서 오천원 이상 구매 완료', '쿠폰', 250, '2024-02-16', '2024-02-29', '2024-02-13', 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/24.jpg', 'T', 24);
SELECT * FROM tblQuest;
COMMIT;
/* 회원수행퀘스트 */
/* 박하령 */
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-17', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1, 1);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-18', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2, 1);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-19', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3, 1);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-20', 'F', TO_TIMESTAMP('2024-02-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4, 1);
/* 안희수 */
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-16', 'T', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4, 2);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-17', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5, 2);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-18', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3, 2);
/* 최익준 */
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-18', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1, 3);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-19', 'T', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3, 3);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-20', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5, 3);
/* 이승원 */
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-17', 'T', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2, 4);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-18', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4, 4);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-19', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4, 4);
/* 대재미 */
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-16', 'T', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1, 5);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-17', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3, 5);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-18', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5, 5);
INSERT INTO tblUserQuest (user_quest_seq, completion_date, status, registration_date, quest_seq, user_seq)
VALUES (user_quest_seq.nextVal, '2024-02-18', 'F', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5, 5);
SELECT * FROM tblUserQuest;
COMMIT;
/* 쿠폰 */
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '카페모먼트 할인 쿠폰', '카페모먼트에서 사용 가능한 5% 할인 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/24.jpg', 2);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '카페모먼트 무료 쿠폰', '카페모먼트에서 사용 가능한 무료 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/24.jpg', 2);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '등갈비여관 할인 쿠폰', '등갈비여관에서 사용 가능한 10% 할인 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/14.jpg', 14);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '등갈비여관 무료 쿠폰', '등갈비여관에서 사용 가능한 무료 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/14.jpg', 14);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '미락전골칼국수 할인 쿠폰', '미락전골칼국수에서 사용 가능한 20% 할인 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/17.jpg', 17);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '미락전골칼국수 무료 쿠폰', '미락전골칼국수에서 사용 가능한 무료 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/17.jpg', 17);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '프렐류드 할인 쿠폰', '프렐류드에서 사용 가능한 5% 할인 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/27.jpeg', 27);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '프렐류드 무료 쿠폰', '프렐류드에서 사용 가능한 무료 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/27.jpeg', 27);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '퓨어앤필라테스 할인 쿠폰', '퓨어앤필라테스에서 사용 가능한 5% 할인 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/26.jpeg', 26);
INSERT INTO tblCoupon (coupon_seq, name, description, registration_date, img, shop_seq)
VALUES (coupon_seq.nextVal, '퓨어앤필라테스 무료 쿠폰', '퓨어앤필라테스에서 사용 가능한 무료 쿠폰입니다.', TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'https://d3ob3cint7tr3s.cloudfront.net/daejaeme/26.jpeg', 26);
SELECT * FROM tblCoupon
COMMIT;
/* 회원보유쿠폰 */
INSERT INTO tblUserCoupon (user_coupon_seq, start_date, end_date, registration_date, used_date, status, coupon_seq, user_seq)
VALUES (user_coupon_seq.nextval, TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), NULL, 'F', 1, 1);
INSERT INTO tblUserCoupon (user_coupon_seq, start_date, end_date, registration_date, used_date, status, coupon_seq, user_seq)
VALUES (user_coupon_seq.nextval, TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), NULL, 'F', 3, 1);
INSERT INTO tblUserCoupon (user_coupon_seq, start_date, end_date, registration_date, used_date, status, coupon_seq, user_seq)
VALUES (user_coupon_seq.nextval, TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), NULL, 'F', 4, 1);
INSERT INTO tblUserCoupon (user_coupon_seq, start_date, end_date, registration_date, used_date, status, coupon_seq, user_seq)
VALUES (user_coupon_seq.nextval, TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'T', 2, 1);
INSERT INTO tblUserCoupon (user_coupon_seq, start_date, end_date, registration_date, used_date, status, coupon_seq, user_seq)
VALUES (user_coupon_seq.nextval, TO_TIMESTAMP('2024-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), NULL, 'F', 3, 2);
SELECT * FROM tblUserCoupon;
COMMIT;
기타 테스트 쿼리 일부
/* 기타 테스트용 쿼리 */
SELECT *
FROM (
SELECT q.*, (SELECT COUNT(*) FROM tblUserQuest uq WHERE q.quest_seq = uq.quest_seq) AS participant_count
FROM tblQuest q
WHERE q.status != 'F'
AND q.start_date <= SYSDATE
AND q.end_date >= SYSDATE
ORDER BY participant_count DESC
)
WHERE ROWNUM <= 3;
SELECT *
FROM (
SELECT *
FROM tblQuest
WHERE status = 'T'
ORDER BY registration_date DESC
)
WHERE ROWNUM <= 3;
SELECT *
FROM (
SELECT a.*, ROWNUM rnum
FROM (
SELECT *
FROM tblUserCoupon
WHERE user_seq = 1
ORDER BY user_coupon_seq ASC
) a
WHERE ROWNUM <= 3
)
WHERE rnum >= 1;
SELECT *
FROM (
SELECT *
FROM tblUserQuest
WHERE user_seq = 1 AND status = 'F'
ORDER BY registration_date DESC
)
WHERE ROWNUM <= 1;
INSERT INTO tblUserCoupon (user_coupon_seq, start_date, end_date, registration_date, used_date, status, coupon_seq, user_seq)
VALUES (
user_coupon_seq.nextVal,
SYSDATE,
SYSDATE + INTERVAL '7' DAY,
SYSDATE,
NULL,
'F',
FLOOR(DBMS_RANDOM.VALUE(1, 10)), -- 1부터 9까지 랜덤한 쿠폰번호 생성
1
);
SELECT user_seq FROM tblUserQuest WHERE user_quest_seq = 5;