SQL 복습 : 실습 문제 풀이

수업 중 실습 문제 다시 풀어봤다.
"다시풀기"라고 써있는건 다시 풀어야하는데, 마지막 DDL, DML을 하면서 데이터나 칼럼이 좀 바뀌어서 다시 풀 수 있을지는 모르겠다.

-- 실습 총 복습
-- basic select는 생략
-- additional select - 함수


-- 1번
SELECT STUDENT_NO AS '학번',
       STUDENT_NAME AS '이름',
       ENTRANCE_DATE AS '입학년도'
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY ENTRANCE_DATE

-- 2번
SELECT PROFESSOR_NAME,
       PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE LENGTH(PROFESSOR_NAME) != 9;

-- 3번 2007년 기준
SELECT PROFESSOR_NAME AS 교수이름,
       108 - CAST(LEFT(PROFESSOR_SSN, 2) as UNSIGNED ) AS 나이
FROM TB_PROFESSOR
ORDER BY 나이;

-- 4번
SELECT substring(PROFESSOR_NAME, 2) AS '이름'
FROM TB_PROFESSOR;

-- 5번
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE CAST(LEFT(ENTRANCE_DATE, 4) as UNSIGNED ) - CAST(CONCAT('19', LEFT(STUDENT_SSN, 2)) as UNSIGNED ) >19
ORDER BY STUDENT_NO DESC;

-- 6번 강의 보기

-- 7번 강의 보기

-- 8번
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE LEFT(STUDENT_NO, 1) NOT IN ('A');

-- 9번
SELECT ROUND(AVG(POINT), 1)
FROM TB_GRADE
WHERE STUDENT_NO = 'A517178';

-- 10번
SELECT DEPARTMENT_NO AS `학과번호`,
       COUNT(*) AS `학생수(명()`
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO;

-- 11번
SELECT COUNT(*)
FROM TB_STUDENT
WHERE COACH_PROFESSOR_NO IS NULL;

-- 12번
SELECT LEFT(TERM_NO, 4) AS `년도`,
       ROUND(AVG(POINT), 1) AS `년도 별 평점`
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY `년도`;

-- 13번 다시 풀기
SELECT DEPARTMENT_NO AS `학과코드명`,
       SUM(
        CASE
            WHEN ABSENCE_YN = 'Y' THEN 1
            ELSE 0
       END
       ) AS `휴학생 수`
FROM TB_STUDENT
GROUP BY `학과코드명`;

-- 14번 다시 풀기
SELECT
    STUDENT_NAME AS `동일이름`,
    COUNT(*) AS `동명이인 수`
FROM TB_STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT(*) >= 2;

-- 15번 다시 풀기
SELECT LEFT(TERM_NO, 4) AS `년도`,
       RIGHT(TERM_NO, 2) AS `학기`,
       ROUND(AVG(POINT), 1) AS `평점`
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY `년도`, `학기`
WITH ROLLUP;

-- additional select - option

-- 1번
SELECT STUDENT_NAME AS `학생 이름`,
       STUDENT_ADDRESS AS `주소지`
FROM TB_STUDENT
ORDER BY `학생 이름`;

-- 2번
SELECT STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE ABSENCE_YN = 'Y'
ORDER BY STUDENT_SSN DESC;

-- 3번
SELECT STUDENT_NAME AS `학생이름`,
       STUDENT_NO AS `학번`,
       STUDENT_ADDRESS AS `거주지 주소`
FROM TB_STUDENT
WHERE (STUDENT_ADDRESS LIKE '%경기도%' OR STUDENT_ADDRESS LIKE '%강원도%')
AND CAST(LEFT(ENTRANCE_DATE, 4) as UNSIGNED) BETWEEN 1900 AND 1999
ORDER BY `학생이름`;

-- 4번
SELECT PROFESSOR_NAME,
       PROFESSOR_SSN
FROM TB_PROFESSOR P
JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = P.DEPARTMENT_NO)
WHERE D.DEPARTMENT_NAME = '법학과'
ORDER BY PROFESSOR_SSN;

SELECT * FROM TB_STUDENT;

-- 5번 다시풀기
SELECT STUDENT_NO, FORMAT(POINT, 2) AS POINT
FROM TB_GRADE
WHERE TERM_NO = '200402'
AND CLASS_NO = 'C3118100'
ORDER BY POINT DESC ;

-- 6번
SELECT STUDENT_NO,
       STUDENT_NAME,
       DEPARTMENT_NAME
