2020-09-09, SQL_2일차_연습문제

자, 일단 전체 스키마부터 보고 시작해야겠죠?

---

 

문제는 8문제가 있었는데 아직 안 배운 부분이 있어서 5개는 풀고 3개는 다음에 풀기로 했습니다.
(푸는대로 추가하도록 하겠습니다. -추가 완료)

 

---

--1번
select count(*) "학생 수"
from tb_student
where department_no = '003' and entrance_date like '01%';

---

 

---

--2번
select category "계열", department_name "학과이름", capacity "정원"
from tb_department
where capacity between 20 and 30 and category ='공학' 
ORDER BY department_name ASC;

select category "계열", department_name "학과이름", capacity "정원"
from tb_department
where 20 <= capacity and capacity <= 30
ORDER BY department_name;

---

 

---

select category 계열, count(*)
from tb_department
where category like '%학%'
group by category
order by 2 desc;

---

 

---

select professor_name "교수이름", SUBSTR(professor_ssn, 1, 2) "출생년도", professor_address "주소"
from tb_professor
where department_no = '002'
order by SUBSTR(professor_ssn, 1, 2) ASC;
--order by 2 ASC;

---

 

---

--5번
select * from TB_student;
select department_no, student_name, 
        DECODE (absence_yn, 'Y',  '휴학',
                             'N', '정상') as 휴학여부
from TB_student
where SUBSTR(student_address, 1, 2) = '서울' and department_no = '001'
order by student_ssn asc;

---

 

---

select '['||RPAD(SUBSTR('900303-1234567',1,8),14,'*') || ']' "주민번호", student_name "이름"
from tb_student
where substr(student_ssn, 1, 2) = '80' and student_ssn LIKE '_______2%' and student_name like '김%'
order by student_name ASC;
--where student_ssn like '80%' and substr(student_ssn, 8, 1) = '2' and student_name like '김%'

---

 

---

select department_no "학과번호", student_name "학생이름", coach_professor_no "지도교수번호", TO_CHAR(entrance_date, 'YYYY"년"') "입학년도"
from tb_student
where student_address is Null and student_ssn LIKE '_______1%' and entrance_date between '05/01/01' and '06/12/31'
order by entrance_date ASC;
--where student_address is Null and substr(student_ssn, 8, 1) = '1' and entrance_date between '05/01/01' and '06/12/31'

---