sql workshop - day1

기록용

--1
select department_name as "학과 명", category as "계열"
from tb_department;

--4
select student_name
from tb_student
where student_no in ('A513079', 'A513090', 'A513091','A513110', 'A513119');

--5
--where capacity>=20 and capacity<=30;
select department_name, category
from tb_department
where capacity between 20 and 30;

--8
select class_no
from tb_class
where preattending_class_no is not null;

--9
select DISTINCT category
from tb_department;

--실습2
--2
select professor_name, professor_ssn
from tb_professor
where professor_name like '____%';

--3
select professor_name AS "교수이름", TRUNC(MONTHS_BETWEEN(
TRUNC(SYSDATE), TO_DATE(CONCAT('19',SUBSTR(professor_ssn,1,2)),'YYYY'))/12) AS "나이"
from tb_professor;

--4
select substr(professor_name,2) "이름"
from tb_professor;

--6
select TO_CHAR(SYSDATE, 'DY')
FROM DUAL;


--11
select student_name "동일이름" , count(*) "동명인 수"
from tb_student
group by student_name
having count(*)>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 substr(term_no,1,4);

--13
select department_no "학과코드명", count(student_no) "휴학생 수"
from tb_student
where absence_yn = 'Y'
group by department_no
order by department_no;

--14
select student_name,count(student_no)
from tb_student;

--15
select student_ssn "주민번호", student_name "이름"
from tb_student
where substr(student_name,1,1) ='김' AND substr(student_ssn,1,2) ='80'
order by student_name;

--16
select department_name "학과이름", capacity "정원",
case when capacity>=40 then '대강의실'
when capacity>=30 then '중강의실'
else '소강의실'
end "강의실크기"
from tb_department
where category ='예체능'
order by capacity desc;

--17
select department_no, student_name,coach_professor_no, to_char(entrance_date,'yyyy')
from tb_student
where student_address is null and entrance_date between '05/01/01' and '06/12/31'
and substr(student_ssn,8,1) ='1'
order by entrance_date;