[오라클]4월 14일 문제와 문제풀이

tochar todate 오늘날짜

--업무가 개발인 사람들은 1.5배, 경영인 사람들은 1.3배, 지원인 사람들은 1.1배, 분석인 사람은 그대로 두고 나머지는 0.95배 해준다.

 

1단계

--1) 각 학과별 학생 수를 검색하세요


--2) 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색하세요


--3) 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색하세요


--4) 과목명에 화학이 포함된 과목의 학점수 총합을 검색하세요



--5) 학과별 기말고사 평균을 성적순(성적 내림차순)으로 검색하세요 ---------------- 어려움


--6) 30번 부서의 업무별 연봉의 평균을 검색하세요(소수점 두자리까지 표시  --너무너무 어렵다...


--7) 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색하세요(학과, 학년, 평점)

 

 

 

 

2단계

 

 

 

--1) 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색하세요


--2) 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 이상인 정보를 검색하세요


--3) 기말고사 평균이 60점 이상인 학생의 정보를 검색하세요
----------이것도 어렵던거같고

--4) 강의 학점이 3학점 이상인 교수의 정보를 검색하세요
-------------------개어려움

--5) 기말고사 평균 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명을 검색하세요
-----------------너무어렵다

 

--5.5) '핵화학' 과목의 평균 점수보다 높은 평균 점수를 가진 과목들의 목록과 그 과목들을 가르치는 교수, 그리고 그 평균 점수를 반환합니다.

 

 

--6) 근무 중인 직원이 4명 이상인 부서를 검색하세요


--7) 업무별 평균 연봉이 3000 이상인 업무를 검색하세요


--8) 각 학과의 학년별 인원중 인원이 5명 이상인 학년을 검색하세요

 

 

 

해당 쿼리는 각 부서별 평균 급여를 계산하고, 그 중에서 가장 높은 평균 급여를 가진 부서의 정보를 출력하는 쿼리입니다.

 

--기말고사 성적 중 과목별로 가장 높은 점수 조회 그리고 그 학생의 이름을 구해라

 

--해당 쿼리는 각 부서에서 가장 적은 급여를 받는 직원의 정보를 조회하는 쿼리입니다. dno, dname 포함

 

--학생별 최대점수의 과목번호

 

 

--부서별 연봉 최적밧 부서명 포함 

 

--부서중 평균급여가 가장 높은 부서번호와 평균급여 (emp 테이블만 이용)

 

 

--임용년도가 동일한 교수의 수를 조회

--기말고사 성적 중 과목별로 가장 높은 점수 조회
---------------------------------------------------------중요
--기말고사 성적 중 과목별로 가장 높은 점수 조회 그리고 그 학생의 이름을 구해라
select course.cname, course.cno, a.maxscore, b.SNAME
from course course
         join(select max(RESULT) as maxscore, cno
              from SCORE
              group by cno) a
             on a.cno = course.cno

         join (select sc.result, st.SNAME, sc.CNO
               from student st,
                    SCORE sc
               where st.SNO = sc.SNO) b
              on b.RESULT = a.maxscore and course.CNO = b.CNO;

 

select sname, a.cname, result
from (select cname, max(result) maxresult
      from course,
           score
      where course.CNO = SCORE.cno
      group by cname) a,
     (select sname, result, cname
      from student,
           score,
           course
      where student.SNO = SCORE.SNO
        and course.CNO = SCORE.CNO) b
where a.cname = b.cname
  and a.maxresult = b.result;

 

select ORDERS, count(*)
from PROFESSOR
where (sysdate - HIREDATE) > 3650
group by ORDERS;

-- 그룹바이에 있는 칼럼만 해빙에서 슬 수 있다. 아니면 함수(카운트) 도 쓸수있다.

select sum(ST_NUM), CNAME
from COURSE
group by CNAME
having CNAME like '%화학%';


-- student랑 score를 결합해야 한다.


select avg(result), A.MAJOR
from score score
         join student A
              on score.SNO = A.SNO

group by A.MAJOR;


SELECT AVG(result) AS avg_result, A.MAJOR
FROM score
         JOIN student A ON score.SNO = A.SNO
