2일차

#4월7일 (목)_To do
1. Join()
-cross join/ equal/ non-equal/ self/ outer
2. 부질의 (Sub Query)
3. 집합연산자
4. 적용 연습 문제 풀이

===================================================
# 계정생성
create user kyu identified by 1234;
 grant connect,resource, dba to kyu;

# JOIN
▶ CROSS JOIN
1행에 다른 테이블의 행을 모두 삽입  

▶OUTER
-RIGHT OUTER JOIN
-LEFT OUTER JOIN
-SELF JOIN

-NON EQUAL JOIN

-OUTER JOIN()

 

# 부질의 (SUB쿼리)

 

# IN

 

#집합연산자
--종류: UNION/ UNION ALL/ INTERSECT/ MINUS
--UNION =A+B-(A∩B) = (A∪B)-(A∩B)
--UNION ALL = A+B+(A∩B) = (A∪B)+(A∩B)
--INTERSECT = (A∩B)
--MINUS = A-B

0407_SQL(연습문제)_E.docx

0.01MB

0407_SQL연습문제_최정규_C.docx

0.02MB

0407_SQL연습문제_최정규_D.docx

0.02MB

0407_SQL연습문제_최정규_E.docx

0.02MB

0407_SQL(연습문제)_C.docx

0.01MB

---

▶과제 C,D,E

/*계정생성*/
create user kyu identified by 1234;
 grant connect,resource, dba to kyu;
 
 --JOIN() 에 대한 내용
 --CROSS JOIN
 --2개 이상의 테이블을 조건없이 실행시키는 조인문
 SELECT * FROM STUDENT;
 SELECT * FROM ENROL;
 SELECT STUDENT.*, ENROL.*
 FROM STUDENT CROSS JOIN ENROL;
 
 --EQUAL JOIN()
 --공통 분모가 있었을때 취하는 조인문
 SELECT STUDENT.STU_NO, STU_NAME, STU_DEPT, ENR_GRADE
 FROM STUDENT, ENROL
 WHERE STUDENT.STU_NO = ENROL.STU_NO;
 
 --NATURAL JOIN(하나의 공통 분모가 있을때 사용하는 조인문/ 2개이상은 에러)
 SELECT STU_NO,STU_NAME, STU_DEPT, ENR_GRADE
 FROM STUDENT
 NATURAL JOIN ENROL;
 
 --JOIN (USING)
 SELECT STU_NO, STU_NAME, STU_DEPT, ENR_GRADE
 FROM STUDENT JOIN ENROL USING(STU_NO);
 
 --ON ->의존해서
 SELECT STUDENT.STU_NO, STU_NAME, STU_DEPT, ENR_GRADE
 FROM STUDENT JOIN ENROL ON(STUDENT.STU_NO = ENROL.STU_NO);
 
--NON EQUAL JOIN
--공통 분모가 없어도 범위를 지정해서
SELECT * FROM EMP;
SELECT * FROM SALGRADE;
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

--SELF JOIN
--EMP테이블에서 자신의 상급자를 구하는 질의문
SELECT * FROM EMP;
SELECT A.EMPNO AS 사원번호, A.ENAME AS 사원명, B.EMPNO AS 상급자사원번호, B.ENAME AS 상급자이름
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO;

--위의 내용을 FROM절에서 사용되도록 하는 JOIN-ON 사용하여 구성
SELECT A.EMPNO , A.EMPNAME, B.EMPNO, B.ENAME
FROM EMP A JOIN EMP B ON(A.EMPNO = B.EMPNO);

--OUTER JOIN()
SELECT * FROM SUBJECT;
SELECT * FROM ENROL;

SELECT A.*,SUB_NAME
FROM ENROL A, SUBJECT B
WHERE A.SUB_NO = B.SUB_NO
ORDER BY 1; 
--위 내용을OUTER JOIN 으로
SELECT A.*, SUB_NAME
FROM ENROL A RIGHT OUTER JOIN SUBJECT B
ON A.SUB_NO = B.SUB_NO
ORDER BY 1;

