Intro to Database (6) : Oracle SQL developer 4 - 함수(2)

2019-07-05(금)

2019-07-08(월)

 

Case : 조건에 따른 처리 

실습(1) - HR계정으로

(https://docs.oracle.com/cd/E17781_01/appdev.112/e18147/tdddg_connecting.htm#TDDDG20000 참고)

각 조건에 따로 원하는 값을 리턴 시키기 위한 기능이다. 

--null value example
SELECT LAST_NAME, NVL(TO_CHAR(COMMISSION_PCT),'NOT APPLICABLE')
FROM EMPLOYEES WHERE LAST_NAME LIKE 'K%';

--SALARY AND TOTAL IMCOME 
SELECT LAST_NAME, NVL2(COMMISSION_PCT, 
SALARY + SALARY*COMMISSION_PCT, SALARY)
FROM EMPLOYEES;

--CASE 
SELECT CASE COUNTRY_ID
WHEN 'IT' THEN 'AAAAAA'
WHEN 'JP' THEN 'JAPAN'
WHEN 'US' THEN 'STATES'
WHEN 'CA' THEN 'CANADA'
ELSE 'NO CONT NAME' 
END
FROM LOCATIONS; 

--REPLACE WORDS
SELECT CASE COUNTRY_ID
WHEN 'IT' THEN 'AAAAAA'
WHEN 'JP' THEN 'JAPAN'
WHEN 'US' THEN 'STATES'
WHEN 'CA' THEN 'CANADA'
ELSE 'NO CONT NAME' 
END "COUNTRY NAME"
FROM LOCATIONS; 

--CASE WITH RANGE  
SELECT HIRE_DATE, CASE 
WHEN HIRE_DATE < TO_DATE('01-1월-90','DD-MON-YY') THEN 'A'
WHEN HIRE_DATE < TO_DATE('01-1월-95','DD-MON-YY') THEN 'B'
WHEN HIRE_DATE < TO_DATE('01-1월-00','DD-MON-YY') THEN 'C'
ELSE 'D'
END "HIRE_DATE LEVEL"
FROM EMPLOYEES; 

--SALARY BY JOB_ID USING DECODE 직업에 따라 임금 차등배수 지급 
SELECT JOB_ID, SALARY, DECODE(JOB_ID,
'AS_PRES', SALARY*1.1,
'AD_VP', SALARY*1.2,
'IT_PROG', SALARY*1.3,
SALARY) "SALARY INCERASE"
FROM EMPLOYEES;

 

실습(2) - 본인 SQL 계정으로

-- 동일한 결과를 내는 두 가지 코딩, 케이스 디코드
-- CASE
SELECT STU_GENDER, STU_WEIGHT, CASE STU_GENDER
WHEN 'M' THEN STU_WEIGHT + 5
WHEN 'F' THEN STU_WEIGHT - 5
ELSE STU_WEIGHT
END "WEIGHT CHANGE"
FROM  STUDENT;

--DECODE 
SELECT STU_GENDER, STU_WEIGHT, DECODE(STU_GENDER,
'M', STU_WEIGHT + 5,
'F', STU_WEIGHT - 5,
STU_WEIGHT) "WEIGHT CHANGE"
FROM STUDENT;

--101. 학과별 학년별 학생들의 평균신장을 학과별 학년별로 검색 (널값 제외, 자릿수 맞춤)
SELECT STU_DEPT, STU_GRADE, NVL(TO_CHAR(ROUND(AVG(STU_HEIGHT))), 'ERROR')  
FROM STUDENT
GROUP BY STU_DEPT, STU_GRADE
ORDER BY STU_DEPT; 

--101. 다른 방법(2)
select count(*), stu_dept 학과, stu_grade 학년, trunc(avg(stu_height),0) 평균신장 
from student 
where stu_height is not null 
group by stu_dept, stu_grade
order by stu_dept desc, stu_grade asc;

 

실습(3) 인사관리 시스템 만들고 'JOIN' 활용하기 - 본인 계정에서

테이블 만들기~ 

--인사관리 시스템 만들기 (본인계정)

--부서 테이블 만들기
CREATE TABLE DEPT(
DEPTNO CHAR(2),
DNAME VARCHAR2(14),
LOC VARCHAR(10),
CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO));

