데이터베이스#5

2023년 2월 2일 오전 9:00

• count
  • count와 다른 컬럼을 조회 할 수 없다 group funtion 오류
• GROUNP BY
• ROLLUP
• having
• join
• 오늘의 키워드
  • Rollup, union
  •  

-- 101번학과교수중에서보직수당을 받는교수의수를 출력하여라.
select count(comm)
from professor
where deptno = 101 ;

select *
from professor
where deptno = 101 and comm IS NOT NULL;


-- 101번학과학생들의몸무게평균과합계를 출력하여라.
select avg(weight)as 평균, sum(weight)as "합계", count(weight)as "인원 수"
from student
where deptno=101;

-- 102번학과학생중에서최대키와최소키를출력하여라.
select max(height)as "최대 키", min(height)as "최소 키"
from student
where deptno = 102;

-- 교수테이블에서학과별로교수수와보직수당을받는교수수를출력하여라
select deptno, count(deptno), count(comm)
from professor
GROUP BY deptno
ORDER BY deptno;

-- 
select deptno, POSITION,aVG(SAL), MIN(SAL), MAX(SAL)
FROM PROFESSOR
GROUP BY DEPTNO, POSITION;

-- 전체학생을소속학과별로나누고, 같은학과학생은다시학년별로그룹핑하여, 학과와학년별인원수, 평균몸무게를 출력하여라, 단, 평균몸무게는소수점이하 첫번째자리에서반올림 한다
SELECT DEPTNO, GRADE, COUNT(*), ROUND(AVG(WEIGHT))
FROM STUDENT
GROUP by DEPTNO, GRADE
ORDER BY DEPTNO, GRADE;

SELECT * FROM STUDENT;

-- 소속학과별 로 교수급여합계 와 모든학과교수 들의 급여합계 를 출력하여라
SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO);

-- ROLLUP 연산자를 이용하여 학과및직급별교수수, 학과별교수수, 전체교수수를 출력하여라.(많이 생각해 볼 예제임)
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO, POSITION)
ORDER BY DEPTNO, POSITION;

SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(POSITION, DEPTNO)
ORDER BY DEPTNO, POSITION;

SELECT DEPTNO, POSITION, grouping(deptno) as "deptno", grouping(position) as "postion", COUNT(*)
FROM PROFESSOR
GROUP BY cube(POSITION, DEPTNO)
ORDER BY DEPTNO, POSITION;


-- 학생수가4명이상인 학년에대해서학년, 학생수, 평균키, 평균몸무게를춗력하여라. 단, 평균키와평균몸무게는소수점첪번째자리에서반올림하고, 춗력순서는평균키가높은순부터내림차순으로춗력하여라.
select grade, count(*), round(avg(height)), round(avg(weight))
from student
where profno IS NOT NULL
group by grade
having count(*) >= 4
order by 3 desc;

-- 실행 순서 보는 방법
select grade1, count(*), round(avg(height)), round(avg(weight))--5순위
from student --1순위
where profno1 IS NOT NULL --2순위
group by grade1--3순위
having count(studno) >= 4--4순위
order by round(avg(height1)) desc;--6순위
-- FROM > WHERE    > GROUP BY    > HAVING > SELECT    > ORDER BY
-- 마트 > 식품코너 > 카트OR바구니>        > 물건 선택 > 다시 카트에 넣을때 정리
select * from student;

SELECT DEPTNO, AVG(SAL)
FROM PROFESSOR
GROUP BY DEPTNO
HAVING DEPTNO >102;

SELECT DEPTNO, AVG(SAL)
FROM PROFESSOR
WHERE DEPTNO >102
GROUP BY DEPTNO;

--학생의 학번, 학과번호, 학과 이름
select * from student, department;

select s.studno, s.name, s.deptno, d.dname
from student s, department d
where s.deptno = d.deptno;

--'전인하'학생의학번, 이름, 학과이름그리고학과위치를춗력하여라.
select s.studno, s.name, s.deptno, d.dname
from student s, department d
where s.deptno = d.deptno
AND NAME = '전인하';

SELECT * 
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO;
-- 위아래 차이점 중복컬럼은 하나로 보여줌
-- 자연조인이라고함
SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT
NATURAL JOIN DEPARTMENT;



--'전인하'라는 이름을 가지는 학생의 학번, 이름, 담당교수번호, 담당교수 이름을 조회 하시오
SELECT S.STUDNO, S.NAME, P.PROFNO, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO
AND S.NAME = '전인하';

SELECT * FROM PROFESSOR;
SELECT * FROM STUDENT;