TIL 20241021

이번 주 목표  Weekly Plan

수업 : SQL 

짧은 산책 User Server

짧은 산책 ERD

오늘 목표  Daily Plan

짧은 산책 User Server DB 연결

오늘 한 것  Done Today

수업 : SQL 

 

1.  SQL

-- customer table 의 전체 데이터 출력
SELECT * FROM customer;

SELECT 
    cust_id, -- 회사번호 
    contact_name, -- 담당자 이름
    company_name, -- 회사 이름
    mileage -- 마일리지
FROM customer;

SELECT
    cust_id AS "고객사 번호",
    company_name AS "고객사명",
    mileage * 1.2 AS "20%추가 마일리지"
FROM customer;

-- 마일리지 100 미만
SELECT
    cust_id AS "고객사 번호",
    company_name AS "고객사명",
    mileage * 1.2 AS "원래 마일리지",
    contact_name
FROM customer
WHERE mileage < 100;

-- 마일리지 100 보다 크다
SELECT
    cust_id AS "고객사 번호",
    company_name AS "고객사명",
    mileage * 1.2 AS "원래 마일리지",
    contact_name
FROM customer
WHERE mileage >= 100;

-- 인천, 마일리지 역순
SELECT
    cust_id,
    company_name,
    contact_name,
    city,
    mileage AS points
FROM customer
where city = "인천"
ORDER BY mileage DESC;

SELECT DISTINCT city from customer;

-- LIMIT 입력된 순서대로 나옴 
SELECT
* 
FROM customer
LIMIT 3; 

SELECT 
* 
FROM customer
ORDER BY mileage DESC
LIMIT 3;

SELECT
* 
FROM customer
WHERE contact_position != "대표";

SELECT
* 
FROM customer
WHERE contact_position <> "대표";

-- 문제 1. 고객테이블에서 도시가 부산이고 마일리지가 100 이하인 고객사만 출력해 보세요

-- 나의 답안
SELECT 
company_name AS "부산 마일리지 100 이하 고객사"
FROM customer
WHERE city = "부산" AND mileage <= 100
ORDER BY mileage DESC;

-- UNION 을 이용한 답안
-- UNION 은 속성이 모두 같아야 함
SELECT 
    cust_id,
    contact_name,
    mileage,
    city
FROM customer
WHERE city = "부산"
UNION
SELECT 
    cust_id,
    contact_name,
    mileage,
    city
FROM customer
WHERE mileage <= 100
-- ORDER BY 1 : 테이블의 첫번째 컬럼 기준으로 정렬
ORDER BY 1;

-- 숫자 계산 
SELECT
    33 + 5 AS addition,
    33 - 5 AS substraction,
    33 * 5 AS multiplication,
    33 / 5 AS int_value, --정수
    CAST(33 as Float) / 5 AS float_value,
    33 % 5 AS remainder;

-- 비교연산자 
-- True 1
-- False 0
SELECT
    33 >= 5 as "greater_than_or_equal",
    33 <= 5 as "less_than_or_equal",
    33 > 5 as "greater_than",
    33 < 5 as "less_than",
    33 = 5 as "eqaul",
    33 != 5 as "not_eqaul";

-- 문제 2. 고객 테이블에서 직위가 대리가 아닌 고객만 검색하세요.
SELECT
* 
FROM customer
WHERE 1=1 -- 정렬을 위해 사용 예쁘게 쓰기 위해, 무조건 참 
AND contact_position != "대리";


-- 문제 3. 고객 테이블에서 직위가 대표 또는 사원인 고객만 출력

SELECT
* 
FROM customer
WHERE 1=1
AND contact_position = "대표" 
OR contact_position = "사원" 
OR contact_position = "대리" ;

-- IN 키워드 사용 
SELECT
* 
FROM customer
WHERE 1=1
AND contact_position IN ("대표", "사원", "대리");


SELECT
*
FROM customer
WHERE mileage BETWEEN 100 AND 200;

SELECT
*
FROM customer
WHERE mileage >= 100 AND mileage <= 200;

-- 문제 4. 고객 테이블에서 city가 서울, 대전이고 직위가 대표이거나 사원인 고객만 출력하세요.ALTER
SELECT
*
FROM customer
WHERE city IN ("서울", "대전")
AND contact_position IN ("대표", "사원");

-- Like 검색 : 많이 쓸 경우 속도가 느려집니다.
SELECT 
region
FROM customer
WHERE region LIKE "%광역시";

-- 문제 5. 고객 테이블에서 region에 특별시 또는 광역시 중에서 고객 마일리지를 역순으로 출력해 보세요.ABORT
SELECT
* 
FROM customer
WHERE 1=1
AND (region LIKE  "%특별시") 
OR (region LIKE "%광역시")
ORDER BY mileage DESC;

SELECT
    length("hello") as length_hello,
    length("안녕하세요") as length_안녕하세요;

SELECT "DREAM" || " " || "COME" || " " || "TRUE" as concatenated;

SELECT  substr("DREAM COME TRUE", 0, 6);

SELECT trim(" abcd "), ltrim(" abcd"), rtrim("abcd ");

SELECT instr("sql java rust javascript","rust");

SELECT replace("010.1234.5678",".","-");

SELECT ceil(123.16),
floor(123.56),
round(123.16),
round(123.16);

SELECT abs(-120);

SELECT round(random() % 100);

SELECT datetime("now"),
date("now"),
time("now");

