14주차 화 1121(데이터 분석, 연산자, 문제풀이)

별칭사용하기
별칭을 사용하는 이유 : 긴 단어를 줄여서 사용하는데 좋음
/*
SQL에서 별칭을 사용할 수 있는 곳
 - 컬럼명 대신 별칭 사용 : 예시) mem_id as id
 - 테이블명 대신 별칭 사용 : 예시) From member mem
*/

-- 회원 테이블 전체 조회하기
-- 컬럼명 : [mem_id, mem_pass, ...]
-- [{'a001','ab',...},{'b001','bc',...},{'...'},,,]
SELECT *
FROM member;

-- 회원의 마일리지 값이 10 이상인 데이터만 조회하기
-- 조회컬럼은 회원아이디, 회원이름, 마일리지
-- 별칭, id, mame, point
-- 사용할 테이블명 : member
-- 사용할 컬럼명 : mem_mileage, mem_id, mem_name
-- 조건이 무엇인지 : mem_mileage >= 10

1.

SELECT 
mem_mileage AS POINT,
      mem_id AS id,
      mem_name AS name
FROM member
WHERE mem_mileage >= 10;

2.
SELECT 
mem_mileage POINT,
      mem_id id,
      mem_name name
FROM member
WHERE mem_mileage >= 10;

3.
SELECT 
mem_mileage 'POINT',
      mem_id 'id',
      mem_name 'name'
FROM member
WHERE mem_mileage >= 10;

 

4.
SELECT 
mem_mileage 'mem POINT',
      mem_id 'id',
      mem_name 'name'
FROM member
WHERE mem_mileage >= 10;

<<<< 컬럼에 띄어쓰기가 가능하지만 추천하지 않는다. >>>>

 

다 같이 별칭으로 사용 가능한 문법이지만 첫번째

SELECT 
mem_mileage AS POINT,
      mem_id AS id,
      mem_name AS name
FROM member
WHERE mem_mileage >= 10;

이 방법을 추천한다.

 

SELECT 
mem_mileage,
      mem_id,
      mem_name
FROM member
WHERE mem_mileage >= 10
-- AND mem_name = '김은대'
ORDER BY mem_mileage DESC, 
mem_id ASC;
/*
<처리 순서>
1. select
2. from
3. 테이블 메모리 올리기
4. where 조건
5. and 조건 또는 or 조건
6. 조회할 컬럼
7. 정렬
*/

 

 

컴파일 순서에 따라 별칭사용하는 부분을 고려해야한다.

SELECT 
      mem_mileage AS POINT,
      mem_id AS id,
      mem_name AS name
FROM member
WHERE mem_mileage >= 10       <<< 컬럼별칭은 모르는 시기
ORDER BY point DESC;
                    ㄴ 숫자(1,2,3)을 적어 정렬을 할 수 있다.

                           + 1 = point / 2 = id / 3 = name 

 

[문제]
/*
회원 중에 76년 1 월 1일 이후에 태어난 회원을 조회해주세요
 - 단, 주민등록번호 앞자리 6자리를 이용
 - 조회컬럼 : 회원아이디, 이름, 주민번호 앞 6자리
 - 사용할 테이블명 : member
 - 사용할 컬럼명 : mem_id, mem_name, mem_regno1
 - 조건이 무엇인지 : mem_regno1 > 760101
*/
SELECT
mem_id,
mem_name,
mem_regno1
FROM member
WHERE mem_regno1 > '760101';

-- 비교연산자 : >, <, >=, <=, =, <>, ! 
-- 논리연산자 : And, Or
-- 산술연산자 : +, -, *, /

