[MySQL] MySQL 퀴즈

전화번호가 6이 첫번째로 나오는위치 출력(INSTR)

-- TEL 컬럼 사용해서 GRADE가 2와 4인 학생,
-- 전화번호가 6이 첫번째로 나오는위치 출력
SELECT NAME 
	 , GRADE
     , TEL
     , INSTR(TEL,'6') AS '6'
  FROM STUDENT
  WHERE GRADE IN ('2','4');

---

전화번호중 앞자리만 출력(SUBSTR, INSTR)

SELECT NAME
	 , TEL
     , SUBSTR(TEL,INSTR(TEL,')')+1,(INSTR(TEL,'-')-1-INSTR(TEL,')')))
     FROM STUDENT
     WHERE DEPTNO1 = 101;

---

급여에 따라 GRADE 설정(CASE WHEN THEN)

SELECT NAME
		 , PAY
     , CASE WHEN PAY BETWEEN 1 AND 300 THEN 'GRADE1'
						WHEN PAY BETWEEN 301 AND 400 THEN 'GRADE2'
            WHEN PAY BETWEEN 401 AND 500 THEN 'GRADE3'
            WHEN PAY > 500 THEN 'GRADE4'
		END GRADE
  FROM PROFESSOR
  ORDER BY PAY DESC;

---

직업의 세번째글자에 속하는 알파벳을 ‘?’로 변경(REPLACE)

SELECT ENAME
	 , JOB
     , REPLACE(JOB,SUBSTR(JOB,3,1),'?')
  FROM EMP
  WHERE DEPTNO = 20;

---

대출액을 대출코드와 대출지점별로 누적합계(SUM() OVER())

SELECT L_CODE AS "대출종목코드"
	 , L_STORE AS "대출지점"
     , L_DATE AS "대출일자"
     , L_QTY AS "대출건수"
     , L_TOTAL AS "대출액"
     , SUM(L_TOTAL) OVER(PARTITION BY L_CODE, L_STORE ORDER BY L_DATE) AS "누적대출금액"
  FROM LOAN;

---

EMP테이블에서 특정 컬러 가져와서 새로운 테이블 생성(CREATE TABLE)

CREATE TABLE NEW_EMP2
AS
SELECT EMPNO, ENAME, HIREDATE
  FROM EMP;

→ 특정 컬럼만 복사해서 새로운 테이블 생성

SELECT * FROM NEW_EMP2;

---

학생 테이블과 교수테이블을 JOIN하여 각각 이름출력(JOIN)

-- 코드1
SELECT A.NAME AS 'STU_NAME'
     , B.NAME AS 'PROF_NAME'
  FROM STUDENT A JOIN PROFESSOR B
 WHERE A.PROFNO = B.PROFNO;

-- 코드2
SELECT A.NAME AS 'STU_NAME'
     , B.NAME AS 'PROF_NAME'
  FROM STUDENT A JOIN PROFESSOR B
    ON A.PROFNO = B.PROFNO;

---

1전공(DEPTNO1)이 101번인 학생의 이름과 지도교수 이름(JOIN)

-- 코드1
SELECT A.NAME AS 'STU_NAME'
	 , B.NAME AS 'PROF_NAME'
  FROM STUDENT A JOIN PROFESSOR B
 WHERE A.PROFNO = B.PROFNO
   AND A.DEPTNO1 =101;

-- 코드2 안시조인
SELECT A.NAME AS 'STU_NAME'
	 , B.NAME AS 'PROF_NAME'
  FROM STUDENT A JOIN PROFESSOR B
    ON A.PROFNO = B.PROFNO
 WHERE A.DEPTNO1 =101;

---

CUSTOMER테이블과 GIFT테이블을 JOIN해서 포인트별 상품 출력(JOIN)

SELECT CUST.GNAME, CUST.POINT, GIFT.GNAME
  FROM CUSTOMER AS CUST, GIFT
 WHERE CUST.POINT BETWEEN GIFT.G_START AND GIFT.G_END
ORDER BY CUST.POINT;

---

학생,점수,학점 테이블 3개 JOIN해서 점수계산(JOIN)

SELECT STU.NAME,SCORE.TOTAL,H.GRADE AS CREDIT
  FROM STUDENT AS STU, SCORE, HAKJUM AS H
 WHERE (STU.STUDNO = SCORE.STUDNO) AND (SCORE.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT)
ORDER BY SCORE.TOTAL DESC;

---

STUDENT 페이지 키 랭킹 매기기(RANK)

SELECT NAME
	   , HEIGHT
     , RANK() OVER(ORDER BY HEIGHT DESC) AS 'RANK'			-- 순위
     , DENSE_RANK() OVER(ORDER BY HEIGHT DESC) AS 'RANK2'	-- 순위(중복이어도 다음순위 다음번호)
     , ROW_NUMBER() OVER(ORDER BY HEIGHT DESC) AS 'RANK3'	-- 순위(중복일때 순서대로 순서매김)
  FROM STUDENT
  WHERE HEIGHT >= 170;