셀렉트문 팁 SELECT 28번 문제설명 + 서브 쿼리 중요

SELECT * FROM 테이블명

WHERE --1

GROUP BY --2

HAVING --3

ORDER BY --4

많이쓴다

USE Study_Exam

GO

 

IF (OBJECT_ID('STUDENT') IS NOT NULL)

    DROP TABLE STUDENT

 

CREATE TABLE STUDENT

(

sno NCHAR(3) PRIMARY KEY,                           --학번

gwaCode NCHAR(2) NOT NULL DEFAULT '',                --학과코드

sname NVARNCHAR(20) NOT NULL DEFAULT '',                    --이름

gender NCHAR(1) NOT NULL DEFAULT 'M',                 --성별구분 : M은 남성, F는 여성

score TINYINT NOT NULL DEFAULT 0                 --점수

)

--테이블에 임의의 데이터를 입력합니다.

INSERT INTO STUDENT VALUES('001','01','강현택','M',70)

INSERT INTO STUDENT VALUES('002','01','김윤재','M',90)

INSERT INTO STUDENT VALUES('003','01','김혜진','F',99)

INSERT INTO STUDENT VALUES('004','01','최미숙','F',80)

INSERT INTO STUDENT VALUES('005','02','이호철','M',80)

INSERT INTO STUDENT VALUES('006','02','이주호','M',100)

INSERT INTO STUDENT VALUES('007','02','이미정','F',99)

INSERT INTO STUDENT VALUES('008','02','홍세진','F',90)

USE Study_Exam --////////////// 문제 28번///////////////

-- step by 1

SELECT 

gwaCode, gender , sname AS '최고점수자',score AS '최고점수'

FROM STUDENT

-- step by 2 -- 이름은 개별적이기때문에 테스트해봄

SELECT 

gwaCode, gender ,  MAX(score) AS '최고점수'

FROM STUDENT

GROUP BY gwaCode, gender

-- step by 3

-- 집계합수가없는 상테에서 MAX만쓸수없다그러기에 GROUP BY 써야한다

SELECT 

gwaCode, gender , sname AS '최고점수자', MAX(score) AS '최고점수'

FROM STUDENT

GROUP BY gwaCode, gender, sname

-- step by 4

SELECT 

TOP 4

gwaCode, gender , sname AS '최고점수자', MAX(score) AS '최고점수'

FROM STUDENT

GROUP BY gwaCode, gender, sname

-- step by 5

SELECT 

TOP 4

gwaCode, gender , sname AS '최고점수자', MAX(score) AS '최고점수'

FROM STUDENT

GROUP BY gwaCode, gender, sname

ORDER BY MAX(score) DESC , gwaCode ASC

-- step by 5 -- 서브 쿼리

SELECT A.gwaCode, A.gender, '최고점수자', '최고점수' FROM 

(

SELECT -- 가상의 테이불을 출력한다  -- 가상테이블 AS 'A'

TOP 4

gwaCode, gender , sname AS '최고점수자', MAX(score) AS '최고점수'

FROM STUDENT

GROUP BY gwaCode, gender, sname

ORDER BY '최고점수' DESC 

)

AS A  -- 가상테이블 AS 'A'

ORDER BY gwaCode ,gender

SELECT * FROM STUDENT AS s

-- step by 5

SELECT *

FROM(

SELECT TOP 4 

gwaCode, gender, sname AS '최고점수자',

MAX(score) AS '최고점수'

FROM STUDENT

GROUP BY gwacode, gender, sname

ORDER BY MAX(score) DESC

)

AS A

ORDER BY gwaCode ASC, gender ASC 

서브 쿼리 

SELECT 

A.gwaCode, A.gender, A.sname [최고점수자], A.[MAX SCORE] [최고점수]

FROM

(SELECT TOP 4 gwaCode, gender, sname, MAX(score) [MAX SCORE]

FROM STUDENT

GROUP BY gwaCode, gender, sname

ORDER BY [MAX SCORE] DESC, gender DESC, gwaCode ASC) A

ORDER BY gwaCode, gender