FROM TB_STUDENT T
JOIN TB_DEPARTMENT D ON (T.DEPARTMENT_NO = D.DEPARTMENT_NO)
ORDER BY STUDENT_NAME;

-- 7번
SELECT CLASS_NAME,
       DEPARTMENT_NAME
FROM TB_CLASS C
JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO);

-- 8번
SELECT CLASS_NAME, PROFESSOR_NAME, CLASS_TYPE
FROM TB_CLASS C
JOIN TB_CLASS_PROFESSOR CP ON (CP.CLASS_NO = C.CLASS_NO)
JOIN TB_PROFESSOR P ON (P.PROFESSOR_NO = CP.PROFESSOR_NO);

SELECT * FROM TB_PROFESSOR;
SELECT * FROM TB_CLASS;

-- 9번
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS C
         JOIN TB_CLASS_PROFESSOR CP ON (CP.CLASS_NO = C.CLASS_NO)
         JOIN TB_PROFESSOR P ON (P.PROFESSOR_NO = CP.PROFESSOR_NO)
        JOIN TB_DEPARTMENT D ON (P.DEPARTMENT_NO = D.DEPARTMENT_NO)
WHERE D.CATEGORY = '인문사회';

-- 10번
SELECT S.STUDENT_NO AS `학번`,
       S.STUDENT_NAME AS `학생 이름`,
       ROUND(AVG(G.POINT), 1) AS `전체 평점`
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE D.DEPARTMENT_NAME = '음악학과'
GROUP BY `학번`;

-- 11번
SELECT D.DEPARTMENT_NAME AS `학과이름`,
       S.STUDENT_NAME AS `학생이름`,
       P.PROFESSOR_NAME AS `지도교수이름`
FROM TB_STUDENT S
JOIN TB_PROFESSOR P ON(S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
JOIN TB_DEPARTMENT D ON(S.DEPARTMENT_NO = D.DEPARTMENT_NO)
WHERE STUDENT_NO = 'A313047';


-- 12번
SELECT S.STUDENT_NAME,
       G.TERM_NO AS TERM_NAME
FROM TB_GRADE G
JOIN TB_STUDENT S ON(G.STUDENT_NO = S.STUDENT_NO)
JOIN TB_CLASS C ON (C.CLASS_NO = G.CLASS_NO)
WHERE C.CLASS_NAME = '인간관계론';

-- 13번 다시 풀기

SELECT C.CLASS_NAME, D.DEPARTMENT_NAME
FROM TB_CLASS C
JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO)
WHERE D.CATEGORY = '예체능'
AND C.CLASS_NO NOT IN (SELECT CP.CLASS_NO
                         FROM TB_CLASS_PROFESSOR CP);

-- 13번 not exist 사용해서
SELECT C.CLASS_NAME, D.DEPARTMENT_NAME
FROM TB_CLASS C
         JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO)
WHERE D.CATEGORY = '예체능'
AND NOT EXISTS(
    SELECT 1
    FROM TB_CLASS_PROFESSOR CP
    WHERE CP.CLASS_NO = C.CLASS_NO
);

-- 14번 다시 풀기

SELECT S.STUDENT_NAME AS `학생이름`, IFNULL(P.PROFESSOR_NAME, '지도교수 미지정') AS `지도교수`
FROM TB_STUDENT S
LEFT JOIN TB_PROFESSOR P ON(P.PROFESSOR_NO = S.COACH_PROFESSOR_NO)
WHERE S.DEPARTMENT_NO = (
    SELECT D.DEPARTMENT_NO
    FROM TB_DEPARTMENT D
    WHERE D.DEPARTMENT_NAME = '서반아어학과'
    )
ORDER BY S.STUDENT_NO DESC;

SELECT *
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '020';

SELECT *
FROM TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '서반아어학과';

-- 15번

SELECT S.STUDENT_NO AS `학번`,
       S.STUDENT_NAME AS `이름`,
       D.DEPARTMENT_NAME AS `학과이름`,
       AVG(G.POINT) AS `평점`
FROM TB_STUDENT S
JOIN TB_GRADE G ON (G.STUDENT_NO = S.STUDENT_NO)
JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = S.DEPARTMENT_NO)
WHERE S.ABSENCE_YN = 'N'
GROUP BY S.STUDENT_NO
HAVING AVG(G.POINT) >= 4.0;

-- 16번 다시풀기

SELECT C.CLASS_NO, C.CLASS_NAME, AVG(G.POINT)
FROM TB_CLASS C
JOIN TB_GRADE G ON G.CLASS_NO = C.CLASS_NO
JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = C.DEPARTMENT_NO
WHERE D.DEPARTMENT_NAME = '환경조경학과'
AND C.CLASS_TYPE LIKE '%전공%'
GROUP BY C.CLASS_NO
ORDER BY C.CLASS_NO;

