실습 모음

 

이거 두개다 동등조인

SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id – SELECT e.last_name, d.department_name FROM employees e INNER JOIN departments d ON (e.department_id = d.department_id)

 

 

 

 

실습 1

 

1.

다음 명령문의 오류는 무엇인가 ?

SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees ;

공백있으면 "" 붙어줘야한다.

 

 

3. EMPLOYEES 테이블의 내용을 검색시 사원번호, 사원이름, 업무코드 , 입사일이 오도록 질의를 작성한다. HIRE_DATE열에 STARTDATE라는 별칭을지정한다.

select employee_id, first_name, last_name, job_id, hire_date as "STARTDATE" from employees;

 

 

4.이름과JOB_ID를 연결한 다음 쉼표 및 공백으로 구분하여 표시하고,열이름을 Employee and Title 로 지정한다

 

select first_name || ' ' || last_name ||', '|| job_id as "Employee and Title" from employees

 

실습 2

봉급 12000 이상인 사람 출력

select first_name, salary from employees where salary > 12000;

 

사원번호가 176인사람

SELECT first_name, last_name, department_id from employees where employee_id = 176;

 

봉급이 5천에서 12000 사이인사람

select last_name, salary from employees where salary not between 5000 and 12000;

 

특정 날짜사이에 입사한 사원 정보 오름차순으로 정렬

select last_name, department_id, hire_date from employees where hire_date between date '2007-02-20' and date '2007-05-02' order by hire_date ASC;

date를 쓰는 이유 그냥 일반 string을 DATE타입으로 변경한거다

date '2007-02-20' ->  '20-FEB-07'

 

부서가  20 50 사이 이름 오름차순으로 출력 

IN 사용하면 편하다.

SELECT LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN(20,50) ORDER BY LAST_NAME

 

부서번호 20 50 샐러리 5000 12000사이

EMPLOYEESSELECT LAST_NAME EMPLOYEE, SALARY "MONTHLY SALARY" FROM EMPLOYEES WHERE SALARY  BETWEEN 5000 AND 12000 AND DEPARTMENT_ID IN(20,50); 

 

05년도 입사

select last_name, hire_date from employees  where hire_date like '%-05'

 

관리자 없는 사원 출력

select last_name, department_id from employees where manager_id is null;

 

커미션 내림차순 커미션 받는사람중에

select last_name, salary, commission from employees where commission is not null order by commission DESC;

 

이름 3번째가 a인사람

select last_name from employees where last_name like '__a%';

 

이름에 a와 e가 잇는 사람

select last_name from employees where last_name like '%a%' and last_name like '%e%';

 

여러조건식만족

select last_name, department_id, salary from employees where salary not in(2500,3500,7000)select last_name, department_id, salary from employees where salary not in(2500,3500,7000) and job_id in ('SA_REP' , 'ST_CLERK')

string도 =인경우 in쓰면 편하다

 

 

커미션 비율 20프로

select last_name , commission_pct from employees where commission_pct = 0.2;

 

실습 3

ROUND 반올림

MONTH_BETWEEN(A,B) A~B 까지 달 계산

 

현재날짜 출력

select sysdate from dual;

 

SALARY 15프로 인상

select salary * 0.15 "new Salary" from employees

 

이름이 J A M 으로 시작하는 사람

select INITCAP(LAST_NAME) NAME, LENGTH(LAST_NAME) "LENGTH OF NAME" FROM employees WHERE SUBSTR(LAST_NAME,1 ,1 ) IN ('J', 'A', 'M') ORDER BY LAST_NAME

아님 LIKE 써서 OR로 나열해도됨

SUBSTR( , 시작위치, 길이)

 

근무달수

select last_name, round(months_between(sysdate, hire_date)) MONTH_WORKED FROM EMPLOYEES ORDER BY MONTHS_WORKED;

 

||사용

select last_name || ' earn ' || salary || 'montly but wants '  || salary*3 "DREAM SALARIES " from employees;

 

LPAD, RPAD 사용 15칸 만들어서 왼쪽을 채워라

select last_name, Lpad(salary,15,'$') salary from employees

 

