jineecode
SQL 오답노트 본문
SQL을 연습해볼 수 있는 좋은 사이트
www.tutorialspoint.com/execute_sql_online.php
Online Sqlite Compiler - Online Sqlite Editor - Online Sqlite IDE - Sqlite Coding Online - Practice Sqlite Online - Execute Sqli
www.tutorialspoint.com
SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.
Query Panel Use this panel to try to solve the problem with other SQL statements (SELECTs, etc...). Results will be displayed below. Share your queries by copying and pasting the URL that is generated after each run.
sqlfiddle.com
1.
132. 다음은 학생 테이블이다. 아래 결과와 같이 가장 높은 성적을 출력하는 쿼리를 작성하시오.
학생 테이블
|
학번 |
이름 |
성적 |
|
2001 |
안창호 |
90 |
|
2002 |
김정호 |
85 |
|
2003 |
김유신 |
75 |
|
2004 |
허준 |
80 |
|
2005 |
이성계 |
95 |
[결과]
|
학번 |
성적 |
|
2005 |
95 |
정답:
답)
select 학번, 성적
from 학생
where 성적 = (select max(성적) from 학생)
내가 쓴 오답:
SELECT 학번, MAX(성적) as 성적 from 학생 group by 학번;
group by를 쓰게 되면 해당 그룹의 모든 것이 출력된다.
group by 를 하게 되면 전부 출력 되겠죠
왜냐하면 group by 라고 하는건 그룹으로 지정한 컬럼(예:학번)에서
max() 함수로 "그룹별"로 가장 높은 값을 가져 오기 때문이에요
그러므로 이 문제는 서브쿼리를 써야 한다.
select 학번, 성적
from 학생
where 성적 = (select max(성적) from 학생)
[출처] [2020년 제4회 기사 실기 출제예상 문제] (11과목) 응용 SW 기초 기술 활용 - 132 (수제비- IT 커뮤니티 (정보처리기사,빅데이터분석기사 등)) | 작성자 보안쌤
수제비- IT 커뮤니티 (정보처리기사... : 네이버 카페
수제비-수험생 입장에서 제대로 쓴 비법서(정보처리기사, 정보처리기능사, 빅데이터 분석기사 등 시리즈)
cafe.naver.com
2.
성적 테이블
|
학번 |
과목 |
성적 |
|
1000 |
보안 |
A |
|
1001 |
운영체제 |
B |
|
1002 |
통계 |
A+ |
|
1003 |
암호학 |
B+ |
|
1005 |
알고리즘 |
A |
|
1006 |
보안 |
A+ |
select 과목
from
(
select 과목, count(과목)
from 성적
group by 과목
having count(과목) > 1
) a;
실행결과
|
과목 |
|
( ) |
답) 보안
정답 및 해설)
먼저 아래의 쿼리가 실행이 된다.
select 과목, count(과목)
from 성적
group by 과목
having count( 과목) > 1;
-- group by 를 이용하여 과목별로 건수(count)를 집계한 후, 과목의 건수가 1보다 큰 데이터만 추출한다.
MariaDB [test2]> select 과목, count(과목)
-> from 성적
-> group by 과목
-> having count(과목) > 1;
+--------+---------------+
| 과목 | count(과목) |
+--------+---------------+
| 보안 | 2 |
+--------+---------------+
1 row in set (0.00 sec)
위 결과를 다시 select 명령으로 과목만 추출한다.
MariaDB [test2]> select 과목
-> from
-> (
-> select 과목, count(과목)
-> from 성적
-> group by 과목
-> having count( 과목) > 1
-> ) a;
+--------+
| 과목 |
+--------+
| 보안 |
+--------+
1 row in set (0.00 sec)
[출처] [2020년 제4, 5회 기사 실기 출제예상 문제] (11과목) 데이터베이스 - 149 (수제비- IT 커뮤니티 (정보처리기사,빅데이터분석기사 등)) | 작성자 보안쌤
수제비- IT 커뮤니티 (정보처리기사... : 네이버 카페
수제비-수험생 입장에서 제대로 쓴 비법서(정보처리기사, 정보처리기능사, 빅데이터 분석기사 등 시리즈)
cafe.naver.com
3.
16. [학과] 테이블에서 ‘교수명’의 이름이 “황”으로 시작하는 튜플의 ‘학과번호’를 777로 업데이트하는 SQL 구문을 작성하시오.
[학과] 테이블
|
학과 |
학과번호 |
학과정원 |
교수명 |
|
전산과 |
110 |
300 |
김일등 |
|
국문과 |
310 |
250 |
황이등 |
|
수학과 |
120 |
400 |
이삼등 |
|
전산과 |
120 |
300 |
김사등 |
|
수학과 |
420 |
180 |
황오등 |
답)__________________________________________________________________________________
정답)
UPDATE 학과
SET 학과번호 = 777
WHERE 교수명 LIKE ‘황%’;
[출처] [2021년 제1회 기사 실기 출제예상 문제] (7과목) SQL 응용 - 16 (수제비- IT 커뮤니티 (정보처리기사,빅데이터분석기사 등)) | 작성자 수제비쌤
수제비- IT 커뮤니티 (정보처리기사... : 네이버 카페
수제비-수험생 입장에서 제대로 쓴 비법서(정보처리기사, 정보처리기능사, 빅데이터 분석기사 등 시리즈)
cafe.naver.com
4.
[성과금] 테이블의 '부서', '성과금등급', '성과금'에 대해서 부서별 소계와 전체 합계를 검색하는 SQL 구문을 작성하시오.
[조건]
- [성과금] 테이블의 속성에는 부서, 성과금등급, 성과금이 있다.
- 속성명은 '성과금합계'로 하고, ROLLUP 함수를 사용한다.
[결과]
|
부서 |
성과금등급 |
성과금합계 |
|
인사과 |
B |
100 |
|
인사과 |
A |
200 |
|
인사과 |
C |
50 |
|
인사과 |
|
350 |
|
회계과 |
B |
100 |
|
회계과 |
C |
50 |
|
회계과 |
A |
200 |
|
회계과 |
|
350 |
|
정보보안과 |
A |
200 |
|
정보보안과 |
B |
100 |
|
정보보안과 |
|
300 |
|
|
|
1,000 |
답)__________________________________________________________________________________
SELECT 부서, 성과금등급, SUM(성과금) AS 성과금합계
FROM 성과금
GROUP BY ROLLUP(부서, 성과금등급);
*ROLLUP에 대하여
SELECT 컬럼1, 컬럼2, ..., 집계함수 FROM 테이블명 [WHERE절] GROUP BY [컬럼 ...] ROLLUP 컬럼 [해빙절][오더바이절]
중간 집계 값을 산출하기 위한 그룹 함수. ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀜을 주의한다.
해당 영상이 많이 참조되었다.
0. 스키마 준비
CREATE TABLE STARBUCKS_ORDER (
ORDER_NO NUMBER(5) NOT NULL,
ORDER_DT VARCHAR2(8) NOT NULL,
BRANCH VARCHAR2(10),
ORDER_ITEM VARCHAR2(100),
REG_NAME VARCHAR2(20),
REG_DTS DATE,
CONSTRAINT ORDER_PK PRIMARY KEY (ORDER_NO)
);
COMMENT ON COLUMN STARBUCKS_ORDER.ORDER_NO IS '주문번호';
COMMENT ON COLUMN STARBUCKS_ORDER.ORDER_DT IS '주문일자';
COMMENT ON COLUMN STARBUCKS_ORDER.BRANCH IS '지점';
COMMENT ON COLUMN STARBUCKS_ORDER.ORDER_ITEM IS '주문상품';
COMMENT ON COLUMN STARBUCKS_ORDER.REG_NAME IS '담당 파트너';
COMMENT ON COLUMN STARBUCKS_ORDER.REG_DTS IS '등록일시';
INSERT INTO STARBUCKS_ORDER VALUES ( 1,'20190801','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 2,'20190801','강남점','카페라떼','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 3,'20190801','강남점','자바칩 프라푸치노','테스',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 4,'20190801','강남점','바닐라 프라푸치노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 5,'20190801','강남점','아메리카노','무드',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 6,'20190801','강남점','아메리카노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 7,'20190801','강남점','쿨라임 피지오','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 8,'20190801','강남점','카페라떼','테스',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 9,'20190802','강남점','아메리카노','무드',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 10,'20190802','강남점','콜드브루','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 11,'20190802','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 12,'20190802','강남점','아메리카노','마샤',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 13,'20190802','강남점','한라봉주스','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 14,'20190802','강남점','카페모카','테스',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 15,'20190802','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 16,'20190802','강남점','아메리카노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 17,'20190802','강남점','그린티크림 프라푸치노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 18,'20190802','강남점','카페라떼','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 19,'20190802','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 20,'20190802','강남점','콜드브루','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 21,'20190802','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 22,'20190803','강남점','카페라떼','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 23,'20190803','강남점','자바칩 프라푸치노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 24,'20190803','강남점','바닐라 프라푸치노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 25,'20190803','강남점','아메리카노','무드',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 26,'20190803','강남점','아메리카노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 27,'20190803','강남점','쿨라임 피지오','마샤',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 28,'20190803','강남점','한라봉주스','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 29,'20190803','강남점','아메리카노','마샤',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 30,'20190804','강남점','콜드브루','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 31,'20190804','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 32,'20190804','강남점','카페라떼','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 33,'20190804','강남점','자바칩 프라푸치노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 34,'20190804','강남점','바닐라 프라푸치노','마샤',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 35,'20190804','강남점','아메리카노','릴리',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 36,'20190804','강남점','아메리카노','봄',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 37,'20190804','강남점','아메리카노','테스',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 38,'20190804','강남점','카페라떼','무드',SYSDATE);
INSERT INTO STARBUCKS_ORDER VALUES ( 39,'20190804','강남점','아메리카노','마샤',SYSDATE);
1. 먼저 GROUP BY를 해본다.
SELECT * FROM STARBUCKS_ORDER;
-- 날짜별
SELECT ORDER_DT, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ORDER_DT ORDER BY ORDER_DT;
-- 주문음료별
SELECT ORDER_ITEM, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ORDER_ITEM;
-- 파트너별
SELECT REG_NAME , COUNT(*) FROM STARBUCKS_ORDER GROUP BY REG_NAME;
2. 이것을 통계내본다.단순히 GROUP BY 앞에 ROLLUP을 해주고 괄호로 묶으면 합해서 결과가 나온다. 즉 엑셀함수의 SUM 같은 역할을 해주는 것이 ROLLUP.
SELECT * FROM STARBUCKS_ORDER;
-- 날짜별
SELECT ORDER_DT, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ROLLUP(ORDER_DT) ORDER BY ORDER_DT;
-- 주문음료별
SELECT ORDER_ITEM, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ROLLUP(ORDER_ITEM);
-- 파트너별
SELECT REG_NAME , COUNT(*) FROM STARBUCKS_ORDER GROUP BY ROLLUP (REG_NAME);
3. 두 개 이상의 컬럼으로 그룹 바이를 할 경우
SELECT * FROM STARBUCKS_ORDER;
-- DT와 ITEM으로 그룹바이
SELECT ORDER_DT, ORDER_ITEM, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ORDER_DT, ORDER_ITEM ORDER BY ORDER_DT;ORDER_DTORDER_ITEMCOUNT(*)
| 20190801 | 바닐라 프라푸치노 | 1 |
| 20190801 | 아메리카노 | 3 |
| 20190801 | 자바칩 프라푸치노 | 1 |
| 20190801 | 카페라떼 | 2 |
| 20190801 | 쿨라임 피지오 | 1 |
| 20190802 | 그린티크림 프라푸치노 | 1 |
| 20190802 | 아메리카노 | 7 |
| 20190802 | 카페라떼 | 1 |
| 20190802 | 카페모카 | 1 |
| 20190802 | 콜드브루 | 2 |
| 20190802 | 한라봉주스 | 1 |
| 20190803 | 바닐라 프라푸치노 | 1 |
| 20190803 | 아메리카노 | 3 |
| 20190803 | 자바칩 프라푸치노 | 1 |
| 20190803 | 카페라떼 | 1 |
| 20190803 | 쿨라임 피지오 | 1 |
| 20190803 | 한라봉주스 | 1 |
| 20190804 | 바닐라 프라푸치노 | 1 |
| 20190804 | 아메리카노 | 5 |
| 20190804 | 자바칩 프라푸치노 | 1 |
| 20190804 | 카페라떼 | 2 |
| 20190804 | 콜드브루 | 1 |
SELECT * FROM STARBUCKS_ORDER;
-- DT와 ITEM으로 그룹바이와 롤업
SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
FROM STARBUCKS_ORDER GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM) ORDER BY ORDER_DT;ORDER_DTORDER_ITEMCOUNT(*)
| 20190801 | 바닐라 프라푸치노 | 1 |
| 20190801 | 아메리카노 | 3 |
| 20190801 | 자바칩 프라푸치노 | 1 |
| 20190801 | 카페라떼 | 2 |
| 20190801 | 쿨라임 피지오 | 1 |
| 20190801 | (null) | 8 |
| 20190802 | 그린티크림 프라푸치노 | 1 |
| 20190802 | 아메리카노 | 7 |
| 20190802 | 카페라떼 | 1 |
| 20190802 | 카페모카 | 1 |
| 20190802 | 콜드브루 | 2 |
| 20190802 | 한라봉주스 | 1 |
| 20190802 | (null) | 13 |
| 20190803 | 바닐라 프라푸치노 | 1 |
| 20190803 | 아메리카노 | 3 |
| 20190803 | 자바칩 프라푸치노 | 1 |
| 20190803 | 카페라떼 | 1 |
| 20190803 | 쿨라임 피지오 | 1 |
| 20190803 | 한라봉주스 | 1 |
| 20190803 | (null) | 8 |
| 20190804 | 바닐라 프라푸치노 | 1 |
| 20190804 | 아메리카노 | 5 |
| 20190804 | 자바칩 프라푸치노 | 1 |
| 20190804 | 카페라떼 | 2 |
| 20190804 | 콜드브루 | 1 |
| 20190804 | (null) | 10 |
| (null) | (null) | 39 |
날짜별로 소계가 나오고, 마지막엔 총합이 나온다.
이것은 즉,
GROUP BY ORDER_DT, ORDER_ITEM + GROUP BY ORDER_DT + 총 합계
이라고 보면 이해하기 쉽다.
*CUBE에 대하여
SELECT 컬럼1, 컬럼2, ..., 집계함수 FROM 테이블명 [WHERE절] GROUP BY [컬럼 ...] CUBE 컬럼 [해빙절][오더바이절]
결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수
1. 먼저 GROUP BY를 해본다.
SELECT * FROM STARBUCKS_ORDER;
-- 날짜별
SELECT ORDER_DT, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ORDER_DT ORDER BY ORDER_DT;
-- 주문음료별
SELECT ORDER_ITEM, COUNT(*) FROM STARBUCKS_ORDER GROUP BY ORDER_ITEM;
-- 파트너별
SELECT REG_NAME , COUNT(*) FROM STARBUCKS_ORDER GROUP BY REG_NAME;
2. 마찬가지로 통계 내본다.단순히 GROUP BY 앞에 CUBE를 해주고 괄호로 묶으면 합해서 결과가 나온다. 여기까지는 ROLLUP과 차이가 없다.
SELECT * FROM STARBUCKS_ORDER;
-- 날짜별
SELECT ORDER_DT, COUNT(*) FROM STARBUCKS_ORDER GROUP BY CUBE(ORDER_DT) ORDER BY ORDER_DT;
-- 주문음료별
SELECT ORDER_ITEM, COUNT(*) FROM STARBUCKS_ORDER GROUP BY CUBE(ORDER_ITEM);
-- 파트너별
SELECT REG_NAME , COUNT(*) FROM STARBUCKS_ORDER GROUP BY CUBE (REG_NAME);3. 두 개 이상의 컬럼으로 cube를 할 경우
SELECT * FROM STARBUCKS_ORDER;
-- DT와 ITEM으로 그룹바이와 큐브
SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
FROM STARBUCKS_ORDER GROUP BY CUBE(ORDER_DT, ORDER_ITEM) ORDER BY ORDER_DT;
ORDER_DTORDER_ITEMCOUNT(*)
| 20190801 | 바닐라 프라푸치노 | 1 |
| 20190801 | 아메리카노 | 3 |
| 20190801 | 자바칩 프라푸치노 | 1 |
| 20190801 | 카페라떼 | 2 |
| 20190801 | 쿨라임 피지오 | 1 |
| 20190801 | (null) | 8 |
| 20190802 | 그린티크림 프라푸치노 | 1 |
| 20190802 | 아메리카노 | 7 |
| 20190802 | 카페라떼 | 1 |
| 20190802 | 카페모카 | 1 |
| 20190802 | 콜드브루 | 2 |
| 20190802 | 한라봉주스 | 1 |
| 20190802 | (null) | 13 |
| 20190803 | 바닐라 프라푸치노 | 1 |
| 20190803 | 아메리카노 | 3 |
| 20190803 | 자바칩 프라푸치노 | 1 |
| 20190803 | 카페라떼 | 1 |
| 20190803 | 쿨라임 피지오 | 1 |
| 20190803 | 한라봉주스 | 1 |
| 20190803 | (null) | 8 |
| 20190804 | 바닐라 프라푸치노 | 1 |
| 20190804 | 아메리카노 | 5 |
| 20190804 | 자바칩 프라푸치노 | 1 |
| 20190804 | 카페라떼 | 2 |
| 20190804 | 콜드브루 | 1 |
| 20190804 | (null) | 10 |
| (null) | 그린티크림 프라푸치노 | 1 |
| (null) | 바닐라 프라푸치노 | 3 |
| (null) | 아메리카노 | 18 |
| (null) | 자바칩 프라푸치노 | 3 |
| (null) | 카페라떼 | 6 |
| (null) | 카페모카 | 1 |
| (null) | 콜드브루 | 3 |
| (null) | 쿨라임 피지오 | 2 |
| (null) | 한라봉주스 | 2 |
| (null) | (null) | 39 |
CUBE는 ROLLUP 과는 달리 음료별로도 그룹바이 한 것의 소계가 나온다.
그러므로 CUBE는 컬럼의 순서가 결괏값이 영향을 미치지 않는다.
[출처] [2021년 제1회 기사 실기 출제예상 문제] (7과목) SQL 응용 - 19 (수제비- IT 커뮤니티 (정보처리기사,빅데이터분석기사 등)) | 작성자 수제비쌤
'지식 > 정보처리기사' 카테고리의 다른 글
| 기사 실기 오답노트. (0) | 2021.04.21 |
|---|---|
| 정보처리기사 실기 도움 되었던 사이트 및 강의 (0) | 2021.04.20 |
| 프로그래밍 언어 활용 오답노트 (0) | 2021.04.17 |
| 파이썬 (0) | 2021.04.17 |
| 프로그래밍 활용 개행 구분하기 (0) | 2021.04.16 |
