0202 오라클 DB 2강 - SQL 문제풀이 (오라클 함수) (함수 설명 포함)

1. 오라클 함수 174P

--Q1. RPAD, SUBSTR, LENGTH 앞자리만 나오게 
 SELECT EMPNO,
  RPAD(SUBSTR(EMPNO,1,2),4,'*')  AS MASKING_EMPNO,
  ENAME, 
  RPAD(SUBSTR(ENAME ,1,1),LENGTH (ENAME),'*')  AS MASKING_ENAME
  FROM EMP WHERE LENGTH(ENAME) >= 5
  AND LENGTH(ENAME) < 6;
  
  
-- Q2. TRUNC, ROUND 하루급여, 시급 계산하기
 SELECT EMPNO, ENAME,SAL,
 TRUNC(SAL/21.5,2) AS DAY_PAY,
 ROUND(SAL/21.5/8,1) AS TIME_PAY
FROM EMP ;

Q1.

RPAD: SQL의 RPAD 함수는 지정된 문자 집합이 있는 문자열을 특정 길이로 오른쪽 패딩하는 데 사용됩니다. 이 함수는 패딩할 문자열과 패딩된 문자열의 원하는 길이라는 두 가지 인수를 사용합니다.

SUBSTR: SQL의 SUBSTR 함수는 문자열의 일부를 추출하는 데 사용됩니다. 소스 문자열, 원하는 하위 문자열의 시작 위치 및 추출할 문자 수의 세 가지 인수를 사용합니다.

LENGTH: SQL의 LENGTH 함수는 문자열의 길이를 결정하는 데 사용됩니다. 길이를 결정할 문자열인 단일 인수를 사용합니다.

SELECT EMPNO,
RPAD(SUBSTR(EMPNO,1,2),LENGTH(EMPNO),'*') AS MASKING_EMPNO,
ENAME,
RPAD(SUBSTR(ENAME,1,1),LENGTH(ENAME),'*') AS MASKING_ENAME
FROM EMP WHERE LENGTH(ENAME) BETWEEN 5 AND 6;

내가 이해한 방식 :

SUBSTR은 변수의 지정한 시작열부터 지정한 마지막열까지 노출한다. (EMPNO)

여기서 RPAD는 변수를 지정하여, 지정한 자리까지 보여주는 대신 공백은 후에 지정한 문자로 채워준다. 

(SUBSTR이 적용된 EMPNO)

따라서 1~2번째 문자열만 보여주는 변수는 4자리까지 보여주되 2번째까지만 보여주는 문자로 지정되었기에

나머지는 *로 채워진다.

 

* LENGTH를 쓰는 이유는 길이가 각각 다를 때 글자의 범위를 지정해주기 위해 쓰는 것.

 

Q2.

TRUNC: SQL의 TRUNC 함수는 숫자의 소수 부분을 제거하여 가장 가까운 정수로 내림하는 데 사용됩니다. 이 함수는 잘라낼 숫자와 결과를 잘라야 하는 소수 자릿수라는 두 가지 인수를 사용합니다.

ROUND: SQL의 ROUND 함수는 숫자를 지정된 소수 자릿수로 반올림하는 데 사용됩니다. 이 함수는 반올림할 숫자와 결과를 반올림할 소수 자릿수라는 두 가지 인수를 사용합니다.

내가 이해한 방식 :

TRUNC : 소수점 치우기

ROUND : 반올림하기 

 

-- Q3. 정직원 되는 날짜 확인, 추가 수당 없는 사람 N/A
--TO_CHAR, NVL, NEXT_DAY, ADD_MONTHS 
 SELECT EMPNO, ENAME, HIREDATE,
 TO_CHAR(NEXT_DAY (ADD_MONTHS (HIREDATE,3),'월요일'),'YYYY-MM-DD') AS R_JOB,
 NVL(TO_CHAR(COMM), 'N/A') AS COMM
 FROM EMP;
 
