20230214 [SQL] INSTR()함수, 서브쿼리, 조건식 , DECODE() , NULL체크 함수

숙제 검사

-- 가입된 회원의 총 인원수를 출력하시오
SELECT COUNT(MIDX) AS CNT
FROM   MEMBER1230
WHERE  delYN='N';

-- 전주에 사는 회원들의 평균나이를 출력하시오
SELECT AVG(2023-SUBSTR(MEMBERBIRTH,1,4)) AS AVGAGE
FROM   MEMBER1230
WHERE  MEMBERADDR='전주';

-- 최근에 등록된 서울에 사는 사람의 이름을 출력하시오
SELECT MEMBERNAME
FROM   MEMBER1230
WHERE  MIDX =
       (SELECT MAX(midx) AS MAXMIDX
       FROM    MEMBER1230
       WHERE   memberAddr='서울'
       );

-- 회원번호 1번부터 7번까지인 사람들의 나이의 총합을 구하시오
SELECT SUM(2023-SUBSTR(memberBirth,1,4)) AS AGE
FROM   MEMBER1230
WHERE  MIDX BETWEEN 1 AND 7;
SELECT * FROM   MEMBER1230;

-- 핸드폰 끝자리가 5인 사람들의 명단을 지역별 이름 내림차순으로 정렬하시오
SELECT   memberAddr,
         memberName
FROM     MEMBER1230
WHERE    SUBSTR(memberPhone,-1)=5
GROUP BY memberAddr,
         memberName
ORDER BY memberName DESC;

-- 사는 지역별로 사람들의 인원 수를 출력하시오
SELECT   MEMBERADDR,
         COUNT(midx)
FROM     MEMBER1230
GROUP BY MEMBERADDR;

 group by를 사용했을 때 반드시 그 사용한 값을 select 로 지정해주어야 한다..

 

---

1.  instr() 함수: 검색어의 시작점 위치 뽑기

-- 사람들의 메일 주소의 자리 위치를 출력하시오
SELECT INSTR(MEMBEREMAIL,'@')
FROM   MEMBER1230;

SELECT *
FROM   MEMBER1230;

 

Q. 회원들이 사용하는 메일별로 사람들의 인원수를 출력하시오 (INSRT () 사용)

SELECT   SUBSTR(memberEmail, INSTR(memberEmail,'@')+1),
         COUNT(*)
FROM     MEMBER1230
GROUP BY SUBSTR(memberEmail, INSTR(memberEmail,'@')+1);

Q. 지역별 성별 인원수를 출력하시오

SELECT   memberAddr,
         memberGender,
         COUNT(*)
FROM     MEMBER1230
GROUP BY memberAddr,
         memberGender
ORDER BY MEMBERADDR;

 

 

2 . 서브 쿼리 (쿼리 안에 쿼리)

-- 회원번호가 회원번호 평균 이하인 사람들의 데이터를 출력하시오
SELECT *
FROM   member1230
WHERE  midx <
       (SELECT AVG(midx)
       FROM    MEMBER1230
       );

Q 나이가 가장 작은 사람의 전화번호를 출력하시오 (서브쿼리 사용)

SELECT MEMBERPHONE
FROM   member1230
WHERE  (
              2023-SUBSTR(memberBirth,1,4)
       )
       =
       (SELECT MIN(2023-SUBSTR(memberBirth,1,4))
       FROM    MEMBER1230
       );

 

Q .다중 행 값을 뽑아내는 서브쿼리

-- 회원번호 중에 회원번호가 홀수인 사람들의 이름을 서브쿼리를 활용해서 출력하세요 홀수확인:mod(컬럼,2)=1
SELECT MEMBERNAME
FROM   member1230
WHERE  midx IN
       (SELECT midx
       FROM    MEMBER1230
       WHERE   MOD(midx,2)=1
       );

값이 여러개일 때, IN()으로 묶어서 해당 경우를 한방에 처리해준다

<IN(1,3,5,7,9) : 하개발자 방식>

 

 

-- 스칼라뷰 서브쿼리(컬럼위치 안에 쓰는 서브쿼리)

-- 회원명단을 출력하고 탈퇴된 회원
SELECT MEMBERNAME,
       (SELECT '탈퇴'
       FROM    MEMBER1230 b
       WHERE   a.MIDX =b.MIDX
       AND     b.DELYN='Y'
       )
FROM   MEMBER1230 a;

-- 인라인뷰 서브쿼리(단일 테이블이 아닌 제약 테이블 사용)

SELECT *
FROM   MEMBER1230 a,
       (SELECT *
       FROM    MEMBER1230
       WHERE   memberGender='female'
       )
       b
WHERE  a.midx = b.midx;

 

Q 각 지역별 여성 인원수를 서브쿼리를 활용해서 출력하세요

SELECT   memberAddr,
         COUNT(*)
FROM     MEMBER1230
WHERE    midx IN
         (SELECT midx
         FROM    MEMBER1230
         WHERE   MEMBERGENDER='female'
         )
GROUP BY MEMBERADDR;

 

3.  case when then else end 조건식

-- 회원이 성별이 male이면 남성이라고 출력하고 female이라면 여성이라고 출력하세요
SELECT midx,
CASE MEMBERGENDER	--성별이
WHEN 'male' THEN '남성' --male이면 남성
WHEN 'female' THEN '여성' --female이면 여성
ELSE '모름' END AS gender
FROM MEMBER1230;

SELECT midx, 
CASE WHEN memberGender='male' THEN '남성'
WHEN memberGender='female' THEN '여성'
ELSE '모름' END
MEMBERGENDER FROM MEMBER1230;

++ ) decode() 조건식 ( 같은 출력값을 가짐)

SELECT midx, DECODE(MEMBERGENDER,'male','남성','female','여성','모름') AS gender FROM MEMBER1230;

 

 

Q. 회원탈퇴되었으면 delYN='Y'이면 탈퇴라고 출력하고 'N'이면 가입이라고 출력하시오(CASE문을 활용해서)

SELECT midx,
CASE WHEN delYN='Y' THEN '탈퇴'
WHEN delYN='N' THEN '가입'
END
delYN FROM MEMBER1230;

Q. 회원번호 짝수인 사람의 IP를 NULL처리하시오.

UPDATE MEMBER1230 SET ip=NULL WHERE MOD(midx,2)=0;
COMMIT;
SELECT *FROM MEMBER1230;

 

 

4.  null체크 함수 nvl()와 nvl2()

-- ip가 null인 사람은 추적불가로 출력하고 아닌 사람은 추적가능이라고 출력하시오
-- null체크 함수 nvl()와 nvl2()
SELECT NVL(ip,'추적불가') FROM MEMBER1230;

SELECT NVL2(ip,'추적가능','추적불가') FROM MEMBER1230; --nvl2(체크하는 곳 , null아니면, null이면)