쿼리 함수

1.합계와 통계

--특정 필드에 대해서 통계를 낼 때

select manager_id, count(*) "Number of Reports" from employees
group by manager_id order by manager_id;

--부서별 월급의 합

select department_id, sum(salary*12) from employees
group by department_id
having sum(salary) >=100000;

--업무에 따른 월급통계

select job_id,count(*)"#", min(salary)"minimum",avg(salary) "Average", median(salary)"Median",
max(salary)"maximum",stddev(salary)"Std_Dev" from employees
group by job_id;

2.최댓값과 평균구하기

--19. 여학생이 수강중인 과목이름을 모두 검색

--join없이 사용

>select stu_name ,B.sub_no, sub_name from subject B,
(select sub_no, stu_name from enrol E,(select stu_gender,stu_no, stu_name from student where stu_gender = 'F') F where E.stu_no = F.stu_no) K
where B.sub_no = K.sub_no;

select stu_name, sub_name,stu_gender from student,subject
where stu_gender = 'F' ;

select stu_name, sub_name,stu_gender from student,subject
where student.stu_gender = 'F' or subject.sub_name = '과목';

>select stu_name ,B.sub_no, sub_name from subject B,
(select sub_no, stu_name from enrol E,(select stu_gender,stu_no, stu_name from student where stu_gender = 'F') F where E.stu_no = F.stu_no) K
where B.sub_no = K.sub_no;

--join으로 사용

SELECT stu_name,sub_name, stu_gender from student NATURAL JOIN subject
where stu_gender = 'F';

SELECT stu_name, sub_name ,stu_gender from student NATURAL JOIN enrol NATURAL JOIN subject
where stu_gender = 'F';

--20. 체중이 60이상인 학생이 수강하는 과목이름을 모두 검색/

select sub_name from subject where sub_no in
(select sub_no from enrol where stu_no in
(select stu_no from student where stu_weight >= 60));

--21. 컴퓨터정보과에 개설된 과목을 수강하는 학생들의 학번과 이름을 검색

select stu_no, stu_name from student where stu_no in
(select stu_no from enrol where sub_no in
(select sub_no from subject where sub_dept = '컴퓨터정보'));

--22. 점수가 70점 이상인 학생수를 검색

select *from enrol where enr_grade >=70;

select count(stu_no) from enrol where enr_grade >= 70; 
select stu_no from enrol where enr_grade >= 70;
select count(*) from student where stu_no in (select stu_no from enrol where enr_grade >= 70);


SELECT stu_no, sub_name ,enr_grade from student NATURAL JOIN enrol NATURAL JOIN subject
where enr_grade >= 70;

---이름까지 표시
SELECT stu_no,stu_name ,sub_name ,enr_grade from student NATURAL JOIN enrol NATURAL JOIN subject
where enr_grade >= 70;

--23. 남학생이면서 권민성 교수 과목을 수강하는 학생의 이름 검색

select stu_name from student
where (stu_gender = 'M') and (stu_no in 
(select stu_no from enrol where sub_no in (select sub_no from subject where sub_prof = '권민성')));

--24. 박희철 학생의 평균점수보다 높은 학생의 학번과 이름을 검색(group by로 평균(avg)내기)

select stu_no from student where stu_name='박희철'; --박희철 학번

SELECT avg(enr_grade) FROM enrol where stu_no=(select stu_no from student where stu_name='박희철');--박희철의 평균쿼리

SELECT stu_no,avg(enr_grade) FROM enrol group by stu_no;  --학번별 평균

select stu_no from (SELECT stu_no,avg(enr_grade) FROM enrol group by stu_no having  avg(enr_grade)> (SELECT avg(enr_grade) FROM enrol
where stu_no=(select stu_no from student where stu_name='박희철')));--박희철보다 높은 학생 (김인중)

--------
select stu_no, stu_name from student
where stu_no in (select stu_no from enrol group by stu_no having avg(enr_grade) > 
(select avg(enr_grade) from enrol where stu_no = (select stu_no from student where stu_name = '박희철')));


--25.김인중 학생의 평균점수보다 높은 학생의 학번과 이름을 검색


select stu_no from student where stu_name='김인중';--김인중 학번
SELECT avg(enr_grade) FROM enrol where stu_no=(select stu_no from student where stu_name='김인중');--김인중 평균


>select stu_no, stu_name from student
where stu_no in (select stu_no from enrol group by stu_no having avg(enr_grade)>=
(select avg(enr_grade) from enrol where stu_no = (select stu_no from student where stu_name = '김인중')));

--25.김인중 학생의 평균점수보다 높은 학생의 학번과 이름을 검색

SELECT STU_NO FROM STUDENT
WHERE STU_NAME = '김인중'; --김인중학번

SELECT ENR_GRADE FROM ENROL WHERE STU_NO =(SELECT STU_NO FROM STUDENT
WHERE STU_NAME = '김인중'); --김인중의 점수

SELECT STU_NO , AVG(ENR_GRADE) FROM ENROL 
GROUP BY STU_NO;  --학번별 평균 

>SELECT STU_NO , AVG(ENR_GRADE) FROM ENROL 
GROUP BY STU_NO
HAVING AVG(ENR_GRADE) >=(SELECT ENR_GRADE FROM ENROL WHERE STU_NO =(SELECT STU_NO FROM STUDENT
WHERE STU_NAME = '김인중')) ; --비교


>SELECT STU_NO FROM (SELECT STU_NO , AVG(ENR_GRADE) FROM ENROL 
GROUP BY STU_NO
HAVING AVG(ENR_GRADE) >=(SELECT ENR_GRADE FROM ENROL WHERE STU_NO =(SELECT STU_NO FROM STUDENT
WHERE STU_NAME = '김인중'))); --점수버리고 학번


>SELECT STU_NO, STU_NAME FROM STUDENT
WHERE STU_NO =(SELECT STU_NO FROM (SELECT STU_NO , AVG(ENR_GRADE) FROM ENROL 
GROUP BY STU_NO
HAVING AVG(ENR_GRADE) >=(SELECT ENR_GRADE FROM ENROL WHERE STU_NO =(SELECT STU_NO FROM STUDENT
WHERE STU_NAME = '김인중')))); --점수 버리고 학번으로 학생테이블에서 이름가져온다.

--26. 전체평균보다 높은 학생의 학번, 이름, 과목이름, 점수를 검색

SELECT E.STU_NO, S.STU_NAME, J.SUB_NAME,  ENR_GRADE
FROM SUBJECT J, ENROL E ,STUDENT S
WHERE J.SUB_NO=E.SUB_NO 
AND S.STU_NO=E.STU_NO 
and e.enr_grade >= (select avg(enr_grade) from enrol);

--과별로 점수가 제일 높은 사람 정보

SELECT STU_DEPT, AVG(ENR_GRADE) FROM STUDENT NATURAL JOIN ENROL GROUP BY STU_DEPT;
SELECT * FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT;


SELECT * FROM (SELECT STU_DEPT, MAX(ENR_GRADE) max_grade FROM STUDENT NATURAL JOIN ENROL GROUP BY STU_DEPT) A, 
(SELECT * FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT) B
WHERE A.STU_DEPT = B.STU_DEPT
and b.enr_grade = A.max_grade;