[강의노트_DB]13. SQL-3

1.현재 날짜를 표시하는 질의를 작성하고 열 레이블을 Data로지정하십시오.

 

SELECT sysdate as "DATA" FROM dual;

-현재 날짜는 dual의 sysdate에 있다.

2.각 사원에 대해 사원 번호,이름, 급여및15% 인상된급여를정수로표시하십시오. 인상된 급여열의 레이블을 New Salary로 지정하십시오.

 

SELECT employee_id, last_name, salary*1.15 as "New Salary" FROM employees;

-애트리뷰트들을 나열하는 부분에 곱하기와 같은 사칙연산자가 들어갈 수 있다.

3.이름이 J, A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를표시하는 질의를 작성하고 각 열에 적합한 레이블을 지정하십시오. 결과를 사원의 이름에 따라 정렬하십시오.

 
SELECT INITCAP(last_name) as "LAST_NAME",length(last_name) as "LENGTH OF LAST NAME" FROM employees WHERE last_name like 'J%' or last_name like 'A%' or last_name like 'M%' order by last_name;

-INITCAP은 첫글자만 대문자, 나머지는 소문자로 표시해주는 함수이고, length는 길이를 표시해주는 함수이다.

 

4.각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달 수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하십시오. 결과는 정수로 반올림하여 표시하고 근무 달 수를 기준으로 정렬하십시오

 

SELECT last_name, ROUND(MONTHS_BETWEEN(sysdate,hire_date)) as "MONTHS_WORKED" FROM employees ORDER BY MONTHS_WORKED;

-MONTHS_BETWEEN은 두 날짜 사이에 차이를 나타내주며 ROUND로 정수로 반올림이 가능하다.

5.각 사원에 대해 다음 항목을 생성하는 질의를 작성하십시오. earn monthly but wants <3 times salary> 열레이블을Dream Salaries로 지정하십시오 

 

SELECT last_name ||' earn ' || salary || ' monthly but wants '|| 3*salary as "Dream Salaries" FROM employees;

-특정한 문장으로 질의를 만들어야 하므로 이는 || 를 사용해서 이을 수 있다.

 

6.모든 사원의 이름과 급여를 표시하는 질의를 작성하십시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열레이블을 SALARY로 지정하십시오. 

 

SELECT last_name,LPAD(salary,15,'$') as "SALARY" FROM employees;

-글자의 길이를 설정해주고 남은 글자들을 원하는 것으로 채워주는 함수가 LPAD이다. LPAD는 왼쪽에 빈 공간을 채우고 RPAD는 오른쪽에 빈 공간을 채워준다.

7.사원의 이름, 입사일 및 급여 검토일을 표시하십시오. 급여 검토일은 여섯달이 경과한 후 첫번째 월요일입니다. 열레이블을 REVIEW로 지정하고 날짜는 “Monday, the Thirty-First of July, 2000”과 같은 형식으로 표시되도록 지정하십시오.

 

SELECT last_name,hire_date, To_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6),'월요일'),'DAY,DD "of the" MONTH,YYYY') as "REVIEW" FROM employees;

-ADD_MONTHS를 통해서 6달이 지난 것을 알 수 있고, NEXT_DAY로 그 다음 첫번째 월요일을 얻을 수 있다. 또한 날짜를 특정 형식으로 만들어 줘야 하므로 이를 위해 TO_CHAR로 변환한다.

 

8. 이름, 입사일 및 업무 시작 요일을 표시하고 열레이블을 DAY로 지정하십시오. Monday를 시작으로 해서 요일을 기준으로 결과를 정렬하십시오. 

 

SELECT last_name,hire_date,To_CHAR(hire_date,'DY') as "DAY"  FROM employees ORDER BY TO_CHAR(hire_date-1,'D');

-윗 문제처럼 날짜에서 특정 시간만 가져오려 하면 TO_CHAR를 사용하도록 한다. 또한 요일을 정렬할 때 ORDER BY를 사용하는데 이 때 월요일을 기준으로 맞춰주려면 hire-date에서 하루를 빼야한다.

 

9.사원의 이름과 커미션을 표시하는 질의를 작성하십시오. 커미션을 받지 않는 사원일 경우 “No Commission”을 표시하십시오. 열레이블은 COMM으로 지정하십시오.

 

SELECT last_name,NVL(To_CHAR(commission_pct,'0.99'),'No Comission') as "COMM" FROM employees;