SELECT * FROM EMP;!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT A.EMPNO AS 사원번호, A.ENAME AS 사원이름, B.EMPNO AS 상급자사원번호, B.ENAME AS 상급자사원이름
FROM EMP A LEFT OUTER JOIN EMP B ON A.MGR=EMPNO;

--부질의 (SUB쿼리)
--학생테이블에서 옥성우 학생보다 신장이 큰 학생들의 학번, 이름, 신장을 구하라
SELECT STU_HEIGHT
FROM STUDENT
WHERE STU_NAME='옥성우';
SELECT STU_NO, STU_NAME,STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT>=172;
--위내용을 SUB쿼리로
SELECT STU_NO, STU_NAME, STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT>= (SELECT STU_HEIGHT
                    FROM STUDENT
                    WHERE STU_NAME='옥성우');
                    

--학생테이블에서 박희철 학생과 같은 테중을 가지고 있는 학생의 정보를 구하라
--단일열 서브쿼리
SELECT * 
FROM STUDENT
WHERE STU_WEIGHT = (SELECT STU_WEIGHT
                    FROM STUDENT 
                    WHERE STU_NAME='박희철')
AND STU_NAME <>'박희철'; /*박희철을 뺴라 (예외처리)*/                    

--학생테이블에서  '컴퓨터 정보'학과와 같은반인 다른 학과의 학생정보를 구하라
--IN 을 사용
SELECT * 
FROM STUDENT 
WHERE STU_CLASS IN( SELECT STU_CLASS 
                    FROM STUDENT
                    WHERE STU_DEPT='컴퓨터정보')
                    AND STU_DEPT<>'컴퓨터정보';
                    
--학생테이블에서 전체학생들의 평균신장보다 큰 학생을 찾아내는 질의문을 코딩
SELECT *
FROM STUDENT
WHERE STU_HEIGHT>(SELECT AVG(STU_HEIGHT)
                    FROM STUDENT);
                    
--학생테이블에서 신장이 모든 학과들의 평균신장보다 큰학생의 정보를 찾아내시오
SELECT *
FROM STUDENT
WHERE STU_HEIGHT > ALL(SELECT AVG(STU_HEIGHT)
                        FROM STUDENT
                        GROUP BY STU_DEPT);
SELECT STU_DEPT, AVG(STU_HEIGHT)
FROM STUDENT
GROUP BY STU_DEPT;

SELECT *
FROM STUDENT
WHERE STU_HEIGHT > (SELECT MAX(AVG(STU_HEIGHT))
                    FROM STUDENT
                    GROUP BY STU_DEPT);
                    
--학생테이블에서 컴퓨터정보의 최소신장과 비교하여 최소신장이 
--더 큰 학과의 학과 명과 최소 신장을 구하라
SELECT STU_DEPT, MIN(STU_HEIGHT)
FROM STUDENT
GROUP BY STU_DEPT HAVING MIN(STU_HEIGHT)> (SELECT MIN(STU_HEIGHT)
                                            FROM STUDENT
                                            WHERE STU_DEPT='컴퓨터정보');

--101번 과목을 수강하는 학생들의 정보를 검색하시오
/*sub쿼리 방법*/
SELECT *
FROM STUDENT
WHERE STU_NO IN (SELECT STU_NO 
                FROM ENROL 
                WHERE SUB_NO=101);
                
--101번 과목을 수강하는 학생들의 학번 이름 점수를 출력하라!!!!!!!!!!!!!!!!!!!
SELECT A.STU_NO, A.STU_NAME, B.ENR_GRADE
FROM STUDENT A, ENROL B
WHERE A.STU_NO=B.STU_NO AND B.SUB_NO=101;

--복수열 부질의(SUB query)
CREATE TABLE TEST(EMPNO, ENAME, SAL, COMM, DEPTNO)
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO=1;
DESC TEST;