6개월뒤 첫번쨍 월요일은 형식에 맞게 출력해라

select last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'monday'), 'fmDAY, "the" DDSPTH "of" MONTH, YYYY') as "REVIEW" from employees;

add month는 달더하는거고 nextday는 해당 날짜로부터 첫번째 monday를 찾는다. tochar은 형식 맞춰주는거 ddspth는 first second이렇게 맟져주는거

 

 

d weak dd month ddd year day 요일

 

월화수목금 요일순으로 출력해보자

select to_char(hire_date,'day') DAY from employees order by to_char(hire_date - 1, 'D')

중요한건 hiredate-1 D 를 order한거다. hire date를 d로 바꾸면 sunday가 1로 치환되니까 -1한다. sunday -1 은 1에서 7로 바뀐다.

 

nvl인 경우 특정 말출력한다

select last_name, nvl(to_char(commission_pct), 'no commission') COMM from employees

nvl2인경우 - 함수 :  NVL2("값", "지정값1", "지정값2")

no commision은 string이니까 to_char사용해서 string으로 바꿔준다.

 

 

DECODE : CASE WHEN같은거

select job_id, decode(job_id, 'AD_PRES', 'A', 'ST_MAN', 'B', 'IT_PROG', 'C', 'SA_REP', 'D' ,'ST_CLERK', 'E', '0') GRADE FROM EMPLOYEES

 

 

실습 4

 

자연조인

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPART_NAME FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D

 

이런식으로도 where해도 가능

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTment_NAME FROM EMPLOYEES E, DEPARTMENTS D  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

 

자연조인 + 중복제거

select disctinct e.job_id, FROM EMPLOYEES E, DEPARTMENTS D  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_ID = 80;

 

3개 조인

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.LOCATION_ID, I.CITY FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS I  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = I.LOCATION_ID AND COMMISSTION IS NOT NULL;

 

이름에 a가 포함된거

SELECT E.LAST_NAME, D.DEPARTment_NAME FROM EMPLOYEES E, DEPARTMENTS D  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID LIKE E.LAST_NAME '%a%';

 

A join B on (식) 이런방법으로 조인 3개 조인하기

SELECT E.LAST_NAME, D.DEPARTment_NAME FROM EMPLOYEES E join DEPARTMENTS D on(E.DEPARTMENT_ID = D.DEPARTMENT_ID) join location I on(d.location_id = I.location_id) where I.city= 'toronote';

 

없는 사원도 포함이면 outer join을 사용해야한다

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;

where += 으로도 표현가능

 

 

아 join on 이 inner join임 default가 inner

inner 은 교집합 outer은 합집합이라고 생각하면됨 ㅇㅇ

select e1.department_id as "DEPARTMENT", e1.last_name as "EMPLOYEE", e2.last_name as "COLLEAGUE" from employees e1 inner join employees e2 on e1.department_id = e2.department_id where not e1.last_name = e2.last_name order by DEPARTMENT, EMPLOYEE, COLLEAGUE;

not = 을 <>으로도 표현 가능

 

 

davies의 입사일보다 늦은사람들 뽑아라

select last_name, hire_date from employees

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

 

 

관리자보다 먼저 입사한사람들 출력 

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

natural join은 알아서 애트리뷰트 이름이 동일한 놈 동등 조인시켜버리니까 이렇게 이름이 다를때는 따로 on에 명시해야된다.

 

 

실습 5

그룹화는 null무시한다

 

select max(salary) as "Maximum", min(salary) as "Minimum", sum(salary) as "Sum", round(avg(salary)) as Average from employees;

 

업무 유형별로 출력

select job_id, max(salary) as "Maximum", min(salary) as "Minimum", sum(salary) as "Sum", round(avg(salary)) as Average from employees group by job_id;

SELECT 절에 그룹 함수와 컬럼명이 같이 기술된 경우 해당 컬럼은 반드시 GROUP BY절에 그룹화 돼야 한다. 그니까 그룹함수랑 그냥 job_id랑 같이 쓰니까 애는 그룹화 해줘야됨

 

업무가 동일한 사원수 출력

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

 

관리자수