-- Q4. 직속상관 사원 번호 출력
--SUBSTR, CASE-WHEN-THEN-ELSE-END AS ~, TO_CHAR
SELECT EMPNO,ENAME,MGR,
CASE 
WHEN MGR IS NULL THEN '0000'
WHEN SUBSTR(MGR,1,2) = '75' THEN '5555'
WHEN SUBSTR(MGR,1,2) = '76' THEN '6666'
WHEN SUBSTR(MGR,1,2) = '77' THEN '7777'
WHEN SUBSTR(MGR,1,2) = '78' THEN '8888' 
ELSE TO_CHAR (MGR) END AS CHG_MGR
FROM EMP;

 

Q3.

• TO_CHAR 함수: TO_CHAR 함수는 Oracle에서 숫자나 날짜를 문자열로 변환하는 데 사용됩니다. 첫 번째 인수는 변환할 값이고 두 번째 인수는 변환에 사용할 형식 마스크입니다.

• NVL 함수: NVL 함수는 null 값을 지정된 값으로 대체하는 데 사용됩니다. 첫 번째는 null에 대해 테스트할 값이고 두 번째는 첫 번째 값이 null인 경우 반환할 값입니다.

• NEXT_DAY 함수: NEXT_DAY 함수는 주어진 날짜에 대해 지정된 다음 요일의 날짜를 반환합니다. 두 개의 인수가 필요합니다. 첫 번째는 날짜이고 두 번째는 요일의 이름입니다.

• ADD_MONTHS 함수: ADD_MONTHS 함수는 지정된 날짜에 지정된 개월 수를 추가하는 데 사용됩니다. 첫 번째 인수는 날짜이고 두 번째 인수는 더할 개월 수입니다.

Q4.

• SUBSTR 함수: SUBSTR 함수는 Oracle에서 문자열의 일부를 추출하는 데 사용됩니다. 첫 번째는 문자열, 두 번째는 시작 위치, 세 번째는 추출할 문자 수의 세 가지 인수를 사용합니다.

• CASE 함수: CASE 함수는 SQL에서 조건부 논리를 수행하는 데 사용됩니다. 여러 인수를 사용하며 첫 번째는 테스트할 표현식이고 그 뒤에는 하나 이상의 조건이 있으며 ELSE 절로 끝납니다. 식이 참인 첫 번째 조건은 CASE 함수에서 반환되는 값을 결정합니다.

• TO_CHAR 함수: Q3에서 위에서 설명한 것과 동일합니다.

2. 다중행 함수 212P (5번은 풀지말기)

--Q1 TRUNC, COUNT(*), GROUP BY // 부서별 급여 출력
SELECT DEPTNO, 
TRUNC(AVG(SAL)) AS AVG_SAL,
MAX(SAL) AS MAX_SAL ,
MIN(SAL) AS MIN_SAL, 
COUNT(*) AS CNT
FROM EMP GROUP BY DEPTNO;

SELECT * FROM EMP; 

--Q2 JOB에 종사하는 사원이 3명 이상인 직책과 인원수
--GROUP BY, HAVING, COUNT(*)
SELECT JOB,
COUNT(*) 
FROM EMP GROUP BY JOB
HAVING COUNT(*) >= 3;

--Q3 연도별 입사원수
--TO_CHAR, COUNT(*), GROUP BY
SELECT * FROM EMP; 

SELECT TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR,
DEPTNO ,
COUNT(*) AS CNT
FROM EMP 
GROUP BY TO_CHAR(HIREDATE,'YYYY'), DEPTNO;

Q1. 부서별 급여 출력 -- TRUNC, COUNT(*), GROUP BY

TRUNC: SQL의 TRUNC 함수는 숫자의 소수 부분을 제거하여 효과적으로 가장 가까운 정수로 내림하는 데 사용됩니다. 두 개의 인수를 사용하는데 첫 번째는 자를 숫자이고 두 번째는 제거할 소수 자릿수입니다. 예를 들어 를 TRUNC (12.345, 1)반환 12.3합니다. 두 번째 인수가 생략되면 기본값은 0이며 소수 부분이 완전히 제거됨을 의미합니다.