--피고용자 테이블 만들기, 이 테이블은 RECURSIVE 하다(자기가 자기 참고)
CREATE TABLE EMP(
EMPNO NUMBER(4)
CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4)
CONSTRAINT FK_MGR REFERENCES EMP(EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO CHAR(2)
CONSTRAINT FK_DEPTNO REFERENCES DEPT(DEPTNO));

-- SALGRADE TABLE 만들기
CREATE TABLE SALGRADE(
GRADE NUMBER(7,2)
CONSTRAINT PK_GRADE PRIMARY KEY,
LOSAL NUMBER(7,2),
HISAL NUMBER(7,2));

--데이터 INSERT에 순서가 있다
--INSERT DEPT DATA  
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');

--INSERT EMP DATA 
insert into emp values(7839, 'KING', 'PRESIDENT', NULL, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, NULL, 10);
insert into emp values(7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, NULL, 20);
insert into emp values(7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, NULL, 30);
insert into emp values(7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, NULL, 10);
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566, to_date('13-07-1987', 'dd-mm-yyyy'), 3000, NULL, 20);
insert into emp values(7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, NULL, 20);
insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into emp values(7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into emp values(7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, NULL, 30);
insert into emp values(7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, NULL, 10);
insert into emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, NULL, 20);
insert into emp values(7876, 'ADAMS', 'CLERK', 7788, to_date('13-07-1987', 'dd-mm-yyyy'), 1100, NULL, 20);  

--INSERT SALGRADE DATA 
insert into salgrade values(1, 700, 1200);
insert into salgrade values(2, 1201, 1400);
insert into salgrade values(3, 1401, 2000);
insert into salgrade values(4, 2001, 3000);
insert into salgrade values(5, 3001, 9999);

DROP TABLE EMP; /*필요시 테이블 삭제하고 다시 작업가능*/
DROP TABLE DEPT;
DROP TABLE SALGRADE;

 

JOIN 조인 

조인을 이용해 여러 테이블에서 동시에 데이터 검색을 할 수 있다.  방금 전에 만든 테이블을 이용해 연습해보자. 

 

1. JOIN...ON

SELECT 무언가 FROM 테이블1 AS T1 

JOIN 테이블2 AS T2

ON T1.ID = T2.ID

WHERE T2.NAME LIKE '';

 

2.  OUTER JOIN 

: 한쪽 테이블에만 있어도 포함시킴

(1) LEFT OUTER JOIN  (2) RIGHT OUTER JOIN 이 존재, 왼쪽 테이블을 기준으로 하느냐 오른쪽 테이블을 기준으로 해서 조인하느냐의 차이가 있어 선택되는 데이터에 차이가 있을 수 있다. 

 

3. CROSS JOIN(FULL JOIN)

두 테이블의 데이터를 합쳐서 조인한다

따로 조인 구문이 필요 없음

SELECT * FROM TABLE1 CROSSJOIN TABLE2;

SELECT * FROM TABLE1, TABLE2;

 

4. Equi Join (Inner Join)

: 따로 조인 구문을 사용하여 두 테이블을 조인할 수 있음

 

 

 

 

 

실습 (4)

--Login as your account and search the following fields from emp and dept tables using Equi join, Natural join, Join~using, and Join~on 
--여러가지 조인방법을 이용하여 emp와 dept를 조인

--사원번호, 사원이름, 부서이름 검색 
SELECT EMPNO,ENAME, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, DNAME FROM EMP JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;

SELECT EMPNO, ENAME, DNAME FROM EMP JOIN DEPT
USING (DEPTNO);
SELECT EMPNO,ENAME,DNAME FROM EMP NATURAL JOIN DEPT;
--사원이름, 지역 검색 
/*1*/SELECT ENAME, LOC FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT ENAME, LOC FROM EMP JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;

