Intro to Database (7) : Oracle SQL developer 5 - 집합 연산

2019-07-08(월)

 

집합 연산자

1. UNION : 합, 중복제거 
2. UNION ALL : 합, 중복되는 포함
3. INTERSECT : 공통
4. MINUS : 첫 번째에만 소속

---

실습(1) : 두 테이블을 기본에 있는 테이블로 만든 후 집합 연산자를 수행하고 레코드의 개수를 주석으로 적으세요. 

1.  하위그룹 개념의 테이블을 2개 만들기

create table a_student as 
select * from student where stu_dept in ('기계','전기전자');
create table b_student as 
select * from student where stu_dept in ('전기전자','컴퓨터정보');

 

2. 만든 테이블로 4가지 집합 연산자 실습을 수행하기

/*1.UNION*/
SELECT * FROM A_STUDENT
UNION 
SELECT * FROM B_STUDENT;

/*2.UNION ALL*/
SELECT * FROM A_STUDENT
UNION ALL
SELECT * FROM B_STUDENT; /*UNION ALL의 경우 겹치는 부분도 반복되서(중복) 출력이 되는것을 확인가능*/

/*3.INTERSECTION*/
SELECT * FROM A_STUDENT
INTERSECT /*교집합 : 중간에 겹치는 부분만 출력(여기서는 전기전자)*/
SELECT * FROM B_STUDENT;

/*4.MINUS*/
SELECT * FROM A_STUDENT
MINUS /*차집합 개념 ; B와의 교집합은 빠지고 A에 해당하는 부분만(여기서는 기계과에 해당)*/
SELECT * FROM B_STUDENT;

---

실습 (2) : 지난시간에 했던 JOIN과 SUBQUERY 복습 예제

1. 복잡한 검색을 위한 동일한 결과를 내는 3가지 방법 코딩

/*JOIN AND SUBQUERY*/
--8. 김인중 학생이 수강하는 과목이름과 강의교수를 검색
--(1).데이터를 병합 (equi join) 한후 김인중학생이 듣는 과목과 교수를 찾아냄
SELECT SUB_NAME, SUB_PROF FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT WHERE STU_NAME = '김인중';

SELECT * FROM STUDENT JOIN ENROL ON STUDENT.STU_NO = ENROL.STU_NO JOIN SUBJECT
ON ENROL.SUB_NO = SUBJECT.SUB_NO; /*JOIN - ON의 경우 키를 써야 한다*/

SELECT * FROM STUDENT S JOIN ENROL E ON S.STU_NO = E.STU_NO JOIN SUBJECT SB
ON E.SUB_NO = SB.SUB_NO; /*약칭을 지정사용도 가능*/

SELECT SUB_NAME, SUB_PROF FROM STUDENT JOIN ENROL USING(STU_NO) 
JOIN SUBJECT USING(SUB_NO) WHERE STU_NAME = '김인중'; /*JOIN ~ USING*/

--(2).데이터를 병합(equi join)한 새로운 테이블을 만든후 김인중학생이 듣는 과목과 교수를 찾아냄
CREATE TABLE M_TABLE AS 
SELECT * FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT;
SELECT SUB_NAME, SUB_PROF FROM M_TABLE WHERE STU_NAME = '김인중';

--(3).김인중학생의 학생번호를 찾아낸 다음 수강테이블에서 수강과목번호를 찾아낸후 과목테이블에서 과목이름과 교수이름을 찾아냄
SELECT STU_NO FROM STUDENT WHERE STU_NAME = '김인중';
SELECT SUB_NO FROM ENROL 
WHERE STU_NO = (SELECT STU_NO FROM STUDENT WHERE STU_NAME = '김인중');
/*하나 하나씩 단계별로 들어가는 것을 볼 수 있다.*/
SELECT SUB_NAME, SUB_PROF FROM SUBJECT
WHERE SUB_NO = (SELECT SUB_NO FROM ENROL
WHERE STU_NO = (SELECT STU_NO FROM STUDENT WHERE STU_NAME = '김인중'));