INSERT INTO TEST VALUES(11,'APPLE',1000,NULL,30);
INSERT INTO TEST VALUES(12,'BANANA',2000,100,30);
INSERT INTO TEST VALUES(13,'CAT',3000,0,10);
INSERT INTO TEST VALUES(14,'DOG',4000,NULL,20);
INSERT INTO TEST VALUES(15,'EGG',5000,100,20);

SELECT * FROM TEST;

--FROM 절의 부질의(SUB Query)에 대한 내용
--학생들의 학과별 평균신장보다 큰 신장의 학생들 정보를 검색
SELECT STU_DEPT, ROUND(AVG(STU_HEIGHT),2)
FROM STUDENT
GROUP BY STU_DEPT;


SELECT STU_NO, STU_NAME, A.STU_DEPT, STU_HEIGHT, AVG_HEIGHT
FROM STUDENT A, (SELECT STU_DEPT, ROUND(AVG(STU_HEIGHT),2) AS AVG_HEIGHT
                FROM STUDENT
                GROUP BY STU_DEPT) B
                WHERE A.STU_DEPT = B.STU_DEPT
                AND STU_HEIGHT > AVG_HEIGHT;
                
--집합연산자
--종류: UNION/ UNION ALL/ INTERSECT/ MINUS
--UNION =A+B-(A∩B) = (A∪B)-(A∩B)
--UNION ALL = A+B+(A∩B) = (A∪B)+(A∩B)
--INTERSECT = (A∩B)
--MINUS = A-B

--테이블 생성
CREATE TABLE A_STUDENT
AS
SELECT *
FROM STUDENT
WHERE STU_DEPT IN('기계','전기전자');

--테이블 생성
CREATE TABLE B_STUDENT
AS
SELECT *
FROM STUDENT
WHERE STU_DEPT IN('전기전자','컴퓨터정보');

SELECT * FROM A_STUDENT;
SELECT * FROM B_STUDENT;

--UNOION
--중복이 되지않고 그대로 합쳐져서 하나의 테이블로
SELECT * FROM A_STUDENT
UNION
SELECT * FROM B_STUDENT;

--UNOION ALL
--중복이 되고 그대로 합쳐져서 하나의 테이블로
SELECT * FROM A_STUDENT
UNION ALL
SELECT * FROM B_STUDENT;

--INTERSECT
--교집합 하나의 테이블로
SELECT * FROM A_STUDENT
INTERSECT
SELECT * FROM B_STUDENT;

--MINUS
--중복이 되고 그대로 합쳐져서 하나의 테이블로
SELECT * FROM A_STUDENT
MINUS
SELECT * FROM B_STUDENT;


--★3개의 테이블의 연결 관계가 A<->B , B<->C 인 경우 3개를 연결해야하는 경우 B를 기준으로★
--컴퓨터개론과목을 수강하는 학생들의 학번  이름 과목 이름을 검색하라
--STUDENT/ ENROL / SUBJECY 테이블을 이용함
SELECT * FROM SUBJECT;

SELECT STU_NO, STU_NAME, SUB_NAME
FROM STUDENT A NATURAL JOIN ENROL B NATURAL JOIN SUBJECT C;    --FROM절 UNION으로 할 경우
--/*WHERE A.STU_NO =  B.STU_NO AND B.SUB_NO = C.SUB_NO;*/ WHERE 절로 할경우



--=========================과제=================================
------------------------------A------------------------------------
--1)
SELECT *
FROM STUDENT NATURAL JOIN(ENROL) 
ORDER BY STU_NO DESC;

----2) 
SELECT B.STU_NO, B.STU_NAME, A.SUB_NAME , C.ENR_GRADE 
FROM SUBJECT A, STUDENT B, ENROL C
WHERE A.SUB_NO=C.SUB_NO AND B.STU_NO = C.STU_NO;

--3)
SELECT SUB_NAME, STU_NO, ENR_GRADE
FROM SUBJECT A NATURAL JOIN STUDENT B NATURAL JOIN ENROL C;

