SQL JOIN/COUNT/GROUP BY

join

--인사관리 데이터 베이스 /사원 정보 조회

--사원번호,사원이름,부서이름 검색

>select empno,ename,dname from emp,dept
where emp.deptno = dept.deptno;

--사원이름,지역검색

select ename, loc from emp natural join dept;

--사원번호,사원이름,부서이름,급여(급여1000이상,2000이하,사원번호순)검색

>select empno,ename,dname,sal from emp join dept  on emp.deptno = dept.deptno
where (sal>= 1000) and (sal<=2000) order by empno;

--사원번호, 사원이름, 부서이름, 지역 (accounting부서 or 시카고지역) 검색

Using non-equi join
select empno,ename,dname,loc from emp natural join dept
where dname = 'ACCOUNTING' OR loc = 'CHICAGO';

--using non-equi join
--사원번호, 사원이름, 급여, 급여등급 (급여등급별 사원번호순) 검색

select empno,ename,sal,grade from emp,salgrade
where sal between losal and hisal order by grade,empno;
--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;

--8. 김인중 학생이 수강하는 과목이름과 강의교수를 검색

SELECT SUB_NAME, SUB_PROF FROM STUDENT A, SUBJECT B, ENROL C
WHERE (A.STU_NO = C.STU_NO AND B.SUB_NO = C.SUB_NO) AND STU_NAME = '김인중';

--9. 김인중 학생이 수강하는 과목이름과 강의교수를 검색 (natural join)

select stu_name, sub_name, sub_prof from student natural join enrol natural join subject where stu_name = '김인중';

--10. 김인중 학생이 수강하는 과목이름과 강의교수를 검색 (join ~ using)

select sub_name,sub_prof, stu_name from student T 
inner join enrol E on T.stu_no = E.stu_no inner join subject B on E.sub_no = B.sub_no
where stu_name = '김인중';

--11. 강종영 교수가 강의하는 과목을 수강하는 학생의 이름을 검색

select sub_name, stu_name from subject b natural join student s natural join enrol e
where sub_prof = '강종영';

--12. 컴퓨터개론을 수강하는 학생들의 학번과 이름을 검색

SELECT E.STU_NO, S.STU_NAME
FROM STUDENT S, ENROL E, SUBJECT J
WHERE S. STU_NO = E.STU_NO
AND E.SUB_NO = J.SUB_NO
AND J.SUB_NAME = '컴퓨터개론';

--13. 시스템분석설계, 소프트웨어공학을 수강하는 학생들의 학번, 이름, 학번순으로 검색

select student.stu_no,student.stu_name,subject.sub_name from enrol,student,subject
where (subject.sub_no=enrol.sub_no) and (enrol.stu_no=student.stu_no)
and (sub_name in( '시스템분석설계', '소프트웨어공학')) order by student.stu_no;

---

--14. 과목이 1학년 과목이거나 컴퓨터정보과에 개설된 과목일 경우 그 과목들을 수강하는 학생들의 학번이나 이름을 검색

select distinct(stu.stu_no), stu_name 
from enrol e join student stu on e.stu_no = stu.stu_no 
join subject sub on e.sub_no = sub.sub_no
where sub_grade = 1 or sub_dept = '컴퓨터정보'
order by stu_no desc;

--14.과목이 1학년 과목이거나 컴퓨터정보과에 개설된 과목일 경우 그 과목들을 수강하는 학생들의 학번이나 이름을 검색

select distinct stu.stu_no, stu.stu_name
from student stu, subject sub, enrol e
where (stu.stu_no=e.stu_no) and (sub.sub_no=e.sub_no) and ((sub_grade = 1) or (sub_dept = '컴퓨터정보'))
order by stu.stu_name;

--15. 기계공작법, 기초전자실험을 수강하는 학생들의 학번, 이름을 학번순으로 검색 (natural join)

select stu_no, stu_name, sub_name from student natural join enrol natural join subject
where sub_name = '기계공작법' or  sub_name = '기초전자실험';

--16. 김태영교수, 김유석교수가 강의하는 과목을 수강하는 학생의 학번을 검색

select c.stu_no "학번"
from enrol a join subject b on a.sub_no=b.sub_no
join student c on a.stu_no=c.stu_no
where b.sub_prof = '김태영' or b.sub_prof = '김유석';