-커미션을 받지 않는 사원은 null일 것 이므로 NVL를 통해서 바꿔주도록 한다.

10.사원의 이름을 표시하고 급여 총액을 별표(*)로 나타내는 질의를 작성하십시오. 각 별표는 1,000달러를 나타냅니다. 급여를 기준으로 데이터를내림차순으로정렬하고열레이블을EMPLOYEES_AND_THEIR_SALARIES로지정하십시오.*/

 

SELECT last_name, salary, RPAD(' ',salary/1000+1,'*') as "EMPLOYEES_AND_THEIR_SALARIES" FROM employees ORDER BY salary DESC;

-RPAD를 사용해서 급여를 별표로 바꾸었다.

11. DECODE 함수를 사용하여 다음 데이터에 따라 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시하는 질의를 작성하십시오. 업무등급 AD_PRES A, ST_MAN B, IT_PROG C, SA_REP D, ST_CLERK E, 기타0 

 

SELECT last_name,DECODE (job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','E',0) as "GRADE" FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 DECODE를 사용한 예시이다.

 

12. 11번 문제의 명령문을 CASE 구문을 사용하여 재작성하십시오.

SELECT last_name, 
  CASE job_id
   WHEN 'AD_PRES' THEN 'A'
   WHEN 'ST_MAN' THEN 'B'
   WHEN 'IT_PROG' THEN 'C'
   WHEN 'SA_REP' THEN 'D'
   WHEN 'ST_CLERK' THEN 'E'
  ELSE '0' END AS GRADE FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 CASE를 사용한 예시이다.

 

1. 모든사원의이름, 부서번호, 부서이름을표시하는질의를작성하십시오. 

 

select e.last_name,d.department_id,d.department_name from employees e,departments d;

- 두 개의 릴레이션에 정보를 가져오고자 할 때에는 릴레이션에도 별칭을 부여한다.

 2. 부서80에속하는모든업무의고유목록을작성하고출력결과에부서의위치를포함시키십시오. (중복제거)

 

select distinct e.job_id , d.location_id from employees e INNER JOIN departments d ON e.department_id=80;

- 조인을 사용하였고, 중복을 제거하기 위해서 distinct를 사용하였다.

3. 커미션을 받는 모든 사원의 이름, 부서이름, 위치 ID 및 도시를 표시하는 질의를 작성하십시오.

select e.last_name,d.department_name,d.location_id,l.city from employees e, departments d, locations l where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;

-JOIN 연산자가 아닌 where 조건으로 조인을 하였다.

4. 이름에 a(소문자)가 포함된 모든 사원의 이름과 부서 이름을 표시하는 질의를 작성하십시오.

 

select e.last_name,d.department_name from employees e,departments d where e.last_name like '%a%' and e.department_id=d.department_id;

5.Toronto에서 근무하는 모든 사원의 이름,업무,부서 번호 및 부서 이름을 표시하는 질의를 작성하십시오. (join,on 키워드 사용)

 
select e.last_name,e.job_id,d.department_id,d.department_name
from employees e Inner Join departments d on (e.department_id=d.department_id) Inner Join locations l on (d.location_id=l.location_id) where l.city= 'Toronto';

 

6.사원의 이름 및 사원 번호를 관리자의 이름 및 관리자 번호와 함께 표시하고, 각각의 열 레이블을 Employee,Emp#, Manager,Mgr#로 지정하십시오.(관리자가 없는 사원도 포함)

 

select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e2.employee_id as "Mgr#"
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id;

 

7.지정한 사원의 이름, 부서 번호 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 질의를 작성하고, 각 열에 적합한 레이블을 지정하십시오. 또한 부서번호, 사원 이름, 동일한 부서에서 근무하는 사원의 이름으로 오름 차순 정렬하시오.

 
select e1.department_id as "DEPARTMENT", e1.last_name as "EMPLOYEE", e2.last_name as "COLLEAGUE"
from employees e1,employees e2 where e1.department_id=e2.department_id and not e1.last_name=e2.last_name
Order by DEPARTMENT, EMPLOYEE, COLLEAGUE;

 

8.Davies라는 사원보다 늦게 입사한 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

 

select last_name,hire_date from employees where hire_date >=(select hire_date from employees where last_name='Davies');

 

9.관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하는 질의를 작성하십시오.

select e1.last_name,e1.hire_date,e2.last_name,e2.hire_date from employees e1 Inner Join employees e2 on (e1.manager_id=e2.employee_id)
where e1.hire_date < e2.hire_date;