--4)
--과목이름은 SUBJECT에 있고  ENROL학번, 점수
SELECT A.SUB_NAME, B.STU_NO, B.ENR_GRADE 
FROM SUBJECT A JOIN ENROL B USING(SUB_NO);

--5)
SELECT STU_NAME , ENR_GRADE
FROM STUDENT A, ENROL B
WHERE A.STU_NO= B.STU_NO AND ENR_GRADE>=70;

--6)
SELECT STU_NAME , ENR_GRADE
FROM STUDENT A JOIN ENROL B USING(STU_NO)
WHERE ENR_GRADE>=60;

--7)
SELECT STU_NAME ,ENR_GRADE
FROM STUDENT NATURAL JOIN ENROL
WHERE ENR_GRADE>=70;


----
--공통 분모가 없어도 범위를 지정해서
SELECT * FROM EMP;
SELECT * FROM SALGRADE;
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
--
--8)
SELECT SUB_NAME, SUB_PROF 
FROM STUDENT A INNER JOIN ENROL B ON A.STU_NO = B.STU_NO 
                INNER JOIN SUBJECT C ON C.SUB_NO=B.SUB_NO 
WHERE STU_NAME ='김인중';


--9) 
SELECT SUB_NAME, SUB_PROF, STU_NAME
FROM SUBJECT NATURAL JOIN STUDENT 
            NATURAL JOIN  ENROL 
WHERE  STU_NAME='김인중';

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

--11)
SELECT STU_NAME, SUB_PROF
FROM SUBJECT JOIN ENROL USING(SUB_NO)
            JOIN STUDENT USING(STU_NO)
WHERE SUB_PROF='강종영';


--12)
SELECT SUB_NAME, STU_NO, STU_NAME
FROM SUBJECT NATURAL JOIN STUDENT 
            NATURAL JOIN  ENROL 
WHERE  SUB_NAME='컴퓨터개론';

SELECT SUB_NAME, STU_NO, STU_NAME
FROM SUBJECT JOIN ENROL USING(SUB_NO)
            JOIN STUDENT USING(STU_NO)
WHERE SUB_NAME='컴퓨터개론';

SELECT SUB_NAME, STU_NO, STU_NAME
FROM SUBJECT JOIN ENROL USING(SUB_NO)
            JOIN STUDENT USING(STU_NO)
WHERE SUB_NAME IN(SELECT SUB_NAME
                FROM SUBJECT
                WHERE SUB_NAME='컴퓨터개론');


------------------------------B------------------------------------
--13)
SELECT STU_NO, STU_NAME
FROM ENROL JOIN STUDENT USING(STU_NO)
WHERE SUB_NO IN(SELECT SUB_NO FROM SUBJECT WHERE SUB_NAME='시스템분석설계' OR SUB_NAME='소프트웨어공학')
ORDER BY STU_NO;

--14)
SELECT STU_NO, STU_NAME
FROM ENROL JOIN STUDENT USING(STU_NO)
WHERE SUB_NO IN(SELECT SUB_NO FROM SUBJECT WHERE SUB_GRADE=1 OR SUB_DEPT='컴퓨터정보');

--15) 
SELECT STU_NO, STU_NAME
FROM ENROL NATURAL JOIN STUDENT
WHERE SUB_NO IN(SELECT SUB_NO 
                FROM SUBJECT 
                WHERE SUB_NAME='기계공작법' OR SUB_NAME='기초전자실험');

--16)
SELECT STU_NO, STU_NAME
FROM ENROL JOIN STUDENT USING(STU_NO)
WHERE SUB_NO IN(SELECT SUB_NO FROM SUBJECT 
WHERE SUB_PROF='김태영' OR SUB_PROF='김유석');

--17)
SELECT STU_NAME, ENR_GRADE, SUB_NAME
FROM SUBJECT NATURAL JOIN ENROL NATURAL JOIN STUDENT;