[문제]
-- 상품분류코드가 P201이고,
-- 상품판매가격이17만원인
-- 상품코드, 상품명, 상품분류코드, 상품판매가격 조회
-- 정렬은 상품명을 기준으로 오름차순
-- 사용할 테이블 : prod
-- 사용할 컬럼 : prod_id, prod_name, prod_lgu, prod_sael
-- 일반조건 : prod_sale = 17만, prod_lgu = 'P210'
-- 정렬조건 : prod_name = asc
SELECT
prod_id,
prod_name,
prod_lgu,
prod_sale
FROM prod
WHERE prod_sale = 170000
AND prod_lgu = 'P201'
ORDER BY prod_name ASC;

[문제]
-- 상품 판매가격이 15만원 또는 17만원 또는 33만원인 상품을 조회
-- 조회컬럼은 상품명, 판매가격, 
SELECT 
prod_name,
prod_sale
FROM prod
WHERE prod_sale = 150000 
OR prod_sale = 170000
OR prod_sale = 330000;

SELECT 
prod_name,
prod_sale
FROM prod
WHERE prod_sale IN (150000, 170000, 330000);

위와 같은 문제를 in을 사용하여 코드를 줄일 수 있다.

 

[문제]
-- 상품명에 삼성이라는 단어가 있는 데이터 조회
-- 조회컬럼, 상품명, 상품코드
-- like : 문자내 특정 단어 포함여부 검색
SELECT
prod_name,
prod_id
FROM prod
-- 상품명 중에 첫글자가 "삼"으로  시작하는 모든 것 찾기
-- WHERE prod_name LIKE '삼%';
-- 상품명 중에 ""으로 시작하는 모든 것 찾기 
-- WHERE prod_name LIKE '_성%';
-- 언더바는 글자 하나를 의미
-- 상품명 중에 마지막 단어가 '치'로 끝나는 모든 것 찾기
-- WHERE prod_name LIKE '%치';
-- 상품명 중에 '여름'이라는 단어가 포함된 모든 것 찾기
-- 여름이 앞, 가운데, 뒤 어디에 있든 나오게된다.
-- 가장많이 쓴다.
WHERE prod_name LIKE '%여름%';

 

[문제]
-- 회원의 거주 지역이 서울이고,
-- 마일리지가 1000 이상인 회원의
-- 아이디, 이름, 주소(앞), 마일리지 조회하기
SELECT
mem_id,
mem_name,
mem_add1,
mem_mileage
FROM member
WHERE mem_mileage > 1000
AND mem_add1 LIKE '%서울%';

 

-[문제]
-- 회원 중에 생일이 1975년도에 태어난 회원조회
-- 조회컬럼은 회원이름, 회원생일
SELECT
mem_name,
mem_bir
FROM member
WHERE mem_bir LIKE '1975%';

-- between A and B : A이상 B이하의 범위 조건

-- 날자 타입 또는 숫자 타입에 대한 범위 ㅇ녀산 조건에 사용됨
SELECT
mem_name,
mem_bir
FROM member
-- WHERE mem_bir BETWEEN '1975-01-01' AND '1975-12-31';
-- WHERE mem_bir BETWEEN '19750101' AND '19751231';
-- WHERE mem_bir BETWEEN '1975.01.01' AND '1975.12.31';
WHERE mem_bir BETWEEN '1975/01/01' AND '1975/12/31';

-- 날자 형식(포멧형식)
-- 0000-00-00, 0000.00.00, 0000/00/00, 00000000

   ㄴ 제일 많이 쓰임

 

[문제]
-- 회원 마일리지의 값이 10이상이고, 100000이하이며,
-- 회원의 성씨가 김씨인 회원들에 대한
-- 회원이름, 마일리지 조회하기
SELECT 
mem_name,
mem_mileage
FROM member
WHERE mem_name LIKE '김%'
And mem_mileage BETWEEN 10 AND 100000;

-- 회원의 출생년도(생일기준)가 1975년생이 아닌 회원에 대한
-- 회원이름, 회원생일 조회
-- Not In, Not Like, Not Between
-- 부정 포함되어 있지 않다면...
SELECT
mem_name,
mem_bir
FROM member
-- WHERE mem_bir Not BETWEEN '1975-01-01' AND '1975-12-31';
-- 결과 1975를 제외한 모든 것들
WHERE mem_bir NOT LIKE '1975%';