select count( distinct manager_id) as "Number of Managers" from employees

 

 

최고급여와 최저금액의 차액

/*5번*/ select (max(salary)-min(salary)) as "DIFFERENCE" from employees;

 

 

having으로 group에 대한 조건 달 수 있음 having에는 group에 대한 조건만 넣고 그거말고는 where에 넣어라

/*6번*/ select manager_id, min(salary) from employees where manager_id is not null group by manager_id having  min(salary) >= 6000 order by min(salary) desc;

 

 

평균급여랑 등등 그룹화 두개하는거

/*7번*/ 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 departments d, employees e where d.department_id = e.department_id group by d.department_name, d.location_id;

 

컬럼별로 집계값 만들기

/*8번*/ select count(*) as "TOTAL",

sum(decode(to_char(hire_date, 'yyyy'), '2005', 1, 0)) as "2005",

sum(decode(to_char(hire_date, 'yyyy'), '2006', 1, 0)) as "2006",

sum(decode(to_char(hire_date, 'yyyy'), '2007', 1, 0)) as "2007",

sum(decode(to_char(hire_date, 'yyyy'), '2008', 1, 0)) as "2008" from employees;

 

 

/*9번*/ select job_id as "Job",

sum(decode(department_id, '20', salary, 0)) as "Dept 20",

sum(decode(department_id, '50', salary, 0)) as "Dept 50",

sum(decode(department_id, '80', salary, 0)) as "Dept 80",

sum(decode(department_id, '90', salary, 0)) as "Dept 90",

sum(salary) as "Total"

from employees group by job_id;

 

 

 

실습 6

잘못된 경우

 

 

 

Zlotkey와 동일한 부서의 모든 사원

/*1번*/ 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;

 

이름에 u가 포함된 사원과 같은 부서에 일하는 모든 사원

/*3번*/ select employee_id, last_name from employees where department_id in (select department_id from employees where last_name like '%u%');

 

위치가 1700인 모든 사원의 이름

/*4번*/ select last_name, department_id, job_id from employees where department_id in (select department_id from departments where location_id = 1700);

 

manger id 가 킹인 사원

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

 

부서이름이 executive인 모든 사원 정보

/*6번*/ select department_id, last_name, job_id from employees where department_id in (select department_id from departments where department_name = 'Executive');

 

이름에 u가 들어가는 사원의 부서에 속하고 평균급여보다 급여가 높은 사원의 정보

/*7번*/ select employee_id, last_name, salary from employees where salary > (select avg(salary) from employees) and department_id in (select department_id from employees where last_name like '%u%');

 

위치가 미국인 사원의 평균 급여보다 높은 사람들

/*8번*/ select employee_id, last_name, salary from employees where salary > (select avg(salary) from employees where department_id in (select department_id from departments where location_id in (select location_id from locations where country_id = 'US')));

 

 

동등조인 때리고 그룹함수하니까 되긴되늗네 왜되는지는 모름 ㅋ 아싀팔 모르겠네 =은 무조건 row가 하나여지만되는데 오른쪽 서브쿼리는 최대 급여를 여러개 보내잖아 싀발모야~

실행순서가 where먼저하니까 그때 salary 튜플의 department가져와서 max를 하나씩 뽑는거가?

/*9번*/ select employee_id, last_name, salary, department_id from employees e1 where e1.salary = (select max(salary) from employees e2 where e1.department_id = e2.department_id) and department_id is not null order by department_id;

 

select  last_name,salary,department_id
    from employees e1
    where (salary,department_id)  in (select max(salary), department_id --in은 사용하면 중복된다. 사용x
                    from employees e2
                    group by e2.department_id)
    order by department_id asc;

난 이렇게 풀었는데 ㄹㅇ

 

 

 

 

/*10번*/ 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;

 

department의 투플이 sub쿼리안에 하나라도 있으면 출력한다.

/*11번*/ select department_id, department_name from departments d

where exists(select department_id from employees e where d.department_id = e.department_id);

 

 

 

/*12번*/ select rownum, employee_id, last_name, salary from (select employee_id, last_name, salary from employees order by salary) where rownum <= 5