[MySQL] #11 SQL 단일 행 함수 실습을 해보자~

SQL

[MySQL] #11 SQL 단일 행 함수 실습을 해보자~

째아 2023. 1. 16. 17:27

-- ################## 문자열 함수 실습 ######################
-- 1) 직원의 이름에 대소문자 상관없이 s가 들어간 직원의 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE lower(ename) LIKE '%s%';

-- 2) 직원 정보 중 사원번호, 이름, 급여를 출력한다. 단, 이름은 앞에서 3글자씩만 출력하라.

SELECT empno, left(ename, 3), sal
FROM emp;

-- 3) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 이름만을 출력하라.

SELECT lower(left(ename, 3))
FROM emp
WHERE length(ename) >= 6;

-- 4) 직원의 이름과 급여를 출력하라, 단, 금액의 정확도를 위해 급여를 6자리로 출력하고, 앞 여백을 모두 * 로 채워 출력하라.

SELECT ename, lpad(sal, 6, '*')
FROM emp;

-- 5) 직원정보를 입력하던중 아래와 같이 입력 되었다.

insert into emp values(8001,'   PARK   ', 'IT' ,  7900 , '21/11/10', 20000,1000,10);
select * from emp;

-- 이름이 'PARK'인 직원의 정보를 출력하여 보자. / 안됨

 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
 FROM emp
 WHERE ename = 'PARK';

-- 이름 좌우에 공백이 있더라도 공백을 제거하고 이름이 'PARK'인 직원의 정보를 출력하여 보자.

 SELECT empno, trim(ename), job, mgr, hiredate, sal, comm, deptno
 FROM emp
 WHERE trim(ename) = 'PARK';

-- ################## 숫자 함수 실습 ######################
-- 1) 급여가 $1,500부터 $3,000 사이의 사람은 급여의 15%를 회비로 지불하기로 하였다.
-- 이를 이름, 급여, 회비(소수점 두 자리 아래에서 반올림)를 출력하라.

SELECT ename, sal, if(sal >= 1500 AND sal <= 3000, round(sal*0.15, 2), 0) 회비
FROM emp;

-- 2) 급여가 $2,000 이상인 모든 사람은 급여의 5%를 경조비로 내기 로 하였다.
-- 이름, 급여, 경조비(소수점 이하 절삭)를 출력하라.

SELECT ename, sal, if(sal >= 2000, truncate(sal*0.05, 0), 0) 경조비
FROM emp;

-- 3) 이익을 배분하기 위해 comm의 150%를 보너스로 지급하려 한다. comm이 있는 직원들을 대상으로
-- 직원번호, 직원명, 급여, comm의 150%를 소숫점이하 올림하여 출력하라. (comm이 0이거나, null이면 제외)

SELECT empno, ename, sal, ceiling(comm*1.5) bonus
FROM emp
WHERE comm IS NOT NULL AND comm != 0;

-- ################## 날짜 함수 실습 ######################

-- 1)입사일로부터 100일이 지난 후를 구해보자. 사원이름, 입사일, 100일 후의 날, 급여를 출력하라.

SELECT ename, hiredate, date_add(hiredate, interval 100 day) '100일 후의 날', sal
FROM emp;

-- 2) 입사일로부터 6개월이 지난 후의 날짜를 구하려고 한다. 입사일, 6개월 후의 날짜, 급여를 출력하라

SELECT hiredate, date_add(hiredate, interval 6 month) '6개월 후의 날', sal
FROM emp;

-- 3) 입사한 달의 근무일수를 계산하여 부서번호, 이름, 근무일수를 출력하라.

SELECT deptno, ename, datediff(last_day(hiredate), hiredate) + 1 근무일수
FROM emp;

-- 4) 모든 사원의 입사일 기준으로 100일이 지난 후의 월요일의 날짜를 구해서 이름, 입사일,’MONDAY’를 출력하라.

SELECT ename, hiredate, date_add(date_add(hiredate, interval 100 day)
 , interval (7 - weekday(date_add(hiredate, interval 100 day))) % 7 day) MONDAY
FROM emp;

-- 5) 입사일로부터 오늘까지의 일수를 구하여 이름, 입사일, 근무일수를 출력하라.

