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;
[오라클] 피봇 언피봇 매우 어렵다 외우기 (0) | 2023.04.17 |
---|---|
[오라클]4월 14일 수업예제 (16) | 2023.04.14 |
[오라클] groupby having important point (0) | 2023.04.14 |
[4월13일]중요한 수업내용 서브쿼리 차집합 교집합 합집합 (0) | 2023.04.13 |
[오라클]4월 13일 문제와 문제풀이 (0) | 2023.04.13 |
댓글 영역