QUERY 연습 - 10.SQL활용

   

   

1

아래 보기의 스키마를 반영하여 직원(emp) 테이블을 생성하는 SQL문을 작성하시오.

   

직원(emp) 테이블 스키마 

컬럼	조건
직원번호(emp_no)	NUMBER(8), PK
직원이름(emp_name)	VARCHAR2(10), NOT NULL
고용일(emp_hdate)	DATE, 시스템일자
부서장 직원번호(emp_mgr_no)	NUMBER(8), FK, emp_no

   

CREATE TABLE emp( emp_no NUMBER(8) CONSTRAINT emp_pk PRIMARY KEY, emp_name VARCHAR2(10) CONSTRAINT emp_name_nn NOT NULL, emp_hdate DATE DEFAULT sysdate, emp_mgr_no NUMBER(8) CONSTRAINT emp_mgr_fk REFERENCE emp(emp_no) );

   

   

   

2

부서 평균 급여액이 8,000달러 이상인 부서만 조회하여

각 부서(deptno)별 급여(salary) 총액, 평균 급여액을 구하는 SELECT 문을 작성하시오.

SELECT deptno, SUM(salary), ROUND(AVG(salary)) FROM emp WHERE salary>=8000 GROUP BY deptno;

   

   

   

3

다음의 SQL 명령문을 순서대로 실행했을 때 데이터베이스에 영구적으로 반영되는 문장을 순서대로 완성하시오.

INSERT INTO emp(empno, ename) VALUES (999, 'Smith'); SAVEPOINT a; DELETE emp WHERE empno = 202; SAVEPOINT b; UPDATE emp SET ename = 'Clark'; ROLLBACK TO SAVEPOINT a; INSERT INTO emp(empno, ename) VALUES (8888, 'Tomas'); SAVEPOINT c; DELETE emp WHERE empno = 100; COMMIT;

 

DELETE emp WHERE empno = 202; UPDATE emp SET ename = 'Clark'; INSERT INTO emp(empno, ename) VALUES (8888, 'Tomas'); DELETE emp WHERE empno = 100;

   

   

   

4

다음의 보기에서 제시된 두 개의 테이블을 조인하여 이름, 부서코드, 부서명을 직원테이블의 나이순으로 출력하는 SQL문을 작성하시오.

SELECT s_emp.name, s_emp.dept_id, s_dept.dept_name FROM s_dept, s_emp WHERE s_dept.dept_id = s_emp.dept_id ORDER BY s_emp.age;

   

   

   

5

오라클에서 기본적으로 제공되는 사용자계정 HR을 이용하여 아래의 조건에 만족하는 쿼리문을 작성하시오

조회하고자 하는 컬럼은 CITY, DEPARTMENT_NAME, JOB_ID, PERSONS(부서별인원수), TOTAL_SALARY(부서별급여합계) 상호 연관된 테이블을 조인하여 작성한다. 도시별 인원합계와 도시별 급여 합계를 산출한다.(ROLLUP을 활용) CITY별, DEPARTMENT_NAME별, JOB_ID별 오름차순으로 정렬한다.

   

SELECT loc.city, dept.department_name, emp.job_id, COUNT(*) AS PERSONS, SUM(emp.salary) total_salary FROM employees emp, departments dept, locations loc WHERE emp.department_id = dept.department_id AND dept.location_id = loc.location_id GROUP BY ROLLUP(loc.city, dept.department_name, emp.job_id) ORDER BY loc.city, dept.department_name, emp.job_id;

   

ROLLUP 함수

주어진 데이터들의 소계, 합계를 구해주는 함수

   

   

   

6

hr 소유의 employees 테이블에 저장된 salary컬럼에 저장된 자료가 달러환율이 적용되었다고 가정을 할 때 아래의 조건을 만족하는 쿼리문을 작성하시오

1) 달러 환율로 적용된 자료를 우리나라 원화로 계산할 수 있는 사용자 정의 함수를 만들고, 2) 사원명(first_name과 last_name을 irum이라는 컬럼으로 정의), 사용자 정의함수를 활용하여 원화로 계산된 컬럼(salary_won)를 조회하는 쿼리문을 작성하시오

   

1) 사용자 정의함수 생성 create or REPLACE function test_fun( salary_data in number, calcu_data in number) return number is result_sal number := 0; begin result_sal := salary_data * calcu_data; return result_sal; end; /

2) 사용자 정의함수를 이용한 자료 조회 select first_name || last_name irum, test_fun(salary, 1100) salary_won from employees;

 

 

 

7

사용자에게 권한을 부여하고 회수하는 명령어

사용자 : sampleuser 접근 호스트 : localhost 대상 데이터베이스 : sakila 대상 테이블 : 모든 테이블

 

가. 사용자 권한 주기 - SELECT, EXCUTE 권한을 sakila 테이블에 부여

GRANT SELECT, EXCUTE ON sakila.* TO 'sampleuser'@'localhost'

 

나. 사용자 권한 회수 가)항에 부여된 권한 중 EXCUTE 권한 회수

REVOKE EXCUTE ON sakila.* FROM 'sampleuser'@'localhost'

 

 

 

8

사용자 정의함수 지정 예

사용자 정의함수 명	fn_add_number
파라미터	임의의 숫자
구현 로직	1부터 입력받은 숫자까지의 합을 구한다.

 

CREATE FUNCTION 'add_number' (num INT) RETURNS INTEGER BEGIN     DECLARE  result INT;     DECLARE  i INT;         SET  result = 0;     SET  i = 1;         WHILE i <= num DO       SET result = result + i;       SET i = i + 1;     END WHILE;     RETURN result; END