💝 [ORACLE]_23.02.24

• CMD 상 SQLPLUS 접속 수 스크립트 연동 방법
  
  🔽 명령어 입력

  sqlplus ora1/oracle_4U@localhost:1521/xepdb1
  conn ora1/oracle_4U@localhost:1521/xepdb1
  @C:\adsql_labs\파일명

• decode 함수
  

🧡 3day 복습 문제

-- 3day 복습 문제 SOL 

--1. 사원이름, 월급, 커미션, 수령액(월급 + 커미션)을 출력하시오.
--  (수령액 계산시 커미션이 적용되지 않는 사원의 경우는 커미션을 
--   일괄적으로 100을 적용합니다.)

select ename, sal, comm, sal+nvl(comm,100)
from emp;


--2. 이름, 커미션을 출력하는데 커미션이 null 인 사원들은 no comm 이란 
--   문자열로 출력되게하시오.


select ename, nvl(to_char(comm),'no_comm') comm
from emp;
 

--3. 이름, 입사한 년도(4자리), 월급, 보너스를 출력하는데
--   보너스가 입사한 년도가 1981 년도면 5000 을 출력하고
--   나머지 사원들은 0 을 출력하시오.


 select  ename, to_char(hiredate,'RRRR'),  sal,
   decode( to_char(hiredate,'RRRR'),'1981', 5000, 0) 보너스
  from  emp;
--
 select  ename, to_char(hiredate,'RRRR'),  sal,
   CASE WHEN to_char(hiredate,'RRRR') = '1981' THEN 5000
   ELSE 0 
   END 보너스
  from  emp;

--
select  ename, to_char(hiredate,'RRRR'),  sal,
   case to_char(hiredate,'RRRR')
      when '1981' then 5000
      else 0 end 보너스
  from  emp;
  
--4. 이름, 월급과 보너스를 출력하는데 
--   월급이 3000 이상이면 보너스를 6000 을 출력하고 
--   월급이 3000  보다 작으면 보너스를 0 을 출력하시오.


select  ename, sal, 
            case  when sal >= 3000  then  6000
                  else  0  end  as  bonus
from  emp;


--5. 이름, 커미션, 보너스를 출력하는데 보너스가 
--   커미션이 null 이면 7000 을 출력하고 
--   커미션이 null 이 아니면  0 을 출력하시오.


 select  ename, comm,
        case  when comm is null  then  7000
              else   0  end  as bonus
   from  emp;



--6. 이름, 월급 , 직업, 보너스를 출력하는데
--         직업이 SALESMAN 이고 월급이 1000 이상이면 보너스를 9000 을 출력하고 
--         직업이 ANALYST 이고 월급이 2500 이상이면  보너스를 8000 을 출력하고 
--         나머지 사원들은 0 을 출력하시오.


 select  ename, sal, job, 
  case  when  ( job='SALESMAN' and sal >= 1000)  then 9000
        when  ( job='ANALYST'  and sal >= 2500)  then 8000
        else  0  end  as bonus
from  emp;


--7. 부서번호, 부서번호별 최대월급을 출력하는데
--        부서번호별 최대월급이 높은것 부터 출력하시오.



select  deptno, max(sal)
 from   emp
 group  by deptno
 order  by  max(sal)  desc;



--8. 위의 결과에서 부서번호 20번은 제외하고 출력하시오.

-- 부서번호 20이 아닌 것들을 선별해 그룹핑 

select  deptno, max(sal)
 from   emp
where deptno !=20
 group  by deptno
 order  by  max(sal)  desc;
 
-- 아래 HAVING 절은 실행 순서가 WHERE과 상이한 점에 유의 
-- 그룹핑한 후 부서번호 20이 아닌 것들을 제외 

select  deptno, max(sal)
 from   emp
--where deptno !=20
 group  by deptno
 HAVING DEPTNO !=20 
 order  by  max(sal)  desc;