SELECT ename, hiredate, datediff(curdate(), hiredate) + 1 근무일수
FROM emp;

-- 6) 직원의 이름, 근속년수를 구하여 출력하라.

SELECT ename, truncate(datediff(curdate(), hiredate) / 365, 0) 근속년수
FROM emp;

-- 7) 입사일부터 지금까지의 날짜수를 출력하라. 부서번호, 이름, 입사일, 현재일,
-- 근무일수(소수점 이하 절삭), 근무년수, 근무월수(30일 기준), 근무주수를 출력하라.

SELECT 
    deptno,
    ename,
    hiredate,
    CURDATE(),
    DATEDIFF(CURDATE(), hiredate) + 1 근무일수,
    TRUNCATE((DATEDIFF(CURDATE(), hiredate) + 1) / 365, 0) 근무년수,
    TRUNCATE((DATEDIFF(CURDATE(), hiredate) + 1) / 30, 0) 근무월수,
    TRUNCATE((DATEDIFF(CURDATE(), hiredate) + 1) / 7, 0) 근무주수
FROM emp;

#timestampdiff(year, curdate(), hiredate)
SELECT 
    deptno,
    ename,
    hiredate,
    CURDATE(),
    DATEDIFF(CURDATE(), hiredate) + 1 근무일수,
    timestampdiff(year, hiredate, curdate()) 근무년수,
    timestampdiff(month, hiredate, curdate()) 근무월수,
    timestampdiff(week, hiredate, curdate()) 근무주수
FROM emp;

-- ################## 변환 함수 실습 ######################

-- 1) 모든 직원의 이름과 입사일을 ‘1996-5-14’의 형태로 출력 하라.

SELECT ename, date_format(hiredate, '%Y-%m-%d') 입사일
FROM emp;

-- 2) 모든 직원의 이름과 입사년과 입사월만 '1981.05' 형식으로 출력하라

SELECT ename, date_format(hiredate, '%Y.%m') 입사년월
FROM emp;

-- 3) 모든 직원의 번호, 이름, 급여를 출력하라. 단, 급여앞에 화폐표시($), 그리고 천단위(,)표시, 소수점이하 2자리가 표시되도록 하라.

SELECT empno, ename, concat('$', format(sal, 2))
FROM emp;

-- 4) 모든 직원의 이름과 입사한 요일을 출력하라.

SELECT ename, dayname(hiredate) 요일
FROM emp;

SELECT ename, substr('월화수목금토일', weekday(hiredate) + 1, 1) 요일
FROM emp;

SELECT ename, date_format(hiredate, '%a') 요일
FROM emp;

-- 5) 문자로 입력된 '20211123' 이라는 값이 날짜로 인식되도록 변경하여 출력하여 보자.

SELECT convert('20211123', date) 날짜;
SELECT date('20211123') 날짜;
SELECT date_format('20211123', '%Y-%m-%d') 날짜;
SELECT str_to_date('20211123', '%Y%m%d') 날짜;

-- ################## 일반 함수 실습 ######################

-- 1) 모든 직원의 이름, 급여, 커미션을 출력하라. 단, comm이 null이면 '없음'으로 출력하라.

SELECT ename, sal, ifnull(comm, '없음') comm
FROM emp;

-- 2) 모든 직원의 이름, 급여, 커미션, 연봉을 출력하라.
-- 보너스는 comm이 null이면 10

SELECT ename, sal, comm, sal*12+ifnull(comm, 10) 연봉
FROM emp;

-- 3) 모든 직원의 이름, 직무, 급여, 커미션, 보너스를 출력하라.
-- 보너스는 직무가 MANAGER이면 급여의 150%, 그외 직원은 급여의 130% 이다.

SELECT ename, job, sal, comm, sal*if(job='MANAGER', 1.5, 1.3) bonus
FROM emp;

-- 4) mgr2 컬럼을 하나 더 만들어서
-- mgr이 null이면 상위담당자/ null이 아니면 mgr값을 가지도록 한다.

SELECT ename, mgr, ifnull(mgr, '상위담당자') mgr2
FROM emp;