CodeDrive

1. 다음 테이블 인스턴트 차트를 기반으로 DEPT 테이블을 생성하십시오.

CREATE TABLE dept(ID Number(7),NAME VARCHAR2(25));
describe dept;

2.DEPARTMENT 테이블의 데이터를 DEPT 테이블에 추가하십시오.

insert into dept(id , name)
select department_id,department_name from departments;

3. 다음 테이블 인스턴스 차트를 기반으로 emp 테이블을 생성하십시오.

Create table emp(ID Number(7),LAST_NAME VARCHAR2(25),FIRST_NAME VARCHAR2(25),DEPT_ID NUMBER(7));

4.EMP 테이블의 LAST_NAME 열의 최대 길이를 50으로 수정하십시오.

ALTER TABLE emp
modify (last_name varchar2(50));

5.EMP 테이블을 삭제하십시오.

drop table emp;

6. EMPLOYEES 테이블 구조를 기반으로 EMPLOYEES2 테이블을 생성하십시오.  EMPLOYEE_ID,first_name,last_name,salary 및  department_id 열만 포함시키고 새 테이블의 열 이름을 각각 ID,First_Name,Last_name,salary 및 dept_id로 지정하십시오.

 
create table EMPLOYEES2(ID number(6),FIRST_NAME varchar(20),LAST_NAME varchar(25),SALARY number(8,2),DEPT_ID number(4));

7. employees2 테이블의 이름을 emp로 변경하십시오.

RENAME employees2 to emp;

8.EMP 테이블에서 FIRST_NAME 열을 삭제하십시오.

ALTER TABLE emp
drop (first_name);

1. EMP 테이블의 ID 열에 테이블 레벨의 PRIMARY KEY 제약 조건을 추가. 제약 조건 이름은 my_emp_id_pk로 지정하시오.

 
alter table emp
add constraint "my_emp_id_pk" primary key(id);

2. ID 열을 사용하여 DEPT 테이블에 PRIMARY_key 제약 조 건을 생성. 제약 조건 이름은 my_dept_id_pk로 지정하시오.

alter table dept
Add (constraint "my_dept_id_pk" primary key(id));

3.EMP 테이블에 DEPT_ID 열을 추가. 존재하지 않는 부서 에 사원이 배정되지 않도록 외래 키 참조를 EMP 테이블에 추가. 제약 조건 이름은 my_emp_dept_id_fk로 지정하시오.

alter table emp
add (constraint "my_emp_dept_id_fk" foreign key(dept_id) references dept(id));

4.User_constaints 뷰를 질의하여 제약 조건이 추가되었는지 확인하시오. (EMP, DEPT 테이블만 질의)

select constraint_type,constraint_name, search_condition
from user_constraints
where table_name= 'emp' or table_name= 'dept';

5.EMP 테이블을 수정하여 십진 자릿수 2, 소수점 이하 자릿수 2인 NUMBER 데이터 유형의 COMMISSION 열을 추가하시오. 커미션 값이 0보다 크도록 커미션 열에 제약 조건을 추가하시오.

alter table emp
add (COMMISSION number(2,2));
alter table emp
add (constraint "commission_ck" check (commission>0));

문제:

문제 풀이:

문제:

문제 풀이:

문제:

문제 풀이:

문제:

입력:

출력:

풀이 방법:

문제:

입력:

출력:

문제 풀이:

1.모든 사원의 급여 최고액, 최저액, 총액 및 평균액을 표시하십시오. 열레이블을 각각 Maximum, Minimum, Sum 및Average로 지정하고 결과를 정수로 반올림하십시오.

 
select MAX(SALARY) as Maximum ,MIN(SALARY) as Minimum,SUM(SALARY) as "SUM" ,round(AVG(SALARY),0) as Average from employees;

2.1번을 수정하여 각 업무 유형별로 표시하십시오.

select job_id ,MAX(SALARY) as Maximum ,MIN(SALARY) as Minimum,SUM(SALARY) as "SUM" ,round(AVG(SALARY),0) as Average from employees group by job_id;

3.업무가 동일한 사원 수를 표시하는 질의를 작성하십시오.

select job_id,count(*)as "COUNT" from employees group by job_id;

4.관리자는 나열하지 말고 관리자 수를 확인하십시오. 열 레이블은 Number of Managers로 지정하십시오.

select count(*) as "Number of Managers" from employees group by manager_id;

