패스트캠퍼스 챌린지 23일차

시청 강의: SQL(2)
시청 날짜 : 11/23/2021

요즘 SQL 쓸일이 꽤 있는데, 마침 SQL 강좌가 있길래 마저 들어보았다.

순위집계

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

1. Rank

SELECT RANK() OVER(PARTITION BY [그룹할 컬럼들] ORDER BY [순위를 매길때 사용할 컬럼들]) 
FROM [테이블1]

2. DENSE_RANK

SELECT DENSE_RANK() OVER (PARTITION BY[그룹할 컬럼들] ORDER BY [순위를 매길 때 사용할 컬럼들])
FROM [테이블1]

3. ROW_NUMBER

    SELECT ROW_NUMBER() OVER (PARTITION BY[그룹할 컬럼들] ORDER BY [순위를 매길 때 사용할 컬럼들])
    FROM [테이블1]

https://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/
위 홈페이지를 들어가면 튜토리얼을 볼 수 있다.

JOIN

JOIN에는 LEFT, OUTER, INNER, SELF 조인이 있다.
아래 예시를 봤을 때,

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);

INNER JOIN

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products t1
INNER JOIN productlines t2 
    ON t1.productline = t2.productline;

LEFT JOIN

SELECT 
    customers.customerNumber, 
    customerName, 
    orderNumber, 
    status
FROM
    customers
LEFT JOIN orders ON 
    orders.customerNumber = customers.customerNumber;

RIGHT JOIN

SELECT 
    employeeNumber, 
    customerNumber
FROM
    customers
RIGHT JOIN employees 
    ON salesRepEmployeeNumber = employeeNumber
ORDER BY 
	employeeNumber;

CROSS JOIN

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(13,2 )
);

CREATE TABLE stores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_name VARCHAR(100)
);

CREATE TABLE sales (
    product_id INT,
    store_id INT,
    quantity DECIMAL(13 , 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (product_id , store_id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

---

패스트캠퍼스 환급 챌린지 바로가기👉 https://bit.ly/3FVdhDa

수강료 100% 환급 챌린지 | 패스트캠퍼스

본 포스팅은 패스트캠퍼스 환급 챌린지 참여를 위해 작성되었습니다.