SQL 데이터베이스 04

데이터베이스 & SQL

-- 19.02.01
-- Additional SELECT 02
-- 그룹 함수 (SUM, AVG, MIN, MAX, COUNT)
-- SUM : 합계 (중복 [DISTINCT] 제거 후, 총합)
-- AVG : 평균 (중복 [DISTINCT] 제거 후, 총합 평균)
--       null값 카운팅을 하지 않는다.
--       nvl(해당 컬럼, 0) 값 치환
-- MIN / MAX
-- : 해당 컬럼의 최소값/ 최대값
-- COUNT : Result Set 행 수 반환, Low 개수, null 값 카운팅하지 않는다.
SELECT  count(bonus_pct),
        count(*)
FROM    employee;
 
SELECT  *   FROM employee;
 
SELECT  count(*),
        count(job_id),
        count(distinct job_id)
FROM    employee
WHERE   dept_id = '50'
OR      dept_id is null;
 
 
-- 직급별 급여의 평균을 출력하시오.
-- 출력항목 : 직급코드, 평균코드
 
SELECT  avg(salary)
FROM    employee;
 
SELECT  avg(salary)
FROM    employee
WHERE   job_id = 'J7';
 
-- ORDER BY : 정렬 - 오름차순( asc, 기본값), 내림차순(desc)
 
SELECT  dept_id,
        job_id,
        emp_name
FROM    employee
order by dept_id asc, job_id desc, emp_name asc;
 
-- GROUP BY : 그룹화 함수 => 그룹화해야한다.
--          WERER 불가, GROUP BY 컬럼 이름만
 
SELECT  dept_id, avg(salary), avg(nvl(salary, 0))
FROM    employee
group by dept_id;
 
-- 직급별 평균급여를 구하시오.
SELECT  job_id, avg(salary)
FROM    employee
group by job_id;
 
-- 부서별, 직급별 평균급여를 구하시오.
SELECT  dept_id, job_id, emp_name, avg(salary)
FROM    employee
group by dept_id, job_id, emp_name;
 
SELECT  max( sum(salary) )
FROM    employee
group by dept_id;
 
-- 직급별 급여의 합계가 500만원 이상인 부서의 부서명과 급여의 합을 출력하세요.
SELECT  dept_id, sum(salary)
FROM    employee
-- WHERE   sum(salary) >= 5000000 그룹함수는 WHERE 조건 불가
group by dept_id
having  sum(salary) >= 5000000
order by 1; 
 
-- HAVING : GROUP BY의 조건절(그룹화된 함수의 조건절)
 
-- WORKSHOP 02
-- 09.
SELECT  trunc(avg(point), 1) as "평점"
FROM    tb_grade
group by student_no
having student_no = 'A517178';
 
-- 10.
SELECT  distinct 
        department_no as "학과번호",
        count(department_no) as "학생수(명)"
FROM    tb_student
group by department_no
order by 1;
 
-- 11. 
SELECT  count(*) as "count(*)"
FROM    tb_student
group by coach_professor_no
having   coach_professor_no is null
order by 1;
 
-- 12. 
SELECT  substr(term_no, 1, 4) as "년도",
        round(avg(point), 1) as "년도 별 평점"
FROM    tb_grade
WHERE   student_no = 'A112113'
group by substr(term_no, 1, 4)
order by 1;
 
-- 13.
SELECT  department_no as "학과코드명",
        sum( decode(absence_yn, 'Y', 1, 0) ) as "휴학생 수"
FROM    tb_student
group by    department_no
order by 1;
 
--SELECT  absence_yn,
--        decode(absence_yn, 'Y', 1, 0)
--FROM    tb_student;
 
-- 14.
SELECT  student_name as "동일이름",
        count(*) as "동명인 수"      
FROM    tb_student
group by student_name
having  count(*) > 1
order by 1;
 
-- 15.
SELECT  substr(term_no, 1, 4) as "년도",
        substr(term_no, -2) as "학기",
        round( avg(point), 1 ) as "평점"
FROM    tb_grade
WHERE   student_no = 'A112113'
group by rollup( substr(term_no, 1, 4), substr(term_no, -2) )
order by 1, 2;
 
SELECT  substr(term_no, 1, 4),
        avg(point)
FROM    tb_grade
group by rollup (substr(term_no, 1, 4)) -- 누적 평점
order by 1;
 
-- ADDITIONAL SELECT : OPTION
-- WORKSHOP 03
-- 01.
SELECT  student_name as "학생 이름",
        student_address as "주소지"
FROM    tb_student
order by 1, 2;
-- order by student_name asc;
 
-- 02.
SELECT  student_name,
        student_ssn