/*2*/SELECT ENAME, LOC FROM EMP JOIN DEPT
USING (DEPTNO);
SELECT ENAME, LOC FROM EMP NATURAL JOIN DEPT;

--사원번호, 사원이름, 부서이름, 급여 (급여 1000이상 2000이하, 사원번호순) 검색
/*1*/SELECT EMPNO, ENAME, DNAME, SAL FROM EMP JOIN DEPT
USING (DEPTNO) WHERE SAL>=1000 AND SAL<=2000;
SELECT EMPNO, ENAME, DNAME, SAL FROM EMP NATURAL JOIN DEPT
WHERE SAL>=1000 AND SAL<=2000;

/*2*/SELECT EMPNO, ENAME, DNAME, SAL FROM DEPT NATURAL JOIN EMP
WHERE SAL >= 1000 AND SAL < 2000 ORDER BY EMPNO;

--사원번호, 사원이름, 부서이름, 지역 (accounting부서 or 시카고지역) 검색
/*1*/SELECT EMPNO, ENAME, DNAME, LOC FROM DEPT, EMP
WHERE DNAME = 'ACCOUNTING' OR LOC = 'CHICAGO';
/*2오류뜬다*/SELECT EMPNO, ENAME, DNAME, LOC FROM DEPT, EMP
WHERE (DEPT.DEPTNO = EMP.DEPTNO) AND  
(NAME = 'ACCOUNTING' OR LOC = 'CHICAGO');

--Using non-equi join 
--사원번호, 사원이름, 급여, 급여등급 (급여등급별 사원번호순) 검색
SELECT EMPNO, ENAME, SAL, GRADE FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
--Using self join (any join methods of your choice)
--사원번호, 사원이름, 상급자사원번호, 상급자이름 검색
SELECT A.EMPNO "사원", A.ENAME "사원이름", B.EMPNO "매니저", B.ENAME "매니저이름" 
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNO;

 

실습(5) : #OF RECORDS(DEPT, EMP) COUNT

테이블의 레코드 개수를 세는 실습을 조인을 활용해 수행해보고 결과가 잘 출력되는지 확인해볼

자

--Exercise #6 : #OF RECORDS(DEPT, EMP) COUNT
--1. CROSS JOIN = 56
SELECT COUNT(*) FROM DEPT CROSS JOIN EMP;

--2. EQUI JOIN = 14
SELECT COUNT(*) 
FROM DEPT,EMP 
WHERE DEPT.DEPTNO = EMP.DEPTNO

--3. LEFT OUTER JOIN = 15
SELECT COUNT(*)
FROM DEPT LEFT OUTER JOIN EMP
ON DEPT.DEPTNO = EMP.DEPTNO

--4. RIGHT OUTER JOIN = 14
SELECT COUNT(*)
FROM DEPT RIGHT OUTER JOIN EMP
ON DEPT.DEPTNO = EMP.DEPTNO

--5.SELF JOIN = 13
SELECT COUNT(*)  FROM EMP E1, EMP E2 WHERE E1.MGR=E2.EMPNO;

--6.NOT EQUI JOIN = 14 
SELECT COUNT(*) FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

 

실습(6) : 부질 의어(SUBQUERRY) 연습

부질 의어를 활용하면 쿼리문을 더 효율적으로 짤 수 있다. 실습을 통해 느껴보자. 

--SUBQUERY 부질의어 연습
SELECT STU_WEIGHT FROM STUDENT WHERE STU_NAME = '박희철';
SELECT * FROM STUDENT WHERE STU_WEIGHT = /*박희철과 몸무게가 같은 사람까지*/
(SELECT STU_WEIGHT FROM STUDENT WHERE STU_NAME = '박희철');
-- 
SELECT SAL, COMM FROM EMP WHERE ENPNO = 7499; /*오류*/