5.최고 급여와 최저 급여의 차액을 표시하는 질의를 작성하고 열 레이블을 DIFFERENCE로 지정하십시오.

select MAX(salary)-Min(salary) as "DIFFERENCE" from employees;

6.관리자 번호 및 해당 관리자에 속한 사원의 최저 급여를 쵸시하십시오. 관리자를 알 수 없는 사원 및 최저 급여가 $6,000 미만인 그룹은 제외시키고 결과를 급여에 대한 내림차순으로 정렬하십시오.

select manager_id,min(salary) from employees where manager_id is not null and salary>6000 group by manager_id order by min(salary) desc;

7.각 부서에 대해 부서 이름, 위치, 사원 수, 부서 내 모든 사원의 평균 급여를 표시하는 질의를 작성하고, 열 레이블을 각각 Name,Locaton, Number of People 및 Salary로 지정하십시오. 평균 급여는 소수점 둘째 자리로 반올림하십시오.

select d.department_name as "Name", d.location_id as "Location", count(e.employee_id) as "Number of People",round(AVG(e.salary),2) as "Salary" from employees e,departments d
where e.department_id=d.department_id group by d.department_name,d.location_id;

8.총 사원 수 및 2005,2006,2007,2008년에 입사한 사원 수를 표시하는 질의를 작성하고 적합한 열 머리글을 작성하십시오.

 
select count(*) as "TOTAL", sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2005',1)) as "2005",sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2006','1')) as "2006" ,sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2007','1')) as "2007" ,sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2008','1')) as "2008" from employees;

9.업무를 표시한 다음 해당 업무에 대해 급여 총액과 부서 별 급여(20,50,80,90)의 총액을 각각 표시하는 형렬 질의를 작성하고 각 열에 적합한 머리글을 지정하십시오.

select job_id as "Job",NVL(sum(DECODE(department_id,20,salary)),0) as "DEPT20" ,NVL(sum(DECODE(department_id,50,salary)),0) as "DEPT50",NVL(sum(DECODE(department_id,80,salary)),0)as "DEPT80",NVL(sum(DECODE(department_id,90,salary)),0) as "DEPT90",sum(salary)as "TOTAL" from employees group by job_id;

1. Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

select last_name,hire_date from employees where department_id =(select department_id from employees where last_name= 'Zlotkey') and not (last_name = 'Zlotkey');

2. 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하고 결과를 급여에 대해 오름차순으로 정렬하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(salary) from employees) order by salary;

3. 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원번호와 이름을 표시하는 질의를 작성하십시오.

select employee_id,last_name from employees where department_id in (select distinct department_id from employees where last_name like '%u%');

4.부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시하십시오.

select e.last_name,e.department_id,e.job_id from employees e,departments d where e.department_id=d.department_id and d.location_id = (select distinct location_id from departments where location_id=1700);

5.King에게 보고하는 모든 사원 이름과 급여를 표시하십시오.

select last_name,salary from employees where manager_id in (select employee_id from employees where last_name= 'King');

6.Executive 부서에 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표시하십시오.

select department_id,last_name,job_id from employees where department_id =(select department_id from departments where department_name= 'Executive'); 

7.평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서에서 근무하는 모든 사원의 번호, 이름 및 급여를 표시하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(salary) from employees) and department_id in (select distinct department_id from employees where last_name like '%u%');

8.미국 내에서 근무하는 사원들의 평균급여보다 많은 급여를 받는 사원의 번호, 이름 및 급여를 표시하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(e.salary) from employees e,departments d where d.location_id in (select distinct location_id from locations where country_id= 'US'));

9.부서 별로 최고 급여를 받는 사원의 번호, 이름, 급여 및 부서 번호를 표시하고 부서 번호에 대해 오름 차순 정렬을 하시오.

 
select employee_id,last_name,salary, department_id from employees where (department_id,salary) in (select department_id,max(salary) from employees group by department_id) order by department_id;

10. From 절을 사용하여 9번 질의를 재 작성하시오.

select e1.employee_id, e1.last_name, e1.salary , e1.department_id
from   employees e1 inner join (select department_id, max(salary) as maxSalary
                                from   employees
                                group by department_id) e2 on e1.salary = e2.maxSalary and e1.department_id = e2.department_id order by e1.department_id;

11.사원이 한 명 이상 존재하는 부서의 번호 및 부서 이름을 표시하시오. (exists 키워드 사용)