-- 회원이름, 회원주민번호(앞-뒤), 주소(앞-뒤)
-- 컬럼 데이터 합치기(병합) : concat(값1, 값2, 값3,...) 함수 사용
SELECT
mem_name,
CONCAT(mem_regno1, '-', mem_regno2) AS mem_regno,
CONCAT(mem_add1, ' ', mem_add2) AS mem_add
FROM member;

 

함수
-- 대소문자 변환
SELECT 
LOWER(mem_id) AS '소문자', UPPER(mem_id) AS '대문자'
FROM member;

-- 공백 제거하기
SELECT
'       왼쪽공백',
LTRIM('   왼쪽공백제거'),
'오른쪽공백    ',
RTRIM('오른쪽공백제거      '),
'    좌우공백   ',
TRIM('   좌우공백    ');

-- 특정 자릿수까지의 문자 추출하기
-- mysql or mariaDB 용 
SELECT
LEFT(prod_id, 4),
RIGHT(prod_id, 6)
FROM prod;


-- [문제]
-- P101 상품분류에 대해 새로운 상품을 등록하려고 합니다.
-- P101000006이현재 마지막 번호입니다.
-- 마지막 번호에 1 증가시키고자 합니다.
-- P101000007
SELECT
CONCAT(
LEFT('P101000006',4),
RIGHT('P101000007',6)
) AS new;

SELECT
concat(LEFT('P101000006', 9),
RIGHT('P101000006',1)+1);

-- 문자열 내 특정 위치값 추출하기
SELECT SUBSTRING('Java Program',7, 3),
SUBSTRING('Java Program',1, 3)

-- 표준 : In, Like, Between
-- 회사에서 만든 것 : concat, substring

 

[문제]
-- 상품명의 4번째 자리부터 2개의 문자가 '칼라'인 것에대한
-- 상품코드, 상품명 조회
SELECT
prod_id,
prod_name
FROM prod
WHERE substring(prod_name,4,2) = '칼라' ;

-- [문제]
-- 회원의 성씨가 김씨이고,
-- 지역이 서울 또는 대전에 거주하고,
-- 기념일에 걸혼이 포함되어 있는 회원 정보조회하기
-- 조회컬럼 : 회원이름, 지역(지역만), 기념일명
SELECT

FROM member
WHERE substring(mem_name, 1, 1) = '김'
AND substring(mem_add1, 1, 2) IN('서울', '대전')
AND mem_memorial LIKE '%결혼%';

[문제]
-- 아이디가 a001인 회원의 마지일리지 이상인 회원조회
-- 조회컬럼은 회원아이디, 회원마일리지
SELECT 
mem_mileage
FROM member

WHERE mem_id = 'a001';

-- subQuery(서브쿼리)
SELECT mem_id, mem_mileage
FROM member
WHERE mem_mileage >= (
SELECT 
mem_mileage
FROM member
WHERE mem_id = 'a001');

[문제]
-- 한번도 주문한적이 없는 회원을 조회해보자
-- 조회 컬럼 : 회원아이디, 회원이름
SELECT
mem_name,
mem_id
FROM member
-- In 함수를 이용해서 서브쿼리를 사용할 경우 규칙
-- 하나의 컬럼에 여러개의 값을 조회 할 수 있음
-- 단일 컬럼에 다중 행이라고 칭합니다.
WHERE mem_id NOT In (SELECT 
cart_member
FROM cart
);

SELECT
mem_name,
mem_id
FROM member
WHERE mem_id Not IN(SELECT
cart_member
FROM cart);


[문제]
-- 상품정보에 존재하지 않는 상품분류를 조회
-- 상품분류코드, 상품분류명 조회
SELECT
lprod_gu,
lprod_nm
FROM lprod
WHERE lprod_gu NOT IN(SELECT
prod_lgu
FROM prod);