--18)
SELECT SUB_NAME
FROM SUBJECT NATURAL JOIN ENROL NATURAL JOIN STUDENT
WHERE STU_NAME = '옥한빛';

--19)
SELECT SUB_NAME, STU_NAME
FROM SUBJECT NATURAL JOIN ENROL NATURAL JOIN STUDENT
WHERE STU_GENDER = 'F';

--20)
SELECT SUB_NAME
FROM SUBJECT NATURAL JOIN ENROL NATURAL JOIN STUDENT
WHERE STU_WEIGHT >= 60;

--21)
SELECT STU_NO, STU_NAME
FROM SUBJECT NATURAL JOIN ENROL NATURAL JOIN STUDENT
WHERE STU_DEPT = '컴퓨터정보';


--22)
SELECT COUNT(*)
FROM ENROL
WHERE ENR_GRADE >= 70;

------------------------------E------------------------------------
--23)
SELECT STU_NAME
FROM SUBJECT NATURAL JOIN STUDENT NATURAL JOIN ENROL
WHERE STU_GENDER='F' AND SUB_PROF='구봉규';

--24)
SELECT STU_NO, STU_NAME
FROM STUDENT NATURAL JOIN ENROL
WHERE ENR_GRADE>(SELECT AVG(ENR_GRADE)
                FROM ENROL NATURAL JOIN STUDENT
                WHERE STU_NAME='김종헌');

--25)
SELECT STU_NO, STU_NAME, ENR_GRADE
FROM STUDENT NATURAL JOIN ENROL
WHERE ENR_GRADE<(SELECT AVG(ENR_GRADE)
                FROM ENROL NATURAL JOIN STUDENT
                WHERE STU_NAME='김종헌');
                
--26)
SELECT STU_NO, STU_NAME, SUB_NAME, STU_GRADE
FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT
WHERE ENR_GRADE>(SELECT AVG(ENR_GRADE)
                FROM ENROL);    
                
--27)

SELECT STU_NO, ENR_GRADE
FROM STUDENT NATURAL JOIN ENROL
WHERE ENR_GRADE > ALL(SELECT AVG(ENR_GRADE)
                    FROM ENROL NATURAL JOIN STUDENT 
                    GROUP STU_DEPT);

--28)
SELECT STU_DEPT, ENR_GRADE
FROM STUDENT NATURAL JOIN ENROL
WHERE ENR_GRADE > (SELECT MAX(ENR_GRADE)
                        FROM ENROL NATURAL JOIN STUDENT
                        WHERE STU_DEPT='기계');
--29)
SELECT STU_NO, STU_NAME, AVG(ENR_GRADE)
FROM STUDENT NATURAL JOIN ENROL
WHERE STU_DEPT ='컴퓨터정보'
GROUP BY STU_NO, STU_NAME
ORDER BY 3 DESC;

--30)
SELECT STU_NO, STU_NAME, ENR_GRADE
FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT
WHERE SUB_NAME='시스템분석설계'
ORDER BY 3 DESC;

--31)
SELECT STU_NO, STU_NAME, COUNT(SUB_NAME) 
FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT
GROUP BY STU_NO, STU_NAME 
HAVING COUNT(SUB_NAME)>=2 
ORDER BY COUNT(SUB_NAME) DESC;

--32)
SELECT STU_NO, STU_NAME
FROM STUDENT NATURAL JOIN ENROL
WHERE STU_NO IN (SELECT STU_NO FROM ENROL GROUP BY STU_NO HAVING COUNT(*)=1)
ORDER BY STU_DEPT, STU_NO;


--33)
SELECT STU_NO, STU_NAME ,AVG(ENR_GRAGE)
FROM STUDENT NATURAL JOIN ENROL
GROUP BY STU_DEPT
HAVING MAX(AVG(ENR_GRADE)=(SELECT ;

--34)
SELECT STU_NO, STU_NAME, SUB_NAME
FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT 
WHERE SUB_NAME ='컴퓨터개론' OR SUB_NAME='시스템분석설계'
ORDER BY STU_NO;