GROUP BY A.MAJOR;


select avg(result)
from SCORE
         join student a on score.SNO = a.SNO
group by a.MAJOR
order by avg(result);
;

select avg(emp.sal), dept.DNAME
from EMP emp
         join dept dept
              on dept.DNO = emp.dno and dno = 30;
group by  dept.DNAME

;



select avg(sal) as A
from emp
group by dno
having dno = 30;



select avg(emp.sal), emp.job
from emp emp
         join dept d on d.DNO = emp.DNO
where emp.DNO = 30
group by emp.job;;
;

SELECT AVG(emp.sal) AS avg_salary, emp.JOB
FROM emp
         JOIN dept d ON d.DNO = emp.DNO
WHERE emp.DNO = 30
GROUP BY emp.JOB;



select avg(emp.sal), emp.job
from emp emp,
     dept dept
where dept.DNO = emp.DNO
  and emp.DNO = 30
group by emp.job;

select max(avr), SYEAR, MAJOR
from STUDENT
where MAJOR = '물리'
group by SYEAR, MAJOR;

SELECT MAX(avr) AS max_avr, SYEAR, MAJOR
FROM STUDENT
WHERE MAJOR = '물리'
GROUP BY SYEAR, MAJOR;



select avg(avr), MAJOR
from student
group by MAJOR
having major != '화학';


select avg(avr), MAJOR
from student
group by MAJOR
having major != '화학'
   and avg(avr) > 2;

select student.*
from STUDENT student,
     (select avg(result) as kaka, sno
      from score

      group by sno) A
where student.SNO = A.SNO
  and A.kaka >= 60;
;


select avg(result), sno
from score
group by sno;

select professor.*, course.ST_NUM
from professor professor,
     course course
where professor.PNO = course.PNO
  and ST_NUM >= 3
;


SELECT professor.PNO, professor.PNAME
FROM professor,
     course
WHERE professor.PNO = course.PNO
  AND course.ST_NUM >= 3;



select avg(score.result) as a, score.cno, course.CNAME, professor.PNO
from score score,
     course course,
     professor professor
where course.CNO = score.CNO
  and professor.PNO = course.PNO
group by score.cno, course.CNAME, professor.PNO
having avg(score.result) > (select avg(score.result)
                            from score score,
                                 course course
                            where score.CNO = course.CNO
                              and CNAME = '핵화학')
;


select professor.pname, professor.pno, A.CNAME, A.a
from PROFESSOR professor,
     (select avg(score.result) as a, score.cno, course.CNAME, professor.PNO
      from score score,
           course course,
           professor professor
      where course.CNO = score.CNO
        and professor.PNO = course.PNO
      group by score.cno, course.CNAME, professor.PNO
      having avg(score.result) > (select avg(score.result)
                                  from score score,
                                       course course
                                  where score.CNO = course.CNO
                                    and CNAME = '핵화학')) A
where A.PNO = professor.PNO
;



select avg(score.result)
from score score,
     course course
where score.CNO = course.CNO
  and CNAME = '핵화학'
;

select count(*), dno
from emp
group by dno
having count(*) >= 4;

select avg(sal) as A, job, dno
from emp
group by job, dno
having avg(sal) >= 3500;

select kkk.A, kkk.job, dept.dname
from dept,
     (select avg(sal) as A, job, dno
      from emp
      group by job, dno
      having avg(sal) >= 3500) kkk
where kkk.dno = dept.DNO;


select count(*), SYEAR,MAJOR
from STUDENT
 group by SYEAR,MAJOR
having  count(*)>=5;

 

 

 

select to_char(sysdate, 'YYYYMMDD')
from DUAL;
select to_date('19911121', 'YYYYMMDD')
from dual;

select count(*), MAJOR
from student
group by MAJOR;

select eno, ename, decode(job, '개발', sal * 99, '지원', sal * 0, sal * -1)
from emp;

select eno, ename, case job when '개발' then sal * 999 when '지원' then sal * 0 when '분석' then sal * -1 end
from emp;