2.  예제 

--9. 김인중 학생이 수강하는 과목이름과 강의교수를 검색 (natural join)
SELECT SUB_PROF FROM SUBJECT  
NATURAL JOIN ENROL NATURAL JOIN STUDENT WHERE STU_NAME = '김인중';

--10. 김인중 학생이 수강하는 과목이름과 강의교수를 검색 (join ~ using)
SELECT STU_NAME, SUB_NAME, SUB_PROF FROM STUDENT 
JOIN ENROL USING(STU_NO) JOIN SUBJECT USING (SUB_NO) WHERE STU_NAME='김인중';

--11. 강종영 교수가 강의하는 과목을 수강하는 학생의 이름을 검색
SELECT STU_NAME, SUB_NAME, SUB_PROF FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT
WHERE SUBJECT.SUB_PROF = '강종영';

--12. 컴퓨터개론을 수강하는 학생들의 학번과 이름을 검색
SELECT STU_NO, STU_NAME FROM STUDENT NATURAL JOIN SUBJECT
WHERE SUB_NAME='컴퓨터개론';

--13. 시스템분석설계, 소프트웨어공학을 수강하는 학생들의 학번, 이름, 학번순으로 검색
SELECT STUDENT.STU_NO, STUDENT.STU_NAME, SUB_NAME FROM STUDENT 
INNER JOIN ENROL ON STUDENT.STU_NO=ENROL.STU_NO 
INNER JOIN SUBJECT ON ENROL.SUB_NO=SUBJECT.SUB_NO 
WHERE SUBJECT.SUB_NAME='시스템분석설계'
OR SUBJECT.SUB_NAME='소프트웨어공학' 
ORDER BY STU_NO,STU_NAME,STU_NO;

--14. 과목이 1학년 과목이거나 컴퓨터정보과에 개설된 과목일 경우 그 과목들을 수강하는 학생들의 학번이나 이름을 검색
select stu_no, stu_name from student 
natural join enrol natural join subject 
where sub_grade= '1' or sub_dept = '컴퓨터정보';
--15. 기계공작법, 기초전자실험을 수강하는 학생들의 학번, 이름을 학번순으로 검색 (natural join)
SELECT STUDENT.STU_NO, STUDENT.STU_NAME,SUB_NAME FROM STUDENT 
INNER JOIN ENROL ON STUDENT.STU_NO=ENROL.STU_NO 
INNER JOIN SUBJECT ON ENROL.SUB_NO=SUBJECT.SUB_NO 
WHERE SUBJECT.SUB_NAME='기계공작법' OR SUBJECT.SUB_NAME='기초전자실험' 
ORDER BY STUDENT.STU_NO DESC;
--16. 김태영교수, 김유석교수가 강의하는 과목을 수강하는 학생의 학번을 검색
select STU_NO from STUDENT 
natural join ENROL natural join SUBJECT 
where SUB_PROF = '김태영' or SUB_PROF = '김유석'; 
--17. 학생의 이름과 점수를 검색
SELECT STU_NAME, ENR_GRADE FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT 
--18. 이름이 옥한빛인 학생이 수강하는 과목이름 검색
--SELECT SUB_NAME FROM SUBJECT 
--WHERE SUB_NO = (SELECT SUB_NO FROM ENROL 
--WHERE STU_NO = (SELECT STU_NO FROM STUDENT WHERE STU_NAME = '옥한빛'));
--
SELECT SUB_NAME FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT WHERE STU_NAME = '옥한빛';

--19. 여학생이 수강중인 과목이름을 모두 검색
--SELECT SUB_NAME FROM SUBJECT
--WHERE SUB_NO = (SELECT SUB_NO FROM ENROL 
--WHERE STU_NO = (SELECT STU_NO FROM STUDENT WHERE STU_GENDER = 'F'));
SELECT SUB_NAME FROM SUBJECT
NATURAL JOIN STUDENT NATURAL JOIN ENROL WHERE STU_GENDER = 'F';

