230705 SQL 강의

-- 테이블 삭제
DROP TABLE AUTCST;

-- 테이블 생성
CREATE TABLE AUTCST
 ( CSTNO CHAR(10) NOT NULL,
   NAME VARCHAR2(20) NOT NULL,
   MODEL VARCHAR2(20) NOT NULL,
   YEAR NUMBER(4) NOT NULL,
   ADDRESS VARCHAR2(50) NOT NULL,
   TELNO VARCHAR2(13) NOT NULL,
   EMAIL VARCHAR2(20),
   CSTGB VARCHAR2(2),
   CONSTRAINT PK_AUTCST PRIMARY KEY(CSTNO) );

select * from autcst;

select * from autcst where cstgb is null;

delete from autcst;

INSERT ALL
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('97000B0001', '이건정', '소나타', 2015, '대전시 서구 둔산로', '010-2222-3331', 'aaa1@aaa.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('97000B0002', '이종삼', '아반떼', 2019, '대전시 중구 대흥로', '010-2222-3332', 'bbb1@bbb.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('99000B0003', '김장미', 'EF소나타', 2020, '대전시 동구 동신로', '010-2222-3333', 'ccc1@ccc.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('99000B0004', '최창호', '에쿠스', 2013, '부산시 유성구 유성로', '010-2222-3334', 'aaa2@aaa.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('15000B0005', '이춘화', '소나타2', 2019, '대전시 대덕구 대덕로', '010-2222-3335', 'bbb2@bbb.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('15000B0006', '전청화', '다이너스티', 2013, '대전시 동구 조흥로', '010-2222-3336', 'ccc2@ccc.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('99000B0007', '김길태', '엑셀', 2020, '서울시 서구 정림로', '010-2222-3337', 'aaa3@aaa.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('99000B0008', '장춘화', '엑센트', 2014, '대전시 중구 중앙로', '010-2222-3338', 'bbb3@bbb.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('20000B0009', '김미희', '소나타2', 2019, '대전시 동구 동서로', '010-2222-3339', 'ccc3@ccc.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('20000B0010', '이재창', '그랜저XG', 2023, '대전시 대덕구 대한로', '010-2222-3340', 'aaa4@aaa.com', null)
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('23000B0011', '백삼효', '아토스', 2008, '대전시 유성구 유로', '010-2222-3341', 'bbb4@bbb.com', '01')
      INTO AUTCST (CSTNO, NAME, MODEL, YEAR, ADDRESS, TELNO, EMAIL, CSTGB)
      VALUES ('23000B0012', '주용식', '티뷰론', 2010, '대전시 서구 서해로', '010-2222-3342', 'ccc4@ccc.com', '02')
    SELECT *
      FROM DUAL;

select * from autcst;

---

-- <문제 1>
-- 연식이 5년 이상된 자동차 고객을 조회하시오.

select to_char(add_months(sysdate,-60),'yyyy') -- 5년전 오늘, 2023년 기준 2018
  from dual;

select * 
  from autcst
 where year < (select to_char(add_months(sysdate,-60),'yyyy') from dual);
 
-- 모범답안
select CSTNO, NAME, MODEL, YEAR
  from autcst
 where year < 2018;

select * 
  from autcst
 where year+5 < 2023;

---

-- <문제 2>
-- 1999년에 등록한 고객을 조회하시오.

select *
  from autcst
 where substr(CSTNO, 1, 2) = '99';

---

-- <문제 3>
-- 총 고객 수를 조회하시오.

select count(CSTNO)
  from autcst;

select count(*)
  from autcst;

---

-- <문제 4>
-- 대전에 연고를 두고 있는 고객을 조회하세요.

select *
  from autcst
 where substr(ADDRESS, 1, 2) = '대전';

---

-- <문제 5>
-- VIP 고객을 조회하시오. (CSTGB='02')

select *
  from autcst
 where cstgb = '02';