[오라클]4.17 문제 및 풀이

--1) 각 학과별 학년별 학생 수를 ROLLUP함수로 검색하세요


--2) 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색하는 데 화학과 생물이 열로 만들어지도록 하세요.(PIVOT 사용)


--3) 학과별 학생이름을 ,로 구분하여 성적순으로(내림차순) 조회하세요.(LISTAGG 사용)


--4) 부서별 업무별 연봉의 평균을 검색하세요(부서와 업무 컬럼의 그룹화 여부도 같이 검색, GROUPING 사용)

 

 

 

 

 

--1) 각 과목의 과목번호, 과목명, 담당 교수의 교수번호, 교수명을 검색하라(NATURAL JOIN 사용)


--2) 화학과 학생의 기말고사 성적을 모두 검색하라(JOIN USING 사용)


--3) 화학 관련 과목을 강의하는 교수의 명단을 검색한다(NATURAL JOIN 사용)


--4) 화학과 1학년 학생의 기말고사 성적을 검색한다(NATURAL JOIN 사용)


--5) 일반화학 과목의 기말고사 점수를 검색한다(JOIN USING 사용)


--6) 화학과 1학년 학생이 수강하는 과목을 검색한다(NATURAL JOIN 사용)

 

 

 

 

 

 

 

 

--1) 다중 컬럼 IN절을 이용해서 기말고사 성적이 80점 이상인 과목번호, 학생번호, 기말고사 성적을 모두 조회하세요.
---------------------내가 잘못알고있엇음. 다시해보기!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

--2) 다중 컬럼 IN절을 이용해서 화학과나 물리학과면서 학년이 1, 2, 3학년인 학생의 정보를 모두 조회하세요.


--3) 다중 컬럼 IN절을 사용해서 부서가 10, 20, 30이면서 보너스가 1000이상인 사원의 사원번호, 사원이름, 부서번호, 부서이름, 업무, 급여, 보너스를 
--   조회하세요.(서브쿼리 사용)


--4) 다중 컬럼 IN절을 사용하여 기말고사 성적의 최고점이 100점인 과목의 과목번호, 과목이름, 학생번호, 학생이름, 기말고사 성적을 조회하세요.(서브쿼리 사용)

 

------------------------내가 좀 다른방식으로 함. 다시해보자

------------------------내가 좀 다른방식으로 함. 다시해보자

------------------------내가 좀 다른방식으로 함. 다시해보자

------------------------내가 좀 다른방식으로 함. 다시해보자

잘못 필터를 넣어서 손가락 사이로 빠져나감.

 

 

 

 

 

 

 

 

--1) WITH 절을 이용하여 정교수만 모여있는 가상테이블 하나와 일반과목(과목명에 일반이 포함되는)들이 모여있는 가상테이블 하나를 생성하여 
--   일반과목들을 강의하는 교수의 정보 조회하세요.(과목번호, 과목명, 교수번호, 교수이름)


--2) WITH 절을 이용하여 급여가 3000이상인 사원정보를 갖는 가상테이블 하나와 보너스가 500이상인 사원정보를 갖는 가상테이블 하나를 생성하여
--   두 테이블에 모두 속해있는 사원의 정보를 모두 조회하세요.


--3) WITH 절을 이용하여 평점이 3.3이상인 학생의 목록을 갖는 가상테이블 하나와 학생별 기말고사 평균점수를 갖는 가상테이블 하나를 생성하여
--   평점이 3.3이상인 학생의 기말고사 평균 점수를 조회하세요.


--4) WITH 절을 이용하여 부임일자가 25년이상된 교수정보를 갖는 가상테이블 하나와 과목번호, 과목명, 학생번호, 학생이름, 교수번호, 기말고사성적을
--   갖는 가상테이블 하나를 생성하여 기말고사 성적이 90이상인 과목번호, 과목명, 학생번호, 학생이름, 교수번호, 교수이름, 기말고사성적을 조회하세요.

 

 

 

 

 

 

select syear, major, count(*)
from student
group by rollup (major, syear);


select major, avg(avr)
from student
where major in ('화학', '생물')
group by major
;

select major, listagg(sname, ',') within group ( order by RESULT)
from STUDENT,
     SCORE
where STUDENT.SNO = SCORE.SNO
group by major
;


select major, listagg(sname, ', ') within group (order by avr)
from STUDENT
group by major;

---이건 되는데
select student.major, listagg(student.sname, ',') within group ( order by score.result)
from STUDENT student,
     SCORE score
where student.SNO = SCORE.SNO
group by student.major;

select dno, job, avg(sal), grouping(dno), grouping(job)
from EMP
group by dno, job;

select cno, cname, pno, pname
from PROFESSOR
         natural join COURSE;

select course.cno, course.cname, PROFESSOR.pno, PROFESSOR.pname
from PROFESSOR PROFESSOR,
     COURSE course
where PROFESSOR.PNO = course.PNO;

select result, SNAME
from student
         join SCORE using (sno);

select pname, cname
from PROFESSOR
         natural join COURSe
where CNAME like '%화학%'
;

select SNAME, MAJOR, SYEAR, RESULT
from STUDENT
         natural join SCORE