SELECT * FROM EMP WHERE (SAL, NVL(COMM, -1)) = 
(SELECT SAL, NVL(COMM, -1) FROM EMP WHERE ENPNO = 7499;); /*복수열 서브쿼리, 오류*/

SELECT STU_DEPT, ROUND(AVG(STU_WEIGHT)) FROM STUDENT
GROUP BY STU_DEPT; /*과별로 평균무게*/

SELECT * FROM STUDENT S,
(SELECT STU_DEPT, AVG(STU_WEIGHT) FROM STUDENT GROUP BY STU_DEPT) S1  
WHERE S.STU_DEPT = S1.STU_DEPT; /*학과가 똑같이 매치되는 것들만*/

/*STUDENT WITH HIGHER THAN DEPT AVERAGE*/
SELECT * FROM STUDENT S,
(SELECT STU_DEPT, AVG(STU_WEIGHT) "AVG_WEIGHT" FROM STUDENT GROUP BY STU_DEPT) S1  
WHERE S.STU_DEPT = S1.STU_DEPT AND S.STU_WEIGHT > S1.AVG_WEIGHT; 

/*N TABLE을 만들어서 조인하는게 더 편하다(조인할때 뒤에다 다 쓰는 것 보다는)*/
CREATE TABLE N_TABLE1 AS 
(SELECT STU_DEPT, AVG(STU_WEIGHT) "AVERAGE" FROM STUDENT GROUP BY STU_DEPT);  
SELECT * FROM N_TABLE1;
SELECT * FROM STUDENT NATURAL JOIN N_TABLE1;
SELECT * FROM STUDENT JOIN N_TABLE1 USING(STU_DEPT)
WHERE STU_WEIGHT > AVERAGE;

실습(7) : 연습문제, 조인과 서브 쿼리 활용 

--1. 학생테이블과 수강테이블을 natural join
SELECT * FROM STUDENT NATURAL JOIN ENROL;
--2. 학번, 이름, 과목이름, 점수를 검색 (equi join)
/*1번 방법*/
SELECT STUDENT.STU_NO, STUDENT.STU_NAME, SUBJECT.SUB_NAME, ENROL.ENR_GRADE 
FROM STUDENT, SUBJECT, ENROL 
WHERE STUDENT.STU_NO=ENROL.STU_NO /*조건을 각각, 테이블이 3개 이므로*/
AND SUBJECT.SUB_NO=ENROL.SUB_NO /*조건을 각각, 테이블이 3개 이므로*/
/*2번 방법*/ 
SELECT S.STU_NO, STU_NAME, SUB_NAME, ENR_GRADE 
FROM STUDENT S INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SB ON SB.SUB_NO = E.SUB_NO;
/*3번 방법*/
SELECT STUDENT.STU_NO, STU_NAME, SUB_NAME, ENR_GRADE 
FROM STUDENT INNER JOIN ENROL ON ENROL.STU_NO = STUDENT.STU_NO
INNER JOIN SUBJECT ON ENROL.SUB_NO = SUBJECT.SUB_NO;
--3. 과목이름과 학번, 점수를 검색 (natural join)
SELECT SUB_NAME, STU_NO, ENR_GRADE 
FROM (STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT);
--4. 과목이름과 학번, 점수를 검색 (join ~ using)
SELECT SUB_NAME, STU_NO, ENR_GRADE 
FROM SUBJECT JOIN ENROL 
USING(SUB_NO);
--5. 점수가 70점이상인 학생이름을 검색 (equi join)
/*방법1*/SELECT STU_NAME FROM STUDENT INNER JOIN ENROL 
ON STUDENT.STU_NO=ENROL.STU_NO /*기준*/ WHERE ENR_GRADE >=70; /*조건*/
/*방법2*/SELECT STU_NAME,ENR_GRADE FROM STUDENT 
JOIN ENROL USING(STU_NO) WHERE ENROL.ENR_GRADE >= 70;
--6. 점수가 60점이상인 학생이름을 검색 (join ~ using)
SELECT STU_NAME,ENR_GRADE FROM STUDENT 
JOIN ENROL USING(STU_NO) WHERE ENROL.ENR_GRADE >= 60;
--7. 점수가 70점이하인 학생이름을 검색 (natural join)
SELECT STU_NAME, ENR_GRADE FROM STUDENT NATURAL JOIN ENROL /*내츄럴 조인시에는 ON필요없음*/
WHERE ENR_GRADE<=70;

