(MariaDB)SQL프로그래밍

SQL프로그래밍

연습문제

1.EMP Table을 복사해서 EMPTEST이름으로 테이블을 생성하고
500 건의 테스트 데이터를 추가하는 procedure를 생성하여 추가하시오.

DROP TABLE if exits emptest;
CREATE TABLE emptest(SELECT * FROM emp);

delimiter $$
CREATE PROCEDURE addProc()
BEGIN 
    DECLARE i INT ;

    SET i=1;

    while(i<=500)DO 
    INSERT INTO emptest VALUES(100+i,'test','test',i,CURDATE(),i,i,i);
    SET i=i+1;
    END while;

END $$
delimiter ;

2.사원(EMPTEST)테이블에서 직업(JOB)이 ‘SALESMAN’ 인 사원 급여(SAL)에 400 더하는 수정(UPDATE) 구문을 구하세요

delimiter $$
CREATE PROCEDURE addSal()
BEGIN
    UPDATE emptest SET sal=sal+400;
END $$
delimiter ;

3.사원(EMPTEST)테이블에서 급여(SAL)가 사원 평균급여 보다 높은 사원을 대상으로 고용일자(HIREDATE)를 1년 더하는 수정(UPDATE) 구문을 구하세요

delimiter $$
CREATE PROCEDURE addYear()
BEGIN 
UPDATE emptest 
SET hiredate=DATE_ADD(hiredate,INTERVAL 1 YEAR) 
WHERE sal > (SELECT AVG(sal) FROM emptest);
END $$
delimiter ;

4.사원(EMPTEST)테이블에서 전체 사원을 대상으로 COMM 컬럼에 100 을 더하고 직업(JOB)이 ‘CLERK’ 인 사원은 현 급여에서 2배, ‘MANAGER’ 인 직업을 가진 사원은 현 급여에서 3배, 이외 직업을 가진 사원은 현 급여에서 4배를 더하는 수정(UPDATE) 구문을 구하세요

//내코드
delimiter $$
CREATE PROCEDURE addComm()
BEGIN 
UPDATE emptest SET comm=ifNULL(comm+100,0+100);
UPDATE emptest SET sal=sal+sal*2 WHERE job='clerk';
UPDATE emptest SET sal=sal+sal*3 WHERE job='manager';
UPDATE emptest SET sal=sal+sal*4 WHERE job !='clerk' AND job !='manager';

END $$
delimiter ;


-- 상관서브쿼리 
UPDATE emptest e1
SET comm=nvl(comm,0) + 100,sal=sal+(
                            SELECT case job
                                when 'clerk' then sal*2
                                when 'manager' then sal*3
                                ELSE sal*4
                                END 
                            FROM emptest e2
                            WHERE e1.EMPNO=e2.EMPNO
                            );

5.사원(EMPTEST)테이블에서 이름(ENAME)이 ‘M’으로 시작하는 사원
삭제(DELETE) 구문을 구하세요

delimiter $$
CREATE PROCEDURE deleteM()
BEGIN
DELETE  FROM emptest WHERE ename LIKE('M%');

END $$
delimiter ;

6. 사원(EMPTEST)테이블에서 급여(SAL)가 사원 평균급여 보다 높은 사원 삭제(DELETE) 구문을 구하세요

delimiter $$
CREATE PROCEDURE deleteSal()
BEGIN
DELETE from emptest WHERE sal > (SELECT AVG(sal) FROM emp);

END $$
delimiter ;