Portfolio


# 1
SELECT concat(firstName, lastName) as Name
FROM offices o join employees e on o.officeCode = e.officeCode
WHERE o.Country = 'UK';

# 2
SELECT concat(firstName, lastName) as Name
FROM offices o join employees e using (officeCode)
WHERE o.Country = 'UK';

# 3
SELECT concat(firstName, lastName) as Name
FROM offices o natural join employees e
WHERE o.Country = 'UK';

lastName이 'Patterson'인 직원이 담당하는 고객회사들의 이름(customerName) 출력

solution


SELECT c.customerName
FROM   customers c
Join   employees e on c.salesRepEmployeeNumber = e.employeeNumber
WHERE  e.lastName = 'Patterson';

담당 직원이 배정되지 않은 고객회사의 이름 출력

solution


select c.customerName
from customers c
where c.salesRepEmployeeNumber is null;

각 고객 회사에 대해 회사명, 주문 회수, 평균 주문 금액, 최대 주문 금액 출력

solution


#1
select c.customerName, count(o.orderNumber) as 주문수,
       count(distinct o.orderNumber) as 주문수,
       round(avg(temp.OrderAmount),2) as 평균주문금액,
       round(max(temp.OrderAmount),2) as 최대주문금액
from (
	select od.orderNumber,
         sum(od.quantityOrdered * od.priceEach) as OrderAmount
	from orderdetails od
	group by od.orderNumber
) temp join Orders o on temp.orderNumber = o.orderNumber
       join customers c on o.customerNumber = c.customerNumber
group by c.customerNumber, c.customerName
order by c.customerName;

#2
select c.customerName, count(o.orderNumber) as 주문수,
       count(distinct o.orderNumber) as 주문수,
       round(avg(temp.OrderAmount),2) as 평균주문금액,
       round(max(temp.OrderAmount),2) as 최대주문금액
from (
	select od.orderNumber,
				 sum(od.quantityOrdered * od.priceEach) as OrderAmount
	from orderdetails od
	group by od.orderNumber
) temp natural join Orders o 
natural join customers c 
group by c.customerNumber, c.customerName
order by c.customerName;

가장 많은 주문 금액의 주문의 고객회사 명, 주문 날짜, 주문금액 출력

solution


# 변수 사용 방법
set @OrderNo = (select  od.orderNumber
                from OrderDetails od
                group by od.orderNumber
                Order By sum(od.priceEach *od.quantityOrdered) desc
                limit 1);
set @OrderTotal = (select sum(od.priceEach *od.quantityOrdered)  
                   from orderDetails od
						 where od.orderNumber = @OrderNo
						 );              

select @OrderNo, @OrderTotal;
 
select concat(E.FirstName, ' ', E.LastName) as 직원이름,
       @OrderTotal as 주문금액,
       O.OrderDate as 주문날짜,
       C.CustomerName as 고객회사
from (Orders O join Customers C on O.customerNumber = C.customerNumber)
     join Employees E on C.salesRepEmployeeNumber = E.EmployeeNumber
where O.OrderNumber = @OrderNo
;


# 중첩 SQL 사용
select concat(E.FirstName, ' ', E.LastName) as 직원이름,
       (select sum(od2.quantityOrdered * od2.priceEach) from orderDetails od2 where od2.OrderNumber = o.orderNumber) as 주문금액,
       O.OrderDate as 주문날짜,
       C.CustomerName as 고객회사
from (Orders O join Customers C on O.customerNumber = C.customerNumber)
     join Employees E on C.salesRepEmployeeNumber = E.EmployeeNumber
where o.orderNumber =  ( select od1.OrderNumber
                         from orderDetails OD1
                         group by Od1.OrderNumber 
		  			          Order by sum(od1.quantityOrdered * od1.priceEach) desc
								 limit 1) ;


# 가상테이블 사용
select concat(E.FirstName, ' ', E.LastName) as 직원이름,
       temp.OrderAmount as 주문금액,
       O.OrderDate as 주문날짜,
       C.CustomerName as 고객회사
from (
      select od.orderNumber, sum(od.priceEach * od.quantityOrdered) as OrderAmount
      from OrderDetails od
      group by od.OrderNumber) temp 
		join Orders o on temp.orderNumber = o.orderNumber
		join Customers c on o.customerNumber = c.customerNumber
		join Employees e on c.salesRepEmployeeNumber = e.employeeNumber
order by temp.OrderAmount DESC
limit 1
;                     



# 반환 결과가 1이상인 경우
select concat(E.FirstName, ' ', E.LastName) as 직원이름,
       sum(od.quantityOrdered * od.priceEach) as 주문금액,
       O.OrderDate as 주문날짜,
       C.CustomerName as 고객회사
from Orders O, Employees E, Customers C, orderDetails od
where O.OrderNumber = od.OrderNumber and 
      C.SalesRepEmployeeNumber = E.EmployeeNumber and 
      O.CustomerNumber = C.CustomerNumber
group by O.OrderNumber, E.FirstName, E.LastName, O.OrderDate, C.CustomerName
having sum(od.quantityOrdered * od.priceEach) >= all ( select sum(Od.priceEach * Od.quantityOrdered)
                                                 from OrderDetails OD
                                                 group by Od.OrderNumber
                                               )
;

현실적으로, 위와 같은 JOIN을 작성해놨는데 변경할 일이 생긴다면 정상적으로 빠르게 작동하는 쿼리로 만들 수 있을까?

Transaction

ACID

Atomicity

원자성
트랜잭션과 관련된 일은 모두 실행되던지 모두 실행되지 않도록 하던지를 보장하는 특성이다.

Consistency

일관성
트랜잭션이 성공했다면, 데이터베이스는 그 일관성을 유지해야 한다. 일관성은 특정한 조건을 두고, 그 조건을 만족하는지를 확인하는 방식으로 검사할 수 있다.

Isolation

독립성
트랜잭션을 수행하는 도중에 다른 연산작업이 끼어들지 못하도록 한다. 임계영역을 두는 것으로 달성할 수 있다.

Durability

성공적으로 트랜잭션이 수행되었다면, 그 결과는 완전히 반영이 되어야 한다.
완전히 반영되면 로그를 남기게 되는데, 후에 이 로그를 이용해서 트랜잭션 수행전 상태로 되돌릴 수 있어야 한다. 때문에 트랜잭션은 로그저장이 완료된 시점에서 종료가 되어야 한다.

정규화

어느 수준까지 정규화를 해야 할까?

역정규화가 필요한 순간은?

ERD

캠퍼들이 작성한 ERD 피드백

CloudERD

나중에 협업할 때 써먹으면 좋다.

성능 관련

클러스터링

읽기
쓰기
main(master)
sub(slave)
동기화

샤딩

Caching

관점을 다르게 해야 한다.

DB를 사용하지 않는게, 접근하지 않는게 제일 좋은게 아닐까?

실시간으로 데이터가 필요한 경우는 어떤 경우일까?

결제 서비스
주문 서비스
채팅 서비스
차트 서비스

반대로, 실시간 데이터가 필요하지 않은 경우는 어떤 경우일까?

포털 서비스

airbnb는 어떨까?

가계부는 어떨까?

3주차

무엇이든 궁금한점 (20분)

Database

NodeJS와 MySQL

기본 명령어

Table JOIN

Transaction

정규화

ERD

CloudERD

성능 관련

Caching