-- 17번
SELECT S.STUDENT_NAME, S.STUDENT_ADDRESS
FROM TB_STUDENT S
WHERE (S.DEPARTMENT_NO = (
    SELECT S2.DEPARTMENT_NO
    FROM TB_STUDENT S2
    WHERE S2.STUDENT_NAME = '최경희'
    ));

-- 18번
SELECT S.STUDENT_NO, S.STUDENT_NAME
FROM TB_STUDENT S
JOIN TB_GRADE G ON S.STUDENT_NO = G.STUDENT_NO
JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = S.DEPARTMENT_NO
WHERE D.DEPARTMENT_NAME = '국어국문학과'
GROUP BY S.STUDENT_NO
ORDER BY AVG(G.POINT) DESC
LIMIT 1;

-- 18번 서브쿼리로
SELECT S.STUDENT_NO, S.STUDENT_NAME
FROM TB_STUDENT S
         JOIN TB_GRADE G ON S.STUDENT_NO = G.STUDENT_NO
         JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = S.DEPARTMENT_NO
WHERE D.DEPARTMENT_NAME = '국어국문학과'
GROUP BY S.STUDENT_NO
HAVING AVG(G.POINT) >= ALL(
    SELECT AVG(G2.POINT)
    FROM TB_STUDENT S2
    JOIN TB_DEPARTMENT D2 ON D2.DEPARTMENT_NO = S2.DEPARTMENT_NO
    JOIN TB_GRADE G2 ON G2.STUDENT_NO = S2.STUDENT_NO
    WHERE D2.DEPARTMENT_NAME = '국어국문학과'
    GROUP BY S2.STUDENT_NO);

-- 19번
SELECT D.DEPARTMENT_NAME AS `계열학과명`,
       ROUND(AVG(G.POINT), 1) AS `전공평점`
FROM TB_GRADE G
JOIN TB_CLASS C ON C.CLASS_NO = G.CLASS_NO
JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = C.DEPARTMENT_NO
WHERE D.CATEGORY = (
    SELECT D2.CATEGORY
    FROM TB_DEPARTMENT D2
    WHERE D2.DEPARTMENT_NAME = '환경조경학과'
    )
GROUP BY `계열학과명`;

-- DDL

-- 1번
CREATE TABLE TB_CATEGORY(
    NAME VARCHAR(10),
    USE_YN CHAR(1) DEFAULT 'Y'
);


DESCRIBE  TB_CATEGORY;

-- 2번
CREATE TABLE TB_CLASS_TYPE(
    NO VARCHAR(5) PRIMARY KEY ,
    NAME VARCHAR(20)
);

-- 3번
ALTER TABLE TB_CATEGORY
ADD PRIMARY KEY (NAME);

-- 4번
ALTER TABLE TB_CLASS_TYPE
MODIFY COLUMN NAME VARCHAR(10) NOT NULL;

-- 5번
ALTER TABLE TB_CATEGORY
MODIFY COLUMN NAME VARCHAR(20) NOT NULL ;

ALTER TABLE TB_CLASS_TYPE
MODIFY COLUMN NO VARCHAR(10) NOT NULL ;

ALTER TABLE TB_CLASS_TYPE
MODIFY COLUMN NAME VARCHAR(20) NOT NULL ;

DESCRIBE TB_CLASS_TYPE;

-- 6번
ALTER TABLE TB_CATEGORY
RENAME COLUMN NAME TO CATEGORY_NAME;

ALTER TABLE TB_CLASS_TYPE
RENAME COLUMN NO TO CLASS_TYPE_NO;
ALTER TABLE TB_CLASS_TYPE
RENAME COLUMN NAME TO CLASS_TYPE_NAME;

-- 7번
ALTER TABLE TB_CATEGORY
RENAME COLUMN CATEGORY_NAME TO PK_CATEGORY_NAME;

ALTER TABLE TB_CLASS_TYPE
RENAME COLUMN CLASS_TYPE_NO TO PK_CLASS_TYPE_NO;

-- 8번
INSERT INTO TB_CATEGORY VALUES ('공학','Y');
INSERT INTO TB_CATEGORY VALUES ('자연과학','Y');
INSERT INTO TB_CATEGORY VALUES ('의학','Y');
INSERT INTO TB_CATEGORY VALUES ('예체능','Y');
INSERT INTO TB_CATEGORY VALUES ('인문사회','Y');
COMMIT;

