[예제 9월 16일] 제6강 실습

-- 9월 16일 실습
-- 실습용 데이터 준비
CREATE TABLE jumsu (
    seq SERIAL,
    name VARCHAR(8) NOT NULL,
    java TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    mysql TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    html TINYINT(3) UNSIGNED,
    PRIMARY KEY (seq)
    ) ENGINE = MyISAM;

INSERT INTO jumsu (name, java, mysql, html)
VALUES ('변악도', '100', '100', '100');
INSERT INTO jumsu (name, java, mysql)
VALUES ('성춘향', '60', '85');
INSERT INTO jumsu (name, java, mysql)
VALUES ('홍길동', '80', '80');
INSERT INTO jumsu (name, java, mysql)
VALUES ('이몽룡', '60', '75');

-- 계산식으로 컬럼을 지정
SELECT seq AS `번호`, name AS `이름`, java AS `자바`,
    mysql AS `MySQL`, java + mysql AS `합계`
FROM jumsu
ORDER BY `합계` DESC;

-- 비교 연산자 실습
SELECT '7' > '8';
SELECT 7 = 8;
SELECT 7 <> 8;
SELECT 7 <=> NULL;
SELECT NULL <=> NULL;

-- NULL과의 비교
SELECT * FROM jumsu WHERE html IS NOT NULL;

-- 자바 점수가 60점이상 80점 이하인 행(row) 출력
SELECT * FROM jumsu WHERE java >= 60 AND java <= 80;
SELECT * FROM jumsu WHERE java BETWEEN 60 AND 80;

-- 자바 점수가 60점미만 80점 초과인 행(row) 출력
SELECT * FROM jumsu WHERE java < 60 OR java > 80;
SELECT * FROM jumsu WHERE java NOT BETWEEN 60 AND 80;

-- 홍길동과 성춘향의 점수
SELECT * FROM jumsu WHERE name = '홍길동' OR name = '성춘향';
SELECT * FROM jumsu WHERE name IN ('홍길동', '성춘향');

-- 홍길동과 성춘향을 제외한 사람들의 점수
SELECT * FROM jumsu WHERE name <> '홍길동' AND name <> '성춘향';
SELECT * FROM jumsu WHERE name NOT IN ('홍길동', '성춘향');

-- 우편번호 테이블에서 서울, 인천, 경기 지역의 구군은 총 몇 개?
SELECT COUNT(DISTINCT gugun) FROM ziptable
WHERE sido IN ('서울', '인천', '경기');

-- 홍씨 성을 가진 사람의 자바 점수
SELECT name, java FROM jumsu WHERE name LIKE '홍%';

-- 우편번호 테이블에서 '하'로 시작하는 구군은 몇 개? (중복 무시)
SELECT COUNT(*) FROM ziptable WHERE gugun LIKE '하%';

-- 우편번호 테이블에서 구군의 이름이 3글자인 곳은 몇 개?(중복 무시)
SELECT COUNT(*) FROM ziptable WHERE gugun LIKE '___'; -- "_" 3개 사용

SELECT COUNT(DISTINCT sido) FROM ziptable;
SELECT COUNT(*) FROM ziptable;

-- 우편번호 테이블에서 각 시도별 구군별 동의 개수? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable GROUP BY sido, gugun;

-- 우편번호 테이블에서 서울, 인천, 경기의  구군별 동의 개수? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable
WHERE sido IN ('서울', '인천', '경기')
GROUP BY sido, gugun;

-- 우편번호 테이블에서 서울, 인천, 경기의  구군별 동의 개 수를 내림차순 출력? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable
WHERE sido IN ('서울', '인천', '경기')
GROUP BY sido, gugun
ORDER BY COUNT(*) DESC;

-- 자바와 MySQL 과목의 평균 점수
SELECT AVG((java + mysql) / 2) AS `자바와 MySQL 합계 평균` FROM jumsu;
SELECT java, mysql, java + mysql AS `sum`, (java + mysql) / 2 AS `avg` FROM jumsu; 
SELECT name, java, mysql, (java + mysql) / 2 FROM jumsu;

-- GROUP_CONCAT() 함수 사용 예
CREATE TABLE score (
    seq SERIAL,
    name VARCHAR(8),
    test_score TINYINT UNSIGNED,
    PRIMARY KEY (seq)
    ) ENGINE = MyISAM;

INSERT INTO score (name, test_score) VALUES ('홍길동', '95');
INSERT INTO score (name, test_score) VALUES ('김제동', '95');
INSERT INTO score (name) VALUES ('홍길동');
INSERT INTO score (name, test_score) VALUES ('홍길동', '100');
INSERT INTO score (name, test_score) VALUES ('김제동', '90');
INSERT INTO score (name) VALUES ('홍길동');

SELECT name, GROUP_CONCAT(test_score)
FROM score
GROUP BY name;