select avg(avr) * 4.5 / 4, MAJOR
from student
where major in ('화학', '생물')
group by major;

select count(*), orders
from PROFESSOR
group by orders
having (sysdate - HIREDATE) / 12 > 10;



select count(*), orders
from PROFESSOR
where (sysdate - HIREDATE) / 365 > 27
group by orders;


select pno, pname, sysdate - HIREDATE
from PROFESSOR;


select *
from COURSE;

select sum(ST_NUM)
from COURSE
where cname like '%화학%';

select *
from course
where cname like '%화학%';

select avg(score.RESULT), major
from student student,
     score score
where score.SNO = student.SNO
group by major
order by avg(score.RESULT);

select avg(result), A.MAJOR
from score score
         join student A
              on score.SNO = A.SNO

group by A.MAJOR
order by avg(RESULT);

select avg(sal), dno
from EMP
group by dno
having dno = 30;
select avg(sal), job
from EMP
where dno = 30
group by job;
select *
from EMP;



select avg(emp.sal), dept.DNAME
from EMP emp
         join dept dept
              on dept.DNO = emp.dno and emp.dno = 30
group by dept.DNAME;

SELECT AVG(emp.sal) AS avg_salary, emp.JOB
FROM emp
         JOIN dept d ON d.DNO = emp.DNO
WHERE emp.DNO = 30
GROUP BY emp.JOB;


select max(avr), syear, MAJOR
from student
where MAJOR = '물리'
group by syear, MAJOR;


select avg(avr), major
from student
group by major;
select avg(avr), major
from student
group by major
having MAJOR != '화학'
   and avg(avr) >= 2.0;

select student.sname, student.sno, avg(score.result)
from student student,
     score score
where STUDENT.SNO = SCORE.SNO
group by student.sname, student.sno
having avg(score.RESULT) >= 60;

select *
from PROFESSOR;

select *
from COURSE;

select professor.pno, professor.pname
from PROFESSOR professor,
     course course
where professor.PNO = course.PNO
  and course.ST_NUM >= 3;



select c.cno, c.cname, avg(s.RESULT)
from course c,
     score s,
     professor p
where c.PNO = p.PNO
  and c.CNO = s.CNO
group by c.cno, c.cname
having avg(s.RESULT) > (select avg(s.RESULT)
                        from course c,
                             score s,
                             professor p
                        where c.PNO = p.PNO
                          and c.CNO = s.CNO
                        group by c.cno, c.cname
                        having c.CNAME = '핵화학');



select c.cno, c.cname, avg(s.RESULT), p.PNAME
from course c,
     score s,
     professor p
where c.PNO = p.PNO
  and c.CNO = s.CNO
group by c.cno, c.cname, p.pname
having avg(s.RESULT) > (select avg(s.RESULT)
                        from course c,
                             score s,
                             professor p
                        where c.PNO = p.PNO
                          and c.CNO = s.CNO
                        group by c.cno, c.cname
                        having c.CNAME = '핵화학');


select count(*), dno
from emp
group by dno
having count(*) >= 4;

select avg(sal), job
from emp
group by job
having avg(sal) >= 3500;

select count(*), syear, major
from student
group by syear, major
having count(*) >= 6;



select d.dno, d.dname, a.b
from dept d,
     (select avg(sal) b, dept.dno, dname
      from emp,
           dept
      where emp.DNO = DEPT.DNO
      group by dept.dno, dept.dname
      order by avg(sal)
              desc) a
where d.dno = a.dno;


select max(a.b)
from (select avg(sal) b, dept.dno, dname
      from emp,
           dept
      where emp.DNO = DEPT.DNO
      group by dept.dno, dept.dname
      order by avg(sal) desc) a;



select dept.dname, dept.dno, z.b
from dept dept,
     (select d.dno, d.dname, a.b
      from dept d,
           (select avg(sal) b, dept.dno, dname
            from emp,
                 dept
            where emp.DNO = DEPT.DNO
            group by dept.dno, dept.dname
            order by avg(sal)
                    desc) a
      where d.dno = a.dno) z
