2020-09-10, SQL_3일차_연습문제

전체 DB 스키마

---

 

---

select student_no, student_name, entrance_date
from tb_student
where department_no = '002'
order by entrance_date;

---

 

--2번
select professor_name, professor_ssn
from tb_professor
where LENGTH(professor_name) != 3;

---

 

select professor_name, to_number(to_char(sysdate, 'YYYY')) - to_number('19' || substr(professor_ssn, 1, 2)) 나이
from tb_professor
order by 2;

---

 

--3번    ******
select SUBSTR(professor_name , 2) 이름
from tb_professor;

---

 

--5번    ****
select student_no, student_name
from tb_student
where to_number(to_char(entrance_date, 'YYYY')) -
      to_number(to_char(to_date(substr(student_ssn, 1, 2), 'RR'), 'YYYY')) > 19
order by 1;   

---

 

---

select to_char(to_date('20201225'), 'YYYYMMDD DAY') 
from dual;

---

 

---

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
select student_no, student_name
from tb_student
where SUBSTR(entrance_date, 1, 4) < 2000;

--강사님
select student_no, student_name
from tb_student
where SUBSTR(student_no, 1, 1) <> 'A';

---

 

---

select round(AVG(point), 1) 평점
from tb_grade
where student_no = 'A517178';

---

 

---

select department_no 학과번호, count(*) "학생수(명)"
from tb_student
group by department_no
order by 1;

---

 

---

select count(*)
from tb_student
where coach_professor_no is null;

---

 

---

select substr(term_no, 1, 4) 년도, round(AVG(point), 1) 평점
from tb_grade
where student_no = 'A112113'
group by substr(term_no, 1, 4);

---

 

---

--12번+1       ****이게 가장 어려웠다.
select department_no 학과코드명, count(*) "휴학생 수"
from tb_student
where absence_yn ='Y'
group by department_no
order by department_no;

--강사님
select department_no 학과코드명,
       sum(case when absence_yn = 'Y' then 1
                                      else 0 end) as "휴학생 수"
from tb_student
group by department_no
order by department_no; 

---

 

---

select student_name 동일이름, count(student_name) "동명인 수"
from tb_student
group by student_name
HAVING count(student_name) > 1;

--강사님
select student_name 동일이름, count(*) "동명인 수"
from tb_student
group by student_name
HAVING count(*) > 1;

---