[강의노트_DB]14. SQL-4

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';