--20. 체중이 60이상인 학생이 수강하는 과목이름을 모두 검색
SELECT SUB_NAME FROM SUBJECT
NATURAL JOIN STUDENT NATURAL JOIN ENROL WHERE STU_WEIGHT >= 60;

--21. 컴퓨터정보과에 개설된 과목을 수강하는 학생들의 학번과 이름을 검색
SELECT STU_NO, STU_NAME FROM STUDENT
NATURAL JOIN SUBJECT NATURAL JOIN ENROL WHERE SUB_DEPT ='컴퓨터정보'; 

select distinct STU_NO, STU_NAME from STUDENT 
natural join ENROL natural join SUBJECT 
where SUB_DEPT = '컴퓨터정보';

--22. 점수가 70점 이상인 학생수를 검색
SELECT count(stu_name) FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT WHERE ENR_GRADE >= 70;

--23. 여학생이면서 구봉규교수 과목을 수강하는 학생의 이름 검색
select STU_NAME from STUDENT 
natural join ENROL natural join SUBJECT 
where STU_GENDER = 'F' AND SUB_PROF = '구봉규'; 

--24. 김종헌학생의 평균점수보다 높은 학생의 학번과 이름을 검색
/*방법1*/SELECT STU_NO, STU_NAME FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT 
WHERE ENR_GRADE > (SELECT AVG(ENR_GRADE) FROM ENROL 
NATURAL JOIN STUDENT NATURAL JOIN SUBJECT WHERE STU_NAME = '김종헌') AND STU_NAME <> '김종헌';
/*김종헌의 평균을 먼저 구하고 */
(SELECT AVG(ENR_GRADE) FROM ENROL 
NATURAL JOIN STUDENT NATURAL JOIN SUBJECT WHERE STU_NAME = '김종헌')
/*이 평균보다 더 큰 사람의 학번과 이름을 출력하고, 김종헌 본인은 제외한다*/
/*방법2*/
select m_table.stu_no, m_table.stu_name, s.a from m_table , 
(select stu_name, avg(enr_grade) a from m_table group by stu_name) s 
where s.stu_name= m_table.stu_name and s.a > (select avg(enr_grade) 
from m_table where stu_name='김종헌'); 
/*방법3*/ 
select stu_name from student 
natural join enrol natural join subject where enr_grade >= 
(select avg(enrol.enr_grade) from student natural join enrol natural join subject where stu_name like '김종헌');
create table ~~~ as (select stu_no, stu_name, stu_dept, avg(enrol.enr_grade) 점수 
from student natural join enrol group by stu_no, stu_name, stu_dept);
drop table ~~~;
/*방법4 : 다른 테이블 다 합친 테이블 ; N테이블 만들고*/
SELECT STU_NO, STU_NAME FROM N_TABLE_1
WHERE ENR_GRADE > (SELECT AVG(ENR_GRADE) FROM N_TABLE_1 WHERE STU_NAME = '김종헌') AND STU_NAME <> '김종헌';

--25. 김인중학생의 평균점수보다 높은 학생의 학번과 이름을 검색
SELECT STU_NO, STU_NAME FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT 
WHERE ENR_GRADE > (SELECT AVG(ENR_GRADE) FROM ENROL 
NATURAL JOIN STUDENT NATURAL JOIN SUBJECT WHERE STU_NAME = '김인중') 
AND STU_NAME <> '김인중';

--26. 전체평균보다 높은 학생의 학번, 이름, 과목이름, 점수를 검색
SELECT STU_NO, STU_NAME, SUB_NAME, ENR_GRADE FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT 
WHERE ENR_GRADE > (SELECT AVG(ENR_GRADE) FROM ENROL); 
(SELECT AVG(ENR_GRADE) FROM ENROL); 

코드를 실행하면서 이해가 잘 안되는 부분이 있거나, 오류가 뜨는 줄이 있다면 댓글이나 톡으로 질문해주시면 최대한 알려드리거나 수정할 수 있도록 하겠습니다. 감사합니다. :D