--9. 직업, 직업별 토탈월급을 출력하는데
--         직업이 SALESMAN 인 사원들을 제외하고 출력하고
--         직업별 토탈월급이 높은것부터 출력하는데 
--         직업별 토탈월급을 출력할때에 천단위를 부여해서
--         출력하시오.


select job, to_char(sum(sal),'99,999') "SUM(SAL)"
from emp
where job != 'SALESMAN'
group by job
order by sum(sal) desc;


--10. 부서번호, 부서번호별 토탈월급을 출력하시오.
--
select deptno, sum(sal)
 from  emp
 group  by deptno;



--11. 위의 결과를 가로로 출력하시오.
--
--
-- 이 문제는 emp 테이블에 부서번호가 10번 20번 30번이
--  있다는것을 안다라는 가정하에 SQL 을 작성하면 됨

select sum(decode(deptno,10,sal,0)) "10",
       sum(decode(deptno,20,sal,0)) "20",
       sum(decode(deptno,30,sal,0)) "30"
from emp;


--12. 직업, 부서번호, 직업별 부서번호별 토탈월급을 출력하시오.

select job,
sum(decode(deptno,10,sal)) as "10",
sum(decode(deptno,20,sal)) as "20",
sum(decode(deptno,30,sal)) as "30"
from emp
group by job;

🧡 SQL_PRAC 실습

--1) 
SELECT  E.EMPLOYEE_ID, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.LOCATION_ID 
FROM EMPLOYEES E JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

--2)
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D JOIN LOCATIONS L 
ON D.LOCATION_ID = L.LOCATION_ID
WHERE DEPARTMENT_ID IN (20, 50);

SELECT *
FROM LOCATIONS;

--3) 
SELECT E.LAST_NAME, D.DEPARTMENT_NAME, L.CITY 
FROM EMPLOYEES E JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

--4) 
SELECT E1.LAST_NAME E_NAME, E2.LAST_NAME M_NAME
FROM EMPLOYEES E1 
JOIN EMPLOYEES E2
ON E1.EMPLOYEE_ID = E2.MANAGER_ID;

--5) 
SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

--6) 
SELECT D.DEPARTMENT_ID, E.LAST_NAME, E.SALARY
FROM EMPLOYEES E 
JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

--7) 
SELECT D.DEPARTMENT_ID, E.LAST_NAME, E.SALARY
FROM EMPLOYEES E 
RIGHT OUTER JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND SALARY >= 10000
ORDER BY DEPARTMENT_ID;
-- WHERE, AND 차이 -> 명령어 처리 순서가 OUTER 조인 먼저, 필터링 진행 (부서에 대한 내용이 필터링) 
-- AND 연산 시 조건 실행 후 OUTER JOIN 실행

--8) 비조인 조건으로 다시 풀기 
SELECT E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E 
JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L 
ON D.LOCATION_ID = L.LOCATION_ID 
WHERE L.CITY = 'Toronto'; 

--9) 
SELECT E1.LAST_NAME "EMPLOYEE", E1.EMPLOYEE_ID "EMP#", E2.LAST_NAME, E2.MANAGER_ID "Mgr#"
FROM EMPLOYEES E1 
LEFT OUTER JOIN EMPLOYEES E2 
ON E1.MANAGER_ID = E2.EMPLOYEE_ID;


--10) 비조인 조건
SELECT E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_NAME, E.SALARY, J.GRADE_LEVEL
FROM EMPLOYEES E , DEPARTMENTS D , JOB_GRADES J 
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND  E.SALARY
BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL; 

-- 비조인 조건은 반드시 AND로 연결해야함 (WHERE은 안됨) 

--11) ?? 
@C:\ADSQL_labs\emp_kind

select e.empno, e.ename, e.emp_kind, k1.office_sal, k2.sal
from emp_kind e, emp_kind1 k1, emp_kind2 k2
where e.empno=k1.empno(+)
and e.empno=k2.empno(+)
order by e.emp_kind;


