Key points

0. Database Objects
1. Self-Join
2. Outer-Join
3. Sub-Query
4. Practice
5. View
6. Sequence
7. INDEX

---

<span style="color:#ffffff", "background:#3d3d3d"> 1. SELF-JOIN 

📌 Defination

📌 Exmaple

Example 1.

부서 이름과 상위 부서 이름을 조회하라

• 🤔 ⅰ Joinned column
  

• a and b have exactly same columns

• Sales table has plural redundant columns which should be joinned to other tables.
  So where contains multiple sentances.
  Just link them using and

• 👉 Code_
  where Sales.prod_id=Products.prod_id
  and Sales.cust_id=Customers.cust_id
  and Sales.channel_id=Channels.channel_id
  and Sales.employee_id=Employees.employee_id;
  
  

• 🤔 ⅱ Columns should be selected

• from the whole 5 tables : prod_name, cust_name, channel_desc, emp_name

• Each tables and columns have different names.

• Set table names as A, B, C, D, E

• 👉 Code_
  select prod_name, cust_name, channel_desc, emp_name
  from Sales A, Products B, Customers C, Channels D, Employees E

😎 ⅲ Final code
select prod_name, cust_name, channel_desc, emp_name
from Sales A, Products B, Customers C, Channels D, Employees E
where A.prod_id=B.prod_id
and A.cust_id=B.cust_id
and A.channel_id=D.channel_id
and A.employee_id=E.employee_id;

 2. OUTER-JOIN 

📌 Defination

• Equi-Join +

 3. Sub-Query 

📌 Defination

• select from문 자체도 임시 table이라 생각하면 됨

• 👉 Sub Query가 from 에 있을 때(Inline view)

• select E.employee_id, E.emp_name
  from (select employee_id, emp_name from employees) E
  where .....

	Sub Query
	(select employee_id, emp_name from employees) E
	==> Create ~~temporary~~ E table.
	    Can be used in main query.

• 👉 Sub Query가 where에 있을 때(중첩 query)
  select D. department_name
  from departments D
  where D.department_id in (select departmnet_id from employees where salary between 5000 and 10000)

	Sub Query
	(select departmnet_id from employees where salary between 5000 and 10000)
	==> Create temporary table.(table has no name) 
	    CANNOT be used in main query.
	    It is similar to Java local values.

	int field_value = 0;  //field(member value)
    	field_value=temp;  // XXX (Because local value is only available in the method where it was created)
        
        public void method_ex(){
				int temp = 30;  //local value
                		temp=field_value;  // O
                		}

 4. Practice 

📌 Menual

• 1. write and test main query

2. Set the sub-query location
3. 조건인지 칼럼 목록인지에 따라 서브쿼리 위치 선정
4. 서브쿼리 작성/실행
5. 메인과 서브쿼리 조합

 5. View 

• The ONLY thing you can do with view is VIEWING
  insert, delete... are not available.

📌 Create view

 7. INDEX 

• 📌 Purpose of index
  For streamlined and fast search

• 📌 Purpose of index
  More indexes, faster search.
  However, if the table was inserted new data, index table should be update/delete

 8. Team project example 

• 📌 database
  Member information table

• id
  pw
  name
  gender
  DOB
  mobile
  last log out
  last log in

• Delivery table (seperate from the member information table)
  id/ seq/ address/ post code
  id/ seq/ address/ post code
  id/ seq/ address/ post code

• Get the whole member information
  JOIN Member information and Delivery table
  Usually the number of address is different by members and save memory.

---

select a.employee_id, a.emp_name, b.emp_name manager_name
from employees a, employees b
where a.manager_id=b.employee_id
order by manager_name;

select a.employee_id, a.emp_name, a.salary, b.emp_name manager_name, b.salary manager_salary
from employees a, employees b
where a.manager_id=b.employee_id
order by a.employee_id;

-- 매니저별 부하직원 연봉합계
select b.emp_name, sum(a.salary)
from employees a, employees b
where b.employee_id=a.manager_id
group by b.emp_name
order by b.emp_name;

--
select a.employee_id, a.emp_name, b.emp_name manager_name
from employees a, employees b
where a.manager_id=b.employee_id;

select * from departments;
select a. department_name, b. department_name parent_dept
from departments a, departments b
where a.parent_id=b.department_id;

-- department id를 출력하되, parent id가 90인 department_id만 출력
select a.department_id, to_char(avg(a.salary),'99,999.99')
from employees a
where department_id in(select department_id from departments
where parent_id =90)
group by department_id;
-- 'where department_id in(60,70,100,110)' is the same meaning of the above clause

select from employees; --department_id, salary
select from departments; --department_name, parent id

-- department_id 90의 직속부서명(parent id가 90인 부서들)과 avg salary 출력
--select b.department_name, to_char(avg(a.salary),'99,999.99')
--from employees a, departments b
--where a.department_id in
-- (select department_id from departments where parent_id =90)
--and a.department_id = b.department_id
--group by b.department_name; 틀린부분

select a.department_id, b.department_name, to_char(avg(a.salary),'99,999.99')
from employees a, departments b
where a.department_id in(select department_id from departments
where parent_id =90)
and a.department_id=b.department_id
group by a.department_id, b.department_name;

--create view(only select)
create or replace view v_person as
select a.department_id, to_char(avg(a.salary),'99,999.99') salary
from employees a, departments b
where a.department_id in (select department_id from departments
where parent_id=90)
and a.department_id=b.department_id
group by b.department_name;
select * from v_person;

-- synonym : for security, make nick names for tables. can be revised
create or replace synonym person_nick for person;
select * from person_nick;

-- sequence
truncate table person;
select * from person;
alter table person add id number;
create sequence seq_id start with 1 increment by 1;
drop sequence seq_id;
select seq_id.nextval from dual;
insert into person values('John','M','20201028','LA',seq_id.nextval);

--index : index usually do 'sequental search'
select employee_id from employees order by employee_id;

--create index
select distinct prod_id from sales where cust_id=387 and prod_id=30 and employee_id=10;
create index inx_cpe on sales(cust_id, prod_id, employee_id);