-- 9번
ALTER TABLE TB_DEPARTMENT
ADD CONSTRAINT FK_DEPARTMENT_CATEGORY FOREIGN KEY (CATEGORY) REFERENCES TB_CATEGORY (PK_CATEGORY_NAME);

-- 10번
CREATE OR REPLACE VIEW `VW_학생일반정보` AS
SELECT STUDENT_NO AS `학번`,
       STUDENT_NAME AS `학생이름`,
       STUDENT_ADDRESS AS `주소`
FROM TB_STUDENT;

-- 11번
CREATE OR REPLACE VIEW `VW_지도면담` AS
SELECT S.STUDENT_NAME AS `학생이름`,
       D.DEPARTMENT_NAME AS `학과이름`,
       P.PROFESSOR_NAME AS `지도교수이름`
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = S.DEPARTMENT_NO
LEFT JOIN TB_PROFESSOR P ON P.PROFESSOR_NO = S.COACH_PROFESSOR_NO
ORDER BY DEPARTMENT_NAME;

-- 12번
CREATE OR REPLACE VIEW `VW_학과별학생수` AS
SELECT D.DEPARTMENT_NAME,
       COUNT(S.STUDENT_NO)
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = S.DEPARTMENT_NO
GROUP BY D.DEPARTMENT_NAME;

-- 13번
UPDATE VW_학생일반정보
SET 학생이름 = 'hy'
WHERE 학번 = 'A213046';

-- 14번
-- 오라클에서는 with readonly가 있다네요

-- 15번
SELECT G.CLASS_NO AS `과목번호`,
       C.CLASS_NAME AS `과목이름`,
       COUNT(STUDENT_NO) AS `누적수강생수(명)`
FROM TB_GRADE G
JOIN TB_CLASS C ON C.CLASS_NO = G.CLASS_NO
WHERE CAST(LEFT(TERM_NO, 4) AS UNSIGNED ) >= 2007
GROUP BY `과목번호`
ORDER BY `누적수강생수(명)` DESC
LIMIT 3;

-- DML

-- 1번
INSERT INTO TB_CLASS_TYPE (PK_CLASS_TYPE_NO, CLASS_TYPE_NAME)
VALUES
    ('01', '전공필수'),
    ('02', '전공선택'),
    ('03', '교양필수'),
    ('04', '교양선택'),
    ('05', '논문지도');

-- 2번
CREATE OR REPLACE VIEW `TB_학생일반정보` AS
SELECT STUDENT_NO AS `학번`,
       STUDENT_NAME AS `학생이름`,
       STUDENT_ADDRESS AS `주소`
FROM TB_STUDENT;

-- 3번
CREATE OR REPLACE VIEW `TB_국어국문학과` AS
SELECT STUDENT_NO AS `학번`,
       STUDENT_NAME AS `학생이름`,
       CONCAT(19, LEFT(STUDENT_SSN, 2)) AS `출생년도`,
       PROFESSOR_NAME AS `교수이름`
FROM TB_STUDENT S
JOIN TB_PROFESSOR P ON P.PROFESSOR_NO = S.COACH_PROFESSOR_NO;

-- 4번
UPDATE TB_DEPARTMENT
SET CAPACITY = ROUND(CAPACITY * 1.1, 0);

-- 5번
UPDATE TB_STUDENT
SET STUDENT_ADDRESS = '서울시 종로구 숭인동 181-21'
WHERE STUDENT_NO = 'A413042';

SELECT *
FROM TB_STUDENT
WHERE STUDENT_NO = 'A413042';

-- 6번
UPDATE TB_STUDENT
SET STUDENT_SSN = LEFT(STUDENT_SSN, 6);

-- 7번
UPDATE TB_GRADE G
SET POINT = 3.5
WHERE STUDENT_NO = (
        SELECT S.STUDENT_NO
        FROM TB_STUDENT S
        WHERE STUDENT_NAME = '김명훈'
        AND DEPARTMENT_NO = (
            SELECT D.DEPARTMENT_NO
            FROM TB_DEPARTMENT D
            WHERE DEPARTMENT_NAME = '의학과'
            )

    )
AND G.TERM_NO = '200501'
AND G.CLASS_NO = (
    SELECT C.CLASS_NO
    FROM TB_CLASS C
    WHERE CLASS_NAME = '피부생리학'
    );

-- 8번
DELETE FROM TB_GRADE
WHERE STUDENT_NO IN (
    SELECT S.STUDENT_NO
    FROM TB_STUDENT S
    WHERE S.ABSENCE_YN = 'Y'
    );