--12) 모르겠음 -> 셀프 조인으로 해결 (+. 비동등 조인 활용) 
SELECT E.EMPNO, E.ENAME, E.SAL, J.SAL "JONES SALARY"
FROM EMP E
JOIN EMP J 
ON E.EMPNO = J.EMPNO  
WHERE E.SAL > J.SAL ;
 
select e.empno, e.ename, e.sal, j.sal as "Jones's Salary"
from emp e, emp j
where upper(j.ename)='JONES'
and j.sal < e.sal;




--13) 모르겠음 
SELECT DISTINCT(D.DEPTNO) DEPTNO, D.DNAME, D.LOC, 
    CASE WHEN ENAME IS NOT NULL THEN 'YES' ELSE 'NO' END EMP 
FROM DEPT D
LEFT OUTER JOIN EMP E 
ON D.DEPTNO = E.DEPTNO;

--SOL 
select d.deptno, d.dname, d.loc, decode(count(e.deptno),0,'NO','YES') as emp
from dept d, emp e
where d.deptno=e.deptno(+)
group by d.deptno, d.dname, d.loc
order by d.deptno

--14) 모르겠음 
SELECT E.FIRST_NAME, E.LAST_NAME, E.SALARY, E.JOB_ID, C.COUNTRY_NAME  
FROM EMPLOYEES E , countries C
WHERE COUNTRY_NAME = 'Canada'
AND E.SALARY BETWEEN >= 6000 AND <= 13000; 

--SOL 징검다리 테이블 (4개 테이블 조인 필) E, D테이블 간 연결고리로서 L,C 조인 수행 (ERD 참고) 
select e.first_name, e.last_name, e.salary, e.job_id, c.country_name
from employees e,
	departments d
,  locations l
, 	countries c
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.country_id=c.country_id
and initcap(c.country_name)='Canada';

--15) 모르겠음 
-- GROUP BY 행에는 SELECT 절에 언급된 모든 속성 (P.PROD_ID, P.PROD_NAME) 기입 필 
SELECT  P.PROD_ID, P.PROD_NAME, SUM(S.QUANTITY_SOLD) SOLD_SUM 
FROM PRODS P
LEFT OUTER JOIN SALES S
ON P.PROD_ID = S.PROD_ID
GROUP BY P.PROD_ID, P.PROD_NAME;

--SOL 

select p.prod_id, p.prod_name, sum(s.quantity_sold) as sold_sum
from prods p, sales s
where p.prod_id=s.prod_id(+)
group by p.prod_id, p.prod_name;

🧡 SQL_PRAC 해답

🔽 3DAY_2

--1
SELECT 	e.employee_id, e.last_name, e.department_id,
        d.department_id, d.location_id
FROM    employees e , departments d
WHERE 	e.department_id = d.department_id;

SELECT 	e.employee_id, e.last_name, e.department_id,
        d.department_id, d.location_id
FROM 	employees e JOIN departments d
ON 	    e.department_id = d.department_id;

--2
SELECT 	d.department_id, d.department_name, l.city
FROM 	departments d, locations l
WHERE 	d.location_id = l.location_id
AND 	d.department_id IN (20, 50);

SELECT 	d.department_id, d.department_name, l.city
FROM 	departments d JOIN locations l
ON 	    d.location_id = l.location_id
AND 	d.department_id IN (20, 50);

--3
SELECT 	e.last_name, d.department_name, l.city
FROM 	employees e , departments d, locations l
WHERE 	e.department_id = d.department_id
AND 	d.location_id = l.location_id;

SELECT 	e.last_name, d.department_name, l.city
FROM 	employees e JOIN departments d
ON 	    e.department_id = d.department_id
JOIN    locations l
ON      d.location_id = l.location_id;

--4
SELECT  e.last_name e_name, m.last_name m_name
FROM    employees e , employees m
WHERE   e.manager_id=m.employee_id;

SELECT  e.last_name e_name, m.last_name m_name
FROM    employees e JOIN employees m
ON      e.manager_id=m.employee_id;