where SYEAR = 1
  and MAJOR = '화학';
;


select cname, result
from COURSE
         join score using (cno)
where CNAME = '일반화학'
;

select distinct CNAME, SYEAR, MAJOR
from STUDENT
         natural join COURSE
where SYEAR = 1
  and MAJOR = '화학'
;
--1) 다중 컬럼 IN절을 이용해서 기말고사 성적이 80점 이상인 과목번호, 학생번호, 기말고사 성적을 모두 조회하세요.

select score.cno, score.sno, score.result, student.sname
from SCORE,
     student
where STUDENT.SNO = SCORE.SNO
  and (STUDENT.sno, score.CNO) in (select st.sno, sc.cno
                                   from score sc,
                                        student st
                                   where SC.sno = ST.SNO
                                     and RESULT >= 80)
order by sno
;

select score.cno, score.sno, score.result, student.sname
from SCORE,
     student
where score.sno = student.sno
  and (student.sno, score.cno) in (select s.sno, sc.cno
                                   from score sc,
                                        student s
                                   where sc.sno = s.sno
                                     and sc.result >= 80)
order by sno;


select score.cno, score.sno, score.result, student.sname
from SCORE,
     student
where score.SNO = STUDENT.SNO
  and RESULT >= 80
order by sno
;

select *
from STUDENT
where MAJOR in ('화학', '물리')
  and SYEAR in (1, 2, 3);

select *
from STUDENT
where MAJOR in (select distinct MAJOR
                from STUDENT
                where MAJOR in ('화학', '물리'))
  and SYEAR in (1, 2, 3);

select distinct MAJOR
from STUDENT
where MAJOR = '화학'
   or MAJOR = '물리';
;

select eno, ename, emp.dno, dname, job, sal, comm
from EMP,
     DEPt
where emp.DNO = DEPT.DNO
  and (emp.dno, comm) in (select dno,
                                 comm
                          from EMP
                          where dno in (10, 20, 30)
                            and comm >= 1000);

select eno, ename, emp.dno, dname, job, sal, comm
from EMP,
     DEPt
where emp.DNO = DEPT.DNO
  and DEPT.dno in (10, 20, 30)
  and comm >= 1000;

select course.cno, cname, student.sno, sname, result
from course,
     student,
     SCORE
where course.CNO = SCORE.CNO
  and STUDENT.SNO = SCORE.SNO
  and result in (select distinct max(result)
                 from student,
                      score
                 where student.SNO = SCORE.SNO
                 group by SCORE.SNO
                 having MAx(RESULT) = 100);


select distinct max(result)
from student,
     score
where student.SNO = SCORE.SNO
group by SCORE.SNO
having MAx(RESULT) = 100;

--4) 다중 컬럼 IN절을 사용하여 기말고사 성적의 최고점이 100점인 과목의 과목번호, 과목이름, 학생번호, 학생이름, 기말고사 성적을 조회하세요.(서브쿼리 사용)

이거 답이
select course.cno, cname, student.sno, sname, result
from course,
     student,
     SCORE
where course.CNO = SCORE.CNO
  and STUDENT.SNO = SCORE.SNO
  and result in (select distinct max(result)
                 from student,
                      score
                 where student.SNO = SCORE.SNO
                 group by SCORE.SNO
                 having MAx(RESULT) = 100)
order by sno
;
이게 맞아?

SELECT c.CNO, c.CNAME, s.SNO, s.SNAME, sc.RESULT
FROM COURSE c,
     STUDENT s,
     SCORE sc
WHERE c.CNO = sc.CNO
  AND s.SNO = sc.SNO
  AND sc.RESULT = 100
  AND c.CNO IN (SELECT sc2.CNO
                FROM SCORE sc2
                WHERE sc2.RESULT = 100
                GROUP BY sc2.CNO
                HAVING MAX(sc2.RESULT) = 100)
order by sno
;

with kkk as (select* from PROFESSOR where ORDERS = '정교수'),
     zzz as (select* from COURSE where CNAME like '%일반%')

select cname, cno, pno, pname
from kkk
         natural join zzz;

with par as (select * from emp where sal >= 3000),
     kin as (select * from emp where comm >= 500)
select ENAME, ENO, sal, comm
from par
         natural join kin;

with kkk as (select * from STUDENT where AVR >= 2.3),
     zzz as (select SNAME, student.sno, avg(result)
             from STUDENT,
                  SCORE
             where STUDENT.SNO = SCORE.SNO
             group by SNAME, STUDENT.sno)
select *
from kkk
         natural join zzz
;

with kkk as (select * from PROFESSOR where months_between(sysdate, HIREDATE) > 25 * 12),
     zzz as (select cno, cname, sno, sname, pno, result
             from STUDENt
                      natural join COURSE
                      natural join SCORE)
select cno, cname, sno, sname, pno, pname, result
from kkk natural join zzz
where RESULT>=90;

select cno, cname, sno, sname, pno, pname, result
from course
natural join student
natural join SCORE
natural join PROFESSOR
where RESULT>=90 and  months_between(sysdate, HIREDATE) > 25 * 12