[문제]
-- 주문번호, 주문자아이디, 주문자이름, 주문상품코드, 주문수량을 조회
-- 조회
-- select 뒤에 조회할 컬럼명 대신해서 서브쿼리 작성 규칙
-- 하나의 컬럼에 하나의 값만 조회 가능
-- 단일 컬럼에 단일 행
SELECT
*,
(SELECT
mem_name
FROM member
WHERE mem_id = cart_member
)AS mem_name
FROM cart;

[문제]
-- 상품명, 상품판매가격, 상품분류코드, 상품분류명 조회
SELECT
prod_name,
prod_sale,
prod_lgu,
(
select
lprod_nm
FROM lprod
WHERE lprod_gu = prod_lgu
) AS Lprod_nm
FROM prod;

[문제]
-- 주문번호, 주문상품코드, 주문수량 ,주문상품명, 상품분류명 조회
-- 단, 회원의 거주지역이 서울 또는 대전인 경우
SELECT 
               cart_no, cart_prod, cart_qty,
               (select
               prod_name
               FROM prod
               WHERE prod_id = cart_prod)
                AS prod_name,
                (select
                          lprod_nm
                FROM lprod
                WHERE lprod_gu = SUBSTRING(cart_prod, 1, 4))
                AS lprod_nm
FROM cart
where cart_member IN (
                                       SELECT mem_id
                                       FROM member
                                       WHERE mem_add1 LIKE '서울%' OR mem_add1 LIKE '대전%')
                                       -- where substirng(mem_add1, 1,2) In ('서울','대전')

ORDER BY cart_no ASC, cart_qty DESC;

 

[메인 문제]

-- 상품코드, 상품명, 상품판매가격, 거래처명 조회하기
-- 단, 회원의 마일리지 값이 100 이상이고, 
--     주문수량이 5 이상이고,
--     상품분류명에 "컴퓨터"가 포함되어 있는 경우
--     거래처 주소지 지역이 서울or대전or광주인 경우

 

 <<<< 내 코드 >>>>

SELECT
               prod_id, prod_name, prod_sale,
               (select
                           buyer_name
               FROM buyer
               WHERE buyer_id = Prod_buyer) AS buyer_name
FROM prod
WHERE prod_id IN (
SELECT 
               cart_prod
FROM cart
WHERE cart_qty >= 5
     AND substring(cart_prod,1,4) IN (
                                                              SELECT lprod_gu
                                                               FROM lprod
                                                               where lprod_nm LIKE '%컴퓨터%')
     AND cart_member IN (
                                           SELECT mem_id
                                            FROM member
                                            WHERE SUBSTRING(mem_add1, 1, 2) IN ('서울', '대전', '광주')
                                                  AND mem_mileage > 100)
);  

 

 <<<< 강사님 코드 >>>>

SELECT prod_id, prod_name, prod_sale,
              (SELECT buyer_name FROM buyer
               WHERE buyer_id = prod_buyer) AS buyer_name
FROM prod
WHERE prod_id IN(SELECT cart_prod FROM cart
                                  WHERE cart_member IN(
                                                -- 회원의 마일리지 값이 100이상
                                                SELECT mem_id FROM member
                                                 WHERE mem_mileage >= 100)
                                    -- 주문수량이 5 이상
                                    AND cart_qty >= 5)
-- 상품분류명에 "컴퓨터"가 포함
AND prod_lgu IN (SELECT lprod_gu FROM lprod
                                WHERE lprod_nm LIKE '%컴퓨터%')
-- 거래처 주소지 지역이 서울 or 대전 or 광주
AND prod_buyer IN (
        SELECT buyer_id FROM buyer
        WHERE SUBSTRING(buyer_add1, 1, 2) IN ('서울','대전','광주'));

 

 

 

 

복사 붙여넣기 했는데 간격 맞춘게 다 풀려서 복구하려다 포기함

＼(´◓Д◔`)／