select department_id,department_name from departments d where exists (select department_id from employees where department_id=d.department_id);

12. 다음을 참고하여 급여를 가장 적게 받는 사원 5명에 대한 정보를 표시하시오.

select rownum,employee_id,last_name,salary from (select employee_id,last_name,salary from employees order by salary) where rownum <=5; 

1.employees 테이블을복사하여cpy_emp테이블을생성하시오.describe문을사용하여employees와cpy_emp의스키마가동일한지확인하기바랍니다.

Create table cpy_emp as select * from employees;
describe cpy_emp;
describe employees;*/

2.다음 예제 데이터의 첫 번째 데이터 행을 cpy_emp 테이블에 추가하십시오. Insert 절에 열을 나열하지 마십시오.

Insert into cpy_emp
values(300,'Ralph','Patel','Rpatel',NULL,sysdate,'SA_MAN',NULL,NULL,NULL,NULL);

3.위의 목록에 있는 예제 데이터의 두 번째 행을 cpy_emp 테이블에 추가하십시오. 이번에는 Insert 절에 열을 명시적으로 나열하십시오.

 
Insert into cpy_emp(employee_id,first_name,last_name,email,hire_date,job_id)
values(301,'Dancs','Betty','Bdancs',sysdate,'SA_REP');

4.301번 사원의 Last_name을 Drexier로 변경하십시오.

update cpy_emp set last_name= 'Drexler' where last_name= 'Betty';

5. 300, 301번 사원의 나머지 사원 정보를 Vance Jones 와 동일하게 변경하시오.

update cpy_emp
set phone_number=(select phone_number from employees where first_name= 'Vance' and last_name= 'Jones'),
salary=(select salary from employees where first_name= 'Vance' and last_name= 'Jones'),
commission_pct=(select commission_pct from employees where first_name= 'Vance' and last_name= 'Jones'),
manager_id=(select manager_id from employees where first_name= 'Vance' and last_name= 'Jones'),
department_id=(select department_id from employees where first_name= 'Vance' and last_name= 'Jones')
where employee_id= '301' or employee_id= '300';

6.급여가 3000미만인 모든 사원의 급여를 3000으로 변경하십시오.

update cpy_emp
set salary=3000
where salary<3000;

7.Dancs Drexler 사원의 정보를 삭제하십시오.

delete from cpy_emp
where last_name= 'Drexler' and first_name= 'Dancs';

문제 설명:

문제 풀이:

먼저 표의 현황을 깔끔하게 정리를 하기 위해서 tickets을 반복문으로 돌아가면서 이를 딕셔너리 타입(해시) 방식으로 정렬을 하였다. 매번 추가하면서 정렬을 한 이유는 만약 가능한 경로가 여러개라면 알파벳 순서가 앞서는 경로를 우선으로 해야하기 때문이다.

티켓을 정리한 뒤에 재귀적으로 탐색을 하는 함수인 travel에 ticket의 정보를 담고 있는 answer_set, 실제 경로인 answer, 출발하는 공항의 이름인 start, 그리고 모든 항공권을 사용했는지 탐색해야하므로 티켓의 갯수인 K 와 몇 개를 사용했는지 알려주는 count를 사용하였다.

처음에는 무조건 ICN에서 시작을 한다는 조건이 있었으므로 초기값은 ICN으로 시작한다. travel 함수에는 세 가지 조건이 존재한다. 

1. count == K 일 때

종료 조건으로 모든 티켓을 다 사용한 경우에 해당한다. 따라서 True를 반환한다.

2. 잘못 경로를 탐색해서 들어갔을 경우

해시 구조를 통해서 티켓을 정리했다보니 B 공항으로 도착을 하긴 했지만 B 공항에서 출발을 하는 티켓이 없을 수도 있다. 따라서 이 경우에 이 값을 찾으려 하면 error가 발생하기 때문에 예외처리구문을 사용해서 이 case를 탐지했다. 이 경우가 발생한거면 잘못된 경로를 온 것이기 때문에 answer의 마지막 값을 빼주고 잘못된 경로이므로 False를 반환한다.

3. 위 두 경우가 아닐 때

해시로 정리한 티켓을 기준으로 하나씩 경로를 늘려가면서 탐색해본다. 

위 과정을 거치다보면 answer를 구할 수 있게 된다.

문제 설명:

문제 풀이:

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;