--5
SELECT 	e.last_name, e.department_id, d.department_name
FROM 		employees e, departments d
WHERE 	e.department_id = d.department_id(+) ;

SELECT 	e.last_name, e.department_id, d.department_name
FROM 	employees e LEFT OUTER JOIN departments d
ON      e.department_id = d.department_id ;


--6
SELECT 	d.department_id, e.last_name, e.salary
FROM 	employees e, departments d
WHERE 	e.department_id(+) = d.department_id;


SELECT 	d.department_id, e.last_name, e.salary
FROM 	employees e RIGHT OUTER JOIN departments d
ON  	e.department_id = d.department_id;


--7
SELECT 	d.department_id, e.last_name, e.salary
FROM 		employees e, departments d
WHERE 	e.department_id(+) = d.department_id
and   e.salary(+) > 10000;

SELECT 	d.department_id, e.last_name, e.salary
FROM 	employees e RIGHT OUTER JOIN departments d
ON 	    e.department_id = d.department_id
and     e.salary > 10000;

--8
SELECT   e.last_name, e.job_id, e.department_id, d.department_name
FROM     employees e, departments d, locations l
WHERE    e.department_id=d.department_id
AND      d.location_id=l.location_id
AND      lower(l.city)='toronto';

SELECT   e.last_name, e.job_id, e.department_id, d.department_name
FROM     employees e JOIN departments d
ON       e.department_id=d.department_id
JOIN     locations l
ON       d.location_id=l.location_id
AND      lower(l.city)='toronto';

--9
SELECT   e.last_name "Employee", e.employee_id "EMP#",
         m.last_name "Manager", m.employee_id "Mgr#"
FROM     employees e, employees m
WHERE    e.manager_id=m.employee_id(+);

SELECT   e.last_name "Employee", e.employee_id "EMP#",
         m.last_name "Manager", m.employee_id "Mgr#"
FROM     employees e LEFT OUTER JOIN employees m
ON e.manager_id=m.employee_id;


--10
SELECT e.LAST_NAME, e.job_id, d.department_name,
       e.salary, j.grade_level
FROM   employees e, departments d, job_grades j
WHERE  e.department_id=d.department_id
AND    e.salary between j.lowest_sal and j.highest_sal;

SELECT e.LAST_NAME, e.job_id, d.department_name,
       e.salary, j.grade_level
FROM   employees e JOIN departments d
ON      e.department_id=d.department_id
JOIN   job_grades j
ON      e.salary between j.lowest_sal and j.highest_sal;

11
select e.empno, e.ename, e.emp_kind, k1.office_sal, k2.sal
from emp_kind e, emp_kind1 k1, emp_kind2 k2
where e.empno=k1.empno(+)
and e.empno=k2.empno(+)
order by e.emp_kind;

select e.empno, e.ename, e.emp_kind, k1.office_sal, k2.sal
from emp_kind e left join emp_kind1 k1
on e.empno=k1.empno
left join emp_kind2 k2
on e.empno=k2.empno
order by e.emp_kind;


12
select e.empno, e.ename, e.sal, j.sal as "Jones's Salary"
from emp e, emp j
where upper(j.ename)='JONES'
and j.sal < e.sal;

select e.empno, e.ename, e.sal, j.sal as "Jones's Salary"
from emp e join emp j
on upper(j.ename)='JONES'
and j.sal < e.sal;

13
select d.deptno, d.dname, d.loc, decode(count(e.deptno),0,'NO','YES') as emp
from dept d, emp e
where d.deptno=e.deptno(+)
group by d.deptno, d.dname, d.loc
order by d.deptno
/

select d.deptno, d.dname, d.loc, decode(count(e.deptno),0,'NO','YES') as emp
from dept d left outer join emp e
on d.deptno=e.deptno
group by d.deptno, d.dname, d.loc
order by d.deptno;


14
select e.first_name, e.last_name, e.salary, e.job_id, c.country_name
from employees e,
	departments d