--17. 학생의 이름과 점수를 검색

SELECT STU_NAME "이름" ,ENR_GRADE "점수" FROM STUDENT
NATURAL JOIN ENROL;

--18. 이름이 옥한빛인 학생이 수강하는 과목이름 검색

SELECT stu_name, sub_name from student NATURAL JOIN enrol NATURAL JOIN subject
where stu_name = '옥한빛';

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

--join없이 사용(enrol)

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 ,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_no from student where stu_gender ='F';
select sub_no from enrol where stu_no in 
(select stu_no from student where stu_gender ='F'); 
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_gender ='F'));

--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';

기본 연습 문제(count,group by)

--91.학과의 수를 검색

select stu_dept, count(*) "Number of Reports" from student
group by stu_dept order by stu_dept;

--91. 학과의 수를 검색

SELECT COUNT(DISTINCT STU_DEPT) "학과의 수" FROM STUDENT;
SELECT COUNT(*) FROM (SELECT STU_DEPT, COUNT(*) FROM STUDENT GROUP BY STU_DEPT);

--92. 학생들의 성씨의 수

SELECT COUNT(DISTINCT SUBSTR(STU_NAME,1,1)) "성씨의 수" FROM STUDENT;

--93. 학생테이블의 레코드의 수를 검색

select count(*) from student;

--94. entrol테이블의 레코드의 수를 검색

select count(*)from enrol;

--95. 학과별 학생들의 인원수를 검색

SELECT STU_DEPT, COUNT(*) FROM STUDENT GROUP BY STU_DEPT;

--96. 학과별 학생들의 인원수를 인원수가 많은 순으로 검색

SELECT STU_DEPT, COUNT(*) FROM STUDENT GROUP BY STU_DEPT ORDER BY COUNT(*) DESC;

--97. 학년별 학생들의 인원수를 검색

SELECT STU_no, COUNT(*) FROM STUDENT GROUP BY STU_no;
select stu_grade, count(stu_grade) "인원수" from student
group by stu_grade;

--98. 학년별 학생들의 인원수가 많은 순으로 검색

SELECT STU_class, COUNT(*) FROM STUDENT GROUP BY STU_class ORDER BY COUNT(*) DESC;

--99. 학과별 학생들의 평균신장을 검색

SELECT STU_DEPT, ROUND(AVG(stu_height),2) FROM STUDENT  GROUP BY stu_dept ;

--100. 학과별 학생들의 체중의 표준편차를 검색 (STDDEV)

select stu_dept, round(stddev(stu_weight),2) from student
group by stu_dept;

--102. 학과별 학년별 학생들의 평균체중

select stu_dept, stu_grade, avg(stu_weight) from student group by stu_dept, stu_grade order by stu_dept;

--103. 학과별 학년별 학생들의 학번의 max와 min값을 검색

SELECT stu_dept, stu_grade, MAX(stu_no) MAX, MIN(stu_no) MIN
FROM STUDENT
GROUP BY stu_dept,stu_grade
ORDER BY stu_dept, stu_grade;

--104. 학과별 학생들의 인원수를 인원수가 많은 순으로 검색

select stu_dept, count(*) from student group by stu_dept order by COUNT(*) desc;

--105. 학과별 학생들의 평균신장을 평균신장 순으로 검색

SELECT STU_DEPT, AVG(STU_WEIGHT) FROM STUDENT GROUP BY STU_DEPT ORDER BY AVG(STU_WEIGHT);

--106. 학과별 학년별 학생들의 평균체중을 평균체중이 많은 순으로 검색

SELECT STU_DEPT, STU_GRADE, ROUND(AVG(STU_WEIGHT),1) FROM STUDENT GROUP BY STU_DEPT, STU_GRADE ORDER BY AVG(STU_WEIGHT) DESC;

--107. 학과별 학생들의 평균신장을 평균신장이 높은 순으로 검색

select STU_DEPT, round(avg(STU_HEIGHT)) from STUDENT
group by STU_DEPT order by round(avg(STU_HEIGHT)) desc;

--108. 학과별 학생들의 평균신장이 170이상인 학과를 평균신장이 낮은 순으로 검색 (having)

SELECT stu_dept, COUNT(*), ROUND(AVG(stu_height)) FROM student HAVING AVG(stu_height) >= 170 group by stu_dept ORDER BY AVG(stu_height);