SQL활용(학사정보 데이터베이스 구축 및 활용)

1. 학사정보 데이터베이스 구축

-1. 학사정보 새로운 접속 만들기

• 사용자 : HAKSA
• 암호 : 1234
• 기본 tablespace : USERS
• 임시 tablespace : TEMP
• Nation role : connect, resource, dba
• 접속명 : local-HAKSA

-2. 학사정보 테이블 생성

CREATE TABLE 학과 (
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(10) NOT NULL
);

CREATE TABLE 교수 (
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(10) NOT NULL,
    학과번호 NUMBER NOT NULL,
    FOREIGN KEY(학과번호) REFERENCES 학과(번호)
);

CREATE TABLE 학생(
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(10) NOT NULL,
    주소 NVARCHAR2(50),
    학년 NUMBER,
    키 NUMBER,
    몸무게 NUMBER,
    상태 NVARCHAR2(4),
    입력일자 DATE,
    학과번호 NUMBER NOT NULL,
    FOREIGN KEY(학과번호) REFERENCES 학과(번호)
);

CREATE TABLE 과목(
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(11) NOT NULL,
    학점 NUMBER,
    교수번호 NUMBER NOT NULL,
    FOREIGN KEY(교수번호) REFERENCES 교수(번호)
);

CREATE TABLE 수강내역(
    학생번호 NUMBER NOT NULL,
    과목번호 NUMBER NOT NULL,
    점수 NUMBER(4,1),
    등급 NVARCHAR2(1),
     CONSTRAINT 수강내역PK  PRIMARY KEY(학생번호, 과목번호),
    FOREIGN KEY(학생번호) REFERENCES 학생(번호),
    FOREIGN KEY(과목번호) REFERENCES 과목(번호)
);

-3. 학사정보 데이터 입력

CREATE SEQUENCE SEQ;
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '소프트웨어공학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '컴퓨터공학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '경영정보학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '경영학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '연극영화학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '철학과');
SELECT * FROM 학과;

DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ;
INSERT INTO 교수 VALUES(SEQ.nextval, '이해진', 1);
INSERT INTO 교수 VALUES(SEQ.nextval, '김정주', 1);
INSERT INTO 교수 VALUES(SEQ.nextval, '김이숙', 1);
INSERT INTO 교수 VALUES(SEQ.nextval, '이석호', 2);
INSERT INTO 교수 VALUES(SEQ.nextval, '김사부', 3);
INSERT INTO 교수 VALUES(SEQ.nextval, '장미희', 5);
INSERT INTO 교수 VALUES(SEQ.nextval, '백남영', 5);
INSERT INTO 교수 VALUES(SEQ.nextval, '김현이', 5);
INSERT INTO 교수 VALUES(SEQ.nextval, '이사부', 5);
SELECT * FROM 교수;

DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ;
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '데이터베이스관리', 3, 3);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '데이터베이스프로그래밍', 3, 3);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, 'SQL활용', 3, 4);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '데이터베이스', 4, 5);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '인터넷마케팅', 3, 5);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '연기실습', 2, 7);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '영화제작실습', 2, 8);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '영화마케팅', 1, 9);
SELECT * FROM 과목;

INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(1, '김이향', '인천남동구', 4, 166, 56, '재학', '2005-03-01',5);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(6, '박지은', '서울양천구', 2, 183, 65, '재학', '2015-02-14',3);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(7, '안칠현', '서울금천구', 1, 178, 65, '재학', '2016-02-19',1);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(8, '김태희', '서울구로구', 1, 165, 45, '재학', '2016-02-19',3);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(9, '채영', '서울구로구', 1, 172, 47, '재학', '2016-02-19',2);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(11, '영란', '서울강남구', 1, 162, 43, '재학', '2016-02-19',5);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(12, '윤호', '서울강남구', 1, 184, 66, '휴학', '2016-08-10',1);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(13, '보아', '서울구로구', 1, 162, 45, '재학', '2016-02-19',1);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(14, '문근영', '서울강남구', 1, 165, 45, '재학', '2016-02-19',3);
INSERT INTO 학생(번호,이름,주소,학년,상태,입력일자,학과번호)
VALUES(3, '이세영', '서울구로구', 4, '재학', '2013-03-01',4);
INSERT INTO 학생(번호,이름,주소,학년,상태,입력일자,학과번호)
VALUES(4, '문주원', '경기부천시', 3, '재학', '2014-02-13',3);
INSERT INTO 학생(번호,이름,주소,학년,상태,입력일자,학과번호)
VALUES(5, '성춘향', '서울영등포구', 3, '재학', '2014-02-13',5);
INSERT INTO 학생(번호,이름,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(10, '박수애', 1, 168, 46, '재학', '2016-02-19',2);
INSERT INTO 학생(번호,이름,주소,학년,키,상태,입력일자,학과번호)
VALUES(2, '박보검', '인천부평구', 4, 168, '재학', '2010-03-01',6);
SELECT * FROM 학생;

INSERT INTO 수강내역 VALUES(7, 1, 84.5, 'B');
INSERT INTO 수강내역 VALUES(12, 1, 50, 'F');
INSERT INTO 수강내역 VALUES(13, 1, 90, 'A');
INSERT INTO 수강내역 VALUES(7, 2, 80, 'B');
INSERT INTO 수강내역 VALUES(13, 2, 94.5, 'A');
INSERT INTO 수강내역 VALUES(9, 3, 90, 'A');
INSERT INTO 수강내역 VALUES(10, 3, 70, 'C');
INSERT INTO 수강내역 VALUES(4, 4, 90, 'A');
INSERT INTO 수강내역 VALUES(6, 4, 55, 'F');
INSERT INTO 수강내역 VALUES(8, 4, 85, 'B');
INSERT INTO 수강내역 VALUES(14, 4, 95, 'A');
INSERT INTO 수강내역 VALUES(4, 5, 70, 'C');
INSERT INTO 수강내역 VALUES(6, 5, 95, 'A');
INSERT INTO 수강내역 VALUES(8, 5, 90, 'A');
INSERT INTO 수강내역 VALUES(14, 5, 95, 'A');
INSERT INTO 수강내역 VALUES(6, 8, 90, 'A');
INSERT INTO 수강내역 VALUES(8, 8, 80, 'B');
INSERT INTO 수강내역 VALUES(14, 8, 90, 'A');
INSERT INTO 수강내역 VALUES(1, 6, 85, 'B');
INSERT INTO 수강내역 VALUES(5, 6, 80, 'B');
INSERT INTO 수강내역 VALUES(11, 6, 78.5, 'C');
INSERT INTO 수강내역 VALUES(1, 7, 95, 'A');
INSERT INTO 수강내역 VALUES(5, 7, 85, 'B');
INSERT INTO 수강내역 VALUES(1, 8, 100, 'A');
SELECT * FROM 수강내역;

2. 학사정보 데이터베이스 활용

-1. 학생 테이블의 모든 열을 검색하라(단, 반드시 와일드카드를 사용한다.)

SELECT * FROM 학생;

-2. 학생 테이블의 이름, 학년, 주소 열만 선택적으로 검색하라

SELECT 이름, 학년, 주소 FROM 학생;

-3. 학생 테이블의 이름과 주소 열만 검색하라(단, 이름 열은 화면에 성명으로 표시하고 주소 열은 현재 주소지로 표시한다)

SELECT 이름 AS "성명", 주소 AS "현재 주소지" FROM 학생;

-4. 학생 테이블의 학년 열을 검색하다(단, 중복되는 행은 딱 한 번만 출력한다)

SELECT DISTINCT 학년 FROM 학생;

-5. 학생 번호가 1인 학생을 검색하라

SELECT * FROM 학생 WHERE 번호 = 1;

-6. 키가 165 미만인 학생을 검색하라

SELECT * FROM 학생 WHERE 키 < 165;

-7. 1학년이고 동시에 키가 170 이상인 학생의 이름, 학년, 키, 몸무게 열을 검색하라

SELECT 이름, 학년, 키, 몸무게 FROM 학생 WHERE 학년 = 1 AND 키 >= 170;

-8. 1학년이거나 또는 키가 170 이상인 학생의 이름, 학년, 키, 몸무게 열을 검색

SELECT 이름, 학년, 키, 몸무게 FROM 학생 WHERE 학년 = 1 OR 키 >= 170;

-9. 몸무게 45보다 크거나 같고 50보다 작거나 같은 학생의 이름, 주소, 키, 몸무게 열을 검색하되, BETWEEN 키워드를 사용하라

SELECT 이름, 주소, 키, 몸무게 FROM 학생 WHERE 몸무게 BETWEEN 45   AND 50;

-10. 몸무게 45보다 크거나 같고 50보다 작거나 같은 학생의 이름, 주소, 키, 몸무게 열을 검색하되, AND 연산자를 사용

SELECT 이름, 주소, 키, 몸무게 FROM 학생 WHERE 몸무게 >= 45 AND 몸무게 <= 50;

-11. 학생의 번호, 이름, 학년열을 검색하라(단, 검색결과에는 2학년과 3학년만 포함되어야 하며, 반드시 IN 연산자를 사용하여야 한다)

SELECT 번호, 이름, 학년 FROM 학생 WHERE 학년 IN (2,3);

-12. 학생의 번호, 이름, 학년열을 검색하라(단, 검색 결과에는 2학년과 3학년만 포함되어야 하며, 반드시 OR 연산자를 사용하여야 한다)

SELECT 번호, 이름, 학년 FROM 학생 WHERE 학년 = 2 OR 학년 = 3;

-13. 박씨 성을 가진 학생의 모든 열을 검색하라

SELECT * FROM 학생 WHERE 이름 LIKE '박%';

-14. 김씨, 이씨, 박씨 성을 가진 학생 모두를 검색하되 반드시 LIKE 연산자를 사용하라

SELECT * FROM 학생 WHERE 이름 LIKE '김%' OR 이름 LIKE '이%' OR 이름 LIKE '박%';
SELECT * FROM 학생 WHERE REGEXP_LIKE(이름,'김|이|박');

-15. 김씨, 이씨, 박씨 성을 가진 학생 모두를 검색하되 반드시 UNION을 사용하라

SELECT * FROM 학생 WHERE 이름 LIKE '김%'
UNION
SELECT * FROM 학생 WHERE 이름 LIKE '이%'
UNION
SELECT * FROM 학생 WHERE 이름 LIKE '박%';

-16. 주소 데이터가 없는 학생의 이름, 학년, 주소를 검색하라

SELECT 이름, 학년, 주소 FROM 학생 WHERE 주소 IS NULL;

-17. 몸무게 데이터가 있는 학생의 성명, 학년, 몸무게를 검색하라

SELECT 이름, 학년, 몸무게 FROM 학생 WHERE 몸무게 IS NOT NULL;

-18. 학생 테이블에서 모든 열에 저장된 데이터를 검색하되, 이름 가나다순으로 정렬하여 출력하라

SELECT * FROM 학생 ORDER BY 이름 ASC;

-19. 학생의 이름, 주소, 키 데이터를 검색하되 키가 큰 학생부터 출력하라

SELECT 이름, 주소, 키 FROM 학생 ORDER BY 키 DESC NULLS LAST;

-20. 1학년 학생의 이름, 학년, 주소, 몸무게 데이터를 검색하라(단 ,반드시 체중이 적은 학생부터 출력하라)

SELECT 이름, 학년, 주소, 몸무게 FROM 학생 WHERE 학년 = 1 ORDER BY 몸무게;

-21. 1학년 학생의 이름, 학년, 키, 몸무게 데이터를 검색하라(단, 키 내림차순으로 정렬하고, 같은 키는 몸무게 오름차순으로 정렬)

SELECT 이름, 학년, 키, 몸무게 FROM 학생 WHERE 학년 = 1 ORDER BY 키 DESC, 몸무게 ASC;

-22. 학생의 번호, 이름, 주소를 검색하되 이름을 가나다 순으로 정렬해라

SELECT 번호, 이름, 주소 FROM 학생 ORDER BY 이름 ASC;

-23. 학생 테이블에서 '문주원' 학생과 학년이 동일한 모든 학생의 이름과 키, 몸무게를 검색하라

SELECT 이름, 키, 몸무게 FROM 학생 WHERE 학년 = (SELECT 학년 FROM 학생 WHERE 이름 = '문주원');

-24. 1번 학과 학생들의 평균 키보다 작은 학생의 이름, 학년, 키를 검색하라

SELECT 이름, 학년, 키 FROM 학생 WHERE 키 < (SELECT AVG(키) FROM 학생 WHERE 학과번호 = 1);

-25. '김태희' 학생과 학년이 같고, '김태희' 학생보다 큰 학생의 이름, 학년, 키를 검색

SELECT 이름, 학년, 키 FROM 학생 WHERE 학년 = (SELECT 학년 FROM 학생 WHERE 이름 = '김태희') AND 키 > (SELECT 키 FROM 학생 WHERE 이름 = '김태희');

-26. 키가 165인 학생의 번호, 이름, 키를 검색하라

SELECT 번호, 이름, 키 FROM 학생 WHERE 키 = 165;

-27. 학생의 수를 검색하라(단, 출력 결과 열은 학생수로 표시한다)

SELECT COUNT(번호) AS "학생수" FROM 학생;

-28. 박씨 성을 가진 학생 중에서 몸무게 정보가 있는 학생의 수를 검색하라(단, 출력결과 열은 '몸무게 정보가 있는 학생수'로 표시한다)

SELECT COUNT(이름) AS "정보있는학생의수" FROM 학생 WHERE 이름 LIKE('박%') AND 몸무게 IS NOT NULL;

-29. 번호가 1인 과목의 평균 점수를 검색하라

SELECT AVG(점수) FROM 수강내역 WHERE 과목번호 = 1;

-30. 과목별 평균 점수를 검색하라(단, 두 번째 출력 결과 열은 '과목평균점수'로 표시한다)

SELECT 과목번호, AVG(점수) AS "과목평균점수" FROM 수강내역 GROUP BY 과목번호 ORDER BY 과목번호;

-31. 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑한 후, 학과별 , 학년별 인원수, 평균키를 검색하라 (단, 세 번째 출력 결과 열은 '인원수'로 표시하고, 네번째 열은 '평균키'로 표시한다)

SELECT 학과번호, 학년, COUNT(학년) AS "인원수", TRUNC(AVG(키)) AS "평균키"  FROM 학생 GROUP BY 학과번호, 학년 ORDER BY 학년;

-32. 누적학생 수가 네 명 이상인 과목의 평균 점수를 검색하라 (단, 두 번째 출력 결과열은 '누적학생수'로 표시하고 세 번째 열은 '과목평균점수'로 표시한다)

수강내역 학생번호 = 학생 번호

SELECT 수강내역.과목번호, COUNT(수강내역.학생번호) AS "누적학생수",AVG(수강내역.점수) AS "과목평균점수" FROM 학생
INNER JOIN 수강내역 
    ON 학생.번호 = 수강내역.학생번호
GROUP BY 수강내역.과목번호 HAVING COUNT(학생.이름) >= 4;

-33. 과목 평균 점수가 높은 행부터 출력되도록 6번 문제를 해결하는 SQL 명령문 변경하라

SELECT 수강내역.과목번호, COUNT(수강내역.학생번호) AS "누적학생수",AVG(수강내역.점수) AS "과목평균점수" FROM 학생
INNER JOIN 수강내역 
    ON 학생.번호 = 수강내역.학생번호
GROUP BY 수강내역.과목번호 HAVING COUNT(학생.이름) >= 4
ORDER BY 과목평균점수 DESC;

-34. 학생들의 학번, 이름, 소속학과 이름을 검색하라

학생 학과번호 = 학과 번호

SELECT 학생.번호, 학생.이름, 학과.이름 FROM 학생
INNER JOIN 학과
    ON 학생.학과번호 = 학과.번호;

-35. 교수의 번호, 이름, 소속 학과 이름을 검색하라

SELECT 교수.번호, 교수.이름, 학과.이름 FROM 교수
INNER JOIN 학과
    ON 교수.학과번호 = 학과.번호;

-36. '김이향' 학생의 번호, 이름, 소속 학과 이름을 검색하라

SELECT 학생.번호, 학생.이름, 학과.이름 FROM 학생
INNER JOIN 학과
    ON 학생.학과번호 = 학과.번호
WHERE 학생.이름 = '김이향';

-37. 키가 180 이상인 학생의 번호, 이름, 키, 소속 학과 이름을 검색하라

SELECT 학생.번호, 학생.이름, 학생.키, 학과.이름 FROM 학생
INNER JOIN 학과
    ON 학생.학과번호 = 학과.번호
WHERE 학생.키 >= 180;

-38. 학과별 교수별 개설 과목 목록 정보인 학과 이름, 교수 이름, 과목 이름을 검색하라

SELECT 학과.이름, 교수.이름, 과목.이름 FROM 학과
INNER JOIN 교수
    ON 학과.번호 = 교수.학과번호
INNER JOIN 과목
    ON  교수.번호 = 과목.교수번호;

-39. 학과별 개설 과목 목록 정보인 학과 이름, 과목 이름을 검색하라

SELECT 학과.이름, 과목.이름 FROM 학과
INNER JOIN 교수
    ON 학과.번호 = 교수.학과번호
INNER JOIN 과목
    ON  교수.번호 = 과목.교수번호;