SELECT
strftime("%Y","now") as year,
strftime("%m","now") as month,
strftime("%d","now") as day,
strftime("%H","now") as hour,
strftime("%M","now") as month,
strftime("%S","now") as second;

select 
date("now") AS current,
julianday("2024-11-21") - julianday("now") AS date_diff;

SELECT
datetime("now", "+20 days"),
datetime("now", "+20 months"),
datetime("now", "+20 hours");

SELECT datetime("now"),
datetime("now","start of month"),
datetime("now","start of month", "+1 month"),
datetime("now","start of month", "+1 month", "-1 day");

-- mileage가 200 이상이면 "VIP 고객" 아니면 "일반 고객"
SELECT 
cust_id, company_name, mileage,
CASE WHEN mileage >= 200 THEN "VIP 고객" 
ELSE "일반 고객"
END AS "고객 구분"
FROM customer ;

-- 문제 6. mileage가 300 이상이면 "VVIP 고객" 200 이상이면 "VIP 고객" 아니면 "일반 고객" 100 이하이면 "고객"
SELECT 
cust_id, company_name, mileage,
CASE WHEN mileage >= 300 THEN "VIP 고객" 
WHEN mileage >= 200 THEN "VIP 고객" 
WHEN mileage >=100 THEN "일반 고객"
ELSE "고객"
END AS "고객 구분"
FROM customer;

-- 문제 7. 도시가 광역시이거나 특별시이면 => "대도시"
-- 아니면 "도시" 
SELECT 
cust_id, company_name, region,
CASE WHEN mileage >= 300 THEN "VIP 고객" 
WHEN mileage >= 200 THEN "VIP 고객" 
WHEN mileage >=100 THEN "일반 고객"
ELSE "고객"
END AS "고객 구분",
CASE WHEN (region LIKE "%광역시") OR ( region LIKE "%특별시")THEN "대도시" 
ELSE "도시"
END AS "city_type"
FROM customer;

SELECT 
emp_id, name, birth_date, hire_date,
-- 태어난 날
strftime("%Y","now") - strftime("%Y", birth_date) as age,
-- 태어난 주 
strftime("%w", birth_date) as weeknum,
julianday("now") - julianday(hire_date) as hired_days
FROM employee;

-- 문제 8. weeknum이 0 = 일요일, 1 = 월요일, 2 = 화요일, 3 = 수요일, 4 = 목요일, 5 = 금요일, 6 = 토요일 
SELECT
CASE WHEN weeknum = "0" THEN "월요일"
WHEN weeknum = "1" THEN "월요일"
WHEN weeknum = "2" THEN "화요일"
WHEN weeknum = "3" THEN "수요일"
WHEN weeknum = "4" THEN "목요일"
WHEN weeknum = "5" THEN "금요일"
WHEN weeknum = "6" THEN "토요일"
ELSE "요일 없음"
END AS "태어난 요일"
FROM
(SELECT 
emp_id, name, birth_date, hire_date,
-- 태어난 날
strftime("%Y","now") - strftime("%Y", birth_date) as age,
-- 태어난 주 
strftime("%w", birth_date) as weeknum,
julianday("now") - julianday(hire_date) as hired_days
FROM employee )

---

정리  Memo

WHERE 1=1

WHERE 1=1 -- 정렬을 위해 사용 예쁘게 쓰기 위해, 무조건 참

 

strftime 시간 문자열 자르기

SELECT
strftime("%Y","now") as year,
strftime("%m","now") as month,
strftime("%d","now") as day,
strftime("%H","now") as hour,
strftime("%M","now") as month,
strftime("%S","now") as second;

 

날짜 계산

select 
date("now") AS current,
julianday("2024-11-21") - julianday("now") AS date_diff;

SELECT
datetime("now", "+20 days"),
datetime("now", "+20 months"),
datetime("now", "+20 hours");

 

CASE WHEN

-- mileage가 300 이상이면 "VVIP 고객" 200 이상이면 "VIP 고객" 아니면 "일반 고객" 100 이하이면 "고객"
SELECT 
cust_id, company_name, mileage,
CASE WHEN mileage >= 300 THEN "VIP 고객" 
WHEN mileage >= 200 THEN "VIP 고객" 
WHEN mileage >=100 THEN "일반 고객"
ELSE "고객"
END AS "고객 구분"
FROM customer;

 

subquery

-- 문제 8. weeknum이 0 = 일요일, 1 = 월요일, 2 = 화요일, 3 = 수요일, 4 = 목요일, 5 = 금요일, 6 = 토요일 
SELECT
CASE WHEN weeknum = "0" THEN "월요일"
WHEN weeknum = "1" THEN "월요일"
WHEN weeknum = "2" THEN "화요일"
WHEN weeknum = "3" THEN "수요일"
WHEN weeknum = "4" THEN "목요일"
WHEN weeknum = "5" THEN "금요일"
WHEN weeknum = "6" THEN "토요일"
ELSE "요일 없음"
END AS "태어난 요일"
FROM
(SELECT 
emp_id, name, birth_date, hire_date,
-- 태어난 날
strftime("%Y","now") - strftime("%Y", birth_date) as age,
-- 태어난 주 
strftime("%w", birth_date) as weeknum,
julianday("now") - julianday(hire_date) as hired_days
FROM employee )

 

KPT

Keep

다시 열심히 해보려 하는 것

Problem

공부에 많은 시간을 할애하지 못하고 있는 것

Try

수업 시간 외로 열심히 하기

소감  Diary

열심히 하기