강사님께서 문제 20개를 만들어 주셔서 풀어보고 풀이 진행할게요!
많이 부족하지만 열심히!
SELECT *
FROM TB_POINT;
SELECT CUSTOMER_CD, POINT_MEMO, POINT
FROM TB_POINT;
SELECT CUSTOMER_CD AS "고객코드",
POINT_MEMO AS "포인트 내용",
POINT AS "포인트"
FROM TB_POINT;
SELECT CUSTOMER_CD, CUSTOMER_NM, EMAIL, TOTAL_POINT
FROM TB_CUSTOMER
WHERE TOTAL_POINT < 10000;
SELECT CUSTOMER_CD,
SEQ_NO,
POINT
FROM TB_POINT
WHERE CUSTOMER_CD = '2017053'
AND SEQ_NO = 2;
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT) / 3,1);
SELECT * FROM TB_GRADE;
SELECT *
FROM TB_GRADE
WHERE (CLASS_CD = 'A' OR CLASS_CD = 'B') OR
(KOR >= 80 AND ENG >= 80 AND MAT >= 80);
SELECT *
FROM TB_POINT
WHERE REG_DTTM BETWEEN '20180101000000' AND '20181231235959'
AND POINT BETWEEN 10000 AND 50000;
SELECT CUSTOMER_CD,
CUSTOMER_NM,
MW_FLG,
BIRTH_DAY,
TOTAL_POINT
FROM TB_CUSTOMER
WHERE TOTAL_POINT >= 20000
AND BIRTH_DAY >= '19800101' AND BIRTH_DAY <= '19891231'
AND MW_FLG = 'M';
SELECT CUSTOMER_CD,
CUSTOMER_NM,
MW_FLG,
BIRTH_DAY,
TOTAL_POINT
FROM TB_CUSTOMER
WHERE MW_FLG = 'M'
AND (BIRTH_DAY LIKE '____05__'
OR BIRTH_DAY LIKE '____06__'
OR BIRTH_DAY LIKE '____07__');
SELECT CUSTOMER_CD,
CUSTOMER_NM,
MW_FLG,
BIRTH_DAY,
TOTAL_POINT
FROM TB_CUSTOMER
WHERE ((CUSTOMER_CD LIKE '2017%'
AND MW_FLG = 'M')
OR (CUSTOMER_CD LIKE '2019%'
AND MW_FLG = 'W'))
AND TOTAL_POINT <= 30000;
SELECT *
FROM TB_ITEM_INFO
WHERE ITEM_CD IN ('S01','S04','S06','S10');
SELECT *
FROM TB_POINT
WHERE CUSTOMER_CD IN ('2017042','2018087','2019095')
AND POINT_MEMO LIKE '%구매%';
SELECT *
FROM TB_POINT
WHERE REG_DTTM LIKE '2019%'
AND POINT_MEMO LIKE '%구매%'
ORDER BY POINT DESC;
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT) / 3,1);
SELECT * FROM TB_GRADE;
SELECT KOR, ENG, MAT, TOT AS 합계
FROM TB_GRADE
WHERE CLASS_CD = 'B'
ORDER BY KOR + ENG + MAT DESC;
SELECT SALES_DT,
PRODUCT_NM,
SUM(SALES_COUNT) AS "총판매수"
FROM TB_SALES
WHERE SALES_DT IN ('20190802','20190803')
GROUP BY SALES_DT,
PRODUCT_NM
ORDER BY SALES_DT,
PRODUCT_NM;
SELECT DISTINCT PRODUCT_NM
FROM TB_SALES
WHERE SALES_DT BETWEEN '20190801' AND '20190802'
ORDER BY PRODUCT_NM;
SELECT CU.CUSTOMER_CD,
CU.CUSTOMER_NM,
CU.MW_FLG,
PT.SEQ_NO,
PT.POINT_MEMO,
PT.POINT
FROM TB_CUSTOMER CU,
TB_POINT PT
WHERE CU.CUSTOMER_CD = '2019069'
AND CU.CUSTOMER_CD = PT.CUSTOMER_CD;
SELECT CU.CUSTOMER_CD,
CU.CUSTOMER_NM,
CU.MW_FLG,
PT.SEQ_NO,
PT.POINT_MEMO,
PT.POINT
FROM TB_CUSTOMER CU
JOIN TB_POINT PT
ON CU.CUSTOMER_CD = PT.CUSTOMER_CD
WHERE CU.CUSTOMER_CD = '2019069';
SELECT CUSTOMER_CD,
CUSTOMER_NM,
TOTAL_POINT,
CASE WHEN TOTAL_POINT BETWEEN 1000 AND 19999 THEN '실버'
WHEN TOTAL_POINT BETWEEN 20000 AND 49999 THEN '골드'
WHEN TOTAL_POINT >= 50000 THEN 'VIP'
ELSE '일반'
END AS "고객 등급"
FROM TB_CUSTOMER;
SELECT @ROWNUM:=@ROWNUM+1 AS ROWNUM,
GD.*
FROM TB_GRADE GD, (SELECT @rownum:=0) TMP
WHERE GD.CLASS_CD IN ('A','C');
SELECT *
FROM TB_CUSTOMER
WHERE (CUSTOMER_CD LIKE '2018%'
OR CUSTOMER_CD LIKE '2019%')
AND (BIRTH_DAY LIKE '199%'
OR BIRTH_DAY LIKE '200%')
AND TRIM(IFNULL(PHONE_NUMBER,'')) <> ''
;
쉽지 않다.
2024.07.12 - [MLOps 과정 : 한국경제 with Toss bank/MY SQL] - MY SQL - SQL 기초 문법 ( 2 )
MY SQL - SQL 기초 문법 ( 2 )
1. SQL Syntax SQL 데이터베이스에서 필요한 형태의 데이터를 추출 또는 가공하기 위해 사용하는 언어- SELECT : 칼럼, 계산 값- FROM : 테이블 명- WHERE : 조건- GROUP- BY : 그룹화- HAVING : 그룹화에 사용되는
shok11.tistory.com
다시 화이팅!
Python 으로 통계 학습하기 - 통계 이론 (0) | 2024.07.13 |
---|---|
Python 으로 통계 학습하기 - 데이터 작업 (0) | 2024.07.13 |
MY SQL - SQL 기초 문법 ( 2 ) (1) | 2024.07.12 |
MY SQL - 데이터베이스와 SQL ( 1 ) (1) | 2024.07.11 |
2024.07.09 / SQL, Pandas - 기초 문법 비교 / (1) (0) | 2024.07.09 |