--8. !!김인중 학생이 수강하는 과목이름과 강의교수를 검색 (equi join)
SELECT SUB_NAME, SUB_PROF
FROM STUDENT S INNER JOIN ENROL E ON S.STU_NO = E.STU_NO INNER JOIN SUBJECT SB ON SB.SUB_NO=E.SUB_NO 
WHERE STU_NAME ='김인중';

--9.미완료 !!!!김인중 학생이 수강하는 과목이름과 강의교수를 검색 (natural join)
SELECT SUB_NAME, SUB_PROF 
FROM STUDENT S NATURAL JOIN ENROL E ON S.STU_NO = E.STU_NO 
NATURAL JOIN SUBJECT SB ON SB.SUB_NO=E.SUB_NO 
WHERE S.STU_NAME ='김인중';
--10. 김인중 학생이 수강하는 과목이름과 강의교수를 검색 (join ~ using)
SELECT SUB_NAME, SUB_PROF
FROM STUDENT S JOIN ENROL E ON S.STU_NO = E.STU_NO JOIN SUBJECT SB ON SB.SUB_NO=E.SUB_NO 
WHERE STU_NAME ='김인중';

--11. 강종영 교수가 강의하는 과목을 수강하는 학생의 이름을 검색
SELECT STU_NAME
FROM STUDENT INNER JOIN ENROL ON ENROL.STU_NO = STUDENT.STU_NO
INNER JOIN SUBJECT ON ENROL.SUB_NO = SUBJECT.SUB_NO
WHERE SUB_PROF = '강종영';
--12!!.미완료) 컴퓨터개론을 수강하는 학생들의 학번과 이름을 검색
SELECT STU_NO, STU_NAME
FROM STUDENT INNER JOIN ENROL ON ENROL.STU_NO = STUDENT.STU_NO
INNER JOIN SUBJECT ON ENROL.SUB_NO = SUBJECT.SUB_NO
WHERE SUBJECT.SUB_NAME = '컴퓨터개론';
--13. 시스템분석설계, 소프트웨어공학을 수강하는 학생들의 학번, 이름, 학번순으로 검색
--14. 과목이 1학년 과목이거나 컴퓨터정보과에 개설된 과목일 경우 그 과목들을 수강하는 학생들의 학번이나 이름을 검색
--15. 기계공작법, 기초전자실험을 수강하는 학생들의 학번, 이름을 학번순으로 검색 (natural join)
--16. 김태영교수, 김유석교수가 강의하는 과목을 수강하는 학생의 학번을 검색

--17.!!  학생의 이름과 점수를 검색
SELECT STU_NAME, ENR_GRADE
FROM STUDENT RIGHT JOIN ENROL ON ENROL.STU_NO = STUDENT.STU_NO;
--18. 이름이 옥한빛인 학생이 수강하는 과목이름 검색
SELECT SUB_NAME 
FROM SUBJECT SB INNER JOIN ENROL E ON E.SUB_NO = SB.SUB_NO
INNER JOIN STUDENT S ON E.STU_NO = S.STU_NO
WHERE S.STU_NAME = '옥한빛';
--19. 여학생이 수강중인 과목이름을 모두 검색
SELECT SUB_NAME 
FROM SUBJECT SB INNER JOIN ENROL E ON E.SUB_NO = SB.SUB_NO
INNER JOIN STUDENT S ON E.STU_NO = S.STU_NO
WHERE S.STU_GENDER = 'F';
--20. 체중이 60이상인 학생이 수강하는 과목이름을 모두 검색
SELECT SUB_NAME 
FROM SUBJECT SB INNER JOIN ENROL E ON E.SUB_NO = SB.SUB_NO
INNER JOIN STUDENT S ON E.STU_NO = S.STU_NO
WHERE S.STU_WEIGHT >= 60;
--21. 컴퓨터정보과에 개설된 과목을 수강하는 학생들의 학번과 이름을 검색