FROM    tb_student
WHERE   absence_yn = 'Y'
order by 2 desc;
-- order by student_ssn desc;
 
-- 03.
SELECT  student_name as " 학생이름 ",
        student_no as " 학번 ",
        student_address as " 거주지 주소 "
FROM    tb_student
WHERE   (student_address like '%경기%'
OR      student_address like '%강원%')
AND     student_no not like 'A%'
order by 1 asc;
-- order by student_name asc;
 
-- 04.
SELECT  professor_name,
        professor_ssn
FROM    tb_professor
WHERE   department_no = '005'
order by 2 asc;
-- order by professor_ssn asc;
 
SELECT  department_no,
        department_name
FROM    tb_department
WHERE   department_name = '법학과';
 
-- 05.
SELECT  student_no,
        to_char(point, '9.00') -- '9.99' 도 가능
FROM    tb_grade
WHERE   term_no = '200402'
AND     class_no = 'C3118100'
order by 2 desc;
-- order by point desc;
 
-- JOIN : 오라클 구문
SELECT  emp_name,
        salary,
        dept_name
FROM    employee,
        department; 
        -- 가능하지만, 무작위이다. 원하는 것과 매칭이 되지 않는다.
        -- 모든 경우의 수를 다 보여준다.
 
SELECT  emp_name,
        salary,
        dept_name
FROM    employee    e,
        department  d -- 변수 선언하듯, 임의로 지정 가능.
WHERE   e.dept_id = d.dept_id ;
-- WHERE   employee.dept_id = department.dept_id ;
 
SELECT  emp_name,
        dept_name,
        --dept_id,
        --job_id,
        job_title
FROM    employee    e,
        department  d,
        job j
-- join 작업
WHERE   e.dept_id = d.dept_id
AND     e.job_id = j.job_id;
 
-- 컴퓨터 공학과에 다니는 학생의 이름과 학번, 학과명을 출력하세요.
SELECT  student_name,
        student_no,
        department_name
FROM    tb_student      s,
        tb_department   d
WHERE   s.department_no = d.department_no
AND     department_name = '컴퓨터공학과';
        
-- [JOIN : ANSI 표준 구문]
-- join 테이블 명 using (해당 컬럼, 공통되는 컬럼명)
SELECT  student_name,
        department_name
FROM    tb_student
join    tb_department using (department_no);
 
-- 대리 직급 사원의 이름과 부서명을 출력하시오.
-- 오라클 구문 형식
SELECT  emp_name,
        dept_name
FROM    employee    e,
        department  d,
        job         j
WHERE   e.dept_id = d.dept_id
AND     e.job_id = j.job_id
AND     job_title = '대리';
 
-- ANSI 구문 형식
SELECT  emp_name,
        dept_name
FROM    employee
join    department  using (dept_id)
join    job         using (job_id)
WHERE   job_title = '대리';
 
-- join on : 사용하고자 하는 컬럼명이 다를 경우
SELECT  dept_name,
        loc_describe
FROM    department
join    location    on (loc_id = location_id);
 
SELECT  dept_name,
        loc_describe
FROM    department  d,
        location    l
WHERE   d.loc_id = l.location_id;
 
-- 06.
SELECT  student_no,
        student_name,
        department_name
FROM    tb_student
join    tb_department using (department_no)
order by 2 asc;
-- order by student_name asc;
 
SELECT  student_no,
        student_name,
        department_name
FROM    tb_student      s,
        tb_department   d
WHERE   s.department_no = d.department_no
order by 2 asc;
-- order by student_name asc;
 
-- 07.
SELECT  class_name,
        department_name
FROM    tb_class
join    tb_department using (department_no)
order by 2;
-- order by class_name asc, department_name asc;
 
SELECT  class_name,
        department_name
FROM    tb_class        c,
        tb_department   d
WHERE   c.department_no = d.department_no
order by 2;
-- order by class_name asc, department_name asc;
 
-- 10.
SELECT  s.student_no as "학번",
        student_name as "학생 이름",
        round(avg(point), 1) as "전체 평점"
FROM    tb_student      s,
        tb_grade        g,
        tb_department   d        
WHERE   s.student_no = g.student_no
AND     d.department_no = s.department_no
AND     department_name = '음악학과'
group by s.student_no, student_name
order by 3 desc;
-- order by point desc;
 
SELECT  student_no as "학번",
        student_name as "학생 이름",
        to_char(round(avg(point), 1), '9.0') as "전체 평점"
FROM    tb_student       
join    tb_grade        using (student_no)
join    tb_department   using (department_no)
WHERE   department_name = '음악학과'
group by student_no, student_name
order by 3 desc;
-- order by point desc;