/***
Oracle DB 테이블 생성
Oracle 19c 기준으로 작성되었습니다.
***/
/***
CUST(고객) 테이블 생성
STR 계정 접속
***/
CREATE TABLE STR.CUST
(
CUST_NO VARCHAR2(20) NOT NULL,
CUST_NM VARCHAR2(1000) NOT NULL,
JOIN_DY VARCHAR2(8) NULL,
PASSWD VARCHAR2(100) NOT NULL,
BRTH_DD VARCHAR2(8) NULL
)
TABLESPACE TS_STR_D;
CREATE UNIQUE INDEX STR.CUST_PK ON STR.CUST
(CUST_NO)
TABLESPACE TS_STR_I;
ALTER TABLE STR.CUST
ADD CONSTRAINT CUST_PK
PRIMARY KEY(CUST_NO);
COMMENT ON TABLE STR.CUST IS '고객';
COMMENT ON COLUMN STR.CUST.CUST_NO IS '고객번호';
COMMENT ON COLUMN STR.CUST.CUST_NM IS '고객명';
COMMENT ON COLUMN STR.CUST.JOIN_DY IS '가입일자';
COMMENT ON COLUMN STR.CUST.PASSWD IS '비밀번호';
COMMENT ON COLUMN STR.CUST.BRTH_DD IS '생년월일';
/***
CUST_DTL(고객상세) 테이블 생성
STR 계정 접속
***/
CREATE TABLE STR.CUST_DTL
(
CUST_NO VARCHAR2(20) NOT NULL,
MBL_NO VARCHAR2(13) NULL,
HOME_ADDR VARCHAR2(1000) NULL,
ZIP_CD VARCHAR2(10) NULL,
EMAIL VARCHAR2(200) NULL
)
TABLESPACE TS_STR_D;
CREATE UNIQUE INDEX STR.CUST_DTL_PK ON STR.CUST_DTL
(CUST_NO)
TABLESPACE TS_STR_I;
ALTER TABLE STR.CUST_DTL
ADD CONSTRAINT CUST_DTL_PK
PRIMARY KEY(CUST_NO);
COMMENT ON TABLE STR.CUST_DTL IS '고객상세';
COMMENT ON COLUMN STR.CUST_DTL.CUST_NO IS '고객번호';
COMMENT ON COLUMN STR.CUST_DTL.MBL_NO IS '휴대폰번호';
COMMENT ON COLUMN STR.CUST_DTL.HOME_ADDR IS '집주소';
COMMENT ON COLUMN STR.CUST_DTL.ZIP_CD IS '우편번호';
COMMENT ON COLUMN STR.CUST_DTL.EMAIL IS '이메일';
/***
테이블, 인덱스 상태 확인
STR 계정 접속
***/
SELECT
OBJECT_TYPE,
OBJECT_NAME,
CREATED
FROM USER_OBJECTS
ORDER BY 2,1;
/***
테이블 상태 확인
STR 계정 접속
***/
SELECT
A.TABLE_NAME,
B.COMMENTS AS "TABLE_COMMENT",
A.COLUMN_NAME,
C.COMMENTS AS "COLUMN_COMMENT",
A.COLUMN_ID
FROM
USER_TAB_COLUMNS A,
USER_TAB_COMMENTS B,
USER_COL_COMMENTS C
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME
ORDER BY A.TABLE_NAME, A.COLUMN_ID;
/***
CUST(고객) 데이터 입력
STR 계정 접속
***/
-- CUST(고객) 테이블 데이터 50건 입력
-- 데이터 특이사항 (김영일 3명, 김영칠 3명)
INSERT INTO STR.CUST VALUES('20221125JPYM00000001','김영일','20040101','flne5450','20000701');
INSERT INTO STR.CUST VALUES('20221125DROE00000002','김영이','20040101','npek0233','20000702');
INSERT INTO STR.CUST VALUES('20221125KJQN00000003','김영삼','20040305','zxet2771','20000703');
INSERT INTO STR.CUST VALUES('20221125WEGD00000004','김영사','20040305','tgli5670','20000704');
INSERT INTO STR.CUST VALUES('20221125AJKK00000005','김영오','20040405','iayw6265','20020705');
INSERT INTO STR.CUST VALUES('20221125LADR00000006','김영육','20040407','ohew6324','20020706');
INSERT INTO STR.CUST VALUES('20221125TKDN00000007','김영칠','20040507','jdsf1139','20020707');
INSERT INTO STR.CUST VALUES('20221125FGJP00000008','김영팔','20050207','pqmj2289','20020708');
INSERT INTO STR.CUST VALUES('20221125ZERM00000009','김영구','20050207','sgie6302','20020709');
INSERT INTO STR.CUST VALUES('20221125IOUS00000010','김일영','20050207','kyqd0483','20020710');
INSERT INTO STR.CUST VALUES('20221125FREG00000011','김일일','20051119','ljix4309','20020711');
INSERT INTO STR.CUST VALUES('20221125JXYA00000012','김일이','20060119','vzvi3915','20050712');
INSERT INTO STR.CUST VALUES('20221125ANQL00000013','김일삼','20060119','dzid2179','19620713');
INSERT INTO STR.CUST VALUES('20221125IKVD00000014','김일사','20060119','cmuf6339','19620714');
INSERT INTO STR.CUST VALUES('20221125ABAI00000015','김일오','20060119','zysq7590','19620715');
INSERT INTO STR.CUST VALUES('20221125YXTK00000016','김일육','20060119','ykwm7623','19620716');
INSERT INTO STR.CUST VALUES('20221125OHAF00000017','김일칠','20060225','qsne5403','19550717');
INSERT INTO STR.CUST VALUES('20221125SAVG00000018','김일팔','20060225','vzsa4413','19550718');
INSERT INTO STR.CUST VALUES('20221125APRV00000019','김일구','20090225','zums3447','19550719');
INSERT INTO STR.CUST VALUES('20221125XCRU00000020','김일영','20090225','xjvz8461','19550720');
INSERT INTO STR.CUST VALUES('20221125XUZN00000021','김이일','20090508','axov6969','20010801');
INSERT INTO STR.CUST VALUES('20221125RFSZ00000022','김이가','20090508','fitu9267','20010802');
INSERT INTO STR.CUST VALUES('20221125GSUH00000023','김이삼','20090508','pqnw5647','20010803');
INSERT INTO STR.CUST VALUES('20221125MNCU00000024','김이사','20090508','rviv2232','20010804');
INSERT INTO STR.CUST VALUES('20221125ZFMC00000025','김이오','20100217','omor3320','19910805');
INSERT INTO STR.CUST VALUES('20221125XUGB00000026','김이육','20100217','iwcm7473','19910806');
INSERT INTO STR.CUST VALUES('20221125CIGG00000027','김이칠','20100217','ultb6084','19910807');
INSERT INTO STR.CUST VALUES('20221125NNXY00000028','김이팔','20100217','ixwx3137','19910808');
INSERT INTO STR.CUST VALUES('20221125VZWP00000029','김이구','20100217','tpin3069','19830809');
INSERT INTO STR.CUST VALUES('20221125CLJU00000030','김삼영','20100217','olha1635','19830810');
INSERT INTO STR.CUST VALUES('20221125AVME00000031','김삼일','20120602','dyho5331','19880811');
INSERT INTO STR.CUST VALUES('20221125NYAA00000032','김영칠','20120602','jfly1680','19980812');
INSERT INTO STR.CUST VALUES('20221125UDXV00000033','김삼삼','20120602','ydeo9942','19980813');
INSERT INTO STR.CUST VALUES('20221125WYVA00000034','김삼사','20120602','fylt4787','19980814');
INSERT INTO STR.CUST VALUES('20221125CFTF00000035','김삼오','20120602','aoyd7537','19670815');
INSERT INTO STR.CUST VALUES('20221125SQXC00000036','김삼육','20120602','waaz3678','19670816');
INSERT INTO STR.CUST VALUES('20221125SQWO00000037','김삼칠','20220102','maat3026','19670817');
INSERT INTO STR.CUST VALUES('20221125ZMPZ00000038','김영일','20220102','iuzn5413','19670818');
INSERT INTO STR.CUST VALUES('20221125AYGK00000039','김삼구','20220102','mwzc8562','19670819');
INSERT INTO STR.CUST VALUES('20221125THBX00000040','김사영','20220102','tvgz0417','19750820');
INSERT INTO STR.CUST VALUES('20221125MDBK00000041','김사일','20220115','alvz3064','19750901');
INSERT INTO STR.CUST VALUES('20221125LTYY00000042','김사가','20220115','xuhp4988','19750902');
INSERT INTO STR.CUST VALUES('20221125FPUV00000043','김사삼','20220115','ssly9245','19750903');
INSERT INTO STR.CUST VALUES('20221125CHXS00000044','김사사','20220415','glzn5012','19750904');
INSERT INTO STR.CUST VALUES('20221125MJTM00000045','김사오','20220415','tlht5713','19700905');
INSERT INTO STR.CUST VALUES('20221125LQZU00000046','김사육','20220415','whtk7257','19700906');
INSERT INTO STR.CUST VALUES('20221125CEGQ00000047','김사칠','20220808','xamu3455','19960907');
INSERT INTO STR.CUST VALUES('20221125FWXP00000048','김사팔','20220808','uxub8838','19960908');
INSERT INTO STR.CUST VALUES('20221125JVPE00000049','김영칠','20220808','vnry4863','19960909');
INSERT INTO STR.CUST VALUES('20221125JWBX00000050','김영일','20220808','kdlb3873','19960910');
COMMIT;
/***
CUST_DTL(고객상세) 데이터 입력
STR 계정 접속
***/
-- CUST_DTL(고객상세) 테이블 데이터 50건 입력
-- 데이터 특이사항 NULL값 4건, Empty String값 4건
INSERT INTO STR.CUST_DTL VALUES('20221125JPYM00000001','015-0101-0101','서울시 강남구 압구정동','06000','uljbou@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125DROE00000002','015-0101-0102','서울시 강남구 압구정동','06000','oldbic@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125KJQN00000003','015-0101-0103','서울시 강남구 압구정동','06000','wbojex@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125WEGD00000004','015-0101-0104','서울시 강남구 압구정동','06000','yhilhj@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125AJKK00000005','015-0101-0105','서울시 강남구 압구정동','06000','tasorz@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125LADR00000006','015-0101-0106','서울시 강남구 청담동','06074','wizgkj@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125TKDN00000007','015-0101-0107','서울시 강남구 청담동','06074','zvrexd@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125FGJP00000008','015-0101-0108','서울시 강남구 청담동','06074','ribait@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125ZERM00000009','015-0101-0109','서울시 강남구 청담동','06074','zuhzaq@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125IOUS00000010','015-0101-0110','서울시 강남구 청담동','06074','gnlcqg@superemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125FREG00000011','015-0101-0111','서울시 강남구 도곡동','06294','tcoesv@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125JXYA00000012','015-0101-0112','서울시 강남구 도곡동','06294','gwuujt@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125ANQL00000013','015-0101-0113','서울시 강남구 도곡동','06294','mzsgxy@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125IKVD00000014','015-0101-0114','서울시 강남구 도곡동','06294','evmcrl@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125ABAI00000015','015-0101-0115','서울시 강남구 도곡동','06294','fnbvau@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125YXTK00000016','015-0101-0116','서울시 강남구 신사동','06027','vqfsez@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125OHAF00000017','015-0101-0117','서울시 강남구 신사동','06027','scwfrs@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125SAVG00000018','015-0101-0118','서울시 강남구 신사동','06027','fsjzna@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125APRV00000019','015-0101-0119','서울시 강남구 신사동','06027','dcearf@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125XCRU00000020','015-0101-0120','서울시 강남구 신사동','06027','czpqas@metajjemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125XUZN00000021','015-0101-0121','서울시 서초구 반포동','06547','pcnann@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125RFSZ00000022','015-0101-0122','서울시 서초구 반포동','06547','sebplp@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125GSUH00000023','015-0101-0123','서울시 서초구 반포동','06547','rknvcf@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125MNCU00000024','015-0101-0124','서울시 서초구 반포동','06547','miqfws@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125ZFMC00000025','015-0101-0125','서울시 서초구 반포동','06547','zxpvie@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125XUGB00000026','015-0101-0126','서울시 강남구 대치동','06284','shfjff@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125CIGG00000027','015-0101-0127','서울시 강남구 대치동','06284','gyxloz@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125NNXY00000028','015-0101-0128','서울시 강남구 대치동','06284','qunrwt@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125VZWP00000029','015-0101-0129','서울시 강남구 대치동','06284','koigww@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125CLJU00000030','015-0101-0130','서울시 강남구 대치동','06284','koacww@gonnemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125AVME00000031','015-0101-0131','부산광역시 해운대구 중동','48099','rkmhzr@narademail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125NYAA00000032','015-0101-0132','부산광역시 해운대구 중동','48099','vxikgq@narademail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125UDXV00000033','015-0101-0133','부산광역시 해운대구 중동','48099','ecddxs@narademail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125WYVA00000034','015-0101-0134','부산광역시 해운대구 중동','48099','mjmxcf@narademail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125CFTF00000035','015-0101-0135','부산광역시 해운대구 중동','48099','bdwfmb@narademail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125SQXC00000036','015-0101-0136','부산광역시 해운대구 우동','48092','byjkso@narademail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125SQWO00000037','015-0101-0137','부산광역시 해운대구 우동','48092',' ');
INSERT INTO STR.CUST_DTL VALUES('20221125ZMPZ00000038','015-0101-0138','부산광역시 해운대구 우동','48092',' ');
INSERT INTO STR.CUST_DTL VALUES('20221125AYGK00000039','015-0101-0139','부산광역시 해운대구 우동','48092','');
INSERT INTO STR.CUST_DTL VALUES('20221125THBX00000040','015-0101-0140','부산광역시 해운대구 우동','48092','');
INSERT INTO STR.CUST_DTL VALUES('20221125MDBK00000041','015-0101-0141','부산광역시 부산진구 부전동','47300','twvruy@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125LTYY00000042','015-0101-0142','부산광역시 부산진구 부전동','47300','fvovzz@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125FPUV00000043','015-0101-0143','','47300','vwyein@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125CHXS00000044','015-0101-0144','','47300','yqavsm@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125MJTM00000045','015-0101-0145','부산광역시 부산진구 부전동','47300','adrwte@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125LQZU00000046','015-0101-0146',' ','48230','zkionw@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125CEGQ00000047','015-0101-0147',' ','48230','cpkghk@qoodaemail.com');
INSERT INTO STR.CUST_DTL VALUES('20221125FWXP00000048','015-0101-0148','부산광역시 수영구 수영동','48230','메일주소');
INSERT INTO STR.CUST_DTL VALUES('20221125JVPE00000049','999-9999-9999','몰라','48230','난아싸라@이메일이없어요');
INSERT INTO STR.CUST_DTL VALUES('20221125JWBX00000050','000-0000-0000','님 마음속','48230','이메일');
COMMIT;
/***
데이터 입력 에러 해결 방법(ORA-01950)
SYS 계정 접속
***/
-- ORA-01950: 테이블스페이스 'TS_STR_D'에 대한 권한이 없습니다.
-- TS_STR_D 테이블스페이스 권한 생성
GRANT UNLIMITED TABLESPACE TO STR;
-- 권한 확인
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE='STR';
-- TS_STR_D 테이블스페이스 권한 원복
REVOKE UNLIMITED TABLESPACE FROM STR;
-- 권한 확인
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE='STR';
/***
SQL 실행
STR 계정 접속
***/
SELECT
A.CUST_NO,
A.CUST_NM,
A.BRTH_DD,
B.MBL_NO,
B.HOME_ADDR,
B.EMAIL
FROM
STR.CUST A,
STR.CUST_DTL B
WHERE A.CUST_NO = B.CUST_NO
AND A.CUST_NM = '김영구';
--AND A.CUST_NM IN ('김영구','김이오','김일구');
/***
CUST(고객), CUST_DTL(고객상세) 데이터 삭제
STR 계정 접속
***/
TRUNCATE TABLE STR.CUST;
TRUNCATE TABLE STR.CUST_DTL;
/***
CUST(고객), CUST_DTL(고객상세) 테이블 삭제
STR 계정 접속
***/
DROP TABLE STR.CUST;
DROP TABLE STR.CUST_DTL;