where z.b = (select max(a.b)
             from (select avg(sal) b, dept.dno, dname
                   from emp,
                        dept
                   where emp.DNO = DEPT.DNO
                   group by dept.dno, dept.dname
                   order by avg(sal) desc) a)
  and z.DNO = dept.DNO;


SELECT d.dno, d.dname, a.avg_sal
FROM dept d,
     (SELECT AVG(sal) AS avg_sal, dno
      FROM emp
      GROUP BY dno) a
WHERE d.dno = a.dno
  AND a.avg_sal = (SELECT MAX(avg_sal)
                   FROM (SELECT AVG(sal) AS avg_sal
                         FROM emp
                         GROUP BY dno));


select avg(sal), dept.dno, dname
from emp,
     dept
where emp.DNO = DEPT.DNO
group by dept.dno, dept.dname
order by avg(sal) desc;


select avg(sal), dept.dno, dname
from emp,
     dept
where emp.DNO = DEPT.DNO
group by dept.dno, dept.dname
order by avg(sal) desc fetch first 1 rows only;



select student.sname, course.cname, score.result
from score score,
     course course,
     student student,
     (select max(score.result) z, course.CNAME, course.cno
      from score score,
           course course
      where score.CNO = course.CNO
      group by course.CNAME, course.cno) a
where course.CNO = score.CNO
  and student.SNO = score.SNO
  and a.CNO = course.CNO
  and score.RESULT = a.z;
;
select max(score.result), course.CNAME, course.cno
from score score,
     course course
where score.CNO = course.CNO
group by course.CNAME, course.cno;



select emp.ename, emp.dno, emp.sal, dept.dname
from dept dept,
     emp emp
where emp.DNO = dept.DNO
  and ename in (select ename from emp where sal in (select min(sal) from emp group by dno));

select min(sal)
from emp
group by dno;


select dept.dname, dept.dno, emp.ename, emp.sal
from emp emp,
     dept dept
where dept.DNO = emp.DNO
  and emp.sal in (select min(sal) from emp group by dno);

select min(sal)
from emp
group by dno;

select student.sname, student.sno, score.result, course.cname
from score score,
     student student,
     COURSE course,
     (select max(score.result) b, student.sname, student.sno
      from score score,
           student student
      where student.SNO = score.SNO
      group by student.sname, student.sno) a
where student.SNO = score.SNO
  and course.CNO = score.CNO
  and a.SNO = student.SNO
  and a.SNAME = student.SNAME
  and a.b = score.RESULT
order by student.SNAME;

SELECT s.sname, s.sno, sc.result, c.cname
FROM student s
         JOIN score sc ON s.sno = sc.sno
         JOIN course c ON sc.cno = c.cno
         JOIN
     (SELECT sno, MAX(result) as max_result
      FROM score
      GROUP BY sno) sm ON s.sno = sm.sno AND sc.result = sm.max_result
order by s.sname;



select max(score.result), student.sname, student.sno
from score score,
     student student
where student.SNO = score.SNO
group by student.sname, student.sno;

select *
from student;

select b.a, b.dno, dept.dname
from dept dept,
     (select min(sal) a, dno from EMP group by dno) b
where b.dno = dept.DNO;

select min(sal) a, dno
from EMP
group by dno;


select b.a, b.dno
from dept dept,
     (select avg(sal) a, dno from EMP group by dno) b;



select a.dno, a.b
from emp emp,
     (select avg(sal) b, dno from EMP group by dno) a
where a.dno = emp.dno
  and a.b = (select max(avg(sal)) from emp group by dno);




SELECT a.dno, a.b
FROM emp,
     (SELECT avg(sal) b, dno
      FROM EMP
      GROUP BY dno
     ) a
WHERE a.dno = emp.dno
  AND a.b = (SELECT max(avg_sal)
             FROM (SELECT avg(sal) avg_sal
                   FROM emp
                   GROUP BY dno)
);



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

select max(avg(sal))
from emp
group by dno;


select count(*), to_char(HIREDATE,
                         'YYYY') from PROFESSOR group by to_char(HIREDATE,
    'YYYY');

select * from PROFESSOR;