기본 문법

• 기본적으로 sql문에서는 distinct를 따로 지정해줘야 중복성이 사라진다.

CREATE

CREATE TABLE IF NOT EXISTS table_name();
CREATE OR REPLACE TABLE table_name();

UPDATE

UPDATE TABLE A SET col1="" WHERE value>100;
UPDATE TABLE A SET col1="" ORDER BY col1 [ASC|DESC] LIMIT 3;

INSERT

INSERT INTO a SELECT * FROM b;
INSERT INTO table_name SET col1="", col2="", ...;
INSERT INTO table_name SELECT * FROM table_name;
INSERT INTO table_name SELECT col1, col2 FROM table_name;

SELECT

# sql에서 default는 ALL 이지만, relational algebra에서는 DISTINCT가 default다
SELECT ... UNION [ALL | DISTINCT] SELECT ... ;
SELECT ... INTERSECT [ALL | DISTINCT] SELECT ... ;
SELECT ... EXCEPT ... ;

SELECT ... FROM .. ORDER BY col_name [ASC, DESC], ...;
# n1부터 n2개 출력 - n1은 0부터 시작
SELECT * FROM table_name LIMIT 3,1;

DELETE

DELETE FROM table_name ORDER BY col LIMIT k;

pt_works

like

SELECT * FROM pt_works WHERE branch_name LIKE "%ry%";

REGEX

SELECT * FROM pt_works WHERE salary REGEXP '^[0-9]*4';

특정 데이터가 존재하는가?

SELECT "Perryridge" in (SELECT branch_name from pt_works");

SELECT 1 IN (1,2,3);

각 브랜치별 샐러리의 합?

▢𝑏𝑟𝑎𝑛𝑐h_𝑛𝑎𝑚𝑒𝒢𝑐𝑜𝑢𝑛𝑡 𝑏𝑟𝑎𝑛𝑐h_𝑛𝑎𝑚𝑒 (𝑝𝑡_𝑤𝑜𝑟𝑘𝑠)

SELECT branch_name, SUM(salary) FROM pt_works GROUP BY branch_name;

#브랜치별 샐러리 합을 기준으로 내림차순
SELECT branch_name, sum(salary) FROM pt_works group by sum(salary) desc;

브랜치의 종류 개수

𝒢𝑐𝑜𝑢𝑛𝑡−𝑑𝑖𝑠𝑡inct(𝑏𝑟𝑎𝑛𝑐h_𝑛𝑎𝑚𝑒)(pt_works)

SELECT COUNT(DISTINCT branch_name) FROM pt_works;

브랜치별 row 개수

▢𝑏𝑟𝑎𝑛𝑐h_𝑛𝑎𝑚𝑒𝒢count(𝑏𝑟𝑎𝑛𝑐h_𝑛𝑎𝑚𝑒)(𝑝𝑡_𝑤𝑜𝑟𝑘𝑠)

SELECT COUNT(DISTINCT branch_name) FROM pt_works GROUP BY branch_name;

각 브랜치의 평균 샐러리값 조회

branch_𝑛𝑎𝑚𝑒𝒢𝐴𝑉𝐺 𝑠𝑎𝑙𝑎𝑟𝑦 (𝑝𝑡_𝑤𝑜𝑟𝑘𝑠)

SELECT branch_name, AVG(salary) FROM pt_works GROUP BY branch_name

브랜치의 개수가 2개보다 많은 브랜치의 평균 찾기

SELECT AVG(salary) FROM pt_works GROUP BY branch_name HAVING COUNT(branch_name) > 2;

브랜치의 평균이 2000 이상인 브랜치 찾기

select branch_name, AVG(salary) FROM pt_works GROUP BY branch_name having avg(salary) > 2000; ```

Borrower & Loan & Depositor

borrower & loan

#find the branch grouped by branch_name where its average amount is larger than or equal to 1000 from loan
SELECT branch_name FROM loan GROUP BY branch_name HAVING AVG(amount) > 1000;


# Finds all the customer names who has a loan in "Perryridge' branch
SELECT customer_name FROM borrower JOIN loan WHERE borrower.loan_number = loan.loan_number AND branch_name="Perryridge";

# 차집합
SELECT customer_name FROM borrower EXCEPT select customer_name FROM depositor;

# Deposit이 있는 모든 Borrower를 구하라
## 서브쿼리 사용
SELECT customer_name FROM borrower WHERE EXISTS (
SELECT customer_name FROM depositor 
WHERE customer_name = borrower.customer_name
);
## 조인 사용
SELECT customer_name FROM borrower natural join depositor;

Account

Find the maximum balance in account relation

SELECT MAX(balance) FROM account;

SELECT balance FROM account EXCEPT
SELECT a1.balance 
FROM account AS a1 JOIN account AS a2 WHERE a1.balance < a2.balance;

EXCEPT o, EXCEPTS x -> 주의하기!!!!!!

Customer

find the names of customers who lives in a street and a city that Smith lives

SELECT c1.customer_name FROM customer AS c1 JOIN 
(SELECT customer_street, customer_city FROM customer WHERE customer_name = "Smith") as c2 
WHERE c1.customer_street=c2.customer_street AND c1.customer_city = c2.customer_city;

FROM,JOIN 뒤에 나오는 릴레이션에 AS 넣어줘야 하는거 잊지 않기

Subquery

staff, customer

# Find staff if its name and age are 
# identical to 'Valerius' 61 years old customer
SELECT * FROM staff WHERE 
(name,age) = (SELECT name,age FROM customer WHERE name="Valerius");

# b에서 max(a)보다 큰 값을 찾아라
SELECT * FROM b WHERE point > (SELECT MAX(point) FROM a);

# b에서 a의 어떤 값보다 큰 모든 수를 찾아라
SELECT point FROM b WHERE point > any (SELECT point FROM a);
SELECT point FROM b WHERE point > (Select min(point) FROM a);

student

# average of total_points that each person gets
SELECT AVG(sum_score) FROM (SELECT sum(score) FROM student GROUP BY name) as t;

# average of points that each person gets
SELECT AVG(score) FROM student GROUP BY name;