--22. 점수가 70점 이상인 학생수를 검색
SELECT COUNT(STU_NO) 
FROM ENROL E 
WHERE E.ENR_GRADE >= 70;
--23. 미완료 여학생이면서 구봉규교수 과목을 수강하는 학생의 이름 검색
SELECT STU_NAME 
FROM STUDENT T INNER JOIN ENROL E ON E.STU_NO = T.STU_NO
INNER JOIN SUBJECT SB ON SB.SUB_NO = E.SUB_NO
WHERE t.stu_gender = 'F' AND SB.SUB_PROF = '구봉규';
--24. 김종헌학생의 평균점수보다 높은 학생의 학번과 이름을 검색
--25. 김인중학생의 평균점수보다 높은 학생의 학번과 이름을 검색
--26. 전체평균보다 높은 학생의 학번, 이름, 과목이름, 점수를 검색
-- 27. 점수가 각 학과 평균 점수보다 높은 학생의 학번 검색
select s.stu_no from student s,
(select stu_dept, avg(enr_grade) avg_enr_grade from student natural join enrol group by stu_dept) d,enrol e
where s.stu_dept=d.stu_dept and s.stu_no=e.stu_no
and e.enr_grade>d.avg_enr_grade;

--28. 기계과 최고점수보다 최고점수가 높은 학과의 학과명과 점수를 검색
SELECT STU_DEPT, MAX(ENR_GRADE) FROM STUDENT 
NATURAL JOIN ENROL WHERE ENR_GRADE > (SELECT MAX(ENR_GRADE) FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT WHERE STU_DEPT = '기계')
GROUP BY STU_DEPT ;

SELECT STU_DEPT, MAX(ENR_GRADE) FROM STUDENT 
NATURAL JOIN SUBJECT NATURAL JOIN ENROL GROUP BY STU_DEPT 
HAVING MAX(ENR_GRADE)> (SELECT MAX(ENR_GRADE) FROM ENROL 
NATURAL JOIN STUDENT NATURAL JOIN SUBJECT WHERE STU_DEPT ='기계'); /*group by + having 추가*/
/*having의 장점 : 비교 연산 옆에 함수를 넣을 수 있는점이 where와 다르다*/

--29. 컴퓨터정보과 학생들의 평균점수를 구해 학생들의 학번, 이름, 평균점수를 성적순으로 검색
SELECT STU_NO, STU_NAME, AVG(ENR_GRADE) A FROM STUDENT 
NATURAL JOIN SUBJECT NATURAL JOIN ENROL
WHERE STU_DEPT = '컴퓨터정보' GROUP BY STU_NAME, STU_NO ORDER BY A DESC;

--30. 시스템분석설계 과목을 수강하는 학생들의 학번, 이름, 점수를 성적순으로 검색 
SELECT STU_NO, STU_NAME, ENR_GRADE FROM STUDENT 
NATURAL JOIN ENROL NATURAL JOIN SUBJECT 
WHERE SUB_NAME = '시스템분석설계' ORDER BY ENR_GRADE DESC;

--31. 2과목이상 수강한 학생들의 학번, 이름, 수강과목수를 수강과목이 많은 순으로 검색
--32. 1과목을 수강한 학생들의 학번, 이름을 학과별 학번수으로 검색
--33. 컴퓨터정보과 학생들중 평균점수가 최고인 학생의 학번, 이름, 평균점수를 검색
--34. 컴퓨터개론과 시스템분석설계 과목을 수강하는 학생의 학번, 이름을 학번순으로 검색