인공지능 5/14

#SQL

#인공지능

 

Primary key(비밀 키)를 조인(join)을 해주었다.

 

 

비밀 키를 이용한 join을 했다.

위에있는 +를 양쪽 다 join하는 것은 full outer join이다.

 

자가 join

 

(select salary, LAST_NAME from EMPLOYEES
union
select MANAGER_ID, DEPARTMENT_NAME  from DEPARTMENTS)
minus
select MIN_SALARY, JOB_ID from jobs;
-- union : 중복되는 행을 한번만 출력 , union all : 중복되는 행을 그대로 출력
-- interset : 중복되는 행만 출력 , minus : 첫 번째 select 문의 조회결과에서 두 번째 조회 결과를 뺌

SELECT department_id FROM   employees
UNION
SELECT department_id FROM   departments;

SELECT department_id FROM   employees
where department_id > 100
UNION ALL
SELECT department_id FROM   departments
where department_id > 200
order by 1;

SELECT department_id
FROM   employees
INTERSECT
SELECT department_id
FROM   departments
ORDER BY department_id;

SELECT department_id
FROM   departments
MINUS
SELECT department_id
FROM   employees; 

select EMPLOYEE_ID from employees
minus
select EMPLOYEE_ID from JOB_HISTORY;

select min(salary) from EMPLOYEES;

select first_name from EMPLOYEES
where salary = (select min(salary) from employees); -- 서브쿼리는 괄호로 묶어서 사용한다. 중요☆
-- 단일행 서브쿼리 

SELECT *
FROM employees A
WHERE A.salary in(SELECT salary  FROM employees
                  WHERE last_name = 'Taylor');

SELECT *
FROM employees A
WHERE A.salary in (
                   SELECT MIN(salary) 최저급여
                   FROM employees
                   GROUP BY department_id
                  )
ORDER BY A.salary DESC;

 

select job_id, salary from EMPLOYEES --basic
where (job_id, salary) in(select 'AD_PRES', 24000 from dual);

SELECT *
FROM employees A
WHERE (A.job_id, A.salary) IN ( SELECT job_id, MIN(salary) 그룹별급여  FROM employees
                               GROUP BY job_id)
ORDER BY A.salary DESC;

 

 

select * from employees,jobs;

select * from employees,
(select to_char(hire_date,'YYYY'), sum(salary) 
from employees group by to_char(hire_date,'YYYY')) B;

SELECT *
FROM employees A,
                  ( SELECT  department_id
                    FROM    departments
                    WHERE   department_name = 'IT') B
WHERE A.department_id = B.department_id;

테이블과 값을 만들어주었다.

테이블에 데이터를 넣어주었다.

 

 

 

select * from DEPARTMENTS
where DEPARTMENT_NAME='Sample_Dept'; -- 값 찾기

update hr.DEPARTMENTS
set manager_id=201 , location_id=1800
where DEPARTMENT_NAME='jino';  -- 값을 업데이트

delete from hr.departments
where department_name='Sample_Dept';
select * from DEPARTMENTS; -- 값 삭제

 

Ex) 테이블만들기

테이블 - 마우스 오른쪽키 - 새 테이블 생성

테이블이랑 열을 생성해주었다.

 

주요키와 널값, 기본키 설정을 할 수 있다.

 

 

테이블 생성

 

행 삽입