COUNT(*): SQL의 COUNT 함수는 쿼리 결과 집합의 행 수를 반환하는 데 사용됩니다. 구문 은 COUNT(*)열 값이 null인지 여부에 관계없이 모든 행을 계산합니다. 예를 들어 SELECT COUNT(*) FROM EMPEMP 테이블의 행 수를 반환합니다.

GROUP BY: SQL의 GROUP BY 절은 동일한 값을 가진 행을 요약 행으로 그룹화하는 데 사용됩니다. 결과 집합은 하나 이상의 열을 기반으로 데이터를 집계하여 생성됩니다. 예를 들어 SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO각 부서의 직원 수를 반환합니다. 결과 집합에는 각 부서의 직원 수와 함께 DEPTNO의 각 고유 값에 대해 하나의 행이 있습니다.

 

Q2 JOB에 종사하는 사원이 3명 이상인 직책과 인원수 --GROUP BY, HAVING, COUNT(*)

HAVING : HAVING: SQL의 HAVING 절은 SELECT, UPDATE 및 DELETE 문과 함께 사용되며 그룹 또는 집계에 대한 검색 조건을 지정하는 데 사용됩니다. HAVING 절은 GROUP BY 절과 함께 사용됩니다. HAVING 절은 각 그룹에 대해 계산된 일부 집계 값을 기반으로 SELECT 문에서 반환되는 그룹을 제한하는 데 사용됩니다. 예를 들어 HAVING 절을 사용하여 평균 급여가 $50,000보다 큰 부서를 찾을 수 있습니다.

 

Q3.  연도별 입사원수 --TO_CHAR, COUNT(*), GROUP BY

TO_CHAR : TO_CHAR: SQL의 TO_CHAR 함수는 숫자나 날짜를 문자열로 변환하는 데 사용됩니다. TO_CHAR 함수는 종종 통화 또는 날짜와 같은 특정 형식으로 데이터를 표시하는 데 사용됩니다. TO_CHAR 함수는 두 개의 인수를 사용합니다. 첫 번째 인수는 변환할 표현식이고 두 번째 인수는 형식 모델입니다. 형식 모델은 날짜 형식, 숫자 형식 또는 기타 유형의 형식일 수 있습니다.

 

--Q4 추가수당 받는 사원 수와 받지 않는 사원 수 (내림차순)
--NVL2, COUNT(*), GROUP BY, ORDER BY 
SELECT NVL2(COMM,'O','X') AS EXIST_COMM
,COUNT(*) AS CNT
FROM EMP
GROUP BY NVL2(COMM,'O','X')
ORDER BY NVL2(COMM,'O','X') ASC ;

Q4 추가수당 받는 사원 수와 받지 않는 사원 수 (내림차순) --NVL2, COUNT(*), GROUP BY, ORDER BY 

NVL2 :  NVL2열에서 NULL 값을 처리하는 데 사용되는 SQL 함수입니다. 첫 번째는 NULL을 테스트하려는 표현식이고, 두 번째는 표현식이 NULL이 아닌 경우 반환되는 값이고, 세 번째는 표현식이 NULL인 경우 반환되는 값입니다. 예를 들어 SALARY일부 NULL 값이 있는 열이 있고 이를 0으로 바꾸려는 경우 NVL2다음과 같이 함수를 사용할 수 있습니다.

SELECT NVL2(SALARY, SALARY, 0) AS "SALARY"
FROM employees;

 

 

ORDER BY : 하나 이상의 열을 기준으로 오름차순 또는 내림차순으로 SELECT 문의 결과 집합을 정렬하는 데 사용되는 SQL의 절입니다. 각 열을 오름차순(ASC) 또는 내림차순(DESC)으로 정렬할지 여부를 지정할 수 있습니다. 순서를 지정하지 않으면 기본 순서는 오름차순입니다.employees예를 들어 테이블을 SALARY내림차순 으로 정렬하려는 경우 다음 쿼리를 사용할 수 있습니다.

SELECT *
FROM employees
ORDER BY SALARY DESC;