달력

12

« 2019/12 »

  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  •  
  •  
  •  
  •  

   

   

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

 

 

 

 



'Development > Database' 카테고리의 다른 글

네트워크형 DBMS  (0) 2019.07.30
계층형 DBMS  (0) 2019.07.30
QUERY 연습 - 10.SQL활용  (0) 2019.07.22
서브쿼리를 사용하여 데이터 추가  (0) 2019.07.22
Oracle - 단일 행 함수  (0) 2019.07.15
미러링(Mirroring)의 구성도  (0) 2019.07.09
Posted by codedragon codedragon

댓글을 달아 주세요