,  locations l
, 	countries c
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.country_id=c.country_id
and initcap(c.country_name)='Canada';

select e.first_name, e.last_name, e.salary, e.job_id, c.country_name
from employees e
join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
join countries c
on l.country_id=c.country_id
where initcap(c.country_name)='Canada';

15
select p.prod_id, p.prod_name, sum(s.quantity_sold) as sold_sum
from prods p, sales s
where p.prod_id=s.prod_id(+)
group by p.prod_id, p.prod_name;

select p.prod_id, p.prod_name, sum(s.quantity_sold) as sold_sum
from prods p left outer join sales s
on p.prod_id=s.prod_id
group by p.prod_id, p.prod_name;

🧡 8장 서브쿼리.

• MAIN 명령어 내 괄호로 표기하는 서브 쿼리
• Subquery를 포함하면 명령어는 조인처럼 여러 테이블을 참조할 수 있습니다

-- 
SELECT * 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID  = (SELECT DEPARTMENT_ID 
                    FROM EMPLOYEES 
                    WHERE EMPLOYEE_ID = 141) 
AND EMPLOYEE_ID NOT IN (141) ;

--

--SELECT EMPLOYEE_ID, MIN(SALARY)
--FROM EMPLOYEES 
--GROUP BY EMPLOYEE_ID;

SELECT *
FROM EMPLOYEES 
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES);
-- 

SELECT DEPARTMENT_ID, MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) > (SELECT MIN(SALARY) FROM EMPLOYEES); 



-- 100, 999
-- 서브쿼리 리턴 값이 NULL인 경우-> NVL함수 활용한 수정 필 
select last_name, commission_pct
from employees
where commission_pct = (select commission_pct
                    from employees
                    where employee_id=100);
                    
    -- 서브쿼리 리턴 값이 NULL인 경우                
                    select commission_pct
                    from employees
                    where employee_id=100;
                    
-- 서브쿼리 리턴 값이 NULL인 경우-> NVL함수 활용한 수정 필 
select last_name, commission_pct
from employees
where nvl(commission_pct,0) = (select nvl(commission_pct,0)
                    from employees
                    where employee_id=100);
-- 
select last_name, commission_pct
from employees
where nvl(commission_pct,0) = (select nvl(commission_pct,0)
                    from employees
                    where employee_id=999);
            
            select nvl(commission_pct,0)
                    from employees
                    where employee_id=999;
                    
-- 서브쿼리 반환 값이 여러개인 경우 '=' 아닌 
-- IN 연산자 OR '=ANY' 사용 필 

SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
 FROM employees
GROUP BY department_id);

-- ALL 어떤 값과 비교해도 그 값보다 작아야 한다
-- ALL보다 작은 것은 MIN 보다 작다
------------------------------- 
SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < ANY (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-- OR 
SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < MAX (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-----------------------------------
SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > ANY (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

-- OR 

SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > MIN (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
---------------------------------
SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < ALL (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

-- OR 

SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < MIN (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
---------------------------------
SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > ALL (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

-- OR 

SELECT 
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > MAX (9000,6000,4200)
(SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-----------------------------

• Multiple-Row Subquery
  • <ANY 는 최대값보다 작음을 의미합니다.
  >ANY 는 최소값보다 큼을 의미합니다.
  =ANY 는 IN과 같습니다.
  • <ALL 은 최소값보다 작음을 의미합니다.
  >ALL 은 최대값보다 큼을 의미합니다.

-- ALL 어떤 값과 비교해도 그 값보다 작아야 한다
-- ALL보다 작은 것은 MIN 보다 작다
-------------------------------
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < ANY (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-- OR
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < MAX (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-----------------------------------
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > ANY (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-- OR
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > MIN (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
---------------------------------
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < ALL (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-- OR
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary < MIN (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
---------------------------------
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > ALL (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-- OR
SELECT
FROM
WHERE
employee_id, last_name, job_id, salary
employees
salary > MAX (9000,6000,4200)
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
-----------------------------