[DB] 퀴즈

-- 1. CONNECT BY LEVEL 을 활용하여 1 ~ 10까지 출력하시오
SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 10;

-- 2. CONNECT BY LEVEL 을 활용하여 0.1 ~ 0.5까지 출력하시오
SELECT LEVEL*0.1 AS NO
FROM DUAL
CONNECT BY LEVEL <= 5;

 

-- 3. 다음 쿼리가 실행되지 않는 이유?

SELECT * FROM EMPFAMILY
WHERE AVG(AGE) > 40;

 

SELECT AGE 
FROM EMPFAMILY
GROUP BY AGE
HAVING AVG(AGE) > 40;

 

-- 4.

WITH T AS

(
SELECT '100,101,102,103,' AS TXT FROM DUAL
)

위와 같은 임시테이블의데이터에서 마지막 ',' 만 빼고 출력하려면?

 

WITH T AS(
SELECT '100,101,102,103,' AS TXT FROM DUAL
)
SELECT RTRIM(TXT,',') AS TXT
FROM T;

 

-- 5.

WITH TMPAS
(
SELECT '10.100.10.1' AS IP FROM DUAL
UNION ALL SELECT '10.100.1.10' FROM DUAL
UNION ALL SELECT '10.10.10.10' FROM DUAL
UNION ALL SELECT '1.10.1.20' FROM DUAL
UNION ALL SELECT '3.10.1.140' FROM DUAL
)

위와 같은 임시테이블의 데이터를 '.' 기준으로 분리하여 다음과 같이 출력하시오.

 

WITH TMP AS(
SELECT '10.100.10.1' AS IP FROM DUAL
UNION ALL SELECT '10.100.1.10' FROM DUAL
UNION ALL SELECT '10.10.10.10' FROM DUAL
UNION ALL SELECT '1.10.1.20' FROM DUAL
UNION ALL SELECT '3.10.1.140' FROM DUAL
)
SELECT IP
, LPAD((SUBSTR(IP, 1, INSTR(IP, '.', 1, 1)-1)),3,0) AS IP_1
, LPAD(RTRIM(SUBSTR(IP, INSTR(IP, '.', 1, 1)+1, INSTR(IP, '.', 1, 1)),'.'),3,0) AS IP_2
, LPAD(RTRIM(RTRIM(SUBSTR(IP, INSTR(IP, '.', 1, 2)+1, INSTR(IP, '.', -1, 3)), 1), '.'), 3, 0) AS IP_3
, LPAD(LTRIM(SUBSTR(IP, INSTR(IP, '.', -1)),'.'), 3, 0) AS IP_4
FROM TMP
ORDER BY IP_1, IP